Error while using the Automic Utility DB Load
Article ID: 91806
Updated On:
Products
CA Automic Workload Automation - Automation Engine
Issue/Introduction
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'
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'
Environment
Release: AUTWAB99000-12.1-Automic Workload Automation-Base Edition
Component:
Component:
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
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.
Feedback
Yes
No
Powered by
