Chapter 6. DB2 SQL/XML programming 121
Example 6-31 XML to XML join using XMLEXISTS
select * from BK_TO_CSTMR_STMT c, XMLADDRESS a
where xmlexists('declare default element namespace
Nm/fn:string(.) = $j/MsgRcpt[ Nm/fn:string(.) ] ]'
c.BK_TO_CSTMR_STMT as "i",
a.CUSTADDR as "j");
Example 6-31 was created to illustrate the mechanics of an XML to XML join as clearly as
possible. This query does not contain any filter predicate that is indexable. However, the join
predicate is eligible for index access because it has been expressed with the string() function,
which is indexable.
Similarly, if you have XML to XML join on a numeric field, use xs:double so that an XML
DECFLOAT index can be used for the join predicate.
6.8 XML with change data capture tools
The primary application scenario for this book is the ISO 20022 standard for banking
Another common source of XML messages is the range of replication and event publishing
tools that are used to capture changes from existing database, and publish change data
capture (CDC) messages. The published messages can sometimes be used to replicate
changes to another database (such as a data warehouse). Increasingly,
CDC messages are
commonly being used in event-driven systems. Changes to source data, which meets certain
criteria (such as bank transfers that exceed a threshold value), can be routed to a workflow
system (such as WebSphere Message Broker) where the CDC event can be examined by
using workflows that implement business processes, and initiate automated actions.
This section considers ways in which CDC messages can be handled by DB2 pureXML.
6.8.1 Change data capture tools background
Change data capture tools tend to follow an architecture along the following lines:
capture process is used to read the database log of a source database, looking for
changes that have been requested by a subscription definition.
2. When qualifying changes are found in the log, they are packaged (typically into
unit-of-work boundaries) and transmitted over a network infrastructure to the target
systems that have subscribed to them.
3. The target systems receive the changes and do something with them (such as update a
database, invoke an application process, publish an CDC message in XML or other
format, and so on).
IBM replication and event publishing tools that publish XML CDC messages include the tools
listed in Table 6-3 on page 122. In the table, LUW stands for Linux, UNIX, and Windows.