Stage Variables for Counter Not working in 8.7

[mdbatra]

Hi,

I have a job that generates sequences using stage variables based on change in key field value. We have upgraded from 8.1 to 8.7. The job works perfectly in 8.1, however in 8.7 the job is generating duplicate sequences. Has anyone come across a similar scenario? Do stage variables/their initialization work differently in 8.7?

[SURA]

Share the code. To my knowledge there should not be any chage due to versions.

[mdbatra]

Description -
The job is doing a CDC with the target table. (keys - Parent_id and child_name).
For every new parent entry - child_id is generated from 1.
For every existing parent and new child, max(child_id) is fetched from the target table lookup(key - parent_id) and child_id is generated from max(child_id) +1.
For change_code=3 records, child_id is populated with child_id fetched from target table lookup(key - parent_id,child_name).

Code: Select all

sv1= IF ISNULL(input_link.MAX_CHILD_ID) THEN 1 ELSE (if input_link.change_code <>1 then input_link.MAX_CHILD_ID else input_link.MAX_CHILD_ID+1)
sv2= IF input_link.PARENT_ID= sv3 THEN (if input_link.change_code=1 then sv2+1 else sv2) ELSE sv1
sv3= input_link.PARENT_ID

Initial values of Stage Variables -
sv1 =0
sv3=0

Target Link
Child_ID= if input_link.change_code=3 then input_link.CHILD_ID else sv2

Thanks

[ray.wurlod]

Are you running in sequential mode in 8.1 and two node parallel in 8.7?

What expressions do you use to generate your counter?

[ArndW]

My first thought is that if you get duplicate sequences you might have more than a 1-node configuration running now. Could the version change also have inadvertantly change your configuration?

[mdbatra]

This one was an issue with incorrect partitioning, not sure how it was working on the old version though.

Thanks all for your responses.