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.

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

(a) Condition analysis by table

• When no LOB RDAREA is included
  

  pddbst VV-RR(Object Option) ** Table Analysis ** 2003/03/31 18:53:24 [1] Table Name : TBL01 [2] Auth Id : user1 [3] Hash : [4] Total Segment : 104 [5] Max Sum Segment : 104 [7] Total Page : 520 [6] Max Sum Page : 520 [8] Total Row : 500 [9] RD Area Count : 1/ 1 [10] -------------------------------------------------------------------------------- RD Area Name : user_rdarea_1 [11] Server : bes1 [12] Reference Pending Status : P [41] Check Pending Status : P [42] Original RD Area Name : user_rdarea_1 [28] Generation Number : 0[29] Replica RD Area Count : 0[30] Status : [38] [31] [32] [33] [34] History1 Hold Status : Hold Code : 0 Hold Time : History2 Hold Status : Hold Code : 0 Hold Time : Job Name : [25] Line Count : [26] Index Method : [27] Unused Segment: 24 [13] Search Mode : INS [35] Segment Reuse : - segments [36] Reuse Search Failure : 0/ 0 [37] Used(Full) Used( Full) Sum [14] [15] [16] [17] [18] Segment 100%( 0%) 104( 0) 104 [19] [20] [21] [22] [23] Page 100%( 0%) 518( 0) 520 Row Count : 500 [24] Collect Prearranged Page : 55 [39] Used Page Ratio Page(Ratio) [40] 0% : 57( 11%) 1- 10% : 19( 4%) 11- 20% : 28( 6%) 21- 30% : 70( 14%) 31- 40% : 95( 19%) 41- 50% : 71( 14%) 51- 60% : 69( 14%) 61- 70% : 58( 12%) 71- 80% : 37( 8%) 81- 90% : 14( 3%) 91-100% : 2( 1%) Total 520

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:
    number of used segments/value of 18 100 (%)
    
15. Percentage ratio of full segments:
    number of full segments/value of 18 100 (%)
    
16. Number of 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:
    number of used pages/value of 23 100 (%)
    
20. Percentage ratio of full pages:
    number of full pages/value of 23 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 RDAREA to be analyzed.
29. Always 0.
30. Always 0.
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-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
    

Note 1

Nos. 39-40 are displayed when the -d option is specified.

• When a LOB RDAREA is included
  

  pddbst VV-RR(Object Option) ** Table Analysis ** 2003/04/03 12:37:22 [1] Table Name : TBL03 [2] Auth Id : user1 [3] Hash : [4] Total Segment : 1826 [5] Max Sum Segment : 1822 [7] Total Page : 1842 [6] Max Sum Page : 1822 [8] Total Row : 500 [9] RD Area Count : 2/ 2 [10] -------------------------------------------------------------------------------- RD Area Name : user_rdarea_3 [11] Server : bes1 [12] Status : [42] Original RD Area Name : user_rdarea_3 [32] Generation Number : 0[33] Replica RD Area Count : 0[34] [35] [36] [37] [38] History1 Hold Status : Hold Code : 0 Hold Time : History2 Hold Status : Hold Code : 0 Hold Time : Job Name : [29] Line Count : [30] Index Method : [31] Unused Segment: 145 [13] Search Mode : INS [39] Segment Reuse : - segments [40] Reuse Search Failure : 0/ 0 [41] Used(Full) Used( Full) Sum [14] [15] [16] [17] [18] Segment 100%( 0%) 4( 0) 4 [19] [20] [21] [22] [23] Page 85%( 0%) 17( 0) 20 Row Count : 500 [24] RD Area Name : user_rdlob_2 Server : bes1 Status : Column Name : C2 [25] Original RD Area Name : user_rdlob_2 Generation Number : 0 Replica RD Area Count : 0 History1 Hold Status : Hold Code : 0 Hold Time : History2 Hold Status : Hold Code : 0 Hold Time : Unused Segment: 9178( 9178) Used Used Sum Segment 100% 1822 1822 Segment Over : N [26] Last Segment : 1822/ 11000 [27] Lobmap Over : N [28]

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:
    number of used segments/value of 18 100 (%)
    
