How to force an area sweep in CA-IDMS SQL

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

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.

Environment

Release: IDADSO00100-18.5-ADS-for CA-IDMS
Component: