[AWA v12.1] JWP process is running in AE but shows as inactive in AWI.

book

Article ID: 185801

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine CA Automic Workload Automation - Automation Engine

Issue/Introduction

The JWP process is inactive in AWI, and when checking for the process itself on the AE server, it appears to be running.

However when using AWI, it is clear that the JWP is unresponsive. Any AWI function that relies on the JWP such as the process monitoring and the search functionality do not work.

When the JWP process is restarted, it is working temporarily with the message eventually appearing in the AWI as JWP is inactive. 

Cause

Looking at the JWP logs and traces, we can find that the JWP has been trying to delete records in the PMM* tables.

0200219/192235.144 - 35     U00003524 UCUDB: ===> Time critical DB call!       OPC: 'EXEC' time: '249895ms'
20200219/192235.144 - 35     U00003525 UCUDB: ===> 'DELETE FROM PMIAM WHERE PMIAM_PMMA_IDNR IN ( SELECT * FROM (SELECT PMMA_Idnr FROM PMMA WHERE PMMA_Timestamp <= ? ORDER BY PMMA_Idnr) WHERE ROWNUM <= 1000 )'
20200219/192552.607 - 35     U00003524 UCUDB: ===> Time critical DB call!       OPC: 'EXEC' time: '197463ms'
20200219/192552.607 - 35     U00003525 UCUDB: ===> 'DELETE FROM PMMA WHERE PMMA_Idnr IN ( SELECT * FROM (SELECT PMMA_Idnr FROM PMMA WHERE PMMA_Timestamp <= ? ORDER BY PMMA_Idnr) WHERE ROWNUM <= 1000 )'
20200219/192558.482 - 36     U00003524 UCUDB: ===> Time critical DB call!       OPC: 'EXEC' time: '459055ms'
20200219/192558.482 - 36     U00003525 UCUDB: ===> 'DELETE FROM PMMAV WHERE PMMAV_PMMA_Idnr IN ( SELECT * FROM (SELECT PMMA_Idnr FROM PMMA WHERE PMMA_Timestamp <= ? ORDER BY PMMA_Idnr) WHERE ROWNUM <= 1000 )'


The JWP dropping does indeed appear to be caused by the delete of the PM* table records.  These tables are used to keep track of performance within the system.  The JWPs automatically cleanup (delete) the PM* records - by default, they will delete 1000 records that are related to each other throughout the PM* tables (much like the AH table is the header table for all A* tables for statistics) and the JWPs will delete anything over 30 days.  These delete statements are taking too long and causing a bottleneck for other traffic (AWI traffic for example) that uses the JWPs.

Environment

Release : 12.1

Component : AUTOMATION ENGINE

Resolution

The ultimate fix to this issue will be to update to 12.2 or 12.3.  The reason is there were optimizations to the PM* tables and routines that were built into those two versions where there was too much risk to update the code in 12.1.

Workaround to stay in v12.1

Something you can do in 12.1 that should help is to add an index that is in 12.2 and above (your DBA should be able to add this):
CREATE INDEX NK_PMMA_Timestamp ON PMMA(PMMA_Timestamp DESC, PMMA_PMMD_Idnr) tablespace UC4_INDEX;
If problems after the index is added persists, please try the following: 

- Allocate more memory to the JWPs - this is something we'd recommend you do anyways. We recommend 2GB. 

- Add more JWPs to the system - this is something we'd recommend you do anyways. This will help spread the load across multiple JWPs so that if some are "stuck" on statements against the PM* tables, the others can pick up the load for the AWI.

Please see our sizing documentation to determine the correct amount:
https://docs.automic.com/documentation/webhelp/english/AWA/12.1/DOCU/12.1/AWA%20Guides/help.htm#Installation_Common/install_AWA_ARA_sys_requirements_sizing.htm?Highlight=JWP
- Lower the batch size that is deleted at one time. By default the JWP deletes 1000 records at a time. This number can be lowered and tuned to what is best for your system (a few different options may need to be tried, maybe start with 800). Lowering the number may cause the overall runtime for cleaning up the PM* tables to take longer as the load is spread out, lowering the amount of time the JWPs are busy running one statement, but increase the overall time the cleanup takes. To do this:
  1. Go to client 0
  2. Open UC_SYSTEM_SETTINGS
  3. Add the keyword: PERFORMANCE_CLEANUP_BATCH_SIZE
  4. Set the Value for the keyword to a lower number than 1000 (default) - as stated before, perhaps start with 800 or 900.