Central Ohio Db2 Users Group – December 2017

CODUG – Central Ohio Db2 Users Group – December 2017

December 05th, 2017

SEGUS & SOFTWARE ENGINEERING present

Db2 z/OS Lies, Damn Lies, and Statistics

..Lies, damn lies, and statistics… – Benjamin Disraeli, Prime Minister of England (1868, 1874-1880)

The above line may, or may not, have been spoken well over 100 years ago, but the need for statistics and, above all else, accurate statistics is more important than ever in the Db2 world of today.

Presentation Outline

  • Db2 RUNSTATS basics & catalog tables and Columns used for access path
  • IBM recommendations through the ages : from Db2 V3 to Db2 12
  • Db2 RUNSTATS advanced
  • SYSCOLDIST explained
  • RUNSTATS real world Q&A :
    use of SAMPLE, COLGROUP, PROFILE, REOPT (ONCE), TABLESAMPLE SYSTEM, HISTOGRAM, …
  • RUNSTATS reversal

Speaker Biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in Db2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’s involved in the development of SE’ maintenance and recovery Solutions.

Michigan Db2 Users Group – December 2017

MDUG – Michigan Db2 Users Group

December 6th, 2017

SEGUS & SOFTWARE ENGINEERING present

1- 25 Years of Missed Opportunities? SQL Tuning Revisited

2- Db2 z/OS Lies, Damn Lies, and Statistics


1- 25 Years of Missed Opportunities? SQL Tuning Revisited

“But that’s the way we’ve always done SQL Tuning” – sound familiar?

The definition of madness is to keep doing the same thing and expecting a different outcome.
Find out how to stop the madness.

Nothing is more expensive than a missed opportunity” (H. Jackson Brown, Jr).

This is especially true when it comes to SQL Tuning!

There are many low-hanging fruits within easy grasp of anyone tasked with tuning their systems. But how do you know what is dangling within reach if you can’t see it?

Find out how a modern DB2 z/OS SQL warehouse can collect and store all executed static and dynamic SQL (plex-wide) with basically no overhead.

By comparing SQL statements side-by-side, the “easy pickings” will immediately become apparent. But why stop there? Experts can effortlessly dig deeper and find the totally hidden gems: Disc Problem Detection, Delay Detection, Never executed SQL, SQL Timeline. Precisely pinpoint specific areas to target and get the most out of your DB2 system – while reducing costs.

Outlines

1 – Tuning SQL – how we always done it

2 – Single SQL, package, application

3 – Tuning SQL – year 2004 – ACCESS PATH comparison and simulation

4 – Tuning SQL Revisited – A new methodology

5 – Harvesting the low hanging fruit


2- Db2 z/OS Lies, Damn Lies, and Statistics

..Lies, damn lies, and statistics… – Benjamin Disraeli, Prime Minister of England (1868, 1874-1880)

The above line may, or may not, have been spoken well over 100 years ago, but the need for statistics and, above all else, accurate statistics is more important than ever in the Db2 world of today.

Presentation Outline

  • Db2 RUNSTATS basics & catalog tables and Columns used for access path
  • IBM recommendations through the ages : from Db2 V3 to Db2 12
  • Db2 RUNSTATS advanced
  • SYSCOLDIST explained
  • RUNSTATS real world Q&A :
    use of SAMPLE, COLGROUP, PROFILE, REOPT (ONCE), TABLESAMPLE SYSTEM, HISTOGRAM, …
  • RUNSTATS reversal

Speaker Biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in Db2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’s involved in the development of SE’ maintenance and recovery Solutions.

Northeast Ohio Database Users Group – December 2017

NEODBUG – Northeast Ohio Database Users Group – December 2017

December 07th, 2017

SEGUS & SOFTWARE ENGINEERING present

1- 25 Years of Missed Opportunities? SQL Tuning Revisited

2- Db2 z/OS Lies, Damn lies, and Statistics


1- 25 Years of Missed Opportunities? SQL Tuning Revisited

“But that’s the way we’ve always done SQL Tuning” – sound familiar?

The definition of madness is to keep doing the same thing and expecting a different outcome.
Find out how to stop the madness.

Nothing is more expensive than a missed opportunity” (H. Jackson Brown, Jr).

This is especially true when it comes to SQL Tuning!

There are many low-hanging fruits within easy grasp of anyone tasked with tuning their systems. But how do you know what is dangling within reach if you can’t see it?

Find out how a modern DB2 z/OS SQL warehouse can collect and store all executed static and dynamic SQL (plex-wide) with basically no overhead.

