I decided to exploit the XML capabilities of IBM DB2 version 9.5 fixpack 1. I would first extract the XML messages and then load them into a database table. I could easily create queries or views to extract and manipulate the data buried within the XML message.
I used the following steps to load the XML messages into a DB2 database.
- Create a new DB2 database using a command similar to this:
db2 create database DB951 using codeset utf-8 territory us
- Use the q utility to dump all queued XML messages into a flat file. Each line in the file was a complete XML document.
q -m QMNAME -i QL.FLAT > flat.xmls
- Create a rather simple perl script to split each line of the flat file into a separate XML file. The perl script also creates some useful DB2 commands to load the messages into a newly created table in the DB951 database.
perl split.pl flat.xmls
The split.pl file contained
#!/usr/bin/perl -w
$database = "DB951";
$schema = "SCHEMA";
$table = "FLAT";
$column = "doc";
open (CMD, "> db2cmds.sh");
print CMD << HeAdEr;
#!/bin/sh -x
db2 connect to $database
db2 drop table $schema.$table
db2 "create table $schema.$table($column XML)"
rm import$table.sql
echo "import from load$table.del of del XML from . commitcount 5 insert into $schema.$table($column)" > import$table.sql
echo "@" >> import$table.sql
db2 -td@ -vf import$table.sql
HeAdEr
open (LOAD, "> load$table.del");
$ndex = 0;
while($_ = <>) {
$ndex++;
$file = "$table$ndex.xml";
print LOAD "<XDS FIL='$file' />\n";
open (XML, "> $file");
print XML $_;
close (XML);
}
close (LOAD);
exit;
The db2cmds.sh file looks like this after executing the perl script. I executed the db2cmds.sh script file to load several XML documents into the SCHEMA.FLAT table.
#!/bin/sh -x
db2 connect to DB951
db2 drop table SCHEMA.FLAT
db2 "create table SCHEMA.FLAT(doc XML)"
rm importFLAT.sql
echo "import from loadFLAT.del of del XML from . commitcount 5 insert into SCHEMA.FLAT(doc)" > importFLAT.sql
echo "@" >> importFLAT.sql
db2 -td@ -vf importFLAT.sql
The loadFLAT.del file will contain one line similar to the following for each XML message retrieved from the backout queue. The value for nnn will start at 1 and increase by 1 up to the maximum number of XML messages to be processed.
<XDS FIL="FLATnnn.xml" />
