How does CA IDMS choose the SQL plan or path that's included in an Access Module?

Products

• IDMS
• IDMS - Database

Issue/Introduction

CA IDMS requires an Access Module (AM) to be created for run-units that include SQL statements. This AM contains the actual physical access path / plan that will be used at run-time to satisfy the logical requests coded in the program(s) or dialog(s) in this run-unit. How is that actual plan or path determined?

Environment

Release : All supported releases

Component : CA SQLOPT

Resolution

Some information about how CA IDMS creates Access Modules (AMs) is proprietary. But I have provided below an outline of the steps that are followed, in case that is helpful. 

 

 1- When a user compiles a program or dialog that contains SQL DML, the compiler generates a Relational Command Module (RCM). The RCM contains logical pseudo-code of what the commands in the program or dialog indicate - tables to access, columns to retrieve, where clauses to satisfy, etc. 

 2- When a CREATE ACCESS MODULE statement is issued, the RCMs for the programs to be included in the AM are reviewed. 

 

3- The database structures and definitions (table, record, and area definitions) are then examined, and a determination is made of all of the possible ways that the data being requested can be accessed. 

 

4- Statistics stored in the catalog (or in the dictionary for network records) are examined,  and a cost plan is developed for each possible way to satisfy the DML statements in the programs and dialogs. This is why it is CRITICAL to issue an UPDATE STATISTICS statement whenever the cardinality of the data changes. Without accurate statistics, it's not possible to choose the most efficient path. 

 

5- After determining every possible access path, and developing a cost estimate for each one based on statistics, the physical access plan with the lowest cost is chosen. That path is what gets stored in the AM. 

Additional Information

Knowledge Article 10252 explains where the statistics are stored, for SQL & network databases. It can be found here: 

https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=10252&_ga=2.114309895.1812192348.1564328539-977507405.1564153815