Schlagwort: SQL Codes

Db2 z/OS SQLCODES,

SQLCODES list & review, including their meaning.

Analysis of the behaviour of some reluctant SQL CODES and solutions proposal to get them correctly processing.

Veröffentlicht am

2020-01 How RUNSTATS causes an error SQLCODE

Impossible! I hear you all say… How can a RUNSTATS *cause* an SQL error code? Well, my gentle readers, read on.

Two examples in SPUFI at the end of this newsletter

What we know

We are all, I hope, aware that a badly timed RUNSTATS can cripple your SQL Performance. Just think of a see-sawing, or volatile table, and it gets a RUNSTATS at the zero point… Tablespace scan is then a good access choice… After a couple of million inserts that is probably not the best!

Awful, Implicit Casting

Implicit casting came along a few Db2 releases ago and either made you very happy: “I never have to worry about using apostrophes again!” or very angry: “Developers must know what they are coding!” The thing is, we got it and you cannot *not* do it!

It goes both ways…

The idea behind Implicit casting, in case you don’t know, is that :

The predicate COL1 = 1 and COL1 = ‘1’ are the same to Db2.

It will take that ‘1’ and “cast” it to a variable type that will then be able to be compared to whatever type COL1 is.

So,

  • if COL1 contains a numeric representation of data everything is ok.
  • But if COL1 is CHAR(1) and contains a ’Y’ you then get an SQLCODE -420…

Nasty, nasty business…

Access path is also sub-optimal

When Db2 does implicit casting, it casts to DECFLOAT to then do the comparison etc. (See my DECFLOAT newsletter about what I think about that data type!) Anyway, it is *not* good for performance. In fact, it got so annoying that a few users actually asked for a ZPARM to switch off implicit casting! They actually wanted an error whenever they compared mismatched data types. This request was, of course, turned down.

So where’s the beef?

So, what happened in production was quite simple really:

A query had been running for three years with never a problem. Then one day, after a RUNSTATS, it started returning SQLCODE -420. This was due to the fact that the table processing order had switched, due to the RUNSTATS running at a “bad” time.

This in turn exposed the buggy SQL WHERE predicate that previously had never seen the bad data as it was removed in an earlier branch! This could also happen when the column in question actually contains non-numeric data perhaps dues to a code bug .

See the example SQL at the end  

RUNSTATS Rescue for Db2 z/OS

This user site has our software RUNSTATS Rescue so they quickly got the query up and running, without doing a code change, in a matter of seconds.

Of course, this bad code was discovered in the middle of the year end production freeze so they could not simply change the application code! This code change has been scheduled and will be done in the new year.

Bottom Line

RUNSTATS can cause negative SQLCODEs to be returned and RUNSTATS Rescue buys you the needed time to continue running – even with buggy SQL code!

As always, I would be pleased to hear from you and any war stories you have!

TTFN,
Roy Boxwell
Senior Architect

PS: Just for the record, here are two examples in SPUFI:

 SELECT *
 FROM SYSIBM.SYSDUMMY1                                                  
 WHERE IBMREQD = '1'                                                   
 ;                                                                      
 ---------+---------+---------+---------+---------+---------+---------+-
 IBMREQD                                                                
 ---------+---------+---------+---------+---------+---------+---------+-
 DSNE610I NUMBER OF ROWS DISPLAYED IS 0                                 
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100            
 ---------+---------+---------+---------+---------+---------+---------+-
 SELECT *                                                               
 FROM SYSIBM.SYSDUMMY1                                                  
 WHERE IBMREQD = 1                                                      
 ;                                                                      
 ---------+---------+---------+---------+---------+---------+---------+-
 IBMREQD                                                                
 ---------+---------+---------+---------+---------+---------+---------+-
 DSNE610I NUMBER OF ROWS DISPLAYED IS 0                                 
 DSNT408I SQLCODE = -420, ERROR:  THE VALUE OF A STRING ARGUMENT WAS NOT
          ACCEPTABLE TO THE DECFLOAT FUNCTION     
 DSNT418I SQLSTATE   = 22018 SQLSTATE RETURN CODE                        
 DSNT415I SQLERRP    = DSNXRNUM SQL PROCEDURE DETECTING ERROR            
 DSNT416I SQLERRD    = -245 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION    
 DSNT416I SQLERRD    = X'FFFFFF0B'  X'00000000'  X'00000000'  X'FFFFFFFF'
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION

And the result when the column actually contains a numeric valid value and not “Y”:

CREATE TABLE BOXWELL.SYSDUMMY1 LIKE SYSIBM.SYSDUMMY1       
 ;                                                          
 ---------+---------+---------+---------+---------+---------
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0  
 ---------+---------+---------+---------+---------+---------
 INSERT INTO  BOXWELL.SYSDUMMY1 VALUES ('1')                
 ;                                                          
 ---------+---------+---------+---------+---------+---------
 DSNE615I NUMBER OF ROWS AFFECTED IS 1                      
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0  
 ---------+---------+---------+---------+---------+---------
 SELECT *                                                   
 FROM BOXWELL.SYSDUMMY1                                     
 WHERE IBMREQD = '1'                                        
 ;                                                          
 ---------+---------+---------+---------+---------+---------
 IBMREQD                                                    
 ---------+---------+---------+---------+---------+---------
 1                                                          
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                     
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
 ---------+---------+---------+---------+---------+---------
 SELECT *                                                   
 FROM BOXWELL.SYSDUMMY1                                     
 WHERE IBMREQD = 1                                          
 ;                                                          
 ---------+---------+---------+---------+---------+---------
 IBMREQD                                                    
 ---------+---------+---------+---------+---------+---------
 1                                                          
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

Cool and dangerous! Just like me! LoL ! ! !

Veröffentlicht am

2019-10 DECFLOAT examples: Fun with Numbers

This time I have a nice little real world story for you. Pull up your good old copy of the Db2 9 SQL Guide and I will begin…

What is a number?

Numbers used to be pretty straightforward and easy to spot.

If it was a numeric between -32768 and +32767 it was a SMALLINT or Small Integer, or even a half word for the greying people reading this.

If it was a numeric between -2147483648 and +2147483647 it was an INTEGER or full word. If it was a numeric between 1 – 1031 and 1031 – 1 it was a DECIMAL or packed, or comp-3

Along came BIGINT

After a few years we even got BIGINT (Between -9223372036854775808 and +9223372036854775807), which is much better than DECIMAL if you ask me and is also a double word. In all of these cases, you knew with 100% certainty that you were dealing with a number – Not a column name or anything else!

Then the brown stuff hit the fan… DECFLOAT.

DECFLOAT Arrives

In Db2 9 they introduced the horror that would become DECFLOAT to the Db2 z/OS world. It arrived with little fanfare and a massive amount of ifs and buts, but someone somewhere wanted it…

The problem?

The horrible problem that DECFLOAT dragged in to the party, was the fact that it could contain characters like :

  • SNaN or
  • NaN or
  • INF or
  • Infinity.

Yep, these are all valid DECFLOAT number definitions.

The law of numeric comparisons

The law of comparisons now looks like this:

-NaN < -SNaN < -INF < -0 < 0 < INF < SNaN < NaN

Clear on that?

  • NaN stands for „Not a Number“ with the „quiet“ attribute.
  • SNaN stands for “Signaling Not a Number” which “signals” when it is used (Imagine a Facebook wave here!) and
  • INF is simply infinity.

What problems does this cause?

Well, let’s imagine you have 100’s of tables in a huge Db2 data warehouse, all designed in 1983 with a column called NAN that stands for “National Advertising Notice” – NAN is a good size reduction, still clear about its contents and everything is fine right up until Db2 9 comes along!

Upper and Lower trouble

Now you might think, it is not that bad, IBM are using SNaN and NaN right? Column and Table names have always been upper and lower case sensitive so NAN is not the same as NaN right? Wrong!

The use of inf INFINITY SNAN NAN nAn snAN are all “recognized” and accepted as valid DECFLOAT numbers.

What can you do?

Delimit the column name with double apostrophes is the only correct way. “NAN” for example. Now you go and change 4000 COBOL programs etc.

If you know what you are doing…

Naturally if you are coding ok, you can happily “code” around the problem. Here is some example SQL to show you where you can get badly caught, and to introduce those of you who don’t know about DECFLOAT the joys you can have:

 CREATE TABLE BOXWELL.TESTCASE (NAN  CHAR(10) NOT NULL         
                               ,IBAN CHAR(32) NOT NULL)        
 ;                                                             
 INSERT INTO BOXWELL.TESTCASE VALUES ('TESTNAN' , 'TESTIBAN')
 ;
 COMMIT
 ;    

Let’s do some SELECTs

First up, a normal select against the test table:

 SELECT  NAN                                                 
 FROM BOXWELL.TESTCASE                                       
 ;                                                           
 ---------+---------+---------+---------+---------+---------+
 NAN                                                         
 ---------+---------+---------+---------+---------+---------+
 TESTNAN                                                     
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                      

Correct! Hoorah!

 ---------+---------+---------+---------+---------+---------+
 SELECT "NAN"                                                
 FROM BOXWELL.TESTCASE                                       
 ;                                                           
 ---------+---------+---------+---------+---------+---------+
 NAN                                                         
 ---------+---------+---------+---------+---------+---------+
 TESTNAN                                                     
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                     

Also correct! Delimiters work as planned!

Now the really evil one:

 SELECT COUNT(*)                                                         
 FROM BOXWELL.TESTCASE                                                   
 WHERE IBAN IN (SELECT NAN                                               
                FROM SYSIBM.SYSDUMMY1)                                   
 ;                                                                       
 ---------+---------+---------+---------+---------+---------+---------+-
 DSNT404I SQLCODE = 12, WARNING:  THE UNQUALIFIED COLUMN NAME NAN WAS     
          INTERPRETED AS A CORRELATED REFERENCE                           
 DSNT418I SQLSTATE   = 01545 SQLSTATE RETURN CODE                         
 DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING ERROR             
 DSNT416I SQLERRD    = 0 0  1  1143510784  0  0 SQL DIAGNOSTIC INFORMATION
 DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000001'  X'44289700' 
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION             
                                                                          
 ---------+---------+---------+---------+---------+---------+---------+-
           0                                                              
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                                  

You see what has happened here?

The SQL has “incorrectly” used NAN but gets only a +12 Warning from the parser. It then happily runs on and gives a result. Is this what should happen? I don’t think so!

It gets worse

Now try these queries:

 SELECT NAN                                                  
 FROM SYSIBM.SYSDUMMY1                                               ;                                                           
 ---------+---------+---------+---------+---------+---------+
                                                             
 ---------+---------+---------+---------+---------+---------+
 +NAN                                                        
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1                     

Imagine having typos in your SELECT lines…

 SELECT sNAN                                          
 FROM BOXWELL.TESTCASE                                
 ;                                                    
 ---------+---------+---------+---------+---------+---
                                                      
 ---------+---------+---------+---------+---------+---
 +SNAN                                                
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1              

Cool huh?

What can you do?

Monitor SQLCODE +12 – These should *never* really happen in production! How? Well you can use our WorkLoadExpert to discover these problems in two distinct ways.

  1. Capturing the SQL workload “sees” the SQLCODEs when processed

  2. If you have the SQLCODE Use Case then you get all SQLCODEs that are output by Db2

Doing it right

The recommendation is actually to CAST any of these literal values like CAST(‘snan’ as DECFLOAT) which removes *any* chance of getting it “confused” with a column name or anything else!

 SELECT CAST('NAN' AS DECFLOAT)            
 FROM SYSIBM.SYSDUMMY1                     
 ;                                         
 ---------+---------+---------+---------+--
                                           
 ---------+---------+---------+---------+--
 +NAN                                      
 DSNE610I NUMBER OF ROWS DISPLAYED IS 1   

Where else?

Now that these beasts are also allowed in indexes you must really check all of their usage.

Have fun!

As always I would be pleased to hear from you!

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2019-06 A little quote from Terry Purcell: It just takes one

This is a little quote from Terry Purcell which I think should be understood much better than it possibly is today!

Really? Just one?

The quote is based on the idea that :

One single SQL statement can bring your entire system to its knees.

