Re: MCH0601 on SQL Insert -- RPG400-L

I change below (VARCHAR)
exec sql
create table QTEMP/SVEMLTMP
(TEXT VARCHAR (1000 ) NOT NULL WITH DEFAULT);

To (CHAR)
exec sql
create table QTEMP/SVEMLTMP
(TEXT CHAR (1000 ) NOT NULL WITH DEFAULT);

The the MCH0601 went away. I vote for IBM to rewrite that CPYTOIMPF
command :)

"Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx> wrote in message
news:<mailman.28018.1273853443.2580.rpg400-l@xxxxxxxxxxxx>...
Aahh, you are on to something. I seems to recall now that the CPYTOIMPF
command (Very useful but very buggy command) uses sql behind the scene
to do the work. And indeed, right after the insert statement, I use the
CPYTOIMPF to copy it to the IFS: Here is the complete code for that
sub-routine:

//--------------------------------------------------------------
// copy the EMAILMSG member to a temporary note file in IFS
//-------------------------------------------------------------
begsr cpyEmailMsgMbrToNoteFileInIFS;

//create a temp work file, QTEMP/SVRMLTMP, to house the message
//------------------------------------------------------------
exec sql
drop table QTEMP/SVEMLTMP;
exec sql
create table QTEMP/SVEMLTMP
(TEXT VARCHAR (1000 ) NOT NULL WITH DEFAULT);

//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)));
exec sql insert into QTEMP/SVEMLTMP
select rtrim(SRCDTA) FROM EMAILMSG;

//using CPYTOIMPF to copy the data to IFS
//------------------------------------------------------------
myCmd = 'CPYTOIMPF FROMFILE(QTEMP/SVEMLTMP) ' +
'TOSTMF(''/tmp/' + myNoteFile + ''') ' +
'MBROPT(*REPLACE) RCDDLM(*LF) STRDLM(*NONE) ' +
'STRESCCHR(*NONE) RMVBLANK(*TRAILING)';
qcmdexc(myCmd :%len(%trimr(myCmd)));

endsr;

"CRPence" <CRPbottle@xxxxxxxxx> wrote in message
news:<mailman.28000.1273851491.2580.rpg400-l@xxxxxxxxxxxx>...

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

----------