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.
idmscu19
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.
This article describes the following information:
2
2
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.
    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.
    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.
    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.)
    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
    :
    1. CA IDMS looks for an RCM with a version number that matches
      am-version-number
    2. If no such RCM is found, CA IDMS looks for version 1
    3. If CA IDMS does not find a match, it issues a warning
Parameters for Expansion of ready-options
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