This month I wish to wander through the various types of data that Db2 squirrels away for our use. One of my favorite tables is the SYSPACKSTMT where, like its name suggests, every statement for every package is stored away. Within are a couple of very interesting, for me anyway, columns: STATUS and EXPLAINABLE.
Private Property!
First rule of SYSPACKSTMT is that any row where SEQNO, STMTNO and SECTNO are all equal to zero is internal for IBM use and must be ignored from all investigation or further use! In other words, the SQL must always look a little like this:
SELECT PS.* FROM SYSIBM.SYSPACKSTMT PS
WHERE NOT (PS.SEQNO = 0
AND PS.STMTNO = 0
AND PS.SECTNO = 0)
AND PS.EXPLAINABLE = ' '
LIMIT 5000
;
What’s in a Name?
Starting with EXPLAINABLE – It is what it says on the box! If the statement can be used with the EXPLAIN statement, and I sincerely hope *all* of your production SQL has been explained at BIND/REBIND time, dear readers, then this column has a „Y“. If, on the other hand, it is not EXPLAINable then you simply get an „N“ in it, and if it was last bound prior to Version 7 it will contain a Blank – I also really hope that none of your in-use production packages has a blank in it …
Filter it Down …
This is a useful column to filter your SQL in this table. Basically, the logic here, is that any SQL that is EXPLAINable does stuff that you, as a DBA, can check or alter. Otherwise, you cannot really affect it at all with normal means. An example is a FETCH Cursor statement. You cannot „tune“ these, your only chance is in the cursor declare that the FETCH is using. Further simple examples are COMMIT and ROLLBACK – Very nice to know that your code is doing COMMIT and ROLLBACK but nothing to see here – move along please!
Sub-Optimal Docu Ahead!
Ok, here’s the first bit of info… The docu is not really being honest with you when it states: „Blank means last bound prior to Version 7.“ and it also contains the text for QUERYNO – „If -1 this statement was bound before Version 7.“ These statements are not 100% true, what it additionally means is: „or is a non-standard package“. What you must do is *also* ignore the rows where SEQNO = 1 if the TYPE of the package is a „N“, „F“ or „T“ („N“ is a Native SQL routine package, „F“ is a compiled SQL scalar function and „T“ is a Trigger package). Further, for Advanced Triggers, you must also ignore all rows with a 2 in SEQNO as well!
How does it look?
So now you have this SQL that should only find all really non-explainable SQL, it still finds esoteric weirdoes, but in my test installations they are really only ROLLBACK and COMMIT within Native SQL routines!
SELECT SUBSTR(PS.COLLID , 1 , 18) AS COLLID
, SUBSTR(PS.NAME , 1 , 8) AS NAME
, PS.CONTOKEN
, PS.STATUS
, PK.BINDTIME
, PK.TYPE
, PK.VALID
, PK.OPERATIVE
, PK.VALIDATE
, PK.HOSTLANG
, PK.REMOTE
, PK.REOPTVAR
, PK.LASTUSED
, PS.STMT_ID
, PS.QUERYNO
, PS.EXPANSION_REASON
, PS.STATEMENT
FROM SYSIBM.SYSPACKAGE PK
,SYSIBM.SYSPACKSTMT PS
WHERE PK.LOCATION = PS.LOCATION
AND PK.COLLID = PS.COLLID
AND PK.NAME = PS.NAME
AND PK.CONTOKEN = PS.CONTOKEN
AND PS.EXPLAINABLE = ' '
AND NOT (PS.SEQNO = 0
AND PS.STMTNO = 0
AND PS.SECTNO = 0)
AND NOT (PK.TYPE IN ('N' , 'F' , 'T' , '1')
AND PS.SEQNO = 1
AND PS.STMTNO = 0
AND PS.SECTNO = 0)
AND NOT (PK.TYPE = '1'
AND PS.SEQNO = 2
AND PS.STMTNO = 0
AND PS.SECTNO = 0)
ORDER BY 1 , 2 , BINDTIME DESC
LIMIT 5000
;
It’s a Matter of STATUS
Once you have studied all your EXPLAINABLE = ‚ ‚ rows, we can then move on to „the meat and potatoes“ of this month’s newsletter! What are all the different STATUS column values, and what do they actually mean?
blank The statement is non-executable, or was bound in a Db2 release prior to Version 5.
A Distributed – statement uses Db2 private protocol access. The statement will be parsed and executed at the server using defaults for input variables during access path selection. [This was deprecated in DB2 V9 and dropped from support in DB2 10.]
B Distributed – statement uses Db2 private protocol access. The statement will be parsed and executed at the server using values for input variables during access path selection. [This was deprecated in DB2 V9 and dropped from support in DB2 10.]
C Compiled – statement was bound successfully using defaults for input variables during access path selection.
D Distributed – statement references a remote object using a three-part name. Db2 will implicitly use DRDA access, either because the DBPROTOCOL bind option was not specified (defaults to DRDA), or the bind option DBPROTOCOL(DRDA) was explicitly specified. This option allows the use of three-part names with DRDA access, but it requires that the package be bound at the target remote site.
E Explain – statement is an SQL EXPLAIN statement. The explain is done at bind time using defaults for input variables during access path selection.
F Parsed – statement did not bind successfully and VALIDATE(RUN) was used. The statement will be rebound at execution time using values for input variables during access path selection.
G Compiled – statement bound successfully, but REOPT is specified. The statement will be rebound at execution time using values for input variables during access path selection.
H Parsed – statement is either a data definition statement, or a statement that did not bind successfully and VALIDATE(RUN) was used. The statement will be rebound at execution time using defaults for input variables during access path selection. Data manipulation statements use defaults for input variables during access path selection.
I Indefinite – statement is dynamic. The statement will be bound at execution time using defaults for input variables during access path selection.
J Indefinite – statement is dynamic. The statement will be bound at execution time using values for input variables during access path selection. [Not documented, but I believe these entries are all the same as I entries but with REOPTVAR = ‚Y‘.]
K Control – CALL statement. [Not really CALL statements here!]
L Bad – the statement has some allowable error. The bind continues but the statement cannot be executed.
M Parsed – statement references a table that is qualified with SESSION and was not bound because the table reference is for a declared temporary table that will not be defined until the package or plan is run. The SQL statement will be rebound at execution time using values for input variables during access path selection.
O Compiled for acceleration. The static query was bound successfully for acceleration and will be routed to an accelerator when executed.
Show us the Data!
Let us re-write our SQL, this time with the EXPLAINABLE negated and adding in a limiter to drop all the „DSN%“ stuff, as we are not really interested in all the IBM packages:
SELECT PS.STATUS,
COUNT(*)
FROM SYSIBM.SYSPACKAGE PK
,SYSIBM.SYSPACKSTMT PS
WHERE PK.LOCATION = PS.LOCATION
AND PK.COLLID = PS.COLLID
AND PK.NAME = PS.NAME
AND PK.CONTOKEN = PS.CONTOKEN
AND NOT PS.NAME LIKE 'DSN%'
AND NOT PS.EXPLAINABLE = ' '
AND NOT (PS.SEQNO = 0
AND PS.STMTNO = 0
AND PS.SECTNO = 0)
AND NOT (PK.TYPE IN ('N' , 'F' , 'T' , '1')
AND PS.SEQNO = 1
AND PS.STMTNO = 0
AND PS.SECTNO = 0)
AND NOT (PK.TYPE = '1'
AND PS.SEQNO = 2
AND PS.STMTNO = 0
AND PS.SECTNO = 0)
GROUP BY PS.STATUS
ORDER BY PS.STATUS
;
From my little test system, I get a nice smattering of values:
---------+---------+---------+---------+-----STATUS
---------+---------+---------+---------+-----
27822
C 99838
H 3210
I 63355
J 23
K 6
M 2346
DSNE610I NUMBER OF ROWS DISPLAYED IS 7
Break it Down!
I have 27822 Blank entries none of which were bound before DB2 V5! They are COMMIT (2914), CONNECT (778), DECLARE (21574), RELEASE (72), ROLLBACK (517), SET (66) and WHENEVER (1901) statements. All of the WHENEVERs are old code as I have removed all WHENEVER usage from our code base.
Naturally, no-one should ever have A and B values anymore as they were deprecated in DB2 V9 and removed from support in DB2 10 (It was the DBPROTOCOL(PRIVATE) BIND/REBIND option if you want details).
C is where, for a static shop, most package statements will be found, and you can see that it has the lion’s share in my test system.
I have no three-part name usage, no static EXPLAINs embedded in programs, no failed binds with VALIDATE(RUN), no bad SQL and also no Accelerator so no D, E, F, L or O status values.
The H’s are CALL (124), CLOSE (659), DECLARE (830) Including DGTTs, FETCH (796) and OPEN (708). Naturally, at BIND time there is nothing to do, and so they all get marked with an „H“ for Run time. There are also 93 „normal“ SQL Statements here SELECT, INSERT, UPDATE etc. which should trigger a check to be done! In my case, I know they are all for test cases and so all can be happily ignored.
I is our bread-and-butter – SQL Dynamic SQL ready to be prepared and run. I have CLOSE (4095), DECLARE (12810), DESCRIBE (68), EXECUTE xxxx (11829), EXECUTE IMMEDIATE (526), FETCH (12084), OPEN (4081) and PREPARE (17862).
J, for me at least, is just Assembler EXECUTE IMMEDIATE (23) statements all with REOPTVAR = Y.
K are all NULLID collection SYSSTAT statements all to do with FREE LOCATOR and SAVEPOINT statements with REMOTE = Y. No CALLs at all…
M is all of our SESSION table usage so we have DECLARE (530), DROP TABLE SESSION.xxxx (330), INSERT INTO SESSION.xxx (716), SELECT from SESSION.xxxx (435) and UPDATE SESSION.xxxx (335). This data is also filled if someone has decided to be evil and created a table with the CREATOR „SESSION“. It is not documented anywhere, but then it is assumed by Db2 that it is a transient table and all package SQL ends up here as well – This is probably not what the application designer was thinking of and is *not* recommended for normal use! If you find any „normal“ table usage here you have trouble brewing! On the other hand, it could be an extremely clever (or convoluted) way of getting REOPT(ALWAYS). As an extra bonus/problem any SQL referring to SESSION tables (Including erroneously defined ones) also never makes it into the two Db2 caches!
IBM Use as Well?
If you check the „DSN%“ packages as well, then you will also see two G Status entries for DSNTIAUL where it is looking up the DEFAULT value for a ROWID column in a table. I am guessing that DSNTIAUL needs this to see if the DEFAULT is „A“ – ROWID is GENERATED ALWAYS or „D“ – ROWID is GENERATED BY DEFAULT.
Trust is Good – Control is Better!
The ones to control are the H non-standard records, the unexpected J entries and the M entries, especially if the table is not really a SESSION table!
H like Hotel
Sounds like a dodgy Hotel to me! Sometimes rogue SQL makes it to production, or you have very old packages hanging around and, in both cases, you can get H status records. In nearly all of these cases a DROP of the package cures the „problem“.
J like Juliet
What about poor smitten Juliet? Re-optimizing at every prepare, basically ignoring the DSC, can get ridiculously expensive and so these must all be checked to see „why?“ and „How often?“ Do they really need this functionality? In 99.99% of the cases probably not – It is only costing you every second time and onwards that they run for almost certainly no real benefit and should all be changed to be REOPT(NONE).
M like Mike
And as for Mike… Well, the mini-bind that happens every time does cost you CPU and elapsed time, and if it can be reduced by, for example, the creation of a CREATE GLOBAL TEMPORARY TABLE xxxxx (CGTT) instead of a DECLARE GLOBAL TEMPORARY TABLE xxxxx (DGTT), this can be a real game changer. The differences in handling are small but very important to know between a CGTT and DGTT. Here is a great blog from Brian Laube on the IDUG web site all about these.
Well worth a read! After you have read this and reviewed your workload you can then action a plan to move from CGTT to DGTT or vice versa! As always, your mileage may vary, the check is in the mail and It Depends!
I also wrote a newsletter way back in 2019 all about CGTT and DGTT performance.
Time to Check!
I hope this month’s newsletter has been of interest and spurred a review of your SQL Statements!
TTFN,
Roy Boxwell