Sample Output

Utility Statements › PRINT INDEX › Sample Output

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