Re: Embedded SQL no record retrieved error handling

The error message MCH3402 which precedes the message SQL0901 which relates in text the SQLCODE -901, is the issue which needs to be resolved. The unexpected failure msgMCH3402 is causing the SQL to fail with the generic "SQL did not function correctly" [msgtext "SQL system error"] condition. I infer the error is issued T/QSQROUTE and that the iserror typ e 7949 [AKA RC7949]; the full spooled joblog detail was omitted, and for using the F1=Help versus that and the F6=Print or copying of both the primary and the F11=Alternate page of F1=Help data was omitted, plus the dump did not build a symptom string, difficult to know for sure. Anyhow that general failure is occurring before & instead of the expected "row not found" condition. After the preventive PTF or corrective PTF [& action] is applied, the problem should abate.

Trouble is, you need to report the problem first [what the msgSQL0901 should probably suggest to do, if after performing the provided recovery actions gives no relief] in order for the service provider to determine what is the defect which needs to be corrected; possibly by an existing PTF, possibly by correcting something with the system, or by some new PTF.

Regards, Chuck

John Allen wrote:

I just ran my program twice
Once the parm value was USR01 (record exists for this value)
Once with parm value USS01 (no record exists)
No recompiles, nothing changed just different value in parameter
No messages generated in QSYSOPR

Joblog for value USR01 (record exists for this value)
<<SNIP>>

Worked fine so my SQL statements should be correctly formatted
It retrieves the value I am looking for

Then I get:
Joblog for value USS01 (record does not exist for this value) Which should also be fine, I expect some values will not have a record in the database file. I just want to continue processing
without all these messages in the joblog and without a dump in
QEZJOBLOG

Unable to retrieve query options file.
PREPARE of statement W$MYSTMT completed.
Unable to retrieve query options file.
**** Starting optimizer debug message for query .
Access path of file SFDFT was used by query.
Arrival sequence access was used for file QSQPTABL.
**** Ending debug message for query .
ODP created.
ODP deleted.
VALUES statement complete.
Tried to refer to all or part of an object that no longer exists.
Ownership of object QP0Z236864 in QUSRSYS type *USRSPC changed.
File QAP0ZDMP in library QTEMP already exists.
User Trace data for job 236864/USR01/USR01A dumped to member
QP0Z236864 in file QAP0ZDMP in library QTEMP.
27 records copied from member QP0Z236864.
1 User Trace buffer(s) deleted. SQL system error.
The message above " Tried to refer to all or part of an object
that no longer exists" I use F1 and get the following (not so
helpful help):

Message ID . . . : MCH3402 Severity . . . . : 40
Message type . . : Escape
Date sent . . . : 02/17/10 Time sent . . . : 17:37:54
Message . . . . : Tried to refer to all or part of an object that
no longer exists.
Cause . . . . . : The most common cause is that a stored address
to an object is no longer correct because that object was deleted
or part of the object was deleted.

In addition to this joblog I get a dump in QEZJOBLOG

5722SS1 V5R1M0 010525 COPY FILE QTEMP/QAP0ZDMP
<<SNIP>>