In the past, to determine when it was necessary to reorganize tables and indexes or to expand RDAREAs, the user had to make a comprehensive evaluation of the tables to be reorganized, and of the timing for reorganization based on messages that were output, and of the execution results of the pddbst command. This meant that there was a risk that tables that didn't need to be reorganized were reorganized, and that tables that needed to be reorganized were not reorganized because output messages were not read.
To simplify these operations, HiRDB can now predict when reorganization will become necessary. This is called the facility for predicting reorganization time. The following figure provides an overview of the facility for predicting reorganization time.
Figure 13-7 Overview of the facility for predicting reorganization time
- #: The scheduled date on which an RDAREA will need to be maintained is called the scheduled database maintenance date. For an overview of how HiRDB analyzes prediction data, see 13.4.6(1) HiRDB's analysis of prediction data.
Reorganization time prediction is divided into the following two phases:
- Phase 1: Collection of reorganization time prediction data
- The pddbst command is executed regularly to accumulate database analysis results in a database state analyzed table.
- When any of the SQL statements or commands listed below is executed, database management history information is output to a database management table:
Definition SQL (DROP SCHEMA, DROP TABLE, DROP INDEX, ALTER TABLE)
Data manipulation SQL (PURGE TABLE)
pdrorg command
pdreclaim command
pdload command
pdmod command
- Phase 2: Analysis of reorganization time prediction data
Using the database state analyzed table and the database management table as input information, the pddbst command is executed to analyze the reorganization time prediction data. The user views the execution results of the pddbst command and takes one of the actions listed below, as appropriate. Note that RDAREA automatic extension is performed automatically by HiRDB if automatic extension was specified when the RDAREA was created (no user action is required).
- Execution of the pdrorg command to reorganize a table or index
- Execution of the pdreclaim command to release free pages and segments that are being used
- Execution of the pdmod command to extend an RDAREA
- RDAREA automatic extension
- Execution of the pdmod command to re-initialize an RDAREA
- Reference note
-
- Prediction levels 1 and 2 are supported by the facility for predicting reorganization time. Prediction level 1 is used principally to monitor space shortages in RDAREAs. Prediction level 2 is used to monitor the effect of poor data storage efficiency on online performance as well as to monitor space shortages in RDAREAs.
- Because the primary purpose of the facility for predicting reorganization time is to prevent RDAREA space shortages, this facility predicts reorganization time by assuming the maximum amount of data storage after reorganization. For example, a branched row (where data is partitioned and stored on multiple pages) is assumed to remain a branched row after reorganization. Therefore, when determining whether to reorganize a table or extend an RDAREA, the facility tends to predict RDAREA extension.
All Rights Reserved. Copyright (C) 2011, 2015, Hitachi, Ltd.