Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Purge using DD DUMMY load process

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
navit_1980

New User


Joined: 04 Jun 2006
Posts: 6
Location: Charlotte, NC

PostPosted: Mon May 14, 2007 9:04 pm    Post subject: Purge using DD DUMMY load process
Reply with quote

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
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon May 14, 2007 9:15 pm    Post subject:
Reply with quote

Hello,

Please post your jcl and the error information for the failed attempt.
Back to top
View user's profile Send private message
navit_1980

New User


Joined: 04 Jun 2006
Posts: 6
Location: Charlotte, NC

PostPosted: Mon May 14, 2007 9:40 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
navit_1980

New User


Joined: 04 Jun 2006
Posts: 6
Location: Charlotte, NC

PostPosted: Mon May 14, 2007 9:45 pm    Post subject:
Reply with quote

Here, I am using a DSNUTILB load utility in the PROC DB2UTLT5.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue May 15, 2007 12:55 am    Post subject: Re: Purge using DD DUMMY load process
Reply with quote

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 "
Back to top
View user's profile Send private message
navit_1980

New User


Joined: 04 Jun 2006
Posts: 6
Location: Charlotte, NC

PostPosted: Tue May 15, 2007 1:45 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue May 15, 2007 11:36 am    Post subject: Re: Purge using DD DUMMY load process
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Db2 load job abending in SORT mistah kurtz DB2 4 Mon Jun 05, 2017 3:58 pm
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us