Issue:
Different revisions of one record shares the same revision number.
Cause:
The Database (DB) index evRecElecUri on table TSERECVSN is non-unique, hence it allows the values for both columns evRecElecUri and evVersionNbr between two or more rows are the same, which can result in the same record (value of evRecElecUri are the same ) to have two or more revisions sharing the same revision ID (evVersionNbr are the same too). Due to the index evRecElecUri being non-unique if anything unexpected occurs that potentially can result in the TSERECVSN table being updated twice when a revision is added it could result in duplicated revision numbers being added for different revisions.
Solution :
**Please test this in a Test environment before taking any action against your PROD DB**
1. Take back up for table TSERECVSN before taking any further action.
For SQL Sever
select * into TSERECVSN_BAK from TSERECVSN;
For Oracle
create TABLE TSERECVSN_BAK AS select * from TSERECVSN;
2. Identify the duplicated rows, and have the duplicated rows removed, execute the SQL below against the DB, it will find the duplicated revisions.
SELECT uri, evRecElecUri, evVersionNbr FROM TSERECVSN MAIN WHERE EXISTS ( SELECT * FROM TSERECVSN SUB WHERE SUB. evRecElecUri = MAIN.evRecElecUri AND SUB.evVersionNbr = MAIN.evVersionNbr AND SUB.uri <> MAIN.uri) ORDER BY evRecElecUri DESC;
The following is an example of the output from the SQL :
3. Use the SQL below to delete the duplicated rows.
DELETE FROM TSERECVSN WHERE URI IN (XXXX,XXXXX,XXXXX…..);
//***XXXX are the uris for the duplicated rows that needs to be removed ***)
The following is an example of the SQL to clean up the duplicates shown above. As a rule of thumb delete the older (lower) uri’s of the output
DELETE FROM TSERECVSN WHERE URI IN (293541,291241,291524,292812,293108,292309,289608,288732,284463);
4. Execute the SQL below to confirm there is no longer any duplicated revisions (the SQL should return 0 row).
SELECT uri, evRecElecUri, evVersionNbr FROM TSERECVSN MAIN WHERE EXISTS ( SELECT * FROM TSERECVSN SUB WHERE SUB. evRecElecUri = MAIN.evRecElecUri AND SUB.evVersionNbr = MAIN.evVersionNbr AND SUB.uri <> MAIN.uri) ORDER BY evRecElecUri DESC;
5. Execute the SQL below to drop the Non-Unique index evRecElecUri on table TSERECVSN
For SQL Sever
DROP INDEX [evRecElecUri] ON [dbo].[TSERECVSN];
For Oracle
DROP INDEX evRecElecUri;
6. Use the SQL below to create the index evRecElecUri on table TSERECVSN as Unique
For SQL Sever
CREATE UNIQUE NONCLUSTERED INDEX [evRecElecUri] ON [dbo].[TSERECVSN]
([evRecElecUri] ASC,
[evVersionNbr] DESC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
For Oracle
CREATE UNIQUE INDEX EVRECELECURI ON TSERECVSN (EVRECELECURI ASC, EVVERSIONNBR DESC);
7. Check again via HPE RM/CM Client, and the duplicated revisions should no longer appear
