2021-02 Hidden features of EXPLAIN

EXPLAIN has been with us since DB2 V2.1 and, as I have previously written in older newsletters, (2019-03 EXPLAIN data review and 2012-11 EXPLAIN table maintenance), it has gotten bigger and better over the years. In this newsletter, I wish to quickly bring everyone up-to-date, based on my two older newsletters, and then discuss the usage of CTE Opthints.

Up to speed

Back in 2012, I listed out the tables EXPLAIN can use and ended with Db2 10, so first up are the new and changed tables for Db2 11 and Db2 12:

Db2 11

PLAN_TABLENow with 66 columns
DSN_COLDIST_TABLE
DSN_DETCOST_TABLE
DSN_FILTER_TABLE
DSN_FUNCTION_TABLE
DSN_KEYTGTDIST_TABLE
DSN_PGRANGE_TABLE
DSN_PGROUP_TABLE
DSN_PREDICAT_TABLE
DSN_PREDICATE_SELECTIVITYNew but input for the BIND QUERY command only!
DSN_PTASK_TABLE
DSN_QUERYINFO_TABLETwo LOB tables as well
DSN_QUERY_TABLEOne LOB table as well
DSN_SORTKEY_TABLE
DSN_SORT_TABLE
DSN_STATEMENT_CACHE_TABLEOnly for DSC
DSN_STATEMNT_TABLE
DSN_STAT_FEEDBACKNew table containing RUNSTATS recommendations when EXPLAIN is executed.
DSN_STRUCT_TABLE
DSN_VIEWREF_TABLE

Db2 12

PLAN_TABLENow with 67 columns
DSN_COLDIST_TABLE
DSN_DETCOST_TABLE
DSN_FILTER_TABLE
DSN_FUNCTION_TABLE
DSN_KEYTGTDIST_TABLE
DSN_PGRANGE_TABLE
DSN_PGROUP_TABLE
DSN_PREDICAT_TABLE
DSN_PREDICATE_SELECTIVITY
DSN_PTASK_TABLE
DSN_QUERYINFO_TABLETwo LOB tables as well
DSN_QUERY_TABLEOne LOB table as well
DSN_SORTKEY_TABLE
DSN_SORT_TABLE
DSN_STATEMENT_CACHE_TABLEOnly for DSC
DSN_STATEMNT_TABLE
DSN_STAT_FEEDBACK
DSN_STRUCT_TABLE
DSN_VIEWREF_TABLE

So you can see, that not a lot really happened in Db2 12 as far as any new EXPLAIN tables, but the one big change was the new column sprinkled throughout all of them: PER_STMT_ID

Something new

PER_STMT_ID BIGINT NOT NULL The persistent statement identifier for SQL statements in Db2 catalog tables.
For example, this column corresponds to the following catalog table columns that identify
SQL statements:
• STMT_ID in SYSIBM.SYSPACKSTMT, for SQL statements in packages.
• SDQ_STMT_ID in SYSIBM.SYSDYNQUERY, for stabilized dynamic SQL statements.

This column makes it *much* easier to track your mix of dynamic and static SQL all through the system!

CTE Opthints

I was using one of these the other day and the customer I was working with was amazed to see what it is and how it works. As in all things to do with OPTHINTs, caution must always be used! The best OPTHINT is no OPTHINT!

In the Beginning

Many, many years ago, sometime around DB2 V8, I found a CTE Opthint documented in the internet and thought “Wow! That is the future of hints!” Then they completely disappeared… try doing a google search and you will see what I mean. The cool thing is – They still work! I do not know for how long, but they still work in Db2 12 FL507 at least.

Time to Test

First create a couple of test table candidates and one index:

CREATE TABLE BOXWELL.T1 (C1 CHAR(8) NOT NULL
                        ,C2 CHAR(8) NOT NULL
                        ,C3 SMALLINT NOT NULL);
CREATE TABLE BOXWELL.T2 (C1 CHAR(8) NOT NULL
                        ,C2 CHAR(8) NOT NULL
                        ,C3 SMALLINT NOT NULL);
CREATE INDEX INDX1_T2 ON T2 ( C1 ) CLUSTER ;

The SQL of interest is:

SELECT T1.*        
FROM T1
, T2
WHERE T1.C1 = T2.C1
;

So first you do a normal EXPLAIN and get this output:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
01000  01   01   00  0  T1                  R       00                N   ----  ----   S
01000  01   02   00  1  T2                  I   T   00  INDX1_T2      Y   -Y--  ----   S

So Db2 accesses T1 first with a tablespace scan (A = R) and then uses the index to access T2. Now add the CTE to the front so the SQL looks like:

