DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
cyberhiker
Participant



Joined: 30 Jul 2018
Posts: 2

Points: 41

Post Posted: Mon Jul 30, 2018 10:34 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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/knowledgecenter/en/SSEPEK_10.0.0/apsg/src/tpc/db2z_identitycols.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:
SQL SELECT ID_COL_FIELD INTO :W-ID_COL_FIELD                 
    FROM FINAL TABLE                                   
    ( INSERT INTO SOMETABLENAME
( FIELD1, FIELD2)                 
 VALUES( :W-FIELD1, :W-FIELD2 ) )                       
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Tue Jul 31, 2018 2:51 am Reply with quote    Back to top    

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.

_________________

Image
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42765
Location: Denver, CO
Points: 220367

Post Posted: Tue Jul 31, 2018 6:51 am Reply with quote    Back to top    

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

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
cyberhiker
Participant



Joined: 30 Jul 2018
Posts: 2

Points: 41

Post Posted: Tue Jul 31, 2018 6:56 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Fri Aug 24, 2018 2:57 am Reply with quote    Back to top    

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.

_________________

Image
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours