You can use the AUTOMAINT_SET_POLICYFILE system stored
procedure to configure automatic maintenance for the database. This
procedure takes two parameters: the type of automatic maintenance
to configure; and the name of an XML document that specifies the configuration.
This procedure return the SQL success or SQL error code.
Syntax
>>-AUTOMAINT_SET_POLICYFILE--(--policy_type--,--policy_file_name--)-><
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 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_file_name
- An input argument of type VARCHAR(2048) that specifies the name
of the file that is available in the tmp subdirectory
of the DB2® instance directory.
Note: When the file name is specified with a relative path, the
correct path separator for the DB2 Server must be used and the directory and file should exist with
read permission.
- For example:
- On UNIX if the instance
directory is defined as $HOME/sqllib. For a
policy file named 'automaint/policy.xml', the
file name will be '$HOME/sqllib/tmp/automaint/policy.xml'
- On Windows, the instance
directory name can be determined from the values of the DB2INSTPROF registry variable and the DB2INSTANCE environment variable. For a policy file named 'automaint\policy.xml', if db2set gives DB2INSTPROF=C:\DB2PROF and %DB2INSTANCE%=db2, then the file name will
be C:\DB2PROF\db2\tmp\automaint\policy.xml
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
Example
To modify the current automatic
maintenance settings for automatic backup:
call sysproc.automaint_set_policyfile( 'AUTO_BACKUP', 'AutoBackup.xml' )
This will replace the current automatic backup configuration
settings with the new configuration contained in the AutoBackup.xml file located in the tmp directory under the DB2 instance directory.
There
are sample XML input files located in the SQLLIB/samples/automaintcfg directory which can be used as reference to create policy xml files.
DB2 Version 10.1 for Linux, UNIX, and Windows