Doubt in QMF Query Execution

Anu Vijayaraghavan · New User Joined: 13 Feb 2008 Posts: 16 Location: Chennai

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

dbzTHEdinosauer · Posted: Mon Jun 29, 2009 3:29 pm

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.

Anu Vijayaraghavan · New User Joined: 13 Feb 2008 Posts: 16 Location: Chennai

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

dick scherrer · Posted: Tue Jun 30, 2009 7:08 pm

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.

Anu Vijayaraghavan · New User Joined: 13 Feb 2008 Posts: 16 Location: Chennai

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

Terry Heinze · Posted: Tue Jun 30, 2009 9:33 pm

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

dbzTHEdinosauer · Posted: Tue Jun 30, 2009 9:40 pm

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.

Anu Vijayaraghavan · New User Joined: 13 Feb 2008 Posts: 16 Location: Chennai

thanks for all the help. it worked

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

dbzTHEdinosauer · Posted: Thu Jul 02, 2009 6:34 pm

great solution Anu Vijayaraghavan.

I have learned something and will remember for future reference.