Procedimento armazenado EXPLAIN_FORMAT na Data Virtualization

Você pode executar o procedimento armazenado " EXPLAIN_FORMAT na Data Virtualization para executar o comando " db2exfmt. É possível especificar a formatação das informações do EXPLAIN que são geradas ao construir planos de acesso de consulta e fazer download da saída EXPLAIN gerada em arquivos de texto.

O procedimento EXPLAIN_FORMAT formata o conteúdo das tabelas EXPLAIN com base nos parâmetros especificados e, em seguida, atualiza os dados formatados na tabela EXPLAIN_STATEMENT na coluna EXPLAIN_FORMAT_TEXT. O procedimento também retorna uma instrução SQL como o parâmetro OUTPUT, que pode ser usado para buscar os dados formatados da tabela EXPLAIN_STATEMENT. Para obter mais informações sobre a tabela EXPLAIN , consulte Tabela EXPLAIN_STATEMENT na documentação Db2 .

O procedimento não emite uma instrução COMMIT após a atualização das tabelas EXPLAIN O responsável pela chamada do procedimento deve emitir uma instrução COMMIT ..

O esquema é SYSPROC

Autorizações

Uma das autoridades ou privilégios a seguir é necessária para executar a rotina:
  • Privilégio EXECUTE na rotina
  • Autoridade DATAACCESS
  • autoridade DBADM
  • Autoridade SQLADM
Além disso, os privilégios que são mantidos pelo ID de autorização da sessão, incluindo privilégios que são concedidos a grupos, devem incluir pelo menos uma das opções a seguir:
  • Privilégio INSERT nas tabelas EXPLAIN no esquema especificado
  • Privilégio CONTROL nas tabelas EXPLAIN no esquema especificado
  • Autoridade DATAACCESS
  • Privilégio PUBLIC padrão
  • EXECUTE

Sintaxe

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

Parâmetros de procedimento

explicar_esquema
Um argumento de entrada ou saída do tipo VARCHAR (128) que especifica o esquema que contém as tabelas de explicação em que as informações EXPLAIN devem ser gravadas. Se uma sequência vazia ou NULL for especificado, uma procura será feita para as tabelas de explicações sob o esquema padrão do ID de autorização atual e, em seguida, o esquema SYSTOOLS. Se as tabelas Explain não puderem ser localizadas no esquema especificado, SQL0219N será retornado. Se o responsável pela chamada não tiver privilégio INSERT nas tabelas de explicação no esquema especificado, SQL0551N será retornado.
explicar_solicitante
Um argumento de entrada ou saída do tipo VARCHAR (128) que especifica o ID de autorização do inicializador deste pedido de explicação. Se uma sequência vazia ou NULL for especificado, uma procura será feita para as tabelas de explicação sob a sessão atual
EXPLAIN_TIME
Um argumento de entrada ou de saída do tipo TIMESTAMP que contém o horário de iniciação para a solicitação do Explain Se NULL for especificado, obtenha a solicitação de explicação mais recente.
SOURCE_NAME
Um argumento de entrada ou de saída do tipo VARCHAR (128) que especifica o nome do Pacote (SOURCE_NAME) ou o nome do objeto para a solicitação de explicação O pacote será assumido se a opção object_type não for especificada
SOURCE_SCHEMA
Um argumento de entrada ou de saída do tipo VARCHAR (128) que especifica o esquema do Pacote (SOURCE_SCHEMA) da solicitação do pacote... Se um esquema de pacote não for especificado, essa opção será configurada como '%' Se o parâmetro object_module for fornecido para o procedimento ou função, essa opção corresponderá ao esquema do módulo. Se o tipo de objeto for um procedimento, função ou acionador, essa opção será o esquema do objeto associado. Se o tipo de objeto não for um procedimento, função ou acionador e o parâmetro object_module não for fornecido, o esquema será configurado para o valor do registro especial CURRENT SCHEMA.
versão_fonte
Um argumento de entrada ou saída do tipo VARCHAR (128) que especifica a versão do Pacote (SOURCE_VERSION) da solicitação de explicação. O valor padrão é%.
section_number
Um argumento de entrada ou saída do tipo INTEGER que contém o número da seção na origem. Para solicitar todas as seções, especifique zero.
object_type
Tipo do objeto especificado. O tipo padrão é pacote.
  • PK: Nome do pacote
  • P: Nome do procedimento SQL
  • SP: Um nome de procedimento SQL específico
  • F: Função compilada
  • SF: Um nome de função específica compilada
  • T: Acionador compilado
