EXPLAIN_FORMAT stored procedure

You can run the EXPLAIN_FORMAT stored procedure in Db2 to format the contents of the EXPLAIN tables. 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, see EXPLAIN_STATEMENT table.

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

Read syntax diagramSkip visual syntax diagram EXPLAIN_FORMAT ( explain_schema,explain_requester,explain_time,source_name,source_schema,source_version,object_type,object_modulesection_number,OYC, x OICTF )

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, and the actual value used is 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 Explain-facility.
  2. Call the EXPLAIN_FORMAT stored procedure, as follows:
    Call explain_format('DB2INST1', 'DB2INST1','2024-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