Re: SQL0901 Error in V7R1, but not V6R1

On 21-Jun-2011 10:16 , Needles,Stephen J wrote:

We are performing some tests on our V7R1 box in preparation for our
move from V6R1 and found an SQL in one of the programs that is
failing with a SQL0901 error.

Here is what I have. When I run the SQL outside of the code in
STRSQL, I get the SQL0901 error. The joblog looks like:

Pointer not set for location referenced.

Relevant message details are available in the spooled joblog, not in the interactive scripting area. The above text for instance apparently represents first level text of the error message MCH3601. Those details enable generating keyword symptom strings which can be utilized for better searches in looking for a corrective fix; i.e. to locate a published fix that documents the same failure details.

*** DBOP QRO creation FAILED! ***.
Internal failure occurred in query processor.

The message identifier plus relevant details of the context for which each of the above three messages was issued will appear in the spooled joblog or F6=Print after F1=Help on each individual message; generally easiest to collect as one spool file using CHGJOB LOG(4 0 *SECLVL) followed DSPJOBLOG OUTPUT(*PRINT).

Internal failure occurred in query processor.
Ownership of object GPTFLST in QTEMP type *USRSPC changed.
SQL system error.

The table is a Join Logical File with some select/omit logic and 5
tables.

The SQL looks like:

SELECT Field1, Field2 from LIB/JOINLOG where Field3 = '12345'

Both of the CPF4204 errors look like: <ed>
"Internal failure occurred in query processor. QDT 1.
*If the problem continues, report the problem (ANZPRB command).*

The expanded SQL0901 error shows: <ed>
"An SQL system error has occurred. The current SQL statement cannot
be completed successfully. The error will not prevent other SQL
statements from being processed. Previous messages may indicate that
there is a problem with the SQL statement and SQL did not correctly
diagnose the error. The previous message identifier was CPF4204.
Internal error type 3107 has occurred."

An "internal error" with the OS query processor\engine. Might be interesting to verify if the following request functions well or fails similarly:
OPNQRYF LIB/JOINLOG FORMAT(hasFeild1andField2) QRYSLT('Field3="12345"')

What is the 'error type 3107'? I don't recall seeing it before.

That RC is not really relevant to an end user; probably means "the call to the query engine failed". Additionally, the implication of that message is that a prior failure occurred. Specifically that the query engine has failed catastrophically, for which possibly the only correction is via a PTF.

The -901 suggests that SQL considers the failure to have been prior; i.e. the msgCPF4204 which is a generic "query processor failure". That error is the direct result of a prior msgMCH3601 issued to the unstated program, procedure, and statement. What is probably an impromptu [which should be all upper case] message "DBOP QRO creation FAILED!" gives the OS database query developers a hint as to the origin of the problem, beyond just the failing instruction details [which unlike a copy of an interactive joblog like that given in the quoted message, a spooled joblog would show].

However the query probably could be rewritten to avoid the failing code path, likely circumventing the problem, by replacing the JLF with the equivalent in SQL; either in a VIEW or directly in the SELECT query. But with a circumvention implemented, and that error left unreported, the defect would remain for others to encounter.

Regards, Chuck