7 Distributed Transaction Coordination

Read this chapter to learn about two-phase commit processing. It includes the following sections:

Two-Phase Commit Overview
Two-Phase Commit and Oracle Access Manager for AS/400
Two-Phase Commit Process
In-Doubt Transactions
Enabling Two-Phase Commit Processing
Oracle Required Privileges
AM400_COM Program

Two-Phase Commit Overview

Oracle Access Manager for AS/400 supports two-phase commit processing to ensure transaction consistency in a heterogeneous environment. Two-phase commit processing is an optional feature in Oracle Access Manager.

During installation, the Access Manager parameter for two-phase commit processing defaults to *NO. To enable transaction consistency in a heterogeneous environment, the two-phase commit parameter must be changed to *YES on the Change Access Manager Default Options panel or by using the CHGSQLDFLT command. For additional information, refer to Chapter 6, "Configuring Access Manager".

Note:

If the two-phase commit parameter is set to *NO, then data might not be synchronized between local and remote databases. Synchronization is needed only when changes in data (changes in files or tables) are being made in a transaction both on the AS/400 and on the remote Oracle server.

Terminology

AM400_RID program automatically resolves in-doubt transactions. This program must be active to enable two-phase commit processing. The AM400_RID program runs in a subsystem which has the same name as the install library.

AM400_COM program provides a mechanism for privileged users to dynamically communicate with the AM400_RID program.

Two-phase commit parameter enables two-phase commit processing in Oracle Access Manager for AS/400. The default setting is *NO. It must be changed to *YES to enable two-phase commit processing.

Commit point site is the processing node that initiates a commit or roll back. The AS/400 that is participating in a two-phase commit transaction is always designated as the commit point site. The commit point site is also known as the "coordinator" site.

Prepare phase is the phase during which the coordinator asks participants to prepare to commit or to roll back the changes that were made during a transaction.

Commit phase is the phase during which the coordinator asks all nodes to commit the transactions, if all participants have responded to the coordinator that they are prepared to do so. If any participants cannot prepare, then the coordinator asks all nodes to roll back the transaction.

Implied commit When a program (activation group) ends without error, default behavior for the AS/400 system triggers all pending updates to be committed. This commitment of all pending updates would include updates to Oracle data as part of the AS/400 transactions through the Oracle Access Manager for AS/400.

Two-Phase Commit and Oracle Access Manager for AS/400

Oracle Access Manager for AS/400 enables AS/400 applications to update data on both the AS/400 and remote Oracle servers during a single transaction. Any time that updates are allowed in a distributed environment, a new level of complexity is introduced. When multiple users are sharing and accessing data that exists at many sites, rather than at a single site, these distributed transactions require careful management.

With Oracle Access Manager for AS/400, you now have the option to ensure that all participants (nodes) in a distributed transaction react unanimously to the action to take when a transaction end. That is, they all commit, or they all roll back.

For a database to fully participate in a two-phase commit transaction, it must support both a prepare phase and a commit phase for communicating transactions. The prepare phase ensures that all participating nodes that are referenced in a distributed transaction are prepared to commit or to abort the transaction, regardless of intervening failures. The prepare phase is performed when a COMMIT command is issued at the end of a distributed transaction. The commit phase is performed when all transaction participants have responded to the coordinator that they are prepared to commit.

The Oracle server supports two-phase commit transactions. Therefore, any number of Oracle servers can participate in a distributed two-phase commit transaction.

Note:

If the two-phase commit parameter has been set to *YES, and if the AM400_RID program is active, then the prepare phase occurs automatically when an application COMMIT is executed. No other action is necessary.

Two-Phase Commit Process

There are two phases for two-phase commit processing:

prepare phase
commit phase

Phase 1: Prepare

When DB2/400 receives a commit request from an application or from STRSQL, it asks all participants in the distributed system to enter a prepared state (to promise to commit or roll back the transaction, even if there is a failure). The participating sites reply either that they are prepared to commit or that they are not prepared to commit. When Access Manager receives such a commit request from DB2/400, it broadcasts an informational message to the AM400_RID program. This communication is one way. The AM400_RID program sends no communication to the Access Manager.

Phase 2: Commit

If the AS/400 receives a prepared to commit message from all the participating sites, then the AS/400 broadcasts a COMMIT command to all sites. If even one site has replied that it is not prepared to commit, then the AS/400 aborts the transaction and broadcasts a ROLLBACK command. When an Access Manager site has committed, an information message is sent to the AM400_RID program to announce the end of the transaction.