WITH DSN_INLINE_OPT_HINT
(TABLE_CREATOR
, TABLE_NAME
, ACCESS_TYPE
, ACCESS_CREATOR
, ACCESS_NAME
, JOIN_SEQ) AS
(VALUES (NULL
, NULL
, 'INDEX'
, NULL
, NULL
, NULL)
)
SELECT T1.*        
 FROM T1            
    , T2            
 WHERE T1.C1 = T2.C1
 ;                  

Rules, Rules, Rules

The golden rules of CTE Opthint are that the CTE must be the first CTE, and it must be called DSN_INLINE_OPT_HINT, your ZPARM OPTHINTS must be set to YES to allow them, of course. Just include the columns and the rows you actually need. Every additional column or row is one more step towards optimizer disapproval and the CTE Opthint being ignored.

In the example above, the value NULL is a wild card short hand for all values and that row is simply telling the optimizer “If nothing else is found try and enforce INDEX access”.

So now the access path looks like:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
01001  01   01   00  0  T2                  I       00  INDX1_T2      Y   ----  ----   S
01001  01   02   00  1  T1                  R   T   00                N   -Y--  ----   S

Seeing double?

As you can see, Db2 now uses the index first. So what if you had two indexes?? Create an index on the first table:

CREATE INDEX INDX1_T1 ON T1 ( C1 ) CLUSTER ;

Now the “normal” SQL EXPLAIN shows:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
02000  01   01   00  0  T1                  R       00                N   ----  ----   S
02000  01   02   00  1  T2                  I   T   00  INDX1_T2      Y   -Y--  ----   S

We are back to tablespace scan on T1 and then IX on T2. Now, using the CTE with just INDEX (That is the one we just used) gives you:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
02001  01   01   00  0  T1                  I       00  INDX1_T1      N   ----  ----    
02001  01   02   00  1  T2                  I       01  INDX1_T2      Y   ----  ----     

So, we have now got double index access (which is what we wanted!) What about trying to push T2 up to the first table to be used? Just add a second row in the CTE like this:

WITH DSN_INLINE_OPT_HINT
(TABLE_CREATOR
, TABLE_NAME
, ACCESS_TYPE
, ACCESS_CREATOR
, ACCESS_NAME
, JOIN_SEQ) AS
(VALUES (NULL
, NULL
, 'INDEX'
, NULL
, NULL
, NULL)
  ,(NULL
  , 'T2'
  , NULL
  , 'BOXWELL'
  , 'INDX1_T2'
  , 1 )       
)
SELECT T1.*        
 FROM T1            
    , T2            
 WHERE T1.C1 = T2.C1
 ;           

And the output now changes to be this:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
02002  01   01   00  0  T2                  I       00  INDX1_T2      Y   ----  ----   S
02002  01   02   00  2  T1                  I       00  INDX1_T1      N   ----  ----    

Isn’t that cool?

I think these are very, very handy items and are a crucial extra piece in the puzzle of “tipping point” SQLs. CTE Opthints work for both Dynamic and static SQL by the way.

One final bit of info about these CTE Opthints: If defined OK and accepted by EXPLAIN you will get:

 DSNT404I SQLCODE = 394, WARNING:  USER SPECIFIED OPTIMIZATION HINTS USED DURING ACCESS PATH SELECTION                                      
 DSNT418I SQLSTATE   = 01629 SQLSTATE RETURN CODE                           
 DSNT415I SQLERRP    = DSNXOPCO SQL PROCEDURE DETECTING ERROR               
 DSNT416I SQLERRD    = 20 0  4000000  1143356589  0  0 SQL DIAGNOSTIC INFORMATION                                                       
 DSNT416I SQLERRD    = X'00000014'  X'00000000'  X'003D0900'  X'44263CAD'   
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION               
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                  

If you get something wrong you will get *no* message SQLCODE +394 (This happens if your column names are invalid for example), and if the SQL Opthint is *not* used you get this SQLCODE output:

 DSNT404I SQLCODE = 395, WARNING:  USER SPECIFIED OPTIMIZATION HINTS ARE INVALID (REASON CODE = 'xx'). THE OPTIMIZATION HINTS ARE IGNORED
 DSNT418I SQLSTATE   = 01628 SQLSTATE RETURN CODE                         
 DSNT415I SQLERRP    = DSNXOOP SQL PROCEDURE DETECTING ERROR              
 DSNT416I SQLERRD    = 20 0  4000000  1142580885  0  0 SQL DIAGNOSTIC INFORMATION                                                     
 DSNT416I SQLERRD    = X'00000014'  X'00000000'  X'003D0900'  X'441A6695' 
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION             
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                

Tipping points

