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

Running multiple queries in a PS file through JCL


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
nikhil_bansal04

New User


Joined: 16 Dec 2013
Posts: 12
Location: india

PostPosted: Wed Dec 18, 2013 12:06 pm
Reply with quote

Hello!

I have a PS file in which 'N' number of queries can be written. The layout of the file is this:

Code:
update
     TABLE-NAME1
set
     COL-NAME1 = NEW VALUE1
where
     PREDICATES1

update
     TABLE-NAME2
set
     COL-NAME2 = NEW VALUE2
where
     PREDICATES2


I need to run all these queries through set of JCLs and COBOL modules. I can use set of JCL and COBOL programs only but not run through SPUFI.
Can you please suggest the approach. Please help.

MY APPROACH:

Step 1: Splitting the file into N files, with each file containing one query.
Step 2 : Using each split files content as instream data to run a JCL.

Please suggest any alternative approach if u can think of.
Back to top
View user's profile Send private message
nikhil_bansal04

New User


Joined: 16 Dec 2013
Posts: 12
Location: india

PostPosted: Wed Dec 18, 2013 12:23 pm
Reply with quote

Can I use a dynamic query?
Foe examople, i read the file and extract one query
therafter i simple run the query dynamically?

Can that be done?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Dec 18, 2013 12:35 pm
Reply with quote

Hello,

One way to get what you want is to red this "query" file and generate the JCL and other control statements to run the query in batch. Then SUBMIT each new query job thru the Internal Reader.
Back to top
View user's profile Send private message
nikhil_bansal04

New User


Joined: 16 Dec 2013
Posts: 12
Location: india

PostPosted: Wed Dec 18, 2013 12:48 pm
Reply with quote

Thanks Dick!

But I read in this forum itself that it is not recommended to run a job through COBOL.
In any case,can you please share some documnet as to how to achive this technically.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Wed Dec 18, 2013 12:50 pm
Reply with quote

nikhil_bansal04 wrote:

I need to run all these queries through set of JCLs and COBOL modules. I can use set of JCL and COBOL programs only but not run through SPUFI.
If the queries are already in a file just ISPF cut/paste should suffice you to copy them to a COBOL program, no?

Second, if the file gets updates everyday ( basically you don't have control on the contents of it) but you're sure they will always be DB2 queris and nothing; probably you can define your file as a copybook member and copy that copy in your program?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Dec 18, 2013 12:55 pm
Reply with quote

look at the IDUG the COBOL equivalent of dsntep2
it is called CSNTEP2

www.idug.org/p/cm/ld/fid=149&tid=97&fileid=
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Dec 18, 2013 1:52 pm
Reply with quote

what's wrong with DSNTIAD ?
DB2 for z/OS Utility Guide and Reference wrote:
Use the DSNTIAD program to execute SQL statements other than SELECT statements dynamically.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Dec 18, 2013 1:56 pm
Reply with quote

well ... the buzzword is COBOL,
even if I do not understand the topic at all
if the COBOL program is an existing one... it should be modified to suit the requirement

if it does not exist...
the requirement should be reviewed to check if an already available/existing solution/program/utility can be used
Back to top
View user's profile Send private message
nikhil_bansal04

New User


Joined: 16 Dec 2013
Posts: 12
Location: india

PostPosted: Wed Dec 18, 2013 2:09 pm
Reply with quote

hello everyone,

the problem is in extracting one query at a time from the ps file and executing it.

Is there a way in which we can run multiple queries present in PS file via a JCL or COBOL module? means i get an input file with N queries which i want to execute...
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Dec 18, 2013 2:19 pm
Reply with quote

Quote:
DSNTIAD Input data set. In this data set, you can enter any number of non-SELECT SQL statements, each terminated with a semicolon. A statement can span multiple lines, but DSNTIAD reads only the first 72 bytes of each line.
Back to top
View user's profile Send private message
nikhil_bansal04

New User


Joined: 16 Dec 2013
Posts: 12
Location: india

PostPosted: Wed Dec 18, 2013 2:48 pm
Reply with quote

Thanks a lot GuyC. DSNTIAD is reaaly helpful but
Is there any way i can run multiple Non-selects which span more than 72 characters?
Back to top
View user's profile Send private message
nikhil_bansal04

New User


Joined: 16 Dec 2013
Posts: 12
Location: india

PostPosted: Wed Dec 18, 2013 4:08 pm
Reply with quote

THANKS A LOT GUYS for your help.
this is what i will be doing:

using dsntiad utility to execute multiple non-selects as instream data!

Have a great day! icon_biggrin.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Dec 18, 2013 7:25 pm
Reply with quote

Quote:
Thanks Dick!

But I read in this forum itself that it is not recommended to run a job through COBOL.
In any case,can you please share some documnet as to how to achive this technically.
You're welcome icon_smile.gif

I do believe i'd use GuyC's suggestion over mine.

However - where is it posted that it is not recommended to run a job thru COBOL? I believe there is some misunderstanding here that can be improved.
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Running REXX through JOB CLIST & REXX 13
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
Search our Forums:

Back to Top