Error while using the Automic Utility DB Load

Document ID : KB000091806
Last Modified Date : 23/04/2018
Show Technical Document Details
Issue:
Automic Utility DB Load in V12.1 aborts with a unique constraint error:

U00003592 UCUDB - Status: '23505' Native error: '-803' Msg: ' SQL0803N  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 "11" constrains table "AAREPREF.OH" from having duplicate values for the index key.  SQLSTATE=23505'

 
Cause:
The problem is that the MLS_IA changed it's IDNR from 50005 to 50026. This will be fixed back to 50005 in a future release

To investigate the issue please set up a DB=4 trace on the Automic Utility DB Load. In the output of the trace file, you might find the following information.

This is an excerpt from a db=4 trace from the utilities:
00000000  4D4C535F 4941                        >MLS_IA<
20180417/151447.882 - U00009909 TRACE: (BINDPAR:  OH_Idnr           )                                          9001000a6505380 000004
                                00000000  0000C36A                             >..â–’j<
20180417/151447.882 -                                        >50026<
                                00000000  496E7465 6C6C6967 656E7420 4175746F  >Intelligent Auto<
                                00000000  31312E32                             >11.2<
20180417/151447.883 - INSERT INTO OH (OH_Client, OH_OType, OH_Name, OH_Idnr, OH_Title, OH_CrUserIdnr, OH_CrDate, OH_ModUserIdnr, OH_ModDate, OH_ModCnt, OH_LastUserIdnr, OH_LastCnt, OH_JPP_Count, OH_C0Ref, OH_NoStatUpd, OH_DeleteFlag, OH_ExpFlag, OH_MaxWait, OH_MaxParallel2, OH_MPElse, OH_OpenUserIdnr, OH_MaxRetCode, OH_MrtMethod, OH_Mrt, OH_MrtErt, OH_Frt, OH_Ert, OH_ErtCnt, OH_ErtCorr, OH_ErtIgn, OH_ErtMinCnt, OH_ErtMethod, OH_ErtTrend, OH_Srt, OH_SrtErt, OH_FcstStatus, OH_UC4Priority, OH_RefIdnr, OH_OX_ContentLen, OH_OpenSession, OH_OV_Flag, OH_OV_Mode, OH_OPU_Flag, OH_OEO_Flag, OH_IsTemplate, OH_RollbackFlag, OH_Inactive, OH_DeactDelay, OH_AEVersion) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
20180417/151447.884 - U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: 100000000  DB-HDBC: 100000000
20180417/151447.884 - U00003591 UCUDB - DB error info: OPC: 'SQLExecDirect' Return code: 'ERROR'
20180417/151447.884 - U00003592 UCUDB - Status: '23505' Native error: '-803' Msg: ' SQL0803N  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 "11" constrains table "AAREPREF.OH" from having duplicate values for the index key.  SQLSTATE=23505'
20180417/151447.884 - U00003594 UCUDB Ret: '2' opcode: 'INSR' SQL Stmnt: 'INSERT INTO OH (OH_Client, OH_OType, OH_Name, OH_Idnr, OH_Title, OH_CrUserIdnr, OH_CrDate, OH_ModUserIdnr, OH_ModDate, OH_ModCnt, OH_LastUserIdnr, OH_LastCnt, OH_JPP_Count, OH_C0Ref, OH_NoStatUpd, OH_DeleteFlag, OH_ExpFlag, OH_MaxWait, OH_MaxParallel2, OH_MPElse, OH_OpenUserIdnr, OH_MaxRetCode, OH_MrtMethod, OH_Mrt, OH_MrtErt, OH_Frt, OH_Ert, OH_ErtCnt, OH_ErtCorr, OH_ErtIgn, OH_ErtMinCnt, OH_ErtMethod, OH_ErtTrend, OH_Srt, OH_SrtErt, OH_FcstStatus, OH_U
20180417/151451.265 - U00000002 EXISTS
Resolution:
As a workaround you can perform the following steps:
  • Restore the database
  • Before you execute the dbload again, please be so kind and get in touch with support. As a reference, you can use this article. Support will then provide you some delete statements that has to be executed beforehand.