15.3.3 Condition analysis by table or index

The utility analyzes the segment status of a table or index storage page and the status of all storage pages by RDAREA.

Organization of this subsection
(1) Purpose
(2) Analysis results
(3) Analyzing the analysis results

(1) Purpose

You can determine the degree of disorganization of tables or indexes. For a table or index that has been partitioned and stored in multiple RDAREAs, you can determine whether or not partitions are distributed uniformly among the RDAREAs.

(2) Analysis results

The following shows the results of condition analysis by table or index:

(a) Condition analysis by table
Explanation
  1. Date and time of completion of acquisition of the information required for this condition analysis, in the format YYYY/MM/DD hh:mm:ss:
    YYYY: Year. MM: Month. DD: Date. hh: Hour. mm: Minute. ss: Second.
  2. Name of the table subject to analysis.
  3. Authorization identifier of the table subject to analysis.
  4. Name of the hash function used if the table is partitioned with flexible hash or FIX hash. Hash is displayed for flexible hash partitioning; Fix Hash is displayed for FIX hash partitioning. This field is blank if the table uses neither flexible nor FIX hash partitioning.
  5. Total number of segments allocated to the table (number of used segments + number of unused segments).
  6. Total number of pages in the segments allocated to the table (used segments + unused segments).
  7. Maximum total number of segments allocated to the table per RDAREA.
  8. Maximum total number of pages in the segments allocated to the table per RDAREA.
  9. Total number of rows stored in the table (total number of rows stored in each RDAREA); displayed only when the -s option is specified.
  10. Number of RDAREAs analyzed successfully and number of RDAREAs containing the table storage pages.
  11. Name of RDAREA subject to analysis.
  12. Indicates the name of the server managing the RDAREA.
  13. Total number of unused segments in the RDAREA. The parentheses enclose the total number of unused segments in HiRDB files that are not in the frozen update status in the corresponding RDAREA.
  14. Percentage ratio of used segments:
    [Figure]number of used segments/value of 18[Figure][Figure] 100 (%)
  15. Percentage ratio of full segments:
    [Figure]number of full segments/value of 18[Figure][Figure] 100 (%)
  16. Number of used segments. Segments being released by pdreclaim are displayed as used segments.
  17. Number of full segments.
  18. Total number of segments allocated to the corresponding table in the RDAREA (number of used segments).
  19. Percentage ratio of used pages:
    [Figure]number of used pages/value of 23[Figure][Figure] 100 (%)
  20. Percentage ratio of full pages:
    [Figure]number of full pages/value of 23[Figure][Figure] 100 (%)
  21. Number of used pages.
  22. Number of full pages.
  23. Total number of pages in the segments allocated to the corresponding table in the RDAREA (number of used pages + number of unused pages).
  24. Number of rows stored in the RDAREA; displayed only when the -s option is specified. Because NOWAIT retrieval is performed to count the number of stored rows, errors may occur.
  25. Job name specified during data loading, reorganization, or reloading with the synchronization point specification. Parentheses enclose the type of utility that uses this job:
    L: pdload
    R: pdrorg
    Blank: Not set
  26. Number of rows stored in the database during data loading, reorganization, or reloading with the synchronization point specification.
  27. Index creation method used during data loading, reorganization, or reloading with the synchronization point specification (value of the -i option).
  28. Name of the original RDAREA.
  29. Generation number of the corresponding RDAREA.
  30. Number of replica RDAREAs.
  31. Shutdown history of the corresponding RDAREA.
    History1: Shutdown information immediately before the shutdown was released (if the RDAREA is currently shut down, this field displays the current shutdown information)
    History2: Shutdown information immediately preceding History1
  32. Shutdown type of the corresponding RDAREA. If there is no history, this field is blank.
    CMD: Command shutdown by HiRDB error detection
    FLT: Error shutdown
  33. Code indicating the cause of the shutdown of the corresponding RDAREA. For details about the shutdown cause code, see 15.3.1(2)(a) For an RDAREA other than a LOB RDAREA.
  34. Shutdown time of the corresponding RDAREA. If there is no history, this field is blank.
  35. Page search mode:
    INS: New page allocate mode
    REU: Free page reuse mode
  36. Number of segments specified for SEGMENT REUSE during execution of CREATE TABLE or ALTER TABLE. If a number of segments for SEGMENT REUSE is not specified, this field displays 0. If NO is specified for SEGMENT REUSE or SEGMENT REUSE is omitted, the field displays -.
  37. Number of times the mode changed from new page allocate to free page reuse and then changed back to new page allocate because there was no reusable free space (n/m).
    n: Idle count
    m: Number of times mode changed from new page allocate to free page reuse
    n and m are reset to 0 at the following times:
    • When the RDAREA is closed
    • When HiRDB is restarted
    • When the PURGE TABLE statement is executed
  38. Whether the table is in reload-not-completed data status or is being reorganized. If the table is not in either of the following statuses, this field is blank.
    DATA_UNFINISH:
    The table is in reload-not-completed data status, which means that table reorganization was executed, but reload processing has not been completed for some reason such as an error. To release the table from reload-not-completed data status, you must re-execute the table reorganization or reload data into the table.
    ON_RORG:
    The table is currently being reorganized.
  39. Number of pages that can be released by pdreclaim (number of used free pages whose usage ratio is 0%).
  40. Number of used pages for each 10% range. The parentheses enclose the percentage ratio to all used pages. Because the fraction part for each ratio is rounded up, the total may exceed 100. Note the following:
    • A full page may not always fall in the usage ratio range of 91 to 100%.
    • A free space created by update processing that shortens an existing row is not treated as free space.
    • If the ratio of used pages is less than (100 - free space ratio specified with PCTFREE), the storage efficiency may have decreased. You should consider executing pdrorg. For an area storing branch rows (variable-length character string data with a length of 256 bytes or greater, data for repetition columns, and data for columns of abstract data type), executing pdrorg has no effect on the storage efficiency.
  41. Check pending status for referential constraint in the table information in the RDAREA. If no referential constraint has been defined for the table, this item is blank.
    P: Check pending status
    Blank: Not check pending status
  42. Check pending status for check constraint in the table information in the RDAREA. If no check constraint has been defined for the table, this item is blank.
    P: Check pending status
    Blank: Not check pending status
  43. Identifier of the unit containing an audit trail file that might not have been changed to data loading completed status, because pdload terminated abnormally during data loading to the audit trail table (even though data loading to the audit trail file was completed). If pdload did not terminate abnormally or the timing of abnormal termination has nothing to do with the change of audit trail file status, this field is blank.
  44. Generation number of the audit trail file (last 3 characters of the audit trail file name) whose status might not have been changed for the same reason as in No. 43. If pdload did not terminate abnormally or the timing of abnormal termination has nothing to do with the change of audit trail file status, this field is blank.
  45. Summary information for segments. This information does not include binary-only segments.
  46. Summary information for pages.
  47. Percentage of binary-only segments that are used segments.
    [Figure]Number of used binary-only segments[Figure]total number of binary-only segments allocated to the table or index[Figure] 100 (%)[Figure]
  48. Percentage of binary-only segments that are full segments.
    [Figure]Number of full binary-only segments[Figure]total number of binary-only segments allocated to the table or index[Figure] 100 (%)[Figure]
  49. Number of used binary-only segments. Segments being released by pdreclaim are displayed as used binary-only segments.
  50. Number of full binary-only segments.
  51. Total number of binary-only segments allocated to the table or index.
  52. Summary information for binary-only segments.
  53. Percentage of pages in the binary-only segments that are used pages.
    [Figure]Number of used pages in the binary-only segments[Figure]total number of pages in the binary-only segments allocated to the table or index[Figure] 100(%)[Figure]
  54. Percentage of pages in the used binary-only segments that are full pages.
    [Figure]Number of full pages in the used binary-only segments[Figure]total number of pages in the binary-only segments allocated to the table or index[Figure] 100(%)[Figure]
  55. Number of used pages in the binary-only segments.
  56. Number of full pages in the binary-only segments.
  57. Total number of pages in the binary-only segments allocated to the table or index.
  58. Summary information for pages in the binary-only segments.
  59. Number of segments being released if pdreclaim with the -a option specified is running concurrently or terminated with an error. If pdreclaim with the -a option specified is not executed, 0 is displayed.
  60. Reuse option value specified in SEGMENT REUSE in the table definition.
  61. Free area search mode for branch rows:
    INS: New page allocate mode
    REU: Free page reuse mode
    If improve storage efficiency for tables with multiple branch rows is not specified for the reuse option value in SEGMENT REUSE, "-" is displayed.
  62. Number of full pages that can be released by pdreclaim or pdrorg.
  63. Total number of managed rows in table deleted status.
