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
 
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: 1777
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: 1777
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
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
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us