Purge using DD DUMMY load process

navit_1980 · Posted: Mon May 14, 2007 9:04 pm

Hi

Can anyone help me with a purge process using a Load utility and do a DD DUMMY load of the dummy dataset?
I am trying to run the job, it is abending.
Can anyone give me a sample job doing the same thing, it will be highly appreciated.

Thanks,
Navit

dick scherrer · Posted: Mon May 14, 2007 9:15 pm

Hello,

Please post your jcl and the error information for the failed attempt.

navit_1980 · Posted: Mon May 14, 2007 9:40 pm

This is the jcl:
=========

//XXXXXXX JOB (NKTC),'LOAD XPERFLOGD',
// CLASS=I,MSGCLASS=V,NOTIFY=&SYSUID
//JOBLIB DD DSN=DB2TC.DBA.LOADLIB,DISP=SHR
// DD DSN=D9ST.DB2.DSNLOAD,DISP=SHR
//U11STEP EXEC UCC11,PARM='F'
//*
//JS000100 EXEC DB2CMDT5
//SYSUT1 DD *
-TERM UTILITY(WCCLD43.XPEFLOG)
/*
//*
//JS000200 EXEC DB2UTLT5,
// SYSTEM=D9ST,
// UID='WCCLD43.XPEFLOG'
//SYSUT1 DD DSN=&&SYSUT1,
// DISP=(NEW,DELETE,DELETE),
// UNIT=WORK,SPACE=(CYL,(300,300))
//SORTOUT DD DSN=&&SORTOUT,
// DISP=(NEW,DELETE,DELETE),
// UNIT=WORK,SPACE=(CYL,(300,300))
//SYSDISC DD DSN=&&SYSDISC,
// DISP=(NEW,DELETE,DELETE),
// UNIT=WORK,SPACE=(CYL,(300,300))
//SYSMAP DD DSN=&&SYSMAP,
// DISP=(NEW,DELETE,DELETE),
// UNIT=WORK,SPACE=(CYL,(300,300))
//SYSERR DD DSN=&&SYSERR,
// DISP=(NEW,DELETE,DELETE),
// UNIT=WORK,SPACE=(CYL,(300,300))
//SYSREC01 DD DUMMY
//SYSIN DD DSN=WCCT.TEST.DEV1.XPEFLG.SYSPUNCH,DISP=SHR

The syspunch file is: WCCT.TEST.DEV1.XPEFLG.SYSPUNCH
=============

TEMPLATE BEPJWEY3
DSN('WCCT.TEST.DEV1.XPEFLG.P&PA.')
DISP(OLD,KEEP,KEEP)
LOAD DATA LOG NO REPLACE
UNICODE CCSID(00037,01208,01200)
INTO TABLE "WCC43 "."XPERF_LOG_DATA "
PART 00001 INDDN SYSREC01
WHEN(00001:00002) = X'000A'
( "TRAN_TYPE " POSITION( 00003:00044) VARCHAR MIXED
, "MAX_TIME " POSITION( 00046:00055) DECIMAL
NULLIF(00045)=X'FF'
, "MIN_TIME " POSITION( 00057:00066) DECIMAL
NULLIF(00056)=X'FF'
, "AVE_TIME " POSITION( 00068:00077) DECIMAL
NULLIF(00067)=X'FF'
, "STD_DEV " POSITION( 00079:00088) DECIMAL
NULLIF(00078)=X'FF'
, "TOTAL " POSITION( 00090:00093) INTEGER
NULLIF(00089)=X'FF'
, "PASSED " POSITION( 00095:00098) INTEGER
NULLIF(00094)=X'FF'
, "FAILED " POSITION( 00100:00103) INTEGER
NULLIF(00099)=X'FF'
, "INVALID_ERR " POSITION( 00105:00108) INTEGER
NULLIF(00104)=X'FF'
, "FATAL_ERR " POSITION( 00110:00113) INTEGER
NULLIF(00109)=X'FF'
, "SECURITY_ERR " POSITION( 00115:00118) INTEGER
NULLIF(00114)=X'FF'
, "OTHER_ERR " POSITION( 00120:00123) INTEGER
NULLIF(00119)=X'FF'
, "LOG_FILE_LOCATION " POSITION( 00124:00165) VARCHAR MIXED
, "CREATE_TIME " POSITION( 00166:00191) TIMESTAMP EXTERNAL
, "APPLICATION " POSITION( 00192:00233) VARCHAR MIXED
, "CHANNEL " POSITION( 00234:00275) VARCHAR MIXED
, "SERVICENAME " POSITION( 00276:00317) VARCHAR MIXED
)
INTO TABLE "WCC43 "."XPERF_LOG_DATA "
PART 00002 INDDN BEPJWEY3
WHEN(00001:00002) = X'000A'
( "TRAN_TYPE " POSITION( 00003:00044) VARCHAR MIXED
, "MAX_TIME " POSITION( 00046:00055) DECIMAL
NULLIF(00045)=X'FF'
, "MIN_TIME " POSITION( 00057:00066) DECIMAL
NULLIF(00056)=X'FF'
, "AVE_TIME " POSITION( 00068:00077) DECIMAL
NULLIF(00067)=X'FF'
, "STD_DEV " POSITION( 00079:00088) DECIMAL
NULLIF(00078)=X'FF'
, "TOTAL " POSITION( 00090:00093) INTEGER
NULLIF(00089)=X'FF'
, "PASSED " POSITION( 00095:00098) INTEGER
NULLIF(00094)=X'FF'
, "FAILED " POSITION( 00100:00103) INTEGER
NULLIF(00099)=X'FF'
, "INVALID_ERR " POSITION( 00105:00108) INTEGER
NULLIF(00104)=X'FF'
, "FATAL_ERR " POSITION( 00110:00113) INTEGER
NULLIF(00109)=X'FF'
, "SECURITY_ERR " POSITION( 00115:00118) INTEGER
NULLIF(00114)=X'FF'
, "OTHER_ERR " POSITION( 00120:00123) INTEGER
NULLIF(00119)=X'FF'
, "LOG_FILE_LOCATION " POSITION( 00124:00165) VARCHAR MIXED
, "CREATE_TIME " POSITION( 00166:00191) TIMESTAMP EXTERNAL
, "APPLICATION " POSITION( 00192:00233) VARCHAR MIXED
, "CHANNEL " POSITION( 00234:00275) VARCHAR MIXED
, "SERVICENAME " POSITION( 00276:00317) VARCHAR MIXED
)
..
..
..
..
..
..
..
..
..
INTO TABLE "WCC43 "."XPERF_LOG_DATA "
PART 00012 INDDN BEPJWEY3
WHEN(00001:00002) = X'000A'
( "TRAN_TYPE " POSITION( 00003:00044) VARCHAR MIXED
, "MAX_TIME " POSITION( 00046:00055) DECIMAL
NULLIF(00045)=X'FF'
, "MIN_TIME " POSITION( 00057:00066) DECIMAL
NULLIF(00056)=X'FF'
, "AVE_TIME " POSITION( 00068:00077) DECIMAL
NULLIF(00067)=X'FF'
, "STD_DEV " POSITION( 00079:00088) DECIMAL
NULLIF(00078)=X'FF'
, "TOTAL " POSITION( 00090:00093) INTEGER
NULLIF(00089)=X'FF'
, "PASSED " POSITION( 00095:00098) INTEGER
NULLIF(00094)=X'FF'
, "FAILED " POSITION( 00100:00103) INTEGER
NULLIF(00099)=X'FF'
, "INVALID_ERR " POSITION( 00105:00108) INTEGER
NULLIF(00104)=X'FF'
, "FATAL_ERR " POSITION( 00110:00113) INTEGER
NULLIF(00109)=X'FF'
, "SECURITY_ERR " POSITION( 00115:00118) INTEGER
NULLIF(00114)=X'FF'
, "OTHER_ERR " POSITION( 00120:00123) INTEGER
NULLIF(00119)=X'FF'
, "LOG_FILE_LOCATION " POSITION( 00124:00165) VARCHAR MIXED
, "CREATE_TIME " POSITION( 00166:00191) TIMESTAMP EXTERNAL
, "APPLICATION " POSITION( 00192:00233) VARCHAR MIXED
, "CHANNEL " POSITION( 00234:00275) VARCHAR MIXED
, "SERVICENAME " POSITION( 00276:00317) VARCHAR MIXED
)

===============================================
Error received:
-----------------

DSNU1036I DSNURELD - UNABLE TO ESTIMATE SPACE REQUIREMENTS FOR INDDN/UNLDDN
DSNU012I DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8

navit_1980 · Posted: Mon May 14, 2007 9:45 pm

Here, I am using a DSNUTILB load utility in the PROC DB2UTLT5.

wanderer · Posted: Tue May 15, 2007 12:55 am

Are you trying to empty all partitions or just 1st one? Because load statement for other partions still have INDDN BEPJWEY3.

You can remove the first 3 template lines from punch if you are not going to use the file 'WCCT.TEST.DEV1.XPEFLG.P&PA.' to load.

If you are emptying the whole table, then the complete punch can be reduced to only this,

LOAD DATA LOG NO REPLACE INDDN SYSREC01
INTO TABLE "WCC43 "."XPERF_LOG_DATA "

navit_1980 · Posted: Tue May 15, 2007 1:45 am

My table is partitioned in 12 months partitions.
I need to purge only one partition at a time which is 2 months old.

So, how can i code that in this job?

I need to see if partition is 2 months older than todays date/month, i need to purge that parition only, not complete table.

wanderer · Posted: Tue May 15, 2007 11:36 am

Well, your current load card of LOAD DATA LOG NO REPLACE is going to empty the whole table anyway. If you want to empty only one partition, then for that partition you have to specify REPLACE after the INTO TABLE tablename, not before. For others, you have to use RESUME YES instead of REPLACE in the same place i.e. after the INTO TABLE.

About the error that you are currently getting, it is hard to tell why, without looking at the messages in rest of the job.

I would think mixing TEMPLATE and a JCL hard coded SYSREC01 in the same LOAD statement DD INDDN is not very good idea. If you separate them, may be running the empty partiion step before the actual load, it will be clean and you won't have to change the load parms everytime. The separate dummy load parm can be changed alone, depending on which partiion you want to empty.

I also have a question. Since from your post it looks like each partion contains only data for one month, and you are emptying after 2 months, why do you need 12 partitions?

hope it helps.