Note 1
Nos. 28 to 30 are displayed when HiRDB Staticizer Option is used. If a generation is specified for analysis and the specified replica RDAREA generation does not exist, the utility issues a warning message, in which case no results are displayed.
Note 2
Nos. 39 to 40 are displayed when the -d option is specified.
Note 3
Nos. 43 and 44 are displayed for an audit trail table.
Note 4
Nos. 45 to 58 are displayed if a column of the BINARY type with a definition length of at least 256 bytes or a column of an abstract data type has been defined.
Explanation
  1. Date and time of completion of acquisition of the information required for this condition analysis, in the format YYYY/MM/DD hh:mm:ss:
    YYYY: Year. MM: Month. DD: Date. hh: Hour. mm: Minute. ss: Second.
  2. Name of the table subject to analysis.
  3. Authorization identifier of the table subject to analysis.
  4. Name of the hash function used if the table is partitioned with flexible hash or FIX hash. Hash is displayed for flexible hash partitioning; Fix Hash is displayed for FIX hash partitioning. This field is blank if the table uses neither flexible nor FIX hash partitioning.
  5. Total number of segments allocated to the table (number of used segments + number of unused segments).
  6. Total number of pages in the segments allocated to the table (used segments + unused segments).
  7. Maximum total number of segments allocated to the table per RDAREA.
  8. Maximum total number of pages in the segments allocated to the table per RDAREA.
  9. Total number of rows stored in the table (total number of rows stored in each RDAREA). This information is displayed only when the -s option is specified.
  10. Number of RDAREAs analyzed successfully and number of RDAREAs containing the table storage pages.
  11. Name of RDAREA subject to analysis. If it is a user LOB RDAREA, this field displays the name of the user RDAREA for the LOB column structure base table, followed by the name of the user LOB RDAREA for the LOB data.
  12. Indicates the name of the server managing the RDAREA.
  13. Total number of unused segments in the RDAREA. The parentheses enclose the total number of unused segments in HiRDB files that are not in frozen update status in the corresponding RDAREA.
  14. Percentage ratio of used segments:
    [Figure]number of used segments/value of 18[Figure][Figure] 100 (%)
  15. Percentage ratio of full segments:
    [Figure]number of full segments/value of 18[Figure][Figure] 100 (%)
  16. Number of used segments. Segments being released by pdreclaim are displayed as used segments.
  17. Number of full segments.
  18. Total number of segments allocated to the corresponding table in the RDAREA (number of used segments).
  19. Percentage ratio of used pages:
    [Figure]number of used pages/value of 23[Figure][Figure] 100 (%)
  20. Percentage ratio of full pages:
    [Figure]number of full pages/value of 23[Figure][Figure] 100 (%)
  21. Number of used pages.
  22. Number of full pages.
  23. Total number of pages in the segments allocated to the corresponding table in the RDAREA (number of used pages + number of unused pages).
  24. Number of rows stored in the RDAREA. This information is displayed only when the -s option is specified. There may be an error in the storage rows count because a NOWAIT search is conducted.
  25. Name of LOB column.
  26. Whether or not order has been lost in the LOB RDAREA:
    Y: Order has been lost.
    N: Order has not been lost.
  27. Position information indicating that the segment being used is the last segment. This information is displayed in the format last-position/total-number-of-segments. When No. 26 is Y, this field always indicates the last segment.
  28. Whether or not the LOB management entries are all used:
    Y: All are used.
    N: There are unused entries.
  29. Job name specified during data loading, reorganization, or reloading with the synchronization point specification. Parentheses enclose the type of utility that uses this job:
    L: pdload
    R: pdrorg
    Blank: Not set
  30. Number of rows stored in the database during data loading, reorganization, or reloading with the synchronization point specification.
  31. Index creation method used during data loading, reorganization, or reloading with the synchronization point specification (value of the -i option).
  32. Name of the original RDAREA.
  33. Generation number of the corresponding RDAREA.
  34. Number of replica RDAREAs.
  35. Shutdown history of the corresponding RDAREA.
    History1: Shutdown information immediately before the shutdown was released (if the RDAREA is currently shut down, this field displays the current shutdown information)
    History2: Shutdown information immediately preceding History1
  36. Shutdown type of the corresponding RDAREA. If there is no history, this field is blank.
    CMD: Command shutdown by HiRDB error detection
    FLT: Error shutdown
  37. Code indicating the cause of the shutdown of the corresponding RDAREA. For details about the shutdown cause code, see 15.3.1(2)(a) For an RDAREA other than a LOB RDAREA.
  38. Shutdown time of the corresponding RDAREA. If there is no history, this field is blank.
  39. Page search mode:
    INS: New page allocate mode
    REU: Free page reuse mode
  40. Number of segments specified for SEGMENT REUSE during execution of CREATE TABLE or ALTER TABLE. If a number of segments for SEGMENT REUSE is not specified, this field displays 0. If NO is specified for SEGMENT REUSE or SEGMENT REUSE is omitted, the field displays -.
  41. Number of times the mode changed from new page allocate to free page reuse and then changed back to new page allocate because there was no reusable free space (n/m).
    n: Idle count
    m: Number of times mode changed from new page allocate to free page reuse
    n and m are reset to 0 at the following times:
    • When the RDAREA is closed
    • When HiRDB is restarted
    • When the PURGE TABLE statement is executed
  42. Whether the table is in reload-not-completed data status or is being reorganized. If the table is not in either of the following statuses, this field is blank.
    DATA_UNFINISH:
    The table is in reload-not-completed data status, which means that table reorganization was executed, but reload processing has not been completed for some reason such as an error. To release the table from reload-not-completed data status, you must re-execute table reorganization or reload data into the table.
    ON_RORG:
    The table is currently being reorganized.
  43. Summary information for segments. This information does not include binary-only segments.
  44. Summary information for pages.
  45. Number of segments being released if pdreclaim with the -a option specified is running concurrently or terminated with an error. If pdreclaim with the -a option specified is not executed, 0 is displayed.
  46. Reuse option value specified in SEGMENT REUSE in the table definition.
  47. Free area search mode for branch rows:
    INS: New page allocate mode
    REU: Free page reuse mode
    If improve storage efficiency for tables with multiple branch rows is not specified for the reuse option value in SEGMENT REUSE, "-" is displayed.
  48. Number of full pages that can be released by pdreclaim or pdrorg.
  49. Total number of managed rows in table deleted status.
