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:
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:
Now all of the ON data has vanished, so you must add an OR into the query:
Second attempt
Giving us now this Output:
The COLEXP row SQL text looks like:
SUBSTR(DIGITS("A"."CORR_TYPE"),1,10)So we can also exclude these rows from the select.
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:
Host with no colon?
Which gives (excluding the first columns for clarity!):
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)
