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.

It is Now Official!

IBM just released, on the 2022-06-24, an update on the AHA page DB24ZOS-I-717 that CTE inline hints are now supported with a pointer to the APAR PK04574 (Closed in 2015!!!). Now we must just wait for the docu update I guess!

2019-03 EXPLAIN data review

A quick history of EXPLAIN and a new standard Db2 EXPLAIN Query to bring out the best and most underused columns.

EXPLAIN has been with us from nearly the start of Db2 (DB2 V2R1 when the b was B!) and, over the years, the number of Explain tables has mushroomed up to 20 in Db2 12. Not *all* of this data is really useful but there are bits and pieces that are well worth adding to your standard repertoire of explain queries!

Table of contents
  1. PLAN_TABLE
  2. DSN_DETCOST_TABLE
  3. DSN_FILTER_TABLE
  4. DSN_PREDICAT_TABLE
  5. New Standard Explain SQL

PLAN_TABLE

This can be used to see in which release the PLAN_TABLE was created:

V2.1 – 25 column format
V2.2 – 28 column format
V2.3 – 30 column format
V3.1 – 34 column format
V4.1 – 43 column format
V5.1 – 46 column format
V6.1 – 49 column format
V7.1 – 51 column format
V8.1 – 58 column format
V9.1 – 59 column format
10 – 64 column format ending with MERGN
11 – 66 Column format ending with EXPANSION_REASON
12 – 67 column format ending with PER_STMT_ID

What could be interesting here?? Well what about:

PREFETCH                           D, S, L, U, or Blank?

  • D for Dynamic
  • S for pure Sequential
  • L for through a page list
  • U for unsorted RID list
  • Blank for Unknown or no prefetch

COLUMN_FN_EVAL R, S, X, Y, or Blank?

  • R for while data is read
  • S for while performing a sort
  • X for while data is read but using OFFSET
  • Y for while performing a sort but using OFFSET
  • Blank for after data retrieval and any sort

PAGE_RANGE Y or Blank.

  • Y for yes the table qualifies for page range screening
    so that only the needed partitions are scanned
  • Blank for no

PRIMARY_ACCESSTYPE D, P, S, T, Blank. Is direct row access attempted first:

  • D it tries to use direct row access with a rowid column. If it cannot do this it uses the access path that is described in the ACCESSTYPE column
  • P it uses a DPSI and a part-level operation to access the data
  • S it uses sparse index access for a sideways table reference
  • T the base table or result file is materialized into a work file, and the work file is accessed via sparse index access. If a base table is involved, then ACCESSTYPE indicates how the base table is accessed
  • Blank it does not try to use direct row access by using a rowid column or sparse index access for a work file.

DSN_DETCOST_TABLE

There are a ton of interesting columns here but most are “IBM internal only”, however, these are available for our enjoyment:

ONECOMPROWSThe number of rows qualified after applying
local predicates.
IMLEAFThe number of index leaf pages scanned
by Data Manager.
IMFF  The filter factor of matching predicates only.
IMFFADJ  The filter factor of matching and screening
DMROWS  The number of data manager rows returned
(after all stage 1 predicates are applied).
RDSROWThe number of data manager rows returned
(after all stage 1 predicates are applied).
IXSCAN_SKIP_DUPSWhether duplicate index key values
are skipped during an index scan.
  • Y Duplicate key values are skipped
  • N Duplicate key values are not skipped
IXCAN_SKIP_SCREENWhether key ranges that are disqualified
by index screening predicates are
skipped during an index scan.
  • Y Disqualified key ranges are skipped
  • N Key ranges are not skipped
EARLY_OUTWhether fetching from the table stops
after the first qualified row.
  • Y Internal fetching stops after the first
    qualified row
  • N Internal fetching continues after the first
    qualified row
BLOCK_FETCH or N Was block fetch used?

DSN_FILTER_TABLE

Sometimes it is really interesting to see when the predicate is applied and whether or not it could be pushed down.