15. Percentage ratio of full segments:
    number of full segments/value of 18 100 (%)
    
16. Number of 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:
    number of used pages/value of 23 100 (%)
    
20. Percentage ratio of full pages:
    number of full pages/value of 23 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 RDAREA to be analyzed.
33. Always 0.
34. Always 0.
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.

(b) Condition analysis by index

• B-tree index
  

  pddbst VV-RR(Object Option) ** Index Analysis ** 2003/03/31 18:56:00 [1] Index Name : IDX_TBL01_C1 [2] Auth Id : user1 [3] Total Segment : 1 [4] Max Sum Segment : 1 [6] Total Page : 5 [5] Max Sum Page : 5 [7] RD Area Count : 1/ 1 [8] -------------------------------------------------------------------------------- RD Area Name : user_rdarea_1 [9] Server : bes1 [10] Status : [22] Original RD Area Name : user_rdarea_1 [23] Generation Number : 0[24] Replica RD Area Count : 0[25] [26] [27] [28] [29] History1 Hold Status : Hold Code : 0 Hold Time : History2 Hold Status : Hold Code : 0 Hold Time : Unused Segment: 24 [11] Used(Full) Used( Full) Sum [12] [13] [14] [15] [16] Segment 100%( 0%) 1( 0) 1 [17] [18] [19] [20] [21] Page 100%( 0%) 5( 0) 5 Collect Prearranged Page : 0 [30] Collect On Page : 0 [31] Used Page Ratio Page(Ratio) [32] 0% : 0( 0%) 1- 10% : 1( 20%) 11- 20% : 0( 0%) 21- 30% : 3( 60%) 31- 40% : 0( 0%) 41- 50% : 1( 20%) 51- 60% : 0( 0%) 61- 70% : 0( 0%) 71- 80% : 0( 0%) 81- 90% : 0( 0%) 91-100% : 0( 0%) Total 5

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:
    number of used segments/value of 16 100 (%)
    
13. Percentage ratio of full segments:
    number of full segments/value of 16 100 (%)
    
14. Number of 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:
    number of used pages/value of 21 100 (%)
    
18. Percentage ratio of full pages:
    number of full pages/value of 21 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 Figures 15-9 and 15-10.

    UNFINISH_1

    See Figures 15-11 and 15-12.

23. Name of the RDAREA to be analyzed.
24. Always 0.
25. Always 0.
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-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.

Note 1

Nos. 30-32 are displayed when the -d option is specified.

• Plug-in index
  

  pddbst VV-RR(Object Option) ** Index Analysis ** 2002/03/29 18:05:06 [1] Index Name : IDX05 [2] Auth Id : MANUAL [3] Total Segment : 9501[4] Max Sum Segment : 9501 [6] Total Page : 9501[5] Max Sum Page : 9501 [7] RD Area Count : 1/ 1 [8] -------------------------------------------------------------------------------- RD Area Name : USER_RDLOB_10 [9] Server : BES02 [10] Status : [15] Original RD Area Name : USER_RDLOB_10 [19] Generation Number : 0[20] Replica RD Area Count : 0 [21] [22] [23] [24] [25] History1 Hold Status : Hold Code : 0 Hold Time : History2 Hold Status : Hold Code : 0 Hold Time : Unused Segment: 499( 499) [11] [12] [13] [14] Used Used Sum Segment 100% 9501 9501 [16] [17] Segment Over : N Last Segment : 9501/ 10000 [18] Lobmap Over : N

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:
    number of used segments/value of 16 100 (%)
    
13. Number of 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 Figures 15-9 and 15-10.

    UNFINISH_1

    See Figures 15-11 and 15-12.

    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 8 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 RDAREA to be analyzed.
20. Always 0.
21. Always 0.
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.

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'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.