EXPLAIN_FORMAT stored procedure in Watson Query
You can run the EXPLAIN_FORMAT
stored procedure in
Watson Query to run the db2exfmt
command. You can specify the formatting of
the EXPLAIN
information that is generated when you build query access plans and
download the generated EXPLAIN
output in text files.
The EXPLAIN_FORMAT
procedure formats the contents of the
EXPLAIN
tables based on the specified parameters and updates the formatted data in
the EXPLAIN_STATEMENT table under the column EXPLAIN_FORMAT_TEXT. The procedure also returns an SQL
statement as the OUTPUT parameter, which can be used to fetch the formatted data from the
EXPLAIN_STATEMENT table. For more information about the EXPLAIN
table, see EXPLAIN_STATEMENT table in the Db2® documentation.
The procedure does not issue a COMMIT
statement after updating the
EXPLAIN
tables. The caller of the procedure must issue a COMMIT
statement.
The schema is SYSPROC
.
Authorizations
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- INSERT privilege on the explain tables in the specified schema
- CONTROL privilege on the explain tables in the specified schema
- DATAACCESS authority
- Default PUBLIC privilege
- EXECUTE
Syntax
---
[source,c++]
>>--EXPLAIN_FORMAT----(-- explain_schema-- , --explain_requester--, --explain_time-- , --------------->
>-- ------------ source_name------- , ---------source_schema-------- , --------source_version-- , --->
>-- ------------ object_type------- , ----------------------------object_module----------------------->
v--------|
>-- --section_number-- , ----format_flags------+-----+------- , ---graph_flags-----+---+-------+--------+-----<>
'--O--' '-x-' '--- O --'
'--Y--' '--- I --'
'--C--' '--- C --'
'--- T --'
'--- F --'
---
Procedure parameters
- explain_schema
- An input or output argument of type VARCHAR(128) that specifies the schema that contains the explain tables where the EXPLAIN information should be written. If an empty string or NULL is specified, a search is made for the explain tables under the default schema of the current authorization ID and then the SYSTOOLS schema. If the Explain tables cannot be found under the specified schema, SQL0219N is returned. If the caller does not have INSERT privilege on the explain tables in the specified schema, SQL0551N is returned.
- explain_requester
- An input or output argument of type VARCHAR(128) that specifies the authorization ID of the initiator of this explain request. If an empty string or NULL is specified, a search is made for the explain tables under the current session.
- explain_time
- An input or output argument of type TIMESTAMP that contains the time of initiation for the Explain request. If NULL is specified, then obtain the latest explain request.
- source_name
- An input or output argument of type VARCHAR(128) that specifies the Package name (SOURCE_NAME) or object name for the explain request. Package is assumed if the object_type option is not specified.
- source_schema
- An input or output argument of type VARCHAR(128) that specifies the Package schema (SOURCE_SCHEMA) of the package request. If a package schema is not specified, this option is set to '%'. If the object_module parameter is provided for the procedure or function, then this option corresponds to the module schema. If the object type is a procedure, function, or trigger, this is the schema of the associated object. If the object type is not a procedure, function, or trigger, and the object_module parameter is not provided then the schema is set to the value of the CURRENT SCHEMA special register.
- source_version
- An input or output argument of type VARCHAR(128) that specifies the Package version (SOURCE_VERSION) of the explain request. The default value is %.
- section_number
- An input or output argument of type INTEGER that contains the section number in the source. To request all sections, specify zero.
- object_type
- Type of the object specified. The default type is package.
- object_module
- Module name of the routine when the object_type option is P, SP, F, or SF. Module names are ignored if object_type parameter is not specified.
- format_flags
- An input argument of type VARCHAR(128) that contains multiple flags that can be combined
together as a string. If an empty string or NULL is specified, then the formatting options are
determined automatically.
- O: Operator summary
- Y: Force formatting of the original statement even if column EXPLAIN_STATEMENT.EXPLAIN_TEXT contains formatting. The default behavior is to automatically detect if the statement requires formatting and use the original formatting when it exists.
- C: Use a more compact mode when formatting statements and predicates. The default is an expanded mode. If Y is not specified, then C takes effect only if automatic detection determines that the statement requires formatting.
- graph_flags
- An input argument of type VARCHAR(128) that contains multiple graph flags that can be combined together as a string. If an empty string or NULL is specified, then 'TIC' is the default option.
- extract_sql
- An output argument of type VARCHAR(2048) that contains the SQL statement which can be used to extract the formatted data against EXPLAIN_STATEMENT.
Usage notes
The parameters explain_schema, explain_requester, explain_time, source_schema, source_name, source_version, section_number comprise the key used to look up the information for the section in the explain tables. If NULL, empty or wildcard inputs were provided for these parameters, the actual value used will be updated in the parameter on return.
- Use passed parameters to format the EXPLAIN information retrieved from the explain tables.
- Update the formatted data in the EXPLAIN_STATEMENT table under the column EXPLAIN_FORMAT_TEXT.
- Returns the actual values used for all the INOUT parameters (i.e. explain_schema, explain_requester, explain_time, source_schema, source_name ,source_version , section_number).
- If the procedure is successful, the OUT parameter EXTRACT_SQL is populated with an example SQL statement that can be used to retrieve formatted data from the EXPLAIN_STATEMENT table. Otherwise, it is populated with an error message.
Example
- Create the explain tables and gather explain data for the query or queries of interest using the methods documented in https://www.ibm.com/docs/en/db2/11.5?topic=optimization-explain-facility.
- Call the
EXPLAIN_FORMAT
stored procedure, as follows:Call explain_format('DB2INST1', 'DB2INST1', '2022-11-08-02.28.42.810882', 'SQLC2P31', 'NULLID', '', 0, '', '', 'T', ?)
Use the parameters from the following table:
Type Parameter List Sample Value in Above Example INOUT Explain_Schema DB2INST1 INOUT Explain_Requester DB2INST1 INOUT Explain_Time 2022-11-08-02.28.42.810882 INOUT Source_Name SQLC2P31 INOUT Source_Schema NULLID INOUT Source_Version INOUT Section_number 0 IN Object_type IN Object_Module IN Graph_flags T OUT Extract_SQL ? - Extract the formatted data from the EXPLAIN_STATEMENT table by running the following SQL
statement from the extract_sql OUT
parameter:
The query shows the formatted explain plan similar to the following example:select EXPLAIN_FORMAT_TEXT from "DB2INST1".EXPLAIN_STATEMENT where EXPLAIN_REQUESTER='DB2INST1' and EXPLAIN_TIME='2022-11-08-02.28.42.810882' and SOURCE_NAME='SQLC2P31' and SOURCE_SCHEMA='NULLID' and SOURCE_VERSION='' and SECTION_NUMBER=0 and EXPLAIN_LEVEL='O' FOR READ ONLY;
EXPLAIN INSTANCE:DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019 Licensed Material - Program Property of IBM IBM DATABASE 2 Explain Table Format Tool
DB2_VERSION: 11.05.9 FORMATTED ON DB: SAURABH SOURCE_NAME: SQLC2P31 SOURCE_SCHEMA: NULLID SOURCE_VERSION: EXPLAIN_TIME: 2022-11-08-02.28.42.810882 EXPLAIN_REQUESTER: DB2INST1
Database Context: ---------------- Parallelism: None CPU Speed: 4.000000e-05 Comm Speed: 0 Buffer Pool size: 697394 Sort Heap size: 3090 Database Heap size: 5099 Lock List size: 106213 Maximum Lock List: 98 Average Applications: 1 Locks Available: 3330839
Package Context: --------------- SQL Type: Dynamic Optimization Level: 5 Blocking: Block All Cursors Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ---------------- QUERYNO: 1 QUERYTAG: CLP Statement Type: Select Updatable: No Deletable: No Query Degree: 1
Original Statement: ------------------ select * from EXPLAIN_ACTUALS
Optimized Statement: ------------------- SELECT Q1.EXPLAIN_REQUESTER AS "EXPLAIN_REQUESTER", Q1.EXPLAIN_TIME AS "EXPLAIN_TIME", Q1.SOURCE_NAME AS "SOURCE_NAME", Q1.SOURCE_SCHEMA AS "SOURCE_SCHEMA", Q1.SOURCE_VERSION AS "SOURCE_VERSION", Q1.EXPLAIN_LEVEL AS "EXPLAIN_LEVEL", Q1.STMTNO AS "STMTNO", Q1.SECTNO AS "SECTNO", Q1.OPERATOR_ID AS "OPERATOR_ID", Q1.DBPARTITIONNUM AS "DBPARTITIONNUM", Q1.PREDICATE_ID AS "PREDICATE_ID", Q1.HOW_APPLIED AS "HOW_APPLIED", Q1.ACTUAL_TYPE AS "ACTUAL_TYPE", Q1.ACTUAL_VALUE AS "ACTUAL_VALUE" FROM DB2INST1.EXPLAIN_ACTUALS AS Q1
Access Plan: ----------- Total Cost: 9.33976 Query Degree: 1
Rows RETURN ( 1) Cost I/O | 7 TBSCAN ( 2) 9.33976 1 | 7 TABLE: DB2INST1 EXPLAIN_ACTUALS Q1
Extended Diagnostic Information: -------------------------------- Diagnostic Identifier: 1 Diagnostic Details: EXP0020W Statistics have not been collected for table "DB2INST1"."EXPLAIN_ACTUALS". This may result in a sub-optimal access plan and poor performance. Statistics should be collected for this table.
Plan Details: ------------- 1) RETURN: (Return Result) Cumulative Total Cost: 9.33976 Cumulative CPU Cost: 64369 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 0.55104 Cumulative Re-CPU Cost: 13776 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 8.8576 Estimated Bufferpool Buffers: 1 Arguments: --------- BLDLEVEL: (Build level) DB2 v11.5.9.0 : z2201010100 CPUCACHE: (Per-thread CPU cache size) 16777216 HEAPUSE : (Maximum Statement Heap Usage) 96 Pages PLANID : (Access plan identifier) 4e88ef7dccf8bbe7 PREPTIME: (Statement prepare time) 37 milliseconds SEMEVID : (Semantic environment identifier) e58edaa6cc913871 STMTHEAP: (Statement heap size) 8192 STMTID : (Normalized statement identifier) 226ceef303eb75ac TENANTID: (Compiled In Tenant ID) 0 TENANTNM: (Compiled In Tenant Name) SYSTEM Input Streams: ------------- 2) From Operator #2 Estimated number of rows: 7 Number of columns: 14 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.ACTUAL_VALUE+Q2.ACTUAL_TYPE+Q2.HOW_APPLIED +Q2.PREDICATE_ID+Q2.DBPARTITIONNUM +Q2.OPERATOR_ID+Q2.SECTNO+Q2.STMTNO +Q2.EXPLAIN_LEVEL+Q2.SOURCE_VERSION +Q2.SOURCE_SCHEMA+Q2.SOURCE_NAME +Q2.EXPLAIN_TIME+Q2.EXPLAIN_REQUESTER 2) TBSCAN: (Table Scan) Cumulative Total Cost: 9.33976 Cumulative CPU Cost: 64369 Cumulative I/O Cost: 1 Cumulative Re-Total Cost: 0.55104 Cumulative Re-CPU Cost: 13776 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 8.8576 Estimated Bufferpool Buffers: 1 Arguments: --------- CUR_COMM: (Currently Committed) TRUE LCKAVOID: (Lock Avoidance) TRUE MAXPAGES: (Maximum pages for prefetch) ALL PREFETCH: (Type of Prefetch) NONE ROWLOCK : (Row Lock intent) SHARE (CS/RS) SCANDIR : (Scan Direction) FORWARD SKIP_INS: (Skip Inserted Rows) TRUE SPEED : (Assumed speed of scan, in sharing structures) FAST TABLOCK : (Table Lock intent) INTENT SHARE TBISOLVL: (Table access Isolation Level) CURSOR STABILITY THROTTLE: (Scan may be throttled, for scan sharing) TRUE VISIBLE : (May be included in scan sharing structures) TRUE WRAPPING: (Scan may start anywhere and wrap) TRUE Input Streams: ------------- 1) From Object DB2INST1.EXPLAIN_ACTUALS Estimated number of rows: 7 Number of columns: 15 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.$RID$+Q1.ACTUAL_VALUE+Q1.ACTUAL_TYPE +Q1.HOW_APPLIED+Q1.PREDICATE_ID +Q1.DBPARTITIONNUM+Q1.OPERATOR_ID+Q1.SECTNO +Q1.STMTNO+Q1.EXPLAIN_LEVEL+Q1.SOURCE_VERSION +Q1.SOURCE_SCHEMA+Q1.SOURCE_NAME +Q1.EXPLAIN_TIME+Q1.EXPLAIN_REQUESTER Output Streams: -------------- 2) To Operator #1 Estimated number of rows: 7 Number of columns: 14 Subquery predicate ID: Not Applicable Column Names: ------------ +Q2.ACTUAL_VALUE+Q2.ACTUAL_TYPE+Q2.HOW_APPLIED +Q2.PREDICATE_ID+Q2.DBPARTITIONNUM +Q2.OPERATOR_ID+Q2.SECTNO+Q2.STMTNO +Q2.EXPLAIN_LEVEL+Q2.SOURCE_VERSION +Q2.SOURCE_SCHEMA+Q2.SOURCE_NAME +Q2.EXPLAIN_TIME+Q2.EXPLAIN_REQUESTER Objects Used in Access Plan: --------------------------- Schema: DB2INST1 Name: EXPLAIN_ACTUALS Type: Table Time of creation: 2022-11-08-02.27.27.843587 Last statistics update: Number of columns: 14 Number of rows: 7 Width of rows: 392 Number of buffer pool pages: 1 Number of data partitions: 1 Distinct row values: No Tablespace name: USERSPACE1 Tablespace overhead: 6.725000 Tablespace transfer rate: 0.040000 Source for statistics: Single Node Prefetch page count: 32 Container extent page count: 32 Table overflow record count: 0 Table Active Blocks: -1 Average Row Compression Ratio: -1 Percentage Rows Compressed: -1 Average Compressed Row Size: -1