© 2002 International Business Machines Corporation. All rights reserved.

Introduction

This article describes how MQSeries® and DB2® XML Extender can be used together to construct applications that combine XML messaging and database access. We focus on a set of optional DB2 functions and stored procedures that can be installed with DB2 XML Extender Version 7.2. With these functions and procedures, it is possible to support a wide range of applications that use XML messages and MQ functions. These functions and stored procedures provide an easy and yet powerful way to integrate diverse software applications, an essential element for constructing many kinds of systems, such as business-to-business transaction systems and Customer Relationship Management systems.

We first provide a quick overview of the DB2 XML Extender, followed by an overview of the DB2 features released in DB2 7.2. With this background in place, we then review the latest capabilities of the DB2 XML Extender that integrates DB2 MQ features with XML messaging. Finally, we illustrate some of the usage examples through an insurance software application.

Back to top

XML Extender Overview

XML has emerged as the accepted standard for data interchange in the past few years. DB2 XML Extender was developed to integrate the power of IBM's DB2 Universal Database^TM (DB2 UDB) with the flexibility of XML. This allows XML applications to take advantage of the power of DB2.

A sample XML document is shown in Figure 1 and is used for illustration purposes in this paper. For each XML document, a Data Type Definition (DTD) file can be used to define the XML elements and attributes and to verify the syntax of the XML document. Figure 2 shows the DTD file (insurance.dtd) that can be used to verify the application XML document in Figure 1.

Figure 1. Sample XML document

<?xml version="1.0"?>
<!DOCTYPE Application SYSTEM
"c:\dxx\samples\dtd\insurance.dtd">
<Application key="123">
<Insured>
<Name>John Doe</Name>
<DateOfBirth>1950-01-01</DateOfBirth>
<Coverage>
<Amt>100000</Amt>
<Code>Life1</Code>
</Coverage>
</Insured>
<Agent>
<Id>A101</Id>
<Agent_Name>Brian Lee</Agent_Name>
</Agent>
</Application>

Figure 2. Sample DTD file (file name: insurance.dtd)

<?xml encoding="US-ASCII"?>
<!ELEMENT Application (Insured, Agent)>
<!ATTLIST Application key CDATA #REQUIRED>
<!ELEMENT Insured (Name, DateOfBirth, Coverage)>
<!ELEMENT Name (#PCDATA)>
<!ELEMENT DateOfBirth (#PCDATA)>
<!ELEMENT Coverage (Amt, Code)>
<!ELEMENT Amt (#PCDATA)>
<!ELEMENT Code (#PCDATA)>
<!ELEMENT Agent (ID, Agent_Name)>
<!ELEMENT ID (#PCDATA)>
<!ELEMENT Agent_Name (#PCDATA)^gt;

The XML Extender provides many features to help you manage and exploit XML data with DB2, including the following:

• A choice of working with XML either intact or mapped to relational tables
  
  XML column - This method stores intact XML documents in DB2. The documents are inserted into columns that are enabled for XML (see the new extended data types below) and can be updated, retrieved, and searched. Element and attribute data can be mapped to DB2 tables (called side tables) that may be indexed to provide for fast search.
  
  XML collection - This method maps XML document structures to one or more DB2 tables so that you can either compose XML documents from existing DB2 data, or decompose XML documents (by which we mean storing untagged element or attribute content) into traditional DB2 tables.
  
  
• A mapping scheme, represented in the Document Access Definition (DAD) file, to map incoming XML documents to untagged relational data (also called "shred" or "decompose") or to generate outgoing XML documents from existing relational data. A DAD file is also used to map an XML column that stores intact XML documents into DB2 side tables. The side tables are typically indexed to provide for fast search.
  
  
• Extended data types, functions, and stored procedures to store and access XML documents. These are defined under the schema DB2XML. The following user-defined types (UDTs) are provided to use with these new XML features:

  XMLVARCHAR:	for small documents stored in DB2
  XMLCLOB:	for large documents stored in DB2
  XMLFILE:	for documents stored outside DB2

In addition to the XML specific data types, a set of user-defined functions (UDFs) can be used to store and retrieve XML documents in XML columns. For example, the following SQL reads an XML document from a server file and inserts it into an XML column. The column XML_APP of the XML_APPLICATION_TAB table is defined as XMLVarchar type.

Example 1:

INSERT INTO xml_application_tab (ID, TYPE, XML_APP)
VALUES('1234','Group', db2xml.XMLVarcharFromFile('c:\samples\application.xml'))

Additionally, the XML Extender provides a set of extraction functions for various SQL data types. They extract and convert the element content or attribute value from an XML document to the data type that is specified by the function name. For example, the following SQL statement reads the XML document from the file and returns the Coverage Amount in integer format.

Example 2:

values db2xml.EXTRACTINTEGER(db2xml.XMLVarcharFromFile
('c:\samples\application.xml'), '/Application/Insured/Coverage/Amt')

All the extraction functions take two input parameters. The first parameter is the XML Extender UDT. The second parameter is the location path that specifies the XML element or attribute. In our example 2 above, the XML document is read from the file as a XMLVARCHAR type. Multiple XML documents can be queried in one function call using the table extraction functions provided by the XML Extender.

The DAD file itself is an XML document. It associates an XML document structure to columns in DB2 database tables when using either XML columns or XML collections. A sample DAD file that specifies the relationship between the tables and the structure of the sample insurance XML document is shown in Figure 3. In this simple illustration, the DAD file describes that the elements and attributes of the XML document shown in Figure 1 can be derived from the columns of two tables. These two tables can be created using the following SQL statements:

create table application_tab(app_key integer, name varchar(16),
dob date, coverage_code varchar(8), amt decimal(10,2), agent_id
varchar(16))
create table agent_tab(agent_id varchar(16), agent_name
varchar(16))

insert into application_tab values (123, 'John Doe',
'1950-01-01', 'Life1', 100000.00, 'A101')
insert into agent_tab values ('A101', 'Brian Lee')

Figure 3. Sample DAD file using SQL mapping

<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">
<DAD>
<validation NO>
<Xcollection>
<SQL_stmt>select app_key, name, dob, amt, coverage_code,  b.agent_id, agent_name
from application_tab a, agent_tab b where a.agent_id = b.agent_id
ORDER BY app_key, agent_id</SQL_stmt>
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE Application SYSTEM "c:\dxx\samples\dtd\insurance.dtd"</doctype>
<root_node>
<element_node name="Application">
<attribute_node name="key">
<column name="app_key"/>
</attribute_node>
<element_node name="Insured">
<element_node name="Name">
<text_node><column name="name"/></text_node>
</element_node>
<element_node name="DateOfBirth">
<text_node><column name="dob"/></text_node>
</element_node>
<element_node name="Coverage">
<element_node name="Amt">
<text_node><column name="amt"/></text_node>
</element_node>
<element_node name="Code">
<text_node><column name="coverage_code"/></text_node>
</element_node>
</element_node>
</element_node>
<element_node name="Agent">
<element_node name="ID">
<text_node><column name="agent_id"/></text_node>
</element_node>
<element_node name="Agent_Name">
<text_node><column name="agent_name"/></text_node>
</element_node>
</element_node>
</element_node>
</root_node>
</Xcollection>
</DAD>

Using the DAD file shown in Figure 3 and the two tables created earlier, we can write a C program that calls the stored procedure provided by DB2 XML Extender. The program will generate (compose) an insurance policy application XML document as shown in Figure 1. Note that we use C with embedded SQL in all our examples. Thus, an indicator variable is used for each parameter of a stored procedure call. For simplicity, we only show the SQL call statement in our examples. A sample C program section that uses embedded SQL and calls a DB2 XML stored procedure is shown in Appendix B. Also, the stored procedure parameters are described in Appendix A.

The following example assumes that a result table is created with the name of XML_APPLICATION_TAB, and that the table has one column of XMLVARCHAR type (refer to the C program in Appendix B for more details). The XML document (as shown in Figure 1) will be available from this table column at the end of program run

Example 3:

EXEC SQL CALL db2xml.DXXGENXML(:dad :dad_ind; :resultTabName :rtab_ind, :overrideType :ovtype_ind,:override:ov_ind, :maxrow :maxrow_ind,:numrow :numrow_ind, :returnCode :returnCode_ind, :returnMsg :returnMsg_ind);

Also provided in DB2 XML Extender are stored procedures to decompose XML documents. For example, the following stored procedure call uses a DAD file for an XML collection to decompose the XML document into a collection of DB2 tables. The decomposition procedures require a similar DAD file, but one that uses RDB_node mapping instead of the SQL mapping that we used to compose an XML document. A sample DAD file that uses RDB_node mapping is shown in Figure 4. All these stored procedures are defined in the db2xml schema.

Example 4:

EXEC SQL CALL db2xml.DXXSHREDXML(:dad :dad_ind, :xmlDoc :xmlDoc_ind, :returnCode :returnCode_ind, :returnMsg :returnMsg_ind)

Figure 4. Sample DAD file using RDB_node mapping

<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">
<DAD>
<dtdid>neworder.dtd</dtdid>
<validation>YES</validation>
<Xcollection>
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE Order SYSTEM "c:\dxx\samples\dtd\neworder.dtd"</doctype>
<root_node>
<element_node name="Order">
<RDB_node>
<table name="order_tab" key="order_key"/>
<table name="part_tab" key="part_key"/>
<table name="ship_tab" key="date"/>
<condition>order_tab.order_key=part_tab.o_key AND part_tab.part_key=ship_tab.p_key
    </condition>
</RDB_node>
<attribute_node name="Key">
<RDB_node>
<table name="order_tab"/>
<column name="order_key" type="integer"/>
</RDB_node>
</attribute_node>
<element_node name="Customer">
<element_node name="Name">
<text_node>
<RDB_node>
<table name="order_tab"/>
<column name="customer_name" type="varchar(16)"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="Email">
<text_node>
<RDB_node>
<table name="order_tab"/>
<column name="customer_email" type="varchar(16)"/>
</RDB_node>
</text_node>
</element_node>
</element_node>
<element_node name="Part">
<attribute_node name="Color">
<RDB_node>
<table name="part_tab"/>
<column name="color" type="char(6)"/>
</RDB_node>
</attribute_node>
<element_node name="Key">
<text_node>
<RDB_node>
<table name="part_tab"/>
<column name="part_key" type="integer"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="ExtendedPrice">
<text_node>
<RDB_node>
<table name="part_tab"/>
<column name="price" type="decimal(10,2)"/>
<condition>price > 2500.00</condition>
</RDB_node>
</text_node>
</element_node>
<element_node name="Tax">
<text_node>
<RDB_node>
<table name="part_tab"/>
<column name="tax" type="real"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="Quantity">
<text_node>
<RDB_node>
<table name="part_tab"/>
<column name="qty" type="integer"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="Shipment" multi_occurrence="YES">
<element_node name="ShipDate">
<text_node>
<RDB_node>
<table name="ship_tab"/>

Back to top

New DB2 MQ XML Features

The DB2 XML Extender version 7.2 expands its capabilities by adding functions and stored procedures that allow access to the DB2 MQ functions directly from DB2 XML applications. See Using MQSeries from DB2 Applications for an overview of these integration functions. We refer to these functions as the DB2 MQ functions throughout this document and we show you some examples of how to use them here.

MQSeries is the market leading messaging middleware. It is a flexible messaging system that allows applications to communicate in a distributed, heterogeneous environment using different messaging models. The MQ functions and stored procedures described here were developed using the Application Messaging Interface (AMI) of MQSeries. Two key concepts in MQSeries AMI are carried forward into these new DB2 MQ functions. These are:

• Service points: a logical point from which a message may be sent or received. It refers to a particular queue within a queue manager configured through AMI.
• Policies: define the quality of service options (for example, priority, persistence, etc.)

The use of AMI provides a clean separation between messaging actions and the definitions that dictate how those actions should be carried out. The result of this is a new set of easy-to-configure and easy-to-use DB2 MQ functions and stored procedures.

With the new DB2 MQ functions installed as part of DB2 XML Extender, your applications can:

• Use SQL statements to send, read, or receive documents as XML messages in the service points (queues) as defined in MQSeries AMI. The following SQL statement reads an XML message using the default AMI service point configuration.

  Example 5:

  values db2xml.MQREADXML()
• Compose an XML message from tables and send it directly to the message queue. Or, decompose an XML message from the queue into relational tables. Here is an example of how to use the XML Extender procedures to do XML message decomposition and storage.
  
  

  Example 6:

  EXEC SQL CALL db2xml.DXXMQSHRED(:serviceName:serviceName_ind, :policyName:policyName_ind, :dadFileName:dadFileName_ind, :status:status_ind)

  This statement reads an XML message from the queue, decomposes the message using the DAD file that is specified by the dadFileName variable, and stores the untagged data fields into database tables that are specified in the DAD file itself.

All the new DB2 MQ features can be used from any of the DB2-supported programming models (C, Java^TM, SQL, etc). Additionally, after you install a message broker from WebSphere® MQIntegrator® or the MQSeries Publish/Subscribe SupportPac, the Publish/Subscribe messaging type is also available as part of the DB2 MQ features. Because WebSphere MQIntegrator supports the XML standard, more advanced message handling features are available.

Back to top

Limitations

Currently, the DB2 MQ functions are available on the following platforms: Windows NT®, Windows® 2000, AIX®, Sun Solaris, and HP-UX. The support on OS/390® and z/OS^TM is being planned.

The XML messaging document in the examples of this paper is stored as XMLVARCHAR type. Currently, the DB2 MQ functions have a limitation of 4000 characters in length for the XMLVARCHARr type. A matching set of functions and stored procedures is also available to handle XML documents that are stored as XMLCLOB type. These functions and procedures can handle XML documents of up to 1 GB in length. For information about the functions and stored procedures that handle XMLCLOB type document, please refer to DB2 XML Extender MQSeries XML Functions and Stored Procedures, Release Notes, Version 7.2.

Additionally, MQSeries provides the ability for message operations and database operations to be combined in a single unit of work as an atomic transaction. This feature is currently not supported by the DB2 MQ functions.

Back to top

Usage scenarios

There are a wide variety of database application scenarios that can benefit from combining MQSeries and XML messaging. In this section, we review some scenarios in an insurance company that can benefit from these new features. A subset of insurance operation is described in Figure 5. We will continue to use the samples given at the beginning of the article for illustration purposes.

Figure 5. Part of an insurance operation scenario

Sending and retrieving XML messages

The simplest application scenario is probably to exchange datagrams to coordinate multiple applications, to exchange information, request services, and provide notification of interesting events, etc.

For example, assume that you have a database table NEW_BUSINESS that has an XMLVARCHAR type column named INSURANCE_APP. The table is used to store insurance applications submitted through a web site. The following statement sends each insurance application (in XML form) to the default service point (queue).

Example 7:

select db2xml.MQSENDXML(INSURANCE_APP) from NEW_BUSINESS

This operation sends every insurance policy application in the NEW_BUSINESS table to the queue, where they can be picked up by insurance New Business or Underwriting Systems for further processing. This might be one of the first steps in the batch processing an insurance company carries out at the end of each business day.

Suppose an insurance policy application is currently stored as a standalone file outside the DB2 tables. The following SQL statement sends the XML document contained in file "c:\xml\my_application.xml" to the service MY.AMI.SERVICE using policy MY.AMI.POLICY. Here, the correlation identifier 'Group' is used to indicate that this is a group insurance policy application so that the Group Administration System can identify the insurance application in the shared queue and retrieve it for further processing.

Example 8:

values db2xml.MQSENDXML('MY.AMI.SERVICE', 'MY.AMI.POLICY', 'c:\xml\my_application.xml', 'Group')

At the receiving end of the queue, the application can either store the tagged XML formatted document in its entirety in an XML column, or decompose the document using the stored procedures described in Decomposing XML messages [production, code as a reference] to save the information (fields and attributes) in a set of DB2 tables (XML collection).

The following SQL statement simply reads the first insurance application from the queue defined by APPLICATION_SERVICE using the default AMI policy (DB2.DEFAULT.POLICY).

Example 9:

values db2xml.MQREADXML('APPLICATION_SERVICE')

The following SQL statement retrieves and removes the first 50 insurance applications that have a correlation ID of 'Health' (indicating that they are Health Insurance Applications) from the queue. The queue is referred to by the Underwriting_Service AMI service point, and the AMI policy 'Underwriting_Policy' specifies the quality of service. This SQL statement also stores the received insurance applications in the UNDERWRITING table with system date and time information.

Example 10:

insert into UNDERWRITING select current timestamp, t.msg from table (db2xml.MQRECEIVEALLXML('Underwriting_Service',

'Underwriting_Policy', 'Health', 50)) t

Decomposing XML messages

The decomposition stored procedures that incorporate MQ functions include:

• DXXMQINSERT()
• DXXMQINSERTALL()
• DXXMQSHRED()
• DXXMQSHREDALL()

These procedures break down (or "shred") incoming XML documents in the queue and to store individual data elements in existing database tables. The DXXMQINSERT () and DXXMQINSERTALL() procedures decompose all XML documents in a queue, whereas DXXMQSHRED() and DXXMQSHREDALL() only decompose the first message in the queue.

These stored procedures also differ in terms of input parameters. The DXXMQINSERT() and DXXMQINSERTALL() procedures take an enabled XML collection name as input. A DAD file is used as input to generate an enabled XML collection and return the collection name. The DXXMQSHRED() and DXXMQSHREDALL() take a DAD file as input. A stored procedure that uses an enabled XML collection as input generally results in better performance.

In the following stored procedure call, DXXMQINSERTALL() retrieves all XML documents from the queue defined by serviceName, decomposes the documents, and inserts data into the tables according to the mapping that is specified in the DAD file with which it was enabled.

Example 11:

EXEC SQL CALL db2xml.DXXMQINSERTALL(:serviceName
:serviceName_ind,
:policyName :policyName_ind,
:collection :collection_ind,
:status :status_ind)

This SQL statement assumes that an AMI service, indicated by serviceName, and a policy, indicated by policyName have been defined in AMI.

In our insurance application scenario, an insurance policy application document includes at least three groups of information:

• Insured/Owner Information (Name, Address, Date of Birth, Social Security Number, etc.)
• Agent Information (Agent ID, Agent Name, Service Level, etc.)
• Coverage Information (Product ID, Application Date, Insured Amount, etc.)

It makes logical sense to decompose the insurance application message into three tables where each table stores one type of information. However, for simplicity, our examples use only the application and agent tables. The stored procedure call above inserts data records into the two predefined DB2 tables. As a result, each table will contain a record for each insurance application received in the messaging queue. After the insurance application document is broken down into untagged non-XML data elements in relational tables, follow-up processing can be performed using standard SQL-based applications.

Composing XML messages

The composition stored procedures DXXMQGEN() and DXXMQRETRIEVE() are used to generate XML documents from data in database tables. The resulting XML documents are sent to the queue defined through MQSeries AMI. DXXMQGEN() takes a DAD file as input (as shown in Figure 3). DXXMQRETRIEVE() takes an enabled XML collection name as input. They basically produce the same result XML documents (as shown in Figure 1).

The following sample call generates an XML document and sends it to the queue:

Example 12:

EXEC SQL CALL db2xml.DXXMQGEN(:serviceName :serviceName_ind,
:policyName :policyName_ind,
:dad :dad_ind,
:overrideType :ovtype_ind,
:override :ov_ind
:maxrow :maxrow_ind,
:numrow :numrow_ind,
:status :status_ind)

The DAD file specifies how data in various tables will be assembled together to generate the resulting XML document. Users can also specify the maximum number of XML documents to be generated in each call using the max_row parameter, or they can override certain conditions in the DAD file by using the parameters overrideType and override (see definitions in Appendix A). This override feature allows applications to perform dynamic queries by overriding the <SQL_stmt> tag values in the DAD file, or the conditions in RDB_nodes for RDB_node mapping. In the example of Figure 2, the <xcollection> element in the DAD file has an <SQL_stmt> element. The override parameter can override the value of <SQL_stmt>, by changing the ORDER_BY clause, for example.

When an insurance application is approved and the insurance policy is issued, we may want to send some correspondence, such as a policy page summarizing the coverage, to the policy owner. The data for the policy page is readily available in the database after the insurance administration system has completed its policy issuing process. The data is likely to be stored across several different tables and needs to be extracted and formatted. The DB2 MQ stored procedures described above provide a very efficient method for generating a policy page document and sending it to a designated queue. A print job system can receive the XML formatted policy page document from the queue, print it, and mail it to the policy owner.

Publish/Subscribe with WebSphere MQIntegrator

The Publish/Subscribe messaging model can be used in conjunction with IBM WebSphere MQIntegrator software or the simple MQSeries Publish/Subscribe SupportPac. The model is most often used to disseminate real-time information in a timely manner.

For example, the following SQL statement reads an insurance policy application from the queue defined using the AMI Service APPLICATION_SERVICE. It then extracts the Agent ID data component from the XML document using the location path specified, and publishes using PUBLISH_SERVICE and PUBLISH_POLICY defined through AMI. The data element is published under topic 'Agent' with a correlation ID of 'Annuity'. This allows users who have subscribed to the 'Agent' topic to receive this piece of information in their subscriber's service queue defined through AMI, and to further relate the agent to Annuity product.

Example 13:

values db2mq.MQPUBLISH('PUBLISH_SERVICE', 'PUBLISH_POLICY',
db2xml.EXTRACTVARCHAR(db2xml.MQREADXML('APPLICATION_SERVICE'),
'/Application/Agent/Id'),
'VariableRate', 'Annuity')

Because WebSphere MQIntegrator supports XML-based self-defining messages, applications can take advantage of many of the advanced features provided by the product. For example, subscribing applications can now select which publications they receive based not only on the topic of the publication, but also on specific content of the messages, or both.

Back to top

Summary

The combination of DB2 MQ functions and the XML Extender can be used to meet a variety of application needs. The new DB2 MQ XML features are easy to use and provide a powerful repertoire of capabilities and functions. Using a set of functions and stored procedures, you can develop database applications more efficiently, especially when integrating a heterogeneous set of applications across a variety of platforms.

Back to top

Appendixes

Appendix B. Sample C Code of Calling a Stored Procedure

Using the DAD file shown in Figure 3 and the tables application_tab and agent_tab created earlier, the following C program calls the stored procedure provided by DB2 XML Extender and composes an insurance policy application XML document. A sample of the result XML document is shown in Figure 1. The code assumes that a result table is created with the name of XML_APPLICATION_TAB, and that the table has one column XMLAPP of XMLVARCHAR type. The XML document (as shown in Figure 1) will be available from this table column at the end of the program run.

Resources

• Information on the DB2 XML MQ functions can be found at this PDF: http://www.ibm.com/software/data/db2/extenders/xmlext/docs/v72wrk/dxxmq.pdf
  
  
• More information on the DB2 MQ functions can be found in the DB2 Developer Domain article entitled "Using MQSeries from DB2 Applications".
  
  
• IBM DB2 Universal Database XML Extender Administration and Programming, Version 7 Release 1
  
  
• IBM DB2 XML Extender Release Notes, Version 7 Release 2
  
  
• DB2 XML Extender MQSeries XML Functions and Stored Procedures, Release Notes, Version 7.2
  
  

About the authors

Rate this article

Comments

Back to top

Table of contents

• Introduction
• XML Extender Overview
• New DB2 MQ XML Features
• Limitations
• Usage scenarios
• Summary
• Appendixes
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers