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
 

 

use the result from a query to run a member in pds

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CLIST & REXX
View previous topic :: :: View next topic  
Author Message
tech4infi

New User


Joined: 10 May 2010
Posts: 7
Location: USA

PostPosted: Mon May 10, 2010 11:34 pm    Post subject: use the result from a query to run a member in pds
Reply with quote

I'm running a QMF REXX proc from a job. This proc reads a dataset and use that value to run a query. The query returns a few columns, col1, col2, col3. The value of col3 corresponds to member in a PDS.

col3 can have values daily, weekly, monthly, quarterly & yearly. Based on the value, I need to look up the PDS and run the update query stored in that member.

I'm not sure how to set the value of col3 in my REXX proc. I think I can look up other post to find the member in PDS.

Thanks
Back to top
View user's profile Send private message

superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Mon May 10, 2010 11:46 pm    Post subject: Reply to: use the result from a query to run a member in pds
Reply with quote

I don't understand the question. icon_cry.gif
Back to top
View user's profile Send private message
tech4infi

New User


Joined: 10 May 2010
Posts: 7
Location: USA

PostPosted: Tue May 11, 2010 12:16 am    Post subject:
Reply with quote

Hi,

Thanks for a speedy response.

I've a JCL which run a proc using a input file
//JS010 EXEC QCPDQMF,
// SYSTEM=DB2S, DB2 SUBSYSTEM
// IEBGCND=0, DO NOT WRITE TO SYSOUT
// OUTDSP1='MOD',
// OUTDSN='USERID.PRINT' REPORT DSN
//PS010.PARMIN DD DISP=SHR,DSN=DDIN(memname)
//PS010.SYSTSIN DD *
ISPSTART PGM(DSQQMFE) PARM(M=B,S=DSNJ,I=userid.proc)
//*

userid.proc
ADDRESS "TSO" "EXECUTIL TS"
TRACE ?R
ADDRESS "TSO" "EXECIO 1 DISKR PARMIN (FIFO FINIS"
PARSE UPPER PULL KEY .
ADDRESS "QRW" "SET GLOBAL (field="key
ADDRESS "QRW" "RUN QUERY" "username.query"
IF DSQ_RETURN_CODE = DSQ_SEVERE | DSQ_RETURN_CODE = DSQ_FAILURE
THEN EXIT DSQ_RETURN_CODE

the query in the proc returns this data

Col1 Col2 Col3
1234 abcd daily

I want use the vaule of col3 to look up a member with same name in a pds and run the query in that member.

I've tried many ways (with my limited knowledge of rexx) to use (set) the value of col3 in my procc. But I'm not successful.

Thanks
Back to top
View user's profile Send private message
tech4infi

New User


Joined: 10 May 2010
Posts: 7
Location: USA

PostPosted: Tue May 11, 2010 3:18 am    Post subject: Re: Reply to: use the result from a query to run a member in
Reply with quote

superk wrote:
I don't understand the question. icon_cry.gif


Please let me know if me still unclear.
Back to top
View user's profile Send private message
superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Tue May 11, 2010 5:02 am    Post subject: Reply to: use the result from a query to run a member in pds
Reply with quote

I know nothing about QMF REXX. Where exactly are those values returned to (the terminal, a dataset, as variables)?
Back to top
View user's profile Send private message
tech4infi

New User


Joined: 10 May 2010
Posts: 7
Location: USA

PostPosted: Tue May 11, 2010 8:03 am    Post subject:
Reply with quote

Hi kevin,

The values are returned to terminal, we can also save the data as a temp table.

I've gone through QMF manual as well as REXX manual, but cannot find something useful.

please help me.

Thanks
Back to top
View user's profile Send private message
tech4infi

New User


Joined: 10 May 2010
Posts: 7
Location: USA

PostPosted: Tue May 11, 2010 8:28 am    Post subject:
Reply with quote

just to clarify, by terminal I mean QMF report panel
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Tue May 11, 2010 9:21 am    Post subject:
Reply with quote

If the query result is stored in OUTDSN, then your rexx program should read this file and analyse the result.

Also, does it have to be batch QMF? You could run the query from within the rexx.
Back to top
View user's profile Send private message
tech4infi

New User


Joined: 10 May 2010
Posts: 7
Location: USA

PostPosted: Tue May 11, 2010 10:38 am    Post subject:
Reply with quote

Marso wrote:
If the query result is stored in OUTDSN, then your rexx program should read this file and analyse the result.

Also, does it have to be batch QMF? You could run the query from within the rexx.


Thanks for response.

Yes, I've to run a batch QMF only. I'm linking multiple application. I've a bat file to do this, which FTP's a file (created by another application) to mainframe as a PS. Then the bat file submits this job which executes the proc.

This proc reads the input from the file and pass it to the query.

Based on the result from the query, I need to run a update query stored in a PDS and then FTP the result, which will be used by another application as input.


I can export the result of the query to a OUT DSN, but the data is exported in IXF format, which will include the column declaration along with the data retrieved.

Then I think I've to use the sort within rexx to get the value of col3 available to rexx.

But I'm looking if there is more simpler way to use the value of col3 returned from the query.
Back to top
View user's profile Send private message
Pedro

Senior Member


Joined: 01 Sep 2006
Posts: 2019
Location: Silicon Valley

PostPosted: Tue May 11, 2010 7:09 pm    Post subject: Reply to: use the result from a query to run a member in pds
Reply with quote

I think the current suggestion is to get your QMF process to put its output to a file and then to read the file from the rexx.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Tue May 11, 2010 8:15 pm    Post subject: Reply to: use the result from a query to run a member in pds
Reply with quote

From what I found when I was running QMF batch (back in 2003):

In your QCPDQMF procedure, there should be a DD card for DSQPRINT. It may look like this:
Code:
//DSQPRINT DD    SYSOUT=*,DCB=(RECFM=FBA,LRECL=133,BLKSIZE=1330)

You have to override it with a DSN, which may look like this:
Code:
//stepname.DSQPRINT DD  DISP=(NEW,PASS),UNIT=SYSDA,SPACE=(TRK,(10,1)),DSN=&&QMFOUT


Then, in the rexx step, you need only have another DD for the same file:
Code:
//QMFOUT    DD   DISP=(OLD,DELETE),DSN=&QMFOUT


The report certainly contains header lines, maybe there is a line with '---------' before the dataline.
Read the file with EXECIO, search for the separator line, then get your data.

Without seeing the job procedure , the QMF procedure and the QMF output, there is a lot of guessing in my reply...
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Tue May 11, 2010 8:28 pm    Post subject:
Reply with quote

I had another close look at your 1st post.
No doubt your QCPDQMF procedure has:
Code:
//DSQPRINT DD  DISP=SHR,DSN=&OUTDSN

So, all you need in the rexx step is to have a DD for the same file:
Code:
//QMFOUT    DD   DISP=SHR,DSN=USERID.PRINT  <= your OUTDSN parameter

The following is still correct:
Marso wrote:
The report certainly contains header lines, maybe there is a line with '---------' before the dataline.
Read the file with EXECIO, search for the separator line, then get your data.

Without seeing the job procedure , the QMF procedure and the QMF output, there is a lot of guessing in my reply...
Back to top
View user's profile Send private message
tech4infi

New User


Joined: 10 May 2010
Posts: 7
Location: USA

PostPosted: Tue May 11, 2010 10:59 pm    Post subject:
Reply with quote

Thank You All.

I'll try the suggestion.

I think I'll change the queries in the PDS to QMF queries.
It will be easier to build the query name and run it rather then finding the meber in PDS and finding a way to get the query from the the pds member.

Thanks
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Wed May 12, 2010 1:19 am    Post subject:
Reply with quote

tech4infi wrote:
I think I'll change the queries in the PDS to QMF queries.
It will be easier to build the query name and run it rather then finding the meber in PDS and finding a way to get the query from the the pds member.
Whatever. I just hope you are not as confused as I am now! icon_confused.gif
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 -> CLIST & REXX All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 6 Fri Nov 18, 2016 1:48 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


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