By comparing SQL statements side-by-side, the “easy pickings” will immediately become apparent. But why stop there? Experts can effortlessly dig deeper and find the totally hidden gems: Disc Problem Detection, Delay Detection, Never executed SQL, SQL Timeline. Precisely pinpoint specific areas to target and get the most out of your DB2 system – while reducing costs.

Outlines

1 – Tuning SQL – how we always done it

2 – Single SQL, package, application

3 – Tuning SQL – year 2004 – ACCESS PATH comparison and simulation

4 – Tuning SQL Revisited – A new methodology

5 – Harvesting the low hanging fruit


2- Db2 z/OS Lies, Damn lies, and Statistics

..Lies, damn lies, and statistics… – Benjamin Disraeli, Prime Minister of England (1868, 1874-1880)

The above line may, or may not, have been spoken well over 100 years ago, but the need for statistics and, above all else, accurate statistics is more important than ever in the Db2 world of today.

Presentation Outline

  • Db2 RUNSTATS basics & catalog tables and Columns used for access path
  • IBM recommendations through the ages : from Db2 V3 to Db2 12
  • Db2 RUNSTATS advanced
  • SYSCOLDIST explained
  • RUNSTATS real world Q&A :
    use of SAMPLE, COLGROUP, PROFILE, REOPT (ONCE), TABLESAMPLE SYSTEM, HISTOGRAM, …
  • RUNSTATS reversal

Speaker Biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in Db2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’s involved in the development of SE’ maintenance and recovery Solutions.

Rotten Results from RUNSTATS Require Rescue

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

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

 

RUNSTATS are good aren’t they?

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

 

Daily fire fighting

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

 

Why do the Access Paths go “wrong”?

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

 

Timing is everything

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

Basic rules are:

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

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

Is there a way back from the abyss?

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

Yes! There is a way back from the abyss

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

Why a tool?

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

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

Sounds like a lot of work.

What else must you do?

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

Hang on – What about PLAN STABILITY?

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

It all works together

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

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

TTFN,

Roy Boxwell

 

Can you guess how interesting the DSN_PREDICAT_TABLE really is?

DB2 z/OS literal replacement:

Do you know the queries to list the Dynamic SQL which have literals or Parameter markers?

Here is a very handy method to (fairly) quickly see if you have a literal problem that is killing the DSC or causing access path problems.

 

I was asked by a customer the other week, to help them out with a classic DB2 SQL Performance problem involving dynamic SQL. Should Literals or Parameter markers be used in dynamic SQL? Now, of course, the classic answer is: “It depends.” But this customer wanted to go a stage further and get a list of all the dynamic SQL which had literals in the text.

 

EXPLAIN to the rescue!

Naturally an EXPLAIN must be the starting point, because manually parsing the SQL text is incredibly difficult. If you have paid for the DB2 Optimizer—and the ability of EXPLAIN to output to seventeen tables—why not use it and see what you get?

Manual time

After trolling through various manuals, it quickly becomes apparent that the table of choice is the DSN_PREDICAT_TABLE, as it contains at least one row for each predicate and, after all, it is predicates that are truly interesting for the optimizer. (Naturally DSC usage also hangs on the use, or not, of literals also in the SELECT—but that is another story!)

What is in the table?

The contents are quite simple really:

SELECT  A.QUERYNO                 Identification

      , A.QBLOCKNO                Identification

      , A.APPLNAME                Identification

      , A.PROGNAME                Identification

      , A.PREDNO                  Identification

      , A.TYPE                    Type of op AND, OR, EQUAL etc

      , A.LEFT_HAND_SIDE          Column/Value/colexp etc

      , A.LEFT_HAND_PNO           Child predicate number

      , A.LHS_TABNO               Table no.

      , A.LHS_QBNO                Query Block no.

      , A.RIGHT_HAND_SIDE         As left hand

      , A.RIGHT_HAND_PNO          As left hand

      , A.RHS_TABNO               As left hand

      , A.RHS_QBNO                As left hand

      , A.FILTER_FACTOR           Estimated FF

      , A.BOOLEAN_TERM            Whole WHERE is Boolean?

      , A.SEARCHARG               DM or RDS?

      , A.JOIN                    Simple join or not?

      , A.AFTER_JOIN              Predicate after/during join?

      , A.ADDED_PRED              T Transitive clos., B Bubble,
                                  C correlation,

                                  J Join, K like, L local, P push down,

                                  R page range, S simplification

      , A.REDUNDANT_PRED          Is the predicate redundant?

      , A.DIRECT_ACCESS           ROWID Possible?

      , A.KEYFIELD                Is the predicate in indexes?

      , A.EXPLAIN_TIME            Identification

      , A.CATEGORY                IBM Internal use

      , A.CATEGORY_B              IBM Internal use

      , A.TEXT                    First 2000 bytes of text

      , A.PRED_ENCODE             IBM Internal use

      , A.PRED_CCSID              IBM Internal use

      , A.PRED_MCCSID             IBM Internal use

      , A.MARKER                  Host vars, parameter markers,
                                  special regs

      , A.PARENT_PNO              If a root predicate then zero

      , A.NEGATION                Is NOT used?

      , A.LITERALS                Literals separated by colons

      , A.CLAUSE                  HAVING, ON, WHERE or SELECT

      , A.GROUP_MEMBER            Identification

      , A.ORIGIN                  Origin of predicate. Blank, C, R or U

      , A.UNCERTAINTY             Level of uncertainty

      , A.SECTNOI                 Identification

      , A.COLLID                  Identification

      , A.VERSION                 Identification

