Question & Answer
Question
How to collect explain data / access plan for SQL stored procedures and dynamic SQL?
Answer
Configuration:
In all techniques outlined below, the explain tables need to be created. For more information on doing this task, see Explain Tables.
db2 "connect to sample"
db2 -tvf ~/sqllib/misc/EXPLAIN.DDL
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
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) Use the following technote to identify the name of the package that corresponds to your SQL routine: How to map stored procedure and user defined function (UDF) names to packages
2) Identify the section number for the SQL statement within the routine you are interested in obtaining the explain for. Run a query such as this, replace SCHEMA_NAME & P854000274 with the name of the schema and package identified in step 1.
db2 "select sectno, text from syscat.statements where pkgschema='SCHEMA_NAME' and pkgname='P854000274'"
3) Call EXPLAIN_FROM_CATALOG, replace SCHEMA_NAME & P854000274 with the name of the schema and package identified in step 1 and replace EXPLAIN_SCHEMA with the name of the schema for which the explain tables were created under. The 4th input parameter identifies the section number. The example below shows a command to obtain the explain for the statement for section 1. Replace this number with the proper section number that was identified in step 2.
db2 "call EXPLAIN_FROM_CATALOG( 'SCHEMA_NAME', 'P854000274', ' ', 1, 'EXPLAIN_SCHEMA', ?, ?, ?, ?, ? )"
This will populate the explain tables under the EXPLAIN_SCHEMA you specified
4) Run this command to extract the explain plan. Replace SCHEMA_NAME, P854000274, and EXPLAIN_SCHEMA respectively similar to the commands above:
db2exfmt -d sample -g TIC -w % -s SCHEMA_NAME -n P854000274 -# 0 -e EXPLAIN_SCHEMA -o exfmt.out
Technique 3: Obtain the explain plan from dynamic SQL section
Refer to EXPLAIN_FROM_SECTION procedure for reference. This example assumes we are looking for the explain for the query "select c1 from t2 where c2 = ?".
The query below may return multiple executable_ids. The output may also dynamically change and by the time CALL EXPLAIN_FROM_SECTION is called, the executable_id may be gone and the error SQL20501N The explain facility failed because the specified section could not be found. Reason code = "1" is returned.
select executable_id, Total_cpu_time, Varchar('select c1 from t2 where c2 = ?', 200) as stmt_text from table(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -1)) as T
-------------------------------------------------------------------
x'0000000100000000000000000000007100000000000220161013193227871860'
x'0000000100000000000000000000007200000000000220161013193227880208'
TOTAL_CPU_TIME STMT_TEXT
-------------------- -------------------------------
13 select c1 from t2 where c2 = ?
1376 select c1 from t2 where c2 = ?
DB2INST1 is the schema where the explain tables are located. Here we only generate the explain plan for one of the executable_ids.
Value of output parameters
--------------------------
Parameter Name : EXPLAIN_SCHEMA
Parameter Value : DB2INST1
Parameter Name : EXPLAIN_REQUESTER
Parameter Value : DB2INST1
Parameter Name : EXPLAIN_TIME
Parameter Value : 2016-10-25-15.04.28.489886
Parameter Name : SOURCE_NAME
Parameter Value : SYSSH200
Parameter Name : SOURCE_SCHEMA
Parameter Value : NULLID
Parameter Name : SOURCE_VERSION
Parameter Value :
Return Status = 0
Generate the access plan for the section and output to file db2exfmt_bad.out
db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -o db2exfmt_bad.out
db2exfmt -d sample -e db2inst1 -g TIC -w 2016-10-25-15.04.28.489886 -n SYSSH200 -s NULLID -o db2exfmt.out -# 0
Notes:
- Specify the EXPLAIN_TIME, SOURCE_NAME and SOURCE_SCHEMA from step #3. Otherwise the plan of a different query is generated.
- For dynamic SQL always ensure SOURCE_SCHEMA=NULLID and SOURCE_NAME is SYSS* which is the name of dynamic placeholder packages used to execute dynamic SQL. Some execution ids may belong to same query located inside static SQL package which has a different name.
- Reference: Example: Investigating query performance using Explain information obtained from a section
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21279292