Building the DB2 Health Monitor Sample Application for PHP, Part 2: DB2 pureXML or DOM? You decide

DB2 9 pureXML™ technology allows businesses to improve data integrity and avoid legacy data lock-in. It enables application developers to utilize the best storage medium for the task and eliminate the middle-tier application logic previously required to take advantage of Web services. It enables DBAs to centralize logic at the database server, and to help to optimize performance. More importantly, pureXML technology helps to reduce the complexity of application development back to what it always has been by utilizing the database for what it does best: to describe, retrieve, and store data. This article uses the DB2 Health Monitor Sample Application as an example to show you how this is possible.

Share:

Rizwan Tejpar (rtejpar@ca.ibm.com), DB2 Management Systems Developer, IBM

Rizwan TejparRizwan Tejpar is an Industry Internship Program (IIP) student at IBM, where he develops sample applications for showcasing DB2 working with open-source technologies. He attended the ZendCon conference in 2006 and SDWest conference in 2007, where he demonstrated DB2 pureXML capabilities to developers on the trade show floor. Mr. Tejpar's most recent contribution was the DB2 Health Monitor Sample Application for PHP, which put an SOA spin on database health monitoring. Some of his other programming language experience includes C++, C# .NET, SQL, XQuery, and JSP.



03 January 2008

Also available in Russian Japanese

Information Management: DB2 9

As Part one describes, application developers often use middle-tier programming languages such as PHP, along with the languages' respective DOM parser in order to re-describe and re-structure the data into XML. At an enterprise level, multiply this n-fold. Imagine n-services and n-applications, each of which converts the data into XML, possibly using different techniques. From a software engineering perspective this poses a problem -- specifically regarding maintainability, performance, and data integrity.

First, note that the data is actually being manipulated twice: once by the SQL within the database engine, and again at the application level. This introduces additional risk in that the integrity of the data can no longer be guaranteed since it has been altered at the application level, outside of the database engine. Also, any additional work to parse and construct the data outside of the database engine, while constant in algorithmic complexity, may vary in performance depending on the programming language used.

It is simply not enough for the application architecture within a business to change. The underlying technology which manages the data that drives the business must evolve as well.

IBM DB2 9 database management system addresses this issue through the use of its pureXML capabilities and hybrid database engine. The data that previously could only be stored in the traditional relational format, can now be stored natively as XML as well. As in the case of the DB2 Health Monitor Sample Application, it is also possible to convert traditional SQL data into an XML document on-the-fly within the database engine.

In this sense, DB2 9 allows:

  • The business to improve data integrity and prevent data from being locked into a legacy format
  • The application developer to utilize the best storage medium for the task and eliminate embedded application logic for re-structuring data as XML
  • The database administrator to centralize logic at the database server and optimize and guarantee performance of a specific level

While the DB2 Health Monitor Sample Application uses pureXML from the start, it is still possible to illustrate the thinking involved when attempting the transition to pureXML by examining the queries and code from all three possible approaches:

  1. Multiple SQL Queries with DOM Parsing
  2. Multiple SQL-XML Queries with DOM Parsing
  3. One XQUERY with multiple embedded SQL-XML Queries

Each of the above approaches, from top to bottom, builds on the previous approach and utilizes more pureXML than the last.

**For illustration purposes, the code and queries below will only build the health definitions section of the DB2 Health Monitor Sample Application health report as defined by its XML Schema Definition (XSD).

Scenario 1

The SQL query below extracts the desired health definition information for the database manager, database, tablespace container, and tablespace objects. A standard SQL table is returned; therefore, DOM parsing, as shown below the query, must be used to assemble the data as XML.

Listing 1. SQL query extracting a desired health definition
SELECT  t1.ID, t1.NAME, t1.SHORT_DESCRIPTION, t1.LONG_DESCRIPTION, t1.TYPE, t1.FORMULA, 
		t1.UNIT, t2.EVALUATE, t2.SENSITIVITY, t2.ALARM_THRESHOLD, t2.WARNING_THRESHOLD 
FROM table(health_get_ind_definition('en_US')) as t1,
     table(
            SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD, WARNING_THRESHOLD 
            FROM table(HEALTH_GET_ALERT_CFG('TS', 'G', '','')) as t 
       UNION	
            SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD, WARNING_THRESHOLD 
            FROM table(HEALTH_GET_ALERT_CFG('TSC', 'G', '','')) as t 
       UNION
            SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD, WARNING_THRESHOLD 
            FROM table(HEALTH_GET_ALERT_CFG('DBM', 'G', '','')) as t 
       UNION
            SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD, WARNING_THRESHOLD 
            FROM table(HEALTH_GET_ALERT_CFG('DB', 'G', '','')) as t
     ) as t2
WHERE t1.ID = t2.ID
Listing 2. DOM parsing to assemble the data as XML
$dom = new DOMDocument('1.0');
$root = $dom->appendChild(new DOMElement('DB2Health_Report'));
$defset = $root->appendChild(new DOMElement('HIDefinitionSet'));

for($i = 0; $i < count($result); $i++)
{
	$def = $defset->appendChild(new DOMElement('HIDefinition'));
	$def->setAttribute('hiIdentifier', $result[$i]['ID']);
	$def->setAttribute('hiName', $result[$i]['NAME']);
	$def->setAttribute('hiShortDesc', $result[$i]['SHORT_DESCRIPTION']);
	$def->setAttribute('hiLongDesc', $result[$i]['LONG_DESCRIPTION']);
	$def->setAttribute('hiType', $result[$i]['TYPE']);
	$def->setAttribute('hiFormula', $result[$i]['FORMULA']);
	$def->setAttribute('hiUnit', $result[$i]['UNIT']);

	$settings = $def->appendChild(new DOMElement('HISettings'));
	$settings->setAttribute('sensitivity', $result[$i]['SENSITIVITY']);
	$settings->setAttribute('evaluate', $result[$i]['EVALUATE']);
	$settings->setAttribute('alarmThreshold', $result[$i]['ALARM_THRESHOLD']);
	$settings->setAttribute('warningThreshold', $result[$i]['WARNING_THRESHOLD']);
}

$xml = $dom->saveXML();

Scenario 2

SQL-XML is another standard that allows the XML structure to be defined within the query itself. As Listing 3 illustrates, rather that defining the elements and attributes within DOM, it is done through the query. In other words, for each row that was previously returned as a set of columns, a single XML document is returned.

Listing 3. SQL-XML query that returns a single XML document
SELECT
    XMLELEMENT
    (
        NAME "HIDefinition",
        XMLATTRIBUTES
        (
            t1.ID as "hiIdentifier",
            t1.NAME as "hiName",
            t1.SHORT_DESCRIPTION as "hiShortDesc",
            t1.LONG_DESCRIPTION as "hiLongDesc",
            t1.TYPE as "hiType", 
            t1.FORMULA as "hiFormula",
            t1.UNIT as "hiUnit"			
        ),
        XMLELEMENT
        (
            NAME "HISettings",
            XMLATTRIBUTES
            (
                t2.SENSITIVITY as "sensitivity",
                t2.EVALUATE as "evaluate",
                t2.ALARM_THRESHOLD as "alarmThreshold",
                t2.WARNING_THRESHOLD as "warningThreshold"
            )
        )
    )
FROM table(health_get_ind_definition('en_US')) as t1,
     table(
            SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD, WARNING_THRESHOLD 
            FROM table(HEALTH_GET_ALERT_CFG('TS', 'G', '','')) as t 
       UNION	
            SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD, WARNING_THRESHOLD 
            FROM table(HEALTH_GET_ALERT_CFG('TSC', 'G', '','')) as t 
       UNION
            SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD, WARNING_THRESHOLD 
            FROM table(HEALTH_GET_ALERT_CFG('DBM', 'G', '','')) as t 
       UNION
            SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD, WARNING_THRESHOLD 
            FROM table(HEALTH_GET_ALERT_CFG('DB', 'G', '','')) as t
     ) as t2
WHERE t1.ID = t2.ID

Therefore, only a minimal amount of DOM parsing is required in order to put all of the definitions within a single XML document structure. Listing 4 shows this minimal DOM parsing:

Listing 4. Minimal DOM parsing to put all of the definitions within a single XML document
$dom = new DOMDocument('1.0');
$dom->loadXML('<DB2Health_Report></DB2Health_Report>');

$def_set = new DOMDocument('1.0');
$def_set->loadXML('<HIDefinitionSet>'. $result .'</HIDefinitionSet>');

$root = $dom->getElementsByTagName('DB2Health_Report')->item(0);
$def_set_root = $def_set->getElementsByTagName('HIDefinitionSet')->item(0);

$import = $dom->importNode($def_set_root, true);
$root->appendChild($import);

$xml = $dom->saveXML();

Scenario 3

The query below represents the pureXML alternative to the process described in Scenario 1. Using this approach, the DOM parsing has disappeared entirely and no external concatenation mechanisms are required. XQuery is used as the wrapping mechanism to define the overall structure of the XML document and the SQL-XML query from Scenario 2 is embedded within the structure in order to build the health definition subsection within the report. Normally, only an XQuery statement would be needed; however, since the health data is in the traditional SQL format, the use of SQL-XML is required to transform the data to its XML equivalent.

Listing 5. XQuery to define the overall structure of the XML document
xquery
<DB2Health_Report>
    <HIDefinitionSet>
    {
      for $def in db2-fn:sqlquery('
          SELECT
              XMLELEMENT
              (
                  NAME "HIDefinition",
                  XMLATTRIBUTES
                  (
                      t1.ID as "hiIdentifier",
                      t1.NAME as "hiName",
                      t1.SHORT_DESCRIPTION as "hiShortDesc",
                      t1.LONG_DESCRIPTION as "hiLongDesc",
                      t1.TYPE as "hiType", 
                      t1.FORMULA as "hiFormula",
                      t1.UNIT as "hiUnit"			
                  ),
                  XMLELEMENT
                  (
                      NAME "HISettings",
                      XMLATTRIBUTES
                      (
                          t2.SENSITIVITY as "sensitivity",
                          t2.EVALUATE as "evaluate",
                          t2.ALARM_THRESHOLD as "alarmThreshold",
                          t2.WARNING_THRESHOLD as "warningThreshold"
                      )
                  )
              )
          FROM table(health_get_ind_definition('en_US')) as t1,
               table(
                      SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD,WARNING_THRESHOLD 
                      FROM table(HEALTH_GET_ALERT_CFG('TS', 'G', '','')) as t 
                 UNION	
                      SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD,WARNING_THRESHOLD 
                      FROM table(HEALTH_GET_ALERT_CFG('TSC', 'G', '','')) as t 
                 UNION
                      SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD,WARNING_THRESHOLD 
                      FROM table(HEALTH_GET_ALERT_CFG('DBM', 'G', '','')) as t 
                 UNION
                      SELECT ID, EVALUATE, SENSITIVITY, ALARM_THRESHOLD,WARNING_THRESHOLD 
                      FROM table(HEALTH_GET_ALERT_CFG('DB', 'G', '','')) as t
               ) as t2
          WHERE t1.ID = t2.ID			
      ')
      return $def
    }
    </HIDefinitionSet>
</DB2Health_Report>

Full health report

Again, the above three scenarios only build the definition section of the health report; to build the full structure, as defined by the XSD, this pureXML solution was used.

The above query may seem intimidating. Although it is a single XQuery statement, it contains multiple embedded SQL-XML queries in order to gather health information on four types of database objects. XQuery only acts as a wrapper in order to glue all of the sub-XML documents together. To give an idea of how many queries are used, here is a breakdown per health report section:

Table 1. XQuery sub-query breakdown by Health Report section
Health report sectionNumber of sub-queriesDescription
InfoSet6Health Dashboard Information
HIDefinitionSet1Health Definition Information
AlertSet7Health Alert Information
ErrorSet0Error Information
TOTAL14

Notice the clear level of overlap between the scenarios and the distinct pattern for placement of the assembly logic:

  1. Scenario One: Gets the data as a table structure with the specified columns from the health UDFs. DOM parsing is used to assemble the entire XML structure.
  2. Scenario Two: Gets the data from the same source, but, instead of defining columns, it defines elements and attributes. DOM parsing is used to place the data within overall XML structure.
  3. Scenario Three: Places the structure from scenario two within the overall XML structure using XQuery as the wrapping mechanism.

The above findings can be summarized by the following diagram:

Figure 1. Scenario summary
SCENARIO DIAGRAM

Therefore, with only a single query being used, application maintenance has just been simplified; and from a DBA perspective, the troubleshooting process has just been improved. With the ability to centralize the logic entirely at the database server, in a stored procedure for example, DBAs can now do what they do best: optimize the database to guarantee performance and data integrity. While the data may be in XML, the same ideas of indexing, server-side logic, and query optimization still apply as they did with SQL.

Summary

This article examined the creation of a single DB2 Health Monitoring Service based on existing, traditional SQL data and analyzed it from both an SOA and data-centric perspective. The SOA perspective gives the notion of software stack flexibility -- the idea of utilizing the best technology for the task while maintaining a clear separation between presentation, business logic, and data layers. More importantly, the data perspective gave us the idea of reducing the complexity of an SOA application back to that of traditional application development by utilizing the database engine for what it does best: to describe, retrieve, and store data.

So where do we go from here?

If this single service is capable of monitoring a DB2 database on any server, imagine it monitoring multiple databases on multiple servers. Extending this idea even further, what if all of the XML health reports could be mined, then integrated into a single health report, and finally transformed using an XSLT into an RSS/ATOM feed?

To put it in context, the database engine has just been used to re-describe and re-organize the data into a form that can provide a summarized view of the health status changes of DB2 databases/servers across the entire enterprise.

If DB2 can do this to improve the operational efficiency of the business from an administrative point of view, can similar techniques be applied towards analyzing customer-related data?

Resources

Learn

  • Read Part 1 in this series and learn to use DB2 9 to implement SOA.
  • Browse the technology bookstore for books on these and other technical topics.

Get products and technologies

  • Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.

Discuss

More downloads

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, Open source
ArticleID=280214
ArticleTitle=Building the DB2 Health Monitor Sample Application for PHP, Part 2: DB2 pureXML or DOM? You decide
publish-date=01032008