Continuous Surrogate key generation for multiple runs

swathi.yanala · Post by **swathi.yanala** » Wed Sep 10, 2008 6:19 am

Hi

Is there a possibility in DataStage PX to generate a surrogate key which is continuous for multiple runs also.

For Example: If in 1 run, the surrogate key generator generates 1 through 10 keys, then in next run the key count should start from 11 and continue the count from there on.

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Wed Sep 10, 2008 6:43 am

Yes. All you have to do is to record where you left off, and add that to a key generated from 0 or 1 in the next run.

balajisr · Post by **balajisr** » Wed Sep 10, 2008 6:43 am

Yes. You need to set "Start Value" as 11.

Get the maximum value from the previous run,increment it by 1 and set this value as a job parameter to this job. Use this job parameter as "Start Value".

gnan_gun · Post by **gnan_gun** » Mon Sep 15, 2008 8:36 am

In Datastage u can, But u have to pass maximum value of last run to startvalue by manually. By Using the Oracle Sequences you can achieve this with out passing any params by manually. Just create a sequence in oracle and use it where u required.
Ex: Insert in to table(empid,ename, sal) values (oracle_sequence.nextval, orchestrate.ename, orchestrate.sal)

ray.wurlod · Post by **ray.wurlod** » Mon Sep 15, 2008 8:52 am

U did not ask the question. U is the name of one of our posters.

This is one of the reasons we ask posters to adhere to a professional standard of written English on DSXchange. DSXchange is not a mobile telephone; SMS may have its place, but DSXchange is not that place.

crouse · Post by **crouse** » Mon Sep 15, 2008 9:12 am

Be careful using database sequences in PX jobs... sooner or later they WILL be the slowest link in your job(s), and could slow the job(s) down significantly.

srividya · Post by **srividya** » Mon Sep 15, 2008 12:19 pm

Yes ... using the database sequences definitely slowed our jobs significantly... The more data we tried processing, the worse the performance got ... We have to go for a re-design

....

This is what we are doing(trying to

) ......We have created a UNIX script which returns the maximum of the sequence from the table. We are returning this Value as output of this script.

In the sequencer job we are calling this before the required job and pass the output to the job

Now i am working on whether i can pass the pouput directly in the sequencer or whether i will need to assign this to a variable in UNIX script only ....

Will keep you posted once i get something ....

ray.wurlod · Post by **ray.wurlod** » Mon Sep 15, 2008 3:59 pm

Could I suggest using a DataStage job (a server job is most efficient) rather than a UNIX script for extracting the next value from the target table? Using the server job's user status area makes it easier for the job sequence to retrieve the value, which it can do with an unqualified reference to the activity variable $UserStatus.