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


<PolicyDocument xmlns:db2="" xmlns="" xmlns:xsi="" xsi:schemaLocation=" ../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.