PWP freezes due to SQL SELECT statement querying AH table containing approximately 130 million records

book

Article ID: 84342

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine AUTOMIC WORKLOAD AUTOMATION

Issue/Introduction

Error Message :
20171121/193221.521 - U00003434 Server routine 'JPAKTJ_R/AKTX' required '89' minutes and '30,767' seconds for processing

The Primary Worker Process (PWP) freezes due to a SQL SELECT statement that is querying the AH table that contains approximately 130 million records.

Since the statement takes so long to run due to the high number of records in the AH table, it blocks processing during that time and causes the PWP to freeze. 

Messages similar to the one above are written in the log file indicating how long it took to run (in this example it was 89 minutes).

 

Cause

Cause type:
Defect
Root Cause: The following SQL statement is executed when restarting an already deactivated job:
SELECT COUNT(*) FROM AH WHERE AH_ParentHir = ?
This query is not needed and can degrade the systems performance when there are a lot of statistics records.

Environment

OS: All

Resolution

Update to a fix version listed below or a newer version if available where the SQL query has been removed.
 

Fix Status: Released

Fix Version(s):
Component(s): Automation Engine, DB

Automation Engine 12.1.1 - Available
Automation Engine 12.1.0 HF4 - Available

Additional Information

Workaround :
N/A