Transformer Stage surrogate key generation

[adi_2_chaos]

Hi All,

I'm trying to create a Surrogate key using a Transformer stage. I used the DBSequence type Sequence, and gave all the Oracle Db connection details. In the Database, I created the Database Sequence which I am planning to use. However, I'm unable to understand how to use the sequence value in the Transformer's Derivation Area to be able to generate Output derivation. Can someone kindly guide me on this.

Thanks & Regards,
Sriram

[jwiles]

The Parallel Job Developer Guide quite clearly describes how to setup the Surrogate Key tab in the Stage properties page of a Transformer stage to access a database sequence.

It also mentions using the NextSurrogateKey() function in a derivation to retrieve the keys.

Regards,

[chulett]

From what I recall, it also spells out that the Sequence must be created by a DataStage job rather than manually. Yes?

[madsongtel]

From transformer stage you can generate surrogate keys.

First create surrogate key file by using surrogate key stage and use that .sk file in transformer surrogatekey tab file option. give path and file name which you have initialized, in output link assign nextsurrogatekey() then it will generate unique keys.

and all the keys which is generated all store in that file, so at any time it wont take the key which is already created.

[jwiles]

madsongtel, the OP's post stated that he is attempting to use a Database Sequence as his key source, not a SK state file.

Craig, I believe you're right. The 8.0 docs on the SKG stage don't say specifically, but the 8.5 docs do say so, that the Database Sequence must be created by the Surrogate Key stage and that you cannot use a sequence previously generated outside of DataStage.

adi_2_chaos, you will need to create your Database sequence using the instructions listed in the Parallel Job Developer Guide under the Surrogate Key Generator section.

Regards,

[adi_2_chaos]

Thanks All,

Got the solution