Note
Nos. 32 to 34 are displayed when HiRDB Staticizer Option is used. If a generation is specified for analysis and there is no specified generation of replica RDAREA area, the utility issues a warning message, in which case no results are displayed.
(b) Condition analysis by index
Explanation
  1. Date and time of completion of acquisition of the information required for this condition analysis, in the format YYYY/MM/DD hh:mm:ss:
    YYYY: Year. MM: Month. DD: Date. hh: Hour. mm: Minute. ss: Second.
  2. Name of the index subject to analysis.
  3. Authorization identifier of the index subject to analysis.
  4. Total number of segments allocated to the index (number of used segments + number of unused segments).
  5. Total number of pages in the segments allocated to the index (used segments + unused segments).
  6. Maximum total number of segments allocated to the index per RDAREA.
  7. Maximum total number of pages in the segments allocated to the index per RDAREA.
  8. Number of RDAREAs analyzed successfully and number of RDAREAs containing the index storage pages.
  9. Name of an RDAREA subject to analysis.
  10. Name of the server managing the RDAREA.
  11. Total number of unused segments in the RDAREA.
  12. Percentage ratio of used segments:
    [Figure]number of used segments/value of 16[Figure][Figure] 100 (%)
  13. Percentage ratio of full segments:
    [Figure]number of full segments/value of 16[Figure][Figure] 100 (%)
  14. Number of used segments. Segments being released by pdreclaim are displayed as used segments.
  15. Number of full segments.
  16. Total number of segments allocated to the corresponding index in the RDAREA (number of used segments).
  17. Percentage ratio of used pages:
    [Figure]number of used pages/value of 21[Figure][Figure] 100 (%)
  18. Percentage ratio of full pages:
    [Figure]number of full pages/value of 21[Figure][Figure] 100 (%)
  19. Number of used pages.
  20. Number of full pages.
  21. Total number of pages in the segments that are allocated to the corresponding index in the RDAREA (number of used pages + number of unused pages).
  22. Status of unfinished index during index creation (if the index has been completed, this field is blank):
    UNFINISH_0
    See Figure 15-9 Types of unfinished index by table (1/2) and Figure 15-10 Types of unfinished index by table (2/2).
    UNFINISH_1
    See Figure 15-11 Types of unfinished index by RDAREA (1/2) and Figure 15-12 Types of unfinished index by RDAREA (2/2).
  23. Name of the original RDAREA.
  24. Generation number of the corresponding RDAREA.
  25. Number of replica RDAREAs.
  26. Shutdown history of the corresponding RDAREA.
    History1: Shutdown information immediately before the shutdown was released (if the RDAREA is currently shut down, this field displays the current shutdown information)
    History2: Shutdown information immediately preceding History1
  27. Shutdown type of the corresponding RDAREA. If there is no history, this field is blank.
    CMD: Command shutdown by HiRDB error detection
    FLT: Error shutdown
  28. Code indicating the cause of the shutdown of the corresponding RDAREA. For details about the shutdown cause code, see 15.3.1(2)(a) For an RDAREA other than a LOB RDAREA.
  29. Shutdown time of the corresponding RDAREA. If there is no history, this field is blank.
  30. Number of pages that can be released by pdreclaim (number of used free pages whose usage ratio is 0%).
  31. Number of pages that are under release processing because pdreclaim is currently executing or has terminated due to an error. If pdreclaim has terminated with an error, re-execute it (the value of this field becomes 0). The displayed page count is included in the percentage ratio of used pages (0%).
  32. Number of used pages for each 10% range. The parentheses enclose the percentage ratio to all used pages. Because the fraction part for each ratio is rounded up, the sum of all values may exceed 100. Note the following:
    • For a page that stores 201 or more duplicate keys, the percentage ratio of PCTFREE specified during table definition is ignored. Therefore, data is not rearranged according to the PCTFREE value even during reorganization.
    • For an index to which the index key value with no lock is applied, the deletion key value is treated as free space. If all used pages are deletion key values, the usage ratio is 0%.
    • A full page may not always fall in the usage ratio range of 91 to 100%.
    • A free space created by update processing that shortens an existing row is not treated as free space.
    • If the ratio of used pages is less than (100 - free space ratio specified with PCTFREE), the storage efficiency may have decreased. You should consider executing pdrorg. For an area that stores branch rows (variable-length character string data with a length of 256 bytes or greater, data for repetition columns, and data for columns of abstract data type), executing pdrorg has no effect on the storage efficiency.
  33. Numeric values for the items shown below, when index key values with no locks are used or uncommitted deletion data is locked.
    • Number of rows managed by remaining entries for each indicated range of percentages of used index pages
      (Remaining entries are index entries that still exist in the index due to lock control even after their keys have been deleted as a result of updating or deletion of index key values)
    The utility displays 3 digits, and the maximum length is 5 digits when a decimal point and units character are included. The decimal point and units character are displayed when the number of rows to be displayed requires more than 3 digits. In such a case, rounding occurs at the 4th digit. For the display format for the number of rows managed by remaining entries, see 15.3.1(2)(a) For an RDAREA other than a LOB RDAREA.
  34. Total number of used pages and total number of rows managed by remaining entries for all percentage ranges.
  35. Total number of rows managed by the index. The utility displays an integer of up to 15 digits in length. If the value consists of more than 15 digits, the utility displays it as an exponent (for example, 1000000000000000 would be displayed as 1E+15). For the display formats, see 15.3.1(2)(a) For an RDAREA other than a LOB RDAREA.
  36. Total number of managed rows in deleted status in the index and the percentage of all managed rows in the index that are managed rows in deleted status.
  37. Number of segments being released if pdreclaim with the -a option specified is running concurrently or terminated with an error. If pdreclaim with the -a option specified is not executed, 0 is displayed.
