CA IDMS SQL considerations for a physical data access path or plan?
Article ID: 135416
Updated On:
Products
Issue/Introduction
When a program or dialog contains SQL statements, as part of the pre-compile step we create a Relational Command Module (RCM). This contains pseudo-code that extracts all of the key components of each SQL command - operations, database tables, column lists, were clauses, etc. These RCM(s) are then processed and an Access Module (AM) is created to contain the physical path that will be used to satisfy the logical SQL commands. Clients may want a clearer understanding of how the logical requests in a program's SQL commands are translated into the physical access path that is stored in the Access Module (AM).
Environment
Release : 19.0
Component : CA IDMS SQL
Resolution
Some of how CA IDMS creates Access Modules is proprietary, so some details are not included here. But this document provides an outline of the steps we go through, in case that is helpful.
1- When you compile a program or dialog that contains SQL DML, we generate a Relational Command Module (RCM). The RCM contains logical pseudo-code of what the commands indicate - tables to access, columns to retrieve, where clauses to satisfy, etc.
2- When you issue a CREATE ACCESS MODULE statement, the SQL optimizer reviews the RCM(s) for the programs included in the CREATE ACCESS MODULE statement. There must be a one-to-one mapping of AMs and run-units at run-time, so if multiple programs link to or invoke other program(s), the RCMs for all of the programs in the run-unit must be contained in a single AM. This means some RCMs for subroutines may end up included in multiple AMs.
3- The Optimizer then reviews the database structures involved (table, record, and area definitions) and determines every possible way to access the data being requested.
4- The optimizer then reviews all of the available statistics related to these database structures; these are stored in the catalog (or in the dictionary for network records). It develops a cost plan for each possible way that we can satisfy the DML statements in the program(s) & dialog(s). This is why it is CRITICAL to issue an UPDATE STATISTICS statement whenever the cardinality of the data changes. Without accurate statistics, the optimizer cannot choose the most efficient path.
5- After determining every possible access path, and developing a cost estimate for each one based on statistics, we choose the physical access plan with the lowest cost. That path is what gets stored in the AM.
Additional Information
Feedback
