MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Purge 60 Days of Old Data

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Job Control Language(JCL)
View previous topic :: View next topic  
Author Message
jayram99
Beginner


Joined: 16 Aug 2004
Posts: 52
Topics: 21
Location: falls church.va,usa

PostPosted: Tue Oct 25, 2016 11:24 am    Post subject: Purge 60 Days of Old Data Reply with quote

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.

Appreciate your Help.
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Wed Oct 26, 2016 12:57 pm    Post subject: Re: Purge 60 Days of Old Data Reply with quote

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.

Code:

//   SET TBLNAME='DB2D.TABLE_PURGE' 
//*                   
//STEP0100 EXEC PGM=SORT,PARM='JP1"&TBLNAME"'       
//SYSOUT   DD SYSOUT=*                               
//SORTIN   DD *                                     
//SORTOUT  DD DSN=&&S,DISP=(NEW,CATLG,DELETE),SPACE=(CYL,(1,1),RLSE)
//SYSIN    DD *                                     
  OPTION COPY                                       
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),             
  HEADER1=(' DELETE FROM',/,                         
             JP1,/,                                 
           '  WHERE DATE(DT_LST_UPDT) <=',           
           ' CURRENT DATE - 60 DAYS',/,             
           ' ;')                                     
//*                                                 
//STEP0200 EXEC PGM=IKJEFT01,COND=(0,LT,STEP0100)
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121                             
//SYSPRINT DD SYSOUT=*                                             
//SYSTSIN  DD *                                                     
 DSN SYSTEM(DB2P)                                                   
 RUN  PROGRAM(DSNTIAUL) -                                           
      PLAN(DSNTIAUL)    -                                           
      PARMS('SQL')      -                                           
      LIB('DB2P.RUNLIB.LOAD')                                       
//SYSPUNCH DD SYSOUT=*                                             
//SYSIN    DD DSN=&&S,DISP=(OLD,DELETE,DELETE)
//*


jayram99 wrote:

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 - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
t-bonham@scc.net
Supermod


Joined: 18 Oct 2012
Posts: 30
Topics: 0
Location: Minneapolis, MN

PostPosted: Wed Oct 26, 2016 3:13 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail AIM Address
jayram99
Beginner


Joined: 16 Aug 2004
Posts: 52
Topics: 21
Location: falls church.va,usa

PostPosted: Thu Oct 27, 2016 1:10 pm    Post subject: Purge 60 Days of Old data Reply with quote

Thanks Kolusu for you help.

The first part is working and could delete the data.

I couldn't delete and commit for every 5000 records.

My company DB2 version is not supporting the DELETE Statement as below.

DELETE FROM (SELECT * FROM TABLE_NAME WHERE DATE(DT_LST_UPDT) <= 60 DAYS FETCH FIRST 5000 ROWS ONLY WIHT UR).

Please let me know if there any other ways to handle this scenario.

Thank you,
jayaram
Back to top
View user's profile Send private message Yahoo Messenger MSN Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu Oct 27, 2016 2:26 pm    Post subject: Re: Purge 60 Days of Old data Reply with quote

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

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Job Control Language(JCL) All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group