DB2 UDB V8.2 on the Windows Environment

80 DB2 UDB V8.2 on the Windows Environment

򐂰 UPDATE PROFILE ONLY

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

cleanup

only

Tables with fresh stats or tables

where an auto reorg did not

complete previously

TSIZE=0 and

FPAGE>0

F1, F2 or F3 out of

bounds

F1, F2 or F3 out of

bounds

F7 out of bounds

F7 out of bounds

Done

Reorganize table & indexes

Reorganize indexes

cleanup

only pages

Every evaluation interval

Yes

Yes

Yes

Yes

Yes

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