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.
z/OS DB2 - Surrogate Key Generation Through Transformer
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 135
- Joined: Tue Aug 14, 2007 4:27 am
- Location: Mumbai
z/OS DB2 - Surrogate Key Generation Through Transformer
Thanks
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"
I believe that DB2 on z/OS is accessed via a gateway system. As such, believe it or not, it is not supported by DataStage DB2 connector. At least that is what I was told by IBM when I tried to access it from DataStage 8.1 and had similar issues.
Last edited by asorrell on Mon Aug 19, 2013 10:50 pm, edited 2 times in total.
-
- Participant
- Posts: 135
- Joined: Tue Aug 14, 2007 4:27 am
- Location: Mumbai
Thanks for reply Andy, sorry but I couldn't understand your statement that zOS DB2 is not supported by DS.
We are running jobs with all the functionality with zOS db2 and its working fine.
Did you mean zOS DB2 Sequence are not supported by DataStage ?
We are running jobs with all the functionality with zOS db2 and its working fine.
Did you mean zOS DB2 Sequence are not supported by DataStage ?
Thanks
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"
The reply I got from IBM was that accessing z/OS DB2 through a gateway was unsupported and as such, no functionality was guaranteed to work. If it did, great, if not, too bad.
Last edited by asorrell on Mon Aug 19, 2013 10:45 pm, edited 1 time in total.
-
- Participant
- Posts: 135
- Joined: Tue Aug 14, 2007 4:27 am
- Location: Mumbai
Forgot to add - at release 9.1 they added a DB2 Connector for z/OS. I have no idea of the requirements for support / configuration as I don't have access to 9.1 right now.
http://pic.dhe.ibm.com/infocenter/iisin ... 2conn.html
http://pic.dhe.ibm.com/infocenter/iisin ... 2conn.html
You should contact your official support provider in order to verify whether or not the Surrogate Key Stage (and the transformer's SK logic) support a DB sequence on DB2 z/OS.
A workaround would be to call the DB sequence in the connector by using the NEXT VALUE function--this will require coding your own SQL to perform the inserts. I did this recently with a client using IS 9.1 on Linux for System z.
Regards,
A workaround would be to call the DB sequence in the connector by using the NEXT VALUE function--this will require coding your own SQL to perform the inserts. I did this recently with a client using IS 9.1 on Linux for System z.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 135
- Joined: Tue Aug 14, 2007 4:27 am
- Location: Mumbai
Thank You James.
The logic is not very simple in other jobs. The NextValue is used in many places so coding it in DB2 Connector will not solve .
I shall reach to official IBM support . I hope they have some solution to it.
The logic is not very simple in other jobs. The NextValue is used in many places so coding it in DB2 Connector will not solve .
I shall reach to official IBM support . I hope they have some solution to it.
Thanks
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"