EXPLAIN

The EXPLAIN utility statement describes the strategy that is used to access data in the following statements:
idmscu19
The EXPLAIN utility statement describes the strategy that is used to access data in the following statements:
  • DECLARE CURSOR
  • DELETE
  • SELECT
  • UPDATE
  • INSERT that contains a query specification in its VALUES clause
The description is stored as rows in a table which you can retrieve using a SELECT statement.
The EXPLAIN statement is a CA IDMS extension of the SQL standard.
This article describes the following information:
2
2
Authorization
To issue an EXPLAIN statement that specifies:
  • An access module, you must own or have the DISPLAY permission on the access module being explained
  • A statement, you must have the permissions required to execute the statement to be explained
Also, if the table in which the access plan is to be stored is:
  • Already defined in the dictionary, you must own or have the INSERT permission on the table
  • Not already defined in the dictionary, you must:
    • Own the schema that is associated with the table or have the CREATE and INSERT permissions on the table
    • Have the USE permission on the area in which rows of the table is stored
Syntax
►►─── EXPLAIN ────────────────────────────────────────────────────────────────►  ►─┬─ 
access-module-specification
 ───────────────────────────────────────┬────►    └─ STATEMENT '
sql-statement
' ─┬─────────────────────────────────────┬─┘                                  └─ STATEMENT NUMBER 
statement-number
 ─┘  ►─┬──────────────────────────────────────────────────────┬───────────────────►    └─ INTO TABLE ─┬────────────────┬─── 
table-identifier
 ─┘                   └─ 
schema-name
. ─┘  ►─┬─────────────────────────────┬────────────────────────────────────────────►◄    └─ IN 
segment-name
.
area-name
 ─┘
Expansion of access-module-specification
►►─── ACCESS MODULE 
access-module-name
 ───────────────────────────────────────►  ►─┬─────────────────────────────┬────────────────────────────────────────────►    └─ VERSION 
am-version-number
 ─┘  ►─┬─────────────────────────┬────────────────────────────────────────────────►◄    │          ┌───── , ────┐ │    └─ MODULE ─▼─ 
rcm-name
 ─┴─┘
Parameters
  • access-module-specification
    Identifies an access module to be explained. Expanded syntax for
    access-module-specification
    s presented immediately following the EXPLAIN syntax.
  • STATEMENT '
    sql-statement
    '
    Directs CA IDMS to return the access strategy for the specified SQL statement.
    Sql-statement
    must be an explainable statement and must be enclosed in single quotation marks.
  • STATEMENT NUMBER
    statement-number
    Assigns a reference number to the access plan for the statement that is specified in the STATEMENT parameter. The reference number is stored in the SECTION column in each row of the access plan.
    Statement-number
    must be an integer in the range 0 through 32,767. If not specified, a value of 0 is returned.
  • INTO TABLE
    table-identifier
    Specifies the table in which CA IDMS is to store the access plan. If you do not include the INTO TABLE parameter in an EXPLAIN statement,
    table-identifier
    is 'ACCESS_PLAN'.
    If
    table-identifier
    does not exist, CA IDMS automatically defines it in the dictionary using the column definitions described in "Usage" following these parameter descriptions. If
    table-identifier
    identifies an existing table, the table must be defined with the appropriate columns for storing the access plan.
    Specifying "EXPLAIN" as the
    schema-name
    or
    table-identifier,
    where you store the access plan, produces an error message. The syntax parser attempts to perform a second EXPLAIN.
  • schema-name
    Identifies the schema that is associated with the named table.
    If you do not specify
    schema-name
    , it defaults to:
    • The current schema that is associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
    • The schema that is associated with the access module that is used at runtime, if the statement is embedded in an application program
  • IN
    segment-name
    .
    area-name
    Identifies the area to be used for storing rows of the table that is named in the INTO TABLE parameter.
    IN parameter information is used only when the INTO TABLE parameter identifies a table that does not exist.
    If you do not specify the IN parameter, CA IDMS:
    • Uses the default area, if any, for the schema that is associated with the table that is named in the INTO TABLE parameter
    • Returns an error, if the schema does not have a default area
