WebSphere DataPower and DB2 pureXML, Part 2: DB2 pureXML as an audit log for WebSphere DataPower

An important aspect of structure and content validation of XML document instances is the ability to track validation failures. Part 1 of this series illustrated structure and content validation of XML document instances through XML schema and Schematron. The validation was performed through the IBM® WebSphere® DataPower® SOA appliance. In this article, see how IBM DB2® pureXML™ can further complement the WebSphere DataPower SOA appliance by providing an easily accessed and queried audit log. The scenario illustrated in this article is applicable to any situation where XML document instances are being exchanged.

Susan Malaika (malaika@us.ibm.com), Senior Technical Staff Member, IBM

Susan MalaikaSusan Malaika is a senior technical staff member in IBM's Information Management Group (part of IBM Software Group). Her specialties include XML, the Web, and databases. She has developed standards that support data for grid environments at the Global Grid Forum. In addition to working as an IBM product software developer, she has also worked as an Internet specialist, a data analyst, and an application designer and developer. She has also co-authored a book on the Web and published articles on transaction processing and XML. She is a member of the IBM Academy of Technology.



Christian Pichler (cpichle@us.ibm.com), Data Server Solutions (Co-op), IBM

Christian PichlerChristian Pichler is a co-op from the Technical University of Vienna in Austria, where he is working on his thesis for a double Master's degree in Computer Engineering and Computer Science with a focus on health care. For IBM, Christian is working on technologies for storing XML in DB2, and accessing it through Web services, feeds, and XForms. He is specializing in XML standards for health care.



19 June 2008

Also available in Vietnamese

Prerequisites

The ideas presented in this article follow on from Part 1 of this series, "XML schema and content validation in the context of DataPower and DB2 pureXML" (developerWorks, May 2008) Please refer to that article for the theoretical background on this topic.

Introduction

The previous article in this series illustrated the validation of structure and content of XML document instances through the IBM WebSphere DataPower SOA appliance before storing the XML document instances in a DB2 pureXML database.

The purpose of this article is to illustrate the use of a DB2 pureXML database as an easily accessed and queried audit log for the WebSphere DataPower appliance (see Figure 1). In addition to logging the XML document instances, the audit information can include details of the routing, transformation, or validation of XML document instances through the WebSphere DataPower appliance.

Figure 1. DB2 pureXML audit log scenario
DB2 pureXML audit log scenario

The basic idea of the scenario is that the WebSphere DataPower appliance stores the validation results in a DB2 pureXML audit log database, after performing XML schema and Schematron validation on XML document instances received from the submitting endpoint. An example for the submitting endpoint can be an XForms client or even another information system exchanging XML document instances. The WebSphere DataPower SOA appliance stores a success or failure message in the audit log database, containing detailed information on why the validation failed. Regardless of the validation result, the WebSphere DataPower appliance also forwards the validated XML document instance to the receiving endpoint, which can be, for example, another DB2 pureXML database, or even another application or information system.

A major benefit of the solution illustrated here is that it can be applied to any scenario where XML document instances are exchanged. The exchange of XML document instances can be, for example, between two information or application systems through Web services. A similar article entitled "Using DB2 9 pureXML with WebSphere ESB" (developerWorks, June 2008) describes the use of DB2 pureXML as an audit log for WebSphere Enterprise Service Bus (WESB).


Setting up the scenario

The following sections describe the setup of a simple scenario, as illustrated in Figure 2, that showcases the use of a DB2 pureXML database as an audit log for the WebSphere DataPower SOA appliance. To keep the scenario brief and simple, it is assumed that XML document instances are submitted from a client to the WebSphere DataPower appliance, which validates the XML document instances and then forwards the XML document instances to another DB2 pureXML database.

Figure 2. DB2 pureXML audit log example scenario
DB2 pureXML audit log example scenario

In order to set up the scenario illustrated in Figure 2, follow the steps outlined below:


Step 1: Create XML documents, XML schema, Schematron, and the receiving endpoint

The scenario illustrated in Figure 2 is an extended version of the scenario used in Part 1 of this series. The first step to set up the scenario used in this article is to follow Steps 1 and 2 of Part 1.


Step 2: Create DB2 pureXML audit log database

This section describes the setup of the DB2 pureXML database that serves as an audit log for the validation results from the WebSphere DataPower appliance.

As illustrated in Listings 1 and 2, below, the setup of the audit log database consists of one table and one stored procedure only:

Listing 1. Setup of DB2 pureXML audit log database
DROP DATABASE AUDIT@

CREATE DATABASE AUDIT@

CONNECT TO AUDIT@

CREATE SCHEMA DB2ADMIN@

CREATE TABLE DB2ADMIN.AUDIT
	(ID INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
	 AUDITINFO XML,
	 RECORD XML)@
Listing 2. Setup of the stored procedure to insert audit information into the Audit table
CREATE PROCEDURE insertAuditRecord (IN xmlAuditInfo XML, IN xmlRecord XML)
	SPECIFIC insertAuditRecord
P1: BEGIN

    INSERT INTO DB2ADMIN.AUDIT (AUDITINFO, RECORD)
         VALUES (xmlAuditInfo, xmlRecord);
END P1@

The stored procedure insertAuditRecord is then exposed through a Data Web Service, as shown in Figure 3, below, which means that the Stored Procedure can be called through SOAP or REST requests:

Figure 3. DB2 pureXML audit log database configuration overview
DB2 pureXML audit log database configuration overview

This article will not cover further details on how to create Data Web Services. If you need more information, please read the tutorial "IBM Data Studio: Get started with Data Web Services" (developerWorks, November 2007).


Step 3: Configure WebSphere DataPower SOA appliance

The theoretical background on the WebSphere DataPower SOA appliance relevant to this scenario is introduced in Step 3 of the first article in this series.

The first step is to configure XML Schema validation. In other words, XML documents being sent to this policy on the DataPower appliance are validated against a particular XML schema. To configure XML schema validation, add an XML schema validation processing action to the processing rule, as shown in Figure 4, Number 3:

Figure 4. XML firewall configuration of the DataPower SOA Appliance
XML firewall configuration of the DataPower SOA Appliance

The default behavior of the XML schema validation is that if the validation action fails, the DataPower appliance will respond to the request with a failure message and the HTTP 500 error code back to the client that initially sent the XML document. The standard error message for this case does not contain any specific information on why the validation action failed.

To override the default behavior and provide more information on the validation failure, we include an on-error action in the rule, as shown in Figure 4, Number 2. The on-error action causes the policy to call another rule named Rule #2 (shown in Figure 5, Number 1). If Rule #2 is called, it executes the XSL stylesheet shown in Listing 3.

Figure 5. Screenshot of the XML Firewall configuration of the DataPower SOA Appliance
Screenshot of the XML Firewall configuration of the DataPower SOA Appliance

After executing Rule #2, the default behavior for the on-error action is to abort the processing policy, which can be overridden to continue processing. In this scenario, the on-error action is configured to continue processing if any fatal errors occur during XML schema validation.

Listing 3. XSL stylesheet reading validation failure details and inserting audit record in DB2 pureXML audit log database
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  	xmlns:dp="http://www.datapower.com/extensions"
	extension-element-prefixes="dp"
	exclude-result-prefixes="dp">

  <xsl:output method="xml"/>

  <xsl:template match="/">

    <dp:url-open
	target="http://auditdb2:8080/audit/rest/audit/insertAuditRecord"
	response="ignore"
	data-type="xml"
	content-type="text/xml">
      <q0:insertAuditRecord xmlns:q0="urn:example">
        <q0:_xFFFF_xmlAuditInfo>
          <error>
            <xsl:value-of select="dp:variable('var://service/error-message')"/>
          </error>
        </q0:_xFFFF_xmlAuditInfo>
        <q0:_xFFFF_xmlRecord>
          <xsl:copy-of select="." />
        </q0:_xFFFF_xmlRecord>
      </q0:insertAuditRecord>
    </dp:url-open>

  </xsl:template>

</xsl:stylesheet>

The XSL stylesheet shown in Listing 3 obtains the specific error message that explains why the validation action failed and inserts the error message into the audit log database through the Data Web Service of the DB2 pureXML audit log database. The stylesheet then forwards the original XML document instance to the receiving endpoint.

