Accessing the value of an Identity column after insert

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
cyberhiker
Participant
Posts: 2
Joined: Mon Jul 30, 2018 10:17 am

Accessing the value of an Identity column after insert

Post by cyberhiker »

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 ) )                       
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
cyberhiker
Participant
Posts: 2
Joined: Mon Jul 30, 2018 10:17 am

Post by cyberhiker »

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.
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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.
Post Reply