Troubleshooting SQL20305N messages issued by INSERT or UPDATE statements

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

Procedure

Determine the index name and the index XML pattern clause
1. Obtain the index name (index-name,index-schema) from SYSCAT.INDEXES by issuing the following query using the index-id from the error message:
```
SELECT INDNAME,INDSCHEMA
   FROM SYSCAT.INDEXES
   WHERE IID = index-id AND 
   TABSCHEMA ='schema' AND TABNAME ='table-name'
```
2. Use index-name and index-schema to obtain the index data type and XML pattern from SYSCAT.INDEXES by issuing the following query:
```
SELECT DATATYPE, PATTERN
   FROM SYSCAT.INDEXXMLPATTERNS
   WHERE INDSCHEMA = 'index-schema' AND 
   INDNAME = 'index-name'
```
To find the failing node values in the input document, search for the string SQL20305N 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 is causing the error. For small node values the full value is used in the XQuery predicate. For node values that are 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.
Because the document was rejected and does not exist in the table, the XQuery statement cannot be run on it. To solve this problem, create a new table with the same columns as the original table and insert the failing document in the new table. Do not create any indexes on the new table.
Copy the generated XQuery statement from the db2diag log file and replace the table name in the XQuery with the newly created table name.
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 locate the failing value in the document, modify the input document to correct the problem and resubmit the INSERT or UPDATE statement.

Example: INSERT statement error

In the following example, hello world is an invalid DOUBLE value, and the entire value is used in the generated XQuery predicate. Note that *N is used as a placeholder in the error message where schema information is not applicable.

CREATE TABLE t1 (x XML);

CREATE INDEX ix1 ON t1(x) 
   GENERATE KEY USING XMLPATTERN '/root/x/text()' 
   AS SQL DOUBLE REJECT INVALID VALUES;

DB20000I  The SQL command completed successfully.

INSERT INTO t1 VALUES (XMLPARSE (DOCUMENT
   'The beginning of the documenthello world'
   STRIP WHITESPACE));

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

SQL20305N  An XML value cannot be inserted or updated because of an error
detected when inserting or updating the index identified by "IID = 23" on
table "ADUA.T".  Reason code = "5".  For reason codes related to an XML schema
the XML schema identifier = "*N" and XML schema data type = "*N".
SQLSTATE=23525

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

2007-03-06-12.02.08.116046-480 I4436A1141         LEVEL: Warning
PID     : 1544348              TID  : 1801        PROC : db2sysc
INSTANCE: adua                 NODE : 000         DB   : ADTEST
APPHDL  : 0-18                 APPID: *LOCAL.adua.070306200203
AUTHID  : ADUA
EDUID   : 1801                 EDUNAME: db2agent (ADTEST)
FUNCTION: DB2 UDB, Xml Storage and Index Manager, 
          xmlsIkaProcessErrorMsg, probe:651
MESSAGE : ZRC=0x80A50411=-2136669167=XMS_XML_IX_INSERT_UPDATE_ERROR
          "XML node value error during insert or update XML index"
DATA #1 : String, 36 bytes
SQL Code: SQL20305N ; Reason Code: 5
DATA #2 : String, 321 bytes
To locate the value in the document that caused the error, create 
a new table with the same columns as the original table and insert 
the failing document in the table. Do not create any indexes on 
the new table. Replace the table name in the query below with the 
newly created table name and execute the following XQuery.
DATA #3 : String, 187 bytes
xquery for $i in db2-fn:xmlcolumn("ADUA.T.X")[/*:root/*:x/text()="hello world"]
return
<Result>
 <ProblemDocument> {$i} </ProblemDocument>
 <ProblemValue>{$i/*:root/*:x/text()/..} </ProblemValue>
</Result>;

To find the failing node value:

Create a new table with the same columns as the original table:
```
CREATE TABLE t2 LIKE t1;
```

Insert the failing document into the new table:

INSERT INTO t2 VALUES (XMLPARSE (DOCUMENT
'The beginning of the documenthello world'
STRIP WHITESPACE));

Copy the generated XQuery statement from the db2diag log file and replace the table name in the XQuery with the new table name:
```
xquery for $i in db2-fn:xmlcolumn("ADUA.T2.X")[/*:root/*:x/text()="hello world"]
return
{$i}
{$i/*:root/*:x/text()/..}
;
```

Execute the XQuery statement against the new table. The result of the query statement is as follows (with minor formatting changes):

<Result>
   <ProblemDocument>
      <root>The beginning of the document<x>hello world</x></root>         
   </ProblemDocument>
   <ProblemValue><x>hello world</x></ProblemValue>
</Result>

Correct the error:

The document can be changed so that the <x> element has a numeric value that will cast successfully to the DOUBLE data type:

INSERT INTO t1 VALUES (
   XMLPARSE (DOCUMENT 
   '<root>The beginning of the document<x>123</x></root>' 
   STRIP WHITESPACE))