Note:

Access Manager operates under AS/400 commitment control, including implied commits (defined in section "Implied commit"). Refer to the appropriate IBM AS/400 documentation for additional information.

In-Doubt Transactions

An in-doubt transaction occurs when a program or operation terminates abnormally before committing or rolling back the transaction. This creates an outstanding transaction, which must be resolved in order to free the involved resources and to ensure transaction consistency.

Resolving In-Doubt Transactions

In-doubt transactions can be resolved automatically by Oracle Access Manager for AS/400. Oracle Access Manager uses a separate task to resolve in-doubt transactions. This task runs in its own subsystem and must be active to enable two-phase commit processing. The task runs the AM400_RID program. On the WRKACTJOB display, the AM400_RIDI job will be seen to be active in the "lib" system (where "lib" is the name of the install library).

The AM400_RID program controls the automatic resolve-in-doubt processing for Oracle Access Manager for AS/400. This program runs in its own subsystem parallel to user programs. It is not called by user programs. The ability to perform resolve-in-doubt processing is required if two-phase commit was requested on the CHGSQLDFLT command through the "Access Manager Two-Phase Commit" line by specifying *YES to that option. The AM400_RID program performs resolve-in-doubt processing only for those Oracle Servers to which it has a valid connection. The only Oracle Servers to which the AM400_RID program might have a valid connection are those named in the list of Oracle Servers on the first page of the CHG2PCPRM command (the AM400_COM, see later, can actually be used to dynamically add Oracle Servers to that list). When a user attempts to CONNECT, the Access Manager queries the status of the AM400_RID program to see if it has a valid connection to the same Oracle Server to which the user is attempting to CONNECT. If it does, then the user connection is allowed. If it does not have a valid connection, then the user CONNECT fails.

Note:

Resolve-in-doubt processing actually occurs very rarely. It usually occurs due to some catastrophic failure. But it is required in order to keep data in a consistent state among the systems performing a distributed transaction. Informational messages are sent from the Access Manager program to the AM400_RID program every time a transaction changes status. No user data is sent in these messages.

The Oracle user ID specified for each connection that AM400_RID has with an Oracle Server must have rather high privileges. User ID (and password) are specified on the second screen of the CHG2PCPRM command when you are adding or modifying a possible connection. See Section 7.6, Oracle required privileges for further information.

Figure 7-1 AS/400 In-Doubt Transactions

As a transaction progresses, the Access Manager sends status messages to the AM400_RID program. The AM400_RID program does not send anything back to the Access Manager. In this manner, the AM400_RID program is kept informed of the status of all Oracle Access Manager transactions.

Enabling Two-Phase Commit Processing

To enable two phase commit processing, perform the following steps.

Checklist for Enabling Two-Phase Commit

Step 1: Change Access Manager Two-Phase Commit Default
Step 2: Change the AM400_RID Options
Step 3: Add or Remove AM400_COM Privileged User IDs
Step 4: Start the Subsystem that Runs the AM400_RID Program
Step 5: Start the AM400_COM Program (Optional)

Step 1: Change Access Manager Two-Phase Commit Default

Change the Access Manager two-phase commit parameter to *YES. For more information about this panel and its settings, refer to Chapter 6, "Configuring Access Manager".

Step 2: Change the AM400_RID Options

After issuing the CHG2PCPRM command, the first panel that is displayed confirms which library is to be changed and confirms the four resolve-in-doubt (RID) single-valued options that can be manipulated. The library name should match the library where you installed the Oracle Access Manager for AS/400. These four single-valued options are listed in the panel as: "DataQueue Timeout (in seconds)", "CleanUp Delay (in seconds)", "Trace Level", and "Dead Check counter". After these single-valued options, space is available on the panel to describe the Oracle servers to which Oracle Access Manager for AS/400 will connect in response to a connection request in STRSQL or in user applications.

Example 7-1 Change Resolve In Doubt (RID) Options Panel

