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

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

                    CONTROL CARD(S)
                    .........1.........2.........3.........4.........5.........6.........7.........8
                    REPORT AREA=PXX,DUMPS=TRACE
 
                    FUNCTION=REPORT
                       AREA=PXX
                       DUMPS=TRACE

This page of the report shows the following:

• The command exactly as entered.
• An analysis of keywords encountered and expected. Any errors found are flagged with a note in the left margin.
• Any messages related to syntax processing.

*** CMD= SELFR DBID= 001   TABLE= POL USER= $$$  $ID.CBSTS001(001) 000102    DATE= 1062003 TIME= 145837  
*********** REQUEST SPECIFICATION **************
DATAVIEW ELEMENT: PO    OFFSET IN RECORD: 00000 LENGTH: 00005
DATAVIEW ELEMENT: LI    OFFSET IN RECORD: 00005 LENGTH: 00003
DATAVIEW ELEMENT: PN    OFFSET IN RECORD: 00008 LENGTH: 00003
DATAVIEW ELEMENT: QTY   OFFSET IN RECORD: 00011 LENGTH: 00003 
DATAVIEW ELEMENT: UCOST OFFSET IN RECORD: 00014 LENGTH: 00005
WHERE:   EQ Z N PO    0000 0005 V 0005 12345 F1F2F3F4F5
WHERE: A EQ C N PN    0000 0003 V 0003 XYZ E7E8E9
KEY DEF: POLI  ( 000B )          TYPE: NATIVE   ( D0 ) ROWS: 00002974
   FIELDS= 00000.005   ; 00005.003   ;
   CARD  = 000000010   ; 000000200
   STATS: 012C 0000 00CC 0000 00C8 0014 00C8 0014 00C8 0014 00C8 0014 00C8 0014
KEY DEF: PN    ( 000F )          TYPE: KEYINC=Y ( 40 ) ROWS: 00002974
   FIELDS= 00008.003   ;
   CARD  = 000000100   ;
   STATS: 012C 0000 00CC 0000 00C8 0014 00C8 0014 00C8 0014 00C8 0014 00C8 0014
PRI. KEY PN
        FROM= XYZ
    (IN HEX)= E7E8E9
          TO= XYZ
    (IN HEX)= E7E8E9
SEC. KEY POLI
        FROM= 12345
    (IN HEX)= F1F2F3F4F5000000
          TO= 12345
    (IN HEX)= F1F2F3F4F5FFFFFF
**************** OPTIMIZATION *******************
CBSOR REASONS: 1-5: NNNNN 6: D 7-9: YNY 10-17: YYNYNNYN 18-21: NNNN
TYPE OPTIMIZATION......: POPULATION DEPENDENT
INDEX BUFFER REFERENCES: 00000013
**************** SELECTION **********************
TYPE SELECTION.........: *INDIRECT*  DUE TO:
     O KEY INTERSECTION
     O KEY NOT AVAILABLE
**************** ORDERING ***********************
TEMPORARY INDEX NOT REQUIRED.
**************** RETRIEVAL **********************
DATA RECORDS ACCESSED DUE TO: SELECTION
***END OF SELFR CMD FOR SET  00000001
CBS SET NUMBER  00000001  BEING RELEASED
TOTAL NBR INDEX ENTRIES SCANNED 00000013
TOTAL NBR DATA AREA ROWS READ   00000001
TOTAL NBR ROWS ACCEPTED         00000001
***END-OF-SET***

This page of the report displays the following information:

Message at top of page

Request is made by CA Ideal program CBSTS001, version 001, system $ID, on line 000102.

REQUEST SPECIFICATION section

The section displays the following information:

DATAVIEW ELEMENT:

The program requests a DATAVIEW with elements PO, LI, PN, QTY and UCOST for table POL. 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 PO=12345 and PN=XYZ are requested. For an explanation of the remaining fields, see the CA Datacom/DB Database and System Administration Guide.

KEY DEF:

Displays information for the keys defined in table POL. This group of fields is repeated for each key.

Keys POLI and PN are followed by the key ID (000B) and (000F).

TYPE:

For the POLI key, the type is NATIVE. For the PN 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:

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=

Field PO starts at offset zero in the record for a length of 5. The second field of the POLI key follows the semicolon for an offset of 5 and a length of 3. Field PN references the first and only field of key PN which is at offset 8 in the record for a length of 3.

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:

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 character and hexadecimal. In this example: the optimizer selected key PN as primary traversal key with the range of key character values xyz to xyz and hexadecimal values E7E8E9 to E7E8E9.

SEC. KEY

Displays the name of the secondary key used in the search and the range of key values in both character and hexadecimal. In this example: it also selected key POLI as a secondary traversal key with the range of key character values 12345 to 12345 and hexadecimal values F1F2F3F4F5000000 to F1F2F3F4F5FFFFFF.

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:

The choice of which key to use is POPULATION DEPENDENT.

INDEX BUFFER REFERENCES:

Indicates the type of optimization.

This example requires 13 index buffer references to determine that the previously shown key PN is best.

SELECTION section

The section displays the following information at TYPE SELECTION: Selection is INDIRECT because no single key can be used to directly locate qualifying rows (KEY NOT AVAILABLE), even though KEY INTERSECTION is used.

ORDERING section

The section displays information about use of a temporary index.

TEMPORARY INDEX NOT REQUIRED. For an explanation of the reasons that may be listed if a temporary index is required, see the CA Datacom/DB Database and System Administration Guide.

RETRIEVAL section

The section displays the following information:

DATA RECORDS ACCESSED DUE TO: SELECTION

Indicates rows were retrieved due to SELECTION criteria.

***END OF SELFR CMD FOR SET 00000001

SELFR processing has completed for the designated set.

CBS SET NUMBER 00000001 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

Number of data area rows read in order to satisfy the SELxx requests for set.

TOTAL NBR ROWS ACCEPTED

Number of rows returned to requestor.

***END-OF-SET***

No more diagnostics for this set.

If this is a frequent request, defining a key containing both PO and PN would eliminate statistical population counting and index scanning since TYPE SELECTION would then be DIRECT.