Migrate SQL Access Modules

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

Applications are often migrated from one environment to another: test to production; test to QA; production to test; one CV to another; etc. In this process, all application components must be migrated including access modules. An access module is the executable form of the SQL statements that a program issues. When you create an access module, you also invoke the optimizer. The optimizer automatically determines the most efficient access to the data requested by the SQL statements. Advantage CA-IDMS stores the access strategy in the access module. At some sites the migration from one environment to another is done manually; other sites use an automated tool such as Endevor/DB. How can the migration of access modules be accomplished?

Resolution

Access modules contain time stamps, one of which is the timestamp from the database accessed in the program code and others for the Relational Command Modules (RCMs) contained in the catalog. They also contain optimized code based on the database structure and statistics from the environment in which they were created. So the only time that an access module can be legitimately migrated from one environment to another and be expected to run is if the two environments have identical databases, including the timestamps.

In general, to move an access module successfully from one environment to another, the best method is to migrate the program or dialog load module, along with all RCM load modules contained in the access module. Once the RCMs have been successfully migrated, then a "Create Access Module" statement should be issued on the target system. This will re-create the access module with the correct timestamps and optimized code for that environment.

RCMs contain a logical encapsulation of the SQL statements within a dialog or program and can be migrated from one environment to another without error. There is a one-to-one mapping between RCMs and the programs and dialogs within an application; every dialog or program that contains SQL will have an associated RCM. An Access Module may contain one to many RCMs; it should contain each RCM within the application thread.

Most migration tools, such as Endevor DB, do not currently manage any catalog components because of these timestamp considerations.