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

Document ID : KB000084342
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
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).

 
Environment:
OS: All
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.
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