Troubleshooting
Problem
When upgrading a Maximo database from 7.1 to 7.5, the upgrade can fail in ConfigDB with the error: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot create more than one clustered index on table 'workorder'. Drop the existing clustered index 'wo_rptdt_idx' before creating another.
Symptom
Upgrade process stops prematurely. Failure occurs in ConfigDB.
Cause
An UpdateDB script creates a new, clustered index on an existing table. The table already has a clustered index. Because a table is physically arranged according to the clustered index, if one exists, a table cannot have more than one clustered index.
Environment
SQL Server
Diagnosing The Problem
Execute this query. Scan the clusterrule column for a value other than 0:
Select o.name, i.name, c.name, k.keyno, 'unique'=i.status & 2, 'clustered'=i.status & 16
From dbo.sysindexes i, sysobjects o, sysindexkeys k, syscolumns c
Where o.type = 'U' And user_name(o.uid) = 'dbo' And i.id = o.id And i.indid > 0 And i.indid < 255
And k.id = o.id And k.indid = i.indid And k.colid = c.colid And c.id = o.id And INDEXPROPERTY(o.id,i.name,'IsStatistics') != 1
And upper(o.name) = '<tablename>' Order By 2, 4
Resolving The Problem
Restore the database to pre-UpdateDB. Drop the existing clustered index, then re-create the index as non-clustered. Run UpdateDB over again from the beginning.
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21654691