Friday, July 25, 2014

DataStage Custom Routine to Get a File Size

Below is a DataStage custom transform routine to get the size of a file. The full path of the file is passed in as a parameter called "Filename".

Thursday, July 24, 2014

Connect to Oracle DB with Linux Shell Script

In this post, we will see how to connect to Oracle DB in Linux Shell Script. There are some prerequisite for a Linux server to connect to Oracle DB. There should be oracle db driver installed on the Linux server from which you want to connect to Oracle DB. Check whether you can call/connect SQLPLUS from command line.
$ sqlplus
if you are not able to, check whether oracle path is set in .profile file. Lets assume, Oracle DB driver is installed on Server and Sqlplus path is set.

Now, there are some basic steps to connect to Oracle DB-
a) Connect to Oracle DB with UserID and Password
b) Execute the SQLs
c) Store the output in a file or send to standard output (terminal)

Monday, July 21, 2014

Navigating the many paths of metadata for DataStage 8

Source : Navigating the many paths of metadata for DataStage 8

Looking at the methods for importing metadata table definitions into DataStage 8 ETL jobs.
All of the metadata import methods of DataStage 7 are in DataStage 8 and all execute in the same way.  Developers familiar with previous versions will be right at home!  What is tricky to come to terms with are all the new ways to get metadata into DataStage 8 and the Metadata Server.  The Metadata Server can provide reporting on metadata from products outside of DataStage such as BI tools so in some cases you might be importing the metadata for reporting and not for ETL. 
The list that follows covers just the techniques for importing metadata to be used by DataStage jobs.

Friday, July 18, 2014

Some DataStage Job design

Updating the Master Reports List (MRL) in Datastage

Create a Job which has a Row Generator stage linking to an ODBC or DB2 stage. Change the parameter in the Row Generator stage to output zero records. Write the SQL to update the MRL table as 'After SQL' in the output ODBC or DB2 stage.