_______________________________________________________________________________________
                         Change RID (Resolve In Doubt) Options
                                                                       System: HQAS400
 Installation Library:  ORAAM130

 DataQueue Timeout (in seconds)  . . . . . .  90
 CleanUp Delay (in seconds)  . . . . . . . .  30
 Trace Level . . . . . . . . . . . . . . . .  49
 Dead Check Counter  . . . . . . . . . . . . 100

 Type options, press Enter.
  1=Add  2=Change  4=Remove

 Opt      Oracle Server Name       Description
          VMS_SRV1                 Connect to VMS1X system
          MVS_920                  MVS running Oracle 9.2.0
          MVS_901                  MVS running Oracle 9.0.1
          PC_GTW_92                WinNt 4.0 server - 9.2
          H920_130                 SUN running Oracle 9.2.0
                                                                       Bottom

 F1=Help  F4=Prompt  F9=Retrieve  F10=Switch to PrivID screen  F12=Cancel

 (C) Copyright Oracle Corporation, 2006
_______________________________________________________________________________________

Caution:

The CLEANUP_DELAY, DATAQUEUE_TIMEOUT, and DEAD_CHECK_COUNTER are tuning parameters that are used to govern how often and when the AM400_RID program attempts to perform resolve in-doubt tasks. Do not change these parameters unless directed to do so by Oracle Support Services.

Oracle Server Name defines the name of any Oracle server that is to be accessed by Access Manager. If a server is not found in this list of servers, then it cannot be accessed by Access Manager when two-phase commit is enabled. When resolving an in-doubt transaction, the AM400_RID program uses this list to identify the Oracle server to which it must communicate. The Oracle server name is the same as the AS/400 RDB directory name and the TNS entry name.

The tuning parameters are described below:

DATAQUEUE_TIMEOUT is a tuning parameter that requires a single numeric value (in seconds). With two-phase commit enabled, every Oracle Access Manager user task transmits a status signal to the AM400_RID program for every connect and disconnect and at the beginning and end of every transaction. If a status signal is not received by the AM400_RID program after a specified interval, then a timeout occurs.

The AM400_RID program will check the status of all jobs (about which it knows) at least one time in every n seconds, where n is specified by the DATAQUEUE_TIMEOUT parameter. If a connection has already been terminated when the timeout occurs, then the AM400_RID program attempts to perform resolve in-doubt tasks.

CLEANUP_DELAY is a tuning parameter that requires a single numeric value (in seconds). It specifies the amount of time that the AM400_RID program waits (after it has determined that resolve-in-doubt processing might be needed) before attempting to perform resolve in-doubt tasks.

TRACE_LEVEL determines the trace level for the AM400_RID program. You should change this parameter only when requested to do so by Oracle Support Services.

DEAD_CHECK_COUNTER is a tuning parameter that requires a single numeric value. It specifies the minimum (or threshold) number of status signals that will be received by the AM400_RID program before it attempts to perform resolve in-doubt tasks. If the user connection still exists, then no action is taken.

Note:

The DATAQUEUE_TIMEOUT and DEAD_CHECK_COUNTER together determine how often the AM400_RID program checks to see if an in-doubt transaction might be present. DATAQUEUE_TIMEOUT specifies a value in time space, and DEAD_CHECK_COUNTER specifies a value in event-count space.

To add a new Oracle server, select option 1. To change the attributes of an existing Oracle server, select option 2. After selecting either option, the following panel is displayed:

Example 7-2 Change or Enter New Server Attributes Panel

_______________________________________________________________________________________
                          Change or Enter New Server Attributes
                                                                       System: HQAS400
 Installation Library  . . . . . . . .  ORAAM130
 Oracle Server Name  . . . . . . . . .  VMS_SRV1
 Privileged Oracle Id  . . . . . . . .  VMS1Z
 Password for Oracle Id  . . . . . . .  zilchnut
 Startup Parameter . . . . . . . . . .  startup
 Description . . . . . . . . . . . . .  Connect to VMS1X system
 Dead Check Counter  . . . . .  .  . .  100




 F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel

 (C) Copyright Oracle Corporation, 2006
_______________________________________________________________________________________

The fields in the panel are described as follows:

Oracle Server Name defines the name of the Oracle server that is to be accessed by Oracle Access Manager for AS/400.

Privileged Oracle Id designates the Oracle user ID that is associated with the defined server name. For additional information, refer to "Oracle Required Privileges".

Password for Oracle Id is the password of the Privileged Oracle Id that is associated with the defined server name. This parameter will be saved in an encrypted form. For additional information, refer to "Oracle Required Privileges".

