Processing XML on iSeries, Part 2

Read here the first part of this article, about XML and database integration using Saxon.

In last October I looked at DataDirect's XQuery tools. These guys are serious. Their web site contains one of the best Xquery references on the web today.

If you are an iSeries professional, you may ask yourself WTH is xquery and why you should care.

Xquery is a bit more than yet another XML processing language.

It attempts to deal with XML files in a manner similar to relational databases, and this is good. Many developers find it cumbersome to program an event driven process to deal with basically recurring events, like records in a XML message. It seems easier to write a couple of FOR loops (XQuery) rather than trapping template occurences (xslt). For some tasks, a plain procedural program flow rules. I had to develop some pretty complex XML transformation processes lately, and I found using xquery to be both more productive and more readable than XSLT. It is also easier to understand for non-xml experts.

DataDirect support the standard xquery model exceptionally well. Their XML studio shines, and they also extended the standard and added database support, the result being that now it is really easy to define interactions between XML and databases.

Like almost everything Java, I like to see if it is feasible to run it on iSeries, especially as I immediately realized the benefits of native execution: finally a tool to enable XML integration with DB2 tables, NOT WRITTEN IN RPG, maintainable by non-iseries buffs, and able to be inserted into native iseries business processes.

The bulk of my experience is already detailed here and I will not repeat it in this blog. I can just say that it works.

However, not everything is bright and shiny with DataDirect XQuery.

First, there is performance: on my 520 it sucks. A program that took a couple of seconds on a PC runs for more than 30 seconds. There are simply too many dependencies and jars being loaded to execute a single XQuery program. I believe that this issue can be addressed by not spawning a distinct xquery instance for each xml file, but I haven't got to do anything about it.

Then, there is the price... I will be fair to DataDirect and not disclose the price they wanted for an execution license on a System i model 520, but it was a bit on the high side. Had they asked for a price similar to their X86 license plan, we would have almost certainly bought it....

Processing XML on iSeries, Part 1

I've always felt a bit sorry for the poor folks who insist on using RPG to write new code on the System i platform. I mean no offense, but IMHO clinging to outdated technology and claiming it superior is not a smart move. RPG is simply a very bad fit for many modern information processing tasks. Like processing XML.

Creating XML files is easy. You can think of it as a complex report, and as such a XML file can be created by a plain RPG II program with some O record wizardry.

On the other hand, reading an XML file file is complex. There's white space to ignore, completion checks to do, schemas to validate, DTD to test against, naming spaces, private tags, encodings to parse, and more.

On the gripping hand, we can limit our problem to getting the data out of XML and into data entities that are more familiar to System i developers, like database tables.

Fortunately, there are tools that let you map the relationship between data in XML and data in a table. Some are custom made for the System i and geared for RPG developers. I am deliberately going to ignore them in this post. If I have a choice, I prefer to use ubiquitous technology, that is easy to understand by hordes of programmers. Other solutions require deployment of ESB / ETL / EAI or similar infrastructures. While I am a big fan of ESB, sometimes you need a local solution rather than depend on external processes and processing, not to mention the costs of integration, licenses, and servers.

That leaves us little choice but to focus on Java based tools.

The first one is Saxon, to be found here http://www.saxonica.com .
Saxon is among the leading XML transformation tools. In fact, it is embedded into a very large number of commercial XML processing tools and libraries.

Saxon has an extension, called Saxon-SQL, that lets you embed SQL data modification statements into a XSLT transformation stylesheet.
You need Java at version of at least 1.4 , and the Saxon distribution. The free Saxon-B is good enough for our needs, but I recommend that you get Saxon-SA - at £300.00 per server it is a real bargain.
The JT400 package is also required in order to use JDBC connections to the System i database.

I unzipped the entire distribution into a new directory: /QIBM/UserData/saxon

Now let's peek at the XML and database integration task.
Let's suppose that we are to insert into the Work Order application table data from XML.

The XML looks like this:

<?xml version="1.0"?>
<WO_OUT>
<ROW>
<WO>U10002</WO>
<ITEM>80012470</ITEM>
<FACILITY>PL</FACILITY>
<START_DATE>20081126</START_DATE>
<DUE_DATE>20081129</DUE_DATE>
<QUANTITY>654321</QUANTITY>
<TV_TRANSACTION_ID>85246786</TV_TRANSACTION_ID>
<INTERFACE_TRANSFER_DATE>20081015</INTERFACE_TRANSFER_DATE>
<INTERFACE_TRANSFER_TIME>110903</INTERFACE_TRANSFER_TIME>
</ROW>
</WO_OUT>

The mapping to the data table columns is:

ITEM --> TCPROD
FACILITY --> TCFAC
START_DATE --> TCRDTE
DUE_DATE --> TCDDTE
QUANTITY --> TCQORD
TV_TRANSACTION_ID --> TCTRID
INTERFACE_TRANSFER_DATE --> TCDATE
INTERFACE_TRANSFER_TIME --> TCTIME

The following XSLT maps the XML values into the corresponding database columns. It is based on the sample that comes with Saxon.

Notice the following special interest items:

• The stylesheet refers the DB2 JDBC driver. If you provide the correct library at runtime, this code can be executed on the System i server as well as on an external server. We will use the JT400Native.jar library for internal processing
• The system name, user, password and library are resolved at runtime thru external parameters. The user and password are provided as part of the connection string.
  
• normalize-space is used to take care of extra white space like spaces, tabs, carriage returns
• The INTERFACE_TRANSFER_DATE and INTERFACE_TRANSFER_TIME values are optional and possibly will not exist. Therefore TCDATE and TCTIME must be manipulated to set numeric defaults.
• I recommend to set all database fields to allow null values in case of more missing data.
• Do not use 1 char fields. All char fields must have length of at least 2.
  
