Loading RA WS REST Solution throws: ORA-00001: unique constraint on PK_OVW

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

When trying to load the RA Webservice REST solution using the ucybdbld utility, the load fails and the following is in the load log file:

20220419/184428.158 - U00038164 Loading RA Solution.
20220419/184428.173 - U00038257 Version check result: 
Version of the RA Solution: '4.6.1'. 
Version of database objects: '12.3.5+hf.2.build.1614598643055'.

20220419/184430.861 - U00038257 Version check result: 
Version of the RA Solution: '4.6.1'. 
Version of database objects: '12.3.5+hf.2.build.1614598643055'.

20220419/184430.861 - U00038149 Generating RA object 'WEBSERVICEREST'.
20220419/184430.877 - U00038144 Deleting existing RA Solution 'WEBSERVICEREST'.
...
20220419/184439.666 - U00038145 RA Solution 'WEBSERVICEREST' has been deleted successfully.
20220419/184439.728 - U00038167 Generating RA Agent 'WEBSERVICEREST'
20220419/184439.775 - U00038168 RA Agent 'WEBSERVICEREST' successfully loaded.
20220419/184439.775 - U00038151 RA object 'WEBSERVICEREST' successfully created.
20220419/184439.775 - U00038152 Generating RA Subcomponent 'REST'.
20220419/184439.822 - U00038156 RA Subcomponent 'REST' successfully generated.
20220419/184439.837 - U00038157 Generating RA connection 'RESTCONNECTION'.
20220419/184439.837 - U00038150 A database error occurred:  'OCIStmtExecute', 'ERROR   ', '', 'ORA-00001: unique constraint (AUTOMIC.PK_OVW) violated'.
20220419/184440.025 - U00038067 ABORTING due to error!  
 
Restart the program when cause of error has been corrected.

Environment

Release : 12.3

Component :

Cause

Running a database=4 trace shows the following delete statement being run which deletes the CONN.WEBSERVICEREST.RESTCONNECTION from UC_OBJECT_TEMPLATE in client 0:

20220420/142013.844 -  delete from OVW where dbms_lob.substr( OVW_Value1, 200, 1 ) = 'CONN.WEBSERVICEREST.RESTCONNECTION' and OVW_OH_Client = 0

This should delete the CONN.WEBSERVICEREST.RESTCONNECTION entry from the UC_OBJECT_TEMPLATE variable and it returns that it was successful.  The last thing the load does is run the statement:

20220420/142021.762 - INSERT INTO OVW (OVW_OH_Idnr, OVW_VValue, OVW_OH_Client, OVW_VRName, OVW_Value1) VALUES (114, 'CONN.WEBSERVICEREST.RESTCONNECTION', 0, 'FREI', 'CONN.WEBSERVICEREST.RESTCONNECTION')

which will insert the value back into the variables object.  This is where the load fails with the message:

20220420/142021.762 - U00038150 A database error occurred:  'OCIStmtExecute', 'ERROR   ', '', 'ORA-00001: unique constraint (AUTOMIC.PK_OVW) violated'.

This same message will occur if the template variable has the key already in it even if you run the statement outside of Automic. 

Resolution

Ultimately this issue was caused by having a UC_OBJECT_TEMPLATE variable objects in another client.  Check for other UC_OBJECT_TEMPLATE variables with the following statement:

select OH_NAME, OH_CLIENT from oh where oh_name = 'UC_OBJECT_TEMPLATE'

This should return the client that has the other UC_OBJECT_TEMPLATE