Value of a generated key rolled over to 1

[bozenna]

I have a job that inserts rows into an Oracle table using an artificial key generated by function KeyMgtGetNextValueConcurrent. Recently the job aborted with 'duplicate key' errors because the key values rolled over to 1. The last value before rolling over was a 10 digit number.


What is the best way to handle this issue? I can not delete old records from the destination table. I considered using Oracle sequence instead, but this is not a quick fix.

I must mention that the table has large volume of data and that inserts to that table are done by mutiple processes.

[sumitgulati]

The immediate fix to the issue would be to use the sum of the last Key value and the sequence number. For example say the last value of the key was 9999999999 before the rollback to 1 happen.

Then your expression should be KeyMgtGetNextValueConcurrent('<Your sequence Name>')+9999999999.

Regards,
Sumit

[bozenna]

The immediate fix to the issue would be to use the sum of the last Key value and the sequence number. For example say the last value of the key was 9999999999 before the rollback to 1 happen.

Then your expression should be KeyMgtGetNextValueConcurrent('<Your sequence Name>')+9999999999.

Regards,
Sumit

Hi,
Thank you for your input. The problem is that with a key value that high, the function truncates the result. I do not know how to make it work with large numbers. Do you know?

Bozenna

[ray.wurlod]

As supplied the routine works with integers. Most join columns in star schemas use integer as their data type, so this is a reasonable approach.

If you want larger values than MAXINT (2,147,483,647) then you will need to adapt the routine. To avoid losing precision, make sure that your new routine includes a PRECISION declaration (see DataStage BASIC manual) and/or set the EXACTNUMERIC tuneable and/or use the "string math" function SADD().

[rasi]

Hi Bozenna

The quick and dirty way to fix your problem is to update your existing key column with -1.

SQL : Update table set key_col = key_col * -1

This will set all the records with the same number in negative value. Since you new key is rolled over to 1 it shouldn't be any problem.

Make sure that all the reference table which has this key column is updated.

Thanks
Siva

[bozenna]

Thanks so much. This is a very neat idea.
Bozenna

Hi Bozenna

The quick and dirty way to fix your problem is to update your existing key column with -1.

SQL : Update table set key_col = key_col * -1

This will set all the records with the same number in negative value. Since you new key is rolled over to 1 it shouldn't be any problem.

Make sure that all the reference table which has this key column is updated.

Thanks
Siva

[bozenna]

Thank you very much for the explanation and advoce. This might be a good long term solution. For now I have to use something 'quick and dirty' to resole production problem.

Cheers,
Bozenna

As supplied the routine works with integers. Most join columns in star schemas use integer as their data type, so this is a reasonable approach.

If you want larger values than MAXINT (2,147,483,647) then you will need to adapt the routine. To avoid losing precision, make sure that your new routine includes a PRECISION declaration (see DataStage BASIC manual) and/or set the EXACTNUMERIC tuneable and/or use the "string math" function SADD().

[ray.wurlod]

Did anyone reset your sequence, or change the name by which the sequence was being invoked?

I would expect any roll-around to start generating negative numbers.

Presumably the Oracle column is NUMBER(38), so the MAXINT value discussed above wouldn't be a problem.

For the quick and dirty solution, take a copy of the KeyMgtGetNextValue routine and change the line NextVal = NextVal + 1 to read

Code: Select all

NextVal = SADD(NextVal, 1)

Change the job design to use your new routine instead of KeyMgtGetNextValue