Accessing Sequences in Tranformer stage

[Murali4u]

Hi everyone,
I have one requirement.. I have created a sequence command in DB2 and I need to generate a running sequence no using the sequence option and to call it in transformer stage. Can anybody say how to pass the query and do the exercise.

Note:
I'm aware of system variables and how to use the stage variables to handle the sequence no.

[chulett]

Lookup.

[chulett]

OK... I deleted the private message I got asking for clarification on this so I don't recall exactly what you needed to know or were unsure about. If you still need some help, post back here with whatever questions you have and we'll see about getting you some answers.

[DSguru2B]

Lookup is one option where the db2 stage will refer to you sequence obj and the sql will say something like

Code: Select all

SELECT NEXT VALUE FOR SeqObjName, 'X' FROM SYSIBM.SYSDUMMY1;

where 'X' is your dummy key.

Or if your source is a db2 table then you can add an extra column called "SN" and put the following in the derivation

Code: Select all

(NEXT VALUE FOR SeqObjName) AS SN

The latter option is much more efficient.

[chulett]

Hmmm... shouldn't need a dummy or any kind of key at all, in fact. And that works in the derivation? Interesting as I've always fallen back on 'user-defined' sql without mentioning that column in the stage and then added something akin to that to whatever the stage generated.