Surrogate Key -- Database Sequence

[Murali4u]

Dear all,
Two questions:

1. I could establish the Surogate key generation through flat file. Can you explain how to generate it through DB2 sequence.


2. I have a scenario:
Input : Dataset -- 2 million records contains (empname,esal)
In target DB2 table say EMP (empid is a sequence generator).

For every record in dataset, i want to capture the sequence number plus the value of employee in another dataset without inserting it into the target table..

so my target dataset should be (empid generated sequence no from table, empname, esal)

Can you explain me how to achieve this in parallel.. Thank you..

[jwiles]

Use the Surrogate Key Generator stage and set the Source Type option to Database Sequence. Then fill in the appropriate connection and output column information.

Regards,

[soumya5891]

You can generate surrogate key from a surrogate key generator stage and also from transformer fro DB sequence.And give the proper database related info there.

[ray.wurlod]

If other applications are also updating the table using the sequence, you can also refer to the sequence nextval property in user-defined SQL.

[Murali4u]

Thanks ray, other application interventions may occur, so created a user defined sql which resolves my requirement. But still i dont know how to use the sequence in skg stage..

[ray.wurlod]

It's pretty obvious once you start playing with it.

There are three job designs that use the Surrogate Key Generator stage.

• With no links, the Surrogate Key Generator stage creates a state file.
  
  With an input link, the Surrogate Key Generator stage initializes a state file or obtains initial data from a database sequence.
  
  With an output link, the Surrogate Key Generator stage delivers surrogate keys.