Enuda
asked on
How can I suppress the "WHEN(00001:00002) = X'0314'" card in a DB2 UNLOAD SYSPUNCH members?
Hi all,
I am working to laod a test database using production unload data but the SYSPUNCH members are from the Test database tables with various WHEN(00001:00002) = X'????' conditions in the syspunch members.
I will like to regenerate the syspunch members such that the variuos WHEN(00001:00002) = X'????' card within the members are suppressed. How can I get this accomplished other than manually accessing and deleting the individual lines where this card occurs? I have about a thousand of this members to process and I will hate to spend all of my memorial weekend doing tideous line deletes.
Any suggestion or recommendation that can help will be highly appreciated.
Thanks
I am working to laod a test database using production unload data but the SYSPUNCH members are from the Test database tables with various WHEN(00001:00002) = X'????' conditions in the syspunch members.
I will like to regenerate the syspunch members such that the variuos WHEN(00001:00002) = X'????' card within the members are suppressed. How can I get this accomplished other than manually accessing and deleting the individual lines where this card occurs? I have about a thousand of this members to process and I will hate to spend all of my memorial weekend doing tideous line deletes.
Any suggestion or recommendation that can help will be highly appreciated.
Thanks
ASKER
Hi,
I have tried the DSNTIAUL route and I amrunning into a wall here...what can be the problem with this mainframe script?
THE ERROR MESSAGE
DSNT490I SAMPLE DATA UNLOAD PROGRAM
DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
DSNT504I UNLOAD DATA SET SYSPUNCH BLOCK SIZE SET TO 27920
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE AAPYRLL1.TS32K02S
DSNT408I SQLCODE = -204, ERROR: AAPYRLL1.TS32K02S IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -500 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
THE JCL
//TS32K02S EXEC PGM=IKJEFT01,REGION=0M
//STEPLIB DD DISP=SHR,DSN=SYS2.DB2.HRS1
// DD DISP=SHR,DSN=HRS1.DB2.RUNL
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD DISP=SHR,DSN=HRS1FNAN.HRS1
//SYSIN DD *
AAPYRLL1.TS32K02S NOPAD
//SYSPUNCH DD DSN=HRS1FNAN.AAPYRLL1.REPL
//*SYSREC DD DUMMY,DCB=BLKSIZE=32768
THE TABLESPACE EXIST
DSNT362I +HRS1 DATABASE = AAPYRLL1 STATUS = RW
DBD LENGTH = 1364834
DSNT397I +HRS1
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
TS32K02S TS RW
TS32K03S TS RW
THE CONTROL MEMBER
READY
DSN SYSTEM(HRS1)
DSN
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) LIBRARY('HRS1.DB2.RUNLIB.L
DSN
END
Any one see anything I am missing?
Thanks
I have tried the DSNTIAUL route and I amrunning into a wall here...what can be the problem with this mainframe script?
THE ERROR MESSAGE
DSNT490I SAMPLE DATA UNLOAD PROGRAM
DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
DSNT504I UNLOAD DATA SET SYSPUNCH BLOCK SIZE SET TO 27920
DSNT493I SQL ERROR DURING SQL STATEMENT PREPARE , TABLE AAPYRLL1.TS32K02S
DSNT408I SQLCODE = -204, ERROR: AAPYRLL1.TS32K02S IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -500 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
THE JCL
//TS32K02S EXEC PGM=IKJEFT01,REGION=0M
//STEPLIB DD DISP=SHR,DSN=SYS2.DB2.HRS1
// DD DISP=SHR,DSN=HRS1.DB2.RUNL
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD DISP=SHR,DSN=HRS1FNAN.HRS1
//SYSIN DD *
AAPYRLL1.TS32K02S NOPAD
//SYSPUNCH DD DSN=HRS1FNAN.AAPYRLL1.REPL
//*SYSREC DD DUMMY,DCB=BLKSIZE=32768
THE TABLESPACE EXIST
DSNT362I +HRS1 DATABASE = AAPYRLL1 STATUS = RW
DBD LENGTH = 1364834
DSNT397I +HRS1
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
TS32K02S TS RW
TS32K03S TS RW
THE CONTROL MEMBER
READY
DSN SYSTEM(HRS1)
DSN
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) LIBRARY('HRS1.DB2.RUNLIB.L
DSN
END
Any one see anything I am missing?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It worked but runs very slow
i remember facing that problem myself,
as i recall there is no way of telling db2 not code those statements
maybe you can try to write in your unload statment both the tablespace and table arguments, for example
unload tablespace dsndb06.sysdbase from table sysibm.sysdatabase
maybe when you do it this way, db2 knows the output file will only contain one table so it does not write those when clauses, but i don't believe this will work
what you can do is to unload data using dsntiaul utility and it won't create the when clause in the syspunch file, but it does not work as good as the unload utility