Retrieving Identity Column values from DB2/UDB

[nsm]

Hi,

I have a requirement to maintain Legacy IDs and new Target ID's for all the legacy tables in a cross-ref table. These Target ID's are IDENTITY columns in DB2/UDB.

The only way I know to get the ID columns from target tables is:
After populating each table in target with src table values, create another job where I lookup the target table with all source NON-ID columns and get the key.

When I explain the above to my peers saying this has to be done for all the tables. They are saying there is a way to get the NEXT set of values for the ID column( Ex: like 10 or 20 values) and UDB would lock those so that I can use them in processing.

Anyone tried getting the next usable set of Identity column values before even inserting rows in? Let me know if you have any ideas?

Even if its posible I am not sure how can I use it in Parallel Processing, as the rows doesn't gets processed in a particular order.

Thanks
nsm.

[infranik]

DB2 has a function 'nextval' - try and use it in a script . call this script in the before job subroutine. passing a parameter to this script as how many times the nextval command should execute could be done ,if you include in a while/for loop..
I presume that ID column is using a DB2 sequence object.
db2 "SELECT NEXTVAL FOR <db2seq> FROM <mytable>"

[nsm]

no..the ID columns in DB2/UDB are IDENTITY columns.

[Mike]

What options were used in creating your identity columns? I would relieve the database of its surrogate key generation duties. It's the most inefficient way to manage surrogate keys in an ETL application.

The only time you should ever let the database generate keys is if the key generation responsibility is shared between your ETL application and some other application.

If your ETL application is solely responsible for generating surrogate keys, then that's the place to manage them. The surrogate key stage in version 8 is quite capable.

The reason I asked about the identity column options is because you can set a value for an identity column during an insert (thus taking the responsibility away from DB2). It's been years since I dealt with a DB2 identity column, so I don't remember the specifics (e.g. if you can always do that or if it depends on how the column was created).

Mike

[nsm]

Mike,

When an IDENTITY column is defined as "GENERATED ALWAYS" Database can only create them.

To answer your point of generating ID's shared between applications is kind of no, Bcoz I am doing conversion and after conversion there is another application inserts data in it. but at conversion time its only us.

The keys in Old system are important but they didn't want to keep them because of data duplication and missing keys in large Intervals.
For Conversion we need to use the old keys to populate new system but to not use the old keys as IDs in new system..there was an Idea of creating a cross reference table which has source and target table names and all the source keys and Target Keys.

After conversion is complete and tested they wanted to get rid of cross-ref table.

coming to the coding point for me to populate the cross-ref table :
1) I need to populate the target table with src data with out src keys and then populate this cross-ref table with src keys and corresponding new target generated keys.

I need to put more effort into populating cross-ref table rather than populating straight load tables. That's the reason I was looking into better ideas for fetching these ID columns from target tables.

If I maintain keys in DataStage then it would be less effort to populate this cross-ref table as I could do both source and cross-ref at the same time.

I didn't use this surrogate key generator stage but Will try it out before I talk to my team.
nsm.

[ray.wurlod]

Why not just insert a row that does not mention the identity column? This will force the database to create the next value in that column. You don't really need to retrieve the value.

[vinnz]

You can use identity_val_local() to fetch the last identity value generated.

http://publib.boulder.ibm.com/infocente ... 004231.htm

HTH

[nsm]

Ray,

I kind of guessing you didn't read my previois post detailing why I have to read those ID's again. IF you did and if I Overlooked something let me know.

Vinnz,

the function you mention can be used with in a database procedure or if you are using JDBC you have a way to do the similar but not from datastage where you are doing multi row processing.

Thanks
nsm.

[ray.wurlod]

Surely to read them all you need to do is name them in a SELECT statement, whether generated or user-defined?

[kduke]

We do this all the time and it works fine. On Insert we leave off the column so the database will assign the value.

[vinnz]

Ray,
Vinnz,
the function you mention can be used with in a database procedure or if you are using JDBC you have a way to do the similar but not from datastage where you are doing multi row processing.
Thanks
nsm.

nsm,
Another way to do this would be to use data-change-table-reference clauses in DB2. Google for it and should be able to find examples.

HTH

[nsm]

thanks all.

We spoke to DBA's and had ID columns disabled as IDENTITY columns.