Chapter 11. Performance considerations 265
-- can be replaced by
SELECT X.CRE_DT_TM, X.MINISTMT INTO V_CREDTTM, V_MINISTMT
FROM XMLTable(XMLNAMESPACES(DEFAULT
'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02',
'$d/Document/BkToCstmrStmt' PASSING VALIDXML as "d"
COLUMNS
"CRE_DT_TM" TIMESTAMP PATH './GrpHdr/CreDtTm/text()'
"MINISTMT" XML PATH './Stmt' ) AS X ;
Another way of making the code more elegant and more efficient is to combine the two
XMLQUERY calls into a single select statement, as shown in Example 11-11.
Example 11-11 Single select statement combining two xmlquery expressions
select
xmlcast(xmlquery('
declare default element namespace
"urn:iso:std:iso:20022:tech:xsd:camt.053.001.02";
$d/Document/BkToCstmrStmt/GrpHdr/CreDtTm'
passing VALIDXML as "d") as timestamp)
into V_CREDTTM,
xmlquery('declare default element namespace
"urn:iso:std:iso:20022:tech:xsd:camt.053.001.02";
$d/Document/BkToCstmrStmt/Stmt'
passing VALIDXML as "d")
into V_MINISTMT
from sysibm.sysdummy1 );
11.7.2 XMLEXISTS for index access
Whether you use an XML or a relational index to reduce the number of rows that need to be
accessed in the base table does not matter. Use XMLEXISTS when possible to help the
optimizer. If no suitable relational index is available, then using an XML index requires the
XMLEXISTS function in the vast majority of cases.
Although using predicates to filter rows is a good practice in any situation, because
XMLQUERY and XMLTABLE can be more CPU-heavy than other built-in functions, good
filtering is critical.
11.7.3 Simple XPath expressions
As a generalization, simple XPath expressions perform much better than complex XPath
expressions, because they are more likely to qualify for XML index access path selection.
For example, XPath with forward slash (/) generally performs better than with double forward
slash (//), both for queries and XML index specifications.
Always review the latest APARs to make sure that you download PTFs that improve
performance of XML processing. The best place to start is II14426, which is the information
APAR to link together all the XML support delivery APARs:
http://www.ibm.com/support/docview.wss?uid=isg1II14426
266 Extremely pureXML in DB2 10 for z/OS

Get Extremely pureXML in DB2 10 for z/OS now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.