Re: Embedded SQL no record retrieved error handling

[CRPence <CRPbottle@xxxxxxxxx>]

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