--V11 , A.EXPANSION_REASON        Archive or Temporal table indicator

FROM BOXWELL.DSN_PREDICAT_TABLE A

 

The first four columns are used for joining to your existing EXPLAIN tables (PLAN_TABLE etc.)

Columns of interest

Now what interested me straightaway, were the columns MARKER and LITERALS. Looking in the Managing Performance documentation you see that these columns:

MARKER CHAR(1) NOT NULL WITH DEFAULT

Whether this predicate includes host variables, parameter markers, or special Registers.

LITERALS VARCHAR(128) NOT NULL

This column indicates the literal value or literal values separated by colon symbols.

 

So now it looks quite easy just do a select where LITERALS is non blank. Of course that fails miserably…

First attempt

Here’s an SQL that shows what comes out:
DSN; predicat table, query1-DB2-z-OS-literal-replacement

DSN; predicat table, query1-DB2-z-OS-literal-replacement

This looks like lots of literals, but is actually just one WHERE predicate and one ON being broken down. So I thought “Aha! The PARENT_PNO must be the problem.” Sure enough, when you add AND PARENT_PNO = 0 to the SQL it now Returns:

DSN; predicat table, query1-DB2-z-OS-literal-replacement

Now all of the ON data has vanished, so you must add an OR into the query:

Second attempt

Giving us now this Output:

DSN; predicat table, query1-DB2-z-OS-literal-replacement

The COLEXP row SQL text looks like:

SUBSTR(DIGITS("A"."CORR_TYPE"),1,10)

So we can also exclude these rows from the select.

DSN; predicat table, query1-DB2-z-OS-literal-replacement

Still duplicates?

This*still* looks like duplicates, but now pushed down to the ON clause in this context. Final fix is to make sure that the LHS_TABNO and RHS_TABNO are both equal to zero. Now we have the “literal finder” SQL:

DSN; predicat table, query1-DB2-z-OS-literal-replacement

Host with no colon?

Which gives (excluding the first columns for clarity!):

DSN; predicat table, query1-DB2-z-OS-literal-replacement

See the HV2 and then the list of HVs? Now this is not documented at all! A comma separated list of host variables… super…

All we can now do, at least in standard SQL, is split the output into two distinct blocks, one where MARKER is ‘N’ so no host variables or special registers are involved, and one where they are! It should be easy to remove the HVs and then see if the LITERALS column is empty—or not—but that is a bit more than a simple newsletter can possibly do!

And Finally

So now the final two queries:

SELECT QUERYNO , QBLOCKNO , PREDNO                 
      ,LITERALS
      ,TEXT                                        
FROM BOXWELL.DSN_PREDICAT_TABLE                    
WHERE NOT LITERALS = ''                            
  AND ((CLAUSE = 'WHERE'                           
    AND PARENT_PNO = 0)                            
    OR CLAUSE = 'ON')                              
  AND NOT RIGHT_HAND_SIDE = 'COLEXP'               
  AND LHS_TABNO = 0                                
  AND RHS_TABNO = 0                                
  AND MARKER    = 'N'                              
ORDER BY QUERYNO , QBLOCKNO , PREDNO               
;                                                  
SELECT QUERYNO , QBLOCKNO , PREDNO                 
      ,LITERALS
      ,TEXT                                        
FROM BOXWELL.DSN_PREDICAT_TABLE                    
WHERE NOT LITERALS = ''                            
  AND ((CLAUSE = 'WHERE'                           
    AND PARENT_PNO = 0)                            
    OR CLAUSE = 'ON')                              
  AND NOT RIGHT_HAND_SIDE = 'COLEXP'               
  AND LHS_TABNO = 0                                
  AND RHS_TABNO = 0                                
  AND MARKER    = 'Y'                              
