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.
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
>>-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
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.