Troubleshooting SQL20306N messages issued by CREATE INDEX statements on populated tables

To determine the cause of SQL20306N error messages, refer to "Problem determination for SQL20305N and SQL20306N error messages" in the parent topic, then follow these steps:

Procedure

To find the failing node values in the stored document, search for the string SQL20306N in the db2diag log file and match the reason code number.
Following the reason code, you can find a set of instructions and then a generated XQuery statement that you can use to locate the value in the document that caused the error.
- For small node values, the full value is used in the XQuery predicate.
- For node values too long to be output to the db2diag log file, the starting bytes of the value are used with the fn:starts-with function and the ending bytes of the value are used with the fn:ends-with function in the XQuery predicate.
Execute the XQuery statement to retrieve the entire document and the fragment of the document containing the value that caused the failure.
To provide context information of where the error occurred in the document, the XQuery statement will output the document fragment starting with the parent of the node value causing the failure.
Use the index XML pattern to identify the set of matching XML nodes to inspect. Because the generated XQuery statement uses wildcards for namespaces, it is possible (but not common) for multiple problem values to qualify that have different namespaces. If this occurs, you must use the namespace declaration in the index XML pattern to determine the correct set of matching XML nodes. If the complete value is not used in the predicate to filter the results, the index XML pattern must be used to verify the qualifying problem values returned by the XQuery statement.
Once you have located the failing value in the document, modify the CREATE INDEX XML pattern to correct the problem, or use the XQuery predicate to update or delete the document containing the failing value.

Example: CREATE INDEX failure

In this example, the qualified text value in the stored document exceeds the VARCHAR(4) length constraint in the index XML pattern, so that the CREATE INDEX statement fails. For large values, the generated XQuery uses the fn:starts-with and fn:ends-with functions in the predicate. Note that *N is used as a placeholder in the error message where schema information is not applicable.

INSERT INTO t VALUES (XMLPARSE (DOCUMENT '
   <x>This is the beginning of the document
       <y>test
          <z>rld12345678901234567890123412345678901234567890123
	  45678901234567890123456789009876543211234567890098765
	  43211234456778809876543211234567890455</z>
       </y>
   </x>' strip whitespace))

DB20000I  The SQL command completed successfully.

CREATE INDEX i1 ON t(x) 
   GENERATE KEY USING XMLPATTERN '/x/y//text()' 
   AS SQL VARCHAR(4)

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:

SQL20306N  An index on an XML column cannot be created because of an error
detected when inserting the XML values into the index.  Reason code = "1". For
reason codes related to an XML schema the XML schema identifier = "*N" and XML
schema data type = "*N".  SQLSTATE=23526

The output in the db2diag log file is as follows (with minor formatting changes):

2007-03-06-12.08.48.437571-480 I10148A1082        LEVEL: Warning
PID     : 1544348              TID  : 1801        PROC : db2sysc
INSTANCE: adua                 NODE : 000         DB   : ADTEST
APPHDL  : 0-30                 APPID: *LOCAL.adua.070306200844
AUTHID  : ADUA
EDUID   : 1801                 EDUNAME: db2agent (ADTEST)
FUNCTION: DB2 UDB, Xml Storage and Index Manager, 
          xmlsIkaProcessErrorMsg, probe:361
MESSAGE : ZRC=0x80A50412=-2136669166=XMS_XML_CRIX_ERROR
          "XML node value error during create XML Index"
DATA #1 : String, 36 bytes
SQL Code: SQL20306N ; Reason Code: 1
DATA #2 : String, 72 bytes
To locate the value in the document that caused the error, execute 
the following XQuery.
DATA #3 : String, 435 bytes
xquery for $doc in db2-fn:xmlcolumn("ADUA.T.X")[/*:x/*:y/*:z/text()
[fn:starts-with(., "rld12345678901234567890123412345678901234567890123") 
and fn:ends-with(., "56789009876543211234456778809876543211234567890455")]]
return
<Result>
 <ProblemDocument> {$doc} </ProblemDocument>
 <ProblemValue> {$doc/*:x/*:y/*:z/text()/..} </ProblemValue>
</Result>;

The result of the query statement is as follows (with minor formatting changes):

<Result>
    <ProblemDocument>
         <x>This is the beginning of the document
           <y>test
              <z>rld12345678901234567890123412345678901234567890123
	      45678901234567890123456789009876543211234567890098765
	      43211234456778809876543211234567890455</z>
          </y>
       </x>
</ProblemDocument>
 <ProblemValue>
     <z>rld12345678901234567890123412345678901234567890123
     45678901234567890123456789009876543211234567890098765
     43211234456778809876543211234567890455</z>
 </ProblemValue>
</Result>

Correcting the error:

You can change the CREATE INDEX XML pattern to increase the maximum VARCHAR length:

CREATE INDEX i1 ON t(x) 
   GENERATE KEY USING XMLPATTERN '/x/y//text()' 
   AS SQL VARCHAR(200)