Tuesday, August 05, 2008

Simple perl script to automate loading XML data

While troubleshooting a problem at work yesterday, I needed to work with several XML documents that were in a backout queue on one of our IBM WebSphere MQ systems. I soon discovered that it was too difficult to find the data I needed by simply browsing the messages in place in the backout queue.

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" />


No comments: