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-nameset-specifications ──────────────┬────────────► └─ SR8occurrence-key-1──report-option────┘ ►────┬────────────────────────────────┬──┬──────────────┬───────────────────►◄ ├─ ONLY ◄────────────────────────┤ ├─ DECIMAL ◄───┤ ├─ TREE ─────────────────────────┤ ├─ HEX ────────┤ ├─ FULL ─────────────────────────┤ └─ TERSE ──────┘ ├─ LEG ──────────────────────────┤ ├─ SUMMARY ───┬────────────┬─────┤ │ ├─ ONLY ◄────┤ │ │ └─ DETAILED ─┘ │ └─┬─ NEXT ──┬─┬────────────────┬─┘ ├─ PRIOR ─┤ └─level-number─┘ └─ LVL ───┘
Expansion of set-specifications
►►──┬── SEGMENTsegment-name──┬───────────────────────────────────────────────► └─ DBNAMEdb-name────────┘ ►─┬─ USING subschema-name ─┬───────────────────────────────┬────────────────┬─► │ ├─ OWNER ──┬─occurrence-key-2─┘ │ │ └─ MEMBER ─┘ │ │ │ └─ TABLEschema-name.table-id─┬─────────────────────────────────────────┬┘ ├─ REFERENCED ──┬ ROWIDoccurrence-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-nameSpecifies 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.
- SEGMENTSpecifies 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-nameSpecifies the name of the segment.
- DBNAMESpecifies the database containing the index structures to be reported.
- db-nameSpecifies the name of the database.
- USINGsubschema-nameSpecifies the name of the subschema in which the named indexed set is included.
- TABLEschema.table-idSpecifies the name of a table.
- REFERENCED ROWIDFor the named table, directs the PRINT INDEX utility to report on the index occurrence whose owner is the referenced row identified byoccurrence-key.
- REFERENCING ROWIDFor the named table, directs the PRINT INDEX utility to report on the index occurrence containing the row ID of the referencing row identified byoccurrence-key.
- X'hex-database-key'Specifies the hexadecimal database key of an owner or member record in the specified indexed set.
- page-numSpecifies the page number of an owner or member record in the specified indexed set.
- line-numSpecifies the line number of an owner or member record in the specified indexed set.
- SR8Identifies the index to be processed by specifying an index of an SR8 record in the index.
- page-groupIdentifies the page group of the SEGMENT where the index resides.
- X'database-key'Specifies the hexadecimal database key of the SR8 record.
- page-numSpecifies the page number of the SR8 record.
- line-numSpecifies the line number of the SR8 record.
- ONLYDirects 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.
- TREEDirects 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.
- FULLDirects 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.
- LEGDirects 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.
- NEXTDirects 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.
- PRIORDirects 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.
- LVLDirects the PRINT INDEX utility to report on all the SR8 records in a single level of the index.
- level-numberSpecifies 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.
- SUMMARYRequests 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 user-owned index always contains parts 1 and 3. Part 2 is included only in a detailed summary report.- ONLYRequests 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.
- DETAILEDRequests a summary report with parts 1, 2, and 3 for the target user-owned index. This parameter is ignored for a system-owned index.
- DECIMALDirects 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.
- HEXDirects 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.
- TERSEDirects 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 -- HeaderThe 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 occurrenceA 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 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 overviewAn 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 diagramsA 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
- For more information about designing indexes, see .
- For more information about defining and maintaining indexes, see .