Missing key values when using KeyMgtGetNextValue

preetiv · Post by **preetiv** » Sun Apr 01, 2012 10:17 pm

Hi

I have migrated a server job from Datastage 7.5 version to 8.5 version which uses KeyMgtGetNextValue for generating key sequence values. I noticed that there were some missing key values (there was a jump in sequence) when used in 8.5 which did not happen in 7.5.

Any idea why this can happen?

Thanks,
Preeti

chulett · Post by **chulett** » Mon Apr 02, 2012 7:06 am

Hard to say. It can depend on the job design... do you always use all values generated or are some assigned and then blocked by a constraint? Or did the job fail and roll back some rows?

ray.wurlod · Post by **ray.wurlod** » Mon Apr 02, 2012 3:03 pm

Or might another job be generating values for the same sequence name?

preetiv · Post by **preetiv** » Tue Apr 03, 2012 3:23 am

I have done more testing and found this.

Below is the job design:

Source file --- > Transformer(Has a reference from hash and writes the sequence generated to the hash if the natural key from source value does not exist) and another output link to output file. The sequence generation logic is written in both the output links(first to hash file and then to output file).

The sequence derivation is like this: If isNull(Sequence) then KeyMgtGetNextValue('A',Natural key) else Sequence

It works fine for all the values except when the natural key value is empty. In this case the sequence generated for writing to hash file is different from the one written to output file and there we got a jump.

The same design worked fine on 7.5 version, however, getting this issue in 8.5 version.

As a fix for now, we have calculated the value in stage variable and assigned them on both output links. However, still not sure how it worked in 7.5 and not in 8.5 version

chulett · Post by **chulett** » Tue Apr 03, 2012 5:27 am

That would be a question for your official support provider, me thinks.