Re: MCH0601 on SQL Insert

Lim Hock-Chai wrote:

I changed it to use RPG IO simply due to project dead line. SQL
is wonderful when it works. But when it goes wrong like the one
I see, it becomes a blackbox and is hard to figure out.

Here are some more detail regarding the MCH0601 (Let me know if
you need other info):

1) These blow of SQL codes are running in a Service program.
2) The compiler listing looks like below:
//---------------------------------------------------------
// copy the EMAILMSG member to a temporary note file in IFS
//---------------------------------------------------------
begsr cpyEmailMsgMbrToNoteFileInIFS;

//create a temp work file QTEMP/SVRMLTMP, to house message
//-------------------------------------------------------
//**** sql
//****rop table QTEMP/SVEMLTMP;
/END-FREE
C Z-ADD 4 SQLER6
C CALL SQLROUTE
C PARM SQLCA
/FREE
//**** sql
//****reate table QTEMP/SVEMLTMP
//**** (TEXT VARCHAR (1000 ) NOT NULL WITH DEFAULT);
/END-FREE
C Z-ADD 5 SQLER6
C CALL SQLROUTE
C PARM SQLCA
/FREE

//copy the EMAILMSG member to a temp work file
//------------------------------------------------------
myCmd = 'OVRDBF FILE(EMAILMSG) TOFILE(EMAILMSG) MBR(' +
%trim(piMbr) + ') OVRSCOPE(*CALLLVL)';
qcmdexc(myCmd :%len(%trimr(myCmd)));
//**** sql insert into QTEMP/SVEMLTMP
//**** select rtrim(SRCDTA) FROM EMAILMSG;
/END-FREE
C Z-ADD 6 SQLER6
C CALL SQLROUTE
C PARM SQLCA
/FREE

3) The relevent joblog looks like below:
ODP created.
Blocking used for query.
Cursor SQLCURSOR000000003 opened.
1 rows fetched from cursor SQLCURSOR000000003.
1 rows fetched from cursor SQLCURSOR000000003.
1 rows fetched from cursor SQLCURSOR000000003.
1 rows fetched from cursor SQLCURSOR000000003.
1 rows fetched from cursor SQLCURSOR000000003.
Space offset X'0003F1D3' or X'0000000000000000' is outside
current limit for object MSRL3G343APALHC 345777.
<<SNIP two more mch0601>>
1 rows fetched from cursor SQLCURSOR000000003.
<<SNIP 3 more rows fetched with similar mch0601 symptoms>>
ODP deleted.
Cursor SQLCURSOR000000003 was closed.
DESCRIBE of prepared statement SQLSTATEMENT000003 completed.
PREPARE of statement SQLSTATEMENT000003 completed.
Drop of Q0T467 in QTEMP complete.


4) When I press F1 then F9 on the MCH0601, I got below:

Message ID . . . : MCH0601 Severity . . : 40
Message type . . . . . : Diagnostic
Date sent . . . : 05/14/10 Time sent . . : 08:25:31
Message . . : Space offset X'FFFFB13E' or
X'0000000000000000' is outside current limit for
object MSRL3G343APALHC 345777.
Cause . . : A program tried to set a space pointer,
tried to use storage outside a space, or tried to use
an unallocated page in teraspace.
The space class is X'04'. The space class designates the
type of space: <<SNIP 00 to 03>>
04-implicit process space for heap identifier X'00000000'
in activation group mark X'0000000000000000'.

Message ID . . . : MCH0601 Severity . . : 40
Date sent . . . : 05/14/10 Time sent . . : 08:25:31
Message type . . . . . : Diagnostic
From . . . . : PALHC CCSID . . . : 65535 From program . . . . . . . . . : bzeroeao_Gennaker
Instruction . . . . . . . . : 00000C
To program . . . . . . . . . . : QCPEXPRT
To library . . . . . . . . . : QSYS
To module . . . . . . . . . : QCPEXPRT
To procedure . . . . . . . . : Copy_The_Records
To statement . . . . . . . . : 482

"CRPence" wrote:

So are the message details for the SQL cursor, SQL FETCH, and MCH0601 a secret, or does the /circumvention/ by avoiding the
SQL just render that issue moot, such that there is no longer
any interest in knowing what might have been the problem?

Lim Hock-Chai wrote:

These code are running in a service program. Not sure if this
would place a role in the problem. But anyway, I replaced that
whole sections of code with RPG IO and it works fine now.

Here is the RPG replacement version:
//create a temp work file, QTEMP/SVRMLTMP,
// to house the message
//------------------------------------------------
myCmd = 'DLTF FILE(QTEMP/SVEMLTMP)';
callp(e) qcmdexc(myCmd :%len(%trimr(myCmd)));
myCmd = 'CRTPF FILE(QTEMP/SVEMLTMP) RCDLEN(300)';
qcmdexc(myCmd :%len(%trimr(myCmd)));
open SVEMLTMP;

//copy the EMAILMSG member to a temp work file
//-------------------------------------------------
myCmd = 'OVRDBF FILE(EMAILMSG) TOFILE(EMAILMSG) MBR(' +
%trim(piMbr) + ') OVRSCOPE(*CALLLVL)';
qcmdexc(myCmd :%len(%trimr(myCmd)));
open EMAILMSG;
setll *start EMAILMSG;
read EMAILMSG myMsgRec;
dow not %eof();
mySVEMLTMP.data = myMsgRec.srcdta;
write SVEMLTMP mySVEMLTMP;
read EMAILMSG myMsgRec;
enddo;
close SVEMLTMP;

The details from the Cursor opened and Fetch would best have been included also. As I had suggested, those messages seemed unrelated to a non-cursor implemented INSERT INTO SELECT FROM, so they would be of interest as well. No matter, since the details of the MCH0601 probably are sufficient to suggest that they are not directly representative of the SQL in the application.

The symptom string for the MCH0601 error:
msgMCH0601 F/bzeroeao_Gennaker x/00000C
T/QCPEXPRT Tm/QCPEXPRT TP/Copy_The_Records stmt/482

That indicates that the error being logged, and presumably [most assuredly] also the logged cursor & fetch details preceding those errors, are the result of a request to perform CPYTOIMPF in that job. Thus to find the origin of the errors being logged, determine what invoked the database export [copy] data request.

Output from TRCJOB for its call /flow/ will show what is calling the export.

Warning: most will find reading any more to be a waste of time:

Note: without trace details it is difficult to know for sure, but since the error is logged as a diagnostic, it is possible that the condition is being monitored & handled by the export procedure.

Note: since development rarely seemed to use proper symptom string keywords, finding a preventive will not be as easy as searching only on the above [omitting instruction & statement details] complete symptom string or most or all of the keywords. Success in locating an APAR\PTF would likely require searching on variations of the symptom keywords, which means poring over many hundreds of unrelated search-hits.

FWiW, from an active joblog Display Message, for lack of a panel showing "consolidated information", such that two effective print screens [or text copies] are required to see\obtain all of the relevant information, the F6=Print does present all the information in one location. The spooled joblog also presents a consolidated view.

<rant>
I use USROPT(*EXPERT). Is it so much to expect that the view of the joblog message help [i.e. display message] screen would give an /expert/ consolidated view to avoid using F9=Display Message Details!? And still with the stupid F3=F12=Enter to /return/ [/cancel/, in i vernacular]. When I press F3, why not /Exit/ from DspMsg completely! Argghh!
</rant>

Regards, Chuck