How to increment surrogate key

[Amarpreet]

I have one job which is loading 50000 records in my target table and I am using surrogate key generator stage in the job to generate values for my surrogate key.
In my second job again I have to load some 10,000 records into the same target table, but now I have to look what is the max(surr_key) in the backend and have to start my count from max(surr_key)+1. It is a simple job, as I am using one DB2 enetrprise stage, one transformer and one DB2 enterprise stage for target. How can I get this max(surr_key) value in this job and start my load again from +1 onwards?

[ray.wurlod]

You can not do this easily in one job. A better approach is to run an earlier job to get the next key value, then feed that as a job parameter to your main job. Both jobs can be run from a job sequence.

[Amarpreet]

Could you explain this in more detail?
What I understood is, I have to create one job wherein I have to write a query something like select max(key) from table1, then I will put this into one flat file. In the second job, what should I do. How can I fetch this value into some job parameter and use that parameter+1 in my transformer?

[kumar_s]

You can use userStatus option to pass on value form the previous stage in JobSequnce.
Or you can define your own surrogate key. You can have the max of the field as an additional column in user defined query, and use it the transformer to frame your logic to add 1 with the help of Stage variable.

[ray.wurlod]

The initial value must be obtained from somewhere, using select max(keyvalue) + 1 from table; which is a query that executes in sequential mode - no need for parallelism since it can only return one row. Do this in one job - perhaps even a server job - and capture the result into either a file or that job's user status area. The second approach is better in so far as it avoids the need for some activity to read the file.

The job parameter - perhaps called SKInitialValue - in your main job can then have its value assigned from the upstream job activity's $UserStatus activity variable. If the first job is called GetNextKey then the activity variable is GetNextKey.$UserStatus

To set the user status value in a server job before discarding the output create a simple routine that calls the DSSetUserStatus subroutine. Output from that job is a Sequential File stage that appends to /dev/null (UNIX) or .\NUL (Windows). These techniques can be found in more detail by searching the forum.

[faheemrao]

You can accomplish this into one job. All you need is a lookup to you database table and you can find out what is the max(Key) then you can start your surrogate_key_generator stage with a starting value of 1. Use a stage variable to add the Key value which is generated by surrogate key and the max value which you got from database. You will get the requitred results.




Regards

Rao

I have one job which is loading 50000 records in my target table and I am using surrogate key generator stage in the job to generate values for my surrogate key.
In my second job again I have to load some 10,000 records into the same target table, but now I have to look what is the max(surr_key) in the backend and have to start my count from max(surr_key)+1. It is a simple job, as I am using one DB2 enetrprise stage, one transformer and one DB2 enterprise stage for target. How can I get this max(surr_key) value in this job and start my load again from +1 onwards?

[balajisr]

Kumar,

You can use userStatus option to pass on value form the previous stage in JobSequnce.

Can you please explain how to set value to userstatus in parallel jobs?

[bala_135]

Hi,

To add to the comments

Write a query.

Add a column(which should be unique ie 1) to the source files using the column generator.

select nvl(max(ACCT_SUR_KEY),0) as ACCT_SUR_KEY,1 as DUMMY from ACCOUT.

Do a lookup between the newly generated column and the dummy from the table and propagate max_value from table.

pass this column to the next stage(transformer(max value+@inrownum or buildop(your logic to add).

Regards,
Bala.

[opdas]

Rao,
I tried your way which is working very fine.

You can accomplish this into one job. All you need is a lookup to you database table and you can find out what is the max(Key) then you can start your surrogate_key_generator stage with a starting value of 1. Use a stage variable to add the Key value which is generated by surrogate key and the max value which you got from database. You will get the requitred results.

[kool78]

You can use userStatus option to pass on value form the previous stage in JobSequnce.
Or you can define your own surrogate key. You can have the max of the field as an additional column in user defined query, and use it the transformer to frame your logic to add 1 with the help of Stage variable.

Hi Kumar_s

Could you elobarote more details on how do u assign the value to userstatus..

I tried in manyway but couldn't figure it out, i deeloped a job whihc reads the max value and stores it in a sequential file and as u mentioned we can assign this value to userstatus in JobSequence....how do u do tht?

it would be grt if u can provide more information of this logic.

Thanks