Statements › EXPLAIN

EXPLAIN

The EXPLAIN utility statement describes the strategy 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.

Authorization

To issue an EXPLAIN statement that specifies:

• An access module, you must own or have the DISPLAY privilege on the access module being explained
• A statement, you must have the privileges required to execute the statement to be explained

Additionally, 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 privilege on the table
• Not already defined in the dictionary, you must:
  • Own the schema associated with the table or have the CREATE and INSERT privileges on the table
  • Have the USE privilege 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 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.

Important! Do not specify "EXPLAIN" as the schema-name or table-identifier where you will store the access plan. This produces an error message. The syntax parser interprets this as an attempt to perform a second EXPLAIN.

schema-name

Identifies the schema associated with the named table.

If you do not specify schema-name, it defaults to:

• The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
• The schema associated with the access module 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 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 associated with the table 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 stored in the DDLCATLOD area of the dictionary. Access modules in this area are represented in the SYSTEM.LOADHDR table.

Note: 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 will describe 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 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:

Step types

Values in the STYPE column describe the type of processing:

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 additional 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 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;