CREATE ACCESS MODULE

The CREATE ACCESS MODULE statement creates an access module from one or more RCMs. CA IDMS stores the access module definition and the access module itself in the dictionary. It is also a CA IDMS extension of the SQL standard.
idmscu19
The CREATE ACCESS MODULE statement creates an access module from one or more RCMs. CA IDMS stores the access module definition and the access module itself in the dictionary. It is also a CA IDMS extension of the SQL standard.
This article describes the following information:
2
2
Authorization
To issue a CREATE ACCESS MODULE statement, you must own the schema with which the access module is being associated or hold the CREATE privilege on the named access module.
In addition to enforcing this authorization requirement, CA IDMS also validates the access module owner's authority to execute every DML statement in the RCMs included in the access module 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 created, a warning is issued. If the owner still lacks a necessary authority when the access module is executed, an error is returned.
Syntax
  ►►─── CREATE ACCESS MODULE ─┬────────────────┬─ 
access-module-name
 ───────────►                             └─ 
schema-name
. ─┘  ►─┬──────────────────────────────┬───────────────────────────────────────────►    └─ VERSION 
am-version-number
 ──┘            ┌─────────────────────────── , ─────────────────────────────────┐  ►── FROM ─▼─┬──────────────────┬ 
rcm-name
 ─┬─────────────────────────────┬┴──►              └ 
dictionary-name
. ┘           └ VERSION 
rcm-version-number
 ─┘    ►─┬────────────────────────────────────────────────────────┬─────────────────►    │       ┌─────────────────────── , ────────────────────┐ │    └─ 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 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 created.
    Access-module-name
    must be a one- through eight-character name that follows the conventions for SQL identifiers.
    The combination of
    access-module-name
    and
    am-version-number
    must be unique within the dictionary. Multiple access modules with the same
    access-module-name
    can be associated with a given schema provided they have different version numbers.
  • schema-name
    Specifies the schema to be associated with the access module.
    Schema-name
    must identify a schema defined in the dictionary.
    The owner of the schema with which the access module is associated implicitly becomes owner of the access module.
    If you do not specify
    schema-name
    , CA IDMS uses the current schema in effect for your SQL session.
  • am-version-number
    Specifies the version number of the access module to be created.
    If the specified version of the access module already exists, an error is returned.
    If you do not specify VERSION,
    am-version-number
    is set to 1.
  • FROM
    rcm-name
    Specifies one or more RCMs from which CA IDMS is to create the access module.
    Rcm-name
    must identify an RCM stored in the dictionary and must be unique within the list of RCM names.
    • dictionary-name
      Identifies the dictionary in which the named RCM resides.
      If you do not specify
      dictionary-name
      , it is set to the name of the dictionary to which the SQL session is connected.
    • rcm-version-number
      Specifies the version of the RCM to be included in the access module.
      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
  • MAP
    Specifies one or more mappings for schema names that qualify table and view identifiers in data manipulation statements.
    If you do not specify MAP, table and view identifiers are not replaced. If a table or view has no qualifier, CA IDMS uses the schema name of the access module as the qualifier.
  • 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:
    • 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 you do not specify AUTO RECREATE, the default is ON.
  • ON
    Directs CA IDMS to re-create the access module at runtime when timestamps do not match. CA IDMS continues the current transaction with the re-created access module but does not replace the access module in the dictionary until the transaction terminates with a COMMIT statement.
  • OFF
    Directs CA IDMS not to re-create the access module at runtime. If CA IDMS detects a mismatch in timestamps, it returns an error and terminates the current transaction.
  • 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 you do not specify VALIDATE, the default is VALIDATE ALL.
  • BY STATEMENT
    Directs CA IDMS to check the definition timestamp for a table immediately before executing the first statement in the access module that references the table.
  • BY MODULE
    Directs CA IDMS to check the definition timestamp for each table referenced by a statement in an RCM immediately before executing the first statement in the RCM.
  • ALL
    Directs CA IDMS to check the definition timestamp for each table in the access module immediately before executing the first statement in the access module.
  • READ ONLY
    Specifies transactions started by the access module that do not execute a SET TRANSACTION statement 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 can retrieve data and update the database.
  • DEFAULT ISOLATION
    Specifies the isolation level of transactions started by the access module that do not execute a SET TRANSACTION statement.
    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 the transaction is committed or rolled back.)
    If you do not specify DEFAULT ISOLATION, the default is CURSOR STABILITY.
  • CURSOR STABILITY
    Specifies the default isolation level for a transaction is cursor stability.
    An isolation level of cursor stability guarantees
    read integrity
    . Read integrity ensures that:
    • All data read by the transaction is in a committed state
    • The current row of an updateable cursor is protected from update by other transactions while it remains current
  • TRANSIENT READ
    Specifies the default isolation level for a transaction is transient read.
    An isolation level of transient read provides no guarantees of read integrity. A transaction executing under transient read cannot perform updates to the database. CA IDMS does not maintain any locks for a transaction with an isolation level of transient read.
    The combination of TRANSIENT READ and a transaction state of READ WRITE is invalid. Thus, if you specify TRANSIENT READ, CA IDMS assumes a transaction state of READ ONLY.
  • 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 you do not specify READY, the ready options for all areas used by the access module are determined at runtime by:
    • The transaction state (READ WRITE or READ ONLY)
    • The isolation level
    • The availability of the area under the central version
    For more information, see "Usage," following these parameter descriptions.
  • segment-name
    Identifies the segment associated with the area to which the following ready options apply.
    If the access module is used to access a non-SQL-defined database,
    segment-name
    is optional. In this case, if you do not specify
    segment-name
    , CA IDMS accesses the first segment for which it finds a match on
    area-name
    .
  • area-name
    Specifies the name of the area to which the following ready options apply.
    Area-name
    must identify an area used by the access module.
  • ALL
    Specifies the following ready options apply to all areas in the access module.
Parameters for Expansion of ready-options
The ready-options are used for a specified area or for all areas in the access module. Expanded syntax for
ready-options
is shown immediately following the CREATE ACCESS MODULE syntax.
  • SHARED RETRIEVAL
    Specifies a transaction can retrieve, but not update, data in the area. Other concurrent transactions can retrieve and update data in the area.
  • SHARED UPDATE
    Specifies that:
    • Under the central version, transactions access the indicated areas in shared update mode.
      With access to an area in shared update mode, a transaction can retrieve and update data in the area. Other concurrent central version transactions can also both retrieve and update data in the area.
    • In local mode, CA IDMS first places a physical lock on the indicated areas.
      With a physical lock on an area, a local mode transaction can retrieve and update data in the area. Concurrent transactions executing in other address spaces can retrieve but not update data in the area.
  • PROTECTED RETRIEVAL
    Specifies that:
    • Under the central version, transactions access the indicated areas in protected retrieval mode.
      With access to an area in protected retrieval mode, a transaction can retrieve, but not update, data in the area. Other concurrent central version transactions can also retrieve, but not update, data in the area.
    • In local mode, a ready mode of PROTECTED RETRIEVAL is equivalent to a ready mode of SHARED RETRIEVAL.
  • PROTECTED UPDATE
    Specifies that:
    • Under the central version, transactions access the indicated areas in protected update mode.
      With access to an area in protected update mode, a transaction can retrieve and update data in the area. Other concurrent central version transactions can retrieve, but not update, data in the area.
    • In local mode, a ready mode of PROTECTED UPDATE is equivalent to a ready mode of SHARED UPDATE.
  • EXCLUSIVE
    Specifies that:
    • Under the central version, transactions access the indicated areas in exclusive mode.
      With access to an area in exclusive mode, a transaction can retrieve and update data in the area. All other concurrent central version transactions can neither retrieve nor update data in the area except transactions with an isolation level of transient read, which can retrieve data in the area.
    • In local mode, a ready mode of EXCLUSIVE is equivalent to a ready mode of SHARED UPDATE.
  • INCREMENTAL
    Directs CA IDMS to defer the ready of each indicated area until execution of the first statement in the access module that requires access to the area.
  • PRECLAIM
    Directs CA IDMS to ready each indicated area when executing the first statement in the access module that requires database or dictionary access.
Usage
Automatic Access Module Recreation
An automatic recreation of the access module occurs when CA IDMS detects a change in the definition of a table referenced in the access module.
The scope of what is recreated is limited by how you specify the VALIDATE option, as described in the following table:
If validation is by
CA IDMS recompiles
STATEMENT
Only the statement just checked; other statements which reference the same table or another table with a changed definition are recompiled as they are encountered
MODULE
All statements in the current RCM that reference tables with changed definitions when the first such statement is encountered
ALL
All statements in the access module that reference tables with changed definitions when the first such statement is encountered
Repeatability of Retrieval Operations
An isolation level of cursor stability assures that data currently being accessed by a transaction is protected from update by other transactions. Cursor stability does not protect data that was accessed previously by the transaction.
Therefore, a cursor might return six rows the first time it is opened and five rows the second time, even though both operations are performed within the same transaction and that transaction has not made intervening updates. The discrepancy would be caused by updates by other transactions executing concurrently.
To completely isolate a transaction from the effects of other transactions, specify a protected ready mode for the areas that the transaction accesses. A ready mode of protected retrieval for retrieval applications and protected update for update applications ensures the repeatability of retrieval operations.
Runtime Ready Modes
The ready mode in which an area is accessed at runtime depends on the requested ready mode, the transaction state, the isolation level, and the area's availability:
  • If the transaction state and isolation level are READ ONLY and TRANSIENT READ, all areas are accessed using transient retrieval mode, in which no row locks are placed.
  • If the transaction state and isolation level are READ ONLY and CURSOR STABILITY, all areas are accessed using retrieval modes only.
    If update modes were specified on the CREATE or ALTER ACCESS MODULE statement, they are changed to shared retrieval, and if no ready option was specified, the default is shared retrieval.
  • If the transaction state and isolation level are READ WRITE and CURSOR STABILITY, all areas are accessed using the mode specified on the CREATE ACCESS MODULE.
    If no mode was specified, the default is:
    • Shared update under the central version if the area status is update
    • Shared update in local and no other copy of IDMS has update control of the area
    • Shared retrieval under the central version if the area status is retrieval
    • Shared update in local if another copy of IDMS has update control of the area
Under central version, if an area is being readied in a retrieval mode and the status of the area is transient retrieval, the ready mode is changed to transient retrieval.
Ready Modes and Area Status Under the Central Version
The ready mode in which a central version transaction obtains access to an area must be compatible with the status of the area within the DC/UCF system. If the area's status is:
  • Update, transactions executing under the system can obtain access to the area in any ready mode
  • Retrieval or transient retrieval, transactions executing under the system can obtain access to the area in a retrieval ready mode only
  • Offline to the system, transactions executing under the system cannot obtain access to the area
Shared, Protected, and Exclusive Ready Modes
In the shared ready modes (shared retrieval and shared update), CA IDMS provides protection from the effects of other transactions at the row level. In the protected ready modes (protected retrieval and protected update), CA IDMS provides protection at the area level. The shared ready modes, therefore, allow for greater transaction concurrency than the protected ready modes. The protected ready modes, on the other hand, create less overhead than the shared ready modes and reduce the chances for deadlocking.
In exclusive ready mode, as in the protected ready modes, CA IDMS provides protection at the area level. However, exclusive ready mode prohibits other transactions from retrieving data from the area.
Ready Modes and Later Modifications
The ready clause only affects areas accessed by statements compiled at the time the CREATE ACCESS MODULE statement is issued. If new areas are added at a later time because the access module is altered or because dynamic SQL accesses additional areas at runtime, those areas are accessed using a ready mode determined by the above rules as if no READY option had been specified (unless the READY option is repeated on the ALTER ACCESS MODULE statement).
Example
Creating an Access Module
The following CREATE ACCESS MODULE statement creates an access module from seven RCMs. The schema name EMP_TEST is replaced with EMP_PROD when it qualifies a table or view name, and unqualified tables and views are assumed to be in the EMP_PROD schema.
By default, CA IDMS performs the following tasks:
  • Checks the definition timestamps for all tables in the access module before executing the first statement and automatically re-creates the access module if any timestamps do not match
  • Places a shared update lock on each area in the access module at the time of the first request for data in the area
The following example shows creating an access module.
create access module hrprod.empam001    from emp_dict.empdsp01,       emp_dict.empdsp02,       emp_dict.empdsp03,       emp_dict.empadd01,       emp_dict.empupd01,       emp_dict.empupd02,       emp_dict.empdel01    map emp_test to emp_prod,       null to emp_prod;
More Information