Extremely pureXML in DB2 10 for z/OS

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

"urn:iso:std:iso:20022:tech:xsd:camt.053.001.02";

$i/Document/BkToCstmrStmt/Stmt/Acct/Ownr[

Nm/fn:string(.) = $j/MsgRcpt[ Nm/fn:string(.) ] ]'

passing

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

messages.

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:

1. A

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.

122 Extremely pureXML in DB2 10 for z/OS

Table 6-3 IBM change data capture tools that publish XML messages.

The purpose of this section is to focus on the XML messages that are published from these

tools, and examine how they can be used with DB2 pureXML.

InfoSphere Data Event Publisher and InfoSphere Classic Data Event Publisher both share a

common schema and generate messages similar to the one in Example 6-32.

Example 6-32 XML CDC message format for DB2 and Classic Data Event Publishers

<?xml version="1.0" ?>

<msg xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="mqcap.xsd"

version="1.0.0" dbName="$IMS "

seqNum="IMS000003B4009FF00AIMSY _8"> 1

<rowOp authID="USER0000"

planName="USER0000"

cmitLSN="IMS000003B4009FF00AIMSY "

cmitTime="2010-11-12:14:22:02.374839"> 2

<insertRow subName="CLASSIC" srcOwner="IMSP" srcName="CLASSIC1"> 3

<col name="STRTNM" isKey="0"> 4

<char>Bailey Avenue</char>

</col>

<col name="BLDGNB" isKey="0">

<char>555</char>

</col>

<col name="PSTCD" isKey="0">

<char>95149</char>

</col>

<col name="TWNNM" isKey="0">

<char>San Jose</char>

</col>

</insertRow>

</rowOp>

</msg>

Tool Main data sources

supported for CDC

Comments

InfoSphere Data

Event Publisher

 DB2 for z/OS

 DB2 for LUW

 Oracle

 Offers asynchronous log reader services

 Writes CDC messages as XML (and other formats)

 Publishes messages directly to WebSphere MQ

 Is available for z/OS and LUW versions

InfoSphere Classic

Data Event Publisher

 IMS

 VSAM

 IDMS

 Adabas

 Offers asynchronous log reader services

 Writes CDC messages as XML (and other formats)

 Publishes messages directly to WebSphere MQ or zFS files

 Is available for z/OS only.

InfoSphere Change

Data Capture

 DB2 for z/OS

 DB2 for LUW

 DB2 for iSeries®

 Oracle

 Sybase

 SQL Server

 Offers asynchronous log reader services

 Writes CDC messages as XML (and other formats)

 Source server writes CDC data over tcpip to a target server.

 Target server writes messages to a range of targets, including

WebSphere MQ and files

 Is available for z/OS and LUW versions