Showing posts with label datastage. Show all posts
Showing posts with label datastage. Show all posts

Sunday, September 04, 2011

Execution orders for Stage Variables, Constraints and Column Derivations in Transformer stage


For people that work in DataStage ETL tools, using Transformer stage in a Server/Parallel is very common and personally I would say transformer stage will be used at least once most of the time. Have anyone clear about the execution order for Stage variables, Constraints and Column derivations in Transformer? 

Today what I wish to share is about the execution order for stage variables, constraints and column derivations.

In transformer stage, execution order for the three components I mentioned above is stage variables, constraints and column derivations. Basically, DataStage executes these from top to bottom. This is clearly shown when you double click on the Transformer stage and you would see stage variable is located the top, followed by constraints and column derivations.

Please always remember the following characteristics for each component:
  • Stage variables - Executed for every rows that we processed/extracted
  • Constraints - Can be treated as a filter condition which limits the number of rows/records coming from our input based on the business rules we defined. Stage variable can be used in constraints.
  • Column derivations - Used to get or modify our input values, i.e. concatenation of two values from inputs, set the column to constant value, etc.

Sunday, February 27, 2011

Default Null in DataStage

In DataStage, the way to default null in transformer stage for Server job and Parallel job are different.

In transformer stage in Server job, @NULL is used.
In transformer stage in Parallel job, SetNull() is used.

Monday, July 26, 2010

DataStage job aborted due to ulimits - Need to be able to open at least 16 files; please check your ulimits settings for number of file descriptors

I am running a job on AIX 64-bit with DataStage V8.1 installed. The DataStage job failed with following error messages (refer to screen attached):

Fatal Error: Need to be able to open at least 16 files; please check your ulimits settings for number of file descriptors

Solutions: 

1. Check the value for ulimits in dsenv is set to 102400 or higher. If not, change the value of ulimits and do a restart on DataStage engine. Command to restart DataStage engine as below:
          • cd $DSHOME/
          • . ./dsenv
          • bin/uv admin –stop
          • bin/uv admin -start
2. Login using root and check /etc/security/limits. Look for value of nofiles and nofiles_hard in that file. If the value is lower than 102400 then modify it to 102400 for both of the entries. Add the entries if there are not in the limits file.

To resolve the problem, both of the steps must be applied in dsenv and root level.

References
http://www-01.ibm.com/support/docview.wss?uid=swg21322302
http://www-01.ibm.com/support/docview.wss?uid=swg21385849



Saturday, June 12, 2010

Enable BASIC transformer in DataStage

When you are developing a Parallel job in DataStage, we are able to call a server routine in our Parallel job provided that we make use of BASIC transformer.

BASIC transformer is not visible in the Proccesing palette in Parallel job. Hence we would need to bring in the BASIC transformer to our design canvas following the steps stated below:
Go to Repository tree -> Stages Types -> Parallel ->  Processing -> BASIC Transformer.
Drag and drop the BASIC transformer stage to our design canvas.



With BASIC transformer, we are able to perform various server type data transformation in parallel job. For example, calling a server routine, link variables, etcs.

Friday, May 21, 2010

Non-Wire protocol drivers VS Wire protocol drivers

Basically there are two type of ODBC drivers being used in DataStage:
  1. non-wire protocol drivers - require  database client software to be installed on the DataStage server (the drivers use the API supplied by the database client)
  2. wire protocol drivers - do not require database client software (they communicate with the database directly)
Technorati Tags:

Tuesday, May 04, 2010

Connection to Teradata database via ODBC stage in DataStage

Before we can connect to the Teradata database using ODBC stage in DataStage, we need to define the Data Source on the DataStage server.

In Windows environment, we can defined the Data Source by going to Control Panel -> Administrative Tools -> Data Sources (ODBC) . Add a system DSN.

Note: DataStage cannot work on user DSN.

In Unix environment, three files need to be edited for ODBC configuration.

1. dsenv file - contains the environment variables to connect to different databases
2. .odbc.ini file - contains the names and the data sources available to the Unix users.
3. uvodbc.config file - contains the DSNs name that connect through ODBC

Example of dsenv file

#Oracle 9i
ORACLE_HOME=/space/oracle9i
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib;export
LD_LIBRARY_PATH
ORACLE_SID=WSMK5
export ORACLE_HOME ORACLE_SID<o:p></o:p></span></p>

Example of an .odbc.ini file

[ODBC]
InstallDir=/usr/odbc
Trace=0
TraceDll=/usr/odbc/lib/odbctrac.so
TraceFile=/usr/odbcusr/joe/trace.log
TraceAutoStop=1

[ODBC Data Sources]
financial=tdata.so

[financial]
Driver=/usr/odbc/drivers/tdata.so
Description=NCR 3600 running Teradata V2R5.1
DBCName=123.45.67.10
DBCName2=123.45.67.11
DBCName3=123.45.67.12
Username=odbcadm
Password=
Database=
DefaultDatabase=sales

Example of uvodbc.config

[ODBC DATA SOURCES]

<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = 127.0.0.1

<TEST_DSN>
DBMSTYPE = ODBC

Once we are done with the configuration, we should do a test to the ODBC driver connectivity.
Step 1. Change to $DSHOME directory. Then type in . ./dsenv
Step 2. Run the DataStage server shell by type in ./bin/dssh
Step 3. Log on to the project using LOGTO your_project_name
Step 4. Use DS_CONNECT to get the list of DSNs available.
Step 5. Type DS_CONNECT dsn_name to test the connectivity.

References
http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r0/index.jsp?topic=/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/tasks/wsisinst_Third_Party_ODBC_Drivers.html



Saturday, April 03, 2010

[DataStage 8] IIS-DSEE-TCOS-00036 - Failed to initialize job monitoring

Situation:
While running a simple DataStage parallel job in Windows Server 2003 to extract from CSV file and load into Data Set, the job completed successfully. The problem is the link color is not in GREEN but in BLACK. I verified the job status in DataStage Director and found out everything is Green with one Warning message. Below is the Warning Message:

main program: Failed to initialize job monitoring. Monitoring information will not be generated.

Cause:
JobAppMon is not running.

Solution:
Run command prompt and enter the following commands:

set APT_ORCHHOME=C:\IBM\InformationServer\Server\PXEngine
cd C:\IBM\InformationServer\Server\PXEngine\java
sh jobmoninit start
JobMonApp has been started.

Re-run the job.

Tuesday, September 23, 2008

DataStage Error - [General repository interface 'other error']

Situation
Today,one of my DataStage job that is scheduled to be run everyday suddenly failed and below is the error message that I captured in DataStage Director.

jobControlForEntity..JobControl (@JA_loadETLStreamFromRelationship): Controller problem: Error calling DSRunJob(loadETLStreamFromRelationship), code = -99
[General repository interface 'other error']


When I saw this error message, I really have no clues on why this error message is thrown as the job is running without fail all the time. Anyway, after spending a couple of hours to debug it and I managed to resolve this.


Solutions
  1. Create a new copy of the loadETLStreamFromRelationship by right click on the job and select Create Copy. A new job with the name CopyOfloadETLStreamFromRelationship is created.
  2. Change the job sequence to invoke the new copy of the job which is CopyOfloadETLStreamFromRelationship.
  3. Recompile and re-run the job. Now the job running fine without throwing any errors as stated above.
  4. Delete the previous job loadETLStreamFromRelationship.
  5. Rename the new copy of the job to become loadETLStreamFromRelationship and change back the sequence to invoke the loadETLStreamFromRelationship.

Hope this helps.