CA Workload Automation DE reports fail with exit code 143

Document ID : KB000071566
Last Modified Date : 24/05/2018
Show Technical Document Details
Issue:
Some reports may fail to run or complete with 'out of memory' errors and exit code 143.  You may increase the reporting process memory (see screenshot).  However, if the reports continue to fail, then you will need to check the DB.

Reporting memory
Environment:
CA Workload Automation DE R12
Oracle 11g or 12c
MS-SQL 2008/2012
PostgreSQL 9.x
Cause:
Only some reports may fail.  Smaller reports like 'SECURITY' may complete.  
The reports that access large datasets may fail, e.g. 'FALED_JOBS'.  This is because the report accesses historical and archive tables.  They may have grown to several million records.  The database will take a very long time to complete the request and cause the process to fail.
Resolution:
The archive tables like H_GENERIC_JOB and H_APPLICATION can grow overtime.  If you have implemented MOVEHISTORYDATA in housekeeping, it will move the data from historical tables like ESP_GENERIC_JOB and ESP_APPLICATION to corresponding archive tables. 

If the row count in H_ tables is in several millions (over 10-20 million), then you may need to add indexes to the archive tables.  Here is an example of adding to H_GENERIC_JOB for START_DATE_TIIME field.

Oracle
CREATE INDEX DESCHEMA.IDX_H_GNRC_START_DATE ON DESCHEMA.H_GENERIC_JOB
("START_DATE_TIME" DESC)
NOLOGGING
TABLESPACE DE_TABLESPACE
PARALLEL 4;
ALTER INDEX DESCHEMA.IDX_H_GNRC_START_DATE LOGGING NOPARALLEL;
MS-SQL
CREATE NONCLUSTERED INDEX IX_H_GNRC_START_DATE ON DEUSER.H_GENERIC_JOB (START_DATE_TIME);

PostgreSQL
CREATE INDEX CONCURRENTLY idx_h_gnrc_start_date ON H_GENERIC_JOB (START_DATE_TIME);

Note: The above SQL commands are examples and for reference only.  It is recommended to contact your DBA for more information on how to create the indexes. CA recommends to take full backup of database before making any changes to it.  DE must also be shutdown to prevent any failures.