Hi,

I am currently doing an analysis regarding Optim purge job prod latency issue. This job purges records from the operational tables. This purge job is running on a regular daily basis which is taking more than 30 minutes to execute. 

We've already identified the specific table (TBLE_MSG) which is taking time to execute. The Prod optim primary key setup and DB2 index setup on that TBLE_MSG table is also having same setup in Dev.

Now, what I want is to replay the prod latency issue in Dev region, I did the below steps:

1) Extract prod table data and then load it to dev region.

2) I executed the archival job (extracts data from operational tables and restore it in archival tables)

3) I executed the purge job to delete the records from the operational tables.

From the reference data in Prod, the purge job ran for 55mins to delete 256k+ records. While from the replay in Dev, it ran only for 3 mins to delete 272k+ records.

My main concern and question is: Why is it not taking time to run in Dev region given that it has the same setup as of prod?

Looking forward on your insights. Thank you!

Are your logging files set up the same in both environments?  Not usually.  Perhaps the prod log files are being shared by other applications in prod?  Are you issuing commits the same in each environment?  

just some ideas to check. 

Cheers,

Fred Booker 

Hi Fred,

Thanks for your response. May I ask what "logging files" are you referring to?

Yes we're issuing commits both in Prod and Dev. We have this parameter in the DEFERRED_DELETE processing utility.

DEFERRED_DELETE

   ARCHIVE_NAME xxxxx 

   CONTROL_FILE (xxxxx) 

   DELETE_COMMIT_ROWS 5000 

   COMPARE_ROWS YES

   DEFAULT_KEY_LIMIT 1

   ACCESS_METHOD(TBLE_PRNT,K,99)

   ACCESS_METHOD(TBLE_MSG,K,99)

   DAA_TABLE(TBLE_PRNT, NO)

   DAA_TABLE(TBLE_MSG, YES)