Tuesday, February 19, 2013

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?