Getting Started with DB2 XML Extender for iSeries

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:

Key	Field Name	Alias Name	Data Type	Length	VarLen Alloc	Digits, DecPos	Description
P	BOOKING#	BOOKING_NBR	VARCHAR	20	10		Booking number
	CRUISECO	CRUISE_CO	VARCHAR	40	20		Cruise line name
	ORDER#	ORDER_NBR	VARCHAR	20	10		Order number
	PRODID	PRODUCT_ID	VARCHAR	20	10		Product ID
	CRUISEID	CRUISE_ID	VARCHAR	20	10		Cruise ID
	CRUISEDESC	CRUISE_DESC	VARCHAR	30	10		Cruise description (7 Day...)
	ROOMDESC	ROOM_DESC	VARCHAR	30	10		Room description (std ,ocean, ...)
	COSTSING	COST_SINGLE	PACKED			7,2	Cost to the agency for single occupancy
	COSTDOUB	COST_DOUBLE	PACKED			7,2	Cost to the agency for double occupancy
	COSTADD	COST_ADDITIONAL	PACKED			2,2	Cost for each additional person over two
	CUSTCOST	CUST_COST	PACKED			2,2	Cost to the customer with commission added (percentage)
	DUR	DURATION	INT				Duration of cruise
	DEPTDATE	DEPARTURE_DATE	DATE				Departure date of cruise

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.

Key	Field Name	Alias Name	Data Type	Length	VarLen Alloc	Digits, DecPos	Description
P,F	BOOKING#	BOOKING_NBR	VARCHAR	20	10		Booking number
P	ROOMNBR	ROOM_NBR	VARCHAR	10	6		Room number
	STATUS	BOOKED_STATUS	CHAR	1		Y	Room status (O=open, B=booked, D=cruise has sailed)
	PDCRUISE	PD_CRUISE_LINE	CHAR	1		Y	Paid cruise line status (U=unpaid, P=paid)

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:

Key	Field Name	Alias Name	Data Type	Length	VarLen Alloc	Digits, DecPos	Description
	CRUISECO	CRUISE_CO	VARCHAR	40	20		Name of cruise line
	CRUISEID	CRUISE_ID	VARCHAR	20	10		Cruise ID
	DAYNBR	DAY_NUMBER	INT				Day number (e.g., 1, 2, 3, ...)
	PORT	PORT_OF_CALL	VARCHAR	30	10		Name of port
	ARRTIME	ARRIVE_TIME	TIME			Y	Time of arrival at port
	DEPTTIME	DEPART_TIME	TIME			Y	Time of departure from port

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:

Key	Field Name	Alias Name	Data type	Length	VarLen Alloc	Digits, DecPos	Description
	CRUISECO	CRUISE_CO	VARCHAR	40	20		Name of cruise line
	CRUISEID	CRUISE_ID	VARCHAR	20	10		Cruise ID
	DAYNBR	DAY_NUMBER	INT				Day number (e.g., 1, 2, 3, ...)
	ACTTIME	ACTIVITY_TIME	TIME			Y	Time of activity
	DESC	DESCRIPTION	VARCHAR	100	20		Description of activity

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

Key	Field Name	Alias Name	Data type	Length	VarLen Alloc	Digits, DecPos	Description
	DAYNBR	DAY_NUMBER	INT				Day number

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

BookingNumber*, CruiseID, CruiseDescription, RoomDescription,
DepartureDate, ReturnDate, Duration, SinglePrice, DoublePrice,
ExtraOccupantPrice, Schedule, NumberOfRooms, Rooms)>

   location CDATA #REQUIRED
   arrive CDATA #IMPLIED
   depart CDATA #IMPLIED
>

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:

"/javateam/shoreline/dtd/itinerary.dtd">


      123463
      Cruise Company
      9899
      105
      00011
      5-Day Caribbean
      Oceanview
      10/12/2000
      10/15/2000>
      4
      399
      199
      199



            Fireworks off starboard side