Many people think this is a huge exaggeration – However it is not!

It happened to me

This is not a friend of a friend story, but a story that really happened. Names have, of course, been changed but the story itself is 100% accurate.

The story

It was a normal Db2 PTF Maintenance night…

At this shop they always put maintenance in at midnight on Saturdays. All done automatically, and then roll the update around the data-sharing group so that, relatively quickly, all sub-systems have the same PTF level.

So far so normal

Sunday is not that busy at the site and no-one noticed any change.

Monday was different

Monday morning the machine started acting up. Customers could not login and the help desk was 100% busy. The lead DBA, George, had by around 08:00 a.m. received the first escalation to management and so the number of calls went up.

 

The bad guy

Using our WorkloadExpert (WLX) software together with our Bind ImpactExpert (BIX) the other DBAs, Fred and Ringo, quickly identified one single SQL statement that was taking 30,000 times more resources than in the prior week!

This was an SQL with table functions and LEFT, INNER JOINs etc.

What was it?

The comparison in BIX showed that all that had changed was one index access was now matchcols one instead of matchcols two! That was it! This one tiny change on one little SQL killed this machine…

The fix?

They decided to roll back the PTFs and quickly did this and everything returned to normal…

The lesson?

Always test any PTF before you go live – as just one SQL can kill ya!

Terry had indeed warned us all!

How can we help?

Well, thank you for asking: Our product CDDC contains an SQL replay and compare function that would have spotted this SQL in two different complementary ways. Firstly the BIX part of CDDC would have spotted it straightaway and secondly the replay itself would have thrown this out as a major outlier and bad guy candidate!

Different ways to Rome

They could possibly even have fixed this without backing out the PTF apply. They could have tried using our RUNSTATS Rescue to attempt to use older statistics and see if one of them would have given them a matchcols two access path. This time, however, with all the managers breathing down their necks it was decided – Undo all the changes!

In your shop

How would you have handled this situation in your shop?

Would/Could you have seen this before it happened?

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2018-12: Db2 Checklist – SQLCODEs as never seen before

With this SQLCODE list, from the “bad guys” through to the “odd guys”  – e.g. the SQLCODE 100 – and then onto the „not so bad guys“, you can see a practical list of current SQLCODEs in production from around the globe, including their meaning. This review also analyzes the behaviour of some SQLCODEs and also proposes some solutions to get your Db2 z/OS programs correctly processing these SQLCODEs.

Well perhaps not quite that dramatic! Our SQL WorkLoadExpert for z/OS software (aka WLX) has been running at customer sites for a while now with the “Failing Statements” Use Case active. So I thought it would be interesting to review the eclectic mix of SQLCODEs that it receives…

Bad Guys

