15.5 Standard value definition file (facility for predicting reorganization time)

The facility for predicting reorganization time makes its predictions on the basis of predefined standard values for items. You can change these standard values as appropriate to your environment. You can specify in the standard value definition file the new standard values you wish to use for any items.

Organization of this section
(1) Format
(2) Explanation
(3) Specification rules for the standard value definition file
(4) Names of items whose standard value can be changed
(5) Standard value definition file specification example

(1) Format

<threshold>
item-name=standard-value
    :
[rdarea=RDAREA-name]
item-name=standard-value
    :
[table=authorization-identifier.table-identifier]
item-name=standard-value
    :
[index=authorization-identifier.index-identifier]
item-name=standard-value
    :

(2) Explanation

<threshold>
Specifies that standard values for common items follow. When <threshold> is specified, it must be specified as the first line of the file.
[rdarea=RDAREA-name]
Specifies an RDAREA for which standard values are to be changed.
[table=authorization-identifier.table-identifier]
Specifies the table(s) for which standard values are to be changed.
To include all tables under the specified authorization identifier, specify all for table-identifier. To specify the data dictionary table, omit the authorization identifier.
[index=authorization-identifier.index-identifier]
Specifies the index(es) for which standard values are to be changed.
To include all indexes under the specified authorization identifier, specify all for index-identifier.
item-name=standard-value
Specifies the name of an item whose standard value is to be changed and the new standard value. For details about the item names that can be specified, see (4) Names of items whose standard value can be changed.
Rules
  1. If the same item name is specified more than once, the last specification takes effect.
  2. An item whose value is set to the asterisk (*) will be excluded from the checking performed by the facility for predicting reorganization time. There is no need to reorganize a table on which PURGE TABLE and initial data loading (pdload with existing data deleted) are executed periodically. To remove these resources as targets of the facility for predicting reorganization time, specify an asterisk (*). The facility for predicting reorganization time performs prediction processing assuming that no periodic maintenance such as PURGE TABLE is performed.

(3) Specification rules for the standard value definition file

  1. An item specified before the first [rdarea=RDAREA-name], [table=authorization-identifier.table-identifier], or [index=authorization-identifier.index-identifier] is treated as a common item.
  2. If the same <threshold>, [rdarea=RDAREA-name], [table=authorization-identifier.table-identifier], or [index=authorization-identifier.index-identifier] is specified more than once, the last specification takes effect.
  3. If the same [rdarea=RDAREA-name], [table=authorization-identifier.table-identifier], or [index=authorization-identifier.index-identifier] containing the same item name is specified more than once, the last specification takes effect.
  4. Each of <threshold>, [rdarea=RDAREA-name], [table=authorization-identifier.table-identifier], [index=authorization-identifier.index-identifier], and item-name=standard-value must be specified on a separate line.
  5. There can be no spaces or tab characters within item-name=standard-value or before item-name.
  6. The utility ignores any information enclosed between /* and */. The utility also ignores null lines.
  7. If the standard value definition file contains an error, the utility terminates with an error.
  8. If an RDAREA name, authorization identifier, table identifier, or index identifier contains a lowercase alphabetic letter or space, you must enclose the entire name or identifier in double quotation marks ("). If one of these names or identifiers is not enclosed in double quotation marks ("), any lowercase alphabetic letters it includes are handled as uppercase letters.
  9. Place a specification common to all tables or all indexes (all specified for the table identifier or index identifier) before specifications for specific tables, indexes, or RDAREAs.

(4) Names of items whose standard value can be changed

Table 15-7 describes the items whose standard value can be changed. Table 15-8 provides guidelines for changing standard values.

Table 15-7 Items whose standard value can be changed

No.Item nameSpecifiable valuesStandard valueDescriptionWhether or not changeable
RTI
1EMPTY_PAGE_RATIO*,
10-100
30Among the total number of pages allocated, specifies the percentage that can be released by pdreclaim (%).YYY
2UNDER_PAGE_RATIO10-10010,
or 80-
PCTFREE
Specifies the page usage percentage to be used to check No. 3 (%).YYY
3UNUSED_PAGE_RATIO*,
10-100
50Among the total number of pages allocated, specifies the percentage whose page usage is less than the value of No. 2 (%).YYY
4BRANCH_ROW*,
10-100
50Among the total number of rows, specifies the percentage stored on multiple pages (%).YYN
5USED_SEGMENT_LOB*,
10-100
80Among the total number of segments, specifies the percentage derived from the last segment number in the LOB RDAREA (%).YNN
6USED_SEGMENT_RATIO_CLUS*,
10-100
50Among the total number of segments allocated to a clustering data page, specifies the percentage whose usage is 100% (%).YYN
7DIFF_PCTFREE_M*,
10-100
10Specifies the value used to determine the minimum page usage (%).
This value is obtained by subtracting the minimum page usage rate from the page usage rate that is obtained based on the percentage of unused area specified in PCTFREE.
If the specified value is greater than (100 - percentage of unused area specified in PCTFREE), the value of (100 - percentage of unused area specified in PCTFREE) is assumed as this value.
If * is specified, only the value specified in No. 8 takes effect.
YYY
8DIFF_PCTFREE_P*,
10-100
10Specifies the value used to determine the maximum page usage (%).
This value is obtained by subtracting the page usage that is obtained based on the percentage of unused area specified in PCTFREE from the maximum page usage rate.
If the specified value is greater than the percentage of unused area specified in PCTFREE, the percentage of unused area specified in PCTFREE is assumed as this value.
If * is specified, only the value specified in No. 7 takes effect.
YYY
9DIFF_PCTFREE_RATIO*,
10-100
*Among the total number of pages allocated, specifies the percentage whose usage rate is less than the minimum value of No. 7 and greater than the maximum value of No. 8 (%). When this item is omitted, analysis is not performed for this item.YYY
10USED_SEGMENT*,
10-100
80Among the total number of segments, specifies the percentage used throughout the entire RDAREA (%).YNN
11EXTEND_COUNT_MIN0-240Specifies the minimum number of times the RDAREA can be extended automatically. This value must satisfy the condition EXTEND_COUNT_MIN[Figure] EXTEND_COUNT.YNN
12EXTEND_COUNT0-240Specifies the number of times the RDAREA can be extended automatically. This value must satisfy the condition EXTEND_COUNT_MIN[Figure] EXTEND_COUNT. pddbst cannot determine whether or not the HiRDB file system area becomes full as a result of automatic extension. Therefore, you must determine the value of EXTEND_COUNT taking into account the number of RDAREAs in the HiRDB file system area and the maximum number of extensions.YNN
Legend:
R: RDAREA
T: Table
I: Index
Y: Standard value can be changed.
N: Standard value cannot be changed.

Table 15-8 Guidelines for changing the standard value

No.Item nameStandard valueHow to determine whether or not maintenance is neededGuideline for changing standard value
1EMPTY_PAGE_RATIO(p)=30%If the percentage of used pages with a page usage rate of 0% is (p)% or greater, the utility determines that free pages must be released.If there is not much space available in the RDAREA and you want to release invalid pages as soon as possible, reduce the value of (p).
2UNDER_PAGE_RATIO(a)=X-20%If the percentage of used pages whose page usage rate of (a)% or greater is equal to or greater than (p)%, the facility determines that reorganization is necessary.
  • If the storage row is extremely short for the page size and the page usage rate is less than (a)% immediately after the table is reorganized, reduce the value of (a).
  • If you want to reduce the percentage of pages with invalid area as much as possible, reduce the value of (p).
  • In the case of a table for which NO SPLIT is not specified and which contains variable-length character strings with a defined length of 256 bytes or greater, if more than 50% of the pages obtained immediately after reorganization have a page usage rate of (a)% or less (there is more branched variable-length character string data than non-branched data), increase the value of (p).
  • If there is not much space available in the RDAREA and you want to release the pages with invalid area as soon as possible, reduce the value of (p).
