Migrating CLM 6.0.1 Global Configuration application from Derby to DB2 fails with Indices issues
I've migrated all installed CLM apps to use DB2 instead of Derby. All goes well apart from the Global Configuration application. When I attempt to import the GCData.tar file, created from the Derby database into the new GC DB2 database, I get the following errors :
2016-04-12 10:12:08,424 CRJAZ0577E The following SQL query did not execute properly on the server:
CREATE UNIQUE INDEX GC.DBTRSCHNGVNTTRSRDR ON GC.DB_TRS_CHANGE_EVENT(TRS_ORDER)
com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-603, SQLSTATE=23515, SQLERRMC=null, DRIVER=4.17.28
.
.
CREATE UNIQUE INDEX GC.DBTRSCHNGVNTTRSRDR ON GC.DB_TRS_CHANGE_EVENT(TRS_ORDER)
com.ibm.team.repository.service.internal.db.util.JdbcDB$2: CRJAZ0577E The following SQL query did not execute properly on the server:
CREATE UNIQUE INDEX GC.DBTRSCHNGVNTTRSRDR ON GC.DB_TRS_CHANGE_EVENT(TRS_ORDER)
.
.
2016-04-12 10:12:10,007 CRJAZ0651I Error rebuilding index.
com.ibm.team.repository.common.TeamRepositoryException: CRJAZ0530I Exception occurred creating indices for the schema "GC".
My next thought was to rebuild the indices for the GC database, received following error:
CRJAZ0577E The following SQL query did not execute properly on the server:
CREATE UNIQUE INDEX GC.DBTRSCHNGVNTTRSRDR ON GC.DB_TRS_CHANGE_EVENT(TRS_ORDER)
[2694dd17] CRJAZ0577E The following SQL query did not execute properly on the server:
CREATE UNIQUE INDEX GC.DBTRSCHNGVNTTRSRDR ON GC.DB_TRS_CHANGE_EVENT(TRS_ORDER)
CRJAZ1217E The indices could not be rebuilt for the following database: "//localhost:50000/GC:u
ser=xxxxxxxx;password=xxxxxxxx;"
Using CLM 6.0.1, DB2 10.5
I've seen this error has cropped in when migrating to CLM 6.0, but this is a stand alone CLM 6.0.1 installation.
2 answers
The error means that there are duplicate entries on GC.DB_TRS_CHANGE_EVENT(TRS_ORDER), hence the CREATE UNIQUE INDEX will always fail.
If you know what you're doing, you can go in the original Derby database, remove the duplicate entries, export the data, and import again.
You'd better contact Support though, as there appears to be a defect as the duplicate entries should not be there in the first place.
If you know what you're doing, you can go in the original Derby database, remove the duplicate entries, export the data, and import again.
You'd better contact Support though, as there appears to be a defect as the duplicate entries should not be there in the first place.
Hi Stephen,
This is a known problem and will be resolved in CLM 6.0.2.
reptools-gc import from Derby to another supported Vendor fails with SqlIntegrityConstraintViolationException creating unique index on TRS_ORDER (381556)
If you don't want to set up GC from scratch with the DB2 as the database vendor, you can apply the following workaround:
1. Stop the GC server.
2. BACKUP GC DB in DB2.
3. RUN the SQL query
select * from GC.DB_TRS_CHANGE_EVENT
4. This should display (amongst others) a row with a Modified Date same as the date of running repotools-gc import command.
There should be only one such row.
5. Delete row that was created as the same time and date as the IMPORT command.
6. Repeat steps 3-5 for GC.DB_TRS_BASE_PAGE table.
7. Rebuild the indices for GC.
This is a known problem and will be resolved in CLM 6.0.2.
reptools-gc import from Derby to another supported Vendor fails with SqlIntegrityConstraintViolationException creating unique index on TRS_ORDER (381556)
If you don't want to set up GC from scratch with the DB2 as the database vendor, you can apply the following workaround:
1. Stop the GC server.
2. BACKUP GC DB in DB2.
3. RUN the SQL query
select * from GC.DB_TRS_CHANGE_EVENT
4. This should display (amongst others) a row with a Modified Date same as the date of running repotools-gc import command.
There should be only one such row.
5. Delete row that was created as the same time and date as the IMPORT command.
6. Repeat steps 3-5 for GC.DB_TRS_BASE_PAGE table.
7. Rebuild the indices for GC.