Re: RUNSQL Command: Help & Escape Messages

Chuck

Because of some issues a customer had, I found that STRQMQRY does some extra stuff, beyond what the QM commands alone would do.

The main one is, it often or always creates a commitment definition. And it does not remove it when processing is done. THAT is what caused a problem when the customer created their own commitment definition later.

Using the QM commands themselves gets around this - no commitment definition unless you explicitly ask for it.

Also, if using the internal QM stuff, you can have up to 999 substitution variables - not that anyone is crazy enough to do that, eh?

As to CREATE VIEW & RUNQRY - again, that is 2 steps for the user - whereas using what all of us who have written - an EXCSQL or some such - it is one step to get the results of a SELECT displayed.

That's all I want from both RUNSQLSTM and the new RUNSQL.

Regards
Vern

On 11/16/2012 5:38 AM, CRPence wrote:

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.

<<SNIP>>

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.

<<SNIP>>

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].