0 / 0
Go back to the English version of the documentation
Data Virtualization中的 EXPLAIN_FORMAT 存储过程
Last updated: 2024年11月26日
Data Virtualization中的 EXPLAIN_FORMAT 存储过程

您可以在Data Virtualization中运行 "EXPLAIN_FORMAT存储过程，以运行 "db2exfmt命令。 您可以指定在构建查询存取方案和在文本文件中下载生成的 EXPLAIN 输出时生成的 EXPLAIN 信息的格式。

EXPLAIN_FORMAT 过程根据指定的参数对 EXPLAIN 表的内容进行格式化，并更新 EXPLAIN_STATEMENT 表中 EXPLAIN_FORMAT_TEXT 列下的格式化数据。 该过程还将 SQL 语句作为 OUTPUT 参数返回，该参数可用于从 EXPLAIN_STATEMENT 表中访存格式化数据。 有关 EXPLAIN 表的更多信息，请参阅 Db2® 文档中的 EXPLAIN_STATEMENT 表 。

在更新 EXPLAIN 表之后，该过程不会发出 COMMIT 语句。 过程的调用者必须发出 COMMIT 语句。

模式为 SYSPROC。

授权

需要下列其中一个权限或特权才能运行例程:
  • 对例程的 EXECUTE 特权
  • DATAACCESS 权限
  • DBADM 权限
  • SQLADM 权限
此外，会话的授权标识所拥有的特权 (包括授予组的特权) 必须至少包含下列其中一个选项:
  • 对指定模式中的说明表的 INSERT 特权
  • 对指定模式中的说明表的 CONTROL 特权
  • DATAACCESS 权限
  • 缺省 PUBLIC 特权
  • EXECUTE

语法

---
[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 --'
	                                                                                                                                      
                                                                              
        ---

过程参数

说明模式
类型为 VARCHAR (128) 的输入或输出参数，用于指定包含应写入 EXPLAIN 信息的说明表的模式。 如果指定了空字符串或 NULL ，那么将在当前授权标识的缺省模式下搜索说明表，然后搜索 SYSTOOLS 模式。 如果在指定模式下找不到 Explain 表，那么将返回 SQL0219N 。 如果调用者对指定模式中的说明表没有 INSERT 特权，那么将返回 SQL0551N 。
EXPLAIN_REQUESTER
类型为 VARCHAR (128) 的输入或输出参数，用于指定此说明请求的发起方的授权标识。 如果指定了空字符串或 NULL ，那么将搜索当前会话下的说明表。
EXPLAIN_TIME
类型为 TIMESTAMP 的输入或输出参数，其中包含 Explain 请求的启动时间。 如果指定 NULL ，那么获取最新的说明请求。
SOURCE_NAME
类型为 VARCHAR (128) 的输入或输出参数，用于指定说明请求的包名 (SOURCE_NAME) 或对象名。 如果未指定 object_type 选项，那么将采用包。
SOURCE_SCHEMA
类型为 VARCHAR (128) 的输入或输出参数，用于指定程序包请求的程序包模式 (SOURCE_SCHEMA)。 如果未指定包模式，那么此选项将设置为 "%"。 如果为过程或函数提供了 object_module 参数，那么此选项对应于模块模式。 如果对象类型是过程，函数或触发器，那么这是关联对象的模式。 如果对象类型不是过程，函数或触发器，并且未提供 object_module 参数，那么会将模式设置为 CURRENT SCHEMA 专用寄存器的值。
SOURCE_VERSION
类型为 VARCHAR (128) 的输入或输出参数，用于指定说明请求的包版本 (SOURCE_VERSION)。 缺省值为%。
section_number
类型为 INTEGER 的输入或输出自变量，其中包含源中的节号。 要请求所有部分，请指定零。
对象类型
指定对象的类型。 缺省类型为 package。
  • PK: 程序包名
  • P: SQL 过程名称
  • SP: 特定 SQL 过程名称
  • F: 编译函数
  • SF: 特定已编译函数名
  • T: 已编译的触发器
object_module
当 object_type 选项为 P ， SP ， F 或 SF 时，例程的模块名称。 如果未指定 object_type 参数，那么将忽略模块名称。
format_flags
类型为 VARCHAR (128) 的输入参数，其中包含可组合为字符串的多个标志。 如果指定了空字符串或 NULL ，那么将自动确定格式化选项。
  • O: 运算符摘要
  • Y: 强制格式化原始语句，即使列 EXPLAIN_STATEMENT.EXPLAIN_TEXT 包含格式设置。 缺省行为是自动检测语句是否需要格式化并在存在时使用原始格式化。
  • C: 格式化语句和谓词时使用更紧凑的方式。 缺省值为展开方式。 如果未指定 Y ，那么仅当自动检测确定语句需要格式化时， C 才会生效。
图形标志
类型为 VARCHAR (128) 的输入参数，其中包含可组合为字符串的多个图形标志。 如果指定了空字符串或 NULL ，那么缺省选项为 "TIC"。
  • 关闭选项 (缺省值为打开这些选项)。 如果为空，那么将生成一个图形，后跟所有表的格式化信息。 否则，可以指定以下有效值的任意组合:
    • O: 仅生成图形。 请勿格式化表内容。
    • T: 在图形中的每个运算符下包含总成本。
    • F: 在图形中包含第一个元组成本。
    • I: 在图形中的每个操作程序下包含 I/O 成本。
    • C: 在图形中包含每个运算符的期望输出基数 (元组数)。
    • 注: 允许这些选项的任何组合，但 F 和 T 除外，它们是互斥的。
抽取 SQL
类型为 VARCHAR (2048) 的输出参数，其中包含可用于针对 EXPLAIN_STATEMENT 抽取格式化数据的 SQL 语句。

使用说明

参数 explain_schema ， explain_requester ， explain_time ， source_schema ， source_name ， source_version 和 section_number 组成用于查找说明表中部分的信息的键。 如果为这些参数提供了 NULL ，空或通配符输入，那么返回时将在参数中更新所使用的实际值。

该过程执行以下功能:
  • 使用传递的参数来格式化从说明表中检索的 EXPLAIN 信息。
  • 更新 EXPLAIN_STATEMENT 表中 EXPLAIN_FORMAT_TEXT 列下的格式化数据。
  • 返回用于所有 INOUT 参数 (例如， explain_schema ， explain_requester ， explain_time ， source_schema ， source_name ， source_version 和 section_number) 的实际值。
  • 如果该过程成功，那么将使用可用于从 EXPLAIN_STATEMENT 表检索格式化数据的示例 SQL 语句来填充 OUT 参数 EXTRACT_SQL。 否则，将使用错误消息进行填充。

示例

  1. 使用 https://www.ibm.com/docs/en/db2/11.5?topic=optimization-explain-facility中记录的方法创建说明表并收集感兴趣的一个或多个查询的说明数据。
  2. 调用 EXPLAIN_FORMAT 存储过程，如下所示:
    Call explain_format('DB2INST1', 'DB2INST1', '2022-11-08-02.28.42.810882', 'SQLC2P31', 'NULLID', '', 0, '', '', 'T', ?)

    使用下表中的参数:

    类型 参数列表 以上示例中的样本值
    进出 说明模式 DB2INST1
    进出 EXPLAIN_REQUESTER DB2INST1
    进出 EXPLAIN_TIME 2022-11-08-02.28.42.810882
    进出 SOURCE_NAME SQLC2P31
    进出 SOURCE_SCHEMA NULLID
    进出 SOURCE_VERSION  
    进出 section_number 0
    IN OBJECT_TYPE  
    IN object_module  
    IN Graph_flags T
    输出 抽取 SQL ?
  3. 通过从 extract_sql OUT 参数运行以下 SQL 语句，从 EXPLAIN_STATEMENT 表中抽取格式化数据:
    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;
    此查询将显示类似于以下示例的格式化说明计划:
    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

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