AUTOMAINT_SET_POLICYFILE procedure - configure automatic maintenance policy

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

Read syntax diagramSkip visual syntax diagramAUTOMAINT_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 database instance directory.
Note: When the file name is specified with a relative path, the correct path separator for the database 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 database 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.