STAGEThe stage that the predicate was evaluated.
MATCHING, SCREENING, PAGERANGE,
STAGE1, or STAGE2.
PUSHDOWNWhether the predicate was pushed down.
  • I for the Index Manager evaluates it
  • D for the Data Manager evaluates it
  • Blank means no push down was used

DSN_PREDICAT_TABLE

Here lives the really good stuff that most people do not use! The Bubble Up is normally an SQL coding error and the T is “forgetfulness”…

ADDED_PREDIf this column is non-blank it means that Db2 has
rewritten the query to some extent.
This is not good as it adds CPU to the process.
In my opinion any “added predicate“
should already be coded correctly in the SQL!
  • Blank Db2 did not add the predicate
  • B for bubble up
  • C for correlation
  • J for join
  • K for LIKE for expression-based index
  • L for localization
  • P for push down
  • R for page range
  • S for simplification
  • T for transitive closure

CLAUSE

Is this a SELECT, HAVING, ON, or WHERE clause?
ORIGIN  Where did it come from?
  • C for a column mask
  • R for a Row Permission
  • U specified by User
  • Blank generated by Db2

New Standard Explain SQL

Just adding these to your standard Explain query like this:

SET CURRENT SQLID = 'BOXWELL' ;                                   
SELECT SUBSTR(P.PROGNAME, 1 , 8 ) AS PROGNAME
,SUBSTR(DIGITS(P.QUERYNO), 6, 5) AS LINE
,SUBSTR(DIGITS(P.QBLOCKNO), 4, 2) AS QNO
,SUBSTR(DIGITS(P.PLANNO), 4, 2) AS PNO
,SUBSTR(DIGITS(P.MIXOPSEQ), 4, 2) AS SQ
,SUBSTR(DIGITS(P.METHOD), 5, 1) AS M
,SUBSTR(P.TNAME, 1, 18) AS TABLE_NAME
,P.ACCESSTYPE AS A
,P.PRIMARY_ACCESSTYPE AS PA
,SUBSTR(DIGITS(P.MATCHCOLS), 4, 2) AS CS
,SUBSTR(P.ACCESSNAME, 1, 12) AS INDEX
,P.INDEXONLY AS IO
,SUBSTR(CASE SORTN_UNIQ WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTN_JOIN WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTN_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTN_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END
, 1 , 4) AS UJOG
,SUBSTR(CASE SORTC_UNIQ WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTC_JOIN WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTC_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
CASE SORTC_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END
, 1 , 4) AS UJOGC
,P.PREFETCH AS P
,P.COLUMN_FN_EVAL AS CE
,P.PAGE_RANGE AS PR
,P.QBLOCK_TYPE AS TYPE
,P.MERGE_JOIN_COLS AS MJC
,S.PROCMS AS MS
,S.PROCSU AS SU
,D.EARLY_OUT AS EO
,D.BLOCK_FETCH AS BF
,F.ORDERNO AS ON
,F.PREDNO AS PN
,F.STAGE AS STAGE
,F.PUSHDOWN AS PD
,R.TYPE AS TYPE
,R.ADDED_PRED AS AP
,R.CLAUSE AS CLAUSE
,R.ORIGIN AS OR
,R.REDUNDANT_PRED AS RP
,R.TEXT AS TRANSFORMED_PREDICATE
FROM PLAN_TABLE P
FULL OUTER JOIN
DSN_STATEMNT_TABLE S
ON P.QUERYNO = S.QUERYNO
AND P.APPLNAME = S.APPLNAME
AND P.PROGNAME = S.PROGNAME
AND P.COLLID = S.COLLID
AND P.GROUP_MEMBER = S.GROUP_MEMBER
AND P.SECTNOI = S.SECTNOI
AND P.VERSION = S.VERSION
AND P.EXPLAIN_TIME = S.EXPLAIN_TIME
FULL OUTER JOIN
DSN_DETCOST_TABLE D
ON P.QUERYNO = D.QUERYNO
AND P.APPLNAME = D.APPLNAME
AND P.PROGNAME = D.PROGNAME
AND P.COLLID = D.COLLID
AND P.GROUP_MEMBER = D.GROUP_MEMBER
AND P.SECTNOI = D.SECTNOI
AND P.VERSION = D.VERSION
AND P.EXPLAIN_TIME = D.EXPLAIN_TIME
AND P.QBLOCKNO = D.QBLOCKNO
AND P.PLANNO = D.PLANNO
FULL OUTER JOIN
DSN_FILTER_TABLE F
ON P.QUERYNO = F.QUERYNO
AND P.APPLNAME = F.APPLNAME
AND P.PROGNAME = F.PROGNAME
AND P.COLLID = F.COLLID
AND P.GROUP_MEMBER = F.GROUP_MEMBER
AND P.SECTNOI = F.SECTNOI
AND P.VERSION = F.VERSION
AND P.EXPLAIN_TIME = F.EXPLAIN_TIME
AND P.QBLOCKNO = F.QBLOCKNO
AND P.PLANNO = F.PLANNO
FULL OUTER JOIN
DSN_PREDICAT_TABLE R
ON F.QUERYNO = R.QUERYNO
AND F.APPLNAME = R.APPLNAME
AND F.PROGNAME = R.PROGNAME
AND F.COLLID = R.COLLID
AND F.GROUP_MEMBER = R.GROUP_MEMBER
AND F.SECTNOI = R.SECTNOI
AND F.VERSION = R.VERSION
AND F.EXPLAIN_TIME = R.EXPLAIN_TIME
AND F.QBLOCKNO = R.QBLOCKNO
AND F.PREDNO = R.PREDNO
WHERE 1 = 1
AND P.QUERYNO IN (1 , 2 )
ORDER BY 1 , 2 , 3 , 4 , 5 , 24 , 25
;

