0 / 0
EXPLAIN_FORMAT stored procedure in Data Virtualization
Last updated: Nov 26, 2024
EXPLAIN_FORMAT stored procedure in Data Virtualization

You can run the EXPLAIN_FORMAT stored procedure in Data Virtualization 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

One of the following authorities or privileges is required to run the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority
In addition, the privileges that are held by the authorization ID of the session, including privileges that are granted to groups, must include at least one of the following options:
  • 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.
  • PK: Package name
  • P: SQL procedure name
  • SP: A specific SQL procedure name
  • F: Compiled function
  • SF: A specific compiled function name
  • T: Compiled trigger
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.
  • Turn OFF options (default is to turn them ON). If empty, a graph, followed by formatted information for all of the tables, is generated. Otherwise, any combination of the following valid values can be specified:
    • O: Generate a graph only. Do not format the table contents.
    • T: Include total cost under each operator in the graph.
    • F: Include first tuple cost in graph.
    • I: Include I/O cost under each operator in the graph.
    • C: Include the expected output cardinality (number of tuples) of each operator in the graph.
    • Note: Any combination of these options is allowed, except F and T, which are mutually exclusive.
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.

The procedure performs the following functions:
  • 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

  1. 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.
  2. 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 ?
  3. Extract the formatted data from the EXPLAIN_STATEMENT table by running the following SQL statement from the extract_sql OUT parameter:
    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;
    The query shows the formatted explain plan similar to the following example:
    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
    EXPLAIN INSTANCE:
    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

Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more