On 01-Nov-2011 08:51 , Emily Smith wrote:
Here's my query (field and file names obfuscated due to company
policy).
SELECT FIELD1, FIELD2, FIELD3, FIELD4
FROM FILE1
LEFT OUTER JOIN FILE2
ON FIELD1 = FIELDA
ORDER BY FIELD1 ASC, FIELDB ASC
Prior to upgrading this was run from a CLLE program using the
STRQMQRY command and used 'FILE1' and 'FILE2' from the user's
library list. Since upgrading (which I know may just be coincidence),
the STRQMQRY command fails. A check of the user's job log reveals
the following:
Actual STRQMQRY command string invoked, was omitted.?
FILE1 in WEIRDLIB type *FILE not found
RUN QUERY command failed with SQLCODE -204.
The full details of the first message might be worthwhile, to know
exactly what code detected the condition and which code had requested
the failing code to perform that check. DSPJOBLOG OUTPUT(*PRINT)
generates all of the details of a message, as does F6=Print from the
F1=Help on the message; the latter gives no context within nor any other
messages, but probably not necessary for the first error. A trace might
even be more revealing.
What I can't figure out is where WEIRDLIB is coming from. It isn't
hard-coded in the query. The library does exist, but is not anywhere
in the user's library list. I've also verified that WEIRDLIB isn't
set as the user's CURLIB.
The name would be the /authorization ID/ [identifier], the user
profile name, or the CURRENT SCHEMA for SQL naming for unqualified TABLE
references. The name should be the library list or the CURRENT SCHEMA
for system naming. When the file name is a "long name", the name is
looked-up from the system database cross-reference; from the library
name according to the naming and the current schema or library list. I
am unsure of how the CURRENT SCHEMA could get set for QMQRY activity
however; SQL activity at a prior call level would in my experience,
cause a QM query to fail, not adopt the attributes of a prior and
currently active dynamic SQL environment.
If I run the STRQMQRY command myself from a command line it also
fails trying to find FILE1 in WEIRDLIB.
While a QMQRY is effectively "purely dynamic" regardless that the
statement is "compiled" into an object first to syntax check and then to
enable variable replacement, there is [still AFaIK] no access plan saved
as part of that object type. However the resolved statement string
could be compared to the cached SQL statements, and then run using a
stored plan. The library list for system naming is required to be
compared between the job and the stored query before running with that
plan, because the plan has already "resolved" the names to pointers. If
the QM user profile in conjunction with the compiled source does not act
as a dynamic query, there is probably a defect. While the "source" is
referred to above, a request to RTVQMQRY into a temporary source member
to compare to ensure they match might be worthwhile; i.e. unclear
whether the given source is the retrieved source, or just the
presumed-to-be the source for the compiled *QMQRY object.
However, it runs to completion with no errors if I run the query
using option 9 from 'Work With Query Management Queries'.
That seems very odd, since WRKQMQRY is nothing more than a UIM panel
group invoking the STRQMQRY command rather than the directly from a
command line. That [as for any] command may adjust the "product
library" list or the "current library", but I would not expect the
DSPCMD WRKQMQRY to indicate anything other than *NOCHG [no change] for
both. I would be less surprised if the run option from within STRQM had
given a different result... since that command builds the QM run-time
environment.
What am I missing?
The answer to what is the origin for the error ;-)
Regards, Chuck
midrange.com
