Staggered Sequential Key

[Raftsman]

When I generate a unique partitioned key using

@PARTITIONNUM + (@NUMPARTITIONS * (@INROWNUM - 1)) + 1 + MAXEVNTID

Where MAXEVNTID = max value from the last job execution,

The DB2 table sequentially increment the key by 1 on different partitions.

So far so good.

When the key sequence number hit 100, it begins to jump by 2, sometimes 4 etc. It no longer increase by one. Is this normal. I have 111 records and I would of thought that my key would of been 1 thru 111. It goes from 1 to 100 and then staggers up to 132.

Could someone please explain why.

Thanks

[ray.wurlod]

Why not do some of the research yourself? Capture the individual values of the system variables into additional columns (maybe an additional output link from the Transformer stage) into a text file and see where the cause is.
Let us know.

[vmcburney]

In your DataStage Director open up the job monitor, from a right mouse click display all stage instances, what you should see in your row counts is that one instance of the Transformer processed about 50 rows and the other instance processed 66 rows. That gave Transformer instance 1 the odd numbers 1-99 and Transformer instance 2 even numbers 2-132.

You cannot get a robust sequence without gaps from a parallel counter unless you perfectly or almost perfectly balance the load across all instances.

[Raftsman]

It was the load balancing the staggers the numbers. I have 4 node processing 111 records, Partition 0,1,2,3. Part 0 contained 1-27, Part 1 contained 1-26, part 2 contained 1-26 and part 3 contained 1-32. Using the formula to assign keys staggers the counts by 4. So the last partition 3 contained numbers from 100 - 132 due to the extra records in the partition.

Thanbks for the load bearing answer, it is reacting ass it should. Also Ray, thanks for telling me to insert debug variable, I understand the logic much better