Transformation of SQL predicates to XML predicates

Db2 sometimes transforms an SQL query to change the timing at which a predicate is applied to improve the performance of the query. Db2 might use such a transformation to push SQL predicates into the XPath expression embedded in the XMLTABLE function.

Begin program-specific programming interface information.

For example, a query finds all books that were published after 1991 and lists the year, title and publisher for each book.

SELECT X.*
FROM T1,
	XMLTABLE('/bib/book'
						PASSING T1.bib_xml
						COLUMNS YEAR INT PATH '@year',
							TITLE VARCHAR(30) PATH 'title',
						PUBLISHER VARCHAR(30) PATH 'publisher') X
WHERE X.YEAR > 1991;

Db2 can rewrite the query to process the WHERE X.YEAR > 1991 predicate in the XMLTABLE function. In the rewritten query the original predicate becomes an XPath predicate that is associated with the row-xpath-expression of the XMLTABLE function:

SELECT X.*
FROM T1,
	XMLTABLE('/bib/book[@year>1991]'
					PASSING T1.bib_xml
					COLUMNS YEAR INT PATH '@year',
								TITLE VARCHAR(30) PATH 'title',
						PUBLISHER VARCHAR(30) PATH 'publisher') X

Implications of truncation and trailing blanks

Unlike SQL, in which trailing blanks have no significance, in XPath trailing blanks are significant. For example, the following query contains an additional predicate, X.publisher = 'Addison-Wesley':

SELECT *
FROM T1,
	XMLTABLE('/bib/book'
					PASSING T1.bib_xml
					COLUMNS year INT PATH '@year',
								title VARCHAR(30) PATH 'title',
						 publisher VARCHAR(30) PATH 'publisher') X
WHERE X.year > 1991
	AND X.publisher = 'Addison-Wesley';

Because of the possible truncation when a publisher is cast to varchar(30), and the possibility of trailing blanks in the original XML data, Db2 must add an internal operator, db2:rtrim, to simulate the SQL semantics in order to push the predicate into XPath. As shown below. The predicate X.publisher = 'Addison-Wesley' is transformed into [db2:rtrim(publisher,30)="Addison-Wesley"].

Predicates that are eligible for transformation to XML predicates in XMLTABLE

A predicate that satisfies the following criteria is eligible for transformation to be processed by the XMLTABLE function:

  • The predicate must have one of the following forms: (Where op stands for any of the following operators: =, <, >, <=. >=, or <>.)
    • Column op constant, parameter, or host variable, where the column is from the result table.
    • Column op column, where the column on the left hand side is from the result table and the column and the right hand side is from either the result table or one of the input tables.
    • Column op expression, where the column is from the result table and the expression is any SQL expression that only contains columns from the input table.
    • A BETWEEN predicate that can be transformed into one of the above forms.
    • COLUMN IS (NOT) NULL
    • A predicate that is composed of the above forms combined with AND and OR.
    • COLUMN (NOT) IN (expression 1, ..., expression n), where the column is from the result table and each of the expressions on either a column from the result table or an SQL expression that contains neither columns from the result table nor columns from a table that is NOT an input table.
  • The predicate is a boolean term predicate.
  • The predicate can be applied before any join operations.
  • The result column of the XMLTABLE function that is involved in the predicate is not of any of the following data types:
    • DATE
    • TIME
    • TIMESTAMP
    • DECFLOAT(16)
    • REAL
    • DOUBLE
    This restriction does not apply to IS (NOT) NULL predicate.
  • The result column of the XMLTABLE function involved in the predicate does not have a default clause.
  • The XMLTABLE function does not have a FOR ORDINALITY column.
End program-specific programming interface information.