Managing IDMS SQL Access Modules.

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

When developing IDMS SQL applications, an ACCESS MODULE must correspond to the application run-time structure. It must be considered when a migration strategy is developed for any SQL application.

 

Environment

IDMS - All Supported Releases

Resolution

Several things must be considered in managing access modules. Important questions include:

1. What exactly is an access module?
   
   
2. How is an access module created?
   
   
3. Should every program have its own access module?
   
   
4. How should access modules be migrated?

In addition, access modules should be evaluated for performance. Each of these topics will be addressed in turn.

What exactly is an access module?

An access module is an executable load module. It is created by the Command Facility (OCF or BCF) when the statement 'Create Access Module' is processed. The input to this process is one or more RCMs (Relational Command Modules). RCMs are generated by all IDMS pre-compilers (IDMSDMLC, IDMSDMLP, ADSC, etc) when they encounter embedded SQL statements in a program or dialog; RCMs reflect the logical requests specified in the SQL DML. An access module contains a customized "subschema" for all the tables referenced in the SQL DML statements, security information, and for each SQL DML statement, the physical access strategy that the optimizer has deemed the most efficient way to satisfy the logical request.

How is an access module created?

A Create access Module statement must be explicitly coded, then processed by OCF or BCF to create an access module after the programs containing SQL have been precompiled.

Should every program have its own access module?

Not necessarily. Every program and dialog will have a unique RCM. There must be one access module for the run-time transaction, so it is possible that the access module will contain the optimized code for SQL statements in multiple programs or dialogs. An access module must contain the RCMs for every program that will be executed in an application thread, i.e. a recoverable unit of work. If no program in the application links to another or uses shared cursors, then a 1-to-1 ratio of programs to access modules is appropriate.

How should access modules be migrated?

RCMs can be migrated from one environment to another along with the program load module. Access modules contain timestamps from the catalog for every table referenced in the SQL DML, and from the physical database areas for all the tables to be accessed. Timestamp validation is performed at runtime to match these timestamps in the AM to the catalog and database area(s) being accessed. Because these timestamps will differ between environments, the execution of the SQL statements will fail if an AM created in one environment is executed in a different environment. The recommended migration strategy for AMs is to punch the relevant RCMs from the source DBname to the target, and then to re-create the AM on the target DBname using the same CREATE ACCESS MODULE syntax that was used on the source DBname. This syntax can be discovered by using a DISPLAY ACCESS MODULE statement in OCF or BCF.

How can I tell if an access module is performing well?

Access modules contain the physical access strategies to satisfy the logical SQL DML statements. The specific physical paths are determined by the optimizer, which evaluates all possible ways to satisfy the requests and chooses the most efficient one. This determination of efficiency is based on statistics stored in the catalog and/or dictionary areas. To ensure that accurate statistics are available to the optimization process, the UPDATE STATISTICS command should be issued whenever the cardinality of a table changes, or when new table definitions are added to the catalog and data has been stored in them.

The EXPLAIN statement can be used to evaluate the access paths stored in an Access module. Syntax for this is defined in the SQL Reference. ACMODE is one of the columns in the result table from an EXPLAIN; if an ACMODE= A, it means an Area sweep is being performed. This should be avoided whenever possible, especially in a production environment. Similarly, sets should be walked only when they are the most efficient mode of retrieval. If testing has resulted in acceptable performance, then after an access module is re-created in a production setting, it is recommended that an Explain be executed against it to ensure that it contains no area sweeps or other poorly-performing path. If statistics are different between the two environments, the access path chosen by the optimizer might differ.