Very bad performance deleting Change Monitor data (DB2)

book

Article ID: 210572

calendar_today

Updated On:

Products

CA Compliance Event Manager

Issue/Introduction

Running CEM V6.0 using DB2 as data repository.

A job has been implemented to delete data as described 
in the CEM manual in section "Maintain the Repositories (IBM DB2)"

Subsection "Delete Data from the Change Monitor Repository (IBM DB2)". 

The job performs very badly.

After running more than 11 hours and using more than 5 hours of CPU time the 
job has to be cancelled.

The DBA gives the reason for the long running job as the lack of indexes on the tables.

The delete process seems to scan three of the tables. 

What is the best process to define indexes on these tables to improve the runtime of this job?

Environment

Release : 6.0

Component : CA COMPLIANCE EVENT MANAGER

Resolution

1. Check the time range being specified in the CEMMDBCU job.

2. Run the jobs introduced in PTF SO05848.

Specifically the CEM0102N job.

This is a job that adds indexes to the monitor tables.