• The database table should be journalled - there is an implicit commit.
  

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:fn="http://www.w3.org/2005/xpath-functions"
xmlns:sql="java:/net.sf.saxon.sql.SQLElementFactory"
xmlns:saxon="http://saxon.sf.net/"
extension-element-prefixes="saxon sql">

<!-- insert your database details here, or supply them in parameters -->
<xsl:param name="driver" select="'com.ibm.as400.access.AS400JDBCDriver'"/>
<xsl:param name="LIBRARY"/>
<xsl:param name="SYSNAME"/>
<xsl:param name="SYSUSER"/>
<xsl:param name="SYSPASSWORD"/>

<xsl:param name="database"
select="concat('jdbc:as400://', $SYSNAME,
             ';user=' , $SYSUSER, ';password=',
              $SYSPASSWORD, ';naming=system;libraries=', $LIBRARY)"/>
<xsl:param name="user"/>
<xsl:param name="password" />

<!-- This stylesheet writes the book list to a SQL database -->

<xsl:variable name="count" select="0" saxon:assignable="yes"/>

<xsl:output method="xml" indent="yes"/>

<xsl:template match="WO_OUT">
<xsl:if test="not(element-available('sql:connect'))">
<xsl:message>sql:connect is not available</xsl:message>
</xsl:if>

<xsl:message>Connecting to
    <xsl:value-of select="$database"/>...</xsl:message>

<xsl:variable name="connection"
 as="java:java.sql.Connection" xmlns:java="http://saxon.sf.net/java-type">
<sql:connect driver="{$driver}" database="{$database}"                   
         user="{$user}" password="{$password}">
<xsl:fallback>
  <xsl:message terminate="yes">SQL extensions are not installed</xsl:message>
</xsl:fallback>
</sql:connect>
</xsl:variable>

<xsl:message>Connected...</xsl:message>

<xsl:apply-templates select="WO_OUT">
<xsl:with-param name="connection" select="$connection"/>
</xsl:apply-templates>

<xsl:for-each select="ROW">
<sql:insert connection="$connection" table="KFXT">
 <sql:column name="TCCMPN" select="normalize-space(WO)"/>
    <sql:column name="TCPROD" select="normalize-space(ITEM)"/>
    <sql:column name="TCFAC" select="normalize-space(FACILITY)"/>
    <sql:column name="TCRDTE" select="normalize-space(START_DATE)"/>
    <sql:column name="TCDDTE" select="normalize-space(DUE_DATE)"/>
    <sql:column name="TCQORD" select="normalize-space(QUANTITY)"/>
    <sql:column name="TCTRID" select="normalize-space(TV_TRANSACTION_ID)"/>

    <sql:column name="TCDATE"
       select="if (string-length(normalize-space(INTERFACE_TRANSFER_DATE)) != 0)
                 then normalize-space(INTERFACE_TRANSFER_DATE) else '0.0'"/>
    <sql:column name="TCTIME"
       select="if (string-length(normalize-space(INTERFACE_TRANSFER_TIME)) != 0)
                 then normalize-space(INTERFACE_TRANSFER_TIME) else '0.0'"/>

</sql:insert>
<saxon:assign name="count" select="$count+1"/>
</xsl:for-each>

<xsl:message>Inserted <xsl:value-of select="$count"/> records.</xsl:message>
<sql:close connection="$connection"/>

</xsl:template>

</xsl:stylesheet>

To execute this, you will have to run the QSH java command:

• Set the classpath to include saxon9.jar, saxon9-sql.jar and jt400Native.jar.
• We will use localhost and the current user credentials. If you connect from a remote server, you will need the actual user and password.

here it is embedded in a shell script:

#!/usr/bin/qsh
CP=/QIBM/UserData/Saxon/saxon9.jar
CP=$CP:/QIBM/UserData/Saxon/saxon9-sql.jar
CP=$CP:/QIBM/ProdData/OS400/jt400/lib//jt400Native.jar
java -Djava.version=1.5 -cp $CP  net.sf.saxon.Transform 
     input.xml   insert-table.xslt  LIBRARY=MYLIB  SYSNAME=localhost
     SYSUSER=*current  SYSPASSWORD=*current

and here it is in a CL program

PGM
DCL &CP         *CHAR      1024
dcl &XQ         *char      1024
dcl &PATH2LIBS  *char      1024   '/QIBM/UserData/Saxon'
dcl &PATH2JDBC  *char      1024   '/QIBM/ProdData/OS400/jt400/lib'

CD 'myDirectory'

chgvar &XQ         ('net.sf.saxon.Transform')
CHGVAR VAR(&CP  ) VALUE( +
        &PATH2LIBS *tcat  '/saxon9.jar' *tcat ':' *tcat +
        &PATH2LIBS *tcat  '/saxon9-sql.jar'  *tcat ':' *tcat +
        &PATH2JDBC *tcat  '/jt400Native.jar' *tcat ':' *tcat +
         '.' +
         )

JAVA CLASS(&XQ) +
 CLASSPATH(&CP) +
 parm(                                      +
      'input.xml'                           +
      'insert-table.xslt'                   +
      'LIBRARY=MYLIB'                         +
      'SYSNAME=localhost'                       +
      'SYSUSER=*current'                        +
      'SYSPASSWORD=*current'                    +
      )                                 +
 PROP((java.version 1.5)) +
 OUTPUT(*     )

RETURN
ENDPGM