ALTER ACCESS MODULE

The ALTER ACCESS MODULE access module management statement modifies an access module in the dictionary. It is a CA IDMS extension of the SQL standard.

Authorization

To issue an ALTER ACCESS MODULE statement, you must hold the ALTER privilege on or own the access module named in the statement.

In addition to enforcing this authorization requirement, CA IDMS validates the access module owner's authority to execute each DML statement if the dictionary to which the SQL session is connected is controlled by CA IDMS internal security.

If the access module owner does not hold the authority to execute a DML statement in the access module, when the access module is altered, a warning is issued. If the owner still lacks a necessary authority when the access module is executed, an error is returned.

Syntax

►►─── ALTER ACCESS MODULE ─┬────────────────┬─ access-module-name ────────────►
                           └─ schema-name. ─┘

 ►─┬─────────────────────────────┬────────────────────────────────────────────►
   └─ VERSION am-version-number ─┘

 ►─┬───────────────────────────────┬──────────────────────────────────────────►
   │       ┌───────── , ─────────┐ │
   └─ ADD ─▼─ rcm-specification ─┴─┘

 ►─┬─────────────────────────┬────────────────────────────────────────────────►
   │        ┌───── , ─────┐  │
   └─ DROP ─▼─ rcm-name ──┴──┘
 ►─┬────────────────────────────────────────────────────────────────────────┬─►
   │             ┌───────── , ─────────┐                                    │
   └─ REPLACE ─┬─▼─ rcm-specification ─┴──────────────────────────────────┬─┘
               ├─ CHANGED ────────────────────────────────────────────────┤
               └─ ALL ┬─────────────────────────────────────────────────┬─┘
                      │      ┌───────────────── , ───────────────────┐  │
                      └ MAP ─▼─┬ schema-name-1 ┬ TO - schema-name-2 ─┴──┘
                               └ NULL ─────────┘

 ►─┬───────────────────────────┬──────────────────────────────────────────────►
   └─ AUTO RECREATE ─┬─ ON ──┬─┘
                     └─ OFF ─┘
 ►─┬───────────────────────────────┬──────────────────────────────────────────►
   └─ VALIDATE ─┬─ BY STATEMENT ─┬─┘
                ├─ BY MODULE ────┤
                └─ ALL ──────────┘

 ►─┬──────────────┬───────────────────────────────────────────────────────────►
   ├─ READ ONLY ──┤
   └─ READ WRITE ─┘

 ►─┬──────────────────────────────────────────┬───────────────────────────────►
   └─ DEFAULT ISOLATION ─┬─ CURSOR STABILITY ─┤
                         └─ TRANSIENT READ ───┘

 ►─┬─────────────────────────────────────────────────────────┬────────────────►◄
   │           ┌────────────────── , ────────────────────┐   │
   └─ READY ─┬─▼─ segment-name.area-name ready-options ──┴─┬─┘
             └─ ALL ready-options ─────────────────────────┘

Expansion of rcm-specification

►►─┬────────────────────┬─ rcm-name ─┬──────────────────────────────┬─────────►◄
   └─ dictionary-name. ─┘            └─ VERSION rcm-version-number ─┘

Expansion of ready-options

►►─┬───────────────────────┬──────────────────────────────────────────────────►
   ├─ SHARED RETRIEVAL ────┤
   ├─ SHARED UPDATE ───────┤
   ├─ PROTECTED RETRIEVAL ─┤
   ├─ PROTECTED UPDATE ────┤
   └─ EXCLUSIVE ───────────┘

 ►─┬───────────────┬──────────────────────────────────────────────────────────►◄
   ├─ INCREMENTAL ─┤
   └─ PRECLAIM ────┘

Parameters

access-module-name

Specifies the name of the access module being modified. Access-module-name must identify an access module defined and stored in the dictionary.

schema-name

Specifies the schema associated with the access module. Schema-name must identify the schema associated with the version of the access module being modified.

If you do not specify schema-name, the value used by CA IDMS is the current schema for your SQL session.

am-version-number

Specifies the version of the access module to be modified.

If you do not specify am-version-number, the version number is set to that found as a result of loading the access module from the dictionary. This depends on the test version number and the loadlist in effect for your user session.

ADD rcm-specification

Specifies one or more RCMs to be added to the access module.

Expanded syntax for rcm-specification appears at the end of the statement syntax. Descriptions for these parameters are located at the end of this section.

DROP rcm-name

Specifies one or more RCMs to be deleted from the access module.

REPLACE rcm-specification

Directs CA IDMS to replace one or more RCMs in the access module with the most recent copies from the dictionary.

Expanded syntax for rcm-specification appears at the end of the statement syntax. Descriptions for these parameters are located at the end of this section.

CHANGED

Directs CA IDMS to replace all RCMs whose definition timestamp in the access module does not match the definition timestamp in the RCM load module.

ALL

Directs CA IDMS to recompile all RCMs in the access module.

MAP

Specifies one or more mappings for schema names that qualify table and view identifiers in data manipulation statements. MAP can be specified only with REPLACE ALL.

If you specify MAP, you must supply all schema mappings because existing rules are deleted from the access module.

If you do not specify MAP, schema-name mappings in the existing access module remain in effect.

schema-name-1

Directs CA IDMS to replace occurrences of the specified schema name with the schema name specified in the TO parameter.

NULL

Directs CA IDMS to use the schema name specified in the TO parameter as the qualifier for unqualified table and view identifiers.

TO schema-name-2

Directs CA IDMS to use the specified schema name as the replacement for schema-name-1 or as the qualifier for unqualified table and view identifiers.

AUTO RECREATE

Specifies whether CA IDMS is to re-create the access module after detecting any of the following at runtime:

