I am encountering a problem parsing XML files that reach a certain limit. I am not sure yet if the problem is row count, node count or file size. So far, none of these files have exceeded 10MB. However, row counts are reaching the 70,000 range (with many of the rows being comments that constitute about 10% of the rows). Here is the error I am getting:
There error goes on a bit but I think these are the source errors. Here is how I am parsing the XML. Something to be mindful of is we are using a QNTC path on the iSeries over the network.
Another side note is intData has 77 fields and the XML file could contain 1,000 parent nodes.
Again, just curious what limit I am hitting: xml nodes/rows, file size, character count, data structure size.
PHP Code:
MCH0601 Escape 40 01/03/23 03:00:38.162172 QSQXPATH QSYS *STMT QSQXPATH QSYS *STMT
From module . . . . . . . . : DSNNQOPM
From procedure . . . . . . : SMSCREATEATOMICSEQUENCE
Statement . . . . . . . . . : 6872
To module . . . . . . . . . : DSNNQOPM
To procedure . . . . . . . : SMSCREATEATOMICSEQUENCE
Statement . . . . . . . . . : 6872
Message . . . . : Space offset X'00FFF010' or X'0000000000000000' is outside
current limit for object &1.
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'00'. The space class designates the type of space:
00-primary associated space (includes space objects). 01-secondary
associated space 0. 02-implicit process space for automatic storage.
03-implicit process space for static storage in activation group mark
X'0000000000000000'. 04-implicit process space for heap identifier
X'00000000' in activation group mark X'0000000000000000'. 05-constant space.
06-space for handle-based heap identifier X'00000000'. 07-teraspace offset
X'0000000000000000'. 08-teraspace for System i5 PASE memory address ​
X'0000000000000000'. Offset X'00FFF010' only applies to storage outside
teraspace. X'00000000000000000000000000000000' is a pointer to the teraspace
page or the start of the implicit process space for the allocation.
SQL0901 Information 50 01/03/23 03:00:38.171756 QSQSBAS2 QSYS *STMT QQQSVRTN QSYS *STMT
From module . . . . . . . . : QSQXMLTBLM
From procedure . . . . . . : QSQXMLTBLM
Statement . . . . . . . . . : 8583
To module . . . . . . . . . : QQINVUDF
To procedure . . . . . . . : QQCOMMONUDTF
Statement . . . . . . . . . : 16334
Message . . . . : SQL system error.
Cause . . . . . : An SQL system error has occurred. The current SQL
statement cannot be completed successfully. The error will not prevent
other SQL statements from being processed. Previous messages may indicate ​
that there is a problem with the SQL statement and SQL did not correctly
diagnose the error. The previous message identifier was MCH0601. Internal
error type 9001 has occurred. If precompiling, processing will not continue
beyond this statement. Recovery . . . : See the previous messages to
determine if there is a problem with the SQL statement. To view the
messages, use the DSPJOBLOG command if running interactively, or the WRKJOB
command to view the output of a precompile. An application program
receiving this return code may attempt further SQL statements. Correct any
errors and try the request again. ​
CPF503E Sender copy 30 01/03/23 03:00:38.172312 QDBIOERRQO QSYS *STMT QDBIOERRQO QSYS *STMT
From module . . . . . . . . : QDBIOERRQO
From procedure . . . . . . : SEND_MESSAGE
Statement . . . . . . . . . : 4319
To module . . . . . . . . . : QDBIOERRQO
To procedure . . . . . . . : SEND_MESSAGE
Statement . . . . . . . . . : 4319
Message . . . . : User-defined function error on member XMLTABLE.
Cause . . . . . : An error occurred while invoking user-defined function
XMLTABLE in library QSYS2. The error occurred while invoking the associated
external program or service program CommonUDTF in library QSYS, program
entry point or external name QQCommonUDTF, specific name *N. The error
occurred on member XMLTABLE file XMLTABLE in library QTEMP. The error code
is 1. The error codes and their meanings follow: 1 -- The external program
or service program returned SQLSTATE 58004. The text message returned from
the program is: MCH0601 . 2 -- The external program failed before it
completed. 3 -- The database timed out waiting for the program to return.
The timeout value used by the database was 0 minutes and 30 seconds. 4 --
The external program no longer exists or is not found. 5 -- One of the input
parameters of the function had a data mapping error. 6 through 26 -- See the
previous message in the joblog. For an external program, the program entry
point displayed will be *N. Recovery . . . : For error codes 1 and 2,
determine the cause of the error from either the SQLSTATE or a previously
listed message. For error code 3, either increase the timeout limit using
the QAQQINI file setting or determine why the external program or service
program did not return in the time allotted. For error code 4, ensure the
program or service program exists for the duration of the query. For error
code 5, determine the cause of the data mapping error. For error codes 6
through 26, see the previous message in the joblog. Refer to the DB2 for i
SQL programming topic collection in the Database category in the IBM i
Information Center book, http://www.ibm.com/systems/i/infocenter/ for more
information on user-defined functions. Possible choices for replying to
message . . . . . . . . . . . . . . . : C -- The request is canceled. I --
The request is ignored. ​
PHP Code:
dcl-s infile sqltype(clob_file) inz;
dcl-s fileName char(128) inz;
dcl-s filePathToProcess like(fileName) inz;​
//.....
clear infile;
infile_name = %trimr(filePathToProcess); // file path and name
infile_nl = %len(%trimr(infile_Name)); // length of file path name
infile_fo = SQFRD; // set data access authority
// recreate qtemp table to store xml data in an xml column
exec sql drop table qtemp.xmltable;
exec sql create table qtemp.xmltable(xmlColumn xml);
exec sql insert into qtemp.xmltable values(:infile); ​
exec sql
declare xmlValues cursor for​ select blahblahblah
from qtemp.xmltable b, xmltable(
'$doc/MyPath'
passing b.xmlColumn as "doc" columns ​
MYXMLCOL1 char(10) path '.'
) as a;
// open xml parsing cursor
exec sql open xmlValues; ​
exec sql
fetch xmlValues for :intDataRows rows into :intData :nullIndRows;​
Another side note is intData has 77 fields and the XML file could contain 1,000 parent nodes.
Again, just curious what limit I am hitting: xml nodes/rows, file size, character count, data structure size.
Comment