dSeries applications running slow, jobs stuck in PREDWAIT

book

Article ID: 211521

calendar_today

Updated On:

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.

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.

Environment

Release : 12.x

Component : CA WORKLOAD AUTOMATION DE (DSERIES)

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).