DB2 Version 9.7 for Linux, UNIX, and Windows

AUTOMAINT_SET_POLICY procedure - configure automatic maintenance policy

You can use the AUTOMAINT_SET_POLICY system stored procedure to configure automatic maintenance for the database. This procedure takes two parameters: the type of automatic maintenance to configure; and a BLOB containing XML that specifies the configuration.

To enable the reclamation of extents during the automatic reorganization operations on multidimensional clustering (MDC) tables, you need to specify the "reclaimExtentsSizeForMDCTables" attribute to the ReorgOptions element, along with a threshold value in the XML input files. This threshold value is the minimum size, in kilobytes, of free space inside the table that can be reclaimed. This value must be 0 or larger. For example, if you specify a value of 1024 KB for the threshold, only tables with 1 MB of free space or more are considered for automatic reorganization to reclaim extents.

Syntax


>>-AUTOMAINT_SET_POLICY--(--policy_type--,--policy--)----------><

The schema is SYSPROC.

Table function parameters

policy_type

An input argument of type VARCHAR(128) that specifies the type of automatic maintenance policy to configure. The argument can be one of the following values:

AUTO_BACKUP: automatic backup

AUTO_REORG: automatic table and index reorganization

AUTO_RUNSTATS: automatic table runstats operations

MAINTENANCE_WINDOW: maintenance window

policy

An input argument of type BLOB(2M) that specifies the automatic maintenance policy in XML format.

Authorization

EXECUTE privilege on the SYSPROC.AUTOMAINT_SET_POLICY procedure.

Examples

Example 1: To set the current automatic maintenance settings for runstats operations:

CALL SYSPROC.AUTOMAINT_SET_POLICY
  ( 'AUTO_RUNSTATS', 
    BLOB(' <?xml version=\"1.0\" encoding=\"UTF-8\"?>
      <DB2AutoRunstatsPolicy 
          xmlns=\"http://www.ibm.com/xmlns/prod/db2/autonomic/config\">
        <RunstatsTableScope><FilterCondition/></RunstatsTableScope>
      </DB2AutoRunstatsPolicy>')  
  )

This will replace the current automatic statistics collection configuration with the new configuration contained in the XML document that is passed as the second parameter to the procedure."

Example 2: The automatic reorganization feature of DB2® can use the new "RECLAIM EXTENTS ONLY" option to reorganize multi dimensional clustering (MDC) tables. To enable this feature, set the "reclaimExtentsSizeForMDCTables" value in the AUTO_REORG policy:

CALL SYSPROC.AUTOMAINT_SET_POLICY
  ('AUTO_REORG', 
  BLOB(' <?xml version=\"1.0\" encoding=\"UTF-8\"?>
    <DB2AutoReorgPolicy 
        xmlns=\"http://www.ibm.com/xmlns/prod/db2/autonomic/config\">
      <ReorgOptions dictionaryOption="Keep" indexReorgMode="Online" 
          useSystemTempTableSpace="false" reclaimExtentsSizeForMDCTables ="1024" >
      <ReorgTableScope>
        <FilterClause>TABSCHEMA NOT LIKE 'EMP%'</FilterClause>
      </ReorgTableScope>
    </DB2AutoReorgPolicy>')
  )

There are sample XML input files located in the SQLLIB/samples/automaintcfg directory that you can modify to suit your requirements and then pass the XML content in through the BLOB() scalar function as in the example.