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 RECLAIM EXTENTS option during the automatic reorganization operations on multidimensional clustering (MDC) or insert time clustering (ITC) tables, you need to specify the reclaimExtentSizeForTables attribute to the ReorgOptions element, along with a threshold value in the XML input files.

To enable the RECLAIM EXTENTS option during the automatic reorganization operations on indexes, you need to specify the reclaimExtentsSizeForIndexObjects attribute to the ReorgOptions element, along with a threshold value in the XML input files.
Note: The threshold specified for reclaimExtentsSizeForIndexObjects applies on an index object level. For a nonpartitioned table the value applies to all indexes on the table combined. For a partitioned table the value applies to each nonpartitioned index separately, and to the indexes of each data partition separately.

The threshold values specified for table or index space reclaim is the minimum size, in kilobytes, of reclaimable space in the table or index before an online reorganization to reclaim space is triggered. This threshold value, if specified, must be an integer larger than zero (0). For example, if you specify a value of 1024 KB for the threshold, only objects with 1 MB of reclaimable space or more are considered for automatic reorganization to reclaim space.

The maxOfflineReorgTableSize attribute can be used to limit the size of the table for which offline table and index reorganization is done.

The maxOfflineReorgTableSize also has an impact on online index reorganization. When running an index reorganization, the REBUILD option is not fully online, as there is a period of unavailability at the end of the operation. When automatic reorganization evaluation determines that indexes need to be rebuilt, the CLEANUP ALL option can be used instead of REBUILD. Two conditions must exist for the CLEANUP ALL option to be used:
  • The indexReorgMode attribute must be set to Online and the maxOfflineReorgTableSize attribute must be set to a non-zero value.
  • The table or any partition (for partitioned tables) must be larger than the value of maxOfflineReorgTableSize, in kilobytes.
The CLEANUP operation can be done online. If the reclaimExtentsSizeForIndexObjects attribute is set to a non-zero value, a REORG INDEX(ES) operation with the RECLAIM EXTENTS option set is done after the CLEANUP. This is in addition to the CLEANUP ALL option and only if the reclaimable space exceeds the set threshold. The maxOfflineReorgTableSize value does not affect column organized tables.
Note: Doing an index reorganization using the CLEANUP ALL option might clear the conditions that trigger a rebuild, but it is not guaranteed to do so.

Authorization

One of the following authorities is required to execute the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

None

Syntax

Read syntax diagramSkip visual syntax diagramAUTOMAINT_SET_POLICY(policy_type ,policy)

The schema is SYSPROC.

Procedure parameters

policy_type
An input argument of type VARCHAR(128) that specifies the type of automatic maintenance policy to configure. The value can be one of:
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. See the sample XML files in the SQLLIB/samples/automaintcfg directory for full details about each element and attribute that can be specified.

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 replaces 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 can use the new "RECLAIM EXTENTS" option to reorganize multidimensional clustering (MDC) or insert time clustering (ITC) tables. To enable this feature, set the "reclaimExtentSizeForTables" 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" reclaimExtentSizeForTables ="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.

Example 3: An online reorganization being done on a table, with the maxOfflineReorgTableSize value set to 1.
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="Rebuild" indexReorgMode="Online" useSystemTempTableSpace="true" reclaimExtentsSizeForTables="100000" reclaimExtentsSizeForIndexObjects="50000" />
<ReorgTableScope maxOfflineReorgTableSize="1">
<FilterClause>TABSCHEMA NOT LIKE 'SYS%' AND (TABSCHEMA,TABNAME) NOT IN (SELECT TABSCHEMA, TABNAME FROM SYSCAT.EVENTTABLES)</FilterClause>
</ReorgTableScope>
</DB2AutoReorgPolicy>')
  )