dSeries applications running slow, jobs stuck in PREDWAIT

Products

• CA Workload Automation DE - Scheduler (dSeries)

Issue/Introduction

Users may observer a delay in job processing, i.e jobs going from PREDWAIT to READY state.

Environment

Release : 12.x

Component : CA WORKLOAD AUTOMATION DE (DSERIES)

Cause

The database is taking a long time to complete large running queries.  The DBA may notice high CPU and memory usage.  This will result in dSeries (DE) to take a long time move jobs states.

Resolution

Before doing any DB changes, make sure to run housekeeping jobs daily.  Use MOVEHISTORYDATA and PURGECOMPLETEDJOBS to clear historical and active tables.

DBA can run AWR or SQL Analyzer report to see what exact queries are consuming excessive resources.

The following is a common query used by DE server to gather job states.

WHERE wob.job_name = :1 AND wob.job_qualifier = :2 AND appl.appl_name = :3 AND appl.scheduled_date_time >= :4 AND appl.scheduled_date_time < :5 AND wob.appl_id = appl.appl_id AND wob.wob_type <> 'EXT' AND wob.wob_type <> 'EXTMON' AND wob.state in ('COMPLETE', 'BYPASSED') 

 

Additional indexes may be setup on ESP_APPLICATION tables.  E.g.

ESP_APPLICATION("APPL_NAME","SCHEDULED_DATE_TIME","APPL_ID","APPL_GEN_NO");

Note: DBA may also create a functional or concatenated index for this WHERE statement on ESP_GENERIC_JOB (wob) and ESP_APPLICATION (appl).