When an application or user requests information from a database or server, the OPTIMIZER uses catalog statistics to choose the most efficient access path to the data. These statistics are not automatically updated every time a row is inserted, updated, or deleted because of the overhead that would be involved. However, the more accurate the statistics, the better able the optimizer is to choose an efficient method of retrieving data, thereby improving overall database performance. Every DBA needs to ensure that statistics are updated on a regular basis to maximize database performance.
Another common database problem that impacts performance is the gradual fragmentation and disorganization of data over time. After many inserts, updates, and deletes, the data in a database becomes fragmented - spread out over many physical pages, with many gaps and with a physical sequence much different than the logical sequence of data. Reorganization is required to reload the data into a minimum number of physical pages in a physical sequence that optimizes logical data retrieval.
The DBSPACE Analysis tools help you to analyze DBSPACEs and perform maintenance on them to improve performance. These tools are designed to allow you to specify all DBSPACEs or a subset of the DBSPACEs in the database. You can choose what criteria are to be used to identify candidates for maintenance.
There are two basic DBSPACE Analysis tools: one for Update Statistics analysis and another for DBSPACE Reorganization analysis.
You can analyze DBSPACEs at any time since minimal calls are made to the database catalog. Based on the results of the analysis, the actual maintenance jobs can then be scheduled for off-hours, or as needed.
Whenever possible, schedule the actual maintenance during non-peak hours to prevent locking contention with other database users. Extensive updating of the database system catalogs occurs during UPDATE STATISTICS and DBSPACE reorganizations. During periods of high database usage, this can lead to lock contention.
The DBSPACE Analysis tools help keep DBSPACEs tuned by keeping track of the UPDATE STATISTICS and reorganization activities at the DBSPACE level and by executing these functions where and when required based on specified execution parameters. This data is stored in a database table (SQLMSTR.SQLMAINT) that is created during Control Center installation.
When you select Option 2 from the DBSPACE Analysis Utilties screen (Figure 9) to list UPDATE STATISTICS candidates or Option 3 to list REORG candidates, a detailed analysis of each DBSPACE is conducted to determine the degree of need for maintenance. The results of this analysis are displayed. The DBA can then choose what DBSPACEs to reorganize or to run UPDATE STATISTICS against.
The DBSPACE Analysis tool uses a database table (SQLMSTR.SQLMAINT) to maintain information about DBSPACEs in the database. During execution, each DBSPACE is considered for maintenance based on parameters you enter. After execution, the information in the SQLMAINT table is updated to reflect the changes that have occurred.
When you invoke the DBSPACE Analysis tool, it:
The selection process consists of one step for UPDATE STATISTICS analysis and two steps for the reorganization analysis. The steps are:
The Control Center installation process creates SQLMAINT in a public DBSPACE in the target database as shown in Figure 8.
Figure 8. SQLMAINT Table Definition
CREATE TABLE "SQLMSTR"."SQLMAINT"
( "OWNER" CHAR(8),
"DBSPACENAME" CHAR(18),
"DBSPACENO" SMALLINT,
"FREEPCT" SMALLINT,
"PCTINDX" SMALLINT,
"UPSTAT_DATE" DATE,
"UPSTAT_TIME" TIME,
"UPSTAT_ELAPSED" TIME,
"REORG_DATE" DATE,
"REORG_TIME" TIME,
"REORG_ELAPSED" TIME,
"REORG_FREEPCT" SMALLINT,
"REORG_PCTINDX" SMALLINT,
"REORG_STATUS" CHAR(2),
"REORG_WEIGHT" SMALLINT,
"NPAGES" INTEGER )
IN "PUBLIC"."SQLMAINT";
SQLMAINT can be used as a basis for creating your own maintenance reports. You can also update it manually to further control the maintenance process (such as changing the reorganization date to prevent a large DBSPACE from being selected as a candidate for reorganization).
The columns that make up SQLMSTR.SQLMAINT are:
Function 1 of the DBSPACE Analysis Tool (Initialize Control Table) inserts rows for all private and non-system owned public DBSPACEs into the SQLMAINT table. All DATE columns are initialized to "0001-01-01". All TIME columns are set to "00:00:00". The table is then ready for use with the other DBSPACE Analysis tool functions.