Collecting explain data for SQL stored procedures in DB2

Question

How to collect explain data / access plan for SQL stored procedures in DB2?

Answer

In both techniques outlined below, the explain tables need to be created. For more information on doing this task, see Explain Tables.

Technique 1: Obtain the explain plan when creating the stored procedure:

1) Turn on explain either:

• Dynamically within the scope of the current session by issuing
  db2 "call SET_ROUTINE_OPTS('EXPLAIN ALL')" 
  
• Or, globally at the instance level if the procedure is not being called within the current session
  db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL"
  db2 terminate 
  db2stop 
  db2start 
  

2) Run the create statement for the SQL procedure that requires explaining. If one of the same name already exists, you may need to drop the procedure first or create a similar procedure under a different schema or name. 

3) The resulting explain data will be stored in the explain tables and can be extracted with a command such as the following: 
db2exfmt -d db_name -g TIC -w -1 -n % -s % -# 0 -o outputfilename

4) Disable explain by either issuing either one of these commands depending on how it was enabled. 

• If it was turned on for the current session: 
  db2 "CALL SYSPROC.SET_ROUTINE_OPTS('')"
  
• If it was turned on globally
  db2set DB2_SQLROUTINE_PREPOPTS=
  db2 terminate 
  db2stop 
  db2start

Technique 2: Obtain the explain plan from the package without needing to re-create the procedure: 

Assuming the explain tables under a schema called EXPLAIN_SCHEMA, do the following: 

1) Call the stored procedure 

2) Using a unique portion of the SQL statement from the body of the stored procedure logic you are interested in obtaining the explain for, use the following SQL statement to obtain the executable_id. This is an example if the SQL statement contains "INSERT INTO SYSIBM.SYSDUMMY1". Note: The query is case-sensitive.

db2 "SELECT executable_id FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL,NULL, -1)) AS T where stmt_text like '%INSERT INTO SYSIBM.SYSDUMMY1%'"

3) With the executable_id returned in step 2 (For example: x'0100000000000000581E00000000000002000000010020140109134503816499'), make the following call. Also replace EXPLAIN_SCHEMA with your actual explain schema.

db2 "call EXPLAIN_FROM_SECTION(x'0100000000000000581E00000000000002000000010020140109134503816499', 'M', NULL, 0, 'EXPLAIN_SCHEMA', ?, ?, ?, ?, ?)"

This will populate the explain tables under the schema you specified

4) Run this command to extract the explain plan:
db2exfmt -d <db name> -g TIC -w -1 -n % -s % -# 0 -o exfmt.out

http://www-01.ibm.com/support/docview.wss?uid=swg21279292