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?