MIDRANGE dot COM Mailing List Archive

Home » MIDRANGE-L » November 2012

Re: RUNSQL Command: Help & Escape Messages


On 15 Nov 2012 18:00, Vernon Hamberg wrote:

I have a feeling that the use of temp outputs is not limited to QMQRY
- in STRSQL and set to put output to a file, I have to think it does
the same thing. I can't swear by that, but it makes some sense, and I
think I've seen it.


On 11/15/2012 4:50 PM, Sam_L wrote:

Virtually all the homegrown products use QMQRY under the covers.
(Yes, I have one as well.)

If you run QMQRY to an outfile, I think you'll find that it
processes the result set twice, once to put it to a temp file, then
a second time to put it to your outfile. At least it used to work
this way and it could be significant overhead if you had a large
result set. You can see it happening if you watch the open files
with a large result set.

That's why I mentioned the create table as (select ...) approach.


My preference is to use CREATE VIEW QTEMP/somename AS (select ...) and then issue a RUNQRY () QTEMP/somename, but the VIEW has the limitation that ALIAS references are not allowed. This has the effect of asking for an OPNQRYF OPTIMIZE(*FIRSTIO) for the RUNQRY query ODP, thus possibly maintaining the effect of a SELECT query rather than a performing the full data copy of the result set.

As I recall the QM Query STRQMQRY feature generated a result set in a temporary table [naming QQXTEMP## in QTEMP] and then for its OUTFILE processing would perform a QM SAVE DATA statement to copy that data to the final location. That would have been as a side effect of the design that allows multiple QM FORM specifications to be applied to the same query result. I recalled that being the implementation of the STRQMQRY OUTPUT(*OUTFILE) and know for sure that is the case for the STRQM and QMPROC equivalent where the effects are from two distinct QM statements.

That recollection would be consistent with the observation by Sam that the result set is processed twice. The first being the query to generate the result set and store the data in the QQXTEMP## file, and the second as an INSERT INTO "OutFile" SELECT * FROM QTEMP/QQXTEMP## file to get the result set data into the Output File.

Yet a quick test [on v5r3] with debug messages active did not show the same set of messages for the STRQMQRY as for the equivalent QM statements of RUN QUERY followed by SAVE DATA AS, so I am not so sure. It is reasonable to expect that for performance reasons the STRQMQRY would avoid the temporary file because that command offers no opportunity to re-access the QM "DATA" set again; i.e. there is just one query and the one QMFORM, for the one command invocation of STRQMQRY. Perhaps originally and for some releases since, the code for STRQMQRY was implemented using the combined effects of the QM RUN QUERY and QM SAVE DATA AS, but in order to remove the obviously unnecessary data copy, the performance was improved by eliminating that unnecessary work.?

In contrast, the STRSQL has always used the Query/400 report writer for its OUTFILE processing [except for remote database connections, for which the QM Query support is used {first}] which has never used a separate database file for its result set; instead, processing the data directly from the query ODP.

In either case, STRQMQRY or STRSQL output file processing, the query implementation allows for temporary query results using file names that start with an asterisk; e.g. to perform a sort of the data from the unsorted query ODP, implement a CTE, result of a subquery or NTE when non-live data is allowed. Query Engine temporaries are actual /temporary/ objects, whereas the validly named QM /temporary results/ are /permanent/ objects in the QTEMP library [just like Global Temporary Table (GTT) are].

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact