Posted: Tue Oct 25, 2016 11:24 am Post subject: Purge 60 Days of Old Data
Hi,
I need to delete 60 Days of Old Data from 42 Different Tables. Delete Query has below.
DELETE FROM DB2D.TABLE_PURGE WHERE DATE(DT_LST_UPDT) <= CURRENT DATE - 60 Days;
Everyday we receive 200,000 Records of Data to each Table. Can we design a Job with 1 Jcl and pass 42 Table Names as parameters
to delete data in sequence of each table.
If i give the above the Delete Statement, there might be chances of Job Failing with Lock issues.
Is there a way to commit after every 5000 Rows Delete.
Please advise me if we can design a job with 1 or Few JCL's.
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Wed Oct 26, 2016 12:57 pm Post subject: Re: Purge 60 Days of Old Data
jayram99 wrote:
Can we design a Job with 1 Jcl and pass 42 Table Names as parameters
to delete data in sequence of each table.
Jayram99,
yes you can. Code the following JCL and pass the table name as JCL variable. Initially run step0100 with output to sysout and check if the DELETE statement is generated properly. Once you have the right delete statement then run step0200 to delete the rows. For 42 tables all you need to do is change the symbolic. You can code the below 2 steps as a proc and invoke via JCL passing the different table names.
If i give the above the Delete Statement, there might be chances of Job Failing with Lock issues.
Is there a way to commit after every 5000 Rows Delete.
There are ways to delete specific number of rows dependent on the version of DB2 you are running.
some thing like this (UNTESTED) Sql
Code:
DELETE FROM (SELECT *
FROM YOUR_TABLE_NAME
WHERE DATE(DT_LST_UPDT) <= CURRENT DATE - 60 DAYS
FETCH FIRST 50000 ROWS ONLY) ;
I think 5000 is too low count for the commit. So make it 50,000 and see how much time it takes.
Since you have 200,000 records to be deleted, you need to generate the above statement in step0100, four times.
I will leave it to you to figure out as how to do that. Hint use "/" to create a new line. _________________ Kolusu
www.linkedin.com/in/kolusu
It seems to me that the simplest way to do this is the best, especially when you don't know who will be doing maintenance in the future.
And the simplest would be to just take that DELETE statement and repeat it 42 times, inserting the names of the 42 tables. That should be about a 10 minute task with any modern editor.
Presumably there aren't frequent changes to which tables are involved, so this should run fine for a long time. And it those do change, Kolusu's JCL generator will have to be changed also. (Unless the 42 parms are automatically generated from your data, which wasn't indicated.)
K.I.S.S. has proven to be an enduring design principle.
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Thu Oct 27, 2016 2:26 pm Post subject: Re: Purge 60 Days of Old data
jayram99 wrote:
I couldn't delete and commit for every 5000 records.
My company DB2 version is not supporting the DELETE Statement as below.
Please let me know if there any other ways to handle this scenario.
Thank you,
jayaram
Jayram99,
When you say something does not work you need to provide more information like the error messages, so that we can diagnose as to what the problem is.
Either way try this alternative Sql (again untested)
Code:
DELETE FROM TABLE_NAME
WHERE primary_key IN (SELECT primary_key
FROM TABLE_NAME
WHERE DATE(DT_LST_UPDT) <= CURRENT DATE - 60 DAYS
FETCH FIRST 5000 ROWS ONLY)
;
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
