Interpreting the results of an IDMS EXPLAIN statement

Products

• IDMS
• IDMS - Database

Issue/Introduction

In order to know the physical access path that will be executed to satisfy a logical SQL request, the EXPLAIN statement must be executed. This document helps interpret the results of the EXPLAIN statement.

Environment

Release: All supported releases.
Component: SQL Option.

Resolution

In order to know the physical access path that will be executed to satisfy a logical SQL query, the EXPLAIN statement must be executed. This is an important step as it helps ensure that a resource-intensive query does not have a negative impact in a production environment. The EXPLAIN process is two-fold:

1. The EXPLAIN utility statement must be executed against a statement or an access module. When running an EXPLAIN statement against an SQL command or Access Module, the result is placed in an SQL table called ACCESS_PLAN or another name chosen by the user.
   
   
2. The EXPLAIN result table contents must be reviewed and evaluated to determine the effectiveness of the chosen access path. This is especially useful for finding SQL statements that will cause area sweeps or other costly access strategies. A costly access path might suggest adding new indices or other tuning options to the database.

The structure of the output table created by an EXPLAIN is documented at EXPLAIN. However, it can be cumbersome to learn to interpret the table results. To make this process easier, create a table and join it to the EXPLAIN results table in a view definition. These rows within this table add meaning to the EXPLAIN facility by creating easy-to-understand values for the abbreviated codes that EXPLAIN produces. Selecting from the view as opposed to the actual EXPLAIN results table will produce output that is easier to understand. The definition and values to store in this adjunct table are contained in an SQL script EXPLDDL within the IDMS source library, and are also documented at Enhancing the Presentation of Access Strategy Information. They are included here also, to provide a context for understanding how and why to implement them. The steps to create the table, store values in it, and create the view to join it to the EXPLAIN results table, follow.

1. 1. Set a default schema for this OCF or BCF session, to be used in all of the following statements:
      
      SET SESSION CURRENT SCHEMA <schema_name>;
      
      
   2. Define an access plan code table, and an index on this table, using the following syntax:
      
      CREATE TABLE ACCESS_CODE
    (COLUMN     SMALLINT NOT NULL,  -- column index
     CODE_NUM   SMALLINT,           -- numeric code
     CODE_CHAR  CHAR,               -- character code
     TEXT       CHAR(18) NOT NULL)  -- display text
  NO DEFAULT INDEX   IN <segment_name.area_name>   -- physical SQL data area where rows of this table are to be stored (see note below);
CREATE INDEX ACCESS_CODE_IX
  ON ACCESS_CODE
   (COLUMN,CODE_NUM,CODE_CHAR)
  CLUSTERED;
      
      NOTE: The CREATE TABLE syntax requires a SEGMENT and AREA name to be specified if the default schema does not specify a default data area. These should indicate the physical area where the table rows are to be stored; it must be an area defined FOR SQL. If there is no SQL application data area defined, use the SQL employee demo database area as delivered with the IDMS product, SQLDEMO.EMPLAREA, if it is still defined in the DMCL.
      
      
   3. Store rows in the table to interpret the COMMAND column codes in the EXPLAIN output:
      
      INSERT INTO ACCESS_CODE VALUES (1,8,NULL,'DECLARE CURSOR');
INSERT INTO ACCESS_CODE VALUES (1,9,NULL,'DELETE');
INSERT INTO ACCESS_CODE VALUES (1,17,NULL,'INSERT');
INSERT INTO ACCESS_CODE VALUES (1,25,NULL,'SELECT');
INSERT INTO ACCESS_CODE VALUES (1,29,NULL,'UPDATE');
      
      
   4. Store rows in the table to interpret the STYPE column codes in the EXPLAIN output:
      
      INSERT INTO ACCESS_CODE VALUES (2,0,NULL,' ');
INSERT INTO ACCESS_CODE VALUES (2,1,NULL,'TABLE ACCESS');
INSERT INTO ACCESS_CODE VALUES (2,2,NULL,'NL JOIN');
INSERT INTO ACCESS_CODE VALUES (2,3,NULL,'SM JOIN');
INSERT INTO ACCESS_CODE VALUES (2,4,NULL,'SORT');
INSERT INTO ACCESS_CODE VALUES (2,5,NULL,'MERGE GROUP');
INSERT INTO ACCESS_CODE VALUES (2,6,NULL,'OR List');
INSERT INTO ACCESS_CODE VALUES (2,7,NULL,'DBK (SORTED)');
INSERT INTO ACCESS_CODE VALUES (2,8,NULL,'DBK (UNSORTED)');
INSERT INTO ACCESS_CODE VALUES (2,9,NULL,'SM Full Out Join');
      
      
   5. Store rows in the table to interpret the ACMODE column codes in the EXPLAIN output:
      
      INSERT INTO ACCESS_CODE VALUES (3,NULL,' ',' ');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'A','AREA SWEEP');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'C','CALC');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'I','INDEX');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'M','SET MEMBER');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'N','INSERT');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'O','SET OWNER');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'P','PROCEDURE');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'R','ROWID INDX');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'S','INDEX SEQL');
INSERT INTO ACCESS_CODE VALUES (3,NULL,'T','TEMP TABLE SEQL');
      
      
   6. Store rows in the table to interpret the SORTC and SORTN column codes in the EXPLAIN output:
      
      INSERT INTO ACCESS_CODE VALUES (4,NULL,' ',' ');
INSERT INTO ACCESS_CODE VALUES (4,NULL,'D','DISTINCT');
INSERT INTO ACCESS_CODE VALUES (4,NULL,'F','MERGE FULL OJOIN');
INSERT INTO ACCESS_CODE VALUES (4,NULL,'G','GROUP');
INSERT INTO ACCESS_CODE VALUES (4,NULL,'M','MERGE JOIN');
INSERT INTO ACCESS_CODE VALUES (4,NULL,'O','ORDER BY');
      
      
   7. Force the definition of the ACCESS_PLAN table to contain the EXPLAIN results, so that it can be referenced in the view creation in the following step, by issuing this statement:
      
      EXPLAIN STATEMENT 'SELECT * FROM SYSTEM.TABLE' STATEMENT NUMBER 9999 IN <segment_name.area_name>;
      
      In this syntax, substitute appropriate values for segment and area names, as in step #2 above.
      
      
   8. Create a view to join the ACCESS_PLAN table with the ACCESS_CODE table defined in step 2. A sample view definition follows; customize this as necessary, based on the site's needs. In this example, the FROM list references the ACCESS_CODE table multiple times; eash instance is made unique by an alias name that follows the table reference. The ST instance is used to decodes the STYPE column of ACCESS_PLAN. The AM instance decodes the ACMODE column. The S1 and S2 instances decode the SORTC and SORTN columns, respectively. The COMMAND column isn't included in this view, but could be decoded by introducing another instance of the ACCESS_CODE table in the FROM clause along with the matching join factors. In this example, a descending sort was chosen to force the high level joins to the top of the output, so that the access path is presented in a top down tree format.
      
      CREATE VIEW PLANVIEW(SNO, QB, PB, ST, "STEP TYPE", PST, TSCHEMA,
                     TABLE, "ACCESS MODE", ACNAME, LFS, OSORT, ISORT, SQC) AS
  SELECT CAST(SECTION AS DEC(4)),
         CAST(QBLOCK AS DEC(3)),
         CAST(PBLOCK AS DEC(3)),
         CAST(STEP AS DEC(3)),
         ST.TEXT,
         CAST(PSTEP AS DEC(3)),
         SUBSTR(TSCHEMA,1,8),
         SUBSTR(TABLE,1,10),
         SUBSTR(AM.TEXT,1,10),
         ACNAME,
         LFS,
         SUBSTR(S1.TEXT,1,10),
         SUBSTR(S2.TEXT,1,10),
         SUBQC
    FROM ACCESS_PLAN,
         ACCESS_CODE ST, 
         ACCESS_CODE AM,
         ACCESS_CODE S1,
         ACCESS_CODE S2
   WHERE ST.COLUMN = 2
     AND ST.CODE_NUM = STYPE
     AND AM.COLUMN = 3
     AND AM.CODE_CHAR = ACMODE
     AND S1.COLUMN = 4
     AND S1.CODE_CHAR = SORTC
     AND S2.COLUMN = 4
     AND S2.CODE_CHAR = SORTN
   ORDER BY 1, 2, 4 DESC;

Once these steps are completed, the access path for a statement can be seen by issuing an EXPLAIN against the statement, then issuing a query against this view. This is an example of these steps, with the results:

Delete the results of any pre-existing EXPLAIN:

DELETE FROM ACCESS_PLAN;
*+ Status = 0 SQLSTATE = 00000
*+ 3 rows processed

EXPLAIN a statement into the default ACCESS_PLAN table:

EXPLAIN STATEMENT 'SELECT * FROM DEMOEMPL.EMPLOYEE ';
*+ Status = 0 SQLSTATE = 00000

Review the access plan by selecting from the view you've defined:

SELECT * FROM PLANVIEW ;
*+
*+    SNO   QB   PB    ST  STEP TYPE       PST  TSCHEMA   TABLE
*+    ---   --   --    --  ---------       ---  -------   -----
*+      0    1    0     1  TABLE ACCESS    0    DEMOEMPL  EMPLOYEE
*+
*+ ACCESS MODE  ACNAME              LFS  OSORT       ISORT       SQC
*+ -----------  ------              ---  -----       -----       ---
*+ AREA SWEEP
*+
*+ 1 row processed

In this simple example, the access path consists of one step. The type of step is table access, where the table in question is DEMOEMPL.EMPLOYEE, and the table rows are accessed via an area sweep.