Some fun with DB2 Maintenance Policies and pureXML (Part 2)

In my post yesterday I started to look into retrieving and processing the automated maintenance policies using pureXML. The article ended with the conclusion to use the stored procedure AUTOMAINT_GET_POLICY to fetch the policy document. As stated, the SP returns the policy as BLOB in an output parameter. So, as first step, we need to convert the BLOB to XML and make it available for further processing. My choice is an SQL-based table function.

CREATE OR REPLACE FUNCTION MaintPolicy (poltype varchar(50))
    RETURNS TABLE(poltype varchar(50), poldoc xml)
    NO EXTERNAL ACTION
    MODIFIES SQL DATA
    BEGIN ATOMIC
    declare tpoldocblob blob(2m);
    call sysproc.automaint_get_policy(poltype,tpoldocblob);
    return values(poltype,xmlparse(document tpoldocblob));
END

To choose which kind of policy should be fetched and returned, the policy type is passed in. The prototype above doesn't check for valid parameters. It could be extended for error handling and returning all policy documents if null is passed in. The MODIFIES SQL DATA keyword is needed because the AUTOMAINT_GET_POLICY stored procedure seems to modify data and we ran into an error without it. Within the function we call the SP, fetch the policy as BLOB and then return a table row with the policy type and the policy document. The document is converted to the XML type using XMLPARSE.

By having a table function, we can now "simply" include the policy documents into regular SELECT statement.

select poltype, poldoc from table(MaintPolicy('MAINTENANCE_WINDOW'))

POLTYPE                          POLDOC
-------------------------------- -----------------------------------------------
MAINTENANCE_WINDOW
<DB2MaintenanceWindows xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config">
<!-- Online Maintenance Window  -->
<OnlineWindow Occurrence="During" startTime="22:00:00" duration="07">
  <DaysOfWeek>Fri Sat</DaysOfWeek>
  <DaysOfMonth>All</DaysOfMonth>
  <MonthsOfYear>All</MonthsOfYear>
</OnlineWindow>
</DB2MaintenanceWindows>    

  1 record(s) selected.

The next step is to look into the XML document. This can be done using either XMLQUERY or XMLTABLE. We continue our quest with XMLTABLE, so that we can turn the individual pieces of information into relational columns. The reason is that most tools and administrators prefer relational data.

The obvious way for using XMLTABLE is the following, the result is not:

select t.* from table(MaintPolicy('MAINTENANCE_WINDOW')) as p, xmltable(XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/autonomic/config'), '$POLDOC/DB2MaintenanceWindows' passing p.poldoc as "POLDOC" COLUMNS ONL_DAYS VARCHAR(30) PATH 'OnlineWindow/DaysOfWeek', ONL_DOM  VARCHAR(60) PATH 'OnlineWindow/DaysOfMonth', ONL_MOY  varchar(50) PATH 'OnlineWindow/MonthsOfYear' ) as t
 
SQL20267N  The function "HLOESER.MAINTPOLICY" (specific  "SQL130219093012500")
modifies SQL data and is invoked  in an illegal context. Reason code =  "1". 
SQLSTATE=429BL

The error SQL20267N indicates that our table function is not the last reference in the FROM clause which seems a limitation of calling functions that have MODIFIES SQL DATA. In the explanation and user response sections of the error message the advice is given to rewrite it using a common table expression. Let's try a CTE:

with p(polytpe,poldoc) as
     (select poltype, poldoc from table(MaintPolicy('MAINTENANCE_WINDOW'))) 
select t.* 
from p, xmltable(
   XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/autonomic/config'),
   '$POLDOC/DB2MaintenanceWindows' passing p.poldoc as "POLDOC" 
   COLUMNS
     ONL_DAYS VARCHAR(30) PATH 'OnlineWindow/DaysOfWeek',
     ONL_DOM  VARCHAR(60) PATH 'OnlineWindow/DaysOfMonth',
     ONL_MOY  varchar(50) PATH 'OnlineWindow/MonthsOfYear' ) as t

ONL_DAYS             ONL_DOM                 ONL_MOY                           
-------------------- ----------------------- --------------------
Fri Sat              All                     All           

  1 record(s) selected.


Finally! The function and the query could now be extended and adapted. The WITH clause as a simple form of a common table expression retrieves the policy type and document using our own table function. In the main SELECT we first reference the data of the CTE, then call XMLTABLE for the XML processing. XMLNAMPESPACES is the first function parameter to set the default namespace, i.e., to declare the context for the XML processing. That way we can avoid further namespace declarations within the following clauses. Next, for all the column-related processing, we use "DB2MaintenanceWindows" as the starting point within the XML document. Three columns should be returned, online days, days of month and months of years. Because of the earlier namespace declaration all we need to do is go down to the respective elements.

What is left is to adapt our prototype to the individual requirements and extend it to other policy documents. Do you have questions or comments?

Some fun with DB2 Maintenance Policies and pureXML (Part 1)

To help reduce administrative costs, DB2 supports automatic collection of statistics, automatic database backups, automatic reorganization of tables and indexes, and some more. This so-called automatic maintenance is only an option, it is not necessary to use it with DB2. The specific autonomics can be configured on the database level. If automatic maintenance is switched on, it is a good idea to tell DB2 when it may perform those tasks. You don't want to take indexes, tables, or the entire database offline during peak hours or have I/O-intensive maintenance operations during the day. Your preferences are configured using automated maintenance policies as described in "Scheduled maintenance for high availability".

Policies are specified using XML documents (samples are provided as a starter), then registered with DB2 via either AUTOMAINT_SET_POLICY or AUTOMAINT_SET_POLICYFILE stored procedures. Now that the policies are set, how do you know which are implemented or active? Let's have some fun with DB2, policies, and XML functionality...

To retrieve the policy information, DB2 offers two stored procedures analogous to the SET procedures: AUTOMAINT_GET_POLICY and AUTOMAINT_GET_POLICYFILE. The former returns a BLOB which includes the XML document with the policy, the latter stores the policy document in the file system (only specify the filename and it is stored under ~/sqllib/tmp for most installations). The XML-based information somewhere in the file system is not what we wanted. Looking at AUTOMAINT_GET_POLICY doesn't make us happier either:

 db2 "call automaint_get_policy('MAINTENANCE_WINDOW',null)"

  Value of output parameters
  --------------------------
  Parameter Name  : POLICY
  Parameter Value : x'3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E200A3C4442324D61696E74656E616E636557696E646F7773200A786D6C6E733D22687474703A2F2F7777772E69626D2E636F6D2F786D6C6E732F70726F642F6462322F6175746F6E6F6D69632F636F6E66696722203E0A0A203C212D2D204F6E6C696E65204D61696E74656E616E63652057696E646F7720202D2D3E0A203C4F6E6C696E6557696E646F77204F6363757272656E63653D22447572696E672220737461727454696D653D2232323A30303A303022206475726174696F6E3D22303722203E0A20203C446179734F665765656B3E467269205361743C2F446179734F665765656B3E0A20203C446179734F664D6F6E74683E416C6C3C2F446179734F664D6F6E74683E0A20203C4D6F6E7468734F66596561723E416C6C3C2F4D6F6E7468734F66596561723E0A203C2F4F6E6C696E6557696E646F773E0A3C2F4442324D61696E74656E616E636557696E646F77733E200A'

  Return Status = 0

We cannot directly query the output parameter and it is a BLOB. How about the DB2 system catalog as source for lots of information? SYSTOOLS.POLICY looks promising and it shows up in the package cache with queries related to maintenance policies.

db2 describe table systools.policy

                      Data type                     Column
Column name           schema    Data type name      Length     Scale Nulls
--------------------- --------- ------------------- ---------- ----- ------
MED                   SYSIBM    VARCHAR                    128     0 No   
DECISION              SYSIBM    VARCHAR                    128     0 No   
NAME                  SYSIBM    VARCHAR                    128     0 No   
UPDATE_TIME           SYSIBM    TIMESTAMP                   10     6 No   
POLICY                SYSIBM    BLOB                   2097152     0 Yes  

  5 record(s) selected.

db2 "select med,decision,name from systools.policy"

MED                      DECISION                    NAME                     ------------------------ --------------------------- ---------------------------
DB2CommonMED             NOP                         CommonPolicy               DB2DatabaseRecoveryMED   DBBackupDecision            DBBackupPolicy
DB2TableMaintenanceMED   StatsProfileDecision        StatsProfilePolicy DB2TableMaintenanceMED   TableReorgDecision          TableReorgPolicy   DB2TableMaintenanceMED   TableRunstatsDecision       TableRunstatsPolicy 
 
  5 record(s) selected.

POLICY seems to hold the critical information, but it is a BLOB again. We can change it to XML by parsing the value:

db2 "select xmlparse(document policy) from systools.policy where name='CommonPolicy'"

1
---------------------------------------------------------------------------------
<PolicyDocument xmlns:db2="http://www.ibm.com/xmlns/prod/db2/autonomic/policy" xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/policylang" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ibm.com/xmlns/prod/db2/autonomic/policy ../schema/DB2TableMaintenanceMED.xsd" medScope="DefaultMaintWindow"><PreconditionSection name="CommonMaintWindowPolicySection"><Precondition policyElementId="online_mw"><db2:MaintenanceWindowCondition timeZone="local" windowType="online"><DayOfWeekMask>0000011</DayOfWeekMask><MonthOfYearMask>111111111111</MonthOfYearMask><DayOfMonthMask>11111111111111111111111111111111111111111111111111111111111111</DayOfMonthMask><TimeOfDayMask>T220000/T050000</TimeOfDayMask></db2:MaintenanceWindowCondition></Precondition></PreconditionSection><PolicySection name="f"/></PolicyDocument>    

  1 record(s) selected.


Unfortunately, the output doesn't look like the official version of the policy document. We could extract information from it using XMLTABLE, but it would be based on an internal structure (which could change over time). Thus, we will bite the bullet and will work with AUTOMAINT_GET_POLICY. Read the second part here.

Turn XML data into columnar, relational format using SQL (lots of useful links inside)

The SQL standard offers many functions, some very useful are defined in part 14 of the SQL standard, "ISO/IEC 9075-14:2011 Part 14: XML-Related Specifications". That part 14 was published 2003, so it is rather old. However, one of the gems in SQL/XML (the name for that XML addition to SQL) and in DB2, the function XMLTABLE and its capabilities seem to be hidden as a recent email to me indicates. Or is it how Google, Bing, and other are used...? Anyway, in this article I will provide pointers to some useful resources, mostly older articles, and tag them with the right buzzwords. All in the hope that it is one email less in my inbox...

On IBM's developerWorks is a two-part article giving a detailed overview about XMLTABLE. In part 1 we learn about all the ways XML data can be turned into relational format, including best practices. The 2nd part deals with advanced processing, such as splitting XML documents into smaller pieces (including parameter passing), shredding XML documents during insert processing (turn XML into tables during insert), relational views over XML data, and creating field/value pairs out of XML data (is this already NoSQL...?). Parameter passing for XML processing is also a topic of one of my older posts. XMLTABLE can also be used to extract XML data embedded into another XML document via CDATA. And last, a useful webcast that discussed XMLTABLE in DB2 for both the mainframe and the distributed platform can be found at the pureXML devotees. That group, now driven by IBM customers using pureXML, has upcoming meetings/webcasts. Last but not least, Matthias Nicola has written many articles about XMLTABLE in his blog.

Let's see what the search engines do with this article. Have a nice week...

Call with DB2 experts on XMLTABLE (2009-11-10)

A call with DB2 experts from both z/OS and Linux, UNIX, and Windows will be tomorrow, Nov 10th, at 1pm US Eastern time. Details on this event as part of the "pureXML devotees" activities can be found on the website https://www.ibm.com/developerworks/wikis/display/db2xml/devotee#devotee-xmltable.

Designers, database developers , and DBAs are all invited to join. The call will be recorded, a live chat is available, and materials will be available online. Bring your own food and questions...

XMLTABLE - Explained, the easy way (Part 2, References)

Last week I wrote about XMLTABLE as the "all-in-one" function because it is a very versatile function. Many DB2 customers are using XMLTABLE to allow existing relational applications co-exist with XML data either in the database or on the wire. The first is obvious, XML data is stored in the database and made available by a relational table (view) built on top of the XML data. If XML data is fed to (not into) the database, e.g., via queues, it doesn't necessarily mean it needs to be stored in the XML format. Some customers use the XML format to exchange data with other companies or agencies (think of product information, tax data, payment information, brokerage data, etc.), but process only data stored in purely relational format - no XML involved. What they do is to feed their incoming XML data into the XMLTABLE function and then store the table output in the database.

Today's title "Explained, the easy way" refers to reusing existing excellent information. Two of my colleagues wrote a 2-part article about XMLTABLE that I recommend reading. Part 1 which is titled "Retrieving XML data in relational format" gives an overview, part 2 has lots of examples and is labeled "Common scenarios for using XMLTABLE with DB2".

XMLTABLE - The all-in-one function?! (Part 1, Syntax)

What can produce a relational table out of XML data or a sequence of XML fragments? What can be used to shred (or since the Enron scandal "decompose") data simply by using SQL when ingesting data into a warehouse? What can serve relational applications while managing XML data? Of course I am talking about the XMLTABLE function that is part of the SQL standard and the DB2 pureXML feature.

I plan to post a couple of entries about this very versatile function, hence the "Part 1" in the title. Today, I start with a focus on the syntax for typical usage scenarios.

At first sight the XMLTABLE syntax looks mostly straight-forward:
XMLTABLE "(" [namespace declaration ","] row-definition-XQuery [passing-clause] [COLUMNS column-definitions] ")"

Basically, you could first optionally declare some global namespaces (more later), then comes an XQuery expression similar to those in XMLQUERY and XMLEXISTS to define the row context, then the optional, but familiar PASSING clause and finally the COLUMN definitions similar to a CREATE TABLE statement.

There are usually different ways of writing a (X)query. For the XMLTABLE function, the XQuery clause needs some consideration because it defines the row context, i.e., what part of the XML document is available (and is iterated over) for the column values when each row of the resultset is produced. In some examples in future parts I will show the impact of the XQuery expressions.

The PASSING clause is optional because you could work with constants in your XQuery (not very likely) or use column names to reference the data (e.g., "$DOC" for the DOC column). In many cases you will want to use the PASSING clause to utilize parameter markers, e.g., when directly ingesting application data.

The (optional) column definition is similar to a simple CREATE TABLE statement. You specify the column name and its type (e.g., NAME VARCHAR(20)). After the type comes the most interesting part, the keyword "PATH" followed by a string literal that is interpreted as XQuery expression. Within that XQuery the context (".") refers to that set in the row context (see above). If you would iterate over employees in a department, you could then simply refer to the employees' first- and lastname like shown:

SELECT t.* FROM dept, XMLTABLE('$DEPT/dept/emp' COLUMNS first VARCHAR(20) PATH './first', last VARCHAR(20) PATH './last') as t

Note that for columns all types are supported which are supported by XMLCAST. The reason is that behind the covers XMLCAST is called to map the value identified by the column-related XQuery to the relational column value.

Earlier I mentioned that global namespaces could be declared. Imagine that the department documents all have a default namespace "foo" (e.g., "<dept xmlns="foo"><emp>..."). In order to properly navigate within the documents your query would need to look like shown:

select x.* from dep,xmltable('declare default element namespace "foo";$DOC/dept/emp' COLUMNS first VARCHAR(20) PATH 'declare default element namespace "foo";./first', last VARCHAR(20) PATH 'declare default element namespace "foo";./last') as x

All the different XQueries would need to declare the namespace "foo". To make our lifes simpler, the SQL standard allows to globally declare the namespace using the XMLNAMESPACES function (which usually is used for publishing purposes):

select x.* from dep,xmltable(XMLNAMESPACES(default 'foo'),'$DOC/dept/emp' COLUMNS first VARCHAR(20) PATH './first', last VARCHAR(20) PATH './last') as x

The namespace is declared only once, the statement looks much cleaner and is simpler to write.

That's it for today as an introduction. Please let me know if you have questions on XMLTABLE that you would like to have answered in a future post.