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
 

 

Executing SQL through JCL

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

New User


Joined: 16 Feb 2008
Posts: 19
Location: Bangalore

PostPosted: Wed Jun 10, 2009 2:21 pm    Post subject: Executing SQL through JCL
Reply with quote

Hi,

I am trying to execute SQL through JCL. When i submit the JCL i am getting the rc as 0 but could not get the output. Please advise

The jcl i used is :

Code:
//G58693J JOB ,,NOTIFY=&SYSUID,CLASS=B,MSGLEVEL=(1,1),MSGCLASS=X   
//STEP1 EXEC PGM=IKJEFT01                                           
//SYSTSPRT DD SYSOUT=*                                             
//SYSPRINT DD DSN=G58693.DB2.PRACTICE.SQL1.Q2,DISP=SHR             
//SYSIN    DD DSN=G58693.DB2.PRACTICE.SQL1(Q1),DISP=SHR             
//SYSTSIN  DD *                                                     
  DSN SYSTEM(DSN1)                                                 
/*
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 10, 2009 2:39 pm    Post subject:
Reply with quote

you have not shown us any sql.
any of the output, which will be many dsid's.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Jun 10, 2009 3:00 pm    Post subject:
Reply with quote

Refer this sample

Code:

//STEP001 EXEC PGM=IKJEFT1B
//SYSTSPRT DD  SYSOUT=*                                       
//SYSTSIN  DD  *                                             
 DSN SYSTEM(db2subsystem name)                                             
 RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -         
 LIB('db2 load lib')                                     
/*                                                           
//SYSPRINT DD  SYSOUT=*                                       
//SYSIN DD  *                                                 
your query
/*                                                           
//SYSPUNCH DD  SYSOUT=*
//SYSREC00 DD  DSN=datasetname,DISP=(NEW,CATLG,DELETE)
Back to top
View user's profile Send private message
MFSubbu

New User


Joined: 16 Feb 2008
Posts: 19
Location: Bangalore

PostPosted: Wed Jun 10, 2009 3:05 pm    Post subject:
Reply with quote

My Sql is present in G58693.DB2.PRACTICE.SQL1(Q1) pds which is a simple select statement, when i execute thru spufi i am able to get 120 rows.. but when i execute the same through jcl i am getting blank ps file as output.

I dont have any idea about the program DSNTIAUL. when i tried giving that in my jcl i got the error DSNTIAUL not found in loadlib.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10231
Location: italy

PostPosted: Wed Jun 10, 2009 3:10 pm    Post subject: Reply to: Executing SQL through JCL
Reply with quote

from the info posted Your SYSTSIN data does not contain any RUN command,
so no program is being executed

You are simply connecting to a DB2 subsystem and nothing more
Back to top
View user's profile Send private message
MFSubbu

New User


Joined: 16 Feb 2008
Posts: 19
Location: Bangalore

PostPosted: Tue Jun 16, 2009 11:21 am    Post subject: Reply to: Executing SQL through JCL
Reply with quote

Finally i managed to find the loadlib and my jcl ran fine. but i am not getting where to find the output.

In my sysprint i am getting the following message

DSNT490I SAMPLE DATA UNLOAD PROGRAM
DSNT505I DSNTIAUL OPTIONS USED: SQL
DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
DSNT504I UNLOAD DATA SET SYSPUNCH BLOCK SIZE SET TO 27920
DSNT503I UNLOAD DATA SET SYSREC00 RECORD LENGTH SET TO 163
DSNT504I UNLOAD DATA SET SYSREC00 BLOCK SIZE SET TO 27873
DSNT495I SUCCESSFUL UNLOAD 1 ROWS OF TABLE G58688.EMPLOYEE

I am getting 1 row of table is fetched but where to see that row. Please advise
Back to top
View user's profile Send private message
vinit_infy
Warnings : 1

New User


Joined: 07 Apr 2005
Posts: 56

PostPosted: Tue Jun 16, 2009 11:42 am    Post subject:
Reply with quote

check the sysrec00.
Back to top
View user's profile Send private message
MFSubbu

New User


Joined: 16 Feb 2008
Posts: 19
Location: Bangalore

PostPosted: Tue Jun 16, 2009 12:50 pm    Post subject:
Reply with quote

Thanks Everyone for ur help on this.... icon_smile.gif
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 216
Location: India

PostPosted: Tue Jun 16, 2009 2:55 pm    Post subject: Re: Reply to: Executing SQL through JCL
Reply with quote

MFSubbu wrote:
Finally i managed to find the loadlib and my jcl ran fine. but i am not getting where to find the output.

In my sysprint i am getting the following message

DSNT490I SAMPLE DATA UNLOAD PROGRAM
DSNT505I DSNTIAUL OPTIONS USED: SQL
DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
DSNT504I UNLOAD DATA SET SYSPUNCH BLOCK SIZE SET TO 27920
DSNT503I UNLOAD DATA SET SYSREC00 RECORD LENGTH SET TO 163
DSNT504I UNLOAD DATA SET SYSREC00 BLOCK SIZE SET TO 27873
DSNT495I SUCCESSFUL UNLOAD 1 ROWS OF TABLE G58688.EMPLOYEE

I am getting 1 row of table is fetched but where to see that row. Please advise


Subbu could you post your jcl ?
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Tue Jun 16, 2009 3:37 pm    Post subject: Reply to: Executing SQL through JCL
Reply with quote

Hi Sambhaji

The program DSNTIAUL is meant for UNLOADing table. DDname SYSREC00 will hold the unloaded data, SYSPUNCH will hold the control statement for loading data to the same table. SYSPRINT DD SYSOUT=*
will hold the message generated by the program, not the real output of the SQL executed.

To execute all types of SQL - SELECT, UPDATE, DELETE, INSERT, ALTER, CREATE, DROP.... - you have to use DSNTEP2/DSNTEP4 program.

In this program the DDname SYSPRINT will hold the output of the SQL executed.

Regards
Raghu
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 Executing OO COBOL program invoking J... Virendra Shambharkar COBOL Programming 2 Tue Jan 10, 2017 6:37 pm
No new posts Viewing executing process in NDM .. q... Debb.Brant IBM Tools 0 Wed Dec 14, 2016 10:55 pm
No new posts Easytrieve - Report writing - Only 1 ... Abhi Nature CA Products 2 Wed Sep 14, 2016 11:40 am
This topic is locked: you cannot edit posts or make replies. Executing JCL specific steps at speci... Shruti Takkar JCL & VSAM 6 Mon Feb 22, 2016 12:39 pm
No new posts Error executing command TSO OMVS - Cr... sirbaros TSO/ISPF 5 Mon Oct 26, 2015 8:26 pm


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