ORDER BY QUERYNO , QBLOCKNO , PREDNO               
;

This gives you a very handy method to (fairly) quickly see if you have a literal problem that is killing the DSC or causing access path problems at your shop.

I hope you liked this month’s topic.

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

TTFN,

Roy Boxwell

Once again for your cut and paste (the queries presented first of all as a screenshot):

Query 1

SELECT CLAUSE, PARENT_PNO, SUBSTR(RIGHT_HAND_SIDE , 1 , 8)
      ,LHS_TABNO, RHS_TABNO                               
      ,MARKER, LITERALS                             
FROM BOXWELL.DSN_PREDICAT_TABLE                           
WHERE NOT LITERALS = ''                                   
-----+-------+---------+---------+---------+---------+---------+
CLAUSE PARENT_PNO        LHS_TABNO  RHS_TABNO  MARKER  LITERALS
-----+-----+---------+---------+---------+---------+-----------+
WHERE      0                    0           0  N       0,1,'W' 
WHERE      1  VALUE             2           0  N       0,1     
WHERE      1  VALUE             3           0  N       'W'     
ON         1  COLEXP            2           0  N       1,10    
ON         1  VALUE             2           0  N       'F'     
ON         1  VALUE             2           0  N       'en_US'

 

 

-----+---------+---------+---------+---------+---------+---------+------
CLAUSE     PARENT_PNO            LHS_TABNO  RHS_TABNO  MARKER  LITERALS
-----+---------+---------+---------+---------+---------+---------+------
WHERE               0                    0          0  N       0,1,'W'

 

-----+--------+--------+---------+---------+-----------------
CLAUSE PARENT_PNO     LHS_TABNO RHS_TABNO MARKER LITERALS
-----+-------+--------+---------+----------------------------
WHERE    0                 0            0  N     'X',' ',
                                                  220,219,1,10,'F’
ON       1  COLEXP         2            0  N      1,10
ON       1  VALUE          2            0  N     'F' 
ON       1  VALUE          2            0  N     'en_US'
WHERE    0  VALUE          3            0  N     'X'

 

-----+---------+---------+---------+---------+----------+----
 CLAUSE PARENT_PNO    LHS_TABNO  RHS_TABNO  MARKER LITERALS
 -----+---------+---------+---------+---------+---------+----
 WHERE   0                0              0  N      'X','
                                                   ',220,219,1,10,'F’
 ON      1  VALUE         2              0  N      'F'
 ON      1  VALUE         2              0  N      'en_US'
 WHERE   0  VALUE         3              0  N      'X'

Query 2

SELECT QUERYNO , QBLOCKNO , PREDNO                 
      ,MARKER, SUBSTR(LITERALS, 1 , 32) AS LITERALS
      ,TEXT                                        
FROM BOXWELL.DSN_PREDICAT_TABLE                    
WHERE NOT LITERALS = ''                            
  AND ((CLAUSE = 'WHERE'                           
    AND PARENT_PNO = 0)                            
    OR CLAUSE = 'ON')                              
  AND NOT RIGHT_HAND_SIDE = 'COLEXP'               
  AND LHS_TABNO = 0                                
  AND RHS_TABNO = 0                                
ORDER BY QUERYNO , QBLOCKNO , PREDNO               
;

MARKER  LITERALS                          TEXT                                    
---+---------+---------+---------+---------+---------+---------+--------
N 'X','X','T'                  (((((((((((("B"."WLX_TIMESTAMP"=(SELECT 
N 'X','X','T'                  (((((((((((("B"."WLX_TIMESTAMP"=(SELECT 
Y  HV2,'S'                     ("IQA0610"."IQATW001"."WLX_TIMESTAMP"=(E
N 'X'                          ("Z"."WLX_TIMESTAMP"="A"."WLX_TIMESTAMP"
N 'X'                          ("Z"."WLX_TIMESTAMP"="A"."WLX_TIMESTAMP"
N 'X','X','I','  '             ((((((((((("B"."WLX_TIMESTAMP"=(SELECT M
N 'X','X','I','  '             ((((((((((("B"."WLX_TIMESTAMP"=(SELECT M
N 'X'                          ("Z"."WLX_TIMESTAMP"="A"."WLX_TIMESTAMP"
N  X'                          ("Z"."WLX_TIMESTAMP"="A"."WLX_TIMESTAMP"
Y  HV1,HV2,HV3,HV4,HV5,'F',HV6 ((((("SYSIBM"."SYSCOPY"."DBNAME"=(EXPR)