módulo_objeto
Nome do módulo da rotina quando a opção object_type é P, SP, F ou SF. Os nomes do módulo serão ignorados se o parâmetro object_type não for especificado
sinalizadores_de_formato
Um argumento de entrada do tipo VARCHAR (128) que contém vários sinalizadores que podem ser combinados juntos como uma cadeia. Se uma sequência vazia ou NULL for especificado, as opções de formatação serão determinadas automaticamente.
  • O: Resumo do operador
  • Y: Forçar a formatação da instrução original mesmo se a coluna EXPLAIN_STATEMENT.EXPLAIN_TEXT contém formatação. O comportamento padrão é detectar automaticamente se a instrução requer formatação e usar a formatação original quando existir.
  • C: Use um modo mais compacto ao formatar instruções e predicados.. O padrão é um modo expandido. Se Y não for especificado, então C entrará em vigor apenas se a detecção automática determinar que a instrução requer formatação
sinalizadores_gráfico
Um argumento de entrada do tipo VARCHAR (128) que contém vários sinalizadores de gráfico que podem ser combinados juntos como uma cadeia. Se uma cadeia vazia ou NULL for especificada, então 'TIC' será a opção padrão..
  • Desligue as opções (o padrão é ativá-las). Se estiver vazio, será gerado um gráfico, seguido de informações formatadas para as tabelas. Caso contrário, qualquer combinação dos seguintes valores válidos pode ser especificada:
    • O: Gerar um gráfico apenas. Não formatar o conteúdo da tabela
    • T: Inclua o custo total de cada operador no gráfico.
    • F: Incluir o primeiro custo da tupla no gráfico.
    • I: Inclua o custo de E/S de cada operador no gráfico.
    • C: Inclua a cardinalidade de saída esperada (número de tuplas) de cada operador no gráfico
    • Nota: Qualquer combinação dessas opções é permitida, exceto F e T, que são mutuamente exclusivos.
extract_sql
Um argumento de saída do tipo VARCHAR(2048) que contém a instrução SQL, que pode ser usada para extrair os dados formatados em relação a EXPLAIN_STATEMENT.

Observações de uso

Os parâmetros explain_schema, explain_requester, explain_time, source_schema, source_name, source_version, section_number compõem a chave que é usada para procurar as informações da seção nas tabelas de explicação. Se NULL ou vazio, ou se forem usadas entradas curinga para esses parâmetros, o valor real usado será atualizado no parâmetro no retorno.

O procedimento executa as seguintes funções:
  • Use os parâmetros passados para formatar as informações do EXPLAIN que são recuperadas das tabelas de explicação.
  • Atualize os dados formatados na tabela EXPLAIN_STATEMENT sob a coluna EXPLAIN_FORMAT_TEXT
  • Retorna os valores reais que são usados para todos os parâmetros INOUT (explain_schema, explain_requester, explain_time, source_schema, source_name, source_version, section_number).
  • Se o procedimento for bem-sucedido, o parâmetro OUT EXTRACT_SQL será preenchido com uma instrução SQL de exemplo que pode ser usada para recuperar dados formatados da tabela EXPLAIN_STATEMENT. Caso contrário, será preenchido com uma mensagem de erro.

Exemplo

  1. Crie as tabelas explicativas e reúna os dados explicativos para a consulta ou consultas de interesse usando os métodos documentados em Db2 Explique a facilidade.
  2. Chame o procedimento armazenado EXPLAIN_FORMAT , conforme a seguir:
    Call explain_format('DB2INST1', 'DB2INST1', '2022-11-08-02.28.42.810882', 'SQLC2P31', 'NULLID', '', 0, '', '', 'T', ?)

    Use os parâmetros da tabela a seguir:

    Tipo Lista de parâmetros Exemplo de valor de amostra acima
    INOUT Explicar_Esquema DB2INST1
    INOUT Explique_Solicitante DB2INST1
    INOUT EXPLAIN_TIME 2022-11-08-02.28.42.810882
    INOUT SOURCE_NAME SQLC2P31
    INOUT SOURCE_SCHEMA NULLID
    INOUT Versão_Fonte  
    INOUT section_number 0
    DENTRO OBJECT_TYPE  
    DENTRO Módulo de Objeto  
    DENTRO Sinalizadores do gráfico T
    OUT Extrato_SQL ?
  3. Extraia os dados formatados da tabela EXPLAIN_STATEMENT executando a seguinte instrução SQL a partir do parâmetro extract_sql OUT:
    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;
    A consulta mostra o plano de explicação formatado semelhante ao seguinte exemplo:
    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
    EXPLICAR INSTÂNCIA:
    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