Hi All,
If I use multiple instances or invocations of the same job then How can I generate Surrogate keys be generated, the what is the way if the Oracle Seq.Nextval not available.
Can anyone suggest on this please
Thanks
SCK
Surrogate Keys for the multiple invocations of the Same Job
Moderators: chulett, rschirm, roy
Hi SCK,
The surrogate key generation job that we use is a multiple invocation job.
The invocation is based on JobRunID.
This is how it's been done.
The column generator is used to generate 2 columns one used for lookup to Oracle Enterprise stage and other for generating sequence number. The output from column generator stage is passed to the lookup stage. The lookup stage will get the maximum key from Oracle table. The output from lookup stage is passed to the transformer where stage variables are used to add the maximum value from the lookup stage with sequence number from column generator stage. You will end up with surrogate keys.
HTH
--Rich
Pride comes before a fall
Humility comes before honour
The surrogate key generation job that we use is a multiple invocation job.
The invocation is based on JobRunID.
This is how it's been done.
The column generator is used to generate 2 columns one used for lookup to Oracle Enterprise stage and other for generating sequence number. The output from column generator stage is passed to the lookup stage. The lookup stage will get the maximum key from Oracle table. The output from lookup stage is passed to the transformer where stage variables are used to add the maximum value from the lookup stage with sequence number from column generator stage. You will end up with surrogate keys.
HTH
--Rich
Pride comes before a fall
Humility comes before honour