z/OS DB2 - Surrogate Key Generation Through Transformer
Posted: Mon Aug 19, 2013 10:03 am
Environments
We have two DataStage setups. One for windows and another with AIX.
We have two DB2 setup as well One with Windows and another one is withzOS.
Job Design
Job 1
Seq File ----> Transformer------> writing to multiple DataSets and Updating a Table.
Job 2
Dataset --> Transformer --> Lookup --> DB2
In Transformer I have used DB2 Sequence to generate surrogate keys .
Issue
First job ran file in both the environment and with Both DB2 (Zos and windows).
2nd job in which I have used Sequence failed to connect to the Zos db2 server( It works fine with windows environment) I searched the error messages in forum and found I should add APT_DBNAME variable which changed the error messages to below :-
TRANS_DATA_CONVERSION,0: Error Idx = 1;
DB2Driver Embedded SQL message: ;
sqlcode = -1390;
sqlstate =
TRANS_DATA_CONVERSION,0: Failed to connect to the database
Now what I don't understand that I am using the same db2 environment, user name and password in Job 1 and it works fine which means its able to connect to the database , but in job2 (which has sequence) its not . What makes Sequence with zOS DB2 so special ? What extra setting is required in this case ?
Please revert for any query / clarification / additional info.
We have two DataStage setups. One for windows and another with AIX.
We have two DB2 setup as well One with Windows and another one is withzOS.
Job Design
Job 1
Seq File ----> Transformer------> writing to multiple DataSets and Updating a Table.
Job 2
Dataset --> Transformer --> Lookup --> DB2
In Transformer I have used DB2 Sequence to generate surrogate keys .
Issue
First job ran file in both the environment and with Both DB2 (Zos and windows).
2nd job in which I have used Sequence failed to connect to the Zos db2 server( It works fine with windows environment) I searched the error messages in forum and found I should add APT_DBNAME variable which changed the error messages to below :-
TRANS_DATA_CONVERSION,0: Error Idx = 1;
DB2Driver Embedded SQL message: ;
sqlcode = -1390;
sqlstate =
TRANS_DATA_CONVERSION,0: Failed to connect to the database
Now what I don't understand that I am using the same db2 environment, user name and password in Job 1 and it works fine which means its able to connect to the database , but in job2 (which has sequence) its not . What makes Sequence with zOS DB2 so special ? What extra setting is required in this case ?
Please revert for any query / clarification / additional info.