An attempt to execute an uncompiled statement
A change to the definition of a table referenced in the access module
The execution of a program that has been recompiled since its RCM was included in the access module

CA IDMS identifies the above conditions by comparing definition timestamps in the access module to corresponding timestamps in the database and the host program.

If AUTO RECREATE is not specified, the existing AUTO RECREATE specification for the access module remains in effect.

Note: For more information about the ON and OFF options of AUTO RECREATE, see CREATE ACCESS MODULE.

VALIDATE

Indicates when CA IDMS is to check the definition timestamps of tables in the access module to ensure that the definition has not changed since the access module was created or last altered.

If VALIDATE is not specified, the existing VALIDATE specification for the access module remains in effect.

Note: For more information about the BY STATEMENT, BY MODULE, and ALL options of VALIDATE, see CREATE ACCESS MODULE.

READ ONLY

Specifies transactions started by the access module that do not execute a SET TRANSACTION statement specifying READ WRITE can retrieve data but cannot update the database.

READ WRITE

Specifies transactions started by the access module that do not execute a SET TRANSACTION statement specifying READ ONLY can retrieve data and update the database.

Note: For more information about the READ ONLY and READ WRITE transaction states, see CREATE ACCESS MODULE.

DEFAULT ISOLATION

Specifies the isolation level of transactions started by the access module that do not execute a SET TRANSACTION statement specifying an isolation level.

At runtime, the isolation level of a transaction determines the length of time retrieval locks are held for the purpose of insulating the transaction from the effects of other concurrent transactions. (Update locks are always held until a transaction is committed or rolled back.)

Note: For more information about the CURSOR STABILITY, and TRANSIENT READ DEFAULT ISOLATION options, see CREATE ACCESS MODULE.

READY

Specifies a ready mode for one or more areas accessed through the access module, and specifies when the ready occurs.

The ready mode associated with an area determines:

Under the central version, the ready mode in which transactions access the area. (The ready mode determines the types of area and row locks CA IDMS places for a transaction.)
In local mode, the type of physical lock CA IDMS places on the area.

If READY is not specified, the default ready options for areas used by the access module are:

The existing specifications for areas included in the existing access module
SHARED UPDATE and INCREMENTAL for areas added as a result of new or replaced RCMs

Parameters for Expansion of rcm-specification

dictionary-name

Identifies the dictionary in which the named RCM is located.

If you do not specify dictionary-name, it is set to the name of the dictionary to which your SQL session is connected.

rcm-name

Identifies the RCM.

Rcm-name must identify an RCM stored in the dictionary and must be unique within the list of RCM names.

rcm-version-number

Identifies the version of the RCM.

If you do not specify rcm-version-number:

CA IDMS looks for an RCM with a version number that matches am-version-number
If no such RCM is found, CA IDMS looks for version 1
If CA IDMS does not find a match, it issues a warning

Parameters for Expansion of ready-options

Note: For more information about ready-options, see CREATE ACCESS MODULE.

Usage

Defaulting the Access Module Version Number

If the version of an access module is not specified, it defaults to the version located as a result of a load operation. This is the same version that would be loaded as a result of executing a program associated with the access module.

For example, assume you have set a test version of 10 and you are using the default loadlist that CA IDMS supplied. CA IDMS loads version 10 of the access module if it exists; otherwise, it loads version 1.

Replacing All or Changed RCMs

When replacing all RCMs in an access module or replacing all RCMs which have been changed since being included in the access module, CA IDMS locates the replacement RCM using the same rules as when the RCM was added to (or explicitly replaced in) the access module. Specifically:

The dictionary name is the name of the dictionary from which the RCM was previously loaded
The version is that specified when the RCM was included in the access module, or, if not specified, CA IDMS first looks for an RCM whose version is the same as that of the access module being altered (and if that version is not found, then version 1 of the RCM).

Dropping RCMs

When dropping RCMs from an access module, the newly generated access module has a less than optimal structure unless the ALTER statement contains the REPLACE ALL clause. All RCMs need processing to determine the minimum set of control blocks in the access module.

Avoiding Deadlocks

If you use the access module of an ALTER ACCESS MODULE statement in other SQL statements in the same session, the ALTER should immediately be followed implicitly or explicitly by a COMMIT. This allows the new copy of the access module to load. Without the COMMIT, a deadlock may occur, even if the two SQL statements refer to different access modules.

Transaction State and Isolation Level

If you specify neither transaction state nor DEFAULT ISOLATION on an ALTER ACCESS MODULE statement, the existing values remain in effect. If either is specified, it also establishes a value for the other, as follows:

If READ WRITE or READ ONLY are specified, CURSOR STABILITY is assumed
If TRANSIENT READ is specified, READ ONLY is assumed
If CURSOR STABILITY is specified, READ WRITE is assumed

Examples

Replacing Changed RCMs

The following ALTER ACCESS MODULE statement replaces any changed RCMs in access module EMPAM001 with the most recent copies from the dictionary:

alter access module hrprod.empam001
   replace changed;

Adding New RCMs

The following ALTER ACCESS MODULE statement adds two new RCMs to the SALES001 access module. The statement also changes the lock options for two areas.

alter access module prod.sales001
   add sales.bdgt_001,
   add sales.comm_003
   ready
      salesseg.sales_area shared update incremental
      demoseg.emp_area shared retrieval preclaim;

More Information

For more information about access modules, see CREATE ACCESS MODULE and DROP ACCESS MODULE or see the CA IDMS Database Administration Guide.
For more information about schema-name mappings, see Identifying Entities in Schemas.
For more information about isolation levels, see CREATE ACCESS MODULE.
For more information about ready modes, see the CA IDMS Database Administration Guide.