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.
reclaimExtentsSizeForIndexObjectsattribute to the ReorgOptions element, along with a threshold value in the XML input files.
reclaimExtentsSizeForIndexObjectsapplies 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 must be 0 or larger. 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.
Authorization
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
Default PUBLIC privilege
None
Syntax
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.