How to Use Db2 Explain

How do I use DB2 Explain?

What you're looking for is covered by two DB2 utilities:

1. The explain utility, which shows the optimizer's access plan and estimated cost for a specific query (based on current RUNSTATS statistics)
2. The design advisor, which recommends structural changes to improve the performance of one or more queries

Both utilities require specialized tables to be created in the database.

I tend to use the explain utility more than the advisor, especially if I am able to change the SQL for the query to influence a better access plan. I use the db2expln command-line utility to explain different versions of a query I'm tuning and compare their costs. What's most important is that your table and index statistics are up to date when running explain or the design advisor.

db2 explain stored procedure

If I have understood your requirements correctly you have a procedure where you would like to explain the plan for a query inside that procedure. I'll invent some bogus stuff to explain my thoughts:

create table t 
(  x int not null primary key
,  y int not null) @

create procedure p (n int)
language sql
begin
    declare c cursor for
    select count(1) from t where y = n;
end @

Assuming you would like to explain the plan for the query in the cursor:

db2 "explain plan for select count(1) from t where y = n"
[...]
SQL0206N  "N" is not valid in the context where it is used.  SQLSTATE=42703

Since n is not bound the compiler will complain. However, changing n to a host variable or a parameter marker will be fine (note the ":" )

db2 "explain plan for select count(1) from t where y = :n"

or:

db2 "explain plan for select count(1) from t where y = ?"

Now you can use db2exfmt to look at the plan:

db2exfmt -d sample -g -1 | tee q.plan

Access Plan:
-----------
Total Cost:         0.00644873
Query Degree:       1

            Rows 
           RETURN
           (   1)
            Cost 
             I/O 
             |
              1 
           GRPBY 
           (   2)
          0.0063121 
              0 
             |
              0 
           FETCH 
           (   3)
         0.00627372 
              0 
       /-----+-----\
      0               0 
   IXSCAN    TABLE:    LELLE   
   (   4)             T
 0.00613403          Q1
      0 
     |
      0 
INDEX:    SYSIBM  
SQL141230182649950
        Q1

I think you will find db2exfmt a much better tool than db2expln, you will get a lot more details for your plan.

Generating/Populating Explain tables in DB2 and usage of db2exfmt

As the error message indicates, and the manual says, explain_schema is an INOUT parameter, so you cannot specify a literal value.

You may want to try wrapping the procedure call in a compound statement, providing declared variables for each OUT and INOUT parameter, something like:

begin
  declare v_schema varchar(50) default 'SYSTOOLS';
  declare v_req, v_srcname, v_srcschema, v_srcver varchar(128);
  declare v_ts timestamp;

  call EXPLAIN_FROM_CATALOG( 'XYZ', 'P123456', ' ', 5, v_schema,
                             v_req, v_ts, v_srcname, v_srcschema, v_srcver );
end

PS. Code is not tested

Explanation of DB2 Access plan Graph

Read it from the bottom up. Each node is an operation that is done to satisfy the query. TBSCAN means that the entire table is scanned (which will often happen when your tables are small). HSJOIN is a 'Hash Join' -- joining rows from two tables in memory with a hashmap.

Let's look at one node

          4
       TBSCAN
       (   3)
       7.58097
          1
          |
          4
   TABLE: DB2INST1.TABA

The top '4' is the number of rows returned
The next row 'TBSCAN' is the algorithm used (TBSCAN means table scan. HSJOIN means join by hashmap). In DB2, this is called the 'Operator'
The '(3)' is the sequence number. You can find this query is decomposed into 4 steps
The 7.58097 is the cumulative cost to perform this query up to this step, in something called 'timerons'. It's adjusted based on how your database is configured, and the optimizer will choose an access plan that minimizes this cost
The bottom 4 is the cost of the operation in terms of I/O.

There is a lot more information from db2exfmt that you have omitted. But the graph tells you how DB2 will execute the query.

---

Related Topics