Re: SQL System error in program

["Simon Coulter" <shc@xxxxxxxxxxxxxxxxx>]

Hello Glenn,

In general MCHxxxx messages from IBM code are APARable.  Check the 'from' and 
'to' programs in 
the joblog for the MCH0601 message.  If they begin with a 'Q' talk to IBM.

(The few that aren't are those where user code may have caused the MCH 
exception like "Receiver 
too small to hold result" which is usually caused by a variable being too 
small.  You need to 
check the 'from' and 'to' program names to know who to blame.)

Without knowing what your 'Sandbox' environment is, here is my best guess at 
the problem:
What has happened is that code in the job processing the SQL statement has 
walked off the end 
of an internal space object.  In this case it looks like the Process Control 
Space (*PCS) for 
job 825041/JLCOONS/ZWKS2097S5, but I'm guessing that based on its name.

Since QSQOPEN is likely the 'to' program I'd guess its something to do with 
creating the ODP.

Regards,
Simon Coulter.

«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
«» FlyByNight Software         AS/400 Technical Specialists       «»
«» Eclipse the competition - run your business on an IBM AS/400.  «»
«»                                                                «»
«» Phone: +61 3 9419 0175      Mobile: +61 0411 091 400           «»
«» Fax:   +61 3 9419 0175      mailto: shc@flybynight.com.au      «»
«»                                                                «»
«» Windoze should not be open at Warp speed.                      «»
«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
//--- forwarded letter -------------------------------------------------------
> MIME-Version: 1.0
> Date: Wed, 31 Mar 99 16:49:44 -0500
> From: Glenn-Cons.T.Geathers@am.pnu.com
> To: midrange-l@midrange.com
> Reply-To: MIDRANGE-L@midrange.com
> Subject: Re: SQL System error in program

> 
>   Can anyone give me some advise on this problem...all help would 
>   appreciated, regards Glenn
> 
> 
> ______________________________ Forward Header 
>__________________________________
>      
>   
>      I get the error whenever we execute program I60TSTU/PUR01A within the 
>      IRIS (KPO) Sandbox environment on the KZOP1 AS/400.  The appropriate 
>      way to execute this program is with:  CALL PUR01C.
>   
>      An excerpt of the job log shows the errors generated:
>   
>       Space offset X'00000000' is outside current limit for object    
>         ZWKS2097S5JLCOONS   825041.                                   
>       Function check. MCH0601 unmonitored by QSQOPEN at statement *N, 
>         instruction X'09D8'.                                          
>       Dump output directed to spooled file 2. SQL system error.       
>                                               
>   
>      The first error shown is "MCH0601", and the last one is "SQL0901".  A 
>      complete job log spoolfile, as well as the dump spoolfiles created, 
>      are available with job:  ZWKS2097S5/JLCOONS/825041.
>   
>      The error is always occurring when the following SQL statement is 
>      attempted (I pulled this from a PRTSQLINF output, so the H's represent 
>      different host variables within the program):
>   
>       SELECT SUM ( TQTY ) INTO : H : H FROM V_ITH AS ITH WHERE TLOT = : H 
>      AND TPROD =  
>           : H AND TID = 'TH' AND EXISTS ( SELECT CCCODE FROM V_ZCC WHERE 
>      CCTABL =      
>           'APINTER' AND CCID = 'CC' AND CCCODE = ITH . TTYPE )              
>   
>      Because this program updates a DB2 table, it runs in *SQL naming 
>      convention, rather than *SYS.  Even so, when we run this same command 
>      via interactive SQL, in *SQL naming convention, it processes 
>      successfully.
>   
>      Also of note is that the error occurs on random executions of this 
>      statement.  In our testing, this error has occurred different times on 
>      the 1st, 2nd, 3rd, or 4th execution of the above statement, with 
>      previous executions within the program's logic loop providing expected 
>      results.  In t
 example job mentioned above, it was the 4th execution 
>      that erred, based upon the PUR01P01 spool file showing 3 updates and 
>      then the -901 SQL code.  It's always the MCH0601 and SQL0901 errors 
>      that get logged in the job log, regardless of the iteration.
>   
>      
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---
> 

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---