Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 2285
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: 2285
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 Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Generate public-private key in batch ... vasanthz All Other Mainframe Topics 3 Fri Sep 15, 2017 9:20 am
No new posts Working with hex values in Filemanage... Emile Straker IBM Tools 0 Tue Sep 12, 2017 1:42 am
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Unable to copy data from spool in a d... srikant314 JCL & VSAM 8 Fri Jul 28, 2017 9:10 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us