We all have had SQLs that run fine for years and then, normally after a RUNSTATS, the order of the tables swaps… this is not really seen by anyone until the SQL starts performing really badly! If you have one of these “tipping point” SQLs you can now simply “lock it in” with a simple CTE at the start of the SQL.

Columns of interest

In my examples I only used a few of the allowable columns in a CTE Opthint. Here’s a list of as many as I have found:

Column NameRemarks
TABLE_CREATORNULL for all
TABLE_NAMENULL for all
CORRELATION_NAMENeeded if duplicate table names
ACCESS_TYPERSCAN, INDEX, INLIST, MULTI_INDEX
ACCESS_CREATORIndex Schema, NULL for all
ACCESS_NAMEIndex Name, NULL for all
JOIN_SEQJoin sequence number. 1 for first table.
JOIN_METHODNLJ, SMJ, HYBRID
PARALLELISM_MODECPU, IO, SYSPLEX
ACCESS_DEGREEDegree of required parallelism
JOIN_DEGREEDegree of required parallelism
TABNONormally never required. If used take the number from the PLAN_TABLE
QBLOCKNONormally never required. Must be used if duplicate table names and correlation Ids. Take the value from the PLAN_TABLE
PREFETCHS or L. Sequential or List Prefetch to be used. From this column on I have never used them!
QBLOCK_TYPE
MATCHING_PRED
HINT_TYPE
OPT_PARM

As usual, if you have any questions or ideas please drop me a line!

TTFN,

Roy Boxwell

Update: One of my readers pointed out that he uses these beasts and even had to open a PMR. Here I quote:

“I learned the hard way that CTE Hints were not honored when you did a rebind with APREUSE(WARN).  I reported this to IBM, they did fix it with UI68523, you want to be sure you have that.”

Another reader found that I had cut-and-pasted the DDL a bit too fast so all of the DDL got corrected where it was wrong (The table create and the second index create)

Yet another sent me an updated column list and so have done some changes to the list of columns used in the table.

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)

GIVE and TAKE Program

1,2,3


Give and Take 2020

Information on the Give and Take Programs 4,5,6,7


Previous Give & Take

We have “GIVEn” various free-of-charge Use Cases from our SQL WorkloadExpert for Db2 z/OS like:

1 Index Maintenance Costs

2 EXPLAIN Suppression

3 BIF Usage

Limited free-of-Charge Db2 Application

This Program started in Europe, during our 30th anniversary was such a success, that it is now being Extended for the benefit of North American Db2 z/OS sites.

<a href="http://www.seg.de/produkte/db2-zos-produkte/sql-workloadexpert-for-db2-zos/" target="


Index Maintenance Costs, EXPLAIN Suppression, BIF

Limited free-of-Charge Db2 Application

This Program started in Europe, during our 30th anniversary was such a success, that it is now being Extended for the benefit of North American Db2 z/OS sites.

SQL WorkloadExpert for Db2 z/OS (WLX) contains several “Use Cases”. We provided three of them, free of charge, for one month to different sites. In return, we received their results. We’d like to share this with you now.

We have “GIVEn” various free-of-charge Use Cases from SQL Workload Expert for Db2 z/OS like
1  Index Maintenance Costs
2  EXPLAIN Suppression
3  BIF Usage 
BIX HealthCheck – This last one is still available
We TAKE the anonymized results for research
and will communicate with the local User Groups for discussions

Inspiring experiences

Customer Statements

3BIF USAGE
News
Read the Customer Comments across the Industry 

 

  • Health Care
  • Insurance
  • Banking
  • Car Manufacturing

First results from Db2 z/OS sites

1Index Mantenance CostsNearly all the data we got back showed a positive result for created Indexes…
2EXPLAIN SuppressionAbout 10% of SQLs are actually “left over”…
3 BIF Usage When migrating to a new Db2 version, the BIFs are not always compatible and an extreme amount of data is produced.

 

The difficulty of near-time analysis to track down BIFs within dynamic SQL have been solved with the BIF Usage Use Case…

 [Results from Db2 z/OS sites]

Program 3 – BIF Usage –  has now started

BIF-Usage

 

Presentation

BIF CompatibilityDb2 10 compatibility mode
Changes to the STRING formating of decimal data within the CHAR and VARCHAR built-in function and to the CAST specification with CHAR and VARCHAR result types as well as  UNSUPPORTED TIMESTAMP STRINGs.
White PaperFinding BIFsAnd How to Lead a Problem-Free Life With Them in the Future
Navigating the Challenges of moving to a new Db2 Release
Newsletter2015-01 – BIFCIDS – Where’s the BIF?How will you deal with loop-hole usage in production code?
VideoBIF Usage(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon

BIF Usage video