Force an area sweep in IDMS SQL

Products

• IDMS
• IDMS - Database

Issue/Introduction

This document describes how to force an SQL query in IDMS to use an area sweep.

Environment

Release: All supported releases.
Component: SQL Option.

Resolution

In the SQL Option, IDMS uses statistics maintained in the catalog (or dictionary) in order to optimize queries so that they use the most efficient database access path possible. In some circumstances, it might be desirable to force a query to use an area sweep regardless of any other considerations. For example, using the following sample query,

SELECT * FROM DEMOEMPL.EMPLOYEE WHERE EMP_LNAME='Thompson';

The following SQL will list the access path in the form of a table

DROP TABLE SQLWRK.AP;
*+ Status = 0        SQLSTATE = 00000
EXPLAIN STATEMENT '
SELECT * FROM DEMOEMPL.EMPLOYEE WHERE EMP_LNAME=''Thompson''
' INTO TABLE SQLWRK.AP;
*+ Status = 0        SQLSTATE = 00000
SELECT ACMODE, ACNAME FROM SQLWRK.AP;
*+
*+ ACMODE  ACNAME
*+ ------  ------
*+ I       EM_NAME_NDX
*+
*+ 1 row processed

The output of the explain table indicates that IDMS will perform a keyed retrieval against the EM_NAME_NDX to read the necessary rows. However, using the OPTIMIZE clause with a very large number (2147483647 is the highest allowable number), makes the SQL optimizer think that the resulting output will have a very large number of rows, and therefore decide that the most efficient access path will be an area sweep, despite the fact that the value of the symbolic key in an index is provided in the WHERE clause.

DROP TABLE SQLWRK.AP;
*+ Status = 0        SQLSTATE = 00000
EXPLAIN STATEMENT '
SELECT * FROM DEMOEMPL.EMPLOYEE WHERE EMP_LNAME=''Thompson''
OPTIMIZE FOR 2147483647 ROWS
' INTO TABLE SQLWRK.AP;
*+ Status = 0        SQLSTATE = 00000
SELECT ACMODE, ACNAME FROM SQLWRK.AP;
*+
*+ ACMODE  ACNAME
*+ ------  ------
*+ A
*+
*+ 1 row processed

The "A" in ACMODE indicates that IDMS will perform an area sweep.