Category: News from the Labs

Each month, SOFTWARE ENGINEERING sends out valuable technical DB2 z/OS tips and news based on knowledge gained by the experienced developers and technicians in our product labs. We would like to share this newsletter with other DB2 users, like yourself.

Posted on

2017-09 Db2 12 SQL Access path: Death by APAR

We recently held one of our Design Councils, this one was all about Db2 12 and going Agile. I was asked the following question after one of my presentations about verifying Db2 Code/Catalog/Function Levels:

“How many APARs really can affect access paths?”

I had to admit that I did not know the answer – I *hate* not knowing things, so I set off to find out how many Db2 Optimizer-relevant APARs there were in 2016 up until today (Oct 17th 2017).

 

Db2 12 APAR review sqlaccesspath

First I used this search argument, as I am only interested in Db2, sqlaccesspath keyword related APARs, those that have an Optimizer relevant CSECT, are HIPER and in Db2 12:

5740xyr00 AND sqlaccesspath AND dsnxo* AND yesHIPER AND C10
1.     PI69349: INCORROUT WITH SQLSTATE 01003 OCCURRED FOR A QUERY WITH LEFT OUTER JOIN AND ORDER BY AND ALSO RUNNING WITH ...
incorrout (wrong data) with SQLSTATE 01003 occurred for a query       2017-04-14

 

Remove the yesHIPER and you get 22:

1.     PI82797: LESS FILTERING INDEX SELECTED FOR INNER TABLE OF NESTED LOOP JOIN
A smaller, less filtering index is selected for the inner table with a Nested Loop Join.      2017-10-02
2.     PI78122: INEFFICIENT ACCESS PATH CHOSEN WHEN NO MATCHING INDEX EXISTS TO SATISFY THE JOIN PREDICATE.
An inefficient access path can be selected for a query with no matching index to cover the join predicate.   2017-10-02
3.     PI83289: QUERY ACCESS PATH MAY BE UNPREDICTABLE FOR A QUERY WHERE THERE ARE EQUAL PREDICATES COVERING ALL THE COLUMNS ...
Query access path may be unpredictable for a query where there are equal predicates covering all the columns of a unique index.        2017-09-02
4.     PI83454: INEFFICIENT ACCESS PATH IS USED BECAUSE OF AN INCORRECT COMPOUND FILTER FACTOR OF RANGE PREDICATES
The compound filter factor of two range predicates on the same column is underestimated, that causes an inefficient access      2017-09-02
5.     PI83547: NON-MATCHING INDEX CHOSEN WHEN A MATCHING INDEX EXISTS
A non-matching index could be chosen when an index with good matching exists.    2017-09-02
6.     PI82634: MULTI INDEX ACCESS CHOSEN WHEN A BETTER MATCHING INDEX EXISTS
MULTI-INDEX ACCESS WAS CHOSEN WHEN A MORE EFFICIENT INDEX USING BETTER MATCHING EXISTS.       2017-08-02
7.     PI77792: ABSTRACT:INEFFICIENT ACCESS PATH WITH SORT DISTINCT IN CTE
AN INEFFICIENT ACCESS PATH CAN BE CHOSEN WHEN A SORT FOR DISTINCT IS NEEDED INSIDE A CTE.     2017-06-02
8.     PI73290: Db2 FOR Z/OS USERS OF QUERIES WITH GROUP BY, DISTINCT, IN SUBQUERY OR NOT IN SUBQUERY.
Db2 may choose an inefficient access path as non-matching index access without matching predicates and screening predicates when     2017-05-01
9.     PI73368: ABSTRACT=INEFFICIENT ACCESS PATH WITH SINGLE VALUE PAGE RANGE PREDICATE.An inefficient access path can be chosen when page range access is used for a join predicate and the columns has a cardinality 2017-05-01
10.    PI75966: INEFFICIENT ACCESS PATH IS SELECTED.
List prefetch plan with higher cost may be chosen mistakenly when there is one table in the query block and the index covers        2017-05-01
11.    PI71368: R-SCAN ACCESS PATH CHOSEN OVER MULTI-INDEX ACCESS FOR A TABLE WHICH QUALIFIES FOR NPGTHRSH BEHAVIOR
R-scan access path chosen over multi-index access for a table which qualifies for NPGTHRSH behavior.        2017-04-20
12.    PI75963: ACCESS PATH FOR A QUERY WITH GROUP BY AND ORDER BY CLAUSES INCLUDES AN UNNECESSARY SORT
For the following query the access path includes a sort although it is not necessary: 2017-04-20
13.    PI69349: INCORROUT WITH SQLSTATE 01003 OCCURRED FOR A QUERY WITH LEFT OUTER JOIN AND ORDER BY AND ALSO RUNNING WITH ...
incorrout (wrong data) with SQLSTATE 01003 occurred for a query    2017-04-14
14.    PI74019: DEFECT 40316 - TPCD (REVISTIT) QUERY #UV1B2 CL2 CPU REGRESSION IN V12
There is a regression for performance test. In V11, it could choose good access path, in V12, it chooses bad access path.   2017-04-03
15.    PI72887: INEFFICIENT ACCESS PATH WITH EARLY OUT
INEFFICIENT ACCESS PATH WITH EARLY OUT 2017-04-03
16.    PI71495: ABEND04E RC00E70005 AT DSNXGRDS DSNXOB2 M105 ON SQL STATEMENT WITH CONCENTRATE STATEMENTS WITH LITERALS
ABEND04E rc00e70005 at dsnxgrds dsnxob2 M105 in prepare SQL statement with attributes clause CONCENTRATE STATEMENTS WITH       2017-02-01
17.    PI72800: INCORRECT FILTER FACTOR FOR PARTITION KEY OF VOLATILE TABLE
Db2 may calculate a incorrect filter factor for using BETWEEN and RANGE predicates, when a volatile table is defined as 2017-02-01
18.    PI71110: FORWARDFIT OF PI70237
Performance problem when a user query choose R-scan in a single-table correlated subquery.    2017-02-01
19.    PI68238: SQLACCESSPATH OF THE ENTRY WITH LATEST TIMESTAMP AND ACCESSTYPE NR IS NOT SELECTED WHEN BIND PKG WITH OPTHINT ...
When BIND PACKAGE with OPTHINT and EXPLAIN(YES), the latest timestamp NR path will lost the chance to compete with other 2017-01-12
20.    PI68551: FF OF PI66289-INDEX WITH LESS MATCHING COLUMNS IS CHOSEN FOR INNER TABLE WHICH MAY CAUSE BAD PERFORMANCE
Index with less matching columns is chosen for inner table which may cause bad performance    2017-01-12
21.    PI69414: POOR SQL PERFORMANCE FOR MERGE STATEMENT
Poor SQL performance for MERGE statement       2017-01-03
22.    PI68086: ALLOW MORE TABLES TO BE ELIGIBLE FOR SPARSE INDEX ACCESS.
Due to a code bug, sometimes a table with very big non-correlated subquery (big means expensive in terms of elapsed    2016-12-01

 

Remove the filter for optimizer csects and you get 26:

1.     PI82797: LESS FILTERING INDEX SELECTED FOR INNER TABLE OF NESTED LOOP JOIN
A smaller, less filtering index is selected for the inner table with a Nested Loop Join.      2017-10-02
2.     PI78122: INEFFICIENT ACCESS PATH CHOSEN WHEN NO MATCHING INDEX EXISTS TO SATISFY THE JOIN PREDICATE.
An inefficient access path can be selected for a query with no matching index to cover the join predicate.   2017-10-02
3.     PI83289: QUERY ACCESS PATH MAY BE UNPREDICTABLE FOR A QUERY WHERE THERE ARE EQUAL PREDICATES COVERING ALL THE COLUMNS ...
Query access path may be unpredictable for a query where there are equal predicates covering all the columns of a unique index.        2017-09-02
4.     PI83454: INEFFICIENT ACCESS PATH IS USED BECAUSE OF AN INCORRECT COMPOUND FILTER FACTOR OF RANGE PREDICATES
The compound filter factor of two range predicates on the same column is underestimated, that causes an inefficient access      2017-09-02
5.     PI83547: NON-MATCHING INDEX CHOSEN WHEN A MATCHING INDEX EXISTS
A non-matching index could be chosen when an index with good matching exists.    2017-09-02
6.     PI82634: MULTI INDEX ACCESS CHOSEN WHEN A BETTER MATCHING INDEX EXISTS
MULTI-INDEX ACCESS WAS CHOSEN WHEN A MORE EFFICIENT INDEX USING BETTER MATCHING EXISTS.       2017-08-02
7.     PI77792: ABSTRACT:INEFFICIENT ACCESS PATH WITH SORT DISTINCT IN CTE
AN INEFFICIENT ACCESS PATH CAN BE CHOSEN WHEN A SORT FOR DISTINCT IS NEEDED INSIDE A CTE.     2017-06-02
8.     PI73290: Db2 FOR Z/OS USERS OF QUERIES WITH GROUP BY, DISTINCT, IN SUBQUERY OR NOT IN SUBQUERY.
Db2 may choose an inefficient access path as non-matching index access without matching predicates and screening predicates when     2017-05-01
9.     PI75966: INEFFICIENT ACCESS PATH IS SELECTED.
List prefetch plan with higher cost may be chosen mistakenly when there is one table in the query block and the index covers        2017-05-01
10.    PI73368: ABSTRACT=INEFFICIENT ACCESS PATH WITH SINGLE VALUE PAGE RANGE PREDICATE.
An inefficient access path can be chosen when page range access is used for a join predicate and the columns has a cardinality 2017-05-01
11.    PI71368: R-SCAN ACCESS PATH CHOSEN OVER MULTI-INDEX ACCESS FOR A TABLE WHICH QUALIFIES FOR NPGTHRSH BEHAVIOR
R-scan access path chosen over multi-index access for a table which qualifies for NPGTHRSH behavior.        2017-04-20
12.    PI75963: ACCESS PATH FOR A QUERY WITH GROUP BY AND ORDER BY CLAUSES INCLUDES AN UNNECESSARY SORT
For the following query the access path includes a sort although it is not necessary: 2017-04-20
13.    PI67390: SQLCODE100 MAY OCCUR FOR SQL STATEMENTS USING LIST PREFETCH OR MULTI INDEX ACCESS
SQLCODE +100 may occur for sql statements using List Prefetch or multi index access    2017-04-14
14.    PI69349: INCORROUT WITH SQLSTATE 01003 OCCURRED FOR A QUERY WITH LEFT OUTER JOIN AND ORDER BY AND ALSO RUNNING WITH ...
incorrout (wrong data) with SQLSTATE 01003 occurred for a query    2017-04-14
15.    PI69054: TOTALENTRIES TRUNCATED IN RTS FOR LARGE LOAD RESULTS IN REBUILD INDEX FAILING
2.6 billion row table load is truncating totalentries in RTS.      2017-04-12
16.    PI74019: DEFECT 40316 - TPCD (REVISTIT) QUERY #UV1B2 CL2 CPU REGRESSION IN V12
There is a regression for performance test. In V11, it could choose good access path, in V12, it chooses bad access path.   2017-04-03
17.    PI76121: REMOTE CONNECTION ATTEMPT RESULTS IN NONMATCHING INDEX SCAN OF DSNFEX01 WHEN ROW FOR AUTHID IS MISSING FROM ...
Db2DDF See APAR PI71693 for Db2 11/10 for z/OS.       2017-04-03
18.    PI72887: INEFFICIENT ACCESS PATH WITH EARLY OUT
INEFFICIENT ACCESS PATH WITH EARLY OUT 2017-04-03
19.    PI71495: ABEND04E RC00E70005 AT DSNXGRDS DSNXOB2 M105 ON SQL STATEMENT WITH CONCENTRATE STATEMENTS WITH LITERALS
ABEND04E rc00e70005 at dsnxgrds dsnxob2 M105 in prepare SQL statement with attributes clause CONCENTRATE STATEMENTS WITH       2017-02-01
20.    PI72800: INCORRECT FILTER FACTOR FOR PARTITION KEY OF VOLATILE TABLE
Db2 may calculate a incorrect filter factor for using BETWEEN and RANGE predicates, when a volatile table is defined as 2017-02-01
21.    PI71110: FORWARDFIT OF PI70237
Performance problem when a user query choose R-scan in a single-table correlated subquery.    2017-02-01
22.    PI68238: SQLACCESSPATH OF THE ENTRY WITH LATEST TIMESTAMP AND ACCESSTYPE NR IS NOT SELECTED WHEN BIND PKG WITH OPTHINT ...
When BIND PACKAGE with OPTHINT and EXPLAIN(YES), the latest timestamp NR path will lost the chance to compete with other 2017-01-12
23.    PI68551: FF OF PI66289-INDEX WITH LESS MATCHING COLUMNS IS CHOSEN FOR INNER TABLE WHICH MAY CAUSE BAD PERFORMANCE
Index with less matching columns is chosen for inner table which may cause bad performance    2017-01-12
24.    PI69414: POOR SQL PERFORMANCE FOR MERGE STATEMENT
Poor SQL performance for MERGE statement       2017-01-03
25.    PI68086: ALLOW MORE TABLES TO BE ELIGIBLE FOR SPARSE INDEX ACCESS.
Due to a code bug, sometimes a table with very big non-correlated subquery (big means expensive in terms of elapsed    2016-12-01
26.    PI69029: REBIND APREUSESOURCE(PREVIOUS) IS NOT FINDING PREVIOUS ACCESSPATH WHEN USING PLANMGMT(BASIC)
When running this sample statement: REBIND PACKAGE(TEST.ABC00999.()) EXPLAIN(Y) -      2016-12-01

 

So we are talking about just over two per month…

 

Db2 11 APAR Review sqlaccesspath

Just drop the C10 and add B10 to the search and do it all again.

1.     PI78532: NON-MATCHING INDEX CHOSEN WHEN A MATCHING INDEX EXISTS 17/08/30 PTF PECHANGE
A non-matching index could be chosen when an index with good matching exists. 2017-08-30
2.     PI58411: INCORROUT CAN OCCUR FOR AN UPDATE QUERY USING TEMPORAL TABLES.
Temporal table has multiple indexes. One index to support the primary key constraint and one to support queries against the       2017-01-04
3.     PI62713: INCORROUT WITH SQLSTATE 01003 OCCURRED FOR A QUERY WITH LEFT OUTER JOIN AND ORDER BY AND ALSO RUNNING WITH ...
incorrout (wrong data) with SQLSTATE 01003 occurred for a query with LEFT OUTER JOIN and ORDER BY and also running with Sort     2016-11-02
4.     PI64779: SELECT DISTINCT RETURNS DUPLICATE VALUES.
Incorrect output can occur when the DISTINCT keyword is used, matching index access is used on the IN-list, but the IN-list   2016-10-03
5.     PI61893: INCORROUT WITH UPDATE STATEMENT AND CORRELATED SUBQUERY
A INCORROUT conditions occurs when a UPDATE statement is coded with a correlated subquery that contains a DISTINCT and FETCH     2016-08-02

 

Again, remove the yesHIPER and now there are 54:

1.     PI79438: INEFFICIENT ACCESS PATH FOR QUERY WITH MIN/MAX AND NO GROUP BY
An inefficient access path can be chosen when a query contains a MIN/MAX function with no Group BY.        2017-10-02
2.     PI84286: JOIN PREDICATE WITH CAST FUNCTION NON-INDEXABLE
A join predicate can become non-indexable if the predicate contains a decimal function without the length and scale    2017-10-02
3.     PI83769: AN INEFFICIENT INDEX CAN BE SELECTED TO PARTICIPATE IN MULTI-INDEX ACCESS
AN INEFFICIENT INDEX CAN BE SELECTED TO PARTICIPATE IN MULTI-INDEX ACCESS WHEN THERE IS A HIGH DEGREE OF UNCERTAINTY      2017-09-26
4.     PI82601: LESS FILTERING INDEX SELECTED FOR INNER TABLE OF NESTED LOOP JOIN
A smaller, less filtering index is selected for the inner table with a Nested Loop Join.      2017-09-02
5.     PI82772: INEFFICIENT ACCESS PATH IS USED BECAUSE OF AN INCORRECT COMPOUND FILTER FACTOR OF RANGE PREDICATES
The compound filter factor of two range predicates on the same column is underestimated, that causes an inefficient access      2017-09-02
6.     PI63607: INEFFICIENT ACCESS PATH COULD OCCUR WITH CARTESIAN JOIN
An inefficient access path could occur when the result of a Cartesian join does not produce better index matching on the      2017-08-30
7.     PI78532: NON-MATCHING INDEX CHOSEN WHEN A MATCHING INDEX EXISTS 17/08/30 PTF PECHANGE
A non-matching index could be chosen when an index with good matching exists.    2017-08-30
8.     PI79775: INDEX PROBING NOT BEING INVOKED
Under certain conditions, index probing is not being utilized for predicates that calculate a FF with no matching rows    2017-08-02
9.     PI80690: AE PI76369 FIX COMPLETION
AE PI76369 fix completion.        2017-07-05
10.    PI76369: ABSTRACT:INEFFICIENT ACCESS PATH WITH SORT DISTINCT IN CTE
AN INEFFICIENT ACCESS PATH CAN BE CHOSEN WHEN A SORT FOR DISTINCT IS NEEDED INSIDE A CTE.     2017-06-05
11.    PI75212: DURING AUTOBIND USING QUERYACCELERATION (ENABLE) BIND OPTION, STATIC QUERY IS BOUND FOR ACCELERATION ...
During AUTOBIND for a package bound QUERYACCELERATION(ENABLE), Db2 unexpectedly selects an IDAA access path and binds the query   2017-06-02
12.    PI59793: UNDERESTIMATED TOTAL INDEX FILTERING WITH PAGE RANGE SCREENING WHEN THE PAGE RANGE COLUMNS ARE NOT IN THE INDEX
An inaccurate IMFFADJ value will be generated in instances where when page range screening is performed and the columns used for      2017-05-03
13.    PI62376: INEFFICIENT ACCESS PATH CHOSEN WHEN NO MATCHING INDEX EXISTS TO SATISFY THE JOIN PREDICATE.
An inefficient access path can be selected for a query with no matching index to cover the join predicate.   2017-04-11
14.    PI75342: ACCESS PATH FOR A QUERY WITH GROUP BY AND ORDER BY CLAUSES INCLUDES AN UNNECESSARY SORT.
For the following query the access path includes a sort although it is not necessary: 2017-04-03
15.    PI72177: INEFFICIENT ACCESS PATH IS SELECTED.
List prefetch plan with higher cost may be chosen mistakenly when there is one table in the query block and the index covers        2017-04-03
16.    PI70394: ABSTRACT=INEFFICIENT ACCESS PATH WITH SINGLE VALUE PAGE RANGE PREDICATE.
An inefficient access path can be chosen when page range access is used for a join predicate and the columns has a cardinality 2017-03-09
17.    PI73338: INEFFICIENT JOIN ACCESS ON PARTITIONED TABLE
Nested loop join may be chosen as the join type when hybrid join would likely have been a better choice.      2017-02-01
18.    PI66289: INDEX WITH LESS MATCHING COLUMNS IS CHOSEN FOR INNER TABLE WHICH MAY CAUSE BAD PERFORMANCE
Index with less matching columns is chosen for inner table which may cause bad performance    2017-01-12
19.    PI65041: SQLACCESSPATH OF THE ENTRY WITH LATEST TIMESTAMP AND ACCESSTYPE NR IS NOT SELECTED WHEN BIND PKG WITH OPTHINT ...
When BIND PACKAGE with OPTHINT and EXPLAIN(YES), the latest timestamp NR path will lost the chance to compete with other 2017-01-12
20.    PI58411: INCORROUT CAN OCCUR FOR AN UPDATE QUERY USING TEMPORAL TABLES.
Temporal table has multiple indexes. One index to support the primary key constraint and one to support queries against the       2017-01-04
21.    PI69685: ABEND04E RC00E70005 AT DSNXGRDS DSNXOB2 M105 ON AN SQL STATEMENT WITH ATTRIBUTE CONCENTRATE STATEMENTS WITH ...
ABEND04E rc00e70005 at dsnxgrds dsnxob2 M105 in a prepared SQL statement with prepared attributes clause CONCENTRATE STATEMENTS     2017-01-03
22.    PI71365: R-SCAN ACCESS PATH CHOSEN OVER MULTI-INDEX ACCESS FOR A TABLE WHICH QUALIFIES FOR NPGTHRSH BEHAVIOR
R-scan access path chosen over multi-index access for a table which qualifies for NPGTHRSH behavior.        2017-01-03
23.    PI68896: INCORRECT FILTER FACTOR FOR PARTITION KEY OF VOLATILE TABLE
Db2 may calculate a incorrect filter factor for using BETWEEN and RANGE predicates, when a volatile table is defined as 2017-01-03
24.    PI71415: POOR SQL PERFORMANCE WHEN THE ACCESS PATH USES NON-MATCHING INDEX FOR INDEX SKIPPING
Poor SQL Performance when the access path uses non-matching index for index skipping . 2017-01-03
25.    PI63541: INEFFICIENT ACCESS PATH WITH EARLY OUT
Ineffecient access path can be chosen when the access plan qualifies for index skipping but no index covers the join     2017-01-03
26.    PI68380: SUBOPTIMAL ACCESS PATH WITH A NESTED LOOP JOIN AND MULTI-INDEX ACCESS
A suboptimal access path is chosen when multi-index access is chosen as the inner table for a nested loop join. A hybrid join   2016-12-01
27.    PI67499: INEFFICIENT NON-MATCHING INDEX SCAN IS SELECTED WHEN THERE ARE MORE THAN 1 TABLES IN THE QUERY BLOCK WITH ...
Inefficient non-matching index scan is selected when when the query contains DISTINCT in the query and there are more than 1 2016-12-01
28.    PI70237: INEFFICIENT ACCESS PATH WHEN A QUERY HAS INSUBQ OR EXISTS-SUBQ AND THE SUBQUERY HAS A SINGLE TABLE
Inefficient access path when a query has INSUBQUERY or EXISTS-SUBQUERY and the subquery has a single table. Optimizer    2016-12-01
29.    PI66248: INEFFICIENT ACCESS PATH WHEN NESTED LOOP JOIN USED TO ACCESS A INNER MATERIALIZED QUERY BLOCK
An inefficient rscan access path for the inner table of a nested loop join when the the inner table is a materialized workfile.       2016-12-01
30.    PI64874: INDEX PROBING MAY ACCESS UNQUALIFIED PARTITION AT PREPARE TIME
Unqualified partitions are claimed by index probing at prepare time, that causes unnecessary locking issue and group buffer     2016-11-02
31.    PI62713: INCORROUT WITH SQLSTATE 01003 OCCURRED FOR A QUERY WITH LEFT OUTER JOIN AND ORDER BY AND ALSO RUNNING WITH ...
incorrout (wrong data) with SQLSTATE 01003 occurred for a query with LEFT OUTER JOIN and ORDER BY and also running with Sort     2016-11-02
32.    PI52204: INCORRECT DPSI LEAF PAGE ESTIMATION WHEN ALL THE PARTITION KEYS CONTAINS LOCAL PREDICATES
Inefficient access path is selected due to incorrect DPSI leaf page estimation when all the partition keys contains local 2016-10-03
33.    PI64779: SELECT DISTINCT RETURNS DUPLICATE VALUES.
Incorrect output can occur when the DISTINCT keyword is used, matching index access is used on the IN-list, but the IN-list   2016-10-03
34.    PI64234: INEFFICIENT ACCESS PATH INVOLVING RANGE LIST PREDICATE(S)
A Range list access path could be chosen when a more efficient access path is available.      2016-10-03
35.    PI64089: PERFORMANCE IMPACT WITH CTE SELECTED FROM A VIEW
When a CTE is defined in a view, a sub-optimal access path may occur. A optimal access path will be estimated for the CTE, and        2016-10-03
36.    PI61435: INCORRECT FILTER FACTOR MAY BE ASSIGNED FOR PAGE RANGE SCREENING PREDICATES WHEN PARTITION KEYS ARE DEFINED AS ...
Incorrect filter factor may be assigned for page range screening predicates when partition keys are defined as VARCHAR 2016-10-03
37.    PI60761: Enable NPGTHRSH for qualified partitions of a partitioned table based on partition level NPAGES.
The code to enable NPGTHRSH for qualified partitions of a partitioned table based on partition level stats (NPAGES)      2016-09-02
38.    PI61886: IN SUBQUERY FAILS TO MATCH ON INDEXABLE IN LIST COLUMN
For a query which contains an IN list on a subquery, Db2 currently will use the IN list column in an index match if the 2016-09-02
39.    PI60056: INEFFICIENT ACCESS PATH WITH AGGREGATE FUNCTIONS SELECT(MAX) OR SELECT(MIN).
Less than optimal performance is the result when the optimizer makes a inefficient access path decision when the aggregate       2016-08-02
40.    PI60333: UNNECESSARY SORT FOR ORDER BY WHEN DISTINCT/GROUP BY LIST COVERS A UNIQUE INDEX AND SELECTED INDEX SUPPORT ...
When a query contains an ORDER BY and DISTINCT/GROUP BY clause whose columns cover a unique index, Db2 selects an index with 2016-08-02
41.    PI61893: INCORROUT WITH UPDATE STATEMENT AND CORRELATED SUBQUERY
A INCORROUT conditions occurs when a UPDATE statement is coded with a correlated subquery that contains a DISTINCT and FETCH     2016-08-02
42.    PI60206: POOR SQL PERFORMANCE OF A QUERY THAT HAS A CORRELATED SUBQUERY COST REDUCTION
Poor SQL performance of a query that has a correlated subquery cost reduction . 2016-08-02
43.    PI59348: INEFFICIENT INDEX SELECTION FOR THE INNER TABLE OF A JOIN
Less than optimal performance is the result when the optimizer makes a inefficient index selection for the inner table of a   2016-07-15
44.    PI57513: GROUP BY/DISTINCT/MIN/MAX CLAUSES RESULT IN INCORRECT ACCESS PATH DUE TO INVALID COST REDUCTION
In V11, materialized work files which are used as the inner tables for left joins are being processed using a nested loop 2016-07-04
45.    PI61155: INCORRECT COMPOUND FILTER FACTOR ESTIMATION FOR TWO RANGE PREDICATES
Inefficient index is selected due to incorrect compound filter factor estimation for two range predicates. 2016-07-04
46.    PI50999: INCORRECT MATCHING FILTER FACTOR ESTIMATION FOR DPSI WHEN THE JOIN PREDICATES INVOLVE PARTITION KEY
Inefficient access path is used in a join or correlated subquery because IMFF of DPSI is not correctly estimated when the join       2016-06-02
47.    PI58329: INCORRECT VALUE IN QW0022RX FIELD FOR IFCID022 WHEN UNDER REOPT(ONCE) OR REOPT(AUTO)
Incorrect value in QW0022RX field for IFCID022 when under REOPT ONCE OR REOPT AUTO .   2016-05-04
48.    PI53774: INEFFICIENT ACCESS PATH CHOSEN WHEN Db2 INCORRECTLY ESTIMATES THE FILTERING WHEN COMBINING TWO RANGE ...
Inefficient access path may be chosen when Db2 incorrectly estimates the filtering when combining two range predicates into      2016-04-05
49.    PI54988: OPTIMAL INDEX ACCESS MAY NOT BE USED WHEN STATISTICS IS NOT COLLECTED
Optimal index access may not be used when statistics is not collected.    2016-04-05
50.    PI44963: INCORRECT COST ESTIMATION FOR I1 INDEX SCAN
Cost estimation in DSN_STATEMNT_TABLE is very high for one-fetch index scan(I1). Prefetch method is 'D' in the   2016-03-10
51.    PI53790: INCORRECT ACCESS PATH CHOSEN FOR OPTIMIZE FOR 1 ROW
Db2 is producing an incorrect and inefficient access path in V11 when the OPTIMIZE FOR 1 ROW clause is added to a query which 2016-03-02
52.    PI50063: AN INACCURATE COMPOUND FILTER FACTOR MAY BE PRODUCED FOR A PREDICATE WITH AN OR CLAUSE
AN INACCURATE COMPOUND FILTER FACTOR MAY BE PRODUCED FOR A PREDICATE WITH AN OR CLAUSE 2016-02-01
53.    PI49507: DIFFERENT ACCESS PATH IS USED WHEN QUERY ON VIEW INSTEAD OF BASE TABLE.
A view is directly created on a base table. SQL statement on the view should be equivalent to the SQL on the base table. 2016-02-01
54.    PI49557: INEFFICIENT ACCESS PATH FOR QUERY WITH FUNCTION MIN OR MAX BY INCORRECT REDUCTION FOR EARLY OUT
Inefficient access path for query with function MIN or MAX by incorrect reduction for early out        2016-01-04

 

Remove the filter for optimizer csects and you get 56:

1.     PI79438: INEFFICIENT ACCESS PATH FOR QUERY WITH MIN/MAX AND NO GROUP BY
An inefficient access path can be chosen when a query contains a MIN/MAX function with no Group BY.        2017-10-02
2.     PI84286: JOIN PREDICATE WITH CAST FUNCTION NON-INDEXABLE
A join predicate can become non-indexable if the predicate contains a decimal function without the length and scale    2017-10-02
3.     PI83769: AN INEFFICIENT INDEX CAN BE SELECTED TO PARTICIPATE IN MULTI-INDEX ACCESS
AN INEFFICIENT INDEX CAN BE SELECTED TO PARTICIPATE IN MULTI-INDEX ACCESS WHEN THERE IS A HIGH DEGREE OF UNCERTAINTY      2017-09-26
4.     PI86573: BETTER MATCHING INDEX NOT CHOSEN WHEN COMPETING INDEXES CONTAIN A SUBSET OF THE SAME COLUMNS.
A better matching index can be overlooked when the competing indexes share a set of the same columns.        2017-09-21
5.     PI82772: INEFFICIENT ACCESS PATH IS USED BECAUSE OF AN INCORRECT COMPOUND FILTER FACTOR OF RANGE PREDICATES
The compound filter factor of two range predicates on the same column is underestimated, that causes an inefficient access      2017-09-02
6.     PI82601: LESS FILTERING INDEX SELECTED FOR INNER TABLE OF NESTED LOOP JOIN
A smaller, less filtering index is selected for the inner table with a Nested Loop Join.      2017-09-02
7.     PI78532: NON-MATCHING INDEX CHOSEN WHEN A MATCHING INDEX EXISTS 17/08/30 PTF PECHANGE
A non-matching index could be chosen when an index with good matching exists.    2017-08-30
8.     PI63607: INEFFICIENT ACCESS PATH COULD OCCUR WITH CARTESIAN JOIN
An inefficient access path could occur when the result of a Cartesian join does not produce better index matching on the      2017-08-30
9.     PI79775: INDEX PROBING NOT BEING INVOKED
Under certain conditions, index probing is not being utilized for predicates that calculate a FF with no matching rows    2017-08-02
10.    PI80690: AE PI76369 FIX COMPLETION
AE PI76369 fix completion.        2017-07-05
11.    PI76369: ABSTRACT:INEFFICIENT ACCESS PATH WITH SORT DISTINCT IN CTE
AN INEFFICIENT ACCESS PATH CAN BE CHOSEN WHEN A SORT FOR DISTINCT IS NEEDED INSIDE A CTE.     2017-06-05
12.    PI75212: DURING AUTOBIND USING QUERYACCELERATION (ENABLE) BIND OPTION, STATIC QUERY IS BOUND FOR ACCELERATION ...
During AUTOBIND for a package bound QUERYACCELERATION(ENABLE), Db2 unexpectedly selects an IDAA access path and binds the query   2017-06-02
13.    PI59793: UNDERESTIMATED TOTAL INDEX FILTERING WITH PAGE RANGE SCREENING WHEN THE PAGE RANGE COLUMNS ARE NOT IN THE INDEX
An inaccurate IMFFADJ value will be generated in instances where when page range screening is performed and the columns used for      2017-05-03
14.    PI62376: INEFFICIENT ACCESS PATH CHOSEN WHEN NO MATCHING INDEX EXISTS TO SATISFY THE JOIN PREDICATE.
An inefficient access path can be selected for a query with no matching index to cover the join predicate.   2017-04-11
15.    PI75342: ACCESS PATH FOR A QUERY WITH GROUP BY AND ORDER BY CLAUSES INCLUDES AN UNNECESSARY SORT.
For the following query the access path includes a sort although it is not necessary: 2017-04-03
16.    PI72177: INEFFICIENT ACCESS PATH IS SELECTED.
List prefetch plan with higher cost may be chosen mistakenly when there is one table in the query block and the index covers        2017-04-03
17.    PI70394: ABSTRACT=INEFFICIENT ACCESS PATH WITH SINGLE VALUE PAGE RANGE PREDICATE.
An inefficient access path can be chosen when page range access is used for a join predicate and the columns has a cardinality 2017-03-09
18.    PI73338: INEFFICIENT JOIN ACCESS ON PARTITIONED TABLE
Nested loop join may be chosen as the join type when hybrid join would likely have been a better choice.      2017-02-01
19.    PI66289: INDEX WITH LESS MATCHING COLUMNS IS CHOSEN FOR INNER TABLE WHICH MAY CAUSE BAD PERFORMANCE
Index with less matching columns is chosen for inner table which may cause bad performance    2017-01-12
20.    PI65041: SQLACCESSPATH OF THE ENTRY WITH LATEST TIMESTAMP AND ACCESSTYPE NR IS NOT SELECTED WHEN BIND PKG WITH OPTHINT ...
When BIND PACKAGE with OPTHINT and EXPLAIN(YES), the latest timestamp NR path will lost the chance to compete with other 2017-01-12
21.    PI58411: INCORROUT CAN OCCUR FOR AN UPDATE QUERY USING TEMPORAL TABLES.
Temporal table has multiple indexes. One index to support the primary key constraint and one to support queries against the       2017-01-04
22.    PI69685: ABEND04E RC00E70005 AT DSNXGRDS DSNXOB2 M105 ON AN SQL STATEMENT WITH ATTRIBUTE CONCENTRATE STATEMENTS WITH ...
ABEND04E rc00e70005 at dsnxgrds dsnxob2 M105 in a prepared SQL statement with prepared attributes clause CONCENTRATE STATEMENTS     2017-01-03
23.    PI71365: R-SCAN ACCESS PATH CHOSEN OVER MULTI-INDEX ACCESS FOR A TABLE WHICH QUALIFIES FOR NPGTHRSH BEHAVIOR
R-scan access path chosen over multi-index access for a table which qualifies for NPGTHRSH behavior.        2017-01-03
24.    PI68896: INCORRECT FILTER FACTOR FOR PARTITION KEY OF VOLATILE TABLE
Db2 may calculate a incorrect filter factor for using BETWEEN and RANGE predicates, when a volatile table is defined as 2017-01-03
25.    PI71415: POOR SQL PERFORMANCE WHEN THE ACCESS PATH USES NON-MATCHING INDEX FOR INDEX SKIPPING
Poor SQL Performance when the access path uses non-matching index for index skipping . 2017-01-03
26.    PI63541: INEFFICIENT ACCESS PATH WITH EARLY OUT
Ineffecient access path can be chosen when the access plan qualifies for index skipping but no index covers the join     2017-01-03
27.    PI68380: SUBOPTIMAL ACCESS PATH WITH A NESTED LOOP JOIN AND MULTI-INDEX ACCESS
A suboptimal access path is chosen when multi-index access is chosen as the inner table for a nested loop join. A hybrid join   2016-12-01
28.    PI67499: INEFFICIENT NON-MATCHING INDEX SCAN IS SELECTED WHEN THERE ARE MORE THAN 1 TABLES IN THE QUERY BLOCK WITH ...
Inefficient non-matching index scan is selected when when the query contains DISTINCT in the query and there are more than 1 2016-12-01
29.    PI70237: INEFFICIENT ACCESS PATH WHEN A QUERY HAS INSUBQ OR EXISTS-SUBQ AND THE SUBQUERY HAS A SINGLE TABLE
Inefficient access path when a query has INSUBQUERY or EXISTS-SUBQUERY and the subquery has a single table. Optimizer    2016-12-01
30.    PI66248: INEFFICIENT ACCESS PATH WHEN NESTED LOOP JOIN USED TO ACCESS A INNER MATERIALIZED QUERY BLOCK
An inefficient rscan access path for the inner table of a nested loop join when the the inner table is a materialized workfile.       2016-12-01
31.    PI64874: INDEX PROBING MAY ACCESS UNQUALIFIED PARTITION AT PREPARE TIME
Unqualified partitions are claimed by index probing at prepare time, that causes unnecessary locking issue and group buffer     2016-11-02
32.    PI62713: INCORROUT WITH SQLSTATE 01003 OCCURRED FOR A QUERY WITH LEFT OUTER JOIN AND ORDER BY AND ALSO RUNNING WITH ...
incorrout (wrong data) with SQLSTATE 01003 occurred for a query with LEFT OUTER JOIN and ORDER BY and also running with Sort     2016-11-02
33.    PI52204: INCORRECT DPSI LEAF PAGE ESTIMATION WHEN ALL THE PARTITION KEYS CONTAINS LOCAL PREDICATES
Inefficient access path is selected due to incorrect DPSI leaf page estimation when all the partition keys contains local 2016-10-03
34.    PI64234: INEFFICIENT ACCESS PATH INVOLVING RANGE LIST PREDICATE(S)
A Range list access path could be chosen when a more efficient access path is available.      2016-10-03
35.    PI64779: SELECT DISTINCT RETURNS DUPLICATE VALUES.
Incorrect output can occur when the DISTINCT keyword is used, matching index access is used on the IN-list, but the IN-list   2016-10-03
36.    PI64089: PERFORMANCE IMPACT WITH CTE SELECTED FROM A VIEW
When a CTE is defined in a view, a sub-optimal access path may occur. A optimal access path will be estimated for the CTE, and        2016-10-03
37.    PI61435: INCORRECT FILTER FACTOR MAY BE ASSIGNED FOR PAGE RANGE SCREENING PREDICATES WHEN PARTITION KEYS ARE DEFINED AS ...
Incorrect filter factor may be assigned for page range screening predicates when partition keys are defined as VARCHAR 2016-10-03
38.    PI60761: Enable NPGTHRSH for qualified partitions of a partitioned table based on partition level NPAGES.
The code to enable NPGTHRSH for qualified partitions of a partitioned table based on partition level stats (NPAGES)      2016-09-02
39.    PI61886: IN SUBQUERY FAILS TO MATCH ON INDEXABLE IN LIST COLUMN
For a query which contains an IN list on a subquery, Db2 currently will use the IN list column in an index match if the 2016-09-02
40.    PI60056: INEFFICIENT ACCESS PATH WITH AGGREGATE FUNCTIONS SELECT(MAX) OR SELECT(MIN).
Less than optimal performance is the result when the optimizer makes a inefficient access path decision when the aggregate       2016-08-02
41.    PI60333: UNNECESSARY SORT FOR ORDER BY WHEN DISTINCT/GROUP BY LIST COVERS A UNIQUE INDEX AND SELECTED INDEX SUPPORT ...
When a query contains an ORDER BY and DISTINCT/GROUP BY clause whose columns cover a unique index, Db2 selects an index with 2016-08-02
42.    PI61893: INCORROUT WITH UPDATE STATEMENT AND CORRELATED SUBQUERY
A INCORROUT conditions occurs when a UPDATE statement is coded with a correlated subquery that contains a DISTINCT and FETCH     2016-08-02
43.    PI60206: POOR SQL PERFORMANCE OF A QUERY THAT HAS A CORRELATED SUBQUERY COST REDUCTION
Poor SQL performance of a query that has a correlated subquery cost reduction . 2016-08-02
44.    PI59348: INEFFICIENT INDEX SELECTION FOR THE INNER TABLE OF A JOIN
Less than optimal performance is the result when the optimizer makes a inefficient index selection for the inner table of a   2016-07-15
45.    PI54868: PERFORMANCE DEGRADATION WITH PAGE RANGE FILTERING ON A JOIN PREDICATE AND NO LOCAL PREDICATES
Db2 is not estimating the correct index filtering when page range filtering is available on a join predicate and no local     2016-07-12
46.    PI57513: GROUP BY/DISTINCT/MIN/MAX CLAUSES RESULT IN INCORRECT ACCESS PATH DUE TO INVALID COST REDUCTION
In V11, materialized work files which are used as the inner tables for left joins are being processed using a nested loop 2016-07-04
47.    PI61155: INCORRECT COMPOUND FILTER FACTOR ESTIMATION FOR TWO RANGE PREDICATES
Inefficient index is selected due to incorrect compound filter factor estimation for two range predicates. 2016-07-04
48.    PI50999: INCORRECT MATCHING FILTER FACTOR ESTIMATION FOR DPSI WHEN THE JOIN PREDICATES INVOLVE PARTITION KEY
Inefficient access path is used in a join or correlated subquery because IMFF of DPSI is not correctly estimated when the join       2016-06-02
49.    PI58329: INCORRECT VALUE IN QW0022RX FIELD FOR IFCID022 WHEN UNDER REOPT(ONCE) OR REOPT(AUTO)
Incorrect value in QW0022RX field for IFCID022 when under REOPT ONCE OR REOPT AUTO .   2016-05-04
50.    PI53774: INEFFICIENT ACCESS PATH CHOSEN WHEN Db2 INCORRECTLY ESTIMATES THE FILTERING WHEN COMBINING TWO RANGE ...
Inefficient access path may be chosen when Db2 incorrectly estimates the filtering when combining two range predicates into      2016-04-05
51.    PI54988: OPTIMAL INDEX ACCESS MAY NOT BE USED WHEN STATISTICS IS NOT COLLECTED
Optimal index access may not be used when statistics is not collected.    2016-04-05
52.    PI44963: INCORRECT COST ESTIMATION FOR I1 INDEX SCAN
Cost estimation in DSN_STATEMNT_TABLE is very high for one-fetch index scan(I1). Prefetch method is 'D' in the   2016-03-10
53.    PI53790: INCORRECT ACCESS PATH CHOSEN FOR OPTIMIZE FOR 1 ROW
Db2 is producing an incorrect and inefficient access path in V11 when the OPTIMIZE FOR 1 ROW clause is added to a query which 2016-03-02
54.    PI50063: AN INACCURATE COMPOUND FILTER FACTOR MAY BE PRODUCED FOR A PREDICATE WITH AN OR CLAUSE
AN INACCURATE COMPOUND FILTER FACTOR MAY BE PRODUCED FOR A PREDICATE WITH AN OR CLAUSE 2016-02-01
55.    PI49507: DIFFERENT ACCESS PATH IS USED WHEN QUERY ON VIEW INSTEAD OF BASE TABLE.
A view is directly created on a base table. SQL statement on the view should be equivalent to the SQL on the base table. 2016-02-01
56.    PI49557: INEFFICIENT ACCESS PATH FOR QUERY WITH FUNCTION MIN OR MAX BY INCORRECT REDUCTION FOR EARLY OUT
Inefficient access path for query with function MIN or MAX by incorrect reduction for early out        2016-01-04

 

So we are still talking about just over two per month…

 

Db2 10 APAR Review sqlaccesspath

Just drop the B10 and add A10 to the search and do it all again.

1.     PI49116: TOTALENTRIES TRUNCATED IN RTS FOR LARGE LOAD RESULTS IN REBUILD INDEX FAILING
2.6 billion row table load is truncating totalentries in RTS.                                                         2016-12-01
2.     PI66401: POOR PERF FOR XMLTABLE FUNCTION WITH PREDICATE BEING PUSHED INSIDE XPATH PRODUCES LOOSE HIGH KEY VALUE FOR ...
The XMLTABLE function is producing a loose high key value for MSIKEYP2 when searching for a specific docid.           2016-12-01
3.     PI15740: INCORROUT DUPLICATED RECORDS RETURNED FOR QUERY WITH SQLACCESSPATH OF RANGELIST
The problem can happen when 1) range list access is used,                                                            2016-01-30

Again, remove the yesHIPER and now there are 16 APARs

 

1.     PI85418: PREPARE TAKES LONG TIME AND HIGH CPU IF THE QUERY CONTAINS MANY OR PREDICATES WHICH MAY QUALIFY RANGE LIST ...
A complex query contains many OR predicates that potentially qualifies range list access(ACCESSTYPE=NR), the prepare of the      2017-09-25
2.     PI76372: INEFFICIENT ACCESS PATH CHOSEN WHEN NO MATCHING INDEX EXISTS TO SATISFY THE JOIN PREDICATE.
An inefficient access path can be selected for a query with no matching index to cover the join predicate.   2017-05-01
3.     PI49116: TOTALENTRIES TRUNCATED IN RTS FOR LARGE LOAD RESULTS IN REBUILD INDEX FAILING
2.6 billion row table load is truncating totalentries in RTS.      2016-12-01
4.     PI66135: INEFFICIENT INDEX MAY BE SELECTED
An inefficient index may be chosen by the optimizer when certain levels of uncertainly exist on some predicates. 2016-12-01
5.     PI66401: POOR PERF FOR XMLTABLE FUNCTION WITH PREDICATE BEING PUSHED INSIDE XPATH PRODUCES LOOSE HIGH KEY VALUE FOR ...
The XMLTABLE function is producing a loose high key value for MSIKEYP2 when searching for a specific docid.       2016-12-01
6.     PI58274: Inefficient access path for a query with OPTIMIZE FOR 1 ROW or FETCH FIRST 1 ROW when an inner index matches ...
When the outer composite is guaranteed to be a single row and OPTIMIZE FOR 1 ROW or FETCH FIRST 1 ROW is specified, Db2 is not   2016-07-04
7.     PI59200: AN INDEX WITH LESS MATCHING COLUMN IS USED WHEN QUERY CONTAINS IN LIST PREDICATE
Optimal index may not be used for below query, SELECT * FROM TB1   2016-07-04
8.     PI39053: CLAIM ACQUIRE FOR SET STATEMENTS AGAINST SYSTSTAB RESULTS IN DSNT501I RC00C200EA DURING CATALOG REORG
Claim acquire for SET statements against SYSTSTAB results in DSNT501I RC00C200EA during Db2 CATALOG REORG 2016-05-19
9.     PI54892: DIFFERENT ACCESS PATH IS USED DUE TO A TIMING ISSUE IN OPTIMIZER
Different access path is used due to a timing issue in optimizer. 2016-05-04
10.    PI53633: PERFORMANCE IMPACT WHEN DSNZPARM MAXRBLK IS SET GREATER THAN ACCEPTABLE VALUE
When MAXRBLK is set to a value of 16777216 or higher, the run time evaluation will be incorrect with the threshold       2016-05-04
11.    PI57655: DIRECT ROW ACCESS IS DEFEATED WHEN THE QUERY HAS FFNR
Poor performance due to Direct Row Access through ROWID column being degraded to index/tablespace scan when the query has a 2016-05-04
12.    PI54978: MERGE STATEMENT USES DEFAULT TABLE CARDINALITY WHEN CARDINALITY IS PROVIDED VIA THE "FOR N ROWS" CLAUSE
The merge statement uses the default table cardinality of 10000 when the cardinality is provided via the "FOR n ROWS" clause.   2016-05-04
13.    PI56300: POOR BIND PERFORMANCE MAY OCCUR DUE TO UNNECESSARY ACCESS TO PLAN_TABLE WHEN SET STATEMENT IS APPLIED
Poor bind performance may occur due to unnecessary access to PLAN_TABLE when SET statement is applied.     2016-04-05
14.    PI49018: ACCESS PATH ENHANCEMENT FOR A QUERY CONTAINING A JOIN PREDICATE ON A TABLE WITH DEFAULT COLUMN CARDINALITY ...
Db2 recognizes cases in which an ordered outer table can provide benefit on access to the inner table. When the outer table is    2016-03-02
15.    PI53169: POOR SQL PERFORMANCE WHEN RANGE LIST ACCESS IS USED AND PTF UI22717 IS APPLIED 15/11/25 PTF PECHANGE
Poor SQL performance when range list access is used and PTF UI22717 is applied .       2016-03-02
16.    PI15740: INCORROUT DUPLICATED RECORDS RETURNED FOR QUERY WITH SQLACCESSPATH OF RANGELIST
The problem can happen when 1) range list access is used,   2016-01-30

 

Remove the filter for optimizer csects and you get 17:

1.     PI85418: PREPARE TAKES LONG TIME AND HIGH CPU IF THE QUERY CONTAINS MANY OR PREDICATES WHICH MAY QUALIFY RANGE LIST ...
A complex query contains many OR predicates that potentially qualifies range list access(ACCESSTYPE=NR), the prepare of the      2017-09-25
2.     PI76372: INEFFICIENT ACCESS PATH CHOSEN WHEN NO MATCHING INDEX EXISTS TO SATISFY THE JOIN PREDICATE.
An inefficient access path can be selected for a query with no matching index to cover the join predicate.   2017-05-01
3.     PI71693: REMOTE CONNECTION ATTEMPT RESULTS IN NONMATCHING INDEX SCAN OF DSNFEX01 WHEN ROW FOR AUTHID IS MISSING FROM ...
Db2DDF See APAR PI76121 for Db2 12 for z/OS.   2017-03-02
4.     PI49116: TOTALENTRIES TRUNCATED IN RTS FOR LARGE LOAD RESULTS IN REBUILD INDEX FAILING
2.6 billion row table load is truncating totalentries in RTS.      2016-12-01
5.     PI66135: INEFFICIENT INDEX MAY BE SELECTED
An inefficient index may be chosen by the optimizer when certain levels of uncertainly exist on some predicates. 2016-12-01
6.     PI66401: POOR PERF FOR XMLTABLE FUNCTION WITH PREDICATE BEING PUSHED INSIDE XPATH PRODUCES LOOSE HIGH KEY VALUE FOR ...
The XMLTABLE function is producing a loose high key value for MSIKEYP2 when searching for a specific docid.       2016-12-01
7.     PI58274: Inefficient access path for a query with OPTIMIZE FOR 1 ROW or FETCH FIRST 1 ROW when an inner index matches ...
When the outer composite is guaranteed to be a single row and OPTIMIZE FOR 1 ROW or FETCH FIRST 1 ROW is specified, Db2 is not   2016-07-04
8.     PI59200: AN INDEX WITH LESS MATCHING COLUMN IS USED WHEN QUERY CONTAINS IN LIST PREDICATE
Optimal index may not be used for below query, SELECT * FROM TB1   2016-07-04
9.     PI39053: CLAIM ACQUIRE FOR SET STATEMENTS AGAINST SYSTSTAB RESULTS IN DSNT501I RC00C200EA DURING CATALOG REORG
Claim acquire for SET statements against SYSTSTAB results in DSNT501I RC00C200EA during Db2 CATALOG REORG 2016-05-19
10.    PI54892: DIFFERENT ACCESS PATH IS USED DUE TO A TIMING ISSUE IN OPTIMIZER
Different access path is used due to a timing issue in optimizer. 2016-05-04
11.    PI53633: PERFORMANCE IMPACT WHEN DSNZPARM MAXRBLK IS SET GREATER THAN ACCEPTABLE VALUE
When MAXRBLK is set to a value of 16777216 or higher, the run time evaluation will be incorrect with the threshold       2016-05-04
12.    PI57655: DIRECT ROW ACCESS IS DEFEATED WHEN THE QUERY HAS FFNR
Poor performance due to Direct Row Access through ROWID column being degraded to index/tablespace scan when the query has a 2016-05-04
13.    PI54978: MERGE STATEMENT USES DEFAULT TABLE CARDINALITY WHEN CARDINALITY IS PROVIDED VIA THE "FOR N ROWS" CLAUSE
The merge statement uses the default table cardinality of 10000 when the cardinality is provided via the "FOR n ROWS" clause.   2016-05-04
14.    PI56300: POOR BIND PERFORMANCE MAY OCCUR DUE TO UNNECESSARY ACCESS TO PLAN_TABLE WHEN SET STATEMENT IS APPLIED
Poor bind performance may occur due to unnecessary access to PLAN_TABLE when SET statement is applied.     2016-04-05
15.    PI49018: ACCESS PATH ENHANCEMENT FOR A QUERY CONTAINING A JOIN PREDICATE ON A TABLE WITH DEFAULT COLUMN CARDINALITY ...
Db2 recognizes cases in which an ordered outer table can provide benefit on access to the inner table. When the outer table is    2016-03-02
16.    PI53169: POOR SQL PERFORMANCE WHEN RANGE LIST ACCESS IS USED AND PTF UI22717 IS APPLIED 15/11/25 PTF PECHANGE
Poor SQL performance when range list access is used and PTF UI22717 is applied .       2016-03-02
17.    PI15740: INCORROUT DUPLICATED RECORDS RETURNED FOR QUERY WITH SQLACCESSPATH OF RANGELIST
The problem can happen when 1) range list access is used,   2016-01-30

So we are still talking about just over one per month, which implies that Db2 10 was “more stable” in this area. To be fair, Db2 10 is a lot older (GA date 2010-10-22) than 11 and 12 so I am not surprised that after seven years the bug rate is dropping off!

What does all this mean?

What it means to me, is that just looking at sqlaccesspath shows a pretty high turnover rate that must be tested. There are HIPERs in here that could really hurt, and so applying maintenance, especially now in the Agile Db2 12 world, becomes even more critical to your business.

Are you ready for this?

More about : Db2 12 SQL access paths; Agile, SQL Codes/Catalog/Function Levels

See also our CDDC tool suite: Continuous Delivery – Deployment Check

CDDC Supports fully automated testing of the new Db2 agile delivery:

– BIF/ICI Detection: Checks incompatibilities on FUNCTION LEVEL
– Access Path PreCheck
– Creates quality environments from a production clone
– Capture the entire workload incl. DCL, DDL, commands…

 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

Roy Boxwell

 

 

Posted on

2017-08 Anomalies detected in MERGECOPY and MODIFY Recovery

Db2 z/OS Utilities: Identify and avoid some MERGECOPY or MODIFY Recovery anomalies

Just a little note this month about a strange anomaly in the way MERGECOPY and MODIFY RECOVERY seem to work. Let’s begin with MERGECOPY.

1 – MERGECOPY to the death

How many IICs to change a lightbulb?

I was doing some tests, to see how many Incremental Image Copies you can take before RECOVER dies, about 71 by the way, and found this out…

SCRATCHing my head

I did a Full Image Copy, then various updates, and three Incremental Image Copies. Due to bad luck, my Full Image Copy was “accidentally” scratched… Whoops!

MERGECOPY runs

I ran the MERGECOPY and look what happened:

DSNU000I    199 10:37:11.20 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DC10MC00MCU012
DSNU1044I   199 10:37:11.21 DSNUGTIS - PROCESSING SYSIN AS EBCDIC
DSNU050I    199 10:37:11.23 DSNUGUTC - MERGECOPY TABLESPACE R510D0DC.R510S81 NEWCOPY YES
DSNU463I    199 10:37:11.30 DSNUYBR3 - THE PRIMARY IMAGE COPY DATA SET SETEMP.R510D0DC.R510S81.P0000.D17195.T1734 WITH
DATE=170714 AND TIME=174140
            IS PARTICIPATING IN MERGECOPY.
DSNU463I    199 10:37:11.37 DSNUYBR3 - THE PRIMARY IMAGE COPY DATA SET SETEMP.R510D0DC.R510S81.P0000.D17198.T1025 WITH
DATE=170717 AND TIME=102429
            IS PARTICIPATING IN MERGECOPY.
DSNU463I    199 10:37:11.43 DSNUYBR3 - THE PRIMARY IMAGE COPY DATA SET SETEMP.R510D0DC.R510S81.P0000.D17198.T1235 WITH
DATE=170717 AND TIME=123501
            IS PARTICIPATING IN MERGECOPY.                                                                   
DSNU463I    199 10:37:11.48 DSNUYBR3 - THE PRIMARY IMAGE COPY DATA SET SETEMP.R510D0DC.R510S81.P0000.D17111.A10756 WITH
DATE=170421 AND TIME=075641
            IS PARTICIPATING IN MERGECOPY.
DSNU030I    199 10:37:11.50 DSNUYBR3 - UNABLE TO ALLOCATE SETEMP.R510D0DC.R510S81.P0000.D17111.A10756, RC=4,
CODE=X'17080002' 
DSNU454I    199 10:37:11.63 DSNUYBR0 - COPY MERGE COMPLETE 
            NUMBER OF COPIES=4        
            NUMBER OF COPIES MERGED=3   
            TOTAL NUMBER OF PAGES MERGED=363 
            ELAPSED TIME=00:00:00  
DSNU010I    199 10:37:11.67 DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4

Look again

Did you see what went wrong? No, nor did I the first time! Review again…

When a Return Code = 04 is actually important

Now do you see it?

DSNU030I    199 10:37:11.50 DSNUYBR3 - UNABLE TO ALLOCATE
SETEMP.R510D0DC.R510S81.P0000.D17111.A10756, RC=4,
CODE=X'17080002'

Hidden amongst all the other output…and “only” a RC=4 !

So? What does that have to do with me?

Well, who checks RC=04? Your data is non-recoverable, but you *think* you are green! Pretty nasty if you ask me! Db2 does get a bonus point though for actually merging the incrementals into another incremental…

2 – MODIFY Recovery

DSNUM ALL gone forever?

Remember the bad old days, when you *had* to take a TS level Image Copy of partitioned objects when you REORGed them?

Wasn’t that a terrible time? Terabytes of disk space pointlessly being filled with needless image copy data. Then along came Db2 11, which enabled TP level copies! Hoorah! Ok, not so hot with tapes etc. but who cares, it worked! Finally, we did not need to keep *huge* DSNUM ALL style copies lounging around on our expensive disks.

Too good to be true?

All sounds good, huh? What did we forget? I will tell you… NPSIs (The indexes formally known as NPIs) What is the problem, I hear you shout! Well, let me walk you through a normal scenario with COPY YES indexes…

Keep it Simple Stupid

Let us imagine a little tablespace with two partitions, one DPSI and two NPSIs. All of the indexes are COPY YES. Due to the fact that we now only do INLINE COPYs at the TP level, SYSCOPY gets lots of records looking like this:

SpacePartType
TP1F
IP1F
TP2F
IP2F
NPSI10F
NPSI20F
Then a day later
TP2F
IP2F
NPSI10F
NPSI20F
Then another day later
TP2F
IP2F
NPSI10F
NPSI20F

Remember that the NPSIs are copied with the DPSIs.

Time to DELETE the old data

Now, using MODIFY RECOVERY, you wish to rid yourself of the oldest data from Part 2.

A nice little

MODIFY RECOVERY TABLESPACE DB.TP DSNUM 2 RETAIN LAST(1)

Is enough. However, as it says in the documentation, this form does *not* delete NPSI data.

 

COPY PEND is not your friend!

If you use the DSNUM ALL, which *will* delete the NPSI data, then it will most probably put the entire table space into COPY PEND, which you do *not* want!

Space is the problem

So now the problem begins to appear… imagine that this has been happening for a year or more… you now have hundreds of syscopy entries *and* datasets for NPSI data that you cannot simply, or easily, MODIFY “away” anymore!

What I think is needed, is a new parameter, say “INCLUDE NPSI”, which will also get rid of NPSI data. RFE anyone??

I have no fix for this problem either, except to actually take a DSNUM 0 copy, including all indexes, and then do DSNUM ALL style MODIFY RECOVERY. Pretty messy…

 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

 

Roy Boxwell

Posted on

2017-07 Let’s make Db2 z/OS IMAGE COPY great again!

War story :

Why is Db2 z/OS IMAGE COPY very useful in the current Db2 z/OS world?

Please excuse the heading, I simply could not resist…

This month is another war story from the trenches of Production DBAs fighting the fight for 24×7 Shops around the world…
This time it was a request to recover some tables to a specific point in time that started the ball rolling…

Staged too soon? Bad updates and deletes

The developers pushed some software to production and then found out, five hours later, that there was a logical error and it was doing really bad updates and deletes in a bunch of tables that it should *not* have done. This is bad news…

Backup system to the Rescue?

This firm uses System Level Backup (SLB), but the DBA group, I kid you not, did not know this little factoid (they had alternate facts to works with), and so when the request to restore tables x, y and z to a specific Point in Time (PiT) arrived, they simply created the required RECOVER control cards and, after checking that the tables, indexes and tablespaces were not being used, submitted the RECOVER jobs. Now, the first job worked fine, but the second and the third “bought the farm” with *very* weird messages that the DBA group did not really understand:

RECOVER Messages you do not want to see – Part one

DSNU1520I   319 08:26:44.64 DSNUCBRT - THE RECOVERY BASE FOR TABLESPACE ROYDB.ROYTS DSNUM 1 IS THE SYSTEM LEVEL BACKUP WITH DATE = 20161212, TIME 041302, AND TOKEN X'FF..FF'

DSNU1522I   319 08:26:46.85 DSNUCBRT - THE DFSMSHSM CALL TO RESTORE TABLESPACE ROYDB.ROYTS DSNUM 1 FAILED WITH RC = X'0000005D' AND REASON CODE = X'00000042' SEE THE JOB LOG FOR DFSMSHSM MESSAGES INDICATING THE CAUSE OF THE ERROR

DSNU832I  )DSJP 319 08:26:44.61 DSNUCARS - INDEX ROYSCHEMA.ROYINDEX PARTITION 1 IS IN REBUILD PENDING STATE

DSNU560I  )DSJP 319 08:26:46.87 DSNUGSRX - TABLESPACE ROYDB.ROYTS PARTITION 1 IS IN RECOVER PENDING STATE

DSNU012I    319 08:26:46.88 DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8

(Some names have been changed to protect the innocent!)

Naturally I have added the red and bold highlights. Now this message *really* upset everyone…Why? The tablespaces were previously all RW and OK, but now they were COPY Pending status!

Go Figure!

So what just happened? Well Db2 detected that there was actually an SLB that could be used for the base of this recovery, and so “asked” HSM to get it “back”. It failed with Return Code (RC) X’5D’ decimal 93 and Reason Code X’42’ decimal 66. I love that 42! And with the great “tip” to “Read the Job Log”. The DBA group were a “tad” unhappy about the fact that a *failed* RECOVER set the tablespace in question to COPY Pending by the way!

Where on Earth?

So the DBAs started trying to read the Job Log and could not see anything. They have a 16-way data-sharing group running on 14 separate LPARs… There is *lots* of “job log”…

Eventually under one of 14 different STCs all called “HSM” I found this info:

RECOVER Messages you do not want to see – Part two

08.26.44 S0998158  ARC1801I FAST REPLICATION DATA SET RECOVERY IS STARTING FOR DATA SET QA1B.DSNDBC.ROYDB.ROYTS.J0001.A001, AT 08:26:44 ON 2016/12/12

08.26.46 S0998158  ARC0624I PHYSICAL DATA SET COPY OF VOLUME QA1B.DSNDBC.ROYDB.ROYTS.J0001.A001 TERMINATED PRIOR TO COMPLETION, DFSMSDSS FAILING RC = 8

08.26.46 S0998158  ARC1860I THE FOLLOWING 0001 DATA SET(S) FAILED DURING FAST REPLICATION DATA SET RECOVERY:QA1B.DSNDBC.ROYDB.ROYTS.J0001.A001, COPYPOOL=DSN$LOCDS0P$DB, DEVTYPE=DASD, VOLUME=WSPS95, ARC1166, RC=0

08.26.46 S0998158  ARC1802I FAST REPLICATION DATA SET RECOVERY HAS COMPLETED FOR DATA SET QA1B.DSNDBC.ROYDB.ROYTS.J0001.A001, AT 08:26:46 ON 2016/12/12, FUNCTION RC=0008, MAXIMUM DATA SET RC=0093

So here we see that it failed for RC=0093 (Now in decimal) but with a DFSMSDSS RC = 08

Confused? You will be…

I have not yet found what an RC = 0093 means – My best guess at the moment, is that the data had been “moved” since the SLB, and so the VOLUME swap failed due to some reason or other. The tablespace and index were unavailable and the “window of change” was closing fast…

Can I run backwards out the door?

Panic was approaching when I mentioned they could do the “backwards” LOGAPPLY (BACKOUT YES) and so with high hopes we attempted it, only to see that it sadly died a death if you have COPY Pending status – We felt pretty recursive at this point…

So that left us with one last chance and that was to tell RECOVER to ignore SLB and go directly to an earlier (RESTOREBEFORE) IC with a TORBA syntax.

Hoorah!

We had to wait awhiles but we got there! The RECOVERs all ran though clean and all was well… <phew>

Where’s the Beef?

So “What’s the point?” I hear you muttering… quite simple really:

1- Never rely on SLB to always work

2- Make sure you test it before switching your objects to COPY Pending by accident

3- Still take good old fashioned Image Copies

4- Use of BACKOUT YES can save your bacon *if* you know about it

As usual, if you have any comments or queries please feel free to drop me a line!

 

TTFN

Roy Boxwell

 

More about Utility Management and Space Management: See our RTDX suite of tools

 

Posted on

2017-06 APPLCOMPAT in Db2 12: a little bit more agile?

How to set Collection Ids for a reopt 2, 3 or 4 (Runtime optimization) and avoid the default collection NULLID ?

Following on from my last newsletter, I have had to do some research about Collection Ids and how to set them.

Last newsletter:
2017-05 Db2 version 12: A little bit too agile?
How to handle APPLCOMPAT when it comes to Dynamic SQL. A support to manage Db2 12 “agile” release.

Driven to distraction?

The first thing you learn, is that the cli.ini file is now gone…all of the data is now found in the db2dsdriver.cfg file. OK, how does this all hang together?

First Contact

The very first thing that happens, is it looks up the name and the address of the desired Db2 on z/OS. This is just the TCP/IP data to initiate “first contact”. Once the first contact is done, the User Id and Password are required to validate the connection. All well and good.

If your CurrentPackageSet and your reopt are *not* set, you then get the default collection NULLID under which all of your dynamic SQL executes

Reoptimize this!

Then it gets ugly… If your CurrentPackageSet and your reopt are *not* set, you then get the default collection NULLID under which all of your dynamic SQL executes (Now please re-read my last newsletter to see why that is of major interest!).

But, what happens if you want to use reopt 2, 3 or 4 ?

Quick aside:

The reopt level determines what style of run time optimization to enable on the host. You can set:

  • Reopt 2 – No optimization (and this is the default),
  • Reopt 3 – To get REOPT(ONCE) behavior – so the first time that SQL comes in to execute, it gets optimized with the literal values in it, and then not again, or
  • Reopt 4 – To get REOPT(ALWAYS) behavior, so it drives a re-optimization every time that SQL comes in.

I have NULL idea what you are talking about…

So back to my question…

What happens if you wish to use reopt 2, 3 or 4? Well, you have a problem, as these must use the reserved collection ids NULLID, NULLIDR1 and NULLIDRA respectively, which (remember my last newsletter), all die horribly with APPLCOMPAT getting involved…

It gets worse

Naturally, there is a dark lining to this cloud! The use of reopt actually disallows the use of CurrentPackageSet, which was the only way of managing APPLCOMPAT. What on Earth can you do…I have no idea…

Help is on the way…

We are conducting a Design Council in Germany  (September 4-5th, 2017) about IBMs Continuous Delivery for Db2 in September, where we will discuss all of this with customers and DB2 users, but I would love to get some feedback from you all too!

 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

 

Roy Boxwell

Posted on

2017-05 Db2 version 12: A little bit too agile?

 

How to handle APPLCOMPAT when it comes to Dynamic SQL?

I have been involved in testing in Db2 version 12 for a while now, and I think we need a discussion about a few features that come in with this “agile” release.

APPLCOMPAT to the rescue?

First up, is the use of APPLCOMPAT – This is now available throughout the SQL, but comes with a few problems. If you have static SQL then all is fine and dandy, as the APPLCOMPAT used is stored in the Db2 Catalog. You can easily refer to it and check which package is using what APPLCOMPAT. The real trouble starts with Dynamic SQL.

Dynamic SQL intro

A quick discourse about Dynamic SQL is now required…

All Dynamic SQL that runs, must run “under the control” of a Package.

This means that we all have loads of empty packages that are just used to run Dynamic SQL. You all have the SYSLHxxx, SYSLNxxx style Packages and probably loads more. These Packages are used for authentication, tracing, and validation of requests from remote users.

Package is the Boss

This is all good, apart from when a new Db2 12 Function Level (from now on FL) is activated. Why? Because the “package is boss” – If the package was bound at FL500 and you activate FL501, then any SQL that executes in that package that attempts to use an FL501 statement fails. If the SQL attempts to SET CURRENT APPLCOMPAT = ‘V12R1M501’ it will, of course, also fail.

REBIND the world?

To “fix” this, you simply have to REBIND the package to the new FL level.

Sounds simple, huh?

Well, what that means is that *all* SQL that uses that package will get the ability to go to FL501 straightaway, unless they are coded with SET CURRENT APPLCOMPAT = ‘V12R1M500’ or which level you would prefer…

We all have that coded in our JAVA programs don’t we?

Dynamic Packages always allocated

So the problem now is – You have hundreds of Packages that you must rebind, but you dare not rebind them! Even worse, is that you probably *cannot* rebind them anyway, as they are permanently allocated and in use!

. Imagine how many Dynamic SQLs are running in your shop?
. Can you flush the DSC and rebind all of your “empty” Packages?
. When can you plan such an outage?

COLLECTIONs can help

One work-around is to have a new COLLECTION, which the empty packages are bound to again. This works great, apart from one tiny little problem… The COLLID must then be set/changed in all of the CLI.INI or API places where it is currently set, or just defaulted to, today! At one of my customer sites that would be over 8,000 files to update!

So now my questions to the readers out there

How do you plan to manage this?

How do you plan to roll-out FL levels?

If using a new collection, how many CLI.INIs etc. must be changed?

 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

Roy Boxwell

Posted on

2017-04 Db2 ZPARM : Small ZPARM – Big effect!

Do you know how important it is to check the ZPARM EDM SKELETON POOL size to improve your I/O rate and lower your CPU?

Do you know how to set it?

From the frontline

I was checking and verifying our WorkLoadExpert (WLX) system at a customer’s site recently, and was struck by the size of the EDMPOOL Static SQL Cache.

SSC to you

I call this the “SSC” (Static Statement Cache). What was *really* weird, is that it contained only about 450 statements – even though the EDMSTMTC was set to 1,500,000 KB !!! The Dynamic Statement Cache (DSC) contained about 38,000 Statements.

Different Horses, Different Courses

Now at the same time I happened to get an e-mail from our technical support, which contained a summary of a DSC and a SSC Snap at another customer site. They were getting 50,000 SSC statements, however the DSC size was 4,000,000 KB. This got me seriously wondering about why the SSC was so low at my current site…

Time to upgrade your EDMPOOL!

As luck would have it, an IBM Technical guy was also on site, and so we both peered into the innards of Db2.  I noticed right away that the I/O rate for the EDM SKELETON POOL was *crazy* high! The ZPARM EDM_SKELETON_POOL was set to 10,000 KB which is *crazy* low! (The default is 10,240 KB). We agreed to raise it to 150,000 KB on one member and, if all ok, roll out the change to all the other members in the Group.

Changeable online ZPARM and instantly used

It is an online changeable ZPARM that is instantly used, and as we watched, we saw the I/O to the EDM_SKELETON_POOL dropping and dropping and dropping until it flat lined! Yep – we got the I/O down to zero. Just think of the pay back saving Db2 from searching for free chains, externalizing IFCIDs etc.

Not just SSC got a boost

What I then noticed was that the DSC usage improved too! Why? Well, remember that all SQL needs to be “attached” to a package? Normally a “dummy” like SYSLH… is one of many. But even these packages must be in the pool. When they are cast out then the related DSC entries are *also* cast out!

Big Bottom Line

End of the lesson was:

  • DSC now contains 78,000 SQLs
  • SSC now contains 70,000 SQLs
  • I/O rate to EDM_SKELETON_POOL effectively Zero
  • IFCID 401 (Flushed static SQL) from 3,200,000 per hour down to Zero
  • IFCID 316 (Flushed dynamic SQL) from 36,000 per hour down to 4,000

Just stop to think what this means for the System-wide CPU and I/O rates…
Check it now!

Set the Db2 ZPARM EDM_SKELETON_POOL size

Please check your EDM_SKELETON_POOL size now, introduced in Db2 9, with a default value of 10,240

–  Increase it to at least 150,000 if you can!  –

 

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

Roy Boxwell

 

About Db2 SQL Workload Analysis

SQL Workload Expert for Db2 z/OS offers a complete review of all KPIs (CPU, elapsed, IO etc.) from all SQL (Dynamic and Static) that have executed on the entire system.

Posted on

2017-03 Db2 11 RBA/LRSN Migration 6 to 10 bytes

Db2 11 RBA/LRSN Migration 6 to 10 bytes: How long do you think it will take you to get from 6 – 10 Bytes and Simple/Segmented to UTS’s?
Are you thinking days, months, years??

This month, I want to discuss the pretty big changes that appeared in Db2 11, as these have been made even more important with Db2 12 coming around the corner.

End-of-Time

In Db2 11, the so-called end-of-time or end-of-log problem finally got addressed. The old “it will never run out” six byte RBA (2 to the power of 48 or 256TB) was extended on the left with four bytes of zeroes up to 10 bytes, and the “valid until 17th September 2042 and incremented every 16 microseconds” six byte LRSN was also extended on the left with one byte, and on the right with three bytes. This means the RBA can address 2 to the power of 80 or one Yotta Byte and the LRSN goes up to about the year 36,000. More importantly it goes down to nearly the picosecond!

2042! I will be retired by then…

Well, hold your horses! The end-of-time problem can occur way earlier than that, due to an idiosyncrasy of upgrading to datasharing from non-datasharing. To do this, Db2 must transform your current 6 Byte RBA into a 6 byte LRSN. Naturally an LRSN is a date/time and the RBA just a byte address, so Db2 basically rounds the RBA up to be an LRSN and adds a so-called “DELTA” value to the BSDS. This delta value can surprise you, badly! One of my customers upgraded a schooling system and has now found out that their “end-of-time” is May 2018… Whoops!

 

This delta value can surprise you, badly!
One of my customers upgraded a schooling system and has now found out that their “end-of-time” is May 2018…

 

Check your delta!

Just run a DSNJU004 on your BSDS and check for the STCK TO LRSN DELTA line:

DSNJCNVT CONVERSION PROGRAM HAS RUN   DDNAME=SYSUT1                  
   LOG MAP OF BSDS DATA SET COPY 1, DSN=SB10.BSDS01
   LTIME INDICATES LOCAL TIME, ALL OTHER TIMES ARE GMT.
         DATA SHARING MODE IS ON
         SYSTEM TIMESTAMP   - DATE=2017.079  LTIME=19:29:46.01
         UTILITY TIMESTAMP  - DATE=2016.071  LTIME=18:19:43.66 
         VSAM CATALOG NAME=SB10
         HIGHEST RBA WRITTEN       000000000000FCD54000 2017.079 20:29:46.0
         HIGHEST RBA OFFLOADED     000000000000FBF0AFFF
         RBA WHEN CONVERTED TO V4  00000000000000000000
         MAX RBA FOR TORBA         00000000000000000000
         MIN RBA FOR TORBA         00000000000000000000
         STCK TO LRSN DELTA        00000000000000000000

Here in one of our baby datasharing systems there is no delta, so I can retire!

 

DB2 11 RBA- LRSN Migration 6 to 10 Bytes - Db2 zOSRoadworks ahead!

I like to think of the RBA/LRSN like a three lane German highway (so six lanes in total) that is getting widened to five lanes (so ten in total) You just *know* that the throughput will go up and the traffic jams will go down!

Will it really help?

Well, the out-of-the-box benefits are threefold:

 

  1. No LRSN “spin” – In datasharing a member must wait or “spin”, for some styles of inserts/updates, until it gets a unique LRSN. Now, with faster and faster machines, CPU is being wasted doing nothing but spinning its heels! The IBM Labs state that the percentage overhead ranges between 0% – 6%, and that heavy batch can be much more – even as much as 20%+
  2.  All of the conversion from and to is then gone. Externally always 10, internally a mix. The puffing up and the shrinking down also takes CPU cycles
  3. Converting Logs and tables “may yield a few percent” performance improvement – This again from the IBM Labs.

Road map required!

How to get there? Well first, in NFM, migrate the BSDS from each member one at a time and when all members are done, analyze your workload and pick the biggest usage of UPDATE/DELETE. REORG these objects at the TP/TS level. This gives the biggest improvement earliest.

Rolling on the REORGs

Then trickle through the REORGS on *all* user objects. Here *all* means *all* ! Well actually not all … clone tables cannot be migrated to 10 bytes so you must drop the clone table, REORG the base tablespace and then recreate the clone table.

Remember here to make sure your ZPARMS (OBJECT_CREATE_FORMAT EXTENDED and UTILITY_OBJECT_CONVERSION NOBASIC) are correct and that the Reordered Row Format (RRF) is enabled, since Basic Row Format (BRF) is deprecated!

Forward thinking!

Other things to plan, and think about, at the same time are:

Migrate all INDEX based Partitioned Objects to TABLE based Partitioned Objects and then migrate these to be partitioned by Range PBR (UTS). First an ALTER INDEX xxx.yyy NOT CLUSTER to make it table based instead of index based. Then an ALTER TABLESPACE xx.yy SEGSIZE nn to make it into a PBR (UTS)
Migrate any single table simple/segmented objects to be Partitioned by Growth PBG (UTS). Just an ALTER TABLESPACE xx.yy MAXPARTITIONS 1 is enough
Note that these simple ALTERS cause PACKAGE Invalidation and so must be timed correctly.

Why all the fuss?

Why do this? Well, remember that the UTS, as the underlying tablespace, is the *only* space where Db2 is adding new functionality and performance. Think about inline LOBs, especially with COMPRESS YES, FTB in Db2 12, HASH method, Fast Insert etc. It is clearly the aim of IBM to go to a purely UTS world at some point.

Relative Page Numbering – Should I wait?

In Db2 12, the Relative Page Numbering (RPN) system enables varying sizes of DSSIZE, also at the Index Level. This is seriously good news, *but* it requires a TS level REORG to get there, and you must be in Db2 12. So if you have *vast* history style partitioned tables, you would wait until you are in Db2 12 before you actually do the first big REORG, including going to 10 byte RBA/LRSN if space and time are a big worry.

Are you alone?

No, of course not! Our product RealTimeDatabaseExpert (RTDX) can do all this for you. We also guarantee that no object will be left “hanging”, due to using our BatchControl technology. We also use look-ahead features with time windows to check that we are *not* going to encroach into productive timeframes. If you also have our WorkLoadExpert (WLX) software installed, we can even pick the right moment to fire off the REORGs that you need when you can actually run them! This permanent trickle of reorgs means that within a few years you are done and ready to take advantage of that ten-lane Autobahn!

 

This newsletter is interrupted by our marketing department who are calling out

“By the way, this tailored solution for this special requirement is also available for rent!”

DB2 11 RBA- LRSN Migration 6 to 10 Bytes - Db2 zOS

See our RTDX Flyer and RealTime DBAExpert page

Now back to our usual programming…

How long do you think it will take you to get from 6 – 10 Bytes and Simple/Segmented to UTS’s? Are you thinking days, months, years?? I would love to know! To help you in this phase here are some little SQLs that will give you an idea of how far you have to go…based on the assumption that anything REORGed in the last six months will probably get REORGed again.

SELECT  COUNT(*)                          AS INDEXPARTS_TOTAL
FROM SYSIBM.SYSINDEXSPACESTATS;
SELECT  COUNT(*)                          AS INDEXPARTS
      , SUM(COALESCE(NACTIVE, 0))         AS NACTIVE
      , SUM(COALESCE(REORGINSERTS, 0))    AS REORGINSERTS
      , SUM(COALESCE(REORGDELETES, 0))    AS REORGDELETES
      , SUM(COALESCE(REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSINDEXSPACESTATS
WHERE (REORGLASTTIME IS NULL
  OR (REORGLASTTIME IS NOT NULL
  AND REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
  OR
       (REORGLASTTIME IS NOT NULL
   AND (LOADRLASTTIME IS NULL
    OR (LOADRLASTTIME IS NOT NULL
    AND LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS)))
;                                                          
SELECT  COUNT(*)                          AS TABLEPARTS_TOTAL
FROM SYSIBM.SYSTABLESPACESTATS
; 
SELECT  COUNT(*)                          AS TABLEPARTS
      , SUM(COALESCE(NACTIVE, 0))         AS NACTIVE
      , SUM(COALESCE(REORGINSERTS, 0))    AS REORGINSERTS
      , SUM(COALESCE(REORGUPDATES, 0))    AS REORGUPDATES
      , SUM(COALESCE(REORGDELETES, 0))    AS REORGDELETES
      , SUM(COALESCE(REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSTABLESPACESTATS
WHERE (REORGLASTTIME IS NULL
  OR (REORGLASTTIME IS NOT NULL
  AND REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
  OR (REORGLASTTIME IS NOT NULL
   AND (LOADRLASTTIME IS NULL
    OR (LOADRLASTTIME IS NOT NULL
    AND LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS)))    
;                                                           

Naturally if you are *in* Db2 11 you can easily extend these queries to give you proper feedback like:

SELECT  COUNT(*)                          AS INDEXPARTS 
      , SUM(CASE WHEN B.RBA_FORMAT = 'E' 
                       THEN 1 ELSE 0 END) AS INDEXPARTS_EXTENDED
      , SUM(CASE WHEN B.RBA_FORMAT = 'U'  
                       THEN 1 ELSE 0 END) AS INDEXPARTS_DEFINE_NO 
      , SUM(CASE WHEN B.RBA_FORMAT = 'B' 
                       THEN 1 ELSE 0 END) AS INDEXPARTS_BASIC
      , SUM(CASE WHEN B.RBA_FORMAT = ' ' 
                       THEN 1 ELSE 0 END) AS INDEXPARTS_MIGRATED
FROM SYSIBM.SYSINDEXSPACESTATS A
    ,SYSIBM.SYSINDEXPART       B
WHERE A.CREATOR   = B.IXCREATOR
  AND A.NAME      = B.IXNAME
  AND A.PARTITION = B.PARTITION 
;                                                                    
SELECT  B.RBA_FORMAT                               AS INDEXPART_FORMAT
      , COUNT(*)                                   AS INDEXPARTS 
      , SUM(1E00 * COALESCE(A.NACTIVE, 0))         AS NACTIVE
      , SUM(1E00 * COALESCE(A.REORGINSERTS, 0))    AS REORGINSERTS
      , SUM(1E00 * COALESCE(A.REORGDELETES, 0))    AS REORGDELETES
      , SUM(1E00 * COALESCE(A.REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSINDEXSPACESTATS A                                    
    ,SYSIBM.SYSINDEXPART       B
WHERE A.CREATOR   = B.IXCREATOR 
  AND A.NAME      = B.IXNAME 
  AND A.PARTITION = B.PARTITION
  AND B.RBA_FORMAT IN ('B' , ' ')
  AND ((A.REORGLASTTIME IS NULL 
  OR (A.REORGLASTTIME IS NOT NULL
  AND A.REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))
  OR                                                                 
    (A.REORGLASTTIME IS NOT NULL
    AND (A.LOADRLASTTIME IS NULL
    OR (A.LOADRLASTTIME IS NOT NULL 
    AND A.LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))))
  GROUP BY B.RBA_FORMAT
;                                                                    

SELECT  COUNT(*)                          AS TABLEPARTS 
      , SUM(CASE WHEN B.RBA_FORMAT = 'E' 
                       THEN 1 ELSE 0 END) AS TABLEPARTS_EXTENDED 
      , SUM(CASE WHEN B.RBA_FORMAT = 'U' 
                       THEN 1 ELSE 0 END) AS TABLEPARTS_DEFINE_NO 
      , SUM(CASE WHEN B.RBA_FORMAT = 'B'
                       THEN 1 ELSE 0 END) AS TABLEPARTS_BASIC 
      , SUM(CASE WHEN B.RBA_FORMAT = ' ' 
                       THEN 1 ELSE 0 END) AS TABLEPARTS_MIGRATED
      FROM SYSIBM.SYSTABLESPACESTATS A   
          ,SYSIBM.SYSTABLEPART       B 
WHERE A.DBNAME    = B.DBNAME 
  AND A.NAME      = B.TSNAME 
  AND A.PARTITION = B.PARTITION 
;                                                             
SELECT  B.RBA_FORMAT                        AS TABLEPART_FORMAT
      , COUNT(*)                            AS TABLEPARTS
      , SUM(COALESCE(A.NACTIVE, 0))         AS NACTIVE
      , SUM(COALESCE(A.REORGINSERTS, 0))    AS REORGINSERTS
      , SUM(COALESCE(A.REORGUPDATES, 0))    AS REORGUPDATES
      , SUM(COALESCE(A.REORGDELETES, 0))    AS REORGDELETES
      , SUM(COALESCE(A.REORGMASSDELETE, 0)) AS REORGMASSDELETE
FROM SYSIBM.SYSTABLESPACESTATS A
    ,SYSIBM.SYSTABLEPART       B
WHERE A.DBNAME    = B.DBNAME
  AND A.NAME      = B.TSNAME
  AND A.PARTITION = B.PARTITION
  AND B.RBA_FORMAT IN ('B' , ' ')
  AND ((A.REORGLASTTIME IS NULL
  OR (A.REORGLASTTIME IS NOT NULL
  AND A.REORGLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))     
  OR                                                          
     (A.REORGLASTTIME IS NOT NULL                             
   AND (A.LOADRLASTTIME IS NULL                               
    OR (A.LOADRLASTTIME IS NOT NULL                           
    AND A.LOADRLASTTIME < CURRENT TIMESTAMP - 6 MONTHS))))    
GROUP BY B.RBA_FORMAT                                         
;

Here the RBA_FORMAT value “E” is extended (10 Bytes), “U” is DEFINE NO but if you have the ZPARM OBJECT_CREATE_FORMAT set to EXTENDED (which you should!) then when an insert happens it will get created as extended. The problem children are “B” and Blank.

 

One late bit of info that came from a reader that some of you might find interesting, or perhaps worrying, is this:

There is a potentially time-consuming pre-requisite to the migration and that is the changes made to InfoSphere Information Replication 10.2.1 which expand two columns COMMITSEQ and INTENTSEQ from 10 to 16 bytes to accommodate 10-bytes RBA/LRSN. This modification has a big impact on application programs that make use of these two columns, especially COBOL programs that must be changed and recompiled and regression tested; as well as all SORT statement on the data files extract that include these columns, etc.

 

As usual any comments or criticisms are greatly appreciated

TTFN, Roy Boxwell

 

Share it!

Posted on

2017-02 Why SIZE still matters in Db2 12

What has changed for space management in Db2 12?

How to avoid SIZE limits in Db2 12 like in previous Db2 Releases?

How to monitor the maximum possible SIZE of table and index spaces and table and index partitions?

SIZE in Db2 12: Now that Db2 12 has gone GA, I thought it would be nice to do a quick re-recap of Space management and its problems over the releases. My “old” newsletter 2014-05: Why SIZE matters for Db2 still receives a lot of hits on our website, so I know that this is a big topic of interest for many of you. Some nifty things have been introduced in Db2 12 to make space a lot easier to use and manage.

In the beginning…secondary allocation for tablespaces and indexes since Db2 V7, Db2 V8…

Since Db2 V8, the DBAs of this world have all had the ability to forget about PQTY and SQTY in the DDL for Tablespaces and Indexes. At first, nearly no-one trusted the sliding scale algorithm, and SOFTWARE ENGINEERING’s product Space AssuranceExpert (aka SAX) monitored and reacted instantly to secondary allocations.

However, we now have Db2 12, and I thought it would be interesting to review what was done in Db2 V7 (when our SAX was launched), and the difference nowadays in the Db2 12 world.

IFCID issuing for space extents

Every time a secondary allocation is done in Db2, it can be made to spit out an IFCID. SAX runs as a started task, active 24×7, from Db2 start up until just before Db2 shut down. It catches all of these IFCIDs thrown by Db2, and performs an analysis with six basic questions:

1Can this dataset reach its maximum physical size *before* running out of physical extents? (The actual size is dependent on the “geometry” of the object of course!)
2Will this object run out of datasets? (The number of datasets an object can have is, once again, dependent on the “geometry” of the object)
3Is this partition nearing its maximum size?
4Did Db2 ask for one extent but got more back?
5Are any of my SMS disk storage pools running out of space?
6Are there any SEQUENCES that are about to hit the wall?

(Numbers five and six are actually triggered by a timer, naturally.)

Can this dataset reach its maximum size before running out of extents?

Remember, back in those old days of Db2 V7? We only had 255 extents and 254 partitions, but datasets could still get pretty big pretty fast.

The problem lots of shops had, was that an important dataset would “hit the buffers” of maximum number of extents *way* before it ever ran out of physical space. Thus causing grief, wailing and gnashing of teeth! SAX stopped all this by giving WTO “heads-up” style messages in two flavors. First, a warning message, and then a critical message. This gave DBAs and space managers much needed time to plan for the outage and the, inevitably, long running REORG to actually action the required ALTER, or perhaps even any DROP/RECREATE that had to be done.

IBM also noticed this problem and so introduced in Db2 V8 the “sliding scale” of secondary allocations, as long as the ZPARM OPTIMIZE EXTENT SIZING field (MGEXTSZ) was set to YES (this is the default from Db2 9, by the way). Of course, to really use this, you then had to ALTER all of the existing spaces PQTY and SQTY to be -1, and then remember to delete all PRIQTY and SECQTY lines in your DDL and also rely on the TSQTY and IXQTY ZPARMs giving a big enough “first default”. (By the way, defaults for these two ZPARMS are 0, which is actually translated to be 720k or one cylinder for normal spaces and 7200k or 10 cylinders for LOB spaces). This all probably explains why the take up of this great feature has not been that spectacular and, in fact, Listserv *still* gets questions about “How good is this feature?” This also explains why the primary reason for having SAX is still valid at most shops today!

However, most shops these days tend to ignore the extents problem and only REORG when over 1000 extents have been allocated. This is no problem for SAX, as it knows the SECQTY and the MGEXTSZ ZPARM settings and can decide to “ignore” an IFCID for extent and ALTER SECQTY processing if the SECQTY is -1 and the MGEXTSZ is YES.

Will this object run out of datasets?

Now the problem of running out of datasets is very, very evil indeed… For a non-partitioned space, you can have up to 32 datasets.  Db2 will happily allocate away and you will never know, or even be informed, if, and when, the last possible dataset has just been allocated and, of course, you will not know that the 33rd one cannot be allocated until you get a -904 unavailable resource error! By definition this is “not good”, as you must do a HUGE REORG with a bunch of managers breathing down your neck and *not* make any mistakes with the new allocations. (Again, this is a very good reason to have SAX doing all the monitoring and triggering early warning “heads-up” style messages!)

Is this partition nearing its maximum size?

A partition running out of space is rare, but when it does happen it is, of course, a disaster! The idea in SAX, is to warn when “the end is near” for any of the partitions in use and thus, as before, allow time for the ALTER etc.

Did Db2 ask for one extent but got more back?

Degenerated extents are annoying as well. You have only 255 or 7,257 extents, Db2 requests one but gets up to five back! This is “wasting” your precious supply of extents and so SAX can also warn you if this starts happening. Remedial action can again be planned to correct the problem, (normally a volume defrag in this case). Now in z/OS 1.7 “Extent Constraint Removal” was introduced for the DATACLAS which, if set to “Y”, allows 7,257 extents but still limits you to 123 extents per volume and 59 volumes. So watch out if you are using huge “virtual” disks (E.g. MOD 54 or EAV), as you can end up wasting space because you still cannot exceed 123 extents per volume.

SAX also takes care of duplicate recording – This is where an Extent is registered but SMS “consolidates it into the primary/existing extent – This would normally get logged as an extent but SAX sees this and does not report it as an extent.

Are any of my SMS disk storage pools running out of space?

When an SMS Pool runs out of space, either for sort/work or image copy, it is *not* good! The idea here, is to also give a “heads-up” style alert. The DBA can trigger the space management people to have a look at the state and size of the SMS storage groups this time alerted by percentage used or GBs of space free.

Are there any SEQUENCES that are about to hit the wall?

The usage of SEQUENCES has taken off. Nowadays shops can run into the problem of SEQUENCES hitting the maximum/minimum number for a NOCYCLE defined sequence. SAX tests sequences at the same time as the SMS groups to warn about any encroaching problem with WTO/MSG and reporting.

What was new in Db2 V8?

Db2 V8 introduced a big change – Partitions went up to a maximum of 4,096, and the calculation about how many pieces your NPI can have got “a little bit complex” (see also my previous newsletter: “2014-04 Are you going to pieces”).

What was new in Db2 9?  PBG and UTS spaces

In Db2 9 the next major advance came with UTS spaces. The one that caused the most grief was, of course, PBG. Why? Well, the first problem was that some people went mad and used MAXPARTITIONS 4096 right from the get-go. They then found out this could not simply be changed and ended up being a huge problem. IBM came out with a bunch of fixes for these people, but the recommendation is still true today: “Use the number you expect to use!”

PBGs, however, came with a new set of space management problems:

1By definition every partition is full, and so a TP REORG is “dangerous” -especially if you have VARCHAR, and even more so if compressed.
2ALTER at TP level is not supported for PBG.
3Getting rid of empty partitions was not supported
4Adding partitions dynamically (by command) was not supported.
5What to do if the partition that is “in use” is growing and is

a – The last allowed Partition
b – MAXPARTITIONS is set to one?

Now these are “non trivial” because the Db2 catalog is so defined and you would not want an alert every time someone created a table or index!

The trick here, is to treat these conditions as if it was a normal space and so, instead of warning that you are using the last part, it waits until you are using, e.g. 80% of that part. Then, e.g. at 90% comes the critical threshold warning.

Big changes happened here in Db2 12.

What was new in Db2 10?

With Db2 10 came the ability to ALTER PBGs to add parts which made using DSN1COPY to clone data around a lot better!

What was new in Db2 11?

In Db2 11 the REORG utility can be used to remove any empty parts in PBGs by the use of the ZPARM REORG_DROP_PBG_PARTS being set to ENABLE (DISABLE is the default).

What is new in Db2 12?

Now in Db2 12 there is partition independence for DSSIZE. Before, all partitions had to have the same maximum size (DSSIZE). Now you can have different sizes for different parts. This requires either making a new tablespace (UTS Relative Page Numbering), or an ALTER and TS level reorg of an existing UTS space. The tablespace goes relative page numbering and the RID increases in size to seven bytes hence the need for a TS level REORG. The Partitioning indexes also get DSSIZE so they can vary in size as well. Once you are there, all of the Partitions can then be ALTERed up in size with no outage! This is really, really good!

REORG of a PBG can “spill” into a new partition. This is also really good, as it was the major problem with PBG TP level reorgs. The chance of LOB data going into COPYP during the log apply phase has been stopped – Thankfully! Finally, delete of empty partitions is controlled with a utility DROP PART syntax.

The SAX way for Space monitoring

The SAX tool way of processing all this info is neatly summarised in the help panel of the tool itself:

SUPERVISE LPS

Supervise linear pagesets. If specified, a warning is issued
in case of high allocated reaches this percentage of the
maximum data set size for partitioned objects.

For non-partitioned objects, a warning is issued for every
newly allocated data set as soon as the data set number
reaches this percentage of the maximum number of data sets:

Two different values may be entered for warning and critical
values with different message ids. This may be useful for
automation reasons (see below).

Object type: TABLESPACE      ! Maximum number of data sets
-----------------------------+----------------------------
LOB tablespaces              ! 254
-----------------------------+----------------------------
Non-partitioned tablespaces  ! 32
-----------------------------+----------------------------
Partitioned tablespaces      ! 1 (Percent used check)
-----------------------------+----------------------------
Partitioned By Growth        ! MAXPARTITIONS. LPS check if
tablespaces                  ! more than one. If on last
                             ! partition then percent used.
-----------------------------+----------------------------
Object type: INDEX           ! Maximum number of data sets
-----------------------------+----------------------------
Non-partitioned indexes on   ! MIN ( 4096 , 2 power 32 /
tablespace with LARGE,       !      ( DSSIZE / TS PGSIZE))
DSSIZE, or more than 64      ! Eg: 128 GB DSSIZE with
Partitions                   !       8 KB Tablespace Page
                             ! gives 256 Pieces (datasets)
                             ! Or    4 GB DSSIZE with
                             !       4 KB Tablespace Page
                             ! gives 4096 Pieces (datasets)
-----------------------------+----------------------------
Non-partitioned indexes      ! 32
otherwise                    !
-----------------------------+----------------------------
Partitioned indexes          ! 1 (Percent used check)
-----------------------------+----------------------------
To support automation based on WTO ids two different
thresholds may be specified:
Field (1) specifies a warning threshold using WTO ids
O2RTSU04 - 12W  (non-partitioned spaces)
O2RTSU04 - 14W  (partitioned spaces)
O2RTSU04 - 16W  (partition by growth spaces)
Field (2) specifies a critical threshold using WTO ids
O2RTSU04 - 13W  (non-partitioned spaces)
O2RTSU04 - 15W  (partitioned spaces)
O2RTSU04 - 17W  (partition by growth spaces)

AUDIT DEGENERATED XTS
Audit secondary quantity for de-generated extents. If
specified, a warning is issued in case of the last extent
does not reach this percentage of the SECQTY specified
in the Db2 catalog. If this field is left blank, no
auditing is performed.

AUDIT SMS STOGROUPS
Should the Space AssuranceExpert audit SMS stogroups. Y/N
If Y is entered, a pop-up window will allow you to enter up
to 24 SMS storage groups which will be audited.
If WARN IF % ALLOC > or WARN IF GB FREE < is specified and
exceeded, a warning (WTO) will be issued.

CHECK SYSSEQUENCES
Should the Monitor also check for SYSIBM.SYSSEQUENCES that
are running out of room every PING minutes?

N  - do nothing.  This is the default.
I  - check Identity Columns and Doc Ids for XML.
S  - check User Defined Sequences.
B  - do both.

PERCENT USED
If checking of SEQUENCES is desired then a threshold
percentage must be given from 1 to 99. If this percentage of
the available sequences is exceeded then an action is
triggered.

EXCEEDED ACTION
When a percentage is exceeded this specifies what type and
and which style of message should be externalized.

N  - do nothing.  This is the default.
W  - to write out a WTO.
M  - to write a message to the job log.
B  - do both.

To support automation based on WTO ids the following
messages are output:

O2RTS000 - 20W  (SEQUENCES MAXVALUE)
O2RTS000 - 21W  (SEQUENCES MINVALUE)
O2RTS000 - 22W  (IDENTITY MAXVALUE)
O2RTS000 - 23W  (IDENTITY MINVALUE)

So now you know why size still matters for Db2 12! The big question now is: “Are your space management and monitoring tools up-to-date, or are they still Db2 V7?”

As usual, if you have any comments or queries please feel free to drop me a line!

TTFN

Roy Boxwell

Posted on

2017-01 Db2 12 technical overview: Roy’s first features review

This Db2 12 technical overview presents in an “easy to read” table list a review of new Db2 12 features

Have you encountered any other Db2 12 changes you’d like to discuss?

 

Now that Db2 12 has gone GA I can finally talk about it. So here’s another new Features “first look” at what I think is cool, great, or odd !

This is my personal list for a Db2 12 technical overview – in no particular order :

  •  Db2 12 SQL Optimizer, triggers, Arrays, Merge, UNICODE Columns. Temporal, SQL pagination, SQL Stability, Log, Partitions,…
  •  Data Sharing
  •  Utilities DSN1COPY, Alternate Copy Tools, Audit, REORG, PBG reorgs, COMPRESSRATIO, RELOAD, RO tablespaces, LOAD, BACKUP and Recovery, PiT, RUNSTATS…

 

AGILE This release of Db2 will be ”the last” release, as Db2 Development has gone all agile on us and will be doing Continuous Delivery (CD) from now on. CD promises Easier, Cheaper, Faster and Simpler Db2 maintenance and the quick realization of new functionality.

 

Db2 12 – SQL

Optimizer

MQT or Table expression columns are “trimmed” if they are not used in the outer query.

In LEFT OUTER JOIN, if columns are not used, they can be Pruned.

UNION ALL gets major work when pushing down join predicates as well as pushing down ORDER BY and FETCH FIRST

Outer table joins can get reordered to avoid unnecessary materializations

User-defined functions get two improvements with merge and the introduction of indexes on the join or correlation predicates that are passed in as parameters

Adaptive Index is designed for Multi Index and single index list prefetch to determine at execute time the filtering of each index. This ensures the optimal execution sequence of indexes or, perhaps, a quicker fallback to Tablespace scan if no filtering index exists.

TRIGGERS

The new “advanced” triggers enable SQL and Global variable usage and SQL PL.

ARRAYS

Get a couple of nice new features, specifically the use of a global variable as an array type and the ability to use the ARRAY_AGG without forcing an ORDER BY.

GLOBAL VARIABLES

Get LOB support and in a SET they can be the target.

PureXML

The XMLMODIFY can do multiple document updates in a single invocation. Various XML performance boosts are also included, e.g. XMLTable and the XSLTRANSFORM allows transformations to different formats.

JSON

When using the JSON_VAL function the first argument must not now always be a BLOB. It can be a view, CASE, table expression, trigger transition variable or SQL PL variable or parameter.

MERGE

Is now a full MERGE with the ability to use table references with multiple MATCHED clauses, including DELETE operations.

SQL PAGINATION

The ability of Db2 to “understand” typical paging has been greatly boosted. Typically it was always coded like:

SELECT blah blah blah
FROM mytable
WHERE (SURNAME = ‘BOXWELL’ AND FORENAME > ‘ROY’)
        OR (SURNAME > ‘BOXWELL’)

This is pretty horrible for the Db2 optimizer but we *all* know what we really mean! Now in Db2 12, so does the optimizer! Sadly you must rewrite your queries a little so this example becomes:

SELECT blah blah blah
FROM mytable
WHERE (SURNAME, FORENAME) > (‘BOXWELL’, ‘ROY’)

Also with this comes a nice little feature called OFFSET ROWS. Typically, this is for when the connection to the server is a bit shaky and so after some paging, when the cursor is reopened, the code “knows” it can miss the first 60 rows, so the cursor changes to be:

SELECT * FROM mytable OFFSET 60 ROWS

Nice feature, but beware of polluting the DSC! It is much better to use a parameter marker for these Offsets!

UNICODE Columns

In DB2 11, we got a “fix” for UNICODE columns that was really a “crutch”. This has now been fixed with real UNICODE columns in DB2 12. You must migrate your existing data though!

Piece-wise DELETE

This is a feature I have wanted for decades! Simply add the FETCH FIRST nnnn ROWS ONLY within a DELETE and then programmatically loop around until you are done. Much easier than the method we have today of DECLAREing a CURSOR with an UPDATE of a dummy column and the DELETE WHERE CURRENT OF and after 5000 or so issue a COMMIT.

TEMPORAL RI

You can now add RI as normal and not be forced to use a trigger or stored procedure.

TEMPORAL TABLES

Get the ability to not just be inclusive-exclusive but also inclusive-inclusive.

TEMPORAL Logical Transactions

Another new feature with temporal tables, is the ability to support logical units of work for SYSTEM_TIME. These logical units of work are not determined by COMMIT or ROLLBACK but by using a built-in Global Variable.

PERCENTILE functions

Two new functions PERCENTILE_CONT and PERCENTIL_DISC are new BIFs.

DRDA Fast Load

Is the ability to load data into z/OS DB2 from files sitting on distributed clients.

ODBC

Gets a new INI keyword KEEPDYNAMIC and the connection attribute of SQL_ATTR_KEEP_DYNAMIC.

Obfuscated Code

Mainly of interest to Vendors is the ability to hide your stored procedure, TRIGGER or UDF coding from prying eyes.

RLF for Static SQL

This is a big one! The Resource Limit Facility has always only been available for dynamic SQL. Now you can also use it to cap Static SQL.

TRANSFER OWNERSHIP

This is a very handy way of clearing out all the old owners from a DB2 system.

SQL Stability

Dynamic Plan Stability is nearly the same as BIND QUERY, but the hope is that it will be easier and better to use! But beware of saving all of your dynamic SQL away!

Static Plan Stability gets a good enhancement that allows FREE on the original or previous. What is really good, is that the current version can be in use so there is no application outage anymore.

Insert

New Insert algorythm can be used for faster unclustered insert processing in some cases. Only for UTS MEMBER CLUSTER (This is actually the default for these spaces).

CONCENTARTE LITERALS

Now supported at the Package Level.

FTB

Fast Index Traversal – Especially good for randomly accessed indexes. If the index is unique, and 64 bytes or less, it is eligible. Index is controlled with the new Catalog table SYSIBM.SYSINDEXCONTROL and the -DISPLAY STATS(INDEXMEMORYUSAGE) command.

Log

Active log size can go from 4GB now up to 768GB ! Be careful here!

In-Memory bufferpools

by using PAGESTEAL(NONE) keyword.

PARTITIONS

Finally we get the chance to give each partition its own DSSIZE as well as the Partitioning indexes! This is great, but sadly is only available to an existing space once you have reorged the whole tablespace…However, once you are there, you can then have data and index parts up to 1TB in size, plus, when you do an ALTER of the DSSIZE, it does not cause an outage (as long as you make it bigger!). A side effect of this is that the RID is now seven bytes (see REORG mapping table for other changes). You can now also add partitions in the middle of an existing PBR table.

 

 

Data Sharing

Recovery

of retained locks from a failed member can be handled automatically

LPL and GRECP recovery

LPL and GRECP recovery auto retries three times after waiting three minutes

 

Db2 12 Utilities

DSN1COPY

In DB2 11 this utility got a few sanity checks and now the REPAIR CATALOG utility can fix some of these. The REPAIR CATALOG TEST also looks for some problems caused by misuse/abuse of DSN1COPY.

ALTERNATE COPY POOLS

The usage of BACKUP SYTEM is growing. So is the amount of storage required! The idea here, is to define a set of copy pools, but only one for many DB2 subsystems. The alternate copy pool uses as many volumes as it needs and leaves the other volume free for a different subsystem backup. This reduces the amount of space that must be allocated.

Audit

A new Authorization arrived: UNLOADAUTH to “replace” the “Does the user have SELECT auth on the table?” check that has run up to now. UNLOAD is special and should be controlled over this auth and no longer over just SELECT.

REORG

PBG tablespaces get the best news here!

PBG reorgs can now spill over into a new PBG if the row(s) do not fit back into the original partition. Classic case here, is compressed data that no longer fits back. This forced people to use a TS level reorg or not use compression.

If the PBG contains LOB data and it extended to a new partition in the log apply phase, then the LOB space was left in COPY Pending… pretty horrible and that no longer happens in DB2 12.

Another PBG bonus, is the delete of “emptied” Partitions after a REORG has completed.

Improved FlashCopy support – You can now decide to stop the REORG if the flash copy fails.

New Catalog column COMPRESSRATIO for use by utilities that records the compression savings at the record instead of at the page level.

RELOAD phase can now be offloaded to zIIP.

RO tablespaces can now be REORGed at any SHRLEVEL.

The mapping table gets changed again due to the relative page numbering in the new PARTITION support (seven byte RID).

LOAD

PART REPLACE with dummy input against an empty (PBR) partition could be quicker.

LOAD SHRLEVEL CHANGE PARALLEL support for PBG for SHRLEVEL CHANGE.

Additional zIIP offload, like in REORG, in the RELOAD phase, including the data conversion and loading of the record into the page set.

LOAD RESUME BACKOUT YES to avoid RECP on failure. Adds a new option on LOAD RESUME SHRLEVEL NONE to allow LOAD to back out the rows already loaded upon encountering an error (such as conversion, LOB/XML, duplicate key, referential integrity violation) without leaving the page set in RECP.

PREFORMAT support for auxiliary tables. Support is extended to LOB table spaces and auxiliary indexes.

Maintain MAXASSIGNEDVAL for identity columns. LOAD now maintains the MAXASSIGNEDVAL for user-provided input and resets the value if a LOAD REPLACE is run on the table space.

LOAD REPLACE support for the COMPRESSRATIO column for use by utilities that records the compression savings at the record instead of at the page level column.

BACKUP and RECOVERY

Point-in-Time support for PBGs, Flashcopy FLASHCOPY_PPRCP keyword. As mentioned the default is changed to not recover unchanged objects. MODIFY RECOVERY gets two new options: DELETEDS to delete the datasets and NOCOPYPEND to not set COPY pending after doing the MODIFY.

PiT

Has been improved with the ability to skip unnecessary recoveries. SCOPE UPDATE only processes objects that have been updated up to the TOLOGPOINT or TORBA.

RUNSTATS

New CLUSTERRATIO formula which should better reflect dynamic prefetch. Terry Purcell has stated that it is not a huge change and does not require a RUNSTATS of all tablespaces!

FREQVAL COUNT nn

The COUNT nn is now optional and, if not used, then RUNSTATS will work out the best number for you. This is really, really nice and I would recommend this in an instant! It has also been retro fitted to DB2 11.

Autonomic Statistics with PROFILEs

I am no fan of this, as I believe it makes for a pretty nasty feedback loop where anyone’s “dumb” QMF/SAS/DSNTEP2/SPUFI will get inserted as a PROFILE COLGROUP, and then these PROFILEs will get bigger and bigger until no-one knows which are really useful and which are just fluff! I would recommend setting the ZPARMs STATFDBK_SCOPE to ALL (Default) STATFDBK_PROFILE to NO (Default is YES). When YES is used DB2 12 will create and/or maintain a PROFILE for you. Finally, validate that the SYSTABLES column STATS_FEEDBACK is set to “N” (Default is “Y”) for any and all tables where you do *not* want SYSSTATSFEEDBACK data. E.g. All the DSNDB01 tables where a RUNSTATS is not even allowed!

DSC

DSC Invalidation got switched off by default. In the past *any* RUNSTATS flushed the cache. Now you must add the key word INVALIDATECACHE YES to get this to occur. (Unless you use the REPORT NO UPDATE NONE syntax this still just flushes the DSC)

Inline Stats

Inline Stats got a huge boost with PROFILE support, MOST/BOTH/LEAST and LOAD PARALLEL got inline stats.

 

Have you encountered any other Db2 12 changes you’d like to discuss?

As usual, feel free to email me with questions or comments.

TTFN

Roy Boxwell

 

5

Posted on

Rotten Results from RUNSTATS Require Rescue

Do you know the basic rules to ensure access path stability when using RUNSTATS?

Time for another of my “I noticed something strange at a customer site recently” newsletters. Enjoy!

 

RUNSTATS are good aren’t they?

At this particular site, the RUNSTATS methodology of RUNSTATS was, shall we say, “sub-optimal.” They use an ancient system to decide when to RUNSTATS, and they do tablespace’s and index’s *never* at the same time. Just to complicate matters even more, they never use inline RUNSTATS because “if the REORG abends, the statistics in the DB2 catalog are dead”. Now you are all probably well aware of the scale of the disaster at this site?

 

Daily fire fighting

Nearly every day, some access path somewhere goes horribly wrong… the under- manned and over-worked DBA group are tasked to find and fix ASAP. Cures range from a quick INDEX create or change, or perhaps even a really needed RUNSTATS or REORG.

 

Why do the Access Paths go “wrong”?

The real goal is to stop firefighting and to investigate the root cause. Why do so many access paths go wrong on such a regular basis? The answer is the systemic horribleness of RUNSTATS collection. Dynamic SQL is, obviously, very very sensitive to RUNSTATS. For one thing, the statements are kicked out of the cache! The very next time they come back, the DB2 Optimizer redrives the cost calculations and “Hey Presto!” you have a bad access path. Terry Purcell and Pat Bossmann have often said that about 90% of DB2 performance problems stem from bad RUNSTATS. The old adage “garbage in – garbage out” is still true!

 

Timing is everything

The timing of the RUNSTATS is critical for stable access paths.

Basic rules are:

 1 Only do a RUNSTATS if you really really need to!
a. RUNSTATS are not cheap!
b. The Dynamic Statement cache gets wiped
c. Locks on the Catalog can occur
2Avoid doing RUNSTATS even if RTS says to run one!
a. Lots of people use the incorrect counters to trigger a RUNSTATS. Use the correct ones for the correct Object type
b. Never RUNSTATS LOB spaces – completely pointless work!
c. Even if a MASSDELETE has occurred do you really want to “reset” the DB2 catalog statistics?
d. VOLATILE tables must be handled with *extreme* care!
3Choose your RUNSTATS parameters wisely!
a. Doing a blind “RUNSTATS the world” is just as bad as running an empty RUNSTATS!
b. HISTOGRAM should be used with caution
c. More than a hundred COLGROUPs should start alarm bells ringing

Quite a list here, and it really only shows some “Rules of Thumb”. I’ll bet you all have you own?

Is there a way back from the abyss?

But what happens if you have 1000’s of partitions with terabytes of data and the RUNSTATS was, shall we say, ill-advised or badly timed? Can you go back in time? Hands up those who wants to do a PiT recovery on the production catalog! No takers???

Yes! There is a way back from the abyss

I’ll bet you are all well ahead of me here, but the way to do this is pretty straightforward. You simply acquire our latest tool, RUNSTATS Rescue, to handle it all for you. Or, you could try and reset the data in the DB2 catalog from off-line backups that you happen to have taken before the RUNSTATS that is now killing you. …You did do that, right?

Why a tool?

Apart from the fact that this tool is from us, my firm, just trying to “roll your own” can be a real nightmare. Why?

  • Because you must first find out all of the objects that were touched by the badly performing SQL.
  • Then you must get all of the DB2 Optimizer relevant data back from a point in time before the RUNSTATS executed, and/or the last REBIND(s),
  • and then you must flush the dynamic statement cache and REBIND any static SQL.

Sounds like a lot of work.

What else must you do?

You also have to be transparent and so log what you do. You must allow for the ability to back-out your changes as perhaps you make another access path even worse. And it would be really cool if you could do “on the fly” explains to check that the RUNSTATS really *is* the root of all that evil. Remember that ZPARMS and BUFFERPOOLS also have a major influence on access paths. Even the speed of your machine! It is also a must to then be able to go even further back in time – perhaps as much as a year?

Hang on – What about PLAN STABILITY?

Doesn’t plan stability save you? I hear you all cry. Well, “No” is the short answer! If your package is invalidated by a Schema change (the classics are index drop and recreate or VIEW change), then plan stability does not work anymore. Further, in DB2 12, Dynamic Plan Stability has been announced. Sadly it *also* fails right here as there is no SWITCH PREVIOUS/ORIGINAL support!

It all works together

So, for the static SQL case where the package is not invalided, Plan Stability is good. If not: – RUNSTATS Rescue to the rescue. For Dynamic SQL – RUNSTATS Rescue is the answer.

As always, any questions or comments would be most welcome!

TTFN,

Roy Boxwell