Now there are two items left the DataPower appliance needs to perform. The first one is applying the Schematron XSL stylesheet to the incoming request XML document instance. After Schematron validation, the WebSphere DataPower appliance forwards the Schematron validation result to the Data Web Service of the audit log database, which inserts the validation result into the DB2 pureXML audit log database. Regardless of the validation result, the DataPower appliance also forwards the request XML document instance receiving endpoint. The Schematron validation and the forwarding are achieved through another XSL stylesheet transformation action, as shown in Figure 4, Number 4, which executes the XSL stylesheet illustrated in Listing 4:

Listing 4. XSL stylesheet executing XSL stylesheet, inserting result in DB2 pureXML audit log database, and forwarding the XML document instance to the receiving endpoint
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	xmlns:dp="http://www.datapower.com/extensions"
	extension-element-prefixes="dp"
	exclude-result-prefixes="dp">

  <xsl:output method="xml" />

  <xsl:template match="/">

    <xsl:variable name="schematronResult">
      <error>
        <xsl:value-of select="dp:transform('local:///simple.xsl', .)" />
      </error>
    </xsl:variable>

    <dp:url-open
	 target="http://aduitdb2:8080/audit/rest/audit/insertAuditRecord"
	 response="ignore"
	 data-type="xml"
	 content-type="text/xml">
      <q0:insertAuditRecord xmlns:q0="urn:example">
        <q0:_xFFFF_xmlAuditInfo>
         <xsl:choose>
           <xsl:when test="$schematronResult/error/text()">
            <xsl:copy-of select="$schematronResult" />
           </xsl:when>
           <xsl:otherwise>
            <success>All validation Steps completed successfully!</success>
           </xsl:otherwise>
         </xsl:choose>
        </q0:_xFFFF_xmlAuditInfo>
        <q0:_xFFFF_xmlRecord>
          <xsl:copy-of select="." />
        </q0:_xFFFF_xmlRecord>
      </q0:insertAuditRecord>
    </dp:url-open>

    <dp:url-open
	target="http://endpointdb2:8080/patient/rest/patient/insertPatient"
	response="xml"
	data-type="xml"
	content-type="text/xml">
      <q0:insertPatient xmlns:q0="urn:example">
        <q0:_xFFFF_xmlRecord>
          <xsl:copy-of select="." />
        </q0:_xFFFF_xmlRecord>
      </q0:insertPatient>
    </dp:url-open>

  </xsl:template>

</xsl:stylesheet>

Demonstration

cURL is a command line tool that transfers files to a specific URL, supporting various protocols, including HTTP, and is available for download (see Resources).

After successfully setting up this example, it's now time to show the DataPower SOA Appliance, DB2 pureXML, and Data Web Services together in action. The XML document instances previously defined in this article are used in this demonstration.

To begin the three XML document instances are submitted to the WebSphere DataPower SOA appliance through the command line tool cURL. The submission of the sample XML document instances is illustrated in Listing 5, below:

Listing 5. Submitting the sample XML document instances
cpichle@DAIRYFARM /tmp
$ curl --data-binary @simple_1.xml http://datapowerbox:2055/
<?xml version="1.0" encoding="UTF-8"?>
<ns1:insertPatientResponse
    xmlns:ns1="urn:example"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

cpichle@DAIRYFARM /tmp
$ curl --data-binary @simple_2.xml http://datapowerbox:2055/
<?xml version="1.0" encoding="UTF-8"?>
<ns1:insertPatientResponse
    xmlns:ns1="urn:example"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

cpichle@DAIRYFARM /tmp
$ curl --data-binary @simple_3.xml http://datapowerbox:2055/
<?xml version="1.0" encoding="UTF-8"?>
<ns1:insertPatientResponse
    xmlns:ns1="urn:example"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

cpichle@DAIRYFARM /tmp
$

After submitting the sample XML document instances, it is possible to check the audit information and the receiving endpoint, which is described in the following sections.

Audit information

The major benefit of using DB2 pureXML as an audit log for the WebSphere DataPower SOA appliance is that it provides an easy-to-access log.

We expect three different error messages and one success message in the audit log database as a result of submitting the three sample XML document instances to the DataPower SOA appliance. The first XML document instance violates the Schematron rule, the second document violates the structure defined in the XML schema and the Schematron rule, and the third XML document instance complies with both, XML schema and Schematron.

It is now possible to query the DB2 pureXML audit log database for the validation results of the XML document instances. The query statement to do so is illustrated in Listing 6. Note that the supplied query statement does not return the original XML document instances contained in the audit log.

