Extremely pureXML in DB2 10 for z/OS

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