How to interpret the results of a CA IDMS EXPLAIN statement?

Document ID : KB000025721
Last Modified Date : 14/02/2018
Show Technical Document Details

Description

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.

Solution

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 you run an EXPLAIN statement against an SQL command or Access Module, the result is placed in an SQL table called ACCESS_PLAN or another name that you choose.

  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 in the CA IDMS SQL Reference manual. However, it can be cumbersome to learn to interpret the table results. To make this process easier, you can create a table and join it to your 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 in appendix P of the SQL reference. 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. 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> ; 

 

    1. 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 you want rows of this table 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 you want the table rows to be stored; it must be an area defined FOR SQL. If you do not have an SQL application data area defined, you can use the SQL employee demo database area as delivered with the CA IDMS product, SQLDEMO.EMPLAREA, if it is still defined in your DMCL.

    2. 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');

 

    1. 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');

 

    1. 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');

 

    1. 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');

 

  1. 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.

  2. Create a view to join the ACCESS_PLAN table with the ACCESS_CODE table you defined in step 2. A sample view definition follows; you can customize this as you see fit, based on your site's needs. In this example, you wil see that 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, you can see the access path for a statement by issuing an EXPLAIN against the statement, then issuing a query against this view. This is an example of these steps, with the results:

    1. Delete the results of any pre-existing EXPLAIN:
      DELETE FROM ACCESS_PLAN ; *+ Status = 0 SQLSTATE = 00000 *+ 3 rows processed

 

    1. EXPLAIN a statement into the default ACCESS_PLAN table:
      EXPLAIN STATEMENT 'SELECT * FROM DEMOEMPL.EMPLOYEE ' ; *+ Status = 0 SQLSTATE = 00000

 

  1. 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 you can see that 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.