Maximum Row Count For XML Parsing Using XMLTABLE

[TheZenbudda]

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:

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

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.

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.

[B.Hauser]

It is a real System Error (SQLCODE -901) ... you have to ask IBM.
The maximum size of an XML document that can accessed is 2GB.

But another question ... why to copy the xml document first into a table instead of accessing it directly in a Cursor?

Something like this:

  Declare CsrC01 Cursor For
     Select *
     from XMLTABLE('MyPath'
             Passing XMLParse(Document Get_xml_file('/home/YourDir1/YourDir2/YourXMLFile.xml'))
             Columns  MYXMLCOL1  Char(10)     Path '.',
                      ...) a;​

[TheZenbudda]

Thanks for your response Birgitta!

My initial thoughts behind copying the xml file to a table with an xml column was to perform a "pre-validation" to ensure the xml document was valid. This decision was made as part of an integration project where we had problems with invalid xml documents. Oddly enough, when we query the qtemp file mentioned above, all we see is a pointer, not the raw xml data.

[B.Hauser]

When executing SQL Statements with STRSQL which include LOB (CLOB, DBCLOB, BLOB, XML) data you'll only see a pointer.
You should execute the statement with ACS (IBM i Access Client Solution) - Run SQL Scripts.



BTW XMLPARSE should be enough to pre-evaluate the XML.
... and if you want to validate a XML against a XML Schema, there is also a XMLVALIDATE function.