how to generate a sequence number in transformer

[praveenk]

Hi,

I'm working on datastage 7.1PX, can anyone explain me about generating a unique sequence number using transformer stage..

ThankX alot in advance
praveen

[chulett]

Welcome.

Just for grins, why the "in transformer" part of your question? Why not use the stage that exists just to generate sequence numbers - i.e. the Surrogate Key Generator stage?

[chulett]

You'll find several versions of the formula here if you do an exact search for NUMPARTITIONS in the forums, which should help answer your question. This one allegedly works fine.

[nayanpatra]

If the surrogate key stage is not available to you, you may take the help of stage variables present in the transformer stage.

SV + 1 : SV (Initialize it to 0)

Move this SV into your surrogate key column.

So, when the first record comes the SV is set to 0. After executing the above statement, the variable will hold the value 1.

For second record, SV is set to 1. After executing the above statement, the variable will hold the value 2 and so on.

This design may work if it is a one-time run. But to incorporate it in some daily job run, you may need to take care where to initialize this variable. At the very sequence level you will have to fetch the next surrogate key value and pass it to the job. You may write a routine for this purpose. Say you have stored this value in the job parameter NSK. Now you need to add another job parameter as

NSK + SV : SV1

Now move this SV1 in the surrogate key column.

Hope this helps you solve the problem. Let me know the outcome.

[ray.wurlod]

The outcome will be a consecutive sequence of the same numbers on every node in the configuration. No uniqueness.
Have the sequence start with the partition number (possibly with a constant offset) and be incremented by the (constant) partition count.

[mgendy]

the Surrogate Key will do the same behavior if it is operat in parallel , to resolve that you should use either the Surrogate Key or the Tansformer in sequential mode

[praveenk]

I have a requirement of implementing scd type2 where i want to insert the previous record and the updated record, in which i dont want to disable constraints, so for this i want to make use of composite primarykey for the key column and uniue sequence number.

pls guide me

[chulett]

the Surrogate Key will do the same behavior if it is operat in parallel , to resolve that you should use either the Surrogate Key or the Tansformer in sequential mode

If you are saying that the Surrogate Key Generator stage will "do the same" if not run sequential, as in not produce unique numbers across partitions, that would be incorrect. Unless that's a failing of the early 7.1 version of it, hard to remember that far back.

[chulett]

I have a requirement of implementing scd type2 where i want to insert the previous record and the updated record, in which i dont want to disable constraints, so for this i want to make use of composite primarykey for the key column and uniue sequence number.

I don't see how any of that precludes you from using the Surrogate Key Generator stage for the "unique sequence number" part. Regardless, there are already of couple of posts here on how to generate one in a transformer, what other quidance do you need?

[mgendy]

the Surrogate Key will do the same behavior if it is operat in parallel , to resolve that you should use either the Surrogate Key or the Tansformer in sequential mode

If you are saying that the Surrogate Key Generator stage will "do the same" if not run sequential, as in not produce unique numbers across partitions, that would be incorrect. Unless that's a failing of the early 7.1 version of it, hard to remember that far back.

Exactly thats what i mean , i tried it before and get that result may be for srot or partitioning problem , but that was the result

[srinivas.nettalam]

you can use the formula below

@PARTITIONNUM + ((@INROWNUM-1) * @NUMPARTITION) +1

map this to your target column