DSXchange

I am trying to create a DB2 commit incremental value of 2000. Every 2000 records processed, I write a commit transaction to a temp file. I tried the @INROWNUM, Stage Variables and simple Var + 1 mechanism only to be shut out due to partitioning. Every time a new partition is hit, the @INROWNUM, Stage Variables get set back to their default and start over. This defeats the 2000 row commit process.

Is there a way to process records adding 1 to the increment up to 2000, and then reset to start over at 1 with being forced by partitioning. I looked at the Surrogate key generator and searched throughout the forum to no avail. As I stated above, I created a Stage variable, incremented it by one but it gets reset to it's initial value when a record is written to another partition.

You have to take into consideration partition number and number of partitions when dealing with @INROWNUM in px jobs. Refer to this post for a better understanding.

Yes I understand this. My problem is that due to the Stage variable being reset by partition, I can't keep an incremental value from 1 through ~. I am looking for a possible solution on this. I assume there must be a way to increment a value from start to end record of a file.

More information from my inquiry. I was wrong in my explanation. After reviewing the partitions, I realized that the Stage variable are not getting reset, they are starting at 1 for each partition. After discussing this further with a DB2 expert, it will be very difficult to implement a restart mechanism.

I am going to mark this as resolved and move forward.

Hopefully in the future, IBM will have an easier solution for this issue.

How about setting the APT_RDBMS_COMMIT_ROWS environment variable?