DB2 Version 9.7 for Linux, UNIX, and Windows

Enabling automatic index reorganization in volatile tables

You can enable automatic reorganization to perform index reorganization in volatile tables.

About this task

If you enable automatic index reorganization in volatile tables, automatic reorg checks at every refresh interval whether the indexes on volatile tables require reorganization and schedules the necessary operation using the REORG command.

Procedure

To enable automatic index reorganization in volatile tables, perform the following steps:

Set the DB2_WORKLOAD registry variable to SAP. The following example shows how to set this variable using the db2set command:
```
db2set DB2_WORKLOAD=SAP
```
Restart the database so that this setting takes effect.
Set the auto_reorg database configuration parameter to ON. The following example shows how to set this database configuration parameter using the DB2® CLP command line interface:
```
UPDATE DB CFG FOR SAMPLE USING auto_reorg ON
```
Ensure that the auto_maint and auto_tbl_maint database configuration parameters are also set to ON. By the default, auto_maint and auto_tbl_maint are set to ON.
Set the numInxPseudoEmptyPagesForVolatileTables attribute in the AUTO_REORG policy by calling the AUTOMAINT_SET_POLICY or AUTOMAINT_SET_POLICYFILE procedure. This attribute indicates the minimum number of empty index pages with pseudo deleted keys required to perform the index reorganization. The following example shows how to set this attribute:
```
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" numInxPseudoEmptyPagesForVolatileTables="20" />

 <ReorgTableScope  maxOfflineReorgTableSize="0">
  <FilterClause>TABSCHEMA NOT LIKE 'SYS%'</FilterClause>
 </ReorgTableScope>
</DB2AutoReorgPolicy>')
  )
```
You can monitor the values for the PSEUDO_EMPTY_PAGES, EMPTY_PAGES_DELETED, and EMPTY_PAGES_REUSED column by querying the MON_GET_INDEX table function to help you determine an appropriate value for the numInxPseudoEmptyPagesForVolatileTables attribute.