Parameters for Expansion of access-module-specification
  • ACCESS MODULE
    access-module-name
    Directs CA IDMS to describe the access strategy for all the explainable statements in the whole access module or in one or more specified RCMs in the access module.
    Access-module-name
    must identify an access module that is stored in the DDLCATLOD area of the dictionary. Access modules in this area are represented in the SYSTEM.LOADHDR table. For more information about the SYSTEM.LOADHDR table, see SYSTEM.LOADHDR.
  • VERSION
    am-version-number
    Specifies the version of the access module being explained.
    If
    am-version-number
    is not specified, the version is 1.
  • MODULE
    rcm-name
    Specifies one or more RCMs to be explained. CA IDMS describes the access strategy for each explainable statement in each named RCM.
    The SECTION value for the first explainable statement in the RCM is 0. The SECTION value for each succeeding explainable statement in the RCM is incremented by 1.
    Rcm-name
    must identify an RCM included in the access module that is named in the ACCESS MODULE parameter. Multiple RCM names must be separated by commas.
    If you do not specify the MODULE parameter with ACCESS MODULE, CA IDMS explains all the RCMs in the named access module.
Usage
Explainable Statements
The explainable statements are DECLARE CURSOR, DELETE, INSERT, SELECT, and UPDATE.
Table ACCESS_PLAN
The columns of the ACCESS_PLAN table are:
Column
Data type
Description
DBNAME
CHAR(8)
Dictionary connection for the session in which EXPLAIN is issued
ESTAMP
TIMESTAMP
Date and time EXPLAIN was issued
SCHEMA
CHAR(18)
Access module schema or, if explaining a statement, current schema for the SQL session
MODULE
CHAR(8)
Access module name or, if explaining a statement, IDMSEXPL
VERSION
SMALLINT
Access module version or, if explaining a statement, 0
STAMP
TIMESTAMP
Date and time access module was created, or, if explaining a statement, the same value as ESTAMP
PROGRAM
CHAR(8)
Program (RCM) name or, if explaining a statement, IDMSEXPL
PVERSION
SMALLINT
Program (RCM) version or, if explaining a statement, 0 (if explaining an access module, a version number of 0 indicates that no RCM version was specified the RCM when included in the access module)
PDICT
CHAR(8)
Program (RCM) dictionary or, if explaining a statement, blanks
PSTAMP
CHAR(20)
Date and time the program (RCM) was created or, if explaining a statement, blanks
SECTION
SMALLINT
Section number that is assigned to the SQLCSID field during program precompilation, or
statement-number
specified in the EXPLAIN statement
COMMAND
SMALLINT
Internal command code indicating the type of statement being explained:
8 -- DECLARE CURSOR
9 -- DELETE (searched)
10 -- DELETE (positioned)
17 -- INSERT
25 -- SELECT
29 -- UPDATE (searched)
30 -- UPDATE (positioned)
QBLOCK
SMALLINT
Query block number. Each query that the statement contains is assigned a block. Blocks are numbered beginning with 1.
STEP
SMALLINT
Step number. This number denotes the sequence of the processing step within the query block.
STYPE
SMALLINT
Step type. Denotes the type of processing for the step:
0 -- Null
1 -- Table access
2 -- Nested loop join
3 -- Merge join
4 -- Sort
5 -- Merge group
6 -- OR list
7 -- Dbk (Sorted)
8 -- Dbk (Unsorted)
9 -- Full outer join
PBLOCK
SMALLINT
Parent block number. Parent block numbers indicate nesting of multiple query blocks in a section.
PSTEP
SMALLINT
Parent step number. Parent step numbers correlate rows of query blocks:
If a table scan row is owned by a sort or join row, PSTEP is the step number of the owning row.
PSTEP of the top row of each main query block is 0.
PSTEP of the top row of each subquery is the query block number of the main query block to which it is subordinate.
TSCHEMA
CHAR(18)
Schema-name qualifier of the accessed table or procedure.
TABLE
CHAR(18)
Name of the accessed table or procedure.
TSTAMP
TIMESTAMP
Date and time the accessed table or procedure was created or last altered, or the date and time the EXPLAIN was issued in case no table or procedure was accessed.
ACMODE
CHAR(1)
Mode of access to the database record underlying the table, when STYPE is 1:
'A' -- Area
'C' -- CALC
'I' -- Index
'M' -- Set member
'N' -- Insert
'O' -- Set owner
'P' -- Table procedure
'R' -- ROWID index
'S' -- Sequential
'T' -- (Temporary table)
ACNAME
CHAR(18)
Set or index name.
LFS
CHAR(1)
Leaf scan indicator, when ACMODE is I. Indicates whether data is retrieved by sequential access to index leaf pages.
'N' -- No
'Y' -- Yes
SORTC
CHAR(1)
Composite sort type. A nonblank value in this field indicates that an actual sort is required (data cannot be accessed in sort order).
'D' -- Distinct
'F' -- Merge full outer join
'G' -- Group
'M' -- Merge join
'O' -- Order by
SORTN
CHAR(1)
Inner sort type. An actual sort that is performed for the inner loop of a merge join.
'M' -- Merge join
SUBQC
CHAR(1)
Subquery correlation.
'N' -- Not correlated
'Y' -- Correlated
Step types
Values in the STYPE column describe the type of processing:
Step type
Meaning
1 (Table access)
Access to a single table
2 (Nested loop join)
Join using linked constraint
3 (Merge join)
Join by scanning both tables and sorting the entire result
4 (Sort)
sort that is required by an ORDER BY parameter
5 (Merge group)
Sorting required by an aggregate function on distinct column values with the grouped results
6 (OR list)
Sorting required by one or more OR operators in a WHERE clause
Alternatives to the Default ACCESS_PLAN Table
You can use SQL procedures to tailor the way you retrieve and present access strategy information. You can also:
  • Create a table with the same column definitions (but, optionally, different column names) as in table ACCESS_PLAN and specify this table when you issue the EXPLAIN statement
  • Use the ALTER TABLE statement to add columns to table ACCESS_PLAN, or include more columns at the end of the column list when you create a table equivalent to ACCESS_PLAN
  • Create one or more views of table ACCESS_PLAN
