Generating/Populating Explain tables in DB2 and usage of db2exfmt

Question

I'm having a problem while i'm trying to collect explain plan with the db2exfmt tool.

Can some body explain me the process of how to use that tool?

My requirement is to collect cost of a stored procedure. I have the EXPLAIN tables created in instance 'XYZ' schema and I have a procedure named "UNNAMED", which has the package name "P123456" and the schema "ABCD".

I used the following commands:

! db2exfmt -d SAMPLE -e DB2INST1 -s ABCD -n P123456 -g TIC -w -1 -#***5*** -t

Wherein 5 is the Section Number of the part of the procedure I'm trying to collect cost for.

Furthermore, I have also tried to do the following:

1) Identify the package corresponding to the stored proc :

select r.routineschema, r.routinename, rd.bname as packagename 
from syscat.routines r, syscat.routinedep rd 
where 
    r.specificname=rd.specificname and
    r.routineschema=rd.routineschema and 
    rd.btype='K' and 
    r.routineschema = 'XYZ' and 
    r.routinename = 'ABCD'

2) Identify the section number for the SQL statement :

select sectno, text 
from syscat.statements 
where pkgschema='XYZ' and pkgname='P123456'

3) Populate Explain tables :

call EXPLAIN_FROM_CATALOG( 'XYZ', 'P123456', ' ', 5, 'SYSTOOLS', ?, ?, ?, ?, ? )  

The latter throws an error:

Message: The parameter mode OUT or INOUT is not valid for a parameter in the routine named "EXPLAIN_FROM_CATALOG" with specific name "EXPLAIN_FROM_CATALOG" (parameter number "5", name "EXPLAIN_SCHEMA").. SQLCODE=-469, SQLSTATE=42886, DRIVER=3.50.152

I am logged in as USER : "MNO" and want the explain tables under SYSTOOLS schema to be populated.

Can someone please help me resolve the problem?

Answer 1

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