Startup Parameter specifies when the AM400_RID program can attempt to connect to the designated server. If the STARTUP keyword is specified, then the AM400_RID program attempts to connect to the designated server when the AM400_RID program is started. If no keyword is specified, then the AM400_RID program attempts to connect to the designated server when any applications tries to connect to the server for the first time.

Description is a description of the defined oracle_server.

Step 3: Add or Remove AM400_COM Privileged User IDs

The options found on the panel in Example 7-1, "Change Resolve In Doubt (RID) Options Panel" can also be dynamically changed using the AM400_COM program. Use of the AM400_COM program to change these options is restricted to the set of AS/400 user IDs found in the panel in Example 7-3.

The AM400_COM program allows specified AS/400 user profiles to dynamically add or remove Oracle server parameters, to designate AM400_RID connections to Oracle servers, and to shut down connections to those Oracle servers. For example, if an Oracle server is not designated in the "Oracle Server Name" column in the Change RID (Resolve In Doubt) Options panel, then it can be defined dynamically (for the current invocation of the AM400_RID program) with the AM400_COM program. Refer to "AM400_COM Program" for additional information about the AM400_COM program.

To access the Change RID (Resolve In Doubt) Privileged IDs panel, first issue the CHG2PCPRM command. The first panel that is displayed confirms which library is to be changed and confirms the four single-valued RID options that can be used. Then select PF10 to display the Change RID (Resolve In Doubt) Privileged IDs panel.

Example 7-3 Change Privileged IDs (Relative to AM400_COM) Panel

_______________________________________________________________________________________
                      Change Privileged IDs (Relative to AM400_COM)
                                                                       System: HQAS400
 Installation Library:  ORAAM130

 Time options, press Enter.
   1=Add  4=Remove

 Opt       Privileged ID
           QSECOFR
           AM400ADMIN


                                                                       Bottom

 F1=Help  F4=Prompt  F9=Retrieve  F10=Switch to OracleServers screen  F12=Cancel

 (C) Copyright Oracle Corporation, 2006
_______________________________________________________________________________________

The user ID in the Privileged ID field has full access to the AM400_COM program.

The Change Privileged IDs (Relative to AM400_COM) panel is distributed with the default of QSECOFR in the Privileged ID field. QSECOFR is the AS/400 security administration user ID.

Step 4: Start the Subsystem that Runs the AM400_RID Program

To enable two-phase commit processing, the subsystem that runs the AM400_RID program must be started using the following command:

STRSBS install_lib/install_lib

where install_lib is the library in which Access Manager is installed.

Step 5: Start the AM400_COM Program (Optional)

You can now start the AM400_COM program to dynamically change the current options for the AM400_RID program. The changes made by the AM400_COM program are not persistent. They are valid only for the current instance of the AM400_RID program. Refer to "AM400_COM Program" for more information about starting the AM400_COM program.

Oracle Required Privileges

For the AM400_RID program to be able to perform resolve in-doubt processing, it must be able to connect to any Oracle server to which typical users connect with STRSQL or with a SQL-program package. The AM400_RID program connects to the Oracle server that is designated by Oracle Server Name in the panel in Example 7-2. The AM400_RID program connects to the Oracle server by using the Oracle user ID and password that are specified for that server on the CHG2PCPRM panel (Example 7-2, "Change or Enter New Server Attributes Panel"). The privileged user ID must have the following privileges:

read access to the SYS.PENDING_TRANS$ table
update access to the SYS.PENDING_SESSIONS$ table
privileges to COMMIT or ROLLBACK any transaction with the FORCE option. For more information about the FORCE option, refer to the Oracle Database SQL Language Reference.

To grant these privileges, the data base administrator (DBA) can issue the following GRANT commands in Oracle by using SQL*Plus. This can be done for every server that is designated in the Oracle Server Name column in the Change RID (Resolve In Doubt) Options panel (Example 7-1).

GRANT CREATE SESSIONS, FORCE ANY TRANSACTION TO userid;
GRANT SELECT ON SYS.PENDING_TRANS$ TO userid;
GRANT UPDATE ON SYS.PENDING_SESSIONS$ TO userid;

where userid is the Oracle user ID for the associated server, as set or changed in the Change or Enter New Server Attributes panel.

AM400_COM Program

The AM400_COM program is used to communicate dynamically with the AM400_RID program. The AM400_RID program controls the automatic resolve in-doubt processing of Oracle Access Manager for AS/400. The AM400_COM program can query the AM400_RID program for the following information:

