ucybdbun Error during reorganization of table 'OH' folder corrupted

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

After an issue with the removal of a folder and its subfolders, and having applied to steps in this Article the dbunload tool fails while performing the Reorg:

20230612/094256.724 - U00037107 Error during reorganization of table 'OH'. Program exits with error.
20230612/094256.726 - U00037022 ABORTING due to error. 

After enabling the traces, we can see the cause is related to the folder structure:

20230612/094256.724 - U00003592 UCUDB - Status: '' Native error: '2292' Msg: 'ORA-02292: integrity constraint (AUTOMIC.FK_OFS_OH_F) violated - child record found'
20230612/094256.724 - U00003594 UCUDB Ret: '2' opcode: 'EXEC' SQL Stmnt: 'delete from OH where OH_idnr in (select DIVDB_PK from DIVDB)'

After further investigation, we can see that the issue occurs while performing a delete from OH table where OH_IDNR has this characteristics:

insert into DIVDB (DIVDB_PK) select OH_idnr from OH where (OH_DeleteFlag = 1 or OH_Deleteflag = 5) and OH_OpenUserIdnr = 5 and rownum <= 1000

But since the delete is performed in bulk mode, we cannot obtain what OH_IDNR this is related to:

delete from OH where OH_idnr in (select DIVDB_PK from DIVDB)

In order to find the guilty record, we had to modify in the ini file the parameter chunk_size to 1:

reorg_Chunk_Size = 1

After launching again the REORG, this time we can retrieve the OH_IDNR with a simple query from DIVDB:

select * from divdb;

With this DIVDB_PK (that corresponds to the oh_idnr) we could then check in OH table what is the name of the folder and by looking into the table OFS find the subfolders related to it.

On this case, it was finally found and we then had an issue to delete the folder as it was being used by user UC/UC (5):

Environment

Release : 12.3.x 21.x

Component: Automation Engine

Cause

Corruption of the Folder Structure at database level.

Resolution

To delete the folder/s it is necessary to modify the OH_OPENUSERIDNR of the related OH_IDNR record and set it to empty, after that the folder/s could be deleted and the dbunload tool will not return any error.

In this case, this needs to be done on all impacted folders, not just the parent one.

Additional Information

If needed help to apply this procedure, please open a case with Technical Support referencing this article.