DB2 Identity Column

Havoc · Post by **Havoc** » Wed Jul 04, 2007 5:31 am

Hi,

We have a requirement as follows:

A surrogate key (primary key) needs to be generated for an Exception table.
Our jobs reference a shared container which inserts this surrogate key (using surrogate key generator and lookup to MAXVAL) into the table. But this inhibits us to run our jobs in parallel because we are using an ODBC stage to do so.

We were thinking of using the DB2 Identity column to achieve this purpose of running our jobs in parallel.

We can only use DB2 API stage and we can't use a DB2 sequence object.

What do you guys think? Will using an identity column help us run our jobs in parallel?

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Jul 04, 2007 1:21 pm

Any mechanism external to DataStage will need a sequential process to generate unique keys.

The only way truly to use fully-scalable parallelism is to initialize a lookup data set with the next available key, and then use a formula based on the partition number and partition count to add to that.

Examples have been posted in the past, please search for them.