Avatar of Enuda
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
Mainframe OSDB2

Avatar of undefined
Last Comment
Enuda

8/22/2022 - Mon
momi_sabag

Hi
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
ASKER
Enuda

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.SDSNLOAD
//         DD DISP=SHR,DSN=HRS1.DB2.RUNLIB.LOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN  DD DISP=SHR,DSN=HRS1FNAN.HRS1.INPUT(DSNTIAUL)
//SYSIN DD   *
 AAPYRLL1.TS32K02S  NOPAD
//SYSPUNCH DD DSN=HRS1FNAN.AAPYRLL1.REPLACE.SYSNOPAD(TS32K02S),DISP=SHR
//*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.LOAD')
DSN
END

Any one see anything I am missing?

Thanks
ASKER CERTIFIED SOLUTION
momi_sabag

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Enuda

It worked but runs very slow
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck