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

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

Susan 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 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.

Summary: 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.

View more content in this series

Tag this!

Date: 19 Jun 2008
Level: Intermediate
PDF: A4 and Letter (143KB | 13 pages)Get Adobe® Reader®
Activity: 1181 views

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
Step 2: Create DB2 pureXML audit log database
Step 3: Configure WebSphere DataPower SOA appliance

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

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

Description	Name	Size	Download method
Downloads for this article	download.zip	4KB	HTTP

Information about download methods

Resources

Learn

"WebSphere DataPower and DB2 pureXML, Part 1: XML schema and content validation using WebSphere DataPower and DB2 pureXML (developerWorks, May 2008): Understand how DB2 pureXML and the IBM WebSphere DataPower SOA Appliance can complement each other to realize powerful applications, and provide flexible and speedy access to validated XML documents.
"Generate Web services for DB2 9 pureXML" (developerWorks, June 2007): Create Web services using a simple Java™ class to insert and retrieve XML data, into and from DB2 9 using the pureXML feature.
"Exposing DB2 9 pureXML using WebSphere Integration Developer" (developerWorks, September 2007): Follow step-by-step instructions to build, test, and deploy a mediation module that can store well-formed XML documents in a DB2 XML column using a WebSphere Integration Developer module.
"IBM Data Studio: Get Started with Data Web Services" (developerWorks, November 2007): Get very detailed and simple introduction on how to develop your first Data Web Service.
"Data Web Services: Build Web Services the new way to access IBM database servers" (developerWorks, December 2007): Create and customize a Data Web Service. This article provides useful theoretical background on Data Web Services, which includes an architectural overview on Data Web Services, and addresses different aspects of Data Web Services, such as security.
Schematron: Get more information about Schematron. Schematron is a language for making assertions about patterns found in XML documents and can therefore be used to validate the content of XML documents.
IBM WebShpere DataPower SOA Appliance: Get an overview on the IBM DataPower SOA Appliances product line.
developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
Stay current with developerWorks technical events and webcasts.
Technology bookstore: Browse for books on these and other technical topics.

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

Participate in developerWorks blogs and get involved in the developerWorks community.

About the authors

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

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

author1-email=malaika@us.ibm.com

author1-email-cc=

author2-email=cpichle@us.ibm.com

author2-email-cc=