SQL Programming Techniques › Modularized Programming › Using the SET ACCESS MODULE Statement

Using the SET ACCESS MODULE Statement

Why You Use It

You use a SET ACCESS MODULE statement to specify in the program what access module should be executed for a database transaction. SET ACCESS MODULE overrides the default access module specification for the duration of the transaction.

Default Access Module Specification

The default access module specification is the one associated with the program that initiates the SQL session—that is, the first program to issue an SQL statement.

Note: For information about how an access module is associated with a program, see Preparing and Executing the Program.

The default access module is the access module that is executed unless the program issues a SET ACCESS MODULE statement. The SET ACCESS MODULE specification remains in effect until the database transaction ends. After the database transaction ends, the default access module is re-established.

When to Issue SET ACCESS MODULE

The SET ACCESS MODULE statement is valid only if the program issues it in the transaction before it issues an SQL statement requesting dictionary or database access.

Note: For more information and a list of statements that can precede SET ACCESS MODULE in a database transaction, see the CA IDMS SQL Reference Guide.

Using a Host Variable

You can specify the access module name in a host variable on the SET ACCESS MODULE. This allows the specification of an access module to be decided by conditions not known until runtime.

Note: When you define a host variable for the access module name, an eight-byte character field suffices because an access module name is limited to eight characters.

SET ACCESS MODULE Example

In this example, program EMPACT declares a global cursor and issues a SET ACCESS MODULE statement before starting a transaction with an OPEN statement:

 IDENTIFICATION DIVISION.
 PROGRAM-ID.  EMPACT.
  .
  .
  .

 DATA DIVISION.
 WORKING-STORAGE SECTION.

 EXEC SQL
   DECLARE EMP_CRSR GLOBAL CURSOR FOR
     SELECT EMP_ID
       FROM EMPLOYEE
       WHERE STATUS = 'A'
 END-EXEC.
  .
  .
  .

 PROCEDURE DIVISION.

 MOVE 'EMPAPPL3' TO AM-NAME.

 EXEC SQL
   SET ACCESS MODULE :AM-NAME
 END-EXEC.

 EXEC SQL
   OPEN EMP_CRSR
 END-EXEC.
  .
  .
  .