Date: mm/dd/ccyy ******************************************************************************** Page: 1 * CA Datacom/DB * Time: hh.mm.ss * General Utility * Release: 12 * Copyright © 2009 CA. All rights reserved. * SPnn ******************************************************************************** CONTROL CARD(S) .........1.........2.........3.........4.........5.........6.........7.........8 Œ REPORT AREA=PXX,DUMPS=TRACE FUNCTION=REPORT AREA=PXX DUMPS=TRACE Ž
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 are presented in this area.
Date: mm/dd/ccyy ******************************************************************************** Page: 2 * CA Datacom/DB * Time: hh.mm.ss * General Utility * Release: 12 * Copyright © 2009 CA. All rights reserved. * SPnn ******************************************************************************** *** 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***
Example 1
Request is made by CA Ideal program CBSTS001, version 001, system $ID, on line 000102.
The REQUEST SPECIFICATION section displays the following information:
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.
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.
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).
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.
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.
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.
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.
Statistical population counting for internal use only.
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.
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.
Date: mm/dd/ccyy ******************************************************************************** Page: 3 * CA Datacom/DB * Time: hh.mm.ss * General Utility * Release: 12 * Copyright © 2009 CA. All rights reserved. * SPnn ******************************************************************************** *** 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***
The OPTIMIZATION section displays the following information:
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.
The choice of which key to use is POPULATION DEPENDENT.
Indicates the type of optimization.
This example requires 13 index buffer references to determine that the previously shown key PN is best.
The SELECTION 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.
The ORDERING 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.
The RETRIEVAL section displays the following information:
Indicates rows were retrieved due to SELECTION criteria.
SELFR processing has completed for the designated set.
An explicit (SELPR) or implicit request to release the set was processed.
Number of index entries read in order to satisfy the SELxx requests for set.
Number of data area rows read in order to satisfy the SELxx requests for set.
Number of rows returned to requestor.
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.
Date: mm/dd/ccyy ******************************************************************************** Page: 4 * CA Datacom/DB * Time: hh.mm.ss * General Utility * Release: 12 * Copyright © 2009 CA. All rights reserved. * SPnn ******************************************************************************** ** 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***
Example 2
The request is made by CA Ideal program CBSTS005, Version 001, system $ID, on line 000102.
The REQUEST SPECIFICATION section displays the following information:
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.
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.
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.
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).
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.
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.
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.
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.
Statistical population counting for internal use only.
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.
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.
The presort space (in hexadecimal) is 00003C70.
Date: mm/dd/ccyy ******************************************************************************** Page: 5 * CA Datacom/DB * Time: hh.mm.ss * General Utility * Release: 12 * Copyright © 2009 CA. All rights reserved. * SPnn ******************************************************************************** *** 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***
The OPTIMIZATION section displays the following information:
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.
Optimization is POPULATION INDEPENDENT, so key EMPNO is selected as the primary traversal key without statistical population counting.
Index buffer references are zero.
The SELECTION section displays the following information:
Selection is DIRECT.
The ORDERING section contains the following information:
The ordering criteria, because a signed numeric field is specified.
200 rows meet the selection criteria.
This requires 609 index and data buffer references to build the temporary index.
The RETRIEVAL section displays the following information:
Data records must be re-accessed during retrieval from the temporary index because element EMDTA is not completely contained in the temporary index key.
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.
SELFR processing has completed for the designated set.
An explicit (SELPR) or implicit request to release the set was processed.
Number of index entries read in order to satisfy the SELxx requests for set.
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.
200 rows were returned to requestor.
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.
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|