Read this chapter to learn about two-phase commit processing. It includes the following sections:
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.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.
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.There are two phases for two-phase commit processing:
prepare phase
commit phase
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.
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.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.
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.
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.
To enable two phase commit processing, perform the following steps.
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".
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:
TheCLEANUP_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:
TheDATAQUEUE_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.
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.
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.
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.
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:
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.
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
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.
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