| View previous topic :: View next topic |
| Author |
Message |
ccs
Beginner
Joined: 24 Mar 2004
Posts: 2
Topics: 1
Location: USA
|
 Posted: Thu Mar 25, 2004 8:27 pm Post subject: Data purge in DB2 thru JCL |
 |
|
Hi,
How to purge previous years (ex:2000-2003) data from DB2 table thru JCL. I would like to keep the purged data in a dataset. Please advice me.
Regards
CCS |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12395
Topics: 75
Location: San Jose
|
| Posted: Fri Mar 26, 2004 6:09 am Post subject: |
|
|
ccs,
Even though ravi has provided the right links, I will post the solution as it can be done all in one single step.Try this unload jcl. It will first unload all the records where year is less than the current year. The delete statement will delete all the old records.
| Code: |
//STEP0100 EXEC PGM=IKJEFT01
//*
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('XXXX.RUNLIB.LOAD')
//SYSREC00 DD DSN=YOUR PURGED RECORDS,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=CYL,(X,Y),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT *
FROM
TABLE NAME
WHERE YEAR_COL < CURRENT YEAR
;
DELETE FROM
TABLE NAME
WHERE YEAR_COL < CURRENT YEAR
;
/*
|
Check this link for examples of unloading of the tables using DSNTIAUL
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/APPENDIX1.3.1?DT=20010710165542
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
SureshKumar
Intermediate
Joined: 23 Jan 2003
Posts: 211
Topics: 21
|
| Posted: Fri Mar 26, 2004 10:17 am Post subject: |
|
|
Kolusu,
How does the Utility handle Commits ? CCS may have to consider how the tables are being used to avoid -911's and also design appropriately for restart process. I would like to know if the utility takes a lock for the process are locks for each row being deleted ? thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12395
Topics: 75
Location: San Jose
|
| Posted: Fri Mar 26, 2004 10:34 am Post subject: |
|
|
Sureshkumar,
There is no commit involved in between the deletes while using the utility. After the succesful completion of the job, there is an implicit commit.So if he is worried about the dead locks then he can split the delete cards and apply commit in between.
For ex:
| Code: |
DELETE FROM
TABLE NAME
WHERE YEAR_COL = 2000;
COMMIT;
DELETE FROM
TABLE NAME
WHERE YEAR_COL = 2001;
COMMIT;
DELETE FROM
TABLE NAME
WHERE YEAR_COL = 2002;
COMMIT;
DELETE FROM
TABLE NAME
WHERE YEAR_COL = 2003;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
SureshKumar
Intermediate
Joined: 23 Jan 2003
Posts: 211
Topics: 21
|
| Posted: Fri Mar 26, 2004 10:54 am Post subject: |
|
|
Kolusu,
Thanks for the reply, in effect you mean to say the 'max locks' does not apply to utilities. Just to have clarification. Thanks |
|
| Back to top |
|
|
ccs
Beginner
Joined: 24 Mar 2004
Posts: 2
Topics: 1
Location: USA
|
| Posted: Fri Mar 26, 2004 5:20 pm Post subject: |
|
|
Thanks for your concern Kolusu, Ravi and Suresh...
Regards
CCS |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
