Generating a sequence number in IBM datastage

Question

I have a request to generate a sequence number and it should be done in the following manner

1. Retrieve the initial value from a seq tracker table

2. Use that value as sequence number and add it as column in another parallel process while it gets updated.

3. After job is done, the max value should be updated in the tracker table so that it should start from that number in the next run

Example

Initial value in tracker table: 1

The data stored should be as

A 1 B 2 C 3

3 will be stored in tracker table now, so initial value is 3 now

Next job run

A 4 B 5 C 6

and so on

I am new to datastage so i have tried using inrownum and partition formula but i don't have idea about how to go with this.

Answer 1

You could use a SEQUENCE (object) in Db2 instead of a table - Sequences in Db2 are ment to generate unique numbers. A table is also possible of course.

In Datastage do a lookup against your table or use a surrogate key generator stage in which you refer to the Db2 sequence. The lookup needs to be defined on a artificial column which is always true to enrich your data. Use a transformer to modify the counter retrieved by adding +1 for every row and write it to the table on the last row by using an update if you chose a table or do an alter sequence in Db2 to set the new value.

Note that there should be a +1 before working with the value or before writing it out again in order to avoid to use the last number of the previous run as the first value of the following run.