Reports the statistics of index block utilization.

Syntax

proutil db-name -C idxblockreport [ owner-name.]table-name.index-name
[ tenant tenant-name| group group-name | partition partition-name | composite initial ]

Parameters

db-name
Specifies the name of the source database.
owner-name
Specifies the owner of the table containing the data to be reported. You must specify an owner name unless the table name is unique within the database, or the table is owned by PUB. By default, ABL tables are owned by PUB.
table-name
Specifies the name of the source table containing the source index to be reported.
index-name
Specifies the source index to be reported.
tenant tenant-name
Specifies that only the index partitions of tenant-name are reported. If tenant-name does not own any partition of the specified index, IDXBLOCKREPORT exits with an error. Specifying tenant is allowed only on the databases that are enabled for multi-tenancy.
group group-name
Specifies that only the index partitions of group-name are reported. If group-name does not own any partition of the specified index, IDXBLOCKREPORT exits with an error. Specifying group is allowed only on the databases that are enabled for multi-tenancy.
partition partition-name
Specifies that only the local indexes of the partition-name table partition are reported. IDXBLOCKREPORT exits with an error if:
  • The partition-name is not a partition for table-name.
  • The index-name is not a local index for table-name.
  • The partition-name is neither allocated nor in a rebuilding state.
  • The partition-name is in an inconsistent state due to pending operations such as merge, split, data move, and so on.
Specifying partition is allowed only on the databases that are enabled for table partitioning.
composite initial
Specifies that only the indexes of the initial partition for a partitioned table are reported. IDXBLOCKEREPORT exits with an error if:
  • The composite initial partition for table-name does not exist.
  • The index-name is not a local index for table-name.
  • The composite initial partition is neither allocated nor in a rebuilding state.
  • A value other than initial is specified for the composite argument.
Specifying composite initial is allowed only on the databases that are enabled for table partitioning.

Notes

  • The index block report provides utilization information for a given index. Run the IDXBLOCKREPORT utility as a baseline check on indexes that may need compacting.
  • If the index is for a multi-tenant table, the data is reported on the B-tree for the specified tenant or group.
    Example: Output for an index block report run on a multi-tenant table for a specific tenant.
     BlockSize = 4096  Block Capacity = 4004
                Number  Length  On      Length  Delete
                of      of      Delete  of      Chain           Percent
DBKEY   Level   Entries Entries Chain   Size    Type            Utilized
1408    1       16      166     0       0       root            4
1472    2       73      2644    0       0       leaf            66
7872    2       77      2648    0       0       leaf            66
7680    2       73      2673    0       0       leaf            66
7808    2       85      2779    0       0       leaf            69
6400    2       60      2346    0       0       leaf            58
  • If you specify a partition for a partitioned table, the report is displayed without any modifications to its standard format. However, if you do not specify a partition name for a partitioned index, the IDXBLOCKREPORT utility reports the statistics for all partitions of the partitioned index, each one with its own header with the addition of a new line to describe the partition name and ID.
    Example: Output for an index block report run on an index with two partitions.
    Partition mypartition1 2

BlockSize = 4096  Block Capacity = 4004
                Number  Length  On      Length  Delete
                of      of      Delete  of      Chain           Percent
DBKEY   Level   Entries Entries Chain   Size    Type            Utilized
1408    1       16      166     0       0       root            4
1472    2       73      2644    0       0       leaf            66
7872    2       77      2648    0       0       leaf            66
7680    2       73      2673    0       0       leaf            66
7808    2       85      2779    0       0       leaf            69
6400    2       60      2346    0       0       leaf            58

Partition mypartition2 3

BlockSize = 4096  Block Capacity = 4004
                Number  Length  On      Length  Delete
                of      of      Delete  of      Chain           Percent
DBKEY   Level   Entries Entries Chain   Size    Type            Utilized
1408    1       16      166     0       0       root            4
1472    2       73      2644    0       0       leaf            66
  • If a partition is not allocated, it is skipped and does not appear in the report. If a partition is in an inconsistent state, the utility reports it and move on to the next partition.

  • For the composite partition, the heading information is:

    Composite partition initial 0

  • PROUTIL IDXBLOCKREPORT can be run either online or offline.
  • Multiple users can run PROUTIL IDXBLOCKREPORT simultaneously for different partitions of the same index.
  • When the IDXBLOCKREPORT utility is run online, other users can use the index simultaneously for read operations only. No other administrative operation is allowed on the index.
  • The IDXBLOCKREPORT utility does not lock any record or table and does not compact the index.