Control Center Operations Guide for VSE

DBSPACE Analysis Utility Screen

Figure 9 shows the DBSPACE Analysis Utilities screen.

Figure 9. DBSPACE Analysis Utilities Screen

+--------------------------------------------------------------------------------+
|     mm/dd/yyyy                 CONTROL CENTER                         hh:mm:ss |
|*-----------------------  DBSPACE ANALYSIS UTILITIES -------------------------* |
|| DATABASE => SQLDBA                                                          | |
|| OPTION ===> 3                        REFRESH         => 1 (1=YES 2=NO)      | |
||                                                                             | |
|| 1=INITIALIZE CONTROL TABLE           2=UPDATE STATISTICS CANDIDATES         | |
|| 3=LIST REORG CANDIDATES                                                     | |
||                                                                             | |
|| **************************** SELECTION OPTIONS    ************************* | |
||                                                                             | |
|| OWNER         ===> ALL               DBSPACENAME     => ___________________ | |
||                                                                             | |
|| MIN(PAGES)     ==> 0                 MAX(PAGES)      => 9999999             | |
||                                                                             | |
|| DAYS SINCE ======> 14                                                       | |
||                                                                             | |
|| CRITERIA       ==> 1                                                        | |
||                                                                             | |
|| 1=CLUSTERRATIO < 9999                2=UNCLUSTERED INDEX                    | |
|| 3=NOVERFLOW    >  1 %                4=APPLY ALL CRITERIA                   | |
|*------------------------------------------------------------------ SQC08-----* |
|                                                                                |
|PRESS ENTER TO PROCESS                                                          |
|ENTER F1=HELP F3=EXIT                                                           |
+--------------------------------------------------------------------------------+

Most fields are initialized to some value. The first two parameters (DATABASE and OPTION) are required. They identify the function you want to perform and what database the function will be performed upon. These fields are initialized to Option 3 (List REORG Candidates) and the database to which Control Center is currently connected.

Functions

The option field can have the following values:

Function

Description

1 INITIALIZE CONTROL TABLE

Rebuilds the SQLMAINT control table in real time; not as a scheduled job. All previous maintenance data will be erased and a row for each non-system owned DBSPACE will be inserted into the table.

2 UPDATE STATISTICS CANDIDATES

Lists the DBSPACEs that need their statistics

updated. The candidate list is displayed based upon the other selection options chosen.

3 LIST REORG CANDIDATES

This function lists candidate DBSPACEs that require reorganization

based on the specified criteria. It lists each candidate and advises the DBA of the need for reorganization. This is the default option when the screen is first displayed.

The REFRESH parameter (defaults to YES) updates the SQLMAINT table:

1. Adding rows for any DBSPACEs that are not in the SQLMAINT table.
2. Deleting rows for any DBSPACEs that are not in SYSTEM.SYSDBSPACES.

This is done in real time PRIOR to performing the analysis selected. Other DBSPACE entries are not affected and their maintenance data remains intact.

Selection Options

All parameters below the line labeled "SELECTION OPTIONS"

allow you to control the maintenance activity by restricting the number and type of DBSPACEs that will be selected for analysis. In the case of the UPDATE STATISTICS tool, the selected DBSPACEs will all be candidates. In the REORG tool, candidates retrieved from the SQLMAINT table then have the reorganization CRITERIA applied against them and a final status is presented that states whether the DBSPACE is a REORG candidate.

Parameter

Description

OWNER

Is used to specify whether PUBLIC, PRIVATE, ALL, or specific DBSPACE owner(s) will be selected for analysis. The DB2 Server for VSE wildcard character (%) can be used at the beginning and/or end of a specified DBSPACE OWNER to select "like" DBSPACE OWNER names.

DBSPACENAME

Is used to specify a single DBSPACE name or a group of similar DBSPACE names that should be selected for analysis. The DB2 Server for VSE wildcard character (%) can be used at the beginning and/or end of the specified DBSPACE name to select "like" DBSPACE names. This parameter defaults to choosing all DBSPACEs owned by the specified DBSPACE OWNER.

PAGES

Is used to specify the MINIMUM and MAXIMUM DBSPACE sizes (expressed in pages) to be selected for analysis.

DAYS SINCE

Specifies the number of days that must have passed since the last UPDATE STATISTICS or reorganization before the DBSPACE can be considered for candidate analysis. |1 indicates yesterday.

DBSPACE Reorganization Criteria (CRITERIA)

This parameter applies an additional test to

selected DBSPACEs to determine whether they are candidates for reorganization. After the selection options above have been used to select DBSPACE names from the SQLMAINT table, the reorganization CRITERIA chosen are checked against those DBSPACEs and the results are displayed on a list screen (see Figure 11). Those DBSPACEs meeting the criteria should be considered candidates for reorganization. You can select them from the REORG CANDIDATES LIST screen for job submission and scheduling.

You can select one of four different methods of analyzing the need for reorganization in the list of retrieved DBSPACEs. They are:

Criteria

Description

1 CLUSTERRATIO < nnnn

If the cluster ratio of any index in a DBSPACE is less than the stated value, the DBSPACE is considered a candidate for reorganization, regardless of the value of the CLUSTER field. You may specify a value from 1 to 9999.

2 UNCLUSTERED INDEX

If there are any unclustered indexes in the tables in the DBSPACE, the DBSPACE is identified as a candidate.

3 NOVERFLOW ROWS > n%

If the number of overflow rows for any of the tables in the DBSPACE exceeds the value you entered, the DBSPACE is selected as a candidate.

4 APPLY ALL CRITERIA

If any of the above three criteria apply, the DBSPACE is chosen as a candidate for reorganization.

CLUSTER RATIO < 9999 is the default criterion.