Previous Topic: Sample Report: REPORT PXX - CBS Facility Diagnostic Report – Example 1Next Topic: Statistical Population Counting Report

REPORT (Generate Reports) › Statistics and Diagnostics Area (PXX) Report › Sample Report: REPORT PXX - CBS Facility Diagnostic Report – Example 2

Sample Report: REPORT PXX - CBS Facility Diagnostic Report – Example 2

 ** CMD= SELFR DBID= 001   TABLE= PMF USER= $ID1 $ID.CBSTS005(001) 000102    DATE= 1062003 TIME= 105417   
*********** REQUEST SPECIFICATION **************
DATAVIEW ELEMENT: EMDTA OFFSET IN RECORD: 00000 LENGTH: 00080      
WHERE:   LT Z N EMDTA 0000 0005 V 0005 00375 F0F0F3F7F5
ORDER-BY: D E EMDTA 0075 0005 P Y
KEY DEF: EMPNO ( 0001 )          TYPE: NATIVE   ( D0 ) ROWS: 00005333
   FIELDS= 00000.005   ;
   CARD  = 000000200   ;
   STATS: 006E 0015 005C 000C 00C8 0014 00C8 0014 00C8 0014 00C8 0014 00C8 0014
KEY DEF: STZIP ( 0002 )          TYPE: KEYINC=Y ( 40 ) ROWS: 00005333
   FIELDS= 00068.002   ; 00070.005   ;
   CARD  = 000000060   ; 000000200
   STATS: 012C 0000 00CC 0000 00C8 0014 00C8 0014 00C8 0014 00C8 0014 00C8 0014
PRI. KEY EMPNO
        FROM=
    (IN HEX)= 0000000000
          TO= 00374
    (IN HEX)= F0F0F3F7F4
INDX WRK TBL SIZE (IN HEX)............... 00003C70
**************** OPTIMIZATION *******************
CBSOR REASONS: 1-5: NYYNN 6: I 7-9: NYN 10-17: NYNNNNNN 18-21: YNYN
TYPE OPTIMIZATION......: POPULATION INDEPENDENT
INDEX BUFFER REFERENCES: 00000000
**************** SELECTION **********************
TYPE SELECTION.........: *DIRECT*
**************** ORDERING ***********************
TEMPORARY INDEX REQUIRED DUE TO:
     O ORDER-BY FIELDS
     O SIGNED NUMERIC FIELDS
ENTRIES INDEXED........: 00000200
LOGIO..................: 00000609
**************** RETRIEVAL **********************
RETRIEVAL FROM TEMP INDEX NOT USED DUE TO DATA NOT AVAIL.
DATA RECORDS ACCESSED DUE TO: RETRIEVAL
***END OF SELFR CMD FOR SET  00000007
CBS SET NUMBER  00000007  BEING RELEASED
TOTAL NBR INDEX ENTRIES SCANNED 00000400
TOTAL NBR DATA AREA ROWS READ   00000400
TOTAL NBR ROWS ACCEPTED         00000200
***END-OF-SET***

This page of the report shows the following:

Message at top of report

The request is made by CA Ideal program CBSTS005, Version 001, system $ID, on line 000102.

REQUEST SPECIFICATION section

The section displays the following information:

DATAVIEW ELEMENT:

Displays the program requests a DATAVIEW with element EMDTA for table PMF. The element name is followed by its offset in the record (not including RCE) and its length. The first element is at offset 0.

WHERE:

Displays the contents of the Selection Predicate from the Request Qualification Area. In this example, rows where the first 5 bytes of element EMDTA, an unsigned zone-decimal field, is LT 00375. For an explanation of the remaining fields, see the CA Datacom/DB Database and System Administration Guide.

ORDER-BY:

Displays the rows are to be returned ordered by the signed packed-decimal field starting at offset 75 of element EMDTA for a length of 5 bytes. For an explanation of the way the fields are displayed, see the CA Datacom/DB Database and System Administration Guide.

KEY DEF:

This row displays information about the keys defined for table PMF. This group of fields is repeated for each key traversed. Keys EMPNO and STZIP are followed by the key IDs (0001) and (0002).

TYPE:

For the EMPNO key, the type is NATIVE. For the STZIP key, the type is KEYINC=Y. Codes representing the key attributes, which are for internal use, are printed in parentheses after the key type.

ROWS:

Displays the number of rows indexed by this key. The number is initialized by the LOAD or RETIX function and is maintained by adds and deletes to the table.

FIELDS=

The only field of the key EMPNO starts at offset zero in the record for a length of 005. The first field in the STZIP key starts at offset 68 for a length of 2 and the second field in the key starts at offset 70 for a length of 5.

CARD =

Displays the cardinalities for the columns in the index. For example, if the key contains the branch and dept columns and the cardinalities displayed are 10 and 50, this means there are 10 branches and 50 combinations of branches and departments.

Cardinality is important in understanding the selection efficiency of an index. To continue this example, if there are 1,000 rows in the table and the values are evenly distributed, the search condition "WHERE BRANCH = x" would search 1,000 divided by the cardinality of 10 which is 100 rows. If "AND DEPT = y" is added, then only 1,000 divided by 50, or 20 rows, would be searched.

Cardinality is initialized by the LOAD or RETIX function. It is not updated by normal table maintenance requests. Therefore, optimum selection efficiency could require using RETIX to keep this statistic current.

STATS:

Displays the statistical population counting for internal use only.

PRI. KEY

Displays the name of the primary key used in the search and the range of key values in both characters and hexadecimals. In this example, the optimizer selected key EMPNO as the primary traversal key with the range of key character values zero to 00374 and hexadecimal values of 0000000000 to F0F0F3F7F4.

SEC. KEY

If there was a secondary key used in the search, its name would be displayed after the primary key and it would be followed by the range of key values in both characters and hexadecimals.

INDX WRK TBL SIZE (IN HEX)

The presort space (in hexadecimal) is 00003C70.

OPTIMIZATION section

The section displays the following information:

CBSOR REASONS:

See the CA Datacom/DB Database and System Administration Guide to interpret CBSOR values for 1-5, 6, 7-9, 10-17, and 18-21.

TYPE OPTIMIZATION:

Optimization is POPULATION INDEPENDENT, so key EMPNO is selected as the primary traversal key without statistical population counting.

INDEX BUFFER REFERENCES:

Index buffer references are zero.

SELECTION section

The section displays the following information:

TYPE SELECTION:

Selection is DIRECT.

ORDERING section

The section contains the following information:

TEMPORARY INDEX REQUIRED DUE TO:

The ordering criteria, because a signed numeric field is specified.

ENTRIES INDEXED:

Displays that 200 rows meet the selection criteria.

LOGIO:

This requires 609 index and data buffer references to build the temporary index.

RETRIEVAL section

The section displays the following information:

RETRIEVAL FROM TEMP INDEX NOT USED DUE TO DATA NOT AVAIL.

Data records must be re-accessed during retrieval from the temporary index because element EMDTA is not completely contained in the temporary index key.

DATA RECORDS ACCESSED DUE TO: RETRIEVAL

During record selection, data records were accessed because the sub-element of EMDTA (starting at byte 75 for a length of 5, needed to build a temporary index key) was not contained in the key EMPNO.

***END OF SELFR CMD FOR SET 00000007

SELFR processing has completed for the designated set.

CBS SET NUMBER 00000007 BEING RELEASED

An explicit (SELPR) or implicit request to release the set was processed.

TOTAL NBR INDEX ENTRIES SCANNED

Number of index entries read in order to satisfy the SELxx requests for set.

TOTAL NBR DATA AREA ROWS READ

Note that 400 data records are accessed since each row must be accessed once to build the temporary index, and once to be returned in the work area.

TOTAL NBR ROWS ACCEPTED

200 rows were returned to requestor.

***END-OF-SET***

No more diagnostics for this set.

Since different fields are used for selection and ordering (with selection not an "equal" condition), either a temporary index or index scanning is required. However, adding the order-by field as the last field of the traversal key would eliminate access to the data records while building the temporary index.