Listing 6. Query audit records in the DB2 pureXML audit log database
C:\scenario_2\>db2 CONNECT TO AUDIT

   Database Connection Information

 Database server        = DB2/NT 9.5.1
 SQL authorization ID   = CPICHLE
 Local database alias   = AUDIT

C:\scenario_2\>db2 SELECT ID, AUDITINFO FROM DB2ADMIN.AUDIT

ID  AUDITINFO
--  -------------------------------------------------------------------
22  <error>First name must not be 'christian'!</error>
23  <error>http://watdp.watson.ibm.com:2056/: cvc-particle 3.1: in
           element person with anonymous type, found <name> (in
           default namespace), but next item should be
           identification
    </error>
24  <error>First name must not be 'christian'!</error>
25  <success>All validation Steps completed successfully!</success>

  4 record(s) selected.

C:\scenario_2\>

In addition to the error and success messages, the audit log database furthermore stores the XML document instances the messages belong to. The query to retrieve all messages and XML document instances is illustrated in Listing 7:

Listing 7. Alternative query to retrieve complete audit log records containing the original XML document instance
C:\scenario_2\>db2 SELECT ID, AUDITINFO, RECORD FROM DB2ADMIN.AUDIT

Receiving endpoint

Since the WebSphere DataPower SOA appliance performed the validation of three XML document instances, we can expect three records at the receiving endpoint. In the example in this article, the receiving endpoint is a separate DB2 pureXML database. The database can be queried, as illustrated in Listing 8:

Listing 8. Query XML document instances stored in DB2 pureXML endpoint database
C:\scenario_2\>db2 CONNECT TO HOSPITAL

   Database Connection Information

 Database server        = DB2/NT 9.5.1
 SQL authorization ID   = CPICHLE
 Local database alias   = HOSPITAL

C:\scenario_2\>db2 SELECT ID, COMMENT, RECORD FROM DB2ADMIN.PATIENT

ID  COMMENT  RECORD
--  -------  ----------------------------------------------------------
13           <person
                 xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
                 xsi:noNamespaceSchemaLocation="simple.xsd">
               <identification>1</identification>
               <name>
                 <first>christian</first>
                 <last>pichler</last>
               </name>
             </person>

14           <person
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xsi:noNamespaceSchemaLocation="simple.xsd">
               <name>
                 <first>christian</first>
                 <last>pichler</last>
               </name>
             </person>

15           <person
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xsi:noNamespaceSchemaLocation="simple.xsd">
               <identification>3</identification>
               <name>
                 <first>keith</first>
                 <last>wells</last>
               </name>
             </person>

  3 record(s) selected.

C:\scenario_2\>

Summary

This article has illustrated the practical and convenient use of DB2 pureXML as an audit log for the WebSphere DataPower SOA appliance. The scenario shown is applicable to a variety of scenarios where XML document instances are being exchanged. This article showed how DB2 pureXML adds additional value to the scenario since it provides an easy to query audit log. The audit log information stored in the database is ready for further processing and analyzing XML document instance validation failures. This article has again shown that the combination of the WebSphere DataPower SOA appliance with DB2 pureXML provides a powerful and flexible environment to perform XML document instance validation.


Acknowledgement

Thank you to Bob Callaway and others who have contributed to this work by providing their knowledge and guiding advice.


Download

DescriptionNameSize
Downloads for this articledownload.zip4KB

Resources

Learn

Get products and technologies

  • cURL: Download this command line tool to transfer files to a specific URL, which supports various protocols, including HTTP.
  • Industry Formats and Services with pureXML: Download a great variety of examples, for free! Each example illustrates how to work with XML-based Industry Formats and pureXML. The examples show how to register an XML schema, how to perform validation of XML instance documents, how to query XML data using XQuery or SQL/XML, and much more.
  • IBM Data Studio: Download the development environment used to develop Data Web Services, for free.
  • DB2 Express-C: Download the free version of DB2, which includes the core functionality as the other Data Servers, such as the pureXML technology. DB2 Express-C is free to develop, deploy and distribute.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, XML
ArticleID=314871
ArticleTitle=WebSphere DataPower and DB2 pureXML, Part 2: DB2 pureXML as an audit log for WebSphere DataPower
publish-date=06192008