PRINT INDEX

The PRINT INDEX utility reports on the structure of system-owned indexes and indexed sets. Using the PRINT INDEX utility, you can review:
idmscu19
The PRINT INDEX utility reports on the structure of system-owned indexes and indexed sets. Using the PRINT INDEX utility, you can review:
  • The number of levels in an index
  • The contents of the fixed and variable portions of one or more SR8 records in an index
  • The amount of available space on the page containing each SR8 in an index
This article describes the following information:
2
2
Authorization
To
You Need This Privilege
On
Report on indexes in a segment
DBAREAD
The area containing the index and the area(s) containing records referenced by the index
Syntax
►►─── PRINT INDEX ─┬─ 
set-name
 set-specifications ──────────────┬────────────►                    └─ SR8 
occurrence-key-1
 ── 
report-option
 ────┘  ►────┬────────────────────────────────┬──┬──────────────┬───────────────────►◄       ├─ ONLY ◄────────────────────────┤  ├─ DECIMAL ◄───┤       ├─ TREE ─────────────────────────┤  ├─ HEX ────────┤       ├─ FULL ─────────────────────────┤  └─ TERSE ──────┘       ├─ LEG ──────────────────────────┤       ├─ SUMMARY ───┬────────────┬─────┤       │             ├─ ONLY ◄────┤     │       │             └─ DETAILED ─┘     │       └─┬─ NEXT ──┬─┬────────────────┬─┘         ├─ PRIOR ─┤ └─ 
level-number
 ─┘         └─ LVL ───┘
Expansion of set-specifications
►►──┬── SEGMENT 
segment-name
 ──┬───────────────────────────────────────────────►     └─  DBNAME 
db-name
 ────────┘  ►─┬─ USING subschema-name ─┬───────────────────────────────┬────────────────┬─►    │                        ├─ OWNER ──┬─ 
occurrence-key-2
 ─┘                │    │                        └─ MEMBER ─┘                                     │     │                                                                         │    └─ TABLE 
schema-name.table-id
 ─┬─────────────────────────────────────────┬┘                                   ├─ REFERENCED ──┬ ROWID 
occurrence-key-2
 ─┘                                   └─ REFERENCING ─┘  ►─┬─ report-option ────────────────┬──────────────────────────────────────────►◄    ├─ FULL ─────────────────────────┤    └─ SUMMARY ───┬────────────┬─────┘                  ├─ ONLY ◄────┤                  └─ DETAILED ─┘
Expansion of occurrence-key-1
►►─┬───────────────┬─┬─ X'hex-database-key'──┬────────────────────────────►◄    └─ page-group: ─┘ └─ 
page-num:line-num
 ───┘
Expansion of occurrence-key-2
►►──┬─ X'hex-database-key'──┬─────────────────────────────────────────────►◄     └─ 
page-num:line-num
 ───┘
Expansion of report-option
►►────┬────────────────────────────────┬──────────────────────────────────►◄       ├─ ONLY ◄────────────────────────┤        ├─ TREE ─────────────────────────┤        ├─ LEG ──────────────────────────┤       ├─ NEXT ──┬──┬───────────────┬───┘       ├─ PRIOR ─┤  └─
level-number
 ─┘        └─ LVL ───┘
Parameters
  • set-name
    Specifies the name of the system-owned index or indexed set on which the PRINT INDEX statement is to report.
    When processing a system-owned index, processing begins at the first SR8 record in the SR7-SR8 set.
  • SEGMENT
    Specifies the segment containing the index structures to be reported. When using the FULL option, or when specifying a starting member dbkey, the member area must also exist in this segment.
  • segment-name
    Specifies the name of the segment.
  • DBNAME
    Specifies the database containing the index structures to be reported.
  • db-name
    Specifies the name of the database.
  • USING
    subschema-name
    Specifies the name of the subschema in which the named indexed set is included.
  • TABLE
    schema.table-id
    Specifies the name of a table.
  • REFERENCED ROWID
    For the named table, directs the PRINT INDEX utility to report on the index occurrence whose owner is the referenced row identified by
    occurrence-key
    .
  • REFERENCING ROWID
    For the named table, directs the PRINT INDEX utility to report on the index occurrence containing the row ID of the referencing row identified by
    occurrence-key
    .
  • X'
    hex-database-key
    '
    Specifies the hexadecimal database key of an owner or member record in the specified indexed set.
  • page-num
    Specifies the page number of an owner or member record in the specified indexed set.
  • line-num
    Specifies the line number of an owner or member record in the specified indexed set.
  • SR8
    Identifies the index to be processed by specifying an index of an SR8 record in the index.
  • page-group
    Identifies the page group of the SEGMENT where the index resides.
  • X'
    database-key
    '
    Specifies the hexadecimal database key of the SR8 record.
  • page-num
    Specifies the page number of the SR8 record.
  • line-num
    Specifies the line number of the SR8 record.
  • ONLY
    Directs the PRINT INDEX utility to report only on the SR8 record used as the entry point into the index.
    ONLY is the default when you do not specify a portion of the index structure to report on.
  • TREE
    Directs the PRINT INDEX utility to report on all the SR8 records in the index, starting with the top-level SR8. SR8s are processed by following the next pointers.
  • FULL
    Directs the PRINT INDEX utility to report on:
    • All the SR8 records in the index, starting with the top-level SR8. SR8s are processed by following the next pointers.
    • The database key, index pointer value, and orphan condition of each member record in the index. Member records are processed by walking the bottom level of the index.
  • LEG
    Directs the PRINT INDEX utility to report on the SR8 records connected by up pointers, starting with the SR8 used as the entry point into the index.
    For an unsorted index or for an entry SR8 that is the top-level SR8 in a sorted index, specifying LEG has the same effect as specifying ONLY.
  • NEXT
    Directs the PRINT INDEX utility to report on the SR8 records connected by next pointers in a single level of the index, starting with the SR8 used as an entry point into the index.
  • PRIOR
    Directs the PRINT INDEX utility to report on the SR8 records connected by prior pointers in a single level of the index, starting with the SR8 used as an entry point into the index.
  • LVL
    Directs the PRINT INDEX utility to report on all the SR8 records in a single level of the index.
  • level-number
    Specifies the index level to report on; an integer in the range 0 through 255.
    By default, if you do not specify an index level, the PRINT INDEX utility reports on the SR8s in the level of the SR8 record used as the entry point into the index.
  • SUMMARY
    Requests a summary report for the target index.A summary report consists of three parts:
    • Part 1 (header) provides general information on the index definition.
    • Part 2 (main body) provides information on index owner occurrence(s). A system-owned index contains a single index owner; a user-owned index can contain more than one index owner.
    • Part 3 (index overview) provides global statistical information for a user-owned index only.
    A summary report on a system-owned index contains parts 1 and 2.
    A summary report on a user-owned index always contains parts 1 and 3. Part 2 is included only in a detailed summary report.
    • ONLY
      Requests a summary report with parts 1 and 3 for the target user-owned index. This parameter is ignored for a system-owned index. ONLY is the default.
    • DETAILED
      Requests a summary report with parts 1, 2, and 3 for the target user-owned index. This parameter is ignored for a system-owned index.
  • DECIMAL
    Directs the PRINT INDEX utility to print both the fixed and variable portions of each SR8 record in the report. Symbolic keys in the variable portion of each SR8 are printed in decimal (display) format.
    DECIMAL is the default when you do not specify the way in which the contents of the SR8s in the index are to be printed.
  • HEX
    Directs the PRINT INDEX utility to print both the fixed and variable portions of each SR8 record in the report. Symbolic keys in the variable portion of each SR8 are printed in hexadecimal format.
  • TERSE
    Directs the PRINT INDEX utility to print only the fixed portion of each SR8 record in the report.
Usage
How to submit the PRINT INDEX statement
You submit the PRINT INDEX statement by using the batch command facility or the online command facility.
When to use PRINT INDEX
The PRINT INDEX utility can help you determine whether an index needs to be rebuilt. For example, you should consider rebuilding an index when the PRINT INDEX utility report on the index indicates one of the following:
  • The number of index levels is greater than anticipated for the original index structure.
  • Twenty-five percent or more of the member records are orphans.
An index can be rebuilt using MAINTAIN INDEX or TUNE INDEX. For more information about index rebuilding and indexing in general, see the
CA IDMS Database Administration Section
.
The output of PRINT INDEX without the SUMMARY parameter is proportional to the number of index members that are being reported. If PRINT INDEX is run online or in batch through CV, the output is buffered in scratch. If the scratch area cannot contain all the output, PRINT INDEX fails with a task abend.
Hexadecimal display of symbolic keys
The HEX parameter of the SET/SR8 statement is useful when the symbolic key for the index is a non-displayable data type, such as binary or packed.
When to use DBNAME
You can use DBNAME instead of SEGMENT at any time. You must use it when an index member resides in a different segment from the index structure, and the FULL option is used, or you specify a starting MEMBER dbkey.
JCL Considerations
When you submit a PRINT INDEX utility through the batch command facility, the JCL to execute the facility must include statements to define:
  • The database files that contain the indexes and member records to be accessed
For more information about the generic JCL used to execute the batch command facility, see the section for your operating system in this section.
Examples
Printing an entire index
The following example directs the PRINT utility to report on the EMP-IDX-SET using the FULL option.
PRINT INDEX "EMP-IDX-SET" DBNAME VLDBDBN USING VLDBSUBC FULL;
Printing the bottom level of an index
The following example directs the PRINT utility to report on the COV-IDX-SET using the LEVEL and TERSE options.
PRINT INDEX "COV-IDX-SET" SEGMENT VLDBSPG1 USING VLDBSUBC  MEMBER X'01390448' LVL 0 TERSE;
Printing individual SR8 record
The following example directs the PRINT utility to report on a specific SR8 record.
PRINT INDEX SR8 5:80130:03 NEXT 2 HEX;
Printing an index from an SQL-defined database
The following example directs the PRINT utility to report on the EMP-COVERAGE index that is part of the SQLSPG.EMPLOYEE table.
PRINT INDEX "EMP-COVERAGE” SEGMENT VLDBSPG1 TABLE SQLSPG.EMPLOYEE SUMMARY;
Printing a summary report of an index
The following example directs the PRINT utility to report on the DEPT_EMPL index using the SUMMARY option.
PRINT INDEX DEPT_EMPL SEGMENT USERDB TABLE DEMO.DEPT SUMMARY;
Printing a REFERENCING ROWID summary report of an index
The following example directs the PRINT utility to report on the index occurrence containing the row ID of the referencing row identified by X'01390201'.
PRINT INDEX "COV-IDX-SET" SEGMENT VLDBSPG1 TABLE SQLSPG.COVERAGE REFERENCING ROWID X'01390201' SUMMARY;
Sample Output
Printing an entire index
The PRINT INDEX utility generates the following report after successful completion of the statement in the previous "Printing an Entire Index" example.
IDMSBCF  18.0                                      CA IDMS Batch Command Facility                               mm/dd/yy                                                                                                                                                                                                                                                                     PRINT INDEX "EMP-IDX-SET"                                                                                                                  DBNAME VLDBDBN                                                                                                              USING VLDBSUBC FULL;                                                                                                           SET=EMP-IDX-SET      OWNER=SR7              PAGE GROUP=2     RECORDS PER PAGE=255                                                                                                ODBK=01394301 SR8 N01394303 SR8 P01394306 ASC CUSH=12  SYM TKL=3    COMP                                     MEMBER=EMPLOYEE         PAGE GROUP=1     RECORDS PER PAGE=255                                                    L1   01394303  NUME=5                 U=FFFFFFFF N=01394302 P=01394301 RECL=224   SPA=3164                                                         01394302 0028  01394304 0053  01394307 0106  01394305 0329  01394306 0479                                              L0   01394302  NUME=15                U=01394303 N=01394304 P=01394303 RECL=184   SPA=3164                                                         0138DF01 0001  0138DE01 0003  0138CE01 0004  0138D801 0007  0138C501 0011  0138D401 0013  0138D101 0015                               0138CE02 0016  0138DE02 0019  0138DE03 0020  0138DE04 0021  0138DE05 0023  0138DE06 0024  0138DE07 0027                              0138DE08 0028                                                                                                               01394304  NUME=10    ORPH=8      U=01394303 N=01394307 P=01394302 RECL=140   SPA=3164     . . .                                       MEM  0138DF01                  U=01394302                                                                                                 0138DE01                  U=01394302                                                                                                 0138CE01                  U=01394302                                                                                                 0138D801                  U=01394302                                                                                                 0138C501                  U=01394302                                                                                                 0138D401                  U=01394302                                                                                                 0138D101                  U=01394302                                                                                                 0138CE02                  U=01394302                                                                                                 0138DE02                  U=01394302                                                                                                 0138DE03                  U=01394302                                                                                            . . . 0138CF02     *ORPHAN*OF*  U=01394305                                                                                                 0138CF03     *ORPHAN*OF*  U=01394305                                                                                                 0138D107                  U=01394306                                                                                                 0138D105     *ORPHAN*OF*  U=01394305                                                                                                 0138D108                  U=01394306                                                                                                 0138D106                  U=01394306                                                                                                 0138DB05                  U=01394306                                                                                                 0138DF03                  U=01394306                                                                                                 0138DF04                  U=01394306                                                                                                 0138D807                  U=01394306                                                                                                 0138DB04                  U=01394306                                                                                            TOTAL SR8=6                                                                                                                          Status = 0        SQLSTATE = 00000     
Printing the bottom level of an index
The next report illustrates the use of the LVL and TERSE options to request the printing of the bottom level of an index.
IDMSBCF  18.0                                      CA IDMS Batch Command Facility                               mm/dd/yy                                                                                                                                                                                                                                                              PRINT INDEX "COV-IDX-SET"                                                                                                                  SEGMENT VLDBSPG1 USING VLDBSUBC                                                                                                                                                                                                  MEMBER X'01390448' LVL 0 TERSE;                                                                                               SET=COV-IDX-SET      OWNER=SR7              PAGE GROUP=5     RECORDS PER PAGE=255                                                                                                ODBK=01390201 SR8 N01390203 SR8 P01390202 UNS CUSH=4                                                         MEMBER=COVERAGE         PAGE GROUP=5     RECORDS PER PAGE=255                                                    L0   01390203  NUME=4                 U=FFFFFFFF N=01390202 P=01390201 RECL=52    SPA=3820                                                01390202  NUME=70                U=FFFFFFFF N=01390201 P=01390203 RECL=316   SPA=3820                                           TOTAL SR8=2                                                                                                                          Status = 0        SQLSTATE = 00000    
Printing individual SR8 records
The following report illustrates the use of the SR8 option to request the printing of a specific SR8 record.
IDMSBCF  18.0                                      CA IDMS Batch Command Facility                               mm/dd/yy                                                                                                                                                                                                                                                                                                                                                                                            PRINT INDEX SR8 5:80130:03 NEXT 2 HEX;                                      SET=COV-IDX-SET      OWNER=SR7              PAGE GROUP=5     RECORDS PE           R PAGE=255                                                                                                        ODBK=01390201 SR8 N01390203            SR8 P01390202 UNS CUSH=4                                             L0   01390203  NUME=4                 U=FFFFFFFF N=01390202 P=01390201            RECL=52    SPA=3820                                                                 0139044A  01390449  01390448  01390447                             01390202  NUME=70                U=FFFFFFFF N=01390201 P=01390203            RECL=316   SPA=3820                                                                 01390446  01390445  01390444  01390443  01390442  01390441                  01390440  0139043F  0139043E  0139043D  0139043C  0139043B                  0139043A  01390439  01390438  01390437  01390436  01390435                  01390434  01390433  01390432  01390431  01390430  0139042F                  0139042E  0139042D  0139042C  0139042B  0139042A  01390429                  01390428  01390427  01390426  01390425  01390424  01390423                  01390422  01390421  01390420  0139041F  0139041E  0139041D                  0139041C  0139041B  0139041A  01390419  01390418  01390417               01390416  01390415  01390414  01390413  01390412  01390411               01390410  0139040F  0139040E  0139040D  0139040C  0139040B               0139040A  01390409  01390408  01390407  01390406  01390405               01390404  01390403  01390402  01390401                     TOTAL SR8=2
Printing an index from an SQL-defined database
The following example provides a report, using the FULL option, on an SQL-defined index.
  PRINT INDEX "COV-IDX-SET" SEGMENT VLDBSPG1                                      TABLE SQLSPG.COVERAGE FULL;                                       SET=COV-IDX-SET      OWNER=SR7              PAGE GROUP=5     RECORDS PER PAGE=255                                             ODBK=01390201 SR8 N01390203 SR8 P01390202 UNS CUSH=4                       MEMBER=COVERAGE         PAGE GROUP=5     RECORDS PER PAGE=255 L0   01390203  NUME=4                 U=FFFFFFFF N=01390202 P=01390201 RECL=52    SPA=3820      0139044A  01390449  01390448  01390447                          01390202  NUME=70                U=FFFFFFFF N=01390201 P=01390203 RECL=316   SPA=3820      01390446  01390445  01390444  01390443  01390442  01390441      01390440  0139043F  0139043E  0139043D  0139043C  0139043B . . . MEM  0139044A                  U=01390203      01390449                  U=01390203      01390448                  U=01390203      01390447                  U=01390203      01390446                  U=01390202 …      01390405                  U=01390202            01390404                  U=01390202            01390403                  U=01390202            01390402                  U=01390202            01390401                  U=01390202       TOTAL SR8=2                                    