Here I limit it to just the QUERYNO 1 and 2 as these were the numbers used for the EXPLAIN command:

EXPLAIN ALL SET QUERYNO = 1 FOR              
SELECT INSTANCE, CLONE
FROM SYSIBM.SYSTABLESPACE A
WHERE ( SELECT B.TSNAME
FROM SYSIBM.SYSTABLES B
,SYSIBM.SYSINDEXES C
WHERE C.CREATOR = ?
AND C.NAME = ?
AND C.TBCREATOR = B.CREATOR
AND C.TBNAME = B.NAME ) = A.NAME
AND A.DBNAME = ?
;
EXPLAIN ALL SET QUERYNO = 2 FOR
SELECT A.INSTANCE, A.CLONE
FROM SYSIBM.SYSTABLESPACE A
,SYSIBM.SYSTABLES B
,SYSIBM.SYSINDEXES C
WHERE C.CREATOR = ?
AND C.NAME = ?
AND C.TBCREATOR = B.CREATOR
AND C.TBNAME = B.NAME
AND A.DBNAME = ?
AND A.NAME = B.TSNAME
AND A.DBNAME = B.DBNAME
WITH UR
;
--------+--------+------+--------+---------+---------+--------+------+---
PROGNAME LINE QNO PNO SQ M TABLE_NAME A PA CS INDEX IO UJOG UJOGC
--------+--------+------+--------+---------+---------+--------+------+---
DSNESM68 00001 01 01 00 0 SYSTABLESPACE I 02 DSNDSX01 N ---- ----
DSNESM68 00001 01 01 00 0 SYSTABLESPACE I 02 DSNDSX01 N ---- ----
DSNESM68 00001 02 01 00 0 SYSINDEXES I 02 DSNDXX01 N ---- ----
DSNESM68 00001 02 01 00 0 SYSINDEXES I 02 DSNDXX01 N ---- ----
DSNESM68 00001 02 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00001 02 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00002 01 01 00 0 SYSINDEXES I 02 DSNDXX01 N ---- ----
DSNESM68 00002 01 01 00 0 SYSINDEXES I 02 DSNDXX01 N ---- ----
DSNESM68 00002 01 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00002 01 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00002 01 02 00 1 SYSTABLES I 02 DSNDTX01 N ---- ----
DSNESM68 00002 01 03 00 1 SYSTABLESPACE I 02 DSNDSX01 N ---- ----
DSNESM68 00002 01 03 00 1 SYSTABLESPACE I 02 DSNDSX01 N ---- ----
DSNE610I NUMBER OF ROWS DISPLAYED IS 13
--+---------+---------+---------+---------+---------+---------+--------
P CE PR TYPE MJC MS SU EO BF ON
--+---------+---------+--------+---------+---------+---------+---------
SELECT ------ 1 2 N N 1
SELECT ------ 1 2 N N 2
NCOSUB ------ 1 2 N N 1
NCOSUB ------ 1 2 N N 2
NCOSUB ------ 1 2 N N 1
NCOSUB ------ 1 2 N N 2
SELECT ------ 1 2 N N 1
SELECT ------ 1 2 N N 2
SELECT ------ 1 2 N N 1
SELECT ------ 1 2 N N 2
SELECT ------ 1 2 N N 3
SELECT ------ 1 2 N N 1
SELECT ------ 1 2 N N 2
---------+---------+---------+---------+---------+---------+---------+-----
PN STAGE PD TYPE AP CLAUSE OR RP TRANSFORMED_PREDICATE
---------+---------+---------+---------+---------+---------+---------+-----
3 MATCHING EQUAL WHERE U N "A"."DBNAME"=(EXPR)
2 MATCHING EQUAL WHERE U N "A"."NAME"=(SELECT "B"."TSNAME"
5 MATCHING EQUAL WHERE U N "C"."CREATOR"=(EXPR)
6 MATCHING EQUAL WHERE U N "C"."NAME"=(EXPR)
7 MATCHING EQUAL WHERE U N "C"."TBCREATOR"="B"."CREATOR"
8 MATCHING EQUAL WHERE U N "C"."TBNAME"="B"."NAME"
2 MATCHING EQUAL WHERE U N "C"."CREATOR"=(EXPR)
3 MATCH EQUAL WHERE U N "C"."NAME"=(EXPR)
4 MATCH EQUAL WHERE U N "C"."TBCREATOR"="B"."CREATOR"
5 MATCHING EQUAL WHERE U N "C"."TBNAME"="B"."NAME"
9 STAGE1 EQUAL T WHERE N "B"."DBNAME"=(EXPR)
6 MATCHING EQUAL WHERE U N "A"."DBNAME"=(EXPR)
7 MATCHING EQUAL WHERE U N "A"."NAME"="B"."TSNAME"

It shows you a lot more data than the normal bare bones Explain PLAN_TABLE data especially here the TYPE, STAGE and ADDED_PREDICATE (AP Column).

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

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 = ''                  
Output1                 
-----+-------+---------+---------+---------+---------+---------+
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'


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

Output3
-----+--------+--------+---------+---------+-----------------
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'

Output4
-----+---------+---------+---------+---------+----------+----
 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               
;

Output5
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 Programme 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.

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 inspiring experiences with you now.


Inspiring experiences

We TAKE the anonymized results for research

and will communicate with the local User Groups for discussions

Kundenmeinungen

3BIF USAGE
News
Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren     [Customer Comments]

 

  • Gesetzliche Krankenversicherung
  • Automobil Industrie
  • IT Provider für Banken
  • Versicherungen

Erste Ergebnisse von Db2 z/OS Kunden

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”…
3BIF 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…

[Ergebnisse von Db2 z/OS Kunden (engl)]

Program 3 – BIF Usage   

BIF-Usage

 

Präsentation

BIF KompatibilitätDb2 10 Kompatibilität Mode

 

Änderungen bei der STRING Formatierung von Decimal Data bei der CHAR und VARCHAR built-in Funktion und bei der CAST Spezifikation mit CHAR und VARCHAR Ergebnis Typen sowie UNSUPPORTED TIMESTAMP STRINGs.

White PaperWo sind die BIFs?
Finding BIFs (engl)
Wo sind die BIFs? Und wie können wir in Zukunft problemlos mit BIFs leben?
Wege aus der mangelnden Aufwärtskompatibilität bei der Migration von Db2 Versionen
Newsletter2015-01 – BIFCIDS – Where’s the BIF? (engl.)How will you deal with loop-hole usage in production code?
VideoBIF Usage (engl)(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon

BIF Usage Video