Troubleshooting
Problem
Why would one receive a Database error number -803 , report the error to the owner of the deployment ?
Symptom
One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "20" constrains table "MAXIMO.ASSET" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.53.70
Cause
This sequence error when receiving rotated assets often happens because the sequence generator appears to be out of sequence for MAXIMO.ASSET
Resolving The Problem
1) Check if max(assetid) is greater than assetseq nextval:
You can check this by running the following 2 select statements:
select max(ASSETID) from MAXIMO.ASSET;
select (nextval for assetseq) from sysibm.sysdummy1;
2) If max(assetid) is greater than nextval for assetseq then increase the sequence value.
a) Alter the sequence. XXX is a number that is larger than a difference between max(ASSETID) and (nextval for assetseq).
alter sequence assetseq increment by XXX;
b. Select nextval. , keep the result.
select nextval for assetseq from sysibm.sysdummy1;
c. Select max(id). Make sure this value is smaller than the result of step b
select max(assetid) from maximo.asset;
d. Alter the sequence again.
alter sequence assetseq increment by 1;
e. Commit;
f. Try doing the same use case again and check whether the problem is fixed or not.
3) If max(assetid) is smaller than nextval,alter the sequence by doing the above in reverse.
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21641478