Managing the Contents of an ACCESS_PLAN Table
Each time an EXPLAIN statement is executed; it inserts rows into an ACCESS_PLAN table. Periodically, contents of the table should be deleted using the DELETE statement.
Enhancing the Presentation of Access Strategy Information
Enhancing the Presentation of Access Strategy Information contains an SQL script with the definitions and data for a view that returns the access strategy information in an easy-to-read and understandable format.
Examples
Explaining RCMs in an Access Module
The following EXPLAIN statement returns the access strategy for each explainable statement in the EMPDSP01, EMPDSP02, and EMPDSP03 RCMs in the EMPAM001 access module. CA IDMS stores the access strategy in a table that is named EMPAM001_ACCESS.
explain access module empam001    module empdsp01, empdsp02, empdsp03    into table empam001_access;
Explaining a Specified Statement
The following EXPLAIN statement returns an access strategy for the specified SELECT statement. The access plan is identified by the reference number 4. By default, CA IDMS stores the access strategy in the ACCESS_PLAN table.
explain statement 'select e1.emp_id    from employee e1, position p1    where e1.emp_id = p1.emp_id       and p1.salary_amount >          (select p2.salary_amount             from employee e2, position p2             where e1.emp_id = e2.emp_id                and e2.manager_id = p2.emp_id)'    statement number 4;