These all have negative SQLCODE values and so are bad…but look at the mix…

     SQLCODE        COUNT
     -------        -----
      -30081          209  Communication error
      -30080            1  Communication error
      -20398            3  Error during XML Parsing
      -20385            1  PENDING DDL caused error
      -20289            3  Invalid string unit
      -20223            4  Encryption facility not available
      -20186            5  A dynamic SQL clause is invalid
      -20183            1  ALTER ADD PARTITION invalid syntax
      -20008            2  Attempt to use unsupported feature
       -4743            3  Attempt to use new function
        -950            4  LOCATION name invalid
        -926            1  ROLLBACK not valid in IMS, CICS or RRSAF
        -925          816  COMMIT not valid in IMS, CICS or RRSAF
        -913            6  Deadlock or Timeout
        -911            8  Deadlock or Timeout
        -905            3  RLF timeout
        -904          165  Unavailable resource
        -876            2  xxx cannot be created or altered
        -874            1  Encoding scheme conflict
        -846           17  Invalid IDENTITY or SEQUENCE
        -822            1  SQLDA contains invalid data address
        -811       39,231  More than one row returned for a SELECT
        -805          389  Package not found in PLAN
        -804           21  SQLDA is incorrect
        -803    4,437,491  Duplicate INSERT attempted
        -713            2  Replacement value is invalid
        -669            8  Object cannot be dropped
        -650            2  ALTER cannot be executed
        -647           18  BP for implicit not activated
        -644            5  Invalid value for keyword
        -637            3  Duplicate keyword or clause
        -628            5  Clauses are mutually exclusive
        -612            4  LOCKMAX 0 only if LOCK SIZE TS or TAB
        -607           11  Operation is not defined for this object
        -601           49  Object already exists
        -556            1  Priv cannot be revoked
        -553            1  Authorization not valid (SET CURRENT SQLID)
        -552            1  Auth does not have the privilege
        -551            8  Authorization failure
        -530            4  Insert or Update of Foreign Key invalid
        -525            9  Statement in error at bind time
        -518            8  EXECUTE does not identify a prepared stmt
        -516           15  Describe for a not prepare
        -514            8  Cursor not in a prepared state
        -512            9  Statement reference remote obj invalid
        -502            2  Cursor in an OPEN is already OPEN
        -501   18,825,773  Cursor in a FETCH or CLOSE is not OPEN
        -471        1,832  Procedure/Function failed
        -440            4  Routine not found
        -433            9  VALUE is too long
        -421            8  Operands of SET not the same column count
        -420          266  String value is not acceptable to function
        -419            2  Decimal divide invalid – negative scale
        -418            2  Statement contains invalid parameter markers
        -413           14  Overflow or Underflow
        -408           12  VALUE incompatible with target
        -407           22  UPDATE, INSERT, SET is NULL column NOT NULL
        -406           12  Calculated/Derived numeric out of range
        -405            8  Numeric constant out of range
        -401            2  Incompatible data types
        -390            5  Object not valid where it is used
        -338            9  An ON clause is invalid
        -327            1  Row is outside the bounds of last partition 
        -313           12  Number of host vars not equal par.markers 
        -312            4  Variable not defined or unusable
        -311           98  Length of input host variable out of bounds
        -310      154,274  Decimal host var contains no decimal data
        -305    2,244,618  NULL value cannot be assigned
        -214            1  An expression is invalid
        -304            1  Value cannot be assigned data type range
        -303            1  Value cannot be assigned data type incompat
        -302           13  Value of input var/arg x too large
        -301            3  Value of input var/arg x cannot be used
        -220           10  Column in PLAN_TABLE is incorrect
        -219          916  Required PLAN_TABLE does not exist
        -214            1  Expression invalid
        -208            7  Order by invalid
        -206           90  x is not valid
        -205           11  x is not a column of table
        -204        7,665  x is an undefined name
        -203            1  A reference to col x is ambiguous
        -199           30  Illegal use of keyword
        -196            4  Col cannot be dropped
        -183            5  Out of range for date/timestamp
        -181    1,156,892  String expression of datetime is invalid
        -180            8  Date, Time or Timestamp invalid
        -171            4  Data type/len/val of arg x of y is invalid
        -170            8  Invalid no. args
        -158            1  No.cols not equal result table
        -151            4  Update of catalog col not allowed
        -138            4  2nd or 3rd arg in SUBSTR out of range
        -126            2  Select contains UPDATE and ORDER BY
        -122            6  Col or exp in the select list is not valid
        -120            1  Aggregate or OLAP not valid
        -117           29  No. values not equal no. columns
        -109            7  Clause is not permitted
        -107            3  Name is too long
        -104           23  Illegal symbol
        -103            1  Invalid numeric constant
         -84            2  Unacceptable SQL statement
         -10            1  Non-terminated string constant

Now the ones that jump out are

811     Badly written SELECT that “normally” returns one row – Code must be corrected or the SELECT changed into a CURSOR.

803   This is the all-time classic “Should I insert or update?” problem. It could well be that MERGE is actually a better way forward.

501  This is down to the application development logic being “Always first CLOSE the cursor” which is naturally madness but is “how it is” – This needs a code change to just comment out the crazy CLOSE!

310   Oh dear! Looks pretty nasty to me…

305  Null indicators “forgotten” – quite probably an SQL coding error. Typically a LEFT OUTER JOIN style SQL that “normally” gets a match. SQL and/or code must be checked and changed. COALESCE can help here!

181  What is going on with datetime formats???

Not so Bad Guys

These all have positive SQLCODE values and so are not so bad…

      SQLCODE        COUNT
      -------        ----
          98          397  Dynamic SQL ends in a ;
         162            6  TS in check pending
         203            2  Qualified column name resolved using non-unique
                           or unexposed name
         222       21,652  Reading a hole with sensitive scrollable
         238           23  SQLDA not set-up correctly but enough space
                           is there for the LOB descripton
         347           37  Recursive SQL has no “brake” and could loop
         354          289  Multi-row fetch got warnings. GET DIAGNOSTICS
                           must be used
         403            2  ALIAS points to non-existent table
         445            2  VALUE has been truncated
         466           25  Stored proc returned nnn sets of data
         535            4  Positioned UPDATE/DELETE may depend on the order
                           of rows (self-referencing constraint)
         562            1  GRANT ignored as already held
         585        1,643  Collection appears more than once 
                           when setting a special register
         610            7  DDL has caused an object to enter PENDING
       20272            1  TS converted to table part from index part
       20520       80,115  Deprecated function usage

Here the ones that jump out are

222     This is normal if using sensitive scrollable cursors, probably ok.

585      Why double set the SCHEMA? Just wastes CPU cycles

20520  Whoops! Which feature/function do they mean? I have often seen old PLAN_TABLE usage causing this.

Weird Bad Guys

These two are just odd…

   SQLCODE    COUNT
   -------    -----
         0       25  Everything was ok or perhaps not?
                     Could be warnings were issued…
       100      117  Not found, End of Cursor or ???

From the documentation 100 is actually more interesting than you would think

Explanation:
One of the following conditions occurred:

  • No row met the search conditions specified in an UPDATE or DELETE statement.
  • The result of a SELECT INTO statement was an empty table.
  • The result of the subselect of an INSERT statement is empty.
  • A FETCH statement was executed when the cursor was positioned after the last row of the result table.
  • No available rows qualified for return when SKIP LOCKED DATA was specified with isolation level CS or RS.
  • A FETCH statement that returns a rowset was issued, but there were not enough rows after the current cursor position to reposition the cursor on a full rowset. The cursor has been positioned on a partial rowset. If a target was specified, data was returned only for the number of rows that were actually fetched for the partial rowset. The number of rows that were returned is in field SQLERRD3 of the SQLCA.

– When a SELECT statement is executed using SPUFI, this SQLCODE indicates normal completion.

– This SQLCODE is also issued when LOB data cannot be returned. This situation can occur when an application is running with isolation level UR and another application has locked the LOB table space.

Ignore the usual suspects

With WLX you can also exclude a list of SQLCODEs that you are not interested in. I would be tempted to add 0, 98, 100, 222, 394, 466 and 535 as a starting point.

Does it matter?

Well of course the answer is “It Depends!”
If the programs are correctly processing the SQLCODEs then everything is fine. If, however, error checking is missing, incorrect WHENEVER logic is used, or stuff is simply ignored, then it is probably one of the sources of bad data at your site. It can also cost you money as cpu is not really free.

Measured response

Just for fun (yes, I’m weird like that) I coded a COBOL program that connected to Db2 and then did 1,000,000 CLOSE cursors. On our little machine, the 1,000,000 -501’s caused 68.10 Seconds of CPU – and this was just the CLOSE, no error handling or WHENEVERs involved at all – so the saving could/should be even more. With the CLOSE not being there the job took just 0.11 Seconds of CPU.

Then I did another test, because I know a JAVA framework that loves ending with ROLLBACK, ROLLBACK, COMMIT (I kid you not dear readers!),

and here’s how my 1,000,000 test results look:

Program with Rollback, Rollback, Commit               94.40 Seconds of CPU

Program with Rollback, Commit                               71.10 Seconds of CPU

Program with Rollback                                               19.62 Seconds of CPU

Program with Commit                                                 58.13 Seconds of CPU

So the best thing for performance is to just ROLLBACK! Lol!

How are you?

What do you have at your shop in production? Do you have any tips or tricks about these, or any other, SQLCODEs that you have experienced?

 

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

 

Veröffentlicht am

2017-09 Db2 12 SQL Access paths: 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

 

 

