80 DB2 UDB V8.2 on the Windows Environment
Enables RUNSTATS to modify an existing statistics profile in the system
catalog tables without running the RUNSTATS command options of the
updated statistics profile.
The statistics profile is stored in a visible string format, which represents the
RUNSTATS command, in the STATISTICS_PROFILE column of the
SYSIBM.SYSTABLES system catalog table. This is the RUNSTATS command
with SET PROFILE option:
runstats on table <db2user.tablename tablesample bernoulli (20) set profile
Figure 3-15 shows the stored value in the STATISTICS_PROFILE column of the
SYSIBM.SYSTABLES table resulting from the command:
select statistics_profile
from sysibm.systables where name = 'EMPLOYEE' and creator = '<db2user>'
Figure 3-15 Stored RUNSTATS profile in SYSIBM.SYSTABLES
3.4.3 Automatic reorganization
Table reorganization is used to reclaim the fragmented spaces of a table and to
ensure optimal I/O and query performance. The table reorganization process can
consume resources and have an effect on performance. Determining the right
time to reorganize a table is not a easy task for DBAs.
Prior to V8.2, DB2 had in-place reorg for both table and index high-availability
features. The automatic table and indexing de-fragmentation and re-clustering
features introduced in V8.2 enable DBAs to manage table and index reorg
without manual intervention.
The automatic table maintenance objectives define the tables to be evaluated for
the need of reorganization. Periodically, DB2 determines reorg candidates by
evaluating the tables with fresh statistics or tables where an automatic reorg did
not complete previously. Figure 3-16 on page 81 shows when the table or index
reorg will take place after evaluation.
Chapter 3. Administration and management 81
Figure 3-16 Automatic reorg evaluation
F1, F2, and F3 are the formulas used on table statistics; F4 to F8 are for index
statistics. For details, refer to the REORGCHK command in IBM DB2 Universal
Database Command Reference, SC09-4828-01.
The automatic table and index reorganization are run in the offline maintenance
time period. It runs to completion even if the job goes beyond the specified time
period. The internal scheduling mechanism learns over time and estimates job
completion times. If the offline time period is too small for a reorganization
activity, the scheduler will not start the job the next time around. A state-based
collection health indicator in the Health Monitor provides notification of any
reorganizing actions that cannot be handled using the defined maintenance
objectives or any tables that require manual reorganization.
The process for setting up the automatic table reorganization using Configure
Automatic Maintenance wizard is same as setting up automated RUNSTATS:
1. Refer to 3.1.1, “Automatic backup” on page 54 for steps 1 on page 54 to 4 on
page 58. Only offline automatic reorg is supported in this release, so you
must specify the offline maintenance window. In step 4 on page 58, click
Change next to the Offline maintenance window to open a pop-up dialog.
Reorganize indexes
Reorganize indexes
Tables with fresh stats or tables
where an auto reorg did not
complete previously
TSIZE=0 and
F1, F2 or F3 out of
F1, F2 or F3 out of
F7 out of bounds
F7 out of bounds
Reorganize table & indexes
Reorganize indexes
only pages
Every evaluation interval
For every table
82 DB2 UDB V8.2 on the Windows Environment
2. In the Change Maintenance Window Specification - Offline Activity window,
we specify that the offline maintenance window is every Sunday beginning at
midnight for five hours (Figure 3-17). Click OK to close the window.
Figure 3-17 Automated reorg: Specify offline maintenance window
Chapter 3. Administration and management 83
3. Under Select a maintenance activity to configure, select Defragment data
(REORG) and click Configure Settings to identify the tables to be
maintained by DB2 (Figure 3-18).
Figure 3-18 Automated reorg: select activity
84 DB2 UDB V8.2 on the Windows Environment
4. This opens the Configure settings - Defragment data (REORG) window
(Figure 3-19).
In this window, define the tables you want to exclude from the automatic data
reorganization process. Note that the volatile tables are always excluded.
When the filter is defined, click Refresh Resulting Tables to display all of the
tables to be maintained. Review the list and, when it is complete, click OK.
Figure 3-19 Automated reorg: configure setting

Get DB2 UDB V8.2 on the Windows Environment now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.