the servers to which the AM400_RID program is connected
the user tasks connected to the various Oracle servers

The AM400_COM program can also be used to dynamically alter the environment of the AM400_RID program during an AS/400 user session. The AM400_COM program can be used to perform the following tasks:

add Oracle servers to the list of servers to which normal Access Manager tasks can connect
remove Oracle servers from the list of available servers so that no more connections can be made from AS/400 user tasks to those servers
change the DATAQUEUE_TIMEOUT parameter
change the CLEANUP_DELAY parameter

To access the AM400_COM program, type the following on the AS/400 command line:

CALL AM400_COM PARM(install_lib)

where install_lib is the library in which Access Manager is installed.

AM400_COM Commands

The AM400_COM program recognizes the following commands:

STATUS requests global connection status of the AM400_RID program. Examples:

Request the status of all servers to which the AM400_RID program has a connection:
```
STATUS SERVERS
```
or
```
STATUS
```
Request the status of the connection between AM400_RID and Oracle server server_name:
```
STATUS SERVER server_name
```
Request the status of what is known about a given job: The numeric_value is the six digit AS/400 job number.
```
STATUS JOB numeric_value
```

ADD adds a server to the list of available servers. For example:

ADD SERVER server_name oracle_id oracle_password

REMOVE removes a server from the list of available servers. For example:

REMOVE SERVER server_name

STARTUP starts a connection between the AM400_RID program and an Oracle server. For example:

STARTUP SERVER server_name

SHUTDOWN shuts down a connection between the AM400_RID program and an Oracle server. For example:

SHUTDOWN SERVER server_name [FORCE]

MODIFY modifies the Oracle user ID and the password to be used when the AM400_RID program attempts to connect to a specific Oracle server. For example:

MODIFY SERVER server_name oracle_id oracle_password

QUERY queries various items within the AM400_RID environment. Examples:

Query whether the AM400_RID program knows about a given server or has a connection to a given server. If the STARTUP parameter is given, and if the AM400_RID program knows about the server, but no connection exists from the AM400_RID program to that server, then an attempt is made to start the connection. For example:
```
QUERY SERVER server_name [STARTUP]
```
Query the value that is set for the DATAQUEUE_TIMEOUT parameter. For example:
```
QUERY DATAQUEUE_TIMEOUT
```
Query the value that is set for the CLEANUP_DELAY parameter. For example:
```
QUERY CLEANUP_DELAY
```
Query the value that is set for the DEAD_CHECK_COUNTER parameter. For example:
```
QUERY DEAD_CHECK_COUNTER
```
Query the value that is set for the TRACE_LEVEL parameter. For example:
```
QUERY TRACE_LEVEL
```
Query the value that is set for the SERVER_STARTUP_TIMEOUT parameter. This value is used by the AS/400 user task when it is attempting to ensure that the AM400_RID program has a connection to the same Oracle server to which the user task is attempting to connect. If the user task does not get a positive response from the AM400_RID program within the timeout value that is set by SERVER_STARTUP_TIMEOUT, then the user task is unable to CONNECT to the Oracle server. For example:
```
QUERY SERVER_STARTUP_TIMEOUT
```

SET sets various items within the AM400_RID environment. The following items can be set:

SET CLEANUP_DELAY numeric_value
SET DATAQ_TIMEOUT numeric_value
SET DEAD_CHECK_COUNTER numeric_value
SET TRACE_LEVEL numeric_value
SET SERVER_STARTUP_TIMEOUT numeric_value

LIST displays various items in the AM400_RID environment. Examples:

A list of all the servers that are known to the AM400_RID program and whether or not a connection exists from the AM400_RID program to that server. For example:
```
LIST AVAILABLE SERVERS
```
A list of various items about the shared data space through which the AM400_COM program, the AM400_RID program, and the AS/400 user tasks communicate. Use of this command might be requested by Oracle Support Services. An example:
```
LIST DATASPACE HEADER
```
A list of all used entries in the shared data space. Use of this command might be requested by Oracle Support Services. An example:
```
LIST ALL DATASPACE ENTRIES
```

FORCE forces a scan of all AS/400 user jobs that are known to the AM400_RID program and marks any jobs that need to use resolve in-doubt processing. For example:

FORCE SCAN