Enabling automatic index reorganization in volatile tables on systems running SAP

When running SAP on Db2, enabling automatic reorganization allows you 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.
Warning: Setting the DB2_WORKLOAD registry variable to SAP will enable other registry variables. You can check what these other variables are by running the following command:
```
db2set -gd DB2_WORKLOAD=SAP
```
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 <SID> USING auto_reorg ON
```
where <SID> is the system ID for the database.
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.

Note: In most cases, DB2_WORKLOAD=SAP and auto_reorg ON are default settings for systems running SAP on Db2.

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="Rebuild" indexReorgMode="Online" useSystemTempTableSpace="true" numInxPseudoEmptyPagesForVolatileTables="20" reclaimExtentsSizeForTables="100000" reclaimExtentsSizeForIndexObjects="50000" />
<ReorgTableScope maxOfflineReorgTableSize="1">
<FilterClause>TABSCHEMA NOT LIKE 'SYS%' AND (TABSCHEMA,TABNAME) NOT IN (SELECT TABSCHEMA, TABNAME FROM SYSCAT.EVENTTABLES)</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.

Note: Creating a public alias will fail with SQL1599N.