DB2 Version 9.7 for Linux, UNIX, and Windows

XDB_DECOMP_XML_FROM_QUERY stored procedure for annotated schema decomposition

The stored procedure decomposes one or more XML documents from a binary or XML column. The data from the XML documents is stored in columns of relational tables based on annotations specified in an XML schema.

Syntax


>>-XDB_DECOMP_XML_FROM_QUERY--(--rschema--,--xmlschema--,------->

>--query--,--validation--,--commit_count--,--allow_access--,---->

>--reserved--,--reserved2--,--continue_on_error--,-------------->

>--total_docs--,--num_docs_decomposed--,--result_report--)-----><

The schema of the stored procedure is SYSPROC.

The procedure executes with the read stability isolation level.

Authorization

One of the following authorizations or privileges is required:

All of the following privileges:
- INSERT privilege on all target tables referenced in the annotated schema
- SELECT privilege on the table, alias, or view containing the column holding the input documents
- SELECT, INSERT, UPDATE, or DELETE privilege, as applicable, on any table referenced by the db2-xdb:expression or db2-xdb:condition annotation
CONTROL privilege on all tables referenced in the set of annotated schema documents and on the table, alias, or view containing the column holding the input documents
DATAACCESS authority

If the value of validation is 1, USAGE privilege on the XML schema is also required.

Procedure parameters

rschema

An input argument of type VARCHAR(128) that specifies the SQL schema part of the two-part XML schema repository (XSR) object name registered with the XML schema repository. The value can be NULL. If the value is NULL, the SQL schema part is assumed to be the current value of the CURRENT SCHEMA special register.

xmlschema

An input argument of type VARCHAR(128) that specifies the name of the two-part XSR object name registered with the XSR. This value cannot be NULL.

query

An input argument of type CLOB(1MB). This value cannot be NULL. The query conforms to the rules of an SQL SELECT statement, and must return a result set containing 2 columns. The first column is the document identifier. Each document identifier uniquely identifies an XML document to be decomposed. The column must be of character type or be castable to character type. The second column contains the XML documents to be decomposed. The supported types for the document column are XML, BLOB, VARCHAR FOR BIT DATA, and LONG VARCHAR FOR BIT DATA. The column containing the XML documents must resolve to a column of an underlying base table, the column cannot be a generated column.

For example, the DOCID column in the following SELECT statement contains the unique identifiers for the XML documents stored in SALESDOC column.

SELECT DOCID, SALESDOC FROM SALESTAB

validation

An input argument of type INTEGER that indicates whether validation will be performed on the documents before they are decomposed. The possible values are:

0: Validation is not performed on the input documents before they are decomposed.; If a 0 value is passed and validation is not performed, it is the user's responsibility to validate the documents before calling the stored procedure. For example, the user can use XMLVALIDATE when inserting the XML documents into the column, or use an XML processor before inserting the documents. If an input XML document is not valid and 0 is specified for this parameter, the decomposition results are undefined. See the related reference at the end of this topic for information about XML validation.
1: Validation is performed on the input documents against DTDs or XML schema documents previously registered with the XML schema repository. The input XML documents are decomposed only if the validation is successful.

commit_count

An input argument of type INTEGER. The possible values are:

0: No COMMIT is ever performed by the stored procedure.
n, a positive integer: After every n successful document decompositions, a COMMIT is performed.

allow_access

An input argument of type INTEGER. The possible values are:

0: The stored procedure will acquire an exclusive lock (X) on all tables with mappings in the XML schema. Not all tables will necessarily participate during decomposition of each document, but all target tables will be locked to lower the possibility of deadlock during a long unit of work.
1: When acquiring locks, the stored procedure will wait and possibly timeout.

reserved

The reserved argument is an input argument reserved for future use. The value passed for this argument must be NULL.

reserved2

The reserved2 argument is an input argument reserved for future use. The value passed for this argument must be NULL.

continue_on_error

An input argument of type INTEGER. The possible values are:

0: The stored procedure stops on the first document that cannot be successfully decomposed. If an error occurs during the decomposition of a document, changes to the database made during the decomposition of the document are undone.
1: The stored procedure does not stop on document-specific errors and attempts to decompose all documents specified by query. If an error occurs during the decomposition of a document, changes to the database made during the decomposition of the document are undone, and the stored procedure attempts to decompose the next document. Information about any document that is not successfully decomposed is written to result_report.

The stored procedure does not continue on fatal errors and non-document specific errors regardless of the value of continue_on_error.

total_docs

An output argument of type INTEGER that specifies the total number of input documents that the XDB_DECOMP_XML_FROM_QUERY stored procedure attempted to decompose.

num_docs_decomposed

An output argument of type INTEGER that specifies the number of documents that were successfully decomposed.

result_report

An output argument of type BLOB(100MB). A buffer containing a UTF-8 XML document that lists the name of each input file that was not successfully decomposed, along with a diagnostic message. This report is generated only when there is at least one XML document that could not be successfully decomposed.

The format of the XML document in result_report is as follows:

<?xml version='1.0'?>
<xdb:errorReport xmlns:xdb="http://www.ibm.com/xmlns/prod/db2/xdb1">
   <xdb:document>
      <xdb:documentId>sssss</xdb:documentId>
      <xdb:errorMsg>qqqqq</xdb:errorMsg>
   </xdb:document>
   <xdb:document>
      . . . 
   </xdb:document>
   . . . 
</xdb:errorReport>

The documentId value sssss is the value from the first column specified by query. The value identifies the XML document that was not successfully decomposed. The errorMsg value qqqqq is the error encountered during the attempt to decompose the document.

Output

The SQLCA structure indicates the return status of the procedure after attempting to decompose the XML documents. The procedure can return one of the following SQLCODE values:

0: All documents specified by query were successfully decomposed.
16278: The decomposition of one or more documents failed. The number of successful documents is given as the num_docs_decomposed output parameter to the stored procedure. Individual error messages for each failed document are recorded in result_report. Further diagnostic details on each failure are logged in a db2diag log file.
negative integer: No documents were decomposed. The SQLCODE indicates the reason for the failure. Check the db2diag log file for details of the failure.

Note

The stored procedure is declared with these characteristics:

DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
UNFENCED
THREADSAFE
MODIFIES SQL DATA
PARAMETERSTYLE SQL
CALLED ON NULL INPUT
NEW SAVEPOINT LEVEL 
DBINFO

Example

The following example assumes the table ABC.SALESTAB contains two columns SALESDOC and DOCID. The column SALESDOC contains XML documents, and the column DOCID contains the unique identifier for the XML documents in SALESDOC. All the XML documents correspond to an XML schema registered as ABC.SALES, and the schema has been annotated with decomposition information and enabled for decomposition. The following example calls the stored procedure to decompose the documents stored in SALESDOC using the schema ABC.SALES:

XDB_DECOMP_XML_FROM_QUERY ('ABC', 'SALES', 
    'SELECT DOCID, SALESDOC FROM ABC.SALESTAB', 0, 0, 0, 
    NULL, NULL, 1, numInput, numDecomposed, errorreportBuf);