Get Block of Surrogate Keys from DB Sequence

[darrreever]

Hello Datastagers!

How have you solved this problem? I am loading a large amount of data >100million records and I am using the IIS 8.7 64Bit AIX 6.2 surrogate key generation stage to connect to a DB2 9.7 FP7 Unix 64 bit database sequence with cached values we have created. Due to the fact that multiple applications will need to get a unique key for the table, we cannot use a key gen file in Datastage versus using the db2 sequence.

Herein lies the issue. As you know, and has been discussed in a related post on sequences, the SK Generation stage makes a SQL call to the database for each of the 100million rows to get the next value of the db sequence. That is a lot of I/O and network traffic. What I need/want to do is get a block of sequence numbers one time, either by alter sequence or some other method, and then use the pre returned sequence numbers in the jobs without hitting the database 100M times. Of course the sequence numbers must be pre-used from the db sequence perspective so another application will not get a number in the block and cause RI issues.

A twist is that the keys can be a primary key that I need in a child table as a foreign key, but the parent table has no columns to look-up that PK. How I get around that now is in the jobs I populate the parent table and a dataset that has the PK and a look-up value that I use in the job that loads the child or FK table.

Any suggestions?

God Bless!

[Rakesh311]

Create sequence number for each of your record starting form 1,

then get maximum value of ID from database which already present there and then add generated sequence number with max value form table.

Which you can use as your unique id.

Regards,

[prasannakumarkk]

What harm it will cause if we write a manual insert statement in the DB stage? that is insert statement with nextval for sequence. I havent tried it.