DELETESTAGINGDATA deleting unintended data in dSeries (DE)
Article ID: 208903
Updated On:
Products
Issue/Introduction
Customer ran the following command:
"deletestatingdata olderthan('now less 22 months')"
When checking the H_ tables, they discovered data only goes back to few months instead of at least a year.
Environment
Release : 12.2
Component : CA WORKLOAD AUTOMATION DE (DSERIES)
Cause
DELETESTAGINGDATA will delete data older than completed_date_time based on olderthan. However, the command will actually delete the data on appl_id.
The SELECT is first done by data and ordered by appl_id (PostgreSQL example)
SELECT APPL_ID FROM H_APPLICATION WHERE SCHEDULED_DATE_TIME < '2019-02-19 19:21:26.805'::date ORDER BY APPL_ID
The appl_id field is incremental based on application run and will always increase on each application run. The schedule date time can be manually changed at trigger time to simulate past or future events. An application execute today will have higher appl_id then an application that ran last month. If the application ran today will have schedule date time from last year, then that will show up in above list.
E.g. We trigger an event in past, 2019. The SCHEDULED_DATE_TIME will be in past but APPL_ID will be from today and higher then previous application runs
SELECT APPL_ID FROM ESP_APPLICATION WHERE SCHEDULED_DATE_TIME < '2019-02-19 19:21:26.805'::date ORDER BY APPL_ID
3300498
SELECT * FROM ESP_APPLICATION WHERE APPL_ID = '3300498';
appl_id | job_name | appl_name | appl_gen_no | start_date_time | end_date_time | scheduled_date_time | state
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3300498 | VERIFY~~ | VERIFY | 123 | 2021-02-19 19:34:39.93 | 2021-02-19 19:34:40.25 | 2018-02-19 14:34:23 | COMPLETE
We trigger another event and define an older date than above, 2015 and but appl_id will get incremented.
SELECT * FROM ESP_APPLICATION WHERE APPL_ID = '3300501';
appl_id | job_name | appl_name | appl_gen_no | start_date_time | end_date_time | scheduled_date_time | state |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3300501 | VERIFY~~ | VERIFY | 126 | 2021-02-19 19:46:42.37 | 2021-02-19 19:46:42.64 | 2015-02-19 14:46:26 | COMPLETE
When DELTESTAGINGDATA command is issued it will select applications based on SCHEDULED_DATE_TIME and will get list of APPL_ID. It will delete data based on the highest APPL_ID it gets to lowest.
SELECT APPL_ID FROM H_APPLICATION WHERE SCHEDULED_DATE_TIME < '2019-02-19 19:21:26.805'::date ORDER BY APPL_ID;
appl_id
---------
3300498
3300500
3300501
When that happens, it will end up removing application that may have executed recently or after the OLDERTHAN date condition.
Resolution
The workaround is to setup a stored procedure to delete data from H_ tables based on end_date_time field.
SELECT * FROM H_APPLICATION WHERE SCHEDULED_DATE_TIME < '2020-02-19 19:21:26.805'::date
Feedback