of ship

         ...




      5


         ...




      123463
      Cruise Company
      9899
      106
         ...

         ...

         ...

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.

Element	Attribute	Value	Table(s)	Column
Itinerary Information
CruiseProduct
OrderNumber		123463	cruise_info	order_nbr
Company		Cruise Company	cruise_info activities_temp port_of_calls_temp	cruise_co cruise_co cruise_co
ProductID		9899	cruise_info	product_id
BookingNumber		105	cruise_info rooms	booking_nbr booking_nbr
CruiseID		00011	cruise_info activities_temp port_of_calls_temp	cruise_id cruise_id cruise_id
CruiseDescription		5-day Caribbean	cruise_info	cruise_description
RoomDescription		Oceanview	cruise_info	room_desc
DepartureDate		10/12/2000	cruise_info	departure_date
ReturnDate		10/15/2000
Duration		4	cruise_info	duration
SinglePrice		399	cruise_info	cost_single
DoublePrice		199	cruise_info	cost_double
ExtraOccupantCost		199	cruise_info	cost_additional
Schedule
Day	dayNumber	1	day_temp activities_temp port_of_calls_temp	day_number day_number day_number
Port	location	Miami, Florida	port_of_calls_temp	port_of_call
	arrive		port_of_calls_temp	arrive_time
	depart	5:00 PM	port_of_calls_temp	depart_time
Activity		Fireworks off starboard side of ship	activities_temp	description
	time	11:00 PM	activities_temp	activity_time
NumberOfRooms		5
Rooms
Room	roomNumber	111	rooms	room_nbr
Room	roomNumber	...	rooms	room_nbr
Room	roomNumber	115	rooms	room_nbr

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

/javateam/shoreline/dtd/itinerary.dtd
YES

?xml version="1.0"?
!DOCTYPE ItineraryInformation SYSTEM
"/javateam/shoreline/dtd/itinerary.dtd"





cruise_id"/>

cruise_id"/>


        shoreline.cruise_info.booking_nbr =
        shoreline.rooms.booking_nbr and
        shoreline.cruise_info.cruise_co =
        shoreline.port_of_calls_temp.cruise_co and
        shoreline.cruise_info.cruise_id =
        shoreline.port_of_calls_temp.cruise_id and
        shoreline.cruise_info.cruise_co =
        shoreline.activities_temp.cruise_co and
        shoreline.cruise_info.cruise_id =
        shoreline.activities_temp.cruise_id and
        shoreline.day_temp.day_number =
        shoreline.port_of_calls_temp.day_number and
        shoreline.day_temp.day_number =
        shoreline.activities_temp.day_number

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.

...
// Read the XML file
String = null;
byte buf[] = new byte[5000];
try
{
   FileInputStream in = new
     FileInputStream("/javateam/shoreline/xml/TestItinerary.xml");
   in.read(buf, 0, 5000);
}
catch (Exception e)
{
   System.out.println("Error: " + e.toString());
}
xmlItinerary = new String(buf, 0);
// Call the stored procedure
cs = con.prepareCall("CALL db2xml.dxxInsertXML(?, ?, ?, ?)");

// Register the input parameters
cs.setString(1, "SHORELINE.CRUISE_INFO");
cs.setObject(2, xmlItinerary);
cs.registerOutParameter(3, Types.INTEGER);
cs.registerOutParameter(4, Types.VARCHAR);

// Run the stored procedure
cs.execute();
...

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:

Key	Field Name	Alias Name	Data type	Length	VarLen Alloc	Allow null	Description
F	BOOKING#	BOOKING_NBR	VARCHAR	20	10		Booking number
F	ROOMNBR	ROOM_NBR	VARCHAR	10	6		Room number
	NAME1	NAME_1	VARCHAR	50	30		Passenger 1 name
	ADDR1	ADDRESS_1	VARCHAR	40	20		Passenger 1 address
	CITY1	CITY_1	VARCHAR	15	10		Passenger 1 city
	STATE1	STATE_1	CHAR	2			Passenger 1 state
	ZIP1	ZIP_1	CHAR	9			Passenger 1 zip
	COUNTRY1	COUNTRY_1	CHAR	15			Passenger 1 country
	PHONENBR1	PHONE_NBR_1	CHAR	14			Passenger 1 phone number
	DOB1	DATE_OF_BIRTH_1	DATE				Passenger 1 date of birth
	PROCIND	PROCESSED_INDICATOR	CHAR	1			Indicates if passengers list has been sent to cruise company (N=not sent, P = sent)

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:

Key	Field Name	Alias Name	Data Type	Description
	VALID00001	VALID_DOCUMENT	INT	Valid document indicator
	XML_D00001	XML_DOCUMENT	XMLCLOB	Passengers XML document

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

name CDATA #REQUIRED
address CDATA #REQUIRED
>

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.

   "/javateam/shoreline/dtd/passengerUpdate.dtd">





            address="123 Main St, City, State 11111"/>



            address="234 Main St, City, State 33333"/>





            address="987 Main St, City, State 55555"/>

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.

Element	Attribute	Value	Table	Column
Passenger Update	agency	Shoreline Travel
Confirmation	sendConfirm	No
Cruise	cruiseID	1	cruise_info	cruise_id
Room	roomNumber	1	passengers	room_nbr
Passenger1	name	Jane Doe	passengers	name1
	address	123 Main Street, City, State, 11111	passengers	address1 + state1 + zip1

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

/JavaTeam/Shoreline/dtd/passengerUpdate.dtd
NO

   SELECT 'Shoreline Travel' AS AGENCY_NAME, 'No' AS SEND_CONFIRM,
   CRUISE_ID, SHORELINE.PASSENGERS.BOOKING_NBR as BOOK,
   ROOM_NBR, NAME_1, (ADDRESS_1 CONCAT ', ' CONCAT CITY_1 CONCAT ',
   ' CONCAT STATE_1 CONCAT ' ' CONCAT ZIP_1) AS COMPLETE_ADDRESS_1,
   FROM SHORELINE.CRUISE_INFO, SHORELINE.PASSENGERS WHERE
   CRUISE_CO = 'Cruise Company' AND PROCESSED_INDICATOR = 'N' AND
   SHORELINE.CRUISE_INFO.BOOKING_NBR =
   SHORELINE.PASSENGERS.BOOKING_NBR ORDER BY AGENCY_NAME,
   CRUISE_ID, BOOK, ROOM_NBR

?xml version="1.0" encoding="UTF-8"?
!DOCTYPE PassengerUpdate SYSTEM
"/JavaTeam/Shoreline/dtd/passengerUpdate.dtd"

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.

...
// Remove old records from file by deleting records from XML_PASSENGER
stmt.executeUpdate("DELETE FROM SHORELINE.XML_PASSENGER");

// Call the stored procedure
cs = con.prepareCall("CALL
     db2xml.dxxRetrieveXML(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

// Register the input parameters
cs.setString(1, "SHORELINE.XML_PASSENGER");
cs.setString(2, "SHORELINE.XML_PASSENGER");
cs.setString(3, "xml_document");
cs.setString(4, "valid_document");
cs.setInt(5, 0);
cs.setString(6, "NO_OVERRIDE");
cs.setInt(7, 500);
cs.registerOutParameter(8, Types.INTEGER);
cs.registerOutParameter(9, Types.INTEGER);
cs.registerOutParameter(10, Types.VARCHAR);

// Run the stored procedure
cs.execute();

if (!(cs.getInt(9) == 0))
   throw new Exception("Error creating XML document -- error code: " +
                       cs.getInt(9));

// Create the XML document -- /javateam/shoreline/passengers.xml
stmt.executeQuery("SELECT DB2XML.CONTENT(XML_DOCUMENT,

'/JAVATEAM/SHORELINE/passengers.xml') FROM SHORELINE.XML_PASSENGER");
...

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 mawilson@us.ibm.com.

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 sueschm@us.ibm.com.