Veröffentlicht am

2015-04 SQLCODEs of interest

 

Are your DB2 11 SQLCODES up-to-date?

I originally wanted to call this newsletter “SQLCODE101” but not all of my international readers may understand… Anyway, this newsletter is dedicated to that small group of people who, like me, share an interest in SQLCODEs.

The “newest” SQLCODES are not updated in copy book style checking routines

One thing I noticed years ago, is how often the “newest” codes are not updated in copy book style checking routines.

I was at one customer site once where their copy book entry looked like this:

 88 SQLCA-ERROR                VALUE -999 THRU -001.

And I choked on my coffee!
Scary huh?

It actually got worse when I then saw:

88 SQLCA-WARNING             VALUE +101 THRU +999.

Now these are soooo bad it hurts the eyes!
But as they were lurking in copy book code, they were simply never seen…

 

DB2 11, current documentation

As of DB2 11, current documentation shows the actual ranges are -30106 to -7 and +12 to +30100.

So the above COBOL should really look like this:

88 SQLCA-ERROR               VALUE -32000 THRU -001.
88 SQLCA-WARNING             VALUE   +1   THRU +99
+101 THRU +32000.

 

The SQLCODE +100 is special, as it is “no row found” or “end of cursor”.

 

What other SQLCODEs are of general interest then?

Well here’s my list in no particular order:

 

-803 (Duplicate key)

This is sometimes called the “lazy update check”. First do an insert, if it bounces with a -803 make the key unique or change it to an update statement. Now this logic is fine if the majority of the inserts succeed, but if the majority are failing it is probably time to swap the logic around and try an update, then if you get a +100 do an insert instead. This also stops any “negative SQLCODE monitors” from firing off too many false positives!

 

-802 and its younger brother +802 (Numeric exception)

Now do you see that I have two codes here? The +802 means a numeric exception has occurred, but the SQL carries on with -2 set in the indicator variable:

 

 +802 EXCEPTION ERROR

+802 EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number

Explanation: The exception error exception-type occurred while performing one of the following operations on a field that has a data-type of DECIMAL, FLOAT, SMALLINT, or INTEGER:

 

Whereas the –802 is a bit different:

 

 -802 EXCEPTION ERROR

-802 EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number

Explanation: An exception error has occurred in the processing of an SQL arithmetic function or arithmetic expression. The exception error occurred in one of the following areas:

  • In the SELECT list of an SQL SELECT statement.
  • In the search condition of a SELECT, UPDATE, MERGE, or DELETE statement.
  • In the SET clause of the UPDATE operation.
  • During the evaluation of an aggregate function.

So you must really take good care here!

 

 Very interesting SQLcodes

 

-514 and -518 (Prepared SQL gone)

Now these are *very* interesting and you should have a quick look in your monitor to see how many of these you get. I really hope that all your SQL code has retry logic to rePREPARE the SQL that has gone. I was rather surprised to see prepared statements in current active use getting these messages. But if the data is flushed from the DSC then this is what can happen!

Here’s a little snippet from one of Namik Hrle’s DSC presentations:

 

It gives a little “hint” that the prepared statement can be thrown from the cache at any time, (not just the obvious bad guys like RUNSTATS etc.)

 

-331, +335, +445 and +20141 (Truncation or Character Conversion problem)

Here, only the -331 (CHARACTER CONVERSION CANNOT BE PERFORMED BECAUSE A STRING, POSITION position-number, CANNOT BE CONVERTED FROM source-ccsid TO target-ccsid, REASON reason-code) is actually returning an error code, while all the others – +335 (use of substitute character), +445, & +20141 (Truncation) – carry on regardless. This might not be what is actually desired.

 

+222 (Positioned on a hole)

You have positioned onto a deleted/updated row in a SENSITIVE STATIC cursor – These warnings should just trigger another fetch until either table end or a real row is found.

 

-911 (Timeout or Deadlock)

Now this beauty actually does the ROLLBACK for you, so you must be aware of that when you get this bad guy!

 

 

Do you have any SQLCODEs that you treat specially?

I would love to hear from you if you do!

 

 

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

TTFN

 

Roy Boxwell