On this screen you can specify when automatic maintenance activites can occur. You can see the configured online and offline maintenance windows.
The default online maintenance window of DB2 allows online maintenance tasks like RUNSTATS and online index REORG at any time. You should not change the online maintenance window.
To change the offline maintenance window, press on Change... besides the Offline maintenance windows overview.
Now, you get a screen called Change Maintenance Windows Specification - Offline Activity. Select a time period according to your needs and press on the OK button. In the example below the offline windows is set to Saturday and Sunday night, beginning at midnight with a duration of 8 hours.
Note: If a REORG gets started by the automatic REORG short before the end of the offline window, it will not be stopped if the end of the offline window is reached. The offline REORG will continue normally, but no further table REORGs get started, even if more tables require reorganization.
Press Next to continue.
Screen 4: Notification
You can ignore this screen.
Press Next to continue.
Screen 5: Activites
Automatic REORG uses the REORGCHK function to decide whether an index or table reorganization is required. The REORGCHK function uses the statistics collected by RUNSTATS. Because the REORGCHK funtion requires up to date statistics, ensure that automatic RUNSTATS is turned on as well.
To turn on automatic REORG, select the check mark Automate for Defragment table and index data(REORG). Now press Configure Settings to continue with the dialog Configure Settings - Defragment Data (REORG) that allows you specify advanced features for automatic REORG.
On the first tab called Table Scope in the dialog, you should perform the following changes:
- Select All tables that all tables are checked, if they require reorganization.
- Select Include system tables that also system tables are reorganized from time to time.
Select Only include tables smaller than specified size and enter a value for the table size, for example 10000 KB. This avoids offline table REORGSs on large tables. For large tables, it is advisable to plan the reorg with care. Because automatic REORG uses an offline reorg to reorganize tables, it is advisable not to use automatic REORG for large tables. You should consider using online table REORG for those large tables to avoid large offline maintenance windows.
Note: This option only avoids REORGs on large tables, the indexes on these tables are still candiates for online index REORG.
Now, switch to the Reorganization options tab.
Use of a temporary table space for REORG
During the REORG operation, DB2 creates a copy of the table and at the end of the REORG, DB2 replaces the original table with the well reorganized copy. If a temporary table space is used for the REORG operation, the table copy is created in the temporary table space. Before finishing the REORG, the copy of the table need to be copied back into the regular table space. This takes additional time. Thus REORGSs using a temporary table spaces are typically slower. Without using a temporary table space the copy of the table is made in the table space where the table that is reorganized resides. As DMS table spaces are used in a SAP environment, you need to have enough space in the DMS table space for the table copy. If the table space is not resizeable, the reorg will fail. After the REORG this additional space still belongs to the DMS table space as free space. The temporary spaces in SAP systems are typically SMS and shrink again after the end of the REORG automatically. Thus SAP recommends to use temporary tablespaces for REORG operations. With the option Use a system temporary table space with a compatible page size turned on, the automatic REORG automatically chooses a temporary table space with a compatible page size for the REORG operation.
Row compression related option for REORG
The next choice is related to the New Features in DB2 UDB V9 - Part 4. If you choose Rebuild, the dictionary that is required for the row compression feature is rebuild during REORG. This could lead into a better compression rate, but costs additional time. In comparison to the overall time of a REORG operation, this should be acceptable. Thus, turn the Rebuild option on.
Index reorganization
The next section on the screen Index reorganization mode decides whether index reorganization is allowed in the online maintenance window. Since the offline maintenance window of SAP systems is typically very small, it is advisable to turn the Online option on.
To accept the changes, press the OK button.
Back on the 5. Activities screen, press Next to continue to the summary screen.
Screen: 6. Summary
Review your settings and press Finish.
The DB2 Control Center now performs all necessary tasks to enable automatic maintenance. If this tasks are succesfully performed, a dialog like the following pops up.
2 Comments
