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.
Value of a generated key rolled over to 1
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
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
Then your expression should be KeyMgtGetNextValueConcurrent('<Your sequence Name>')+9999999999.
Regards,
Sumit
Hi,sumitgulati wrote: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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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().
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().
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
Thanks so much. This is a very neat idea.
Bozenna
Bozenna
rasi wrote: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
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
Cheers,
Bozenna
ray.wurlod wrote: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().
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
Change the job design to use your new routine instead of KeyMgtGetNextValue
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.