AWA: db unload hangs when deleting RT records

Products

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

Issue/Introduction

The DB Unload tool (ucybdbun) hangs when deleting records from RT Table. 
The following can be seen in the log:

tail -n 4  ucybdbun_log_00.txt

20200402/010254.233 - U00037111 Starting reorganization for client: '3100'
20200402/010255.392 - U00011667 Count of records in 'MELD' = '2832'
20200402/010255.392 - U00037110 Delete records from table 'MELD' / progress '100'%
20200402/010255.392 - U00037172 Finished cleanup of Table Meld
<----- no further content in log --->

When setting the database=4 trace in ucybdbun.ini as follows:

[TRACE]
database = 4

 

We can find that the SQL statement that hangs is the delete from RT statement ( cleanup of the table RT) :

tail -n 1 ucybdbun_trace_00.txt

20200402/010255.458 - delete from RT where RT_AH_Idnr in (select DIVDB_PK from DIVDB)
<----- no further content in log --->

Environment

Release : 12.2 and superior
Component : AUTOMATION ENGINE
Database: Oracle

Cause

A single report has billions of rows within the RT Table. Database is not capable to delete this report with a single commit.
Hence, it is necessary to manually delete these entries instead of using the DB Unload utility.

Resolution

To fix the issue, it is necessary to delete the RT problematic entries in 200k chunks.

WARNING: Test first the procedure in a Sandbox / Test environment if possible not during business hours. The Oracle processes CPU Usage may spike for a while during Step 2 and take several hours to finish impacting the Automation Engine performances.


To do so, the general overview of the procedure is the following:

1.) Identify RT_AH_idnr responsible for the unload utility hang by using one of these methods:

a. Via the query:

select distinct RH_AH_idnr from RH where RH_DeleteFlag = 1 and rownum <= 1

b. Identifying large reports which potentially can block the delete statement:

select count(*), RT_AH_idnr from RT group by RT_AH_idnr order by 1 desc;

Note the RT_AH_idnr of the "guilty" report and use it on Step 2

2.) Ask your DBAs to delete the Guilty Report manually in 200k or 400k chunks.