Printing a summary report of an index
The following report illustrates the use of the SUMMARY option to request the printing of a user-owned index.
  PRINT INDEX "EMP-COVERAGE"       SEGMENT VLDBSPG1                                                           TABLE SQLSPG.EMPLOYEE SUMMARY;                                                                SET Name: EMP-COVERAGE                                                                                  IBC 70                               Displacement        0                                           Sort option NOT SORTED               Key length          N/A                                         Duplicates FIRST                     Compression         No                                       OWNER: EMPLOYEE                                                                                         AREA VLDBSPG1.EMPL-AREA              Low page  (SUB-     80056                                       Page size 4276                       High page  AREA)    80100                                       Page group 5                         Records per page    255                                      MEMBER: COVERAGE                        Set membership      Optional Automatic                          Located VIA index Yes Displ't 0      Index is            Linked                                      AREA VLDBSPG1.COVE-AREA              Low page  (SUB-     80106                                       Page size 4276                       High page  AREA)    80150                                       Page group 5                         Records per page    255                                                                                                                                           Index overview                                                                                          Nr of owner occurrences                      56                                                      Nr of owner occurrences                      56                           Nr of empty owners                           55      98.2%                Nr of displaced top level SR8s                0       0.0%                Nr of SR8s:              Total                2                                                  Average                0.0                                                Highest                2      Owner X'0138D404'    Min. nr of SR8s:         Total                2                                                  Average                0.0                                                Highest                2      Owner X'0138D404'    Nr of levels:          Average                0.0                                                Highest                1      Owner X'0138D404'    Min. nr of levels:     Average                0.0                                                Highest                1      Owner X'0138D404'    Nr of pages:           Average                0.0                                                Highest                1      Owner X'0138D404'    Min. nr of pages:      Average                0.0                                                Highest                1      Owner X'0138D404'    Nr of occurrences with orphans                0                           Nr of Orphans:           Total                0       0.0%                                           Highest                0      Owner *** N/A ***        Total size of all SR8s                      368                               Size of largest SR8                         316              Distribution of Index Levels                                                                                     ....+....20...+....40...+....60...+....80...+....                                                      2+|                                                            0    0.0%                                          1 |+                                                           1    1.7%                                                             0 |*************************************************-         55   98.2%                                                                                                                                                                                              Distribution of Minimum Index Levels                                                                                                        ....+....20...+....40...+....60...+....80...+....                                                                                 2+|                                                            0    0.0%                                                             1 |+                                                           1    1.7%                                                             0 |*************************************************-         55   98.2%                                                                                                                                                                                              Distribution of Number of SR8s                                                                                                              ....+....20...+....40...+....60...+....80...+....                                                                                 3+|                                                            0    0.0%                                                             2 |+                                                           1    1.7%                                                             1+|                                                            0    0.0%                                                             0 |*************************************************-         55   98.2%                                                           Distribution of Number of Index Members                                                                                                     ....+....20...+....40...+....60...+....80...+....                                                                                76+|                                                            0    0.0%                                                            72 |+                                                           1    1.7%                                                             1+|                                                            0    0.0%                                                             0 |*************************************************-         55   98.2%                                                                                                                                                                                              Distribution of Estimated IOs for Sequential Bottom Level access using 1 Buffer                                                             ....+....20...+....40...+....60...+....80...+....                                                                                 2+|                                                            0    0.0%                                                             1 |+                                                           1    1.7%                                                             0 |*************************************************-         55   98.2%                                                                                                                                                                                              Distribution of Nr of pages with Intermediate Level SR8s                                                                                    ....+....20...+....40...+....60...+....80...+....                                                                                 1+|                                                            0    0.0%                                                             0 |**************************************************         56  100.0%                                                                                                                                                                                              Distribution of Minimum Nr of pages with Intermediate Level SR8s                                                                            ....+....20...+....40...+....60...+....80...+....                                                                                 1+|                                                            0    0.0%                                                             0 |**************************************************         56  100.0%                                                                                                                                                                                              Distribution of % Displaced Intermediate Level SR8s                                                                                         ....+....20...+....40...+....60...+....80...+....                                                                                 1+|                                                            0    0.0%                                                             0 |**************************************************         56  100.0%                                                                                                                                                                                              Distribution of Nr of pages with Bottom Level SR8s                                                                                          ....+....20...+....40...+....60...+....80...+....                                                                                 2+|                                                            0    0.0%                                                             1 |+                                                           1    1.7%                                                             0 |*************************************************-         55   98.2%                                                                                                                                                                                              Distribution of Minimum Nr of pages with Bottom Level SR8s                                                                                  ....+....20...+....40...+....60...+....80...+....                                  2+|                                                            0    0.0%              1 |+                                                           1    1.7%              0 |*************************************************-         55   98.2%                                                                                                Distribution of % Displaced Bottom Level SR8s                                                ....+....20...+....40...+....60...+....80...+....                                  1+|                                                            0    0.0%              0 |**************************************************         56  100.0%          Status = 0        SQLSTATE = 00000                                                            
Printing a REFERENCING ROWID summary report of an index
The following report illustrates the use of the REFERENCING ROWID option to request the printing of the index occurrence containing the row ID of the referencing row identified by X’01390201’.
PRINT INDEX "COV-IDX-SET" SEGMENT VLDBSPG1                                                                                                 TABLE SQLSPG.COVERAGE                                                                                                                REFERENCING ROWID X'01390201' SUMMARY;                                                                                         SET Name: COV-IDX-SET                                                                                                                   IBC 70                               Displacement        0                                                                           Sort option NOT SORTED               Key length          N/A                                                                         Duplicates FIRST                     Compression         No                                                                       OWNER: SR7                                                                                                                              AREA VLDBSPG1.COVE-AREA              Low page  (SUB-     80106                                                                       Page size 4276                       High page  AREA)    80150                                                                       Page group 5                         Records per page    255                                                                      MEMBER: COVERAGE                        Set membership      Optional Automatic                                                          Located VIA index No                 Index is            Linked                                                                      AREA VLDBSPG1.COVE-AREA              Low page  (SUB-     80106                                                                       Page size 4276                       High page  AREA)    80150                                                                       Page group 5                         Records per page    255                                                                                                                                                                                                           OWNER X'01390201' on page 80130                                                                                                         Top level SR8  on page 80130         utilization      5.7%                                                                        Index occurrence totals                                                                                                                 Nr of members                                74                                                                                      Nr of levels                                  1                 1 Minimum                                                            Size of largest SR8                         316                                                                                      Nr of SR8s                                    2                 2 Minimum                                                            Nr of pages with SR8s                         1                 1 Minimum                                                            Nr of displaced SR8s                          0       0.0%                                                                           Nr of entries in use                         74      52.8%                                                                           Nr of Orphans                                 0       0.0%                                                                           Total size of all SR8s                      368                                                                                                                                                                                                                        Nr of Buffers versus Estimated IOs for Sequential Bottom Level access                                                                -------------        -------------                                                                                                        1  - 20                     1                                                                                                   Status = 0        SQLSTATE = 00000                                                                                                                                                                                                                                                    
Report Output Description
  • Part 1 -- Header
    The report header provides general information on the index definition, the index owner record or SQL table, and the index member record or SQL table.
  • Part 2 -- Details for each index occurrence
    A detailed report on index run-time data per index occurrence is always output for a system-owned index. For a user-owned index, it is output only when explicitly requested using SUMMARY DETAILED. The report provides the following:
    • The DBKEY of the index owner record occurrence and its page number.
    • The page number of the first (top level) SR8. Ideally, the top level SR8 should reside on the same page as the index owner, except for an index with only one level and a non-zero index displacement.
    • The number of entries that are used in the top level SR8 is expressed as a percentage utilization of the maximum IBC count assigned to the index.
    • At the intermediate and bottom level (output only if the index occurrence has more than 1 level):
      • Number of SR8's and its computed minimum value
      • Number of pages with SR8's and its computed minimum value
      • Number of displaced SR8's and as a percentage of SR8's
      • Number of entries in use and as a percentage of available entries
      • Number of orphans and as a percentage of used entries
      • Total size of all SR8's
    • Index occurrence totals:
      • Number of levels in the index and its computed minimum value
      • Number of members in the index
      • Size of the largest SR8
      • Number of SR8's and its computed minimum value
      • Number of pages with SR8's and its computed minimum value
      • Number of displaced SR8's and as a percentage of SR8's
      • Number of entries in use and as a percentage of available entries
      • Number of orphans and as a percentage of used entries
      • Total size of all SR8's
    • Estimated IO's versus number of database buffers for sequential bottom level access indicates the physical "sequentiality" of the index. Ideally, the number of I/O's should not vary with the number of buffers and should be equal to the number of pages with bottom level SR8's.
    A displaced SR8 is a bottom level SR8 located within the index displacement or a non-bottom level SR8 located outside the index displacement.
    A computed minimum value is obtained by using the current number of entries in the index, filling SR8's to 100% using the current value of INDEX BLOCK CONTAINS for the index, and assuming that all space on a database page is available to hold the index owner and the associated SR8's.
  • Part 3 -- Index overview and distribution diagrams for a user-owned index
    • Index overview
      An index overview provides the following information:
      • Number of owner occurrences
      • Number of empty owners and as a percentage of owner occurrences
      • Number of displaced (not on same page as owner) top level SR8's
      • Total, average, and highest value of the number of SR8's
      • Total, average, and highest value of the computed minimum number of SR8's
      • Average and highest value of the index level
      • Average and highest value of the computed minimum level
      • Average and highest values of the computed minimum number of pages
      • Number of index occurrences with orphans
      • Number of orphans: total and as a percentage of the number of entries and highest plus its owner DBKey
      • Total size of all SR8's
      • Size of largest SR8
    • Distribution diagrams
      A distribution diagram provides the number and percentage of index occurrences for a certain property in both a numeric and a pseudo-graphical way. Properties for which a distribution diagram is output are:
      • Index level
      • Minimum index level
      • Number of SR8's
      • Number of members in the index occurrence
      • Estimated IOs using 1 buffer for sequential bottom level access
      • Number of pages with intermediate level SR8's
      • Minimum number of pages with intermediate level SR8's
      • Percentage displaced intermediate level SR8's
      • Number of pages with bottom level SR8's
      • Minimum number of pages with bottom level SR8's
      • Percentage displaced bottom level SR8's
More Information