3UNUSED_PAGE_RATIO(p)=50%
4BRANCH_ROW(p)=50%If the percentage of branched rows among the total number of rows is (p)% or greater, the facility determines that reorganization is necessary.If there are many UPDATE processes that result in longer data and search performance needs to be maintained, reduce the value of (p).
5USED_SEGMENT_LOB(p)=80%If the last segment number in the LOB RDAREA exceeds (p)% among the total number of segments and reorganization does not improve the condition, the utility determines that extension of the RDAREA is necessary. If reorganization results in a value less than (p), the facility determines that reorganization is necessary.If the amount of data to be stored may increase suddenly and you want to take action at an early stage, reduce the value of (p).
6USED_SEGMENT_RATIO_CLUS(p)=50%If the number of segments with a page usage per segment of 100% (segments without free pages) is equal to or greater than (p)% of the total number of segments in use, use of free space based on the percentage of unused pages, not the use of free space based on the percentage of unused area, has already begun; therefore, the facility determines that reorganization is necessary.While data can be stored in the same page, clustering effects are still maintained. If you can determine from the storage status in the same segment that clustering is no longer effective, reduce the value of (p).
7DIFF_PCTFREE_M(a)=X-10%If the percentage of used pages with a page usage outside the range from (a)% to (b)% is equal to or greater than (p)%, the facility determines that reorganization is necessary.If you want to perform reorganization even when the page usage rate is high (for a table in which UPDATEs resulting in a longer row occur frequently, or for a table with a cluster key defined in which there are frequent occurrences of INSERTs that involves data with an intermediate key), specify (p). In the case of an index with many duplicate key values, PCTFREE is ignored for the pages that store duplicate key values; therefore, if you specify (p), specify a sufficient value.
8DIFF_PCTFREE_P(b)=X+10%
9DIFF_PCTFREE_RATIO(p)=*
10USED_SEGMENT(p)=80%If the number of used segments in the RDAREA is equal to or greater than (p)%, the facility determines that reorganization is necessary.
If the value remains equal to or greater than (p)% after reorganization, the utility determines that extension is necessary. However, in the case of an RDAREA for which automatic extension has been specified, the utility does not recommend extension because the RDAREA can be extended automatically only up to (a) times.
  • If you want to be able to detect a space shortage at an early stage when RDAREA extension is recommended, reduce the value of (p) (for example, if a space shortage occurs when there is no spare disk, you must first allocate a disk).
  • If the amount of data to be stored may increase suddenly and you want to take action at an early stage, reduce the value of (p).
11EXTEND_COUNT_MIN(a)=23
12EXTEND_COUNT
Legend:
X: 100 - value of first argument of PCTFREE

(5) Standard value definition file specification example

The following shows a standard value definition file specification example:

<threshold>  .........................Common specification
USED_SEGMENT=70
USED_SEGMENT_LOB=65
 :
[table=all]  .........................Specification common to all tables
EMPTY_PAGE_RATIO=30
USED_SEGMENT=20
 :
[index=all]  .........................Specification common to all indexes
EMPTY_PAGE_RATIO=20
USED_SEGMENT=20
 :
[table=authorization-identifier.table-name]  ....Specification common to a specified table
EMPTY_PAGE_RATIO=35
USED_SEGMENT=30
 :
[index=authorization-identifier.index-name]  ...Specification common to a specified index
EMPTY_PAGE_RATIO=80
USED_PAGE_RATIO=60
 :
[rdarea=RDAREA-name]  ................Specification for an individual RDAREA
EXTEND_COUNT=10
 :
[table=authorization-identifier.table-name]  ...Specification for an individual table in an RDAREA
EMPTY_PAGE_RATIO=35
USED_SEGMENT=30
 :
[index=authorization-identifier.index-name]  ...Specification for an individual index in an RDAREA
EMPTY_PAGE_RATIO=80
USED_PAGE_RATIO=60
 :