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
 

 

SQL via JCL query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 72
Location: bangalore

PostPosted: Sat Sep 18, 2010 12:21 am    Post subject: SQL via JCL query
Reply with quote

I have JCL to run the SQL query where i directly give the query in SYSIN, but the output dataset mentioned in the SYSREC00 does not store this result.

Code:
//STEP20  EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB  DD DSN=DB2PRD03.SUBSYS.LOADLIB,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=ECNS.USER.HTAELI.QUAT,DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(1,20),RLSE)



Can you please let me know what can be done to save the output in a outputfile
Back to top
View user's profile Send private message

rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Sat Sep 18, 2010 1:12 am    Post subject:
Reply with quote

While using IKJEFT01, I use SYSPRINT to hold the output DSN. You may wish to try that !

To my knowledge, SYSREC00 is used for unload utilities.
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 72
Location: bangalore

PostPosted: Sat Sep 18, 2010 1:18 am    Post subject: SQL via JCL query
Reply with quote

itried this but i am getting the entire sql as well in the output file.
I just need the output data

please suggest
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Sat Sep 18, 2010 1:39 am    Post subject:
Reply with quote

Hello,
Suggest you to use Unload utilities like DSNTIAUL.
Please find sample JCL below,
Code:
//STEP010  EXEC PGM=IKJEFT01,REGION=1024K     
//STEPLIB  DD  DSN=DB2PRD03.SUBSYS.LOADLIB,DISP=SHR
//SYSPRINT DD  SYSOUT=*                       
//SYSTSPRT DD  SYSOUT=*                       
//SYSREC00 DD SYSOUT=*                         
//SYSPUNCH DD  DUMMY                           
//SYSTSIN  DD  *                               
 DSN SYSTEM(DB2I)                             
  RUN PROG(DSNTIAUL) PLAN(DSNTIAUL) -         
  LIB('DB2PRD03.SUBSYS.LOADLIB')                   
 END                                           
/*                                             
//SYSIN DD *                                   
 SYSIBM.SYSTABLES WHERE NAME='TABLE1'
/*                                             
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1755
Location: Bloomington, IL

PostPosted: Sat Sep 18, 2010 1:41 am    Post subject:
Reply with quote

Although you have refrained from posting the information, I suspect that you are running DSNTEP2 (often renamed by shops), which runs any dynamic SQL. You should use DSNTIAUL (likewise often renamed), which unloads tables to the SYSRECnn DDs.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Sat Sep 18, 2010 1:56 am    Post subject:
Reply with quote

Akatsukami,
That is an interesting piece of information for me. I wasn't really aware that DSNTIAUL is a shop renamed version of DSNTEP2. Though, your post kindles interest in me to do some readup to understand it better, the fact that its a Friday eve doesn't really let me do it ;-)
Anyways, this was the link I referred and hence I thought DSNTIAUL is an IBM supplied program - http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_dsntiaul.htm
Probably I'll use the forthcoming Monday to understand it better.
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 72
Location: bangalore

PostPosted: Sat Sep 18, 2010 1:58 am    Post subject:
Reply with quote

thanks it worked
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1755
Location: Bloomington, IL

PostPosted: Sat Sep 18, 2010 2:29 am    Post subject:
Reply with quote

rockish wrote:
Akatsukami,
That is an interesting piece of information for me. I wasn't really aware that DSNTIAUL is a shop renamed version of DSNTEP2.

I wrote unclearly. DSNTEP2 and DSNTIAUL are separate IBM-supplied programs (they are technically sample DB2 applications, but are invariably used as DB2 utilities). However, both are often renamed in a rather pathetic attempt at "security by obscurity".
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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