How to find Missing Db2 for z/OS static SQL access path information from PLAN_TABLE?

How to use “package” access path information to solve a real-life application issue!

Get access path from the “package”!

The new SQL EXPLAIN statement options!

There are NEW (as of Db2 for z/OS V10) SQL “EXPLAIN” statement options to get the hidden details of the actual access path instructions from the actual package (which is actually used at run time) and stuff that impossible-to-read package instruction “explanation” into the friendly-to-read PLAN_TABLE!

• And then you can query the PLAN_TABLE and understand what is going on!

Review: What is an “access path”?

• Just as a refresher…… what is “access path”? The “access paths” are the “instructions” used by DB2 at runtime to execute the SQL statement. 
  • For dynamic SQL (SPUFI, DSNTIAUL, DSNTEP2, data studio, and other ad-hoc SQL tools) the “access path” is determined by the DB2 optimizer at run time and then used immediately!
  • For static SQL (from COBOL or native SQL stored procedures) … the access path is determined in advance at BIND PACKAGE time (or CREATE PROCEDURE time).
    • Deciding the “access path” in advanced at BIND time saves time at execution time! DB2 does not need to decide how to execute the SQL… it just follows the pre-existing access path instructions! Fast!
  • What does the optimizer do? The optimizer looks at the tables and indexes and statistics and the phase of the moon and considers multiple access paths. It will pick the access path with the lowest estimated cost! 
    • If the optimizer was picking the access path for static SQL then those ‘instructions’ are stuffed inside the ‘package’ which is used at run time. The same package and instructions are used every time the package is used… no involvement of the OPTIMIZER at run time! 
      • If we used EXPLAIN(YES) then the optimizer sticks a copy of the instructions in the PLAN_TABLE! But we had to use EXPLAIN(YES) at BIND PACKAGE time… or CREATE PROCEDURE time. If we did not use EXPLAIN(YES) then it is not saved in the PLAN_TABLE
    • The optimizer is enhanced and made cleverer with every release of DB2 software. It is getting better all the time. It generally picks a “good” access path. Very rarely, the optimizer will pick a “bad” access path.
      • Of course, ‘good’ and ‘bad’ is relative, it really depends.

BACKGROUND: real life application issue!

AS background, we had a recent real-life issue where we wished we had the PLAN_TABLE explanation to understand why some static SQL was performing the way it did!

Recently, we had the experience of a production native SQL procedure which was performing well. 

But the same procedure in UAT (User Acceptance Testing environment) was performing slowly.

• We REORGed all UAT database tablespaces and indexes. We gathered current standard statistics. We rebound and re-created the procedure in UAT… and the procedure continued to perform poorly. Sadness

We (mostly just me) then explained the SQL with the VISUAL EXPLAIN (from Data Studio) for the one giant SQL SELECT from inside the procedure.

Upon review of this one SQL statement inside the procedure… The SQL was not coded clearly, and I could understand why the SQL could perform poorly! The access path was not obvious

• If I could have, I would have made some trivial changes to the procedure to make the SQL SELECT inside more “obvious” and “clear”
• With changed/different SQL SELECT then the optimizer would have an easier time to pick a good/great access path! It would be clearly better!

But, changing the procedure was out of scope for my immediate problem in UAT!

The question was why the procedure was performing well in production and not performing well in UAT?

When the procedure was created in production, EXPLAIN(NO) was used so no access path description existed in the PLAN_TABLE.

That was a pity!

I wanted to know the access path used in production!

I created another procedure, using the same procedure source code, in the production DB2 (outside of the regular application) and then ran that new procedure in the production DB2.

It performed poorly.

Oh-oh.

The production procedure that was performing well was created in Version 11.

• Many ways to confirm release … including SYSIBM.SYSPACKAGE and RELBOUND column

Apparently, the optimizer for V11 of Db2 for z/OS picked a different access path for this (poorly coded) SQL… and that access path was performing well. 

I wanted to use the same access path (as decided by the V11 optimizer for the prod database) in UAT today.. with my V12 DB2 subsystem!

• Normally, I would be surprised that the new V12 optimizer picked a “worse” access path. But in this case, I am not overly concerned.
  • Frist, the SQL was so wonky that I was not surprised that the access path could be different… it was not an obvious choice!
  • Second, if this was prod and the performance was poor then I would make the obvious recommendation to make some minor logical tweaks in the SQL SELECT to make it clearer and to make the optimal access path decision way more obvious! > Basically, we are lucky this specific procedure SELECT ever ran well in prod! Lucky! Lucky!
  • Third, this procedure and application are soon to be decommissioned so there is no need to over worry about it!
  • Fourth… maybe I could have spent more time gathering more sophisticated STATISTICS to better influence the optimizer.
    • Data Studio “statistics advisor” could have suggested optimal RUNSTATS to run for this particular SQL SELECT.

Fortunately, as of Db2 for z/OS Version 10… there is a new SQL EXPLAIN statement option of “EXPLAIN PACKAGE” to extract the access path from the package itself and stuff it into a PLAN_TABLE!

• I heard about this new functionality from attending IDUG! Remember, going to IDUG conferences and reading IDUG content committee blogs helps reminds YOU of new(ish) Db2 functionality!
• I was now given a real-life scenario to try it and use this knowledge I had but never really used before. Hopefully, it will work as I think it might.

