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.