Note 1
Nos. 23 to 25 are displayed when HiRDB Staticizer Option is used. If a generation is specified for analysis and the specified replica RDAREA generation does not exist, the utility issues a warning message, in which case no results are displayed.
Note 2
Nos. 30 to 32 are displayed when the -d option is specified.
Nos. 33 to 35 are displayed when the -d and -z options are both specified.
Explanation
  1. Date and time of completion of acquisition of the information required for this condition in the format YYYY/MM/DD hh:mm:ss.
    YYYY: Year. MM: Month. DD: Day. hh: Hour. mm: Minute. ss: Second
  2. Name of the index subject to analysis.
  3. Authorization identifier for the index subject to analysis.
  4. Total number of segments allocated to the index (number of used segments + number of unused segments).
  5. Total number of pages in the segments allocated to the index (used segments + unused segments).
  6. Maximum total number of segments allocated to the index per RDAREA.
  7. Maximum total number of pages allocated to the index per RDAREA.
  8. Number of RDAREAs analyzed successfully, and number of RDAREAs containing the index storage pages.
  9. Name of the RDAREA subject to analysis.
  10. Name of the server managing the RDAREA.
  11. Total number of unused segments in the RDAREA. The parentheses enclose the total number of unused segments in HiRDB files that are not in frozen update status in the corresponding RDAREA.
  12. Percentage ratio of used segments:
    [Figure]number of used segments/value of 16[Figure][Figure] 100 (%)
  13. Number of used segments. Segments being released by pdreclaim are displayed as used segments.
  14. Total number of segments allocated to the corresponding indexes in the RDAREA (number of used segments).
  15. Status of an unfinished index during index creation (if the index has been completed, this field is blank).
    UNFINISH_0
    See Figure 15-9 Types of unfinished index by table (1/2) and Figure 15-10 Types of unfinished index by table (2/2).
    UNFINISH_1
    See Figure 15-11 Types of unfinished index by RDAREA (1/2) and Figure 15-12 Types of unfinished index by RDAREA (2/2).
    UNFINISH_2
    The plug-in index is being updated on a delayed basis. When a UAP using the plug-in index delayed batch creation function is being executed, or after the UAP finished executing, a batch index creation process using the database reorganization utility will not be executed. For details about the plug-in index delayed batch creation function, see the HiRDB Version 9 System Operation Guide.
  16. Whether or not order is lost in the LOB RDAREA.
    Y: Order is lost.
    N: Order is not lost.
  17. Position information that indicates the last segment that is used in a last position/total number of segments format. If Segment Over is Y, this field always indicates the last segment.
  18. Whether or not the LOB management entries are all used:
    Y: All are used.
    N: There are unused entries.
  19. Name of the original RDAREA.
  20. Generation number of the corresponding RDAREA.
  21. Number of replica RDAREAs.
  22. Shutdown history of the corresponding RDAREA.
    History1: Shutdown information immediately before the shutdown was released (if the RDAREA is currently shut down, this field displays the current shutdown information)
    History2: Shutdown information immediately preceding History1
  23. Shutdown type of the corresponding RDAREA. If there is no history, this field is blank.
    CMD: Command shutdown by HiRDB error detection
    FLT: Error shutdown
  24. Code indicating the cause of the shutdown of the corresponding RDAREA. For details about the shutdown cause code, see 15.3.1(2)(a) For an RDAREA other than a LOB RDAREA.
  25. Shutdown time of the corresponding RDAREA. If there is no history, this field is blank.
Note
Nos. 19 to 21 are displayed when HiRDB Staticizer Option is used. If a generation is specified for analysis and the specified replica RDAREA generation does not exist, the utility issues a warning message, in which case no results are displayed.

(3) Analyzing the analysis results

You can analyze the results of condition analysis by table or index in the same manner as for condition analysis by RDAREA (logical analysis).

If the storage rows are not distributed uniformly among RDAREAs, you are not making the best use of the HiRDB parallel server configuration's performance. In such a case, take appropriate measures, such as checking and revising key range partitioning or applying flexible hash partitioning or FIX hash partitioning (or changing the hash functions). The result should be to partition the storage uniformly among the RDAREAs.

If you have analyzed the status of an RDAREA the stores the LOB attribute of a plug-in-provided abstract data type or an RDAREA that stores a plug-in index, the percentage of used segments in the RDAREA depends on the method used by the plug-in to allocate segments.