Get Identity column value

Titto · Post by **Titto** » Thu Jul 28, 2005 10:14 am

Hi,

I have situation where i need to get the Identity Key column value for the inserted record. I could able to insert the record but I want the value of Identity column. I am using DB2 stage for this. Is there a way using After Sql in DB2 stage
How to acheve this?
any help is really appreciated.

Thanks,
Titto

vmcburney · Post by **vmcburney** » Thu Jul 28, 2005 5:06 pm

What do you mean you want to get the identity value? Do you want to get the last used value? Do you want to get the value for each row? What do you want to do with it? Are you retrieving it for reporting purposes? Do you want to save it somewhere?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 28, 2005 10:08 pm

All good questions from Vincent. How would you go about it if you didn't have DataStage? (It's not a trivial problem.)

A DataStage approach might be to manage the keys within DataStage during processing.
An alternative approach is to select the max value once processing has been completed.
Selecting the current max value after each row has been inserted would simply run too slowly to please anybody.

chulett · Post by **chulett** » Thu Jul 28, 2005 10:30 pm

Sometimes it is trivial.

Back in my Informix days, a SERIAL column would generate a new unique identifier upon insert (if you fed it a zero) and then a check of a specific field in the SQLCA record would retrieve the inserted value. Not sure how you would manage that in DB2 or DataStage for that matter.

If you need to know the value for later processing, I believe you'll find you need to handle the generation of it yourself pre-insert.

Titto · Post by **Titto** » Fri Jul 29, 2005 1:54 pm

Hi All,

The reason i want to user Identity colum is - I have input file comes with records, i need to store them in Table assigining a unique number, when insert it generated a number i want to use this number down the line to update the records with different statuses. So, how can i achieve this in DS. If it would have been any Cobol or other stuff i would have used IDENTITY_VAL_LOCAL function after the insert statement to fetch the latest unique number, I need help in this regard using DS.

Thanks,
Titto

vmcburney · Post by **vmcburney** » Sat Jul 30, 2005 3:43 am

Your first choice is whether to generate the unique ID within your ETL job or leave it to the database. Neither method is right or wrong, they just require different methods. If you continue to generate it on the database then you need to retrieve it again. The two methods are to load it into a hash file for fast lookups or retrieve it from the database via a lookup reference link. Both methods require using the original legacy or translation key to find the new surrogate or unique key.

Hash file method offers an advantage of being very fast and it also lets you store a change data code using the CRC32 function that gives you very fast surrogate key lookups and change data determination. This hash file can be used to process your downstream data and used when you process new source data.

chulett · Post by **chulett** » Sat Jul 30, 2005 7:13 am

Titto wrote:i want to use this number down the line to update the records with different statuses.

I think the key here is what 'down the line' means. If they are followup jobs, then you can get the new column values post-process and hash them up as Vincent notes.

However, if 'down the line' means 'later in the same job' then you'll need to generate them yourself rather than relying on the database to generate them for you.