JCL to get the record count of a table
Moderators: Frank Yaeger, DikDude, Moderator Group
JCL to get the record count of a table
What should I include in my Unload JCL in order to get the record count of the table into a file while unloading it.
Currently my Unload JCL is as given below,
//STEP010 EXEC PGM=IKJEFT01
//STEPLIB DD DSN=DB2P.RUNLIB.LOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(GRT1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*
//SYSPUNCH DD DSN=T6329EG.UNLOAD.SYSPUNCH.PUSDSMX,
// DISP=(NEW,CATLG,DELETE),
// SPACE=()
//SYSREC00 DD DSN=T6329EG.DATA.PUSDSMX,
// DISP=(NEW,CATLG,DELETE),
// SPACE=()
//SYSIN DD *
SELECT *
FROM ODDCDB2P.P.PUSDSMX ;
/*
Can we make any modifications to it so as to get the table count simultaneously during unloads.
Currently my Unload JCL is as given below,
//STEP010 EXEC PGM=IKJEFT01
//STEPLIB DD DSN=DB2P.RUNLIB.LOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(GRT1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*
//SYSPUNCH DD DSN=T6329EG.UNLOAD.SYSPUNCH.PUSDSMX,
// DISP=(NEW,CATLG,DELETE),
// SPACE=()
//SYSREC00 DD DSN=T6329EG.DATA.PUSDSMX,
// DISP=(NEW,CATLG,DELETE),
// SPACE=()
//SYSIN DD *
SELECT *
FROM ODDCDB2P.P.PUSDSMX ;
/*
Can we make any modifications to it so as to get the table count simultaneously during unloads.
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
Code: Select all
//SYSIN DD *
SELECT *
FROM ODDCDB2P.P.PUSDSMX ;
/*
Code: Select all
//SYSIN DD *
SELECT *
FROM ODDCDB2P.P.PUSDSMX
;
SELECT count(*)
FROM ODDCDB2P.P.PUSDSMX
;
/*
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
For SYSRECnn, the output data sets, the value nn ranges from 00 to 99. You can have a maximum of 100 output data sets for a single execution of DSNTIAUL. Each data set contains the data that is unloaded when DSNTIAUL processes a SELECT statement from the input data set. Therefore, the number of output data sets must match the number of SELECT statements (if you specify parameter SQL) or table specifications in your input data set -- in short, you can not have the results of two different queries in a single SYSREC00. For SYSREC00, you need to modify your JCL as:
If you want all the outputs in a single DSN -- add another step to merge SYSRECnn.
Code: Select all
//SYSREC00 DD DSN=output.from.query1,
// DISP=(NEW,CATLG,DELETE),
// DCB=(RECFM=FB,LRECL=133),
// UNIT=DISK,SPACE=(CYL,(5,5),RLSE)
//SYSREC01 DD DSN=output.from.query2,
// DISP=(NEW,CATLG,DELETE),
// DCB=(RECFM=FB,LRECL=133),
// UNIT=DISK,SPACE=(CYL,(5,5),RLSE)
//SYSTSIN DD *
DSN SYSTEM(GRT1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
PARM('SQL') -
LIB('DB2P.RUNLIB.LOAD')
END
//SYSIN DD *
Query 1;
Query 2;
//*
Regards,
Anuj
Anuj
No need to use a utility.Which Utility should I use?
This works several places:
Code: Select all
//SYSIN DD *
SELECT COUNT(*)
FROM Qual.Table
;
//SYSTSIN DD *
DSN SYSTEM(DSNx)
RUN PROGRAM(DSNTEP2) PLAN(yourplan)
Have a good one
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
actually, DSNTEP2 is batch spufi
it is not 'unload'
there are other utilities that are used specifically for 'unloads', DSNTIAUL is one of them.
you are obviously not reading all of the post.
selective reading will lead you to such erroneous posts, as you have just made.
and if you were to make the SYSOUT of your DSNTIAUL utility a DS,
you could parse the record count from that,
since it is there.
it is not 'unload'
there are other utilities that are used specifically for 'unloads', DSNTIAUL is one of them.
you are obviously not reading all of the post.
selective reading will lead you to such erroneous posts, as you have just made.
and if you were to make the SYSOUT of your DSNTIAUL utility a DS,
you could parse the record count from that,
since it is there.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
you don't need PARMS parm for DSNTEP2.
and check with your dbA to insure DNSTEP2 is bound in plan DSNTEP2
you should put the sys db2 library where DSNTEP2 is located: LIB(' ')
the PARMS parm was one error
LIB(' ') probably was also, but you may end-up with a different error - S806 from the IJKEFT.
and you could try with the DSNTEP2 as a plan, as a test, once you get the rest correct.
and check with your dbA to insure DNSTEP2 is bound in plan DSNTEP2
you should put the sys db2 library where DSNTEP2 is located: LIB(' ')
the PARMS parm was one error
LIB(' ') probably was also, but you may end-up with a different error - S806 from the IJKEFT.
and you could try with the DSNTEP2 as a plan, as a test, once you get the rest correct.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page