Within the database, the OPTIMIZER uses catalog statistics to choose the best path to the data within the database. Due to the overhead involved, the database does not automatically update this statistical data during normal database update activity. Without reasonably accurate statistics, the optimizer can choose a less efficient method of retrieving data, reducing the overall database performance. It is therefore the responsibility of the DBA to assure that statistics are updated on a regular basis so that database performance will be maximized.
Another common database problem which impacts performance is the gradual fragmentation and poor organization of data over a period of time. After many inserts, updates, and deletes, the data within a database will become spread over many physical pages, with many gaps in between and with the physical sequence of data different than the logical sequence of data. Periodic reorganization is required to pack the data on the minimum number of physical pages and in a logical sequence that optimizes data retrieval.
The Automated DBSPACE Maintenance tools are known collectively as the SQLMAINT program with its associated functions. They are part of the Database Administration tools. The purpose of the Automated DBSPACE Maintenance tools is to provide a means to automatically monitor DBSPACES and perform maintenance upon them to improve performance. These tools are designed for automatic execution on a scheduled basis on a Control Center support machine, performed at a routine interval with a selected set of parameters.
There are two types of DBSPACE maintenance tools: Statistics Maintenance and DBSPACE Reorganization Maintenance. This chapter explains the steps needed to install SQLMAINT, then explains the function and use of each of the two types of tools.
SQLMAINT jobs should not be scheduled to execute on the service machine, since all automated operations functions with all databases will be disabled while the maintenance functions execute.
Use SQLMAINT during non-peak hours to prevent locking contention with other users of the database. Due to the intensive updating of the database system catalogs, use of this tool during periods of high database use can lead to lock contention.
The parameters used when SQLMAINT is executed will determine the number of DBSPACES that will have maintenance applied. These parameters should be carefully selected to assure that maintenance does not run into peak database use periods.
SQLMAINT automates the UPDATE STATISTICS and the REORGANIZATION processes for a database by keeping track of these activities at the DBSPACE-level and executing these functions where required based on specified execution parameters.
SQLMAINT performs a detailed analysis of each DBSPACE to determine the degree of need for reorganization. The results of this analysis are provided in a hardcopy report for study by the DBA. SQLMAINT can be optionally instructed to automatically reorganize one or more DBSPACES based on the degree of need determined by the analysis. The number of DBSPACES reorganized can be limited by specified quantities or elapsed time limits.
By setting up SQLMAINT to run on a regular schedule, database maintenance can be performed automatically during periods of low database use (e.g., at night or on weekends). SQLMAINT uses a database table to maintain use information for each DBSPACE in the database. When SQLMAINT executes, each DBSPACE is considered for maintenance based on parameters specified by you for that particular execution. After execution, the information within the SQLMAINT table is updated to reflect the changes which have occurred.
When a maintenance function of SQLMAINT is invoked, these steps are performed:
Use of these tools requires Control Center database Administration-level or greater authorization.
You must have database DBA connect authorization to install the SQLMAINT tool into each target database using the install function of the SQLMAINT panel interface. These steps are required to install the SQLMAINT too. They must be completed for each database where SQLMAINT will run. A detailed description of each step follows.
The SQLMAINT tool consists of these files, which must be placed on a CMS minidisk available to SQLMAINT users:
| Note: | These files are part of the Control Center code and reside on the Control Center service machine's code disk, which is typically accessible to users. |
The installation procedure will create a public table within the database (SQLMAINT.SQLMAINT_TABLE). A public DBSPACE must be acquired and available prior to invoking the install process. The DBSPACE name can be any valid database name.
The required size of the public DBSPACE will vary depending on the number of DBSPACES within the target database. The SQLMAINT_TABLE will contain a row for each acquired DBSPACE in the database, which will be used to maintain information about reorganizations done against those DBSPACES using the SQLMAINT tool.
Refer below to DBSPACE Maintenance Utility Panel.
Invoke the panel interface (SQM) of the Control Center; choose Option U (Database Utilities); then Option M (SQLMAINT). At the ensuing panel, select Option I (Install); fill in the database name and then press PF10 to initiate the installation process.
The SQLMAINT installation process will create a public table in the target database as shown in Figure 199. In addition, a row for each non-system-owned DBSPACE will be entered into this table during the install process.
Figure 199. SQLMAINT Table Definition
When SQLMAINT is installed on a database, a control table will be created in a public DBSPACE to store information about maintenance activities. This control table can be used as a basis for creating maintenance reports and can also be updated manually to further control the maintenance process (such as changing the reorganization dates to prevent a large DBSPACE from being reorganized automatically).
The name of the control table defaults to SQLMAINT.SQLMAINT_TABLE unless the SQLOWNER and SQLTNAME variables at the beginning of the SQLMAINT EXEC are changed at your installation. The columns of this table are:
The SQLMAINT.SQLMAINT_TABLE can be viewed at any time to examine the results of SQLMAINT activities. For a discussion of how to use this table, see Using the SQLMAINT Control Table. The SQLMAINT_TABLE is refreshed each time the SQLMAINT function is executed. If you run SQLREORG, the SQLMAINT_TABLE will also be updated. For example:
Shown below is example output from the SQLMAINT installation process.
Figure 200. SQLMAINT Installation Output Example
SQLMAINT version V6.1 03/16/98 13:28:56 INSTALL function executing... Enter a public DBSPACEname for the installation (Or just press ENTER to cancel) tools 03/16/97 13:29:04 Dropping old SQLMAINT.SQLMAINT_TABLE... 03/16/97 13:29:05 Creating new SQLMAINT.SQLMAINT_TABLE... 13:29:06 Refreshing SQLMAINT.SQLMAINT_TABLE... 13:29:06 Deleting SQLMAINT.SQLMAINT_TABLE names not on SYSTEM.SYSDBSPACES... 13:29:06 Refreshing data from SYSTEM.SYSDBSPACES... 13:29:06 0 SQLMAINT.SQLMAINT_TABLE rows updated from SYSTEM.SYSDBSPACES 13:29:06 Get DBSPACE names not in SQLMAINT.SQLMAINT_TABLE... 13:29:57 85 rows from SYSTEM.SYSDBSPACES added to SQLMAINT.SQLMAINT_TABLE SQLMAINT INSTALL completed SQLMAINT ended 03/16/97 13:29:58 Ready; T=3.01/4.97 13:29:58 |
The SQLMAINT install function primes the SQLMAINT_TABLE with entries of all private and non-system-owned public DBSPACES. All date and time columns are set to zeroes. The table is now ready for use by the other functions of SQLMAINT.
The entry panel of the Automated DBSPACE Maintenance tools is shown in Figure 201.
Figure 201. Automated DBSPACE Maintenance Tools Entry Screen
+--------------------------------------------------------------------------------+ |*********************** DBSPACE MAINTENANCE UTILITY ************** ********| | | | FUNCTION ==> LS ( I, LS, US, LR, RP, RR ) - (Install, List Statistics, | | -- Update Statistics, List Reorg Candidates, | | Reorg using Prior List, Run Reorgs - Re-evaluate) | | DATABASE ==> SQMDBA | | -------- | | ----------------------------- Options -------------------------------- | | DBSPACE OWNER ==> ALL (ALL, PUBLIC, PRIVATE, ownername) | | ------------------ | | DBSPACE NAME ==> (blank for ALL, use % for wildcard) | | ------------------ | | SEQUENCE ==> WEIGHT (AGE, WEIGHT) DAYS SINCE ==> 14 | | ------ --- | | TIME LIMIT ==> 180 (Minutes) QTY LIMIT ==> 10 (Max. Run Qty)| | ----- ----- | | STOP ON FAIL ==> NO (YES or NO) REPREP PKGS ==> YES (YES/NO) | | --- --- | | PAGES: MIN ==> 0 MAX ==> 999999 COMMITCOUNT ===> 0 | | ------- ------- | | FILEMODE ==> (Reorg work disk) ALLSTATS ==> NO (YES/NO) | | | | PF1 HELP PF3 QUIT PF6 SCHED PF8 SEL CRITERIA PF9 VIEW TAB PF10 PROCES| +--------------------------------------------------------------------------------+
Most fields are initialized to some values. The first two parameters (FUNCTION and DATABASE) are required. They will identify what function you want to perform and what database the function will be performed upon. These fields are initialized to LS and SQLDBA, respectively. However, if you are currently initialized to a database, that name will appear in the DATABASE field.
The function field can have these values:
The parameters below the line labeled "Options" allow you to control the maintenance functions by restricting the number and type of DBSPACES that will be selected.
The PF Keys offer these options:
The Statistics Maintenance tool consists of the List Statistics and Update Statistics functions.
The main objective of this tool is to choose DBSPACES which need to have UPDATE STATISTICS run based upon the criteria supplied to the tool.
A list of candidates is supplied with the LS option. The US option actually runs the UPDATE STATISTICS utility (SQLDBSU) and maintains the history in the SQLMAINT_TABLE. By using the Job Scheduling tool, the Statistics Maintenance tool can be scheduled to run on a regular basis to cycle through the list of DBSPACES on some determined schedule. Optionally, the job can be scheduled to run on selected DBSPACES after analyzing the report.
This function is chosen by specifying the LS option on the SQLMAINT entry panel. The purpose of this function is to create a list of DBSPACES, based upon the input parameters specified, which need to have UPDATE STATISTICS run.
The output of this run is file "database USTATnnn A", where nnn depends on the DBSPACE OWNER value entered. If ALL was chosen, then nnn=ALL. If PUBLIC was entered, then nnn=PUB. If PRIVATE was entered, then nnn=PRI. If another value was entered, then nnn equals the first three characters of the value. This file is in the format of an SQLDBSU input file with commands to update statistics for the DBSPACES chosen by SQLMAINT. The file can therefore be used as input to a stand-alone DBSU run if desired. Refer to the DB2 Server for VSE & VM Database Services Utility manual for more information on this utility.
This function is chosen by specifying the US option on the SQLMAINT entry panel. The purpose of this function is to run the UPDATE STATISTICS on the DBSPACES chosen according to the parameters supplied.
The output of this run is file "database USTATnnn A" (see LS function above), which is the input to the SQLDBSU to perform the UPDATE STATISTICS on the selected DBSPACES. Another file, SQMINSRT USTATLOG A, will contain the results of the run. The function is either performed at your terminal (PF10) or can be scheduled for later execution (PF6).
Examples of the two files are shown in Figure 202.
Figure 202. Update Statistics DBSU File
COMMENT '************************************************************'
COMMENT '******** Update Statistics for DBSPACEs on SQLDBA *********'
COMMENT '********** OPTIONS: DBSPACEs: PRIVATE.ALL, min=0, **********'
COMMENT '********** max=4097, days=14, qty=5, time=30 ***********'
COMMENT '************************************************************'
UPDATE ALL STATISTICS FOR DBSPACE "MSTRUSR1"."DBSPNAM1";
COMMIT WORK;
UPDATE SQLMAINT.SQLMAINT_TABLE SET UPSTAT_DATE = '97110',
UPSTAT_TIME = '10:47:25'
WHERE OWNER = 'MSTRUSR1' AND DBSPACENAME = 'DBSPNAM1';
COMMIT WORK;
COMMENT '********************************************'
UPDATE ALL STATISTICS FOR DBSPACE "MSTRUSR2"."DBSPNAM2";
COMMIT WORK;
UPDATE SQLMAINT.SQLMAINT_TABLE SET UPSTAT_DATE = '97110',
UPSTAT_TIME = '10:47:25'
WHERE OWNER = 'MSTRUSR2' AND DBSPACENAME = 'DBSPNAM2';
COMMIT WORK;
COMMENT '********************************************'
UPDATE ALL STATISTICS FOR DBSPACE "MSTRUSR3"."DBSPNAM3";
COMMIT WORK;
UPDATE SQLMAINT.SQLMAINT_TABLE SET UPSTAT_DATE = '97110',
UPSTAT_TIME = '10:47:25'
WHERE OWNER = 'MSTRUSR3' AND DBSPACENAME = 'DBSPNAM3';
COMMIT WORK;
COMMENT '********************************************'
UPDATE ALL STATISTICS FOR DBSPACE "MSTRUSR4"."DBSPNAM4";
COMMIT WORK;
UPDATE SQLMAINT.SQLMAINT_TABLE SET UPSTAT_DATE = '97110',
UPSTAT_TIME = '10:47:25'
WHERE OWNER = 'MSTRUSR4' AND DBSPACENAME = 'DBSPNAM4';
COMMIT WORK;
COMMENT '********************************************'
UPDATE ALL STATISTICS FOR DBSPACE "MSTRUSR5"."DBSPNAM5";
COMMIT WORK;
UPDATE SQLMAINT.SQLMAINT_TABLE SET UPSTAT_DATE = '97110',
UPSTAT_TIME = '10:47:25'
WHERE OWNER = 'MSTRUSR5' AND DBSPACENAME = 'DBSPNAM5';
COMMIT WORK;
COMMENT '********************************************'
COMMENT '********************************************'
COMMENT '*** DBSPACES needing update = 10 ****'
COMMENT '********************************************'
COMMENT '*** DBSPACES to update this run = 5 ****'
COMMENT '********************************************'
|
In the preceding figure, Figure 202, ten DBSPACES were eligible for maintenance; however, only five were done since a QTY LIMIT of five was specified.
Figure 203. Update Statistics Report File
Update Statistics for DBSPACEs on SQLDBA
03/16/97 10:47:29
DBSPACEs: PRIVATE.ALL, min=0,
max=4097, days=14, qty=5, time=30
10:47:52 MSTRUSR1.DBSPNAM1 UPDATE STATISTICS completed successfully
10:48:16 MSTRUSR2.DBSPNAM2 UPDATE STATISTICS completed successfully
10:48:18 MSTRUSR3.DBSPNAM3 UPDATE STATISTICS completed successfully
10:48:36 MSTRUSR4.DBSPNAM4 UPDATE STATISTICS completed successfully
10:48:38 MSTRUSR5.DBSPNAM5 UPDATE STATISTICS completed successfully
10:48:38 SQLMAINT UPDATE STATISTICS function completed
|
Note that the panel input values are listed in the report. Here, all private DBSPACES were to be considered, but only five were chosen. A time limit of 30 minutes was allocated to the job, and only DBSPACES up to 4097 pages were considered. Only those DBSPACES which had had statistics updated at least 14 days ago were considered.
The DBSPACE Reorganization Maintenance tool consists of the List Reorg Candidates function (LR), Run Reorg function (RR) and the Run Reorg Prior (RP) function.
The main objective of this tool is reorganization candidate selection. The selection function is a two step process. First, a list of potential DBSPACES is selected from the SQLMAINT table based upon the options entered on the Automated DBSPACE Maintenance entry panel. Second, this list is checked against the selection criteria in effect that determine whether a particular DBSPACE needs reorganizing. The DBSPACE selection options have been covered above. The selection criteria are described next.
Shown in Figure 204 is the Criteria Selection screen for the Automated DBSPACE Maintenance tool.
Figure 204. Automated DBSPACE Maintenance Selection Criteria Screen
+--------------------------------------------------------------------------------+ |*********************** DBSPACE MAINTENANCE UTILITY ************** ********| | | | DATABASE => SQMDBA CRITERIA SELECTION SCREEN | | | | -------------- CRITERIA DERIVED FROM SYSTEM CATALOG: (QUICK RETRIEVAL) ------- | | | | CRITERIA 1: Y UNCLUSTERED INDEX OR CLUSTER RATIO LESS THAN | | - - ---- | | | | CRITERIA 2: Y OVERFLOW ROWS > 10 % (Specify Y or N) | | - -- | | | | -------------- CRITERIA DERIVED FROM SHOW DBSPACE: (LONGER RETRIEVAL) -------- | | Y USE CRITERIA 3 thru 6 (Y or N) | | - | | CRITERIA 3: DATA or INDEX PAGES > 70 % USED | | -- | | CRITERIA 4: PROPORTION of USED DATA/INDEX PAGES | | ipg used > 40 % AND dpg used < 40 % OR dpg used > 40 % AND ipg used < 40 % | | -- -- -- -- | | CRITERIA 5: DATA PAGES FREE SPACE > 35 % AND DATA PAGES USED > 25 % | | -- -- | | CRITERIA 6: EMPTY DATA or INDEX PAGES > 5 % of TOTAL DATA or INDEX PAGES | | -- | | PF1 HELP PF3 QUIT PF6 SET TO DEFAULTS PF7 RET TO PRIOR SCREEN PF10 PROCESS | | | +--------------------------------------------------------------------------------+
The Automated DBSPACE Maintenance tool provides for user selected maintenance criteria parameters. With this panel, you can choose which criteria to use, as well as adjust any of the values of a specific criterion.
There are currently six criteria which are evaluated by SQLMAINT, any one of which can cause a DBSPACE to be selected for reorganization:
The Search Criteria are divided into 2 groups:
Group A criteria afford a quicker determination of a DBSPACE's candidacy and result in a faster SQLMAINT execution. Group B criteria use the SHOW DBSPACE command for each selected DBSPACE to obtain relevant data against which to match the criteria values to determine DBSPACE candidacy.
Each Group's criteria are discussed below. Note that any criterion can be used in conjunction with any other criterion. Criterion 1 offers a choice between 2 index type tests. If no criteria are modified or specified directly by the DBA, the defaults will be used. The defaults are set originally by the menu. Subsequent modification to any values and choices will be retained the next time SQLMAINT is invoked. To reset to the default values, bring up the CRITERIA SELECTION panel and press PF6 to restore to default values.
Figure 205. SQLMAINT DBSPACE Selection Criteria
*************** GROUP A CRITERIA ***************
CRITERION 1: UNCLUSTERED INDEX or CLUSTER RATIO LESS THAN nnnn
This criterion affords a choice of either using
unclustered index occurrences within the DBSPACE OR specifying a
CLUSTERRATIO value (0001-9999) to determine candidates for reorganization.
The 2 choices are mutually exclusive - one or the other can be specified.
Either can be used in conjunction with other criteria.
CLUSTER RATIO LESS THAN nnnn is the value which the
CLUSTERRATIO field in the catalog must be below in order to have the
DBSPACE be picked as a candidate for reorganization regardless of the
value of the CLUSTER field. If this field is blank, then the UNCLUSTERED
INDEX criterion is used and a weight of 1 is assigned if a table has an
index which is W (no longer clustered). If the CLUSTER RATIO field has
a value entered, a weight of 1 is assigned if any table's CLUSTERRATIO
field value is below the value specified in this parameter, regardless
of CLUSTER field value.
The DEFAULT criterion is UNCLUSTERED INDEX.
-------------------------------------------------------------------------
CRITERION 2: OVERFLOW ROWS > nn % (10% is default)
Specifying this criterion will cause SQLMAINT to check
the number of rows in overflow and determine whether the percentage
specified in the selection criterion is equal to or greater than the
actual percentage calculated from the catalog.
The DEFAULT is 10%.
-------------------------------------------------------------------------
*************** GROUP B CRITERIA ***************
Specifying a Y in the USE CRITERIA 3 through 6 field will cause
SQLMAINT to employ all GROUP B criteria when determining candidate
selection. Specifying an N will cause SQLMAINT to use only those
criteria which have values specified in their parameter fields.
The DEFAULT is Y (use all Group B criteria)
CRITERION 3: DATA or INDEX PAGES > nn % USED (70% is default)
This criterion checks the percentage of currently occupied
data and index pages. If the percentage equals or exceeds the value
specified, the DBSPACE is considered a candidate for reorg.
The DEFAULT is 70%.
-------------------------------------------------------------------------
CRITERION 4: PROPORTION of USED DATA/INDEX PAGES nn % (40% is default)
Specifying a value for this criterion will cause
SQLMAINT to do these checks to determine candidacy:
a. If index pages used is GREATER THAN nn % AND
data pages used is LESS THAN nn %
b. If data pages used is GREATER THAN nn % AND
index pages used is LESS THAN nn %
A match on either of the above results in candidacy.
The DEFAULT is 40%.
-------------------------------------------------------------------------
CRITERION 5: DATA PAGES FREE SPACE > nn % AND (nn default is 35%)
DATA PAGES USED > xx % (xx default is 25%)
This criterion checks the percentage of data pages free
space for greater than nn%. If met or exceeded, it checks the percentage
specified (xx %) of data pages used. If both match or exceed the values
specified, the DBSPACE is considered a candidate.
The DEFAULT is 35% for nn, and 25% for xx.
-------------------------------------------------------------------------
CRITERION 6: EMPTY DATA or INDEX PAGES > nn % of TOTAL DATA or INDEX
PAGES.
This criterion checks the percentage of empty data or
index pages for nn %. If met or exceeded, the DBSPACE is considered a
candidate.
The DEFAULT is 5% for nn.
The PF Keys offer these options:
Shown in Figure 206 is the SQLMAINT Table list screen for the Automated DBSPACE Maintenance tool.
Figure 206. SQLMAINT Table List Screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*---------------------- DBSPACE MAINTENANCE UTILITY ----------------------* | || Database => SQMDBA | | || UPSTATS REORGANIZATION | | || OWNER NAME LAST ELAP LAST ELAP STAT WGHT NPAGES | | || -------- ------------------ ----- ---- ----- ---- ---- ---- ------- | | || C370451 C370451 97073 0 97073 1 00 1 1024 | | || M356959 M356959A 97094 0 97094 2 00 1 1024 | | || M760595 M760595B 00000 0 00000 0 1 1024 | | || M997990 M997990A 97074 0 97074 0 00 1 1024 | | || PUBLIC DBEDB00 97016 1 00000 0 2 2048 | | || PUBLIC DSQTSCT1 97035 0 97035 11 1 4097 | | || PUBLIC DSQTSCT2 95334 0 95336 7 1 4097 | | || PUBLIC DSQTSCT3 97041 0 97037 27 1 8064 | | || PUBLIC DSQTSDEF 97071 0 00000 0 1 4097 | | || | | || The fields in RED are modifiable | | || | | || | | || Page 1 of 3 | | |*----------------------------------------------------------------SQMMAIN3----* | |PF: 1 Help 3 EXIT 4 Maint Panel 7 Bkwd 8 Fwd | | 9 Sort/Wght 10 Sort/Udate 11 Sort/Rdate 12 Sort/Rstat ENTER UPDATE | | | +--------------------------------------------------------------------------------+
Pressing PF9 from the main SQLMAINT entry panel will provide you with the rows from the SQLMAINT_TABLE which are retrieved according to the options present on the screen. For the LS and US options, the rows which meet the UPSTAT_DATE parameter (DAYS SINCE) will be presented (the rows must also meet the other parameters selected, such as MIN-MAX pages). For the LR, RR, and RP options, the rows which meet the REORG_DATE parameter (DAYS SINCE) will be presented.
From the screen which is presented, you can sort the report by various fields. You can also update certain fields which would then subsequently alter the candidate list chosen when you finally begin the candidate selection process.
For a full explanation of the new SQLMAINT Table Display screen, enter HELP SQLMNT3 at a CMS command prompt. This is the same information which is presented when you press PF1 from the Table Display List screen.
This function is chosen by specifying the LR option on the SQLMAINT entry panel. The purpose of this function is to create a list of DBSPACES, based upon the input parameters specified, which need to be reorganized.
The output of this run is three files:
Shown in Figure 207 is an example of the reorganization analysis report created by SQLMAINT. This report is saved in a file on the user's 191 A-disk. The filename of the report will be the database name and the filetype will be descriptive of the subset of the database analyzed (the filetype of the example is PRIREOLS, indicating that private DBSPACES were considered for analysis).
In this report, nine private DBSPACES were analyzed and three were selected for reorganization, while one was discounted since it only met criterion 5 (space USER2.DBSPACE2). Note that the others had never been reorganized, since their date/time fields were 0. They happened to be new entries in the SQLMAINT_TABLE. The report was sequenced by DBSPACE name since this was only a report run.
Based on the analysis, DBSPACE MSTRUSR1.DBSPNAM1 will be reorganized first because it has a weight of 2. Weight is calculated by adding the number of unclustered indexes to the number of other reorganization criteria which caused this DBSPACE to be chosen.
The next reorganization will be performed on the DBSPACE with the next highest WEIGHT, and so on.
Figure 207. SQLMAINT List Reorg Candidates Report
DBSPACE REORGANIZATION CANDIDATES REPORT
Database: VMSYSTM1.SQLDBA 03/16/97 13:57:00
OPTIONS: DBSPACEs: PRIVATE.ALL, seq=DBSPACE, min=0,
max=999999, days=14, qty=10, time=180
DBSPACES EXAMINED = 9
**************************************************************
CRITERIA 1 = DBSPACES with Tables with index type W
CRITERIA 2 = DBSPACES with Tables with NOVERFLOW rows >= 10%
CRITERIA 3 = DBSPACES with data or index pages > 70% used
CRITERIA 4 = DBSPACES with index pages used > 40% and data pages < 40%
-- OR -- with data pages used > 40% and index pages < 40%
CRITERIA 5 = DBSPACES with data pages free space > 35%
-- AND -- data pages used > 25%
CRITERIA 6 = DBSPACES with empty data or index pages > 5%
of DBSPACE data pages or index pages
****************************************************************************
MSTRUSR1.DBSPNAM1 DBSPACENO: 59 LAST REORG: 00000 00:00:00
Criteria 1: 2 indexes unclustered
NBR PAGES OCCUPIED PAGES %FREE EMPTY
HEADER: 8 1 ( 12%) 81%
DATA: 679 190 ( 27%) 22% 0
INDEX: 337 24 ( 7%) 48% 2
WEIGHT = 2, DBSPACE PAGES = 1024
****************************************************************************
MSTRUSR2.DBSPNAM2 DBSPACENO: 71 LAST REORG: 00000 00:00:00
Criteria 4: PCTINDX: Old=20, Suggested=20
NBR PAGES OCCUPIED PAGES %FREE EMPTY
HEADER: 8 1 ( 12%) 83%
DATA: 812 437 ( 53%) 12% 0
INDEX: 204 0 ( 0%) 0% 0
WEIGHT = 1, DBSPACE PAGES = 1024
This DBSPACE will NOT be REORGED, the evaluation indicates
that a reorganization is not currently needed.
****************************************************************************
MSTRUSR3.DBSPNAM3 DBSPACENO: 179 LAST REORG: 00000 00:00:00
Criteria 1: 1 index unclustered
NBR PAGES OCCUPIED PAGES %FREE EMPTY
HEADER: 8 1 ( 12%) 97%
DATA: 1733 3 ( 0%) 13% 0
INDEX: 307 2 ( 0%) 67% 0
WEIGHT = 1, DBSPACE PAGES = 2048
****************************************************************************
MSTRUSR4.DBSPNAM4 DBSPACENO: 180 LAST REORG: 00000 00:00:00
Criteria 1: 1 index unclustered
NBR PAGES OCCUPIED PAGES %FREE EMPTY
HEADER: 8 1 ( 12%) 97%
DATA: 1733 3 ( 0%) 13% 0
INDEX: 307 2 ( 0%) 67% 0
WEIGHT = 1, DBSPACE PAGES = 2048
|
This function is chosen by specifying the RP option on the SQLMAINT entry panel. The purpose of this function is to run reorganizations using a previously created list of reorganization candidates in a file named dbname SQLMAINT A).
This option is identical to the RR option, except that DBSPACES are not evaluated; the file containing previously evaluated DBSPACES is used as input to DBSPACE selection.
The output of this run is two files:
Shown in Figure 208 is an example of the reorganization execution report generated by SQLMAINT. This report is saved in a file on the user's 191 A-disk. The filename of the report will be the database name and the filetype will be RUNREOLS.
The reorganization report shows the before and after statistics for the DBSPACE and the results of the reorganization (provided as a status code defined at the end of the report).
Figure 208. Example SQLMAINT DBSPACE Reorganized Report
DBSPACE REORGANIZATION REPORT
Database: VMSYSTM1.SQLDBA 03/16/97 15:10:01
ELAPSE
OWNER DBSPACENAME SPNO TIME ST W DATA STATISTICS INDEX STATISTICS
________ __________________ ____ ______ __ _ _______________ ________________
MSTRUSR1 DBSPNAM1 59 2.38 00 2 679 190(27%) 22% 337 24(7%) 48%
****** After REORG: 679 190(27%) 22% 337 24(7%) 48%
MSTRUSR3 DBSPNAM3 179 1.66 00 1 1733 3(0%) 13% 307 2(0%) 67%
****** After REORG: 1733 3(0%) 13% 307 2(0%) 67%
Number of DBSPACES successfully reorged = 2
Total elapsed time for all reorgs = 4.04 minutes
End of Report for PDEVDBA REORG JOB
NOTE : SPNO - DBSPACE number
ELAPSE- elapsed time, in minutes, for the REORG
W - weight factor of the DBSPACE reorged
ST - status of the REORG
'00' Good Reorg
'XX' Good Reorg but space should be increased
'CC' Good Reorg but selection criteria did not change
'NN' Reorg not done because space should be increased
'SS' Not enough unload disk space for reorg
'TT' Not enough time left to reorg this space
'99' Bad Reorg
DATA/INDEX BEFORE - the results of a SHOW DBSPACE
command before the REORG was done, format:
tttt uu(pp%) ff%
where: tttt is total pages,
uu is pages used,
pp is percent of pages used,
ff is avg. percent free per page.
DATA/INDEX AFTER - the results of a SHOW DBSPACE
command after the REORG was done
|
This function is chosen by specifying the RR option on the SQLMAINT entry panel. The purpose of this function is to evaluate the DBSPACES to create a list of reorganization candidates, then to run reorganization on them according to the specified input parameters.
The output of this run is three files:
SQLMAINT has been designed for repeated execution on a scheduled basis using information in the SQLMAINT table that is created in each database. Since this table will initially have no history of maintenance on each DBSPACE, all DBSPACES will be considered when SQLMAINT is executed the first time. For the List Reorg Candidates (LR) and Run Reorgs (RR) functions, this can take a considerable amount of time (each DBSPACE will be scanned). To reduce the number of DBSPACES scanned, update the SQLMAINT table manually.
To update statistics on the SQLDBA database for a maximum of 50 private DBSPACES per night, with a maximum run time of one hour, and selecting DBSPACES that have not had maintenance for three weeks, this statement could be executed each night from a disconnected virtual machine:
SQLMAINT SQLDBA UPSTATS ( PRIVATE QTY=50 TIME=60 DAYS=21 |
To reorganize DBSPACES on the TESTDBA database for a maximum of 10 private DBSPACES per night, with a maximum run time of three hours, selecting DBSPACES in sequence according to the most need, and only considering DBSPACES that have not been reorganized within the last month, this statement could be executed each night from a disconnected virtual machine:
SQLMAINT TESTDBA RUNREORG ( PRIVATE WEIGHT QTY=10 TIME=180 DAYS=30
The reorganization process will require disk space to hold the unloaded DBSPACE data until each DBSPACE is dropped and reacquired. It is your responsibility to assure that enough disk space is available to hold all DBSPACES that will be reorganized during a single SQLMAINT execution. If the default temporary disk option is selected, each DBSPACE reorganization will acquire a temporary disk to hold the unloaded data and will drop the temporary disk when the reorganization has completed successfully. This option uses the 191 A-disk to hold the DBSU command file and the DBSU output listing file. These files can be large, so enough 191 disk space should be provided.
When a linked disk is specified for SQLMAINT use (with the FILEMODE entry), the disk will be used for all reorganization activity. This includes the unloaded DBSPACE, the DBSU command file, and the DBSU output listing file. These files will not be erased after the reorganization completes, so this disk must be large enough to hold files for all DBSPACE reorganizations that will be performed during a single execution of SQLMAINT.
The SQLMAINT Control Table can be altered by the Control Center Administrator to control the selection process of the SQLMAINT tool.
If a database has a large number of DBSPACES, the REORG_DATE and UPSTAT_DATE columns in the SQLMAINT table can initially be set to different values so that a single SQLMAINT run will only scan a portion of the DBSPACES. For instance, you can execute a database command such as:
UPDATE SQLMAINT.SQLMAINT_TABLE SET UPSTAT_DATE = '99001', REORG_DATE
= '99001' WHERE OWNER < 'M'
This can change about half of the DBSPACES to a different maintenance date. You could then invoke SQLMAINT with an appropriate DAYS SINCE parameter so that only those DBSPACES would be selected. By using the TIME LIMIT and/or QTY LIMIT parameters, you could further restrict the number of DBSPACES that would be maintained until the SQLMAINT table developed a maintenance history.
The REORG_STATUS parameter can also be changed to NN to prevent a DBSPACE from being selected for reorganization.