JCL to get the record count of a table

In this Mainframe Forum - You can post your queries on JCL, OS/390 JCL, MVS JCL, z/OS JCL, JES2 & JES3

Moderators: Frank Yaeger, DikDude, Moderator Group

archup20
Member
Posts: 31
Joined: Thu Dec 09, 2010 9:53 am

JCL to get the record count of a table

Post by archup20 » Mon Oct 03, 2011 3:02 pm

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.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Mon Oct 03, 2011 6:10 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.

archup20
Member
Posts: 31
Joined: Thu Dec 09, 2010 9:53 am

Post by archup20 » Tue Oct 04, 2011 11:58 am

I have tried The SELECT COUNT(*) option instead of SELECT (*) but the output is not proper in my SYSREC00.

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Tue Oct 04, 2011 11:57 pm

but the output is not proper in my SYSREC00.
Do NOT post "it didn't work" - which is a complete waste of everyone's time.

What does it do now? Why is this not "proper". What do you want if not the output of the SELECT COUNT?
Have a good one

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Sat Oct 08, 2011 2:48 pm

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:

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; 
//*
If you want all the outputs in a single DSN -- add another step to merge SYSRECnn.
Regards,
Anuj

archup20
Member
Posts: 31
Joined: Thu Dec 09, 2010 9:53 am

Post by archup20 » Wed Oct 12, 2011 11:50 am

thanks for the new bit of information Anuj.
I did not know that unload JCLs can work like this.

When I gave SELECT COUNT(*) in my unload JCL, the output file did not contain the number of records.

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Wed Oct 12, 2011 9:57 pm

Suggest you not use the unload utility to get the count.

Just run a separate step that genertes the SELECT COUNT(*) output.
Have a good one

archup20
Member
Posts: 31
Joined: Thu Dec 09, 2010 9:53 am

Post by archup20 » Thu Oct 13, 2011 9:53 am

Which Utility should I use? Can you please suggest an alternate one.

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Thu Oct 13, 2011 8:35 pm

Which Utility should I use?
No need to use a utility.

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

archup20
Member
Posts: 31
Joined: Thu Dec 09, 2010 9:53 am

Post by archup20 » Fri Oct 14, 2011 4:32 pm

Is nt this an unload JCL??

Is there any other way to get the record count of a table into a file?

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Fri Oct 14, 2011 4:45 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.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

archup20
Member
Posts: 31
Joined: Thu Dec 09, 2010 9:53 am

Post by archup20 » Fri Oct 14, 2011 5:50 pm

Sorry dint check properly.

I tried using the DSNTEP2

DSN SYSTEM(GRT1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) PARMS('SQL') -
LIB(' ')


But i am getting a MAXCC=8 with the below message

DSNT499I ERROR IN INPUT PARAMETER LIST

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Fri Oct 14, 2011 6:41 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.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

archup20
Member
Posts: 31
Joined: Thu Dec 09, 2010 9:53 am

Post by archup20 » Sat Oct 15, 2011 11:14 am

Thanks dbZ and DikDude it is working fine now.
:)

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Sat Oct 15, 2011 3:39 pm

great that you reached a solution...
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply

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



Other References
Mainframe Tools and others