Get list of activated tools in Datastage Installation

If we want to check what features have been installed with datastage, we can check it by using LicensingServiceAdmin  tool. This will list down all the tool packs or features including Qualitystage as it comes as a part of datastage.


Tool Location :  {IS_INSTALL_DIR}/ASBServer/bin

FastTrack Makes Your DataStage Development Faster

IBM introduced a tool called FastTrack that is a source to target mapping tool that is plugged straight into the Information Server and runs inside a browser. 
The tool was introduced with the Information Server and is available in the 8.1 version.
As the name suggests IBM are using it to help in the analysis and design stage of a data integration project to do the source to target mapping and the definition of the transform rules.  Since it is an Information Server product it runs against the Metadata Server and can share metadata with the other products and it can run inside a browser.
I have talked about it previously in New Product: IBM FastTrack for Source To Target Mapping and FastTrack Excel out of your DataStage project but now I have had the chance to see it in action on a Data Warehouse project.  We have been using the tool for a few weeks now and we are impressed.  It’s been easier to learn than other Information Server products and it manages to fit most of what you need inside frames on a single browse screen.  Very few bugs and it has been in the hands of someone who doesn’t know a lot about DataStage and they have been able to complete mappings and generate DataStage jobs.
I hope to get some screenshots up in the weeks to come but here are some observations in how we have saved time with FastTrack:

DOS Batch Script to Export DataStage Jobs Automatically from a Project

Copy the following script into a text file and rename into ".bat" file.

 This batch script is used to Export all jobs from a Projects
 This script must be run from a DataStage client machine and the parameters below should be given

 Host is server name
 User is DataStage username
 Password is DataStage password
 Imp Location is the directory where the datastage dsx's are stored

A SQL Client Tool - TeraData Studio Express

Teradata Studio Express is a graphical Java program, developed on the Eclipse Rich Client Platform (RCP),  that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc.
This is a nice tool which can connect many DBs like Aster database, DB2 for LUW, DB2 for i5/OS, DB2 for z/OS, Oracle, SQL Server, Generic JDBC connection and of course TeraData DB.

WinSCP - Save all configurations in an INI file

You can configure WinSCP to save configurations to an INI file (instead of Windows Registry):

• Open WinSCP and go to the Preferences section.
• Next to "Other general options" click on the "Preferences" button.

Working with EditPlus Text Editor-Regular Expression How To

Editplus is a lot better than the regular text editor, Notepad.

From all it’s features I like RegExp Support the most, and than comes the block select feature.

Here are the quick lines to carry out regular tasks using regular expression in Editplus. It’s kinda downloadable cheetsheet list.

Remove all empty lines:

Find: “^\n” (Ignore double-quotes in all find/replace)

Replace: “”

Where,

^ – Beginning of the line

\n – New Line

Remove Multiple Spaces convert into single space:

Find: ” +”

Replace: ” “

Where,

+ – find one or more occurance of space character.

Comment multiple line of code:

Find: “^”

Replace: “#” or “//”

You may optionally use: Edit Menu > Format > Line Comment.

Generate Comma Separated List from new line delimited list:

Find: “\n”

Replace: “, “

This helps in even joining some of lines of code instead of replacing by comma you may replace it with “”.

Manipulate columns display order / punctuation:

Find: “([0-9]+)\t([a-zA-Z]+)”

Replace: “\2\t\1″

Where,

[0-9]+ – Finds one or more digits

[a-zA-Z]+ – Finds one or more characters

() – mark the block or capture the group

\2 – 2nd mark expression

Eg:

123 abc

345 cde

567 efg

Becomes:

abc 123

cde 345

efg 567

The Other Way:

- Press Alt+C

- Drag you mouse to select respective column and click

- Copy / Cut as required

[ad#ad-2-300x250]

Append / Add semicolon (any character) at the end of the line:

Find: “\n”

Replace: “;\n”

Enclose lines by quotes:

Find: “\n”

Replace: “‘\n’”

Delete all lines containing a given STRING:

Find: “^.*STRING.*$”

Replace: “”

Remove lines not containing a given STRING:

I don’t know how to do this!!

Convert tab separated file into insert statements:

TSV: abcd de4 iirn 34399

SQL: INSERT INTO TABLENAME VALUES (“abcd”, “de4″, “iirn”,”34399″);

Find: “(.*)\t(.*)\t(.*)\t(.*)”

Replace: “INSERT INTO TABLENAME VALUES (“\1″, “\2″, “\3″,”\4″);”

Format the telephone number:

Find: “([0-9][0-9][0-9])([0-9][0-9][0-9])([0-9].*)”

Replace: “\1-\2-\3″

Eg.:

Original: 1231231231

Formatted-1: 123-123-1231

Remove Brackets:

Find: “\(|\)”

Replace: “”

Where,

\( – Match (. \ is required to escape marking the expression.

| – or

Replace 1st occurrence of character:

Find: ” (.*)”

Replace: “-\1″

Where,

(.*) – matches everything and marks the block

** Make sure you ignore double-quotes(“) while writing in find / replace boxes.

EditPlus supports following regular expressions in Find, Replace and Find in Files command.

Expression – Description

• \t – Tab character.
• \n – New line.
• . – Matches any character.
• | – Either expression on its left and right side matches the target string.
• [] – Any of the enclosed characters may match the target character.
• [^] – None of the enclosed characters may match the target character.
• * – Character to the left of asterisk in the expression should match 0 or more times.
• + – Character to the left of plus sign in the expression should match 1 or more times.
• ? – Character to the left of question mark in the expression should match 0 or 1 time.
• ^ – Expression to the right of ^ matches only when it is at the beginning of line.
• $ – Expression to the left of $ matches only when it is at the end of line.
• () – Affects evaluation order of expression and also used for tagged expression.
• \ – Escape character. If you want to use character “\” itself, you should use “\\”.

Notable Features of Editplus are :

• Spell checking
• Regex-based find & replace
• Encoding conversion
• Newline conversion
• Syntax highlighting
• Multiple undo/redo
• Rectangular block selection
• Auto indentation
• Code folding (Text folding)

- See more at: http://kedar.nitty-witty.com/blog/working-with-editplus-text-editor-regular-expression-how-to#sthash.3CzDA4Bl.dpuf

Editplus is a lot better than the regular text editor, Notepad.
From all it’s features I like RegExp Support the most, and than comes the block select feature.
Here are the quick lines to carry out regular tasks using regular expression in Editplus. It’s kinda downloadable cheet-sheet list.

Remove all empty lines:
Find: “^\n” (Ignore double-quotes in all find/replace)
Replace: ""
Where,
^ – Beginning of the line
\n – New Line

JDBC connection with Teradata

Today I am going to tell you how to connect to TeraData via JDBC, Here I have used a RazorSQL tool.  For JDBC connection with TeraData, you can find the JDBC drivers here. Teradata JDBC driver enables all Java applications to connect a Teradata database using a well-established industry standard Java Database Connectivity (JDBC) API interface.

A new database connection can be obtained in a standard JDBC way by using java.sql.DriverManager.getConnection method with a proper connection string to get a new instance of java.sql.Connection.

The simplest database connection string would be:

Optimzie your DataStage Job Performance with relevant Environment Variables

DataStage has many parameters which can be tweaked and used to optimize the performance of various DataStage Jobs. Even many available to collect more information during the event of crash to get more traces.

For any DataStage Job if you run into problem or want to get more details need to check following variables.

Tips & Tricks for debugging a DataStage job

The article talks about DataStage debugging techniques. This can be applied to a job which

• is not producing proper output data or
• to a job that is aborting or generating warnings

1. Use the Data Set Management utility, which is available in the Tools menu of the DataStage Designer or the DataStage Manager, to examine the schema, look at row counts, and delete a Parallel Data Set. You can also view the data itself.