Description:
This document describes how to force a CA-IDMS SQL query to use an area sweep.
Solution:
In the SQL Option, CA 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 CA-IDMS will perform a keyed retrieval against the EM_NAME_NDX to read the necessary rows.
However, if you use the OPTIMIZE clause with a very large number (2147483647 is the highest allowable number), you get a different result:-
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 CA-IDMS will perform an area sweep.