The following figure shows the operational flow for predicting table reorganization time.

Figure 13-8 Operational flow for predicting table reorganization time

(a) Collecting data for predicting reorganization time

Execute the command shown below to collect data for predicting reorganization time and to store the data in the database state analyzed table. You should execute this command on a daily basis to accumulate data for predicting reorganization time.

 
pddbst -k logi -r ALL -e 1
 

Note

The operation in step (a) must be performed at least four times. To make an accurate prediction, you need to accumulate prediction data from at least four operations (the KFPK10312-I or KFPK10313-I message will be output). In determining the number of times step (a) has been performed, you cannot count an execution of the command if there has been no change in RDAREA status since the last time the command was executed. In other words, an execution of step (a) counts only if there has been a change in RDAREA status. For details, see 13.4.4(2)(a) When prediction data has not been collected correctly.

Reference note

If the data storage status has changed significantly, there is a high probability that reorganization will be required. Therefore, after you update a large volume of data, we recommend that you perform the operations in steps (a) and (b).

(b) Checking for RDAREAs whose scheduled database maintenance date is approaching

Execute the command shown below to check for RDAREAs that are close to their scheduled database maintenance date. You should execute this command on a daily basis.

 
pddbst -k pred -r ALL -e 1
 

A listing is produced of RDAREAs that are close to their scheduled database maintenance dates. An example follows.

Output example

 pddbst 07-02       ***** Rdarea resource forecast *****     2005/05/15 11:07:10
No         Date        RdareaName                       ResKind
---------- ----------- -------------------------------- ----------
         1 2005/05/31  "RDUSER01"                       Segment
         2 2005/06/01  "RDUSER02"                       Segment
---------- ----------- -------------------------------- ----------

Explanation

Date: Displays the scheduled database maintenance dates.

RdareaName: Displays the RDAREAs that will require maintenance.

Note

Because a scheduled database maintenance date is simply an estimate, an RDAREA space shortage might occur before the scheduled date depending on operations.

Reference note

• It might take a considerable amount of time for the pddbst command in step (c) to execute. To avoid adverse effects on online jobs, you should execute the operations in steps (b) and (c) during time periods when online jobs will not be affected adversely.
• If the execution time of the operation in step (c) does not pose a problem, you can skip the operation in step (b) and perform only the operation in step (c).

(c) Identifying the resources that require maintenance and the maintenance methods

If the results of step (b) identify RDAREAs that are close to their scheduled database maintenance dates, execute the command shown below to check for the tables, indexes, and RDAREAs that require maintenance:

 
pddbst -k pred -r ALL -e 1 -m
 

Tables, indexes, and RDAREAs that need to be maintained are listed, together with the appropriate maintenance method for each RDAREA. An output example follows.

Output example

 pddbst 07-02       ***** Rdarea resource forecast *****     2005/05/15 11:07:10
No         Date        RdareaName                       ResKind
---------- ----------- -------------------------------- ----------
         1 2005/05/31  "RDUSER01"                       Segment
         2 2005/06/01  "RDUSER02"                       Segment
---------- ----------- -------------------------------- ----------
 
 pddbst 07-02       ***** Maintenance Information *****      2005/05/15 11:07:10
No          : 1
Rdarea Name : "RDUSER01"
Method      :         ...[1]
Segment     : 42560
--------------------------------------------------------------------------------------
 Reclaim      Reorganize   Type Name                                        Date
------------- ------------ ---- ------------------------------------------- ----------
      28089 *       9363    T   "k1234567"."table01"   ...[2]               2005/05/31
          0        12342 *  T   "k1234567"."table02"   ...[3]               2005/05/31
        851         7235 *  I   "k1234567"."index01"   ...[4]               2005/05/31
          3          -10    T   "k1234567"."table10"   ...[5]               2005/05/31
======================================================================================
No          : 2
Rdarea Name : "RDUSER02"
Method      : Expand   ...[6]
Segment     : 1523
--------------------------------------------------------------------------------------
 Reclaim      Reorganize   Type Name                                        Date
------------- ------------ ---- ------------------------------------------- ----------
        228         1035    T   "k1234567"."table01"                        2005/06/03
          0          121    T   "k1234567"."table03"                        2005/06/03
         30           60    I   "k1234567"."index07"                        2005/06/03
======================================================================================

Explanation

1. For RDAREA RDUSER01, there is no entry in the Method field, so no maintenance is necessary for this RDAREA.
2. There is an asterisk (*) in the Reclaim field for the table named table01 in RDAREA RDUSER01. This means that the pdreclaim command can be used to release free pages and segments that are being used.
3. There is an asterisk (*) in the Reorganize field for the table named table02 in RDAREA RDUSER01. This means that the pdrorg command can be used to reorganize this table.
4. There is an asterisk (*) in the Reorganize field for the index named index01 in RDAREA RDUSER01. This means that the pdrorg command can be used to reorganize this index.
5. There is no asterisk (*) shown for the table named table10 in RDAREA RDUSER01. This means that no maintenance is necessary for this table.
6. For RDAREA RDUSER02, an RDAREA maintenance method is shown in the Method field. The following entries can appear in the Method field:
   Expand: Expand the RDAREA. In this example, the number of segments that need to be expanded is 1,523, as shown in the Segment field.
   Extend: HiRDB will extend the RDAREA automatically.
   Reinit: Re-create the database (unload the data from the RDAREA, reinitialize the RDAREA, and then reload the unloaded data).
   No display: No maintenance is necessary for the RDAREA.
   

   Reference note

   • The meanings of the other headers follow:
     Type: Indicates the resource type:
     T: Table
     I: Index
     L: LOB RDAREA
     Name: Table name or index name.
     Date: Scheduled maintenance date.
     
   • The number of segments shown is the minimum number of segments that need to be expanded by the scheduled maintenance date. It is advisable to expand more segments than the indicated number. Note that there is a limit to the number of times an RDAREA can be expanded.

(d) Maintaining the database

Pursuant to instructions issued by HiRDB, you should take one of the following actions, as appropriate:

• Reorganize a table.
• Reorganize an index.
• Expand an RDAREA.
• Reinitialize an RDAREA.
  

  Note

  Do not perform the operation in step (a) while any of the operations in step (d) is underway. Otherwise, correct predictions cannot be made.

For the collection of reorganization time prediction data under prediction level 1, the facility collects information for the RDAREAs only; therefore, only the directory pages are subject to analysis. Under prediction level 2, on the other hand, the facility also collects information for each table and each index, so the data and index pages also require analysis. This means that the execution time required for pddbst to collect prediction data is longer under prediction level 2 than under prediction level 1.

If reorganization time prediction data is collected under prediction level 2 while an online job is executing, there might be adverse effects on the application. To minimize the effects on the online job application, you can choose one (or both) of the following execution methods:

• Interval analysis
• Merge analysis

For details about interval analysis and merge analysis, see the manual HiRDB Version 9 Command Reference.