DSXchange

Posted: **Mon Jul 30, 2018 10:34 am**

Any ideas on obtaining the value assigned to an Identity column (DB2 table) after an insert? There is no natural key to query on. The value is needed to use in subsequent logic. My coworkers have no experience in using Identity columns and seem to think DS doesn't handle them at all.

IBM doc on the topic of using Identity columns: https://www.ibm.com/support/knowledgece ... ycols.html

In regular SQL the value can be obtained using code like below (assuming a three column table called SOMETABLENAME with columns called: ID_COL_FIELD, FIELD1, FIELD2 with ID_COL_FIELD being the identity column). After the call the variable W-ID_COL_FIELD would contain the value assigned during the insert. An alternative is to use function IDENTITY_VAL_LOCAL() to obtain the value assigned in the most recent insert.

Code: Select all

SQL SELECT ID_COL_FIELD INTO :W-ID_COL_FIELD                  
    FROM FINAL TABLE                                    
    ( INSERT INTO SOMETABLENAME
( FIELD1, FIELD2)                 
 VALUES( :W-FIELD1, :W-FIELD2 ) )

Posted: **Tue Jul 31, 2018 2:51 am**

The identity column is not visible to DataStage when doing an insert, so cannot be handled directly. If you Need to a generate a unique key at runtime and simultaneously require use of that key in the same DataStage Job, then the "Surrogate Key Generator" is what you should be looking into using.

Posted: **Tue Jul 31, 2018 6:51 am**

Have you checked with your official support provider to see if there any help or perhaps workaround there?

Posted: **Tue Jul 31, 2018 6:56 am**

Arnd, the challenge is that this set of tables is shared and is also being updated by other (mainframe) processes that can handle Identity columns. Changing the tables (for example to use sequences) would impact the existing code. We are looking into using the stored procedure stage as an alternative.

Posted: **Fri Aug 24, 2018 2:57 am**

The site that I am currently at has an interesting solution to this Problem. They use DB2 which has no way to query the current value of a DB2 sequence, so they have a shared container which gets the next value of the sequence and then issues an external DB2 command to reset the sequence back by 1.

This is a viable Approach to the problem, and the same approach is used to generate sequence values in Jobs - the current sequence value is retrieved, then the values are not retrieved from the sequence but are computed in a transform stage and when the Job finishes the new "high value" of the sequence is written back to DB2. This produces high Performance and seqeuences without "holes", but one has to ensure that no other process accesses or changes the sequence while the Job is running.

DSXchange

Accessing the value of an Identity column after insert

Accessing the value of an Identity column after insert