Control Center Operations Guide for VSE

DBSPACE Reorganization Analysis Tool

This function lists the DBSPACEs that need to be reorganized. The selection process is conducted by evaluating information in the database catalogs and comparing it to the criteria selected by you.

There are three criteria that are evaluated by the program, any of which may cause a DBSPACE to be selected for reorganization. You can choose any one or all three to be used by the Analyzer tool:

1. The existence of unclustered primary indexes on tables within the DBSPACE. This will typically occur when many inserts and deletes have been performed. It greatly reduces the performance of the database.
2. Overflow pages greater than n% for any table in the DBSPACE. This condition indicates a great deal of fragmentation, caused by updates that have increased the row length. The number of I/O operations required to return selected rows is greatly increased.
3. A CLUSTER RATIO value below the threshold specified by the user.
4. All of the criteria above.

An example of the list generated by the Reorganization Analysis Tool is shown in Figure 11. The list was created by specifying:

|+--------------------------------------------------------------------------------+
||OPTION ===> 3                                                                   |
||                                                                                |
||OWNER ===> PRIVATE                                                              |
||                                                                                |
||PAGES: MINIMUM ==>                    MAXIMUM =========> 8192                   |
||                                                                                |
||DAYS SINCE ======> 14                                                           |
||                                                                                |
||REORG CRITERIA ==> 4                                                            |
|+--------------------------------------------------------------------------------+

Figure 11. DBSPACE Reorganization Analysis List Screen

|

|+--------------------------------------------------------------------------------+
||     mm/dd/yyyy                 CONTROL CENTER                         hh:mm:ss |
||*--------------------------- REORG CANDIDATES LIST ---------------------------* |
||| DATABASE => SQLDBA                                                          | |
|||                                                                             | |
|||   OWNER    DBSPACENAME        LAST DATE   ELAPSED   NPAGES STATUS           | |
|||                                                                             | |
||| _ ANDY     TAYLOR             0001-01-01  00:00:00     128                  | |
||| _ CONNIE   SMITH              1996-11-18  00:01:35     128 NO REORG NEEDED  | |
||| _ FRED     TAYLOR             1996-11-10  00:01:38     128 OVERFLOW ROWS    | |
||| _ MARTIN   MARTIN             1996-11-17  00:02:40     128 NO REORG NEEDED  | |
||| _ MASALI   MASALI             1996-11-17  00:03:00    5120 CLUSTER RATIO    | |
||| _ RAY      MARTIN             1996-11-17  00:02:15    1024 UNCLUSTERED      | |
||| _ TAYLORA  TAYLOR             1996-11-12  00:01:50     128 NO REORG NEEDED  | |
|||                                                                             | |
|||                                                                             | |
|||                                                                             | |
|||                                                                             | |
|||                                                                             | |
|||                                                                             | |
|||                                                                             | |
||*------------------------------------------------------------------ SQC09 ----* |
||                                                                                |
||USE 'X' TO SELECT DBSPACE(S); PRESS ENTER TO SUBMIT                             |
||ENTER F1=HELP F3=EXIT F12=CANCEL                                                |
|+--------------------------------------------------------------------------------+

Figure 11 is an example of the Reorganization Analysis List created by the tool. Note that on this screen the following are displayed:

• the DBSPACE OWNER and NAME.
• the DATE of the last reorganization run for the DBSPACE.
• the ELAPSED time (in hours:minutes:seconds) of the most recent reorganization job for this DBSPACE.
• the size of the DBSPACE.
• the result of the analysis.

From this screen, you can place an "X" next to the DBSPACE(s) for which reorganization is desired. Then, by pressing ENTER, the DBSPACE ANALYSIS SUBMIT screen is displayed and a reorganization job can be scheduled for execution. Only one DBSPACE is reorganized in a single batch job. (See Figure 6 for details concerning the job submission screen).

If more than one DBSPACE is selected, the SUBMIT screen is displayed for the first DBSPACE selected. Once a job is submitted, the LIST screen is re-displayed, with that DBSPACE identified with an asterisk "*" in the select field. If ENTER is pressed again, the SUBMIT screen is displayed for the next DBSPACE with an "X" in the select field. This process is repeated until a reorganization job has been submitted for every DBSPACE selected (unless, of course, F3, F12, or a scroll function key is chosen by you).

In the sample display, seven DBSPACEs were analyzed and three were selected for reorganization. Four DBSPACEs were discounted because they did not meet the criteria selected. Note that one DBSPACE has never been reorganized. Its date and elapsed time fields have initial values. ANDY.TAYLOR is a new entry in the SQLMAINT table.

By pressing F12, you are returned to the previous screen (DBSPACE Analysis Utilities). F3 returns you to the main menu.