APAR status
Closed as canceled.
Error description
This APAR documents changes to the DB2 Administration Guide Volume 3 SC26488800 which did not make Version 3 GA pubs. Continuation of II07837, II08214 and II08289. 5740xyr00 R310 DB2 =============================================================== Version 3 Book Title: DB2 Administration Guide, vol. 3 Pages: p.7-109 Change Description: Index entries for PCLOSEN and PCLOSET have been added to to the Version 3 DB2 Administration Guide, Vol. 3. You should have been able to find 'PCLOSEN' or 'PCLOSET' with a fuzzy search. If you do an exact search the entries are located. This is an error and will be reported to Bookmanager. ================================================================ Version 3 Book Title: DB2 Administration Guide, Volume 3 Pages: 7-111 Change Description: "Query 2" on this page is in error and will not execute. Change it to read as follows: SELECT CLOSERULE, COUNT(*) FROM SYSIBM.SYSINDEXES T1, SYSIBM.SYSINDEXPART T2 WHERE T1.NAME = T2.IXNAME AND T1.CREATOR = T2.IXCREATOR AND T2.PARTITION < 1 GROUP BY CLOSERULE; ================================================================ Version 3 Book Title: DB2 Administration Guide Pages: 7-224, 7-225 Change Description: The sentence on p.7-224 in the paragraph labeled "Example 1: Single Table Access" now states: 'If DB2 decides not to use parallel I/O operations for a step, ACCESS_DEGREE and ACCESS_PGROUP_ID are blank.' This is changed to: 'If DB2 decides not to use parallel I/O operations for a step, ACCESS_DEGREE and ACCESS_PGROUP_ID contain null values.' The sentence on p.7-225 in the paragraph labeled "Example 4: Hybrid Join" now states: '...therefore, JOIN-DEGREE and JOIN_PGROUP_ID are blank.' This is changed to: '...therefore, JOIN-DEGREE and JOIN_PGROUP_ID contain null values.' In addition, the tables on pages 7-224, and 7-225 are changed to show null values instead of blanks in the JOIN-DEGREE and JOIN_PGROUP_ID columns. ================================================================ Book Title: DB2 Administration Guide, Volume 3. Version: V3 Pages: 7-259 Change Description: Insert on page 7-259 before the section titled "Does Your Query Involve Column Functions?" "Does Your Query Involve Correlated Columns of the Same Table? - What is Column Correlation - Two columns of data, A and B of a single table, are correlated if the values in column A are dependent on the values in column B. The following is an excerpt from a large single table. Columns CITY and STATE are highly correlated, and columns DEPTNO and SEX are entirely independent. TABLE CREWINFO CITY STATE DEPTNO SEX EMPNO -------------------------------------------------- Fresno CA A345 F 27375 Fresno CA J123 M 12345 Fresno CA J123 F 93875 Fresno CA J123 F 52325 New York NY J123 M 19823 New York NY A345 M 15522 Miami FL B499 M 83825 Miami FL A345 F 35785 Los Angeles CA X987 M 12131 Los Angeles CA A345 M 38251 In this simple example, for every value of column CITY that equals 'FRESNO', there is only one value of column STATE that matches. -Impacts of Column Correlation- DB2 might not determine the best access path, table order, or join method when your query uses columns that are highly correlated. Column correlation can make the estimated cost of operations cheaper than they actually are. Column cor- relation affects both single table queries and join queries. Single Table Queries (Index Selection) There are two types of potential correlation problems on compound predicates at the index level: Best Matching Columns (see also p.7-213) Index Screening Columns (see also p.7-214) -Column correlation on the Best Matching Columns of an Index The following query selects rows with females in department A345 from Fresno, California. There are 2 indexes defined on the table, Index 1 (CITY,STATE) and Index 2 (DEPTNO,SEX). -Query 1- SELECT ... FROM CREWINFO WHERE CITY = 'FRESNO' AND STATE = 'CA' (PREDICATE1) AND DEPTNO = 'A345' AND SEX = 'F'; (PREDICATE2) Consider the two compound predicates (labeled PREDICATE1 and PREDICATE2), their actual filtering effects (the proportion of rows they select), and their DB2 filter factors. The filter factors are calculated as if the columns of the pre- dicate are entirely independent (not correlated). For more information on how DB2 deals with compound predicates, see p.7-229. INDEX 1 INDEX 2 Matching Predicates Predicate1 Predicate2 CITY=FRESNO DEPTNO=A345 & STATE=CA & SEX=F Matching Columns 2 2 DB2 estimate column=CITY, column=DEPTNO, for matching columns colcard=4 colcard=4, (FF=Filter Factor) FF=1/4 FF=1/4 column=STATE, column=SEX, colcard=3 colcard=2 FF=1/3 FF=1/2 Compound Filter Factor 1/4 x 1/3=.08 1/4 x 1/2=.125 for matching columns Qualified leaf pages .08 x 10=.8 .125 x 10=1.25 based on DB2 estimations INDEX CHOSEN (.8 < 1.25) Actual filter factor 4/10 2/10 based on data distribution Actual number of qualified 4/10 x 10=4 2/10 x 10=2 leaf pages based on BETTER INDEX compound predicate CHOICE (2 < 4) ------------------------------------------------------------ DB2 chooses as an access path the cheapest index, which is strongly influenced by the smallest filter factor of the matching columns. For purposes of illustration, assume that filter factor is the only influence on the access path. The combined filtering of columns CITY and STATE seems very good, whereas the matching columns for the second index do not seem to filter as much. Based on those calculations, DB2 chooses Index 1 as an access path for Query 1. The problem is that the filtering of columns CITY and STATE should not look good. Column STATE does almost no filter- ing. Since columns DEPTNO and SEX do a better job of fil- tering out rows, DB2 should favor Index 2 over Index 1. -Column Correlation on Index Screening Columns of an Index Correlation might also occur on non-matching index columns, used for index screening. See p.7-214 for more information. Index screening predicates help reduce the number of data rows that qualify while scanning the index. However, if the index screening predicates are correlated, they do not fil- ter as many data rows as their filter factors suggest. To illustrate this, use the same Query 1 (see above) with the following indexes on table CREWINFO: Index 3 (EMPNO,CITY,STATE) Index 4 (EMPNO,DEPTNO,SEX) In the case of Index 3, since the columns of Predicate1 are correlated, the index access is not improved as much as es- timated by the screening predicates and therefore Index 4 might be a better choice. (Note that index screening also occurs for indexes with matching columns greater than zero.) -Multiple Table Joins (Join Sequence, Join Method, Index Selection) In Query 2, an additional table is added to the original query (see Query 1 above) to show the impact of column cor- relation on join queries. TABLE DEPTINFO CITY STATE MANAGER DEPT DEPTNAME ---------------------------------------------------- FRESNO CA SMITH J123 ADMIN LOS ANGELES CA JONES A345 LEGAL - Query 2 SELECT ... FROM CREWINFO T1,DEPTINFO T2 WHERE T1.CITY = 'FRESNO' AND T1.STATE='CA' (PREDICATE 1) AND T1.DEPTNO = T2.DEPT AND T2.DEPTNAME = 'LEGAL'; The order that tables are accessed in a join statement affects performance. The estimated combined filtering of Predicate1 is lower than its actual filtering. So table CREWINFO might look better as the first table accessed than it should. Also, due to the smaller estimated size for table CREWINFO, a nested loop join might be chosen for the join method. But, if many rows are selected from table CREWINFO because Pre- dicate1 does not filter as many rows as estimated, then another join method might be better. For more information on detecting and compensating for column correlation, refer to page 7-265. " ================================================================ Book Title: DB2 Administration Guide, Volume 3. Version: V3 Pages: 7-260 Change Description: The following will be inserted on p. 7-260 following the section titled "Are your statistics Current?" "Problems with Column Correlation and Host Variables Be aware of column correlation problems that are further complicated by the use of host variables. When host variables are used in a query, the actual values are not known at bind time. So, column correlation cannot be detected when the plan or package is built. If you know the range of host variables used, it might be possible to anticipate inefficient queries by rewriting some of the queries, as explained above." ================================================================ Book Title: DB2 Administration Guide, Volume 3. Version: V3 Pages: 7-263 Change Description: The following will be inserted on p. 7-263 following the section titled "Using Optimize for N Rows": "Forcing Access Through a Particular Index for Single Table Queries DB2 might choose a less than optimal index access if for example data in two or more columns of an index is corre- lated. To tune your query to force DB2 to select a particu- lar index: 1. Add an ORDER BY clause, with the leading columns of the desired index as the leading columns of the ORDER BY clause. 2. Add the clause OPTIMIZE FOR 1 ROW. This will favor the desired index to be selected. Reducing the Number of Matching Columns Discourage the use of a poorer performing index by reducing the index's matching predicate on its leading column. Reducing the number of best matching columns for a statement has varying effects depending on how the predicate is altered. -Changing an Equal Predicate to a BETWEEN Predicate Use the original query on table CREWINFO, (Query 1 above) with Index 1 (CITY,STATE) and Index 2 (DEPTNO,SEX) but with the following change: - New Query 1 SELECT ... FROM CREWINFO WHERE CITY BETWEEN 'FRESNO' AND 'FRESNO' (MODIFIED PREDICATE) AND STATE = 'CA' AND DEPTNO = 'A345' AND SEX = 'F'; (PREDICATE2) The original Query 1 had a MATCHCOLS value of 2 because there were matching predicates on the two leading columns of the index. The new Query 1 has a MATCHCOLS value of 1 because of the BETWEEN predicate on the leading index column of Index 1. Index 2, which still has MATCHCOLS of 2, is now the optimal choice." ================================================================ Book Title: DB2 Administration Guide, Volume 3. Version: V3 Pages: 7-263 Change Description: title change "Changing a Stage 1 Predicate to a Stage 2 Predicate" ================================================================ Book Title: DB2 Administration Guide, Volume 3. Version: V3 Pages: 7-265 Change Description: title change The title on p. 7-265 following the section titled "Updating Catalog Statistics" will be changed to: "Detecting and Compensating for Column Correlation" ================================================================ Version 3 Book Title: DB2 Administration Guide, Volume 3 Pages: X-72, X-74 Change Description: On p. X-72, under "When Exits Are Taken," replace the first sentence by the following two sentences: 'A validation routine for a table is invoked when DB2 inserts or updates a row, including inserts made by the LOAD utility. The routine is invoked for most delete operations, but NOT for a mass delete of all the rows of a table made by a DELETE statement without a WHERE clause.' On p. X-74, under "Expected Output", replace the list of "Value" and "Meaning" with the following list: VALUE MEANING 0 Allow insert, update, or delete Nonzero Do not allow insert, update, or delete Immediately after that, replace the first sentence of the paragraph with this sentence: 'If the operation is not allowed, the routine might also leave a reason code in EXPLRC2.' ==============================================================
Local fix
Problem summary
Problem conclusion
Temporary fix
Comments
close for INTERNET viewing
APAR Information
APAR number
II08459
Reported component name
PB LIB INFO ITE
Reported component ID
INFOPBLIB
Reported release
001
Status
CLOSED CAN
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
1995-01-31
Closed date
1997-10-31
Last modified date
1997-10-31
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Applicable component levels
[{"Business Unit":{"code":null,"label":null},"Product":{"code":"SG19O","label":"APARs - MVS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"001","Edition":"","Line of Business":{"code":"","label":""}},{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"001","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
31 October 1997