IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Executing SQL through JCL


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Jun 10, 2009 3:10 pm
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
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
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
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: 218
Location: India

PostPosted: Tue Jun 16, 2009 2:55 pm
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: 193
Location: chennai

PostPosted: Tue Jun 16, 2009 3:37 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts executing XCTL command in COBOL witho... CICS 10
No new posts Receiving RC 5 when executing REXX pr... CLIST & REXX 3
No new posts Issues with executing a REXX MACRO th... TSO/ISPF 4
No new posts Get name of Subsystem routine is exec... All Other Mainframe Topics 2
This topic is locked: you cannot edit posts or make replies. Control-m JOB executing even when the... Compuware & Other Tools 6
Search our Forums:

Back to Top