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

2018-04 Negative DBIDs: Something new something old

Or: „Even Roy learns things from Listserv!“

While discussing negative DBIDs on Listserv the other day, another poster commented on my SQL Code snippet and said that it could well be better changing from LOCATE and HEX to using the ASCII Built-In Scalar function. But first, the old data must be able to understand the new data!

About IBM List Servers Discussions – Forums: http://www.vm.ibm.com/techinfo/listserv.html
Db2 z/OS “Listserv”:
IBM-MAIN newsgroup, mostly z/OS, OS/390, & MVS
DB2 Database discussion list

The DBID what?

The DBID, or DataBase object Identifier, is a SMALLINT number that is used internally for *all* objects in Db2. IBM has reserved the first 254 characters for their own use and we, as users, have the rest – up to 65,535. Now, if you are sharp you will have noticed that I wrote SMALLINT.A quick glance in the “limits of Db2” chapter of??? shows us:

Table 143. Numeric limits
Item Limit
Smallest SMALLINT value -32768
Largest  SMALLINT value  32767

The trick is…

So how do they do it? Well, they stuff a quart into a pint pot by going negative on us!

If a DBID goes over the value 32511 it then switches to negative numbers.

This saved IBM two bytes of storage… of course it is two bytes that is *everywhere*, so it actually saves quite a bit of space (pardon the pun!)

The IDs have it… PSID – OBID – ISOBID…

The PSID or Page Set Identifier is also involved here, as that is the “count” of things within that Database and it cannot exceed 32767. Unless, of course, it goes negative! Why here? Well think CLONE, any CLONE objects have the high-bit set, thus flipping them over to be the “mirror image” of the base object.

To round this brief discussion of the IDs, we also have the OBID or OBject IDentifier and the ISOBID or Index Space OBject IDentifier. All of these are stored as SMALLINTs everywhere in the Catalog and Directory and, naturally, the absolute physical never-going-to-change-maximum number of things in a Database is 65535.

LOCATE and HEX

I referred back to one of my older newsletters: “Discovering hidden recovery problems in the SYSLGRNX” from October 2016, where I presented an SQL to “extract” the DSNDB01 SYSLGRNX table into a “usable” format. The SQL started like this:

SELECT COALESCE(
  CASE WHEN
  ((LOCATE(SUBSTR(HEX(A.LGRDBID), 1 , 1),'0123456789ABCDEF')-1)*4096)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 2 , 1),'0123456789ABCDEF')-1)*256)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 3 , 1),'0123456789ABCDEF')-1)*16)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 4 , 1),'0123456789ABCDEF')-1))
        > 32767 THEN
  (((LOCATE(SUBSTR(HEX(A.LGRDBID), 1 , 1),'0123456789ABCDEF')-1)*4096)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 2 , 1),'0123456789ABCDEF')-1)*256)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 3 , 1),'0123456789ABCDEF')-1)*16)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 4 , 1),'0123456789ABCDEF')-1))
       - 32768) * -1
  ELSE
  ((LOCATE(SUBSTR(HEX(A.LGRDBID), 1 , 1),'0123456789ABCDEF')-1)*4096)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 2 , 1),'0123456789ABCDEF')-1)*256)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 3 , 1),'0123456789ABCDEF')-1)*16)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 4 , 1),'0123456789ABCDEF')-1))
  END
      , 0 ) AS LGRDBID

I thought that was a neat way to change a CHAR(2) field into a real number and to also handle the problem of negative DBIDs when they “overflow” the SMALLINT boundary. (Remember, this happens if you have more than 32,511 Databases).

Problem one

First problem that jumped out at me, is the fact that the math is wrong! I should, of course,  have subtracted 32767 from the DBID.

Where does ASCII fit?

Well, it came out in Db2 V8 and got reviewed by me in a very old newsletter called: “SOUNDEX and other “cool” features – part one for DB2 V8” in April of 2012 !

Starting off with DB2 V8 :  ASCII function

V8 introduced us to the ASCII function:

SELECT ASCII('BOXWELL')             
FROM SYSIBM.SYSDUMMY1 ;             
         66

So it takes the leftmost character of any given string expression and returns the ASCII value, in this case 66 for B – I must admit that I have *not* yet found a use for this little gem…

Back to LISTSERV…

The poster wrote:

Roy,

Perhaps your query performs well enough already, so there is no need to change, and I have used LOCATE on a HEX digit myself for similar reasons on SYSPACKSTMT table. However I intend to change my query over to use ASCII function.

For anyone needing to convert a Char for BIT Data column, to an Integer in this case Char 2 bytes, based on the string actually containing a binary integer, you could look at the ASCII function to do it (more efficiently), I believe:

e.g. ASCII(SUBSTR(arg, 1, 1)) * 256 + ASCII(SUBSTR(arg, 2, 1))

I believe ASCII function accepts Char for Bit Data without auto conversion to another CCSID. ASCII and a lot of other fancy functions arrived in Db2 V8. Slightly less of a Pain in the A***. Because of the fancy functions, CTEs, and Recursive SQL, V8 was possibly my favourite release of DB2, when SQL could do absolutely anything, so I did not need REXX post processors any more. I forgot to mention Native SQL Stored Procs, and Non-Padded VARCHAR in indexes, DPSIs. Wow, what a release!

Of course this is amusing, since the function ASCII is somewhat misnamed. What we might use it for has nothing to do with ASCII encoding at all.

Aha! Moment

I thought wow! That could indeed be a lot faster and easier than my code so I changed my SQL to look like:

SELECT COALESCE( 
      CASE WHEN ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256 
              + ASCII(SUBSTR(A.LGRDBID, 2, 1)) > 32767 THEN 
              ((ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256 
              + ASCII(SUBSTR(A.LGRDBID, 2, 1))) - 32767) * -1 
           ELSE ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256 
              + ASCII(SUBSTR(A.LGRDBID, 2, 1)) 
      END , 0 ) AS LGRDBID 
       ,COALESCE( 
      CASE WHEN ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256 
             + ASCII(SUBSTR(A.LGRPSID, 2, 1)) > 32767 THEN 
             (ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256 
             + ASCII(SUBSTR(A.LGRPSID, 2, 1))) - 32767 
           ELSE ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256 
             + ASCII(SUBSTR(A.LGRPSID, 2, 1)) 
           END , 0 ) AS LGRPSID 
            ,TIMESTAMP( 
      CASE WHEN SUBSTR(A.LGRUCDT, 5 , 2 ) > '16' THEN '19' 
           ELSE '20' 
      END CONCAT 
           SUBSTR(A.LGRUCDT, 5 , 2 ) CONCAT '-' CONCAT 
           SUBSTR(A.LGRUCDT, 1 , 2 ) CONCAT '-' CONCAT 
           SUBSTR(A.LGRUCDT, 3 , 2 ) CONCAT '-' CONCAT 
           SUBSTR(A.LGRUCTM, 1 , 2 ) CONCAT '.' CONCAT 
           SUBSTR(A.LGRUCTM, 3 , 2 ) CONCAT '.' CONCAT 
           SUBSTR(A.LGRUCTM, 5 , 2 ) CONCAT '.' CONCAT 
           SUBSTR(A.LGRUCTM, 6 , 2 ) CONCAT '0000' 
                 ) AS LGRUCTS 
      ,A.LGRSRBA 
      ,A.LGRSPBA 
      ,A.LGRPART 
      ,A.LGRSLRSN 
      ,A.LGRELRSN 
      ,COALESCE( 
               ASCII(SUBSTR(A.LGRMEMB, 1, 1)) * 256 
             + ASCII(SUBSTR(A.LGRMEMB, 2, 1)) 
         , 0 ) AS LGRMEMB 
      ,CASE WHEN ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256 
            + ASCII(SUBSTR(A.LGRPSID, 2, 1)) > 32767 THEN 2
            ELSE 1 
       END       AS LGRINST 
FROM SYSIBM.SYSLGRNX A 
FOR FETCH ONLY 
WITH UR 
;

Whoosh!

Yes indeed, over 30% faster than my code…

Now remember, you can only really do this if your CHAR data has the FOR BIT DATA attribute so that no code page conversion is done. The LGRMEMB column is ok because it contains the hex values 00 (For non-datasharing) or 01 to 32 (Maximum number of members in a data-sharing group). These do not convert so they don’t cause a problem.

Thanks again to LISTSERV and the Posters who keep it alive and help old dogs (like me) learn new tricks.

I might even update my old ASCII review to mention that I do indeed have a use for it now!

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

TTFN,

Roy Boxwell

Posted on

2018-03 RTS RUNSTATS

 „Breaking the rules is often fun, although we cannot condone it. But breaking the rules of Real Time Statistics (RTS) in Db2 12 can really land you in hot water.

We provide two queries that may give you a Get Out of Jail Free Card – at least as far as RTS is concerned…”

In this short newsletter, I wish to briefly discuss a change in behavior of the Real Time Statistics (RTS) tables that I have now seen at customer sites. I am not too happy about it! Before we start, a quick resumé of the RTS is required.

The Arrival of RTS

The Real Time Statistics were basically created by Dr Jim Teng in Db2 V7 and have always obeyed a few quite simple rules.

Real Time Statistics (RTS) rules:

  1. Rule Number one
    If in doubt set to NULL.
    This might read a bit odd but the idea from Jim was that if any value is not 100% known then the column must be set to the NULL value.

  2. Rule Number two
    Externalize when asked, or by timer.

  3. Rule Number three
    No DEFINE NO data.

  4. Rule Number four
    Utilities will always update the RTS unless it cannot – see Rule Number one.

  5. Rule Number five
    SQL updates the counters unless they cannot – Think Mass Delete in a multi-table tablespace. Totalrows cannot be updated.

Rules are made to be broken

A couple of years into RTS usage and the clamor for changing various insert values got too big, so IBM enhanced the RTS so that on creation the REORGLASTTIME got set to the created timestamp and all the counter columns got set to zero instead of NULL. Now this was a good change as adding 1,000,000 to NULL you get NULL, whereas adding 1,000,000 to zero means you get 1,000,000 which is obviously better for working out whether or not you need a utility to run. The REORGLASTTIME was also accepted as when you create a nice empty object or you insert one row into an object, then by definition, it is in a perfectly reorganized state!

RTS rule one broken

Mass delete always caused problems, as mentioned earlier, so IBM then made a “half way” fix for the INDEX statistics in Db2 9 by zeroing the TOTALENTRIES when there is a Mass Delete as Db2 knows that the index is now empty.

(PM34730: RTS SYSINDEXSPACESTATS TOTALENTRIES INCORRECT AFTER MASS DELETE.)

Of course it did not update the TOTALROWS, as it didn’t “know” how many rows were mass deleted or truncated. This causes “drift” between the TS and IX statistics, but is only a minor annoyance.

Db2 12 breaks rule one

Now in Db2 12 Rule one has been broken again. Not that much of a break, but still not brilliant! What they have done, is change the STATSLASTTIME to now also be, nearly, the created TS – as if creating an object sets the runstats columns to valid data!

Naturally, the Db2 Catalog is still all -1’s. This makes generating utilities based on the RTS a little bit tricky, as you cannot trust the STATSLASTTIME to now ever actually be the time a RUNSTATS utility really ran – which was the *whole*, and only, purpose of this column! If only they had set the STATSLASTTIME to ‘0001-01-01-00.00.00.00.000000’ then all would be well…

Who woke the dogs up? (Or: Who let the dogs out? I.e. American jargon)

PI79234: SYSIBM.SYSTABLESPACESTATS.STATSINSERTS IS NOT UPDATED BY RTS EXTERNALIZATION SINCE OBJECT IS CREATED.
Is the APAR (PTF UI48494) that caused me all the trouble.

Where’s the beef?

Well, the problem is, if you are generating RUNSTATS based on the RTS – and who isn’t these days? – Then you are probably using this column. Now it *looks* like a RUNSTATS has been run at least once.

This is false and can lead to the not running of RUNSTATS when it most definitely should have been run. Check all your home-grown RUNSTATS checkers. Remember DSNACCOX is also a little bit broken, as it uses these predicates:

(STATSLASTTIME IS NULL OR
 STATSLASTTIME<LOADRLASTTIME OR
 STATSLASTTIME<REORGLASTTIME OR
 STATSLASTTIME< latest PROFILE_UPDATE for the table space1 OR

Make sure you do not use the STATSLASTTIME, but instead join across to the SYSINDEXPART or SYSTABLEPART and pull out the STATSTIME column. This data is still ok and not fake news!

The scope of the problem

To find out the scope of the problem at your site, or even if you have this problem, you can run these two queries which simply list out all the objects that have, according to the Db2 Catalog, never been RUNSTATted, but according to the RTS they have been RUNSTATted:

Query 1

-- SELECT LIST OF TABLESPACES THAT ACCORDING TO DB2 CATALOG HAVE NOT 
-- BEEN RUNSTATTED BUT ACCORDING TO RTS HAVE BEEN.  
-- IGNORE DSNDB01 AS NO RUNSTATS FOR DIRECTORY OBJECTS 
-- IGNORE WORK DEFINED DATABASES AS NO RUNSTATS FOR THESE 
-- ONLY CHECK FOR TABLE TYPES H,M,P,R AND T 
-- DISTINCT IT FOR MULTI TABLE TABLESPACES 
SELECT DISTINCT A.DBNAME, A.NAME, A.PARTITION 
      ,A.REORGLASTTIME, A.STATSLASTTIME 
      ,B.STATSTIME, B.CREATEDTS 
FROM SYSIBM.SYSTABLESPACESTATS A 
   ,SYSIBM.SYSTABLEPART        B 
   ,SYSIBM.SYSDATABASE         C 
   ,SYSIBM.SYSTABLES           D 
WHERE B.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT COALESCE(A.STATSLASTTIME 
                   ,TIMESTAMP('0001-01-01-00.00.00.000000')) 
                  = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT A.DBNAME = 'DSNDB01' 
  AND A.DBNAME = C.NAME 
  AND NOT C.TYPE = 'W' 
  AND A.DBNAME = B.DBNAME 
  AND A.NAME = B.TSNAME 
  AND A.PARTITION = B.PARTITION 
  AND A.DBNAME = D.DBNAME 
  AND A.NAME = D.TSNAME 
  AND D.TYPE IN ('H' , 'M' , 'P' , 'R' , 'T') 
ORDER BY 1 , 2 , 3 
;

Query 2

-- SELECT LIST OF INDEXSPACES THAT ACCORDING TO DB2 CATALOG HAVE NOT 
-- BEEN RUNSTATTED BUT ACCORDING TO RTS HAVE BEEN. 
-- IGNORE DSNDB01 AS NO RUNSTATS FOR DIRECTORY OBJECTS 
-- IGNORE HASH INDEXES AS NO RUNSTATS ALLOWED 
-- ONLY CHECK FOR TABLE TYPES H,M,P,R AND T 
SELECT A.DBNAME, A.INDEXSPACE, A.PARTITION 
      ,A.REORGLASTTIME, A.STATSLASTTIME 
      ,B.STATSTIME, B.CREATEDTS 
FROM SYSIBM.SYSINDEXSPACESTATS A 
    ,SYSIBM.SYSINDEXPART       B 
    ,SYSIBM.SYSINDEXES         C 
    ,SYSIBM.SYSTABLES          D 
WHERE B.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT COALESCE(A.STATSLASTTIME 
                  ,TIMESTAMP('0001-01-01-00.00.00.000000')) 
                  = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT A.DBNAME = 'DSNDB01' 
  AND A.CREATOR = B.IXCREATOR 
  AND A.NAME = B.IXNAME 
  AND A.PARTITION = B.PARTITION 
  AND A.CREATOR = C.CREATOR 
  AND A.NAME = C.NAME 
  AND NOT C.HASH = 'Y' 
  AND C.TBCREATOR = D.CREATOR 
  AND C.TBNAME = D.NAME 
  AND D.TYPE IN ('H' , 'M' , 'P' , 'R' , 'T') 
ORDER BY 1 , 2 , 3 -
;

You can run these in any version of Db2 you like, but you will only get results from a Db2 12 system with this APAR applied and you have created an index or a tablespace. When I run them here on my Db2 10 and 11 systems I get zero rows back and on my test Db2 12 FL501 system I get 172 rows.

The good news is…

Our product for helping you all get “agile” in Db2 12, CDDC – ContinuousDelivery DeploymentCheck, detects and reports these data constellations as does our Statistics Health Check, of course.

I would be very interested to hear your opinions about this “little change in behavior”. Have you come across this at your site?

 

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

TTFN,

Roy Boxwell

Posted on

2018-02 Db2 Catalog Statistics revisited

 

Db2 Optimizer & access path selection for Db2 11 & Db2 12 :

Db2 Catalog data | Problematic default values | Correlations in the Db2 Catalog 

 

It has been six years since the last update so I thought, after Terry Purcell’s excellent presentation in January 2018, it would be a good point in time to go over and rake the coals again—especially as a couple of things have changed for Db2 12!

Terry Purcell – Db2 12 for z/OS Optimizer and RUNSTATS improvements
Webcast replay          Abstract

Are you a RUNSTATS Master?

Every now and again, I hold a little presentation called “Are you a RUNSTATS Master?” Actually these days it’s called “Db2 z/OS Lies, Damn Lies, and Statistics…” where I describe in detail, what the Db2 Optimizer uses for access path selection in relation to the Db2 Catalog data.

Surprised? You will be!

Personally, I am always surprised at how often people say “just that data?” or “is that it?” (the various other reasons for access path selection like CP speed, RID Pool size, Sort Pool size, Max data caching size, and, of course, the 80 bufferpools are also mentioned, but these have nothing to do with RUNSTATS).

So generally the answer is “Yes.” However, the permutations and combinations make the devil in the detail – The Db2 Optimizer’s algorithms are top secret, but the input data it uses is fully described in the documentation.

Just the facts ma’am

What I want to do, is show :

  • the Db2 Catalog data that is used
  • the default values that can cause surprising things to happen
  • the problem of correlations in the Db2 Catalog

Which data are used by the Db2 Optimizer and which are updated by RUNSTATS?

Here is a complete list of the eleven tables used by the Db2 Optimizer:

  1. SYSIBM.SYSCOLDIST
  2. SYSIBM.SYSCOLSTATS *
  3. SYSIBM.SYSCOLUMNS
  4. SYSIBM.SYSINDEXES
  5. SYSIBM.SYSINDEXPART
  6. SYSIBM.SYSKEYTARGETS (same as SYSCOLUMNS)
  7. SYSIBM.SYSKEYTGTDIST (same as SYSCOLDIST)
  8. SYSIBM.SYSROUTINES
  9. SYSIBM.SYSTABLES
  10. SYSIBM.SYSTABLESPACE
  11. SYSIBM.SYSTABSTATS

* degree of parallelism only and, after APAR PK62804, also „sometimes“ used to bound filter factor estimates…

By the Columns

Now we can also list out all of the columns (obviously not including the key columns) which are used by the Db2 Optimizer:

SYSCOLDIST
CARDF, COLGROUPCOLNO, COLVALUE, FREQUENCYF, HIGHVALUE, LOWVALUE, NUMCOLUMNS, QUANTILENO, STATSTIME

SYSCOLSTATS
COLCARD, HIGHKEY, LOWKEY

SYSCOLUMNS
COLCARDF, HIGH2KEY, LOW2KEY

SYSINDEXES
CLUSTERING*, CLUSTERRATIO, CLUSTERRATIOF, DATAREPEATFACTORF, FIRSTKEYCARDF, FULLKEYCARDF, NLEAF, NLEVELS

SYSINDEXPART
LIMITKEY*

SYSKEYTARGETS
CARDF, HIGH2KEY, LOW2KEY, STATS_FORMAT

SYSKEYTGTDIST
CARDF, KEYGROUPKEYNO, KEYVALUE, FREQUENCYF, HIGHVALUE, LOWVALUE, NUMKEYS, QUANTILENO, STATSTIME

SYSROUTINES
CARDINALITY*, INITIAL_INSTS*, INITIAL_IOS*, INSTS_PER_INVOC*, IOS_PER_INVOC*

SYSTABLES
CARDF, EDPROC*, NPAGES, NPAGESF, PCTROWCOMP

SYSTABLESPACE
NACTIVE, NACTIVEF

SYSTABSTATS
CARD, CARDF, NPAGES

Notes: * Columns are not updated by RUNSTATS and _ Columns are not updatable at all. The column STATSTIME is used only if there are duplicates in the SYSCOLDIST table, and then the Db2 Optimizer will use the “newer” data that was probably inserted by a User.

Know your defaults

Which default column values trigger the Db2 Optimizer to use its own internal default values?

SYSCOLUMNS

If COLCARDF                       = -1 then use 25

SYSINDEXES

If CLUSTERRATIOF            = 0 then use CLUSTERRATIO

If CLUSTERRATIO              = 0 then use 0.95 if the index is CLUSTERing = ‘Y’ otherwise 0.00

DATAREPEATFACTORF    = -1 then is ignored

If FIRSTKEYCARDF            = -1 then use 25

If FULLKEYCARDF             = -1 then use 25

If NLEAF                              = -1 then use 33 (Which is SYSTABLES.CARDF / 300)

If NLEVELS                         = -1 then use 2

SYSROUTINES

If CARDINALITY                  = -1 then use 10,000  

If INITIAL_INSTS                 = -1 then use 40,000

If INITIAL_IOS                     = -1 then use 0

If INSTS_PER_INVOC        = -1 then use 4,000

If IOS_PER_INVOC            = -1 then use 0

If IOS_PER_INVOC            = -1 then use 0

SYSTABLES

If CARDF                             = -1 then use 10,000

If NPAGESF                      <= 0 then use NPAGES

If NPAGES                          = -1 then use 501 (Which is CEILING (1 + SYSTABLES.CARDF / 20))

Here you must be very careful if using NPGTHRSH ZPARM as 501 is more than the default value in most shops. This is one of the little changes in Db2 12 where the value -1 is treated as -1 for the NPGTHRSH check.

SYSTABLESPACE

If NACTIVEF                     = 0 then use NACTIVE

If NACTIVE                       = 0 then use 501 (Which is CEILING (1 + SYSTABLES.CARDF / 20))

SYSTABSTATS

If CARDF                         = -1 then use 10,000

If NPAGES                       = -1 then use 501 (Which is CEILING (1 + SYSTABSTATS.CARDF / 20))

So now you can see that non-floating point “old” data, may still be used today and this may cause access path headaches!

Never ever say never

Now to top it all, the data in the SYSCOLDIST and SYSKEYTGTDIST never gets simply “deleted”.

Well, actually, in Db2 12 you can now do a RUNSTATS xxx.yyy RESET ACCESSPATH to delete all SYSCOLDIST and SYSKEYTGTDIST data and set all other relevant columns to their respective defaults, but you must time this RUNSTATS very wisely! If you run it and then forget to do a normal full RUNSTATS…

Oldie but a goldie

Once the data are inserted, they stay there, until they are overwritten by new data, a RUNSTATS RESET, or the object is dropped. This all leads to some very old data in these two tables that can and do cause the Db2 Optimizer a ton of grief! One of the first things I do is to simply select the MIN(STATSTIME) from these tables just to see how old the data really is. Do it yourself and be surprised! I have seen sites with eight-year old data in the SYSCOLDIST and that cannot be good!

Correlate the world

Now onto correlations… There are lots of little tricks that DBAs use to “massage” access path choice. One of these, is to just set NLEVELS to 15 for a given index. Then lots of queries simply refuse to touch it as it would appear to be HUGE. Now, just simply updating columns can cause the Db2 Optimizer, in the best case, to ignore the updates or, perhaps, makes things even worse! So here is a list of the correlations (In other words, if you change xxx remember to change yyy and zzz as well):

Relationships exist among certain columns of certain tables:

  •       Columns within SYSCOLUMNS
  •       Columns in the tables SYSCOLUMNS and SYSINDEXES
  •       Columns in the tables SYSCOLUMNS and SYSCOLDIST
  •       Columns in the tables SYSCOLUMNS, SYSCOLDIST, and SYSINDEXES

 If you plan to update some values, keep in mind the following correlations:

  •  COLCARDF and FIRSTKEYCARDF/FULLKEYCARDF (They must be equal for the 1st column and full, if a single column index)
  •  COLCARDF, LOW2KEY and HIGH2KEY. (For non-default COLCARDF LOW2KEY and HIGH2KEY key must be filled with data) and if the COLCARDF is 1 or 2 Db2 uses LOW2KEY and HIGH2KEY as domain statistics to generate frequencies.
  • CARDF in SYSCOLDIST.  CARDF is related to COLCARDF and FIRSTKEYCARDF and FULLKEYCARDF. It must be at a minimum
  • A value between FIRSTKEYCARDF and FULLKEYCARDF if the index contains the same set of columns
  • A value between MAX(colcardf of each col) and the product of all the columns COLCARDFs in the group
  • CARDF in SYSTABLES. CARDF must be equal or larger than any other cardinalities, such as COLCARDF, FIRSTKEYCARDF, FULLKEYCARDF, and CARDF in SYSCOLDIST
  • FREQUENCYF and COLCARDF or CARDF. The number of frequencies collected must be less than or equal to COLCARDF for the column or CARDF for the column group
  • FREQUENCYF. The sum of frequencies collected for a column or column group must be less than or equal to 1

New in Db2 11

In Db2 11, the table SYSSTATFEEDBACK was introduced giving us the first chance to see what the optimizer thinks is missing. This is truly awesome, as then we can tailor our RUNSTATS to generate exactly what the optimizer needs to really validate and generate a good, stable access path. Of course, you should be a little bit careful with this data as too much of a good thing can be bad for you!

New in Db2 12

(Not just the lowercase b!)

In Db2 12, the SYSSTATFEEDBACK was made even more interesting by now externalizing the required RUNSTATS options *directly* into the already existing RUNSTATS profile or, indeed, actually creating a RUNSTATS profile for you.

I think that is really dangerous, as then you could easily flood your system with bogus stats for end user QMF/SPUFI queries that were run “by accident,” or so called “boss queries” where someone with *no* idea of SQL clicks together a highly complex and badly written SQL before letting it run for a weekend. Naturally the SQL gets rewritten by a helpful ever present DBA, but the statistics recommendations have now landed in the profile and will be updated and kept from this point on.

My personal recommendation is to switch off this feature as it is sadly *on* by default!

Here are the ZPARMs of interest

ZPARM STATFDBK_SCOPE set to ALL by default.
ZPARM STATFDBK_PROFILE set to YES by default.
Plus, in table SYSIBM.SYSTABLES column STATS_FEEDBACK is set to Y by default.

Out-of-the box it starts automatically creating (for TYPE=’C’ with NUMCOLS > 1 and TYPE=’F’ or ‘H‘) profiles and updating existing profiles…Here you must manually check the size of your profiles every now and again just to make sure everything is ok!

One other new thing in Db2 12, is that XML columns can get statistics now to help XMLEXISTS get a better access path.

and finally

Do not forget that our little Freeware tool StatisticsHealthCheck will find all bad correlations, old data and badly updated data for you and it is FREE!

So I hope this little round-up of Db2 Catalog Statistics data was interesting, and, as usual, if you have any comments or questions, then please, feel free to mail me!

TTFN

Roy Boxwell

 

 

Posted on

2018-01 Db2 GETPAGES by Insert

 

Troubleshooting on site : how a simple Db2 SQL INSERT statement causes a high GETPAGES rate and how to fix it.

One shop I visit had an interesting problem the other day.  I thought I would share it with you, all as it really was cool.

The game is afoot!

A few days into this brand new year the production machine started to die…they swapped in a bunch of CPs and ZiiPs to try and help but it didn’t. So now the hunt was on – Who was killing the machine and how?

The usual suspects?

Using our WLX (WorkLoadExpert) software, it was quickly found that a simple SQL INSERT statement appeared to be the cause. It was never in the old top 10 “bad guys” list, but was currently at around number four – and rising up the charts faster than Elvis!

First thought were…

  • cross member invalidation, when multiple members insert in a data-sharing group,
  • or a bad usage of INSERT into SELECT syntax.

Deerstalker hat switched to “ON”

Deerstalker hat mode was quickly switched on and the DBA group started looking at *why* a simple  INSERT could cause such a headache… Then someone noticed that an ALTER had been done… At first, it was thought that this could cause the problem but the ALTER timestamp was 03:43 in the morning! No DBA was working at that time. “Aha!” that is the Db2 Database Maintenance size checker and corrector that ALTERs PRIQTY and SECQTY. (Even though they use the sliding scale and allocate with -1 and -1, go figure…)

PBG pains

I happened to be on site for another reason and started looking around. I noticed that the tablespace in question was a PBG with MAXPARTITION 99 and was at the 45th Partition. This partition had been created at 03:43 and was the ALTER event recorded in SYSIBM.SYSCOPY.

Compress me till it hurts!

Now to add some spice to the story, this table space is set to COMPRESS YES. So it would be good to know if the compression dictionary was copied from partition 44 or not… After looking through various documents and also browsing the VSAM cluster, it became apparent that Db2 creates the dictionary “on-the-fly” after a “Db2-determined number of inserts” – I would love to know that number!

It gets fishy…

All of this was actually a red herring in the grand scheme of things as then the Programming Development Team happened to mention that a brand new logging process had been written that was “faster better cheaper.” It had been rolled out to production after passing all tests with flying colours.

The smoking gun…

Using WLX, it was possible to see that for every insert about 500 GETPAGES were being done. Now, on this table, there are *no* indexes. It really is a just a container that gets dumped out every now and again. So I looked at the DDL and saw that the PBG space was indeed created with PCTFREE 0 and FREEPAGE 0 and also with MEMBER CLUSTER, a so-called MC00 space,

but APPEND YES was missing from the table definition and *this* was the reason for the high number of GETPAGES. It was reading all the SPACEMAP pages instead of just inserting at end.

More roads to Rome

Now another way of speeding this all up, would be to ALTER the tablespace to be TRACKMOD NO which would save a lot of time as the SPACEMAP updates are then not done. However, at this firm, they do a ton of Incremental Image Copies on this space, so removing this was not an option.

Listserv Helps

From listserv comes this info:

“APPEND YES avoids ‘scanning’ the table space part for free space before actually extending the dataset beyond its current high used RBA.  How much does this save?  The cost of getpages for each of your table space bit maps.  If this is mostly a busy insert only table then the bitmaps are most likely in the buffer pool and will not require a physical I/O.  APPEND YES will not avoid the get pages to the calculated home page because of the need to check for duplicates.”

Now it was an extremely busy table but the system was also under extreme stress, so it really was rereading the spacemaps. The DBA group scheduled an ALTER to APPEND YES and hope that if this level of INSERTS happens again that the system will not stop!

And finally

Here’s a last bit of info from IBM’s John Campbell all about this:

TRACKMOD

< NO> – is recommended if do not require incremental COPY

  • Db2 does not keep track of updated pages
  • Less space map page updates which will improve performance
  • Less data sharing overhead
  • Can be altered via ALTER TABLESPACE DDL

New APPEND option is provided for INSERT in V9 NFM

  • CREATE/ALTER TABLE … APPEND YES

Can relieve high get pages during space search

  • APPEND search at the end of table space quickly
  • Not going through looking for deleted space
  • Table space size will tend to grow

With high number of concurrent inserts, APPEND could cause bottleneck on the last space map page

  • Using MEMBER CLUSTER option together with APPEND
  • to relieve the contention at the end

 

Hindsight is always good isn’t it?

 

I hope your new year is off to a better start. As usual any queries or criticism gladly accepted!

 

TTFN

Roy Boxwell

Posted on

2017-11 Db2 APAR list: An APAR a day keeps the bugs at bay

Db2 12 APAR – time saving list

With all the talk about “agile” going on, and referring to one of my older 2017-09 newsletters* on APARs, I think it is time to tell you all about another little service that our company offers.

(completely free, simple and no marketing spam)

*Db2 12 Agile & APAR previous newsletter:

2017-09: Db2 12 SQL Access path: Death by APAR :  How many APARs really can affect access paths?

APAR Database

The APAR database can be accessed by anyone with an IBM Userid and you can merrily search to see if the problem that you have hit is already found and fixed, or a fix is in the works.

The problem is: What about the bugs that you do not *know* you have hit?

Get someone else to do the donkey work

The answer is to get someone else to do all the research for you, and deliver the answer by e-mail every month for all current releases of Db2 going back two years.

Who is that donkey? You will never guess…  😉

Three is the key

There are three Excel spread sheets here.

  1. SQL Performance
    The first is RTS. This lists any and all APARs to do with the Real-Time Statistics tables. If you rely on these tables to decide when to run REORG, COPY and RUNSTATS, then you want to make sure that they are being correctly updated, don’t you?

  2. SQL Access Path
    The second is RUNSTATS. RUNSTATS is a critical utility program and contains bugs like any other non-trivial program. I count RUNSTATS bugs as *always* a personal HIPER. I rely on statistics and so does the Db2 Optimizer. If there is bad data here, then your access paths have no chance!

  3. Performance PTFs
    Finally, and the biggest list, is those APARs that have anything to do with SQL Performance and SQL Access paths (If not already in the RUNSTATS list of course!) Performance PTFs are pretty important!

Going hyper over HIPER

All the APARs have their related PTF. A handy little HIPER column tells you whether or not this is a really important fix. As I mentioned, I treat the RUNSTATS ones as personal HIPERS. Also added is a PE indicator when a PTF goes bad on you (PTF in Error) so you can see if you introduced a problem by correcting another.

One Excel Example: The SQL Performance spread sheet

Db2 12 APAR list free for Db2 z/OS: SQL Performance (RTS) - Performance PTFs - SQL Access Path (RUNSTATS) -

This is from the SQL Performance spread sheet and you can see how it looks.

The same table in HTML:

APARCLOSEDSTATUSDb2 10 Db2 11Db2 12HYPERDescription
PI85305
2017-11-01
Closed
N/A
UI51606
UI51601
INEFFICIENT INDEX CHOSEN WHEN INDEX CAN DO INDEX SKIPPING…
 PI85418
 2017-19-25
Modified
UI50098
UI50099
UI50582
PREPARE TAKES LONG TIME AND HIGH CPU IF THE QUERY CONTAINS MA…
 PI85463
New & Closed
 N/A
 N/A
UI51342
TABLESPACE SCAN INSTEAD OF INDEX ACCESS – WITH INDEX HAVIN…

 …

 …

 …

 …

 …

 …

 …

 …

 Interested in making your life a little bit easier and safer?

If you are interested in Db2 APARs and you have *no* time to go and scrabble around the internet trying to find out which APARs are needed, worthwhile, or dangerous, then

just email our technical support techsupport@seg.de and ask to be added to the APAR Mailing list.

It is completely free, you will not get marketing spam and it really is as simple as that!

 

Get AGILE now!

With these handy lists, you can quickly and easily review the state of your Db2, and react quickly and in a timely manner when you have to.

 

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

TTFN

 

Roy Boxwell

Posted on

2017-10 Db2 log size: How big is your LOG?

 

How to validate that your Db2 LOGs are OK ?

Sizing of LOGs changed quite a bit in Db2 11 and Db2 12, so I thought a little recap about the correct sizing and ZPARMS was in order this month.

– Db2 Active logs – how many?

The perennial favourite “just enough” is of course the correct answer! Naturally you want to make sure that about 24 hours’ worth of data is available on your active logs as reading the archives, especially if they have been migrated to tape, is a real performance killer during restart and rollback processing!

The range of active logs you can have is from 2 to 93 with 3 being the default.

– Db2 Archive logs – how many?

Another perennial favourite “just enough” is of course the correct answer! (See more details later in this newsletter.)

The range of archive logs you can have is from 10 to 10,000, with 10,000 being the default.

Where am I?

How can you find out what your system is doing? Simply run the DSNJU004 BSDS stand-alone print routine to look at the current data. Here is an example JCL to do it all for you:

//BSDS     EXEC PGM=DSNJU004
//STEPLIB  DD DSN=<your.exit.lib>,DISP=SHR
//         DD DSN=<your.load.lib>,DISP=SHR
//SYSUT1   DD DSN=<your.BSDS 01>,DISP=SHR
//SYSUT2   DD DSN=<your.BSDS 02>,DISP=SHR
//SYSPRINT DD SYSOUT=*

And here is the output from my little Db2 12 subsystem, cut down to show just the bits I want to use!

ACTIVE LOG COPY 1 DATA SETS
START RBA/TIME       END RBA/TIME          DATE/LTIME DATA SET INFORMATION
-------------------- --------------------- ---------- --------------------
000000000013516C0000  0000000000135387FFFF  2016.068 DSN=DC10.LOGCOPY1.DS01
2017.307  12:32:08.6  2017.308  20:54:21.1  17:27    STATUS=REUSABLE
00000000001353880000  00000000001355A3FFFF  2016.068 DSN=DC10.LOGCOPY1.DS02
2017.308  20:54:21.1  2017.310  07:31:00.0  17:27    STATUS=REUSABLE
00000000001355A40000  00000000001357BFFFFF  2016.068 DSN=DC10.LOGCOPY1.DS03
2017.310  07:31:00.0  ........  ..........  17:27    STATUS=NOTREUSABLE

ARCHIVE LOG COPY 1 DATA SETS
START RBA/TIME        END RBA/TIME         DATE/LTIME DATA SET INFORMATION
--------------------  -------------------- ---------- --------------------
00000000000000000000  000000000000021BFFFF  2016.068  DSN=DC10.ARCHLOG1.D16068.T1743011.A0000001
2016.068  18:37:21.8  2016.068  18:43:00.9  17:43     VOL=SE121D UNIT=SYSALLDA
                                                                 CATALOGUED
000000000000021C0000  0000000000000437FFFF  2016.068  DSN=DC10.ARCHLOG1.D16068.T1743391.A0000002
2016.068  18:43:00.9  2016.068  18:43:39.1  17:43     VOL=SE121D UNIT=SYSALLD
                                                                 CATALOGUED
00000000000004380000  0000000000000653FFFF  2016.069  DSN=DC10.ARCHLOG1.D16069.T0756574.A0000003
2016.068  18:43:39.1  2016.069  08:56:57.3  7:56      VOL=SE121D UNIT=SYSALLD
                                                                 CATALOGUED

From this you can see that I have three active logs that last about one to two days, and the complete archives from the first install are still available – that will probably not be true in your case!

Now, much later in the output, you can see where I am today:

000000000013516C0000  0000000000135387FFFF  2017.308   
DSN=DC10.ARCHLOG1.D17308.T1954212.A0002638
2017.307  12:32:08.6   2017.308  20:54:21.1 19:54 VOL=SE123 UNIT=SYSALLD
                                                            CATALOGUED
00000000001353880000 00000000001355A3FFFF   2017.310
DSN=DC10.ARCHLOG1.D17310.T0631002.A0002639
2017.308 20:54:21.1    2017.310  07:31:00.0 6:31 VOL=SE123F UNIT=SYSALLD
                                                            CATALOGUED

Then the COPY 2 data where you can see that I do not even archive the COPY 2 logs. Again this should not be the case in your shop.

ACTIVE LOG COPY 2 DATA SETS
START RBA/TIME        END RBA/TIME         DATE/LTIME  DATA SET INFORMATION
--------------------  -------------------- ----------  -------------------- 
000000000013516C0000  0000000000135387FFFF  2016.068  DSN=DC10.LOGCOPY2.DS01
2017.307  12:32:08.6  2017.308  20:54:21.1  17:27     STATUS=REUSABLE
00000000001353880000  00000000001355A3FFFF  2016.068  DSN=DC10.LOGCOPY2.DS02
2017.308  20:54:21.1  2017.310  07:31:00.0  17:27     STATUS=REUSABLE
00000000001355A40000  00000000001357BFFFFF  2016.068  DSN=DC10.LOGCOPY2.DS03
2017.310  07:31:00.0    ........  ........  17:27     STATUS=NOTREUSABLE
ARCHIVE LOG COPY 2 DATA SETS
NO ARCHIVE DATA SETS DEFINED FOR THIS COPY

From this data you can work out a few simple things:

1)      How often do you fill an active log?

2)      How many archive logs do you have in the BSDS, and do they actually still exist?

 

  • You can now also check that all your active logs have the same dataset characteristics (Primary Quantity, Secondary, etc.).
  • If you are filling up more than one log per 10 minutes, you may want to check the size of your logs. The size of the active log can be set to an absolute physical maximum of 5,825 cylinders (87,375 TRKs) for non-EAV and 5,817 cylinders (87,255 TRKs) for EAV. Why the strange difference? Because EAV can only allocate in blocks of 21 cylinders due to the way the EAV volumes work. (See later for more information about using more than 65,535 TRKs for an archive log though!)
  • Space allocation:
    The initial allocation of space is done through the Install CLIST and the tailored sample JCL in the DSNTIJIN member. That is where you can override whatever values are generated by Db2 into “better” ones for your system. In the bad old days the allocation was in RECORDS and today I see KILOBYTES most of the time – which is still a bit of a headache when you want to allocate in CYLINDERS but it is all doable, as the DEFINE CLUSTER syntax allows these size definitions:
CYLINDERS(primary)
KILOBYTES(primary)
MEGABYTES(primary)
RECORDS(primary)
TRACKS(primary)

Log ZPARM of Interest

For the log, there is really only one ZPARM of any real importance when it comes to performance and that is OUTBUFF. Set this to be the biggest you can “afford” in terms of real memory, as it takes the memory as fixed! 400,000 KB is the current max, and this is the IBM recommended value. Reading from memory for rollback is a lot quicker than from active log, which is a lot faster than reading the archive log.

From the documentation:

The larger the output buffer, the more likely that a log read request can return the required log records without needing I/O operations. This is a particularly important consideration if the DB2 subsystem is to be used with a data replication product that uses IFCID 306. In DB2 12, log buffers are permanently page-fixed. When you estimate real storage usage, you must use the entire size that you specify for the OUTBUFF parameter. To avoid page-fixing more storage than necessary, carefully choose the setting for OUTBUFF.
 
Choose the largest size that your system can tolerate for the log output buffer. Because the pages for the log output buffer are permanently fixed in real storage, choose the largest size that you can dedicate in real storage. A larger size for the log output buffer might decrease the number of forced I/O operations that occur because additional buffers are unavailable, and can also reduce the number of wait conditions.

Archive logs are different

The size of the archive logs and how many are recorded in the BSDS are input parameters on installation panel DSNTIPA:

DSNTIPA INSTALL DB2 - ARCHIVE LOG DATA SET PARAMETERS
===> _
Enter data below:
1 PRIMARY QUANTITY ===> 125    Primary space allocation in cylinders
2 SECONDARY QTY    ===> 15     Secondary space allocation in cylinders
3 DEVICE TYPE 1    ===> TAPE   Unit name for COPY1 archive logs
4 DEVICE TYPE 2    ===>        Unit name for COPY2 archive logs
5 BLOCK SIZE       ===> 24576  Rounded up to 4096 multiple
6 READ TAPE UNITS  ===> 2      Number of allocated read tape units
7 DEALLOC PERIOD   ===> 0      Time interval to deallocate tape units
8 RECORDING MAX    ===> 10000  Number of data sets recorded in BSDS

In Db2 10 and Db2 11 you also had an allocation unit (ALCUNIT ZPARM) input field with valid values of BLK, TRK or CYL with a default of BLK.

The PRIQTY now has a range from 1 to 4,369 and a default of 125. (Remember the absolute physical maximum earlier of 5,825/5,817 CYLs? That is why you can/should edit the DSNTIJIN member!)

If migrating from Db2 11, the ALCUNIT value is used to convert from BLK or TRK to CYLs in Db2 12. If not yet in Db2 12 then the ALCUNIT tells you what the allocation unit is for the PRIQTY in either BLK, TRK or CYL with a range of 1 to 999,999.

Some further info about the size of the archive logs here. Remember that they are flat files *not* VSAM Linear Datasets:

By default, DFSMS Direct Access Device Space Management (DADSM) limits the space allocation on a single volume to less than 65535 tracks. Therefore, if the archive log data set size can be greater than or equal to 65535 tracks, you need to specify a primary space quantity of less than 65535 tracks. This action forces the archive log data set to extend to a second volume.

Alternatively, the archive log data sets can be allocated by a DFSMS data class that has a DSNMTYP (data set name type) setting of LARGE or EXT. In this case, you can specify a primary space quantity larger than 65535 tracks without extending to a second volume. LARGE indicates that data sets in the data class are to be allocated in large physical sequential format. EXT indicates that data sets in the data class are to be allocated in extended physical sequential format. A setting of EXT is recommended, and it is required for striping of data sets. If you specify EXT, also set the IFEXT (if extended) parameter to R (required) rather than P (preferred).

The SECQTY has a range from 1 to 4,369 and a default of 15. Same rules for ALCUNIT and switching to CYL in Db2 12 apply here as per the PRIQTY.

The recording max just tells you how many archive log dataset names the BSDS can remember. The documentation mentions two very important facts about this number:

When this number is exceeded, recording resumes at the beginning of the BSDS.

You must create image copies of all DB2 objects, probably several times, before the archive log data sets are discarded. If you fail to retain an adequate number of archive log data sets for all the image copies, you might need to cold start or reinstall DB2. In both cases, data is lost.

So there are two top tips for you! Check if you have had a wrap around and check if you have datasets hanging around that Db2 can no longer allocate and use.

Striped or not?

Finally, for logs, the striping of the active logs is nowadays not recommended, as devices are so much faster, but striping and/or compression of the archive logs is still recommended if space is an issue.

From the Db2 Documentation:

In most cases, do not stripe active log data sets. You can use DFSMS to the stripe the logs, but striping is generally unnecessary with the latest devices. Striping increases the number of I/Os, which can increase CPU time and lead to potentially greater DB2 commit times. Striping might improve the performance of batch insert jobs, but it might also harm the performance of online transaction processing. Striping is especially risky for performance if you replicate the logs over long distances.
Consider striping and compressing archive log data sets by using DFSMS. Doing so might speed up the time to offload the logs and the time to recover by using archive logs. However, the performance of DFSMS striping and compression depends on the z/OS release and the types of hardware that you use.

Check your CHECKPOINTS

Getting back to the output of the DSNJU004 job, a few more things like conditional restart recs etc. are output and then the Checkpoint Queue:

 

                    CHECKPOINT QUEUE                
                08:00:31 NOVEMBER 06, 2017          
TIME OF CHECKPOINT       07:31:03 NOVEMBER 06, 2017 
BEGIN CHECKPOINT RBA            00000000001355B750DC
END CHECKPOINT RBA              00000000001355B81B18
END CHECKPOINT STCK             00D365BB89179A529200
TIME OF CHECKPOINT       20:54:23 NOVEMBER 04, 2017 
BEGIN CHECKPOINT RBA            000000000013538813C5
END CHECKPOINT RBA              00000000001353889D76
END CHECKPOINT STCK             00D363EB5D1777CFB000
TIME OF CHECKPOINT       12:32:09 NOVEMBER 03, 2017 
BEGIN CHECKPOINT RBA            00000000001351723326
END CHECKPOINT RBA              0000000000135172D899
END CHECKPOINT STCK             00D362393D4E6F748C00
TIME OF CHECKPOINT       07:30:11 NOVEMBER 03, 2017 
BEGIN CHECKPOINT RBA            0000000000134F5915DD
END CHECKPOINT RBA              0000000000134F59C445
END CHECKPOINT STCK             00D361F5BEB5A941CA00
TIME OF CHECKPOINT       07:42:59 NOVEMBER 01, 2017 
BEGIN CHECKPOINT RBA            0000000000134D34EB53
END CHECKPOINT RBA              0000000000134D357022
END CHECKPOINT STCK             00D35F74E054AE870800

You can see how busy my Db2 system is, anyway your data will be a lot different I am sure!

Checkpoint ZPARMS of interest

CHKTYPE 

LOGRECS/MINUTES/BOTH         MINUTES is default and is what I recommend. This parameter can still have the old value “SINGLE” which then respects the CHKFREQ value as if it were minutes or log records depending on the size of the Number. Change this to Minutes!

CHKFREQ

If LOGRECS how many log records before a checkpoint 1,000 – 16,000,000
If MINUTES how many minutes before a checkpoint 1 – 60

CHKLOGR

If BOTH how many log records before a checkpoint 1,000 – 99,999,999

CHKMINS

If BOTH how many minutes before doing a Checkpoint. 1 – 1439

IBM, and I, recommend a checkpoint every five minutes or so to get optimum rollback and log usage. Naturally your mileage may vary. Also remember that Db2 writes a checkpoint at active log change as well, so try and guarantee that at least ten checkpoints’ “worth” of data fits onto one log dataset.

One extra note arrived in Db2 11:

If the CHECKPOINT TYPE field is set to BOTH, DB2 prevents log checkpoints from being taken too frequently and degrading performance. Log checkpoints are scheduled, but not necessarily immediately taken, when the number of minutes that passes reaches the value of CHKMINS.


Following all this advice should mean that your logs settle down and your system runs a little bit smoother!

 

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

TTFN

 

Roy Boxwell

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