db2 explain stored procedure

Question

How to explain stored procedure in IBM Data Studio?

I know that there exist db2expln command. But I want a solution to explain the SP in graphical interface.

Also I know that selecting a query and then right clicking on it, there exists open visual explain menu do the explain but don't know the way to explain SP in order that I can set input values for that SP.

thanks

Answer 1

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.