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
 

 

Doubt in QMF Query Execution

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

New User


Joined: 13 Feb 2008
Posts: 16
Location: Chennai

PostPosted: Mon Jun 29, 2009 2:24 pm    Post subject: Doubt in QMF Query Execution
Reply with quote

Hi All,
I have two QMF BATCH steps in a JCL. Both the steps executes the same SQL query but the output is formatted using two different forms.
Since the query is complex and it takes lots of CPU time, is it possible to execute the SQL query only once and then map the output to two different forms?

Current scenario:
Proc 1:
RUN QUERY1 (F=FORM1

Proc 2:
RUN QUERY1 (F=FORM2

Thanks in advance,
Anu
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Jun 29, 2009 3:29 pm    Post subject:
Reply with quote

your can refer here to all qmf documents and pick the one for your version. You can download the PDF's and have them readily accessible on your PC.
Back to top
View user's profile Send private message
Anu Vijayaraghavan

New User


Joined: 13 Feb 2008
Posts: 16
Location: Chennai

PostPosted: Tue Jun 30, 2009 10:42 am    Post subject:
Reply with quote

Thanks for the document references. I did go through a PDF but I was not able to find any references to the requirement which I have posted above.

One reference was little closer to my requirement where different forms are used based on conditional statements. But here I don't have any conditions. I just want to execute a query once and map the output to two different layout and hence two different forms.

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

Site Director


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

PostPosted: Tue Jun 30, 2009 7:08 pm    Post subject:
Reply with quote

Hello,

Have you tried running both at the same time? What happened?

How large is the amount of data selected? You might run the query once to create a data file and then report from that data file.
Back to top
View user's profile Send private message
Anu Vijayaraghavan

New User


Joined: 13 Feb 2008
Posts: 16
Location: Chennai

PostPosted: Tue Jun 30, 2009 9:04 pm    Post subject: Reply to: Doubt in QMF Query Execution
Reply with quote

i am running the two queries in a sequence. Each instance of the query takes 6 minutes to run. How is it possible to run the same query twice simultaneously.

I can create a data file from the output of the first query. but how to use the data file to create a report.

Proc 1:
RUN QUERY1 (F=FORM1
SAVE the output as DATA-FILE1

Now how to use this data file with form2 to create the second report
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Tue Jun 30, 2009 9:33 pm    Post subject:
Reply with quote

Is there a reason you posted this same question on another board only a day after being assisted on this one?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jun 30, 2009 9:40 pm    Post subject:
Reply with quote

Obviously no consideration has been made to either consolidate these "2" reports - on the same data - or a restructuring of the reports.

I do not think that there is a way to generate 2 reports from one query,
since qmf is REPORT/FORM oriented.

If one group must have the data as abc and another as cba, then you have two REPORTS/FORMS - two executions.

Source of data for a qmf query is from db2 tables.
Maybe DATA-FILE1 could be inserted into a temp table and then the 2nd query/report run.
Whether loading a temp table and running the query would be 'quicker' than
re-selecting from the base table - only testing would prove this.
One thing for sure, the detail of the reports would be the same,
since they would be based on exactly the same data
(i.e., no base db2 table modifications between the two executions)

Or load an empty 'report' table (INSERT into report table Select from base table)
first,
then run both qmf queries against the report table.

Should not be too hard to bench mark.

Terry wrote:
Is there a reason you posted this same question on another board only a day after being assisted on this one?

In all fairness, he did wait a day (after he received no answers that said yes he could do this)
instead of the usual 2 minutes as most double posters
Back to top
View user's profile Send private message
Anu Vijayaraghavan

New User


Joined: 13 Feb 2008
Posts: 16
Location: Chennai

PostPosted: Thu Jul 02, 2009 5:50 pm    Post subject:
Reply with quote

thanks for all the help. it worked icon_biggrin.gif
the new query just took 1 second to execute while the old query took 45 seconds.
Please find below the usage:
Proc 1:
RUN QUERY1 (F=FORM1
SAVE DATA AS TEMP_TABLE

Proc 2:
RUN QUERY2 (F=FORM2

Query2:
SELECT * FROM TEMP_TABLE
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 02, 2009 6:34 pm    Post subject:
Reply with quote

great solution Anu Vijayaraghavan.

I have learned something and will remember for future reference.
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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