ACMODE=I in EXPLAIN can mean sorted set

Document ID : KB000125216
Last Modified Date : 24/01/2019
Show Technical Document Details
Introduction:
In the output table of an EXPLAIN statement, a value of "I" in column ACMODE can indicate a sorted set, and not necessarily an index.
Background:
The EXPLAIN statement can be used on an SQL query to generate an output table which will describe the access path that the statement will use.
One of the columns in the EXPLAIN table is ACMODE.
A value of "I" in ACMODE is documented as meaning an index is being used in that step.
Environment:
CA IDMS, all supported releases.
Instructions:
A value of "I" in the ACMODE column of an EXPLAIN table can also mean a sorted set.
A sorted set is similar to an index in that a symbolic key can be used to locate a member of the set or index.
In an index, an internal B-tree structure is used to directly locate the record according to the value of the symbolic key.
In a sorted set, the members of the set are sorted in a chain (linked list) according to the value.
Therefore, access via a sorted set will typically not be as efficient as it would be with an index.

For example, an EXPLAIN on the following SQL statement:
SELECT E.EMP_ID_0415, X.* FROM EMPNET.EMPLOYEE E
INNER JOIN EMPNET.EXPERTISE X ON "EMP-EXPERTISE"
WHERE EMP_ID_0415=23 AND SKILL_LEVEL_0425='02';
may return (in part) the following:
*+ PSTAMP                SECTION  COMMAND  QBLOCK    STEP   STYPE  PBLOCK    
*+ ------                -------  -------  ------    ----   -----  ------    
*+                             0       25       1       1       1       0    
*+                             0       25       1       2       1       0    
*+                             0       25       1       3       2       0    
*+                                                                           
*+  PSTEP  TSCHEMA             TABLE               TSTAMP                    
*+  -----  -------             -----               ------                    
*+      3  EMPNET              EMPLOYEE            0001-01-01-00.00.00.000000
*+      3  EMPNET              EXPERTISE           0001-01-01-00.00.00.000000
*+      0                                          2019-01-24-07.04.09.242171
*+                                                                           
*+ ACMODE  ACNAME              LFS  SORTC  SORTN  SUBQC                      
*+ ------  ------              ---  -----  -----  -----                      
*+ C                                                                         
*+ I       EMP-EXPERTISE       N                                             
*+
Note that the row with ACMODE="I" also specifies ACNAME="EMP-EXPERTISE".
EMP-EXPERTISE is not an index, it's a sorted set.