IBM Support

Duplicate index error on SQL Server only after a patch update

Troubleshooting


Problem

Cannot insert duplicate key row in object "X" with unique index 'Y" error showing after Maximo 7.1.x patch update is done (On SQL Server only)

Symptom

After a Maximo 7.1.x patch update on SQL Server you could get a duplicate index error showing

Cause

Often this is caused as the sequence generator appears to be out of sequence for the mentioned object's uid table.

Environment

SQL Server only

Diagnosing The Problem

As an example ,when testing a new Maximo 7.1.x update and it appears it is no longer possible to move an asset from location to location.
There are some error messages displayed, for example:

BMXAA0066E - Asset "X" in site "Y" was not moved successfully.
BMXAA4211E - Database error number 2601 has occurred when operating on ASSET "X" [SQL1\SQL1] Cannot insert duplicate key row in object 'dbo.asset' with unique index 'asset_ndx1'.

Resolving The Problem

It this example the sequence generator is out of sequence for the assetuid table.

To fix this on SQL Server with Maximo 7.1.x:

a) Get the highest current value for assetuid plus 1:

select max(assetuid)+1 from asset

b1) Update the sequence, use the number from the select for the ### value below:
update maxsequence set maxreserved = ### where tbname='ASSET' and name = 'assetuid'
b2) E.g. if the maximum number is 600, then the update statement is:

update maxsequence set maxreserved = 601 where tbname='ASSET' and name = 'ASSETID'
N.B!: It is necessary to restart Maximo after updating the sequence, so please restart from the Web Sphere console when it is convenient.

c) Test:Create a new asset and or move an asset from a location to another location.

[{"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Component":"Assets","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"7.1;7.1.1","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SSLKTY","label":"Maximo Asset Management for IT"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"7.1;7.1.1;7.1.2","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SSKTXT","label":"Tivoli Change and Configuration Management Database"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"},{"code":"PF027","label":"Solaris"},{"code":"PF010","label":"HP-UX"}],"Version":"7.1.1;7.1.2","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SS6HJK","label":"Tivoli Service Request Manager"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"","label":"Windows 2003 server"}],"Version":"7.1;7.1.2","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
17 June 2018

UID

swg21419788