Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

More than one query in batch job

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Poonam Jain

New User


Joined: 06 Apr 2009
Posts: 5
Location: mumbai

PostPosted: Wed Jun 03, 2009 7:38 pm    Post subject: More than one query in batch job
Reply with quote

Hi,

Can anyone let me know how to code multiple queries in one JCL. I want the output of all queries in one file.
I have codded below JCL that is working only for one query. It is not giving the output for other queries..

____________________

//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=TSYS.DSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSOUT DD *
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=T0515PJ.DB2.ADDCDB2P.DAPPLID,
// DISP=(NEW,KEEP,KEEP),
// DCB=(RECFM=FBA,LRECL=133),
// UNIT=DISK,SPACE=(CYL,(5,5),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(GRT1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
PARM('SQL') -
LIB('DB2P.RUNLIB.LOAD')
END
//SYSIN DD *
MY QUERY 1; -----> Giving output for this
MY QUERY 2;-------> Not giving Output for this and other query below this

Note : Out of queries are not same
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Wed Jun 03, 2009 7:40 pm    Post subject:
Reply with quote

Multiple steps using the same output file with DISP=MOD
Back to top
View user's profile Send private message
Poonam Jain

New User


Joined: 06 Apr 2009
Posts: 5
Location: mumbai

PostPosted: Wed Jun 03, 2009 7:46 pm    Post subject: Reply to: More than one query in batch job
Reply with quote

It means to get output of multiple queries in one step and in one file is not possible?
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Wed Jun 03, 2009 7:49 pm    Post subject:
Reply with quote

That I can not tell you, but merely made one suggestion that will work for your requirement.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Wed Jun 03, 2009 10:42 pm    Post subject:
Reply with quote

Poonam Jain,

This is what I found in the DB2 Utilities manual here.

Quote:
SYSRECnn
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.


You'll have to use multiple output datasets as SYSREC00,SYSREC01, etc in the same step corresponding to each SELECT statement in your SYSIN card.
Back to top
View user's profile Send private message
Poonam Jain

New User


Joined: 06 Apr 2009
Posts: 5
Location: mumbai

PostPosted: Thu Jun 04, 2009 2:47 pm    Post subject: Reply to: More than one query in batch job
Reply with quote

Thnx all, Now it is working. I have codded JCL as below. May be it will help other.

Note : LRECL should be max ( and same every where ) depending on the query which is givning highest output record.
e. g
Query 1 -- 80
Query2 -- 133 --> use this one everywhere
query 3-- 80
-----------------------------------------------------------------------
Code:
//STEP01  EXEC PGM=IKJEFT01,DYNAMNBR=20                 
//STEPLIB  DD  DSN=TSYS.DSNLOAD,DISP=SHR               
//SYSTSPRT DD  SYSOUT=*                                 
//SYSOUT   DD  *                                       
//SYSPRINT DD  SYSOUT=*                                 
//SYSPUNCH DD  SYSOUT=*                                 
//SYSREC00 DD  DSN=&&T0515PJ1,                         
//             DISP=(,PASS),                           
//             DCB=(RECFM=FB,LRECL=133),               
//             UNIT=DISK,SPACE=(CYL,(5,5),RLSE)         
//SYSREC01 DD  DSN=&&T0515PJ1,                         
//             DISP=(,PASS),                           
//             DCB=(RECFM=FB,LRECL=133),               
//             UNIT=DISK,SPACE=(CYL,(5,5),RLSE)         
//SYSREC02 DD  DSN=&&T0515PJ1,                         
//             DISP=(,PASS),                           
//             DCB=(RECFM=FB,LRECL=133),               
//             UNIT=DISK,SPACE=(CYL,(5,5),RLSE)         
//SYSREC03 DD  DSN=&&T0515PJ1,                                       
//             DISP=(,PASS),                                         
//             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;
Query 3;
Query 4;
//*
//COPYREC  EXEC PGM=IEBGENER                                       
//SYSPRINT DD SYSOUT=*                                             
//*                                                                 
//SYSUT1   DD DSN=*.STEP01.SYSREC00,DISP=(OLD,DELETE)               
//         DD DSN=*.STEP01.SYSREC01,DISP=(OLD,DELETE)               
//         DD DSN=*.STEP01.SYSREC02,DISP=(OLD,DELETE)               
//         DD DSN=*.STEP01.SYSREC03,DISP=(OLD,DELETE)               
//SYSUT2   DD DSN=T0515PJ.DB2.ADDCDB2P.DAPPLID,DISP=(,CATLG,DELETE),
//            DCB=(RECFM=FB,LRECL=133),                             
//            UNIT=DISK,SPACE=(CYL,(30,30),RLSE)                   
//SYSIN    DD DUMMY                                                 
//SYSUDUMP  DD SYSOUT=*       
---------------------------------------------------------------------------


Edited: Please use BBcode when You post some code/error, that's rather readable, Thanks... Anuj
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Thu Jun 04, 2009 3:07 pm    Post subject:
Reply with quote

You're welcome. Good to hear it's working icon_smile.gif
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts HLIST Utility In Batch Virendra Shambharkar TSO/ISPF 4 Fri Apr 07, 2017 3:38 pm
No new posts Batch job tuning sgandhla Testing & Performance analysis 5 Fri Mar 24, 2017 9:41 pm
No new posts MIPS/CPU consumption reduction in Batch vishwakotin DFSORT/ICETOOL 4 Sat Mar 18, 2017 5:46 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us