SET CURRENT SQLID='DB2OSC';  -- use PLAN_TABLE under this schema! 
DELETE FROM PLAN_TABLE  -- optional.  delete something (if exists) from PLAN_TABLE
WHERE PROGNAME LIKE 'PVR%' ;

EXPLAIN PACKAGE
COLLECTION 'DBCYPP1'
PACKAGE 'PVR_GET_ADDRESS'
VERSION 'V1'  -- if using version... then optionally specify version
COPY 'CURRENT'  -- the current package copy is the default ;

-- now query the PLAN_TABLE to see the explanation of the access path for this package
SELECT * FROM DB2OSC.PLAN_TABLE
WHERE PROGNAME LIKE 'PVR%' 
ORDER BY BIND_TIME DESC, QUERYNO, QBLOCKNO, PLANNO;

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_explain.html

The results of the query to DB2OSC.PLAN_TABLE showed me the access path. And yes, it was different! (and I could understand why it performed well)

My “plan” was to copy this PLAN_TABLE “explanation” over to the UAT PLAN_TABLE and then tweak it to be used as OPTHINT. 

• There are tools (such as AQT) that turn table content result sets into multiple INSERT statements. I could have used such a tool to generate the SQL INSERT into PLAN_TABLE and then copied/moved/tweaked the SQL INSERT script to run in the UAT. 
• I used DSNTIAUL to SELECT * FROM DB2OSC.PLAN_TABLES to get the 10 rows. I then tweaked the generated LOAD card and then LOADED those 10 rows using DB2 LOAD utility with SHRLEVEL CHANGE
  • Minor digression: I tend to use LOAD with SHRLEVEL CHANGE when loading a small quantity of rows > SHRLEVEL CHANGE behaves like INSERT… which is very safe… no chance of breaking the target table in any way. And when there are few rows to LOAD/insert, it is plenty fast.
• After the data was loaded into UAT PLAN_TABLE… I then used the following SQL UPDATE to tweak the UAT PLAN_TABLE explanation for the UAT environment!

-- zap UAT PLAN_TABLE contents for OPTHINT to work for the UAT procedure!
UPDATE DBCYPA1.PLAN_TABLE
  SET OPTHINT = 'GOODPATH'
 ,PROGNAME = 'PVR_GET_ADDRESS'
  ,CREATOR = 'DBCYPA1'
  ,ACCESSCREATOR = 'DBCYPA1'
  ,COLLID  = 'DBCYPA1'
  ,QUERYNO = 24  -- oddly, the same procedure name in A1 has a diff queryno then prod
 WHERE COLLID  = 'DBCYPP1'
 AND PROGNAME = 'PVR_GET_ADDRESS'
 AND OPTHINT = ' '
 AND QUERYNO = 22;

At this point, I had trouble with REBIND PACKAGE (the native SQL procedure package) to use OPTHINT

• The REBIND of the procedure package failed. Apparently, you are not allowed to REBIND a native SQL package with some specific parameter options…
  • DSNT215I . . .BECAUSE AT LEAST ONE BIND OPTION IS NOT ALLOWED FOR A NATIVE SQL PL PROCEDURE PACKAGE.

But obviously, I had the SQL SOURCE for this procedure in UAT and prod. (and when I double checked

I dropped the procedure in UAT and created it anew … this time adding the OPTHINT ‘GOODPATH’ and WITH EXPLAIN to the CREATE PROCEDURE parameter list…

CREATE PROCEDURE PVR_GET_ADDRESS(
   IN INP_TABLE_NAME        VARCHAR(30), 
   IN INP_REF_ID            INTEGER,
   OUT MSG_OUT              VARCHAR(200),
   OUT RETCODE              INTEGER)         
 VERSION V1
 LANGUAGE SQL
 NOT DETERMINISTIC
 READS SQL DATA
 COMMIT ON RETURN NO
 DYNAMIC RESULT SETS 1
 ISOLATION LEVEL CS
 PACKAGE OWNER DBCYPA1
 QUALIFIER DBCYPA1
 OPTHINT 'GOODPATH' 
 DISABLE DEBUG MODE
 WITH EXPLAIN
P1: BEGIN

The procedure was successfully created with SQLCODE +394 to indicate the OPTHINT was actually used!

Yeah!

I queried the UAT PLAN_TABLE and I could see the rows for the recent creation of this procedure. The rows looked like I expected. The HINTUSED column said GOODPATH!

I ran the procedure and it ran “fast” in UAT. And now my “testers” in UAT were able to do their thing and they were happy.

So there you go…. Saved by the new “EXPLAIN PACKAGE” SQL statement. 

I am relieved we found a workaround or solution for this specific issue.

I hope that YOU have found this real-life example demonstrative of the usefulness of this EXPLAIN PACKAGE. Once you use it once… it is hard to go back! You can find it is useful in other scenarios once you start to think about it!

• Obviously, it is useful when you have NO PLAN_TABLE contents for the package

• It is also helpful when the PLAN_TABLE contents is inconsistent, or you just don’t trust it… then use EXPLAIN PACKAGE and go back into the PACKAGE to help you confirm the real and current access path.