IBM's eServer Business Solution Test (BST) team designs, implements, evaluates, and deploys customer-like scenarios. The team consists of iSeries developers in the Rochester laboratory who design scenarios that demonstrate how iSeries technology can be used to build e-business solutions. Although the team is restricted to a test laboratory environment, great effort is made to reflect reality.

Recently, the BST team created an application called Travel Flights Cruises (TFC), which simulates several fictitious companies in a B2B relationship, including a cruise line, a cruise Web site, a travel agency, and a bank. The travel agency and bank used DB2 XML Extender for iSeries to compose, decompose, and archive Extensible Markup Language (XML) documents. The following provides an overview of the DB2 XML Extender for iSeries product and examples of how TFC utilized it.

DB2 XML Extender Overview

XML is extensible in that the language is a metalanguage that allows you to create your own language, depending on the needs of your enterprise. You use XML to capture not only the data for your particular application, but also the data structure. Although it is not the only data interchange format, XML has emerged as the accepted standard. By adhering to this standard, applications can share data without first transforming it using proprietary formats.

DB2 XML Extender helps you integrate the power of IBM's DB2 Universal Database for iSeries (DB2 UDB) with the flexibility of XML. It provides the ability to generate XML documents from existing data, insert rows into tables from XML documents, and store and access XML documents. XML Extender provides new data types, functions, and stored procedures to manage your XML data in DB2 relational databases.

To view IBM's document on DB2 UDB XML Extender Administration and Programming, click here.

XML Document Examples
XML documents were used by the cruise line and the travel agency to handle the processing of buying and booking cruises. These XML documents contain the itinerary or passenger information and allow integration between the cruise line and the travel agency. The following XML documents were used:

• Itinerary--The cruise line provides the travel agency with detailed itinerary information about cruises they have purchased. The travel agency decomposes this XML document into DB2 database tables. The data within these tables is used by other applications to book cruises for specific customers.
• Passenger--The travel agency provides the cruise line with detailed passenger information for the cruises they have booked for specific customers. This XML document is composed based on the information in DB2 database tables.

Decomposition Example (Itinerary)

Let's take a look at the XML Extender pieces of the itinerary application that allows the travel agency to receive the itinerary XML document from the cruise line. The itinerary XML document contains all of the itinerary information for the cruises that were ordered by the travel agency, based on an order number. The itinerary XML document is decomposed by the travel agency into several DB2 database tables. The travel agency uses an XML collection for storage of the XML data and a custom-designed document type definition (DTD) for the itinerary XML document since there were no suitable industry standard DTDs available at the time the application was developed.

Database Details
The XML document is decomposed into five DB2 tables:

• cruise_info
• rooms
• port_of_calls_temp
• activities_temp
• day_temp

The cruise_info table contains cruise information that is received from the cruise line, as shown in the following table:

The rooms table shown below contains the room numbers that were bought from the cruise line. It is used to track the rooms that are sold to the travel agency customers.

Within the Document Access Definition (DAD) file, no option exists to specify that only unique records should be inserted into the database tables. Within the XML document, for each cruise, there is a corresponding set of ports of call and activities listed. When the XML document is decomposed, the appropriate data is inserted into the port_of_calls and activities tables. The travel agency wanted to ensure that only one set of ports of call and activities is inserted in those tables for each cruise to avoid having the tables populated with duplicate records.

To accomplish this, two temporary tables (port_of_calls_temp and activities_temp were used for the decomposition. Two INSERT triggers (Enter_Port_Of_Calls and Enter_Activities) were added to those tables to insert the new row into the port_of_calls and activities tables if they did not already exist. Two more triggers (Remove_Port_Of_Calls and Remove_Activities) were used to delete the data from the temporary files.

The port_of_calls_temp table is a temporary table used for decomposing the XML document. The actual data is stored in the port_of_calls table. The port_of_calls table contains the same type of information, as shown in the following table:

The activities_temp table is a temporary table used for decomposing the XML document. The actual data is stored in the activities table. The activities table contains the same type of information:

The day_temp table contains the day number for the activities and ports of call, as shown in the following table:

DTD
The DTD document used within this example is shown in Figure 1.

Figure 1: This is the sample XML code for the DTD.

XML Document
An example of what the XML document looks like is shown in Figure 2:

Figure 2: Here's the sample XML document.

DTD Mapping Scheme
The following table illustrates how the structure of the XML document relates to the DB2 tables that are used for the decomposition. This is helpful in the creation of the DAD file because it shows how the two structures compare.

DAD
Figure 4 shows the DAD document used within this example. This DAD
file for an XML collection uses RDB_node mapping.

Figure 4: This is the DAD document used within this example.

XML Enablement Commands
The following lists the commands that must be run to decompose the Itinerary.xml document into the DB2 database tables:

1. When using the DTD to validate XML data in the XML collection (VALIDATION set to YES in the DAD file), the following SQL statement must be issued to store the Itinerary.dtd in the DTD repository table:

INSERT INTO DB2XML.DTD_REF VALUES('/javateam/shoreline/dtd/itinerary.dtd', DB2XML.XMLCLOBFROMFILE('/javateam/shoreline/dtd/itinerary.dtd'), 0, 'author_name', 'creator_name', NULL)

2. Since the dxxInsertXML() stored procedure is used to decompose the document, enable the XML collection using the following command:

CALL PGM(QDBXM/QZXMADM) PARM (ENABLE_COLLECTION db_name SHORELINE.CRUISE_INFO '/javateam/shoreline/dad/itinerary.dad')

Java Source
Figure 5 is the Java code snippet that decomposes the itinerary XML document into the database tables with the defined DAD.

Figure 5: This Java code decomposes the itinerary XML document into the database tables with the defined DAD.

Composition Example (Passengers)

This section describes the XML Extender pieces of the passenger application that allows the travel agency to send a passenger XML document to the cruise line. The passenger XML document contains all the passenger information for the booked rooms that have been reserved by customers of the travel agency. The passenger XML document is composed from two DB2 database tables by using SQL mapping.

The travel agency uses an XML collection for storage of the XML data and a custom designed DTD for the passenger XML document since there were no suitable industry standard DTDs available at the time the application was developed.

Database Details
The XML document is composed of data from two existing DB2 tables. The two DB2 tables are cruise_info and passengers. The xml_passenger table is used to store the XML document. The cruise_info table contains cruise information that is received from the cruise line. The layout of the cuise_info table is shown in the previous decomposition example.

The passengers table contains passenger information for a specific room on a cruise. The layout of the passengers table is shown below:

The xml_passenger table is used to store the XML document content. This table is used for composing the passenger XML document. The layout of the xml_passenger table is shown below:

DTD
Figure 6 shows the DTD document used within this example.

Figure 6: This is the DTD document used in the example.

XML Document
Figure 7 is an example of what the XML document looks like.

Figure 7: Here's an example of the XML document.

DTD Mapping Scheme
The following table illustrates how the structure of the XML document relates to the DB2 tables that are used for the composition. This was helpful in the creation of the DAD file because it shows how the two structures compare.

DAD
The code in Figure 9 shows the DAD document used within this example. This DAD file uses SQL mapping.

Figure 9: This is the DAD document used in this example.

XML Enablement Commands
This following lists the commands that must be run to compose the Passenger.xml document from the DB2 database tables:

1. The following SQL statement must be issued to store the passengerUpdate.dtd in the DTD repository table:

INSERT INTO DB2XML.DTD_REF VALUES('/javateam/shoreline/dtd/passengerUpdate.dtd', DB2XML.XMLCLOBFROMFILE('/JavaTeam/Shoreline/dtd/passengerUpdate.dtd'), 0, 'author_name', 'creator_name', NULL)

2. Since the dxxRetrieveXML() stored procedure is used to compose the document, enable the XML collection using the following command:

CALL PGM(QDBXM/QZXMADM) PARM(ENABLE_COLLECTION db_name SHORELINE.XML_PASSENGER '/javateam/shoreline/dad/passengers.dad')

Java Source
The Java code snippet in Figure 10 composes the passenger XML document from the database tables with the defined DAD.

XML: The Key to Easy Data Exchange

XML is the standard for data interchange for B2B e-commerce solutions. With DB2 XML Extender, it is easy to leverage your business information in DB2 databases to engage in B2B solutions using XML to interchange data.

Author's Note: This article includes excerpts from various IBM Web sites. The authors of this article collected the information from these Web sites and organized it in an easy-to-follow format.

Marie Wilson is a Staff Software Engineer on the BST team located at the Rochester, Minnesota, laboratory. She has over 15 years of experience in the software testing field. Her areas of expertise include WebSphere and Java. Marie can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

Sue Schmidt is a Staff Software Engineer on the BST team located at the Rochester, Minnesota, laboratory. She has over six years of experience in the software testing field. Her areas of expertise include WebSphere and Java. Sue can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..