Data Henrik: February 2013

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">

<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?

Monday, February 18, 2013

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.

Monday, February 4, 2013

(DB2) Redbooks as eBooks (epub)

Do you like Redbooks, IBM's free technical publications, written by subject matter experts? Do you have an e-reader and like reading electronic books? The perfect combination of the two is to download the IBM Redbooks as electronic books in the EPUB format. It is supported on most e-readers and also can be converted to other formats.

As usual, the IBM Redbooks are free to download and free to use, but highly valuable. Here are some IBM Redbooks on DB2 which are available in the EPUB format:

Pages