z/OS DB2 - Surrogate Key Generation Through Transformer

swapnilverma · Post by **swapnilverma** » 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.

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Mon Aug 19, 2013 10:07 am

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.

swapnilverma · Post by **swapnilverma** » Mon Aug 19, 2013 10:16 am

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 ?

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Mon Aug 19, 2013 10:21 am

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.

swapnilverma · Post by **swapnilverma** » Mon Aug 19, 2013 10:28 am

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Mon Aug 19, 2013 10:26 pm

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

jwiles · Post by **jwiles** » Mon Aug 19, 2013 11:53 pm

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,

swapnilverma · Post by **swapnilverma** » Tue Aug 20, 2013 3:42 am

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.