Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 18
Oracle XA

This chapter describes how to use the Oracle XA library. The chapter includes the following topics:

- XA Library-Related Information
- Changes from Release 7.3 to Release 8.0
- General Issues and Restrictions
- Developing and Installing Applications That Use the XA Libraries
- Defining the xa_open String
- Interfacing to Precompilers and OCIs
- Transaction Control
- Migrating Precompiler or OCI Applications to TPM Applications
- XA Library Thread Safety
- Troubleshooting

## XA Library-Related Information

### General Information about the Oracle XA

For preliminary reading and additional reference information regarding the Oracle XA library, see the following documents:

- Oracle Call Interface Programmer's Guide

  See Also: For information on library linking filenames, see the Oracle operating system-specific documentation.

### README.doc

A `README.doc` file is located in a directory specified in the Oracle operating system-specific documentation and describes changes, bugs, or restrictions in the Oracle XA library for your platform since the last version.

## Changes from Release 7.3 to Release 8.0

The following changes have been made:

- Session Caching Is No Longer Needed
- Dynamic Registration Is Supported
- Loosely Coupled Transaction Branches Are Supported
- SQLLIB Is Not Needed for OCI Applications
- No Installation Script Is Needed to Run XA
- The XA Library Can Be Used with the Oracle Parallel Server Option on All Platforms
- Transaction Recovery for Oracle Parallel Server Has Been Improved
- Both Global and Local Transactions Are Possible
- The xa_open String Has Been Modified

### Session Caching Is No Longer Needed

Session caching is unnecessary with the new OCI. Therefore, the old `xa_open` string parameter, `SesCacheSz`, has been eliminated. Consequently, you can also reduce the sessions `init.ora` parameter. Instead, set the transactions `init.ora` parameter to the expected number of concurrent global transactions. Because sessions are not migrated when global transactions are resumed, applications must not refer to any session state beyond the scope of a service. For information on how to organize your application into services, refer to the documentation provided with the transaction processing monitor. In particular, savepoints and cursor fetch state will be cancelled when a transaction is suspended. This means that a savepoint taken by the application in a service will be invalid in another service, even though the two services may belong to the same global transaction.

### Dynamic Registration Is Supported

Dynamic registration can be used if, and only if, both the XA application and the Oracle Server are Version 8.

See Also: "Extensions to the XA Interface" on page 18-13

### Loosely Coupled Transaction Branches Are Supported

The Oracle8 Server supports both loosely and tightly coupled transaction branches in a single Oracle instance. The Oracle7 Server supported only tightly coupled transaction branches in a single instance, and loosely coupled transaction branches in different instances.

### SQLLIB Is Not Needed for OCI Applications

OCI applications used to require the use of `SQLLIB`. This meant OCI programmers had to buy `SQLLIB`, even if they had no desire to develop Pro* applications. This is no longer the case.

### No Installation Script Is Needed to Run XA

The SQL script `XAVIEW`.`SQL` is not needed to run XA applications in Oracle Version 8. It is, however, still necessary for Version 7.3 applications.

See Also: "Responsibilities of the DBA or System Administrator" on page 18-16.

### The XA Library Can Be Used with the Oracle Parallel Server Option on All Platforms

It was not possible with Version 7 to use the Oracle XA library together with the Oracle Parallel Server option on certain platforms. Only if the platform's implementation of the distributed lock manager supported transaction-based rather than process-based locking would the two work together. This limitation is no longer the case; if you can run the Oracle Parallel Server option, then you can run the Oracle XA library.

### Transaction Recovery for Oracle Parallel Server Has Been Improved

All transactions can be recovered from any instance of Oracle Parallel Server. Use the xa_recover call to provide a snapshot of the pending transactions.

### Both Global and Local Transactions Are Possible

It is now possible to have both global and local transactions within the same XA connection. Local transactions are transactions that are completely coordinated by the Oracle Server. For example, the update below belongs to a local transaction.

    CONNECT SCOTT/TIGER;
    UPDATE EMP set sal = sal + 1; /* begin local transaction*/
    COMMIT;                       /* commit local transaction*/

Global transactions, on the other hand, are coordinated by an external transaction manager such as a transaction processing monitor. In these transactions, the Oracle Server acts as a subordinate and processes the XA commands issued by the transaction manager. The update shown below belongs to a global transaction.

    xa_open(oracle_xa+acc=p/SCOTT/TIGER+sestm=10", 1, TMNOFLAGS);
                                 /*Transaction manager opens */
                                 /* connection to the Oracle server*/
    tpbegin();                   /* begin global transaction, the transaction*/
                                 /*manager issues XA commands to the oracle*/
                                 /*server to start a global transaction */
    UPDATE EMP set sal = sal + 1;
                                 /* Update is performed in the */
                                 /* global transaction*/
    tpcommit();                  /* commit global transaction, */
                                 /* the transaction manager issues XA commands*/
                                 /* to the Oracle server to commit */
                                 /* the global transaction */

The Oracle7 Server forbids a local transaction from being started in an XA connection. The update shown below would return an `ORA`-`2041` error code.

    xa_open("oracle_xa+acc=p/SCOTT/TIGER+sestm=10" , 1, TMNOFLAGS);
                                 /* Transaction manager opens */
                                 /*connection to the Oracle server */
    UPDATE EMP set sal = sal + 1;/* Oracle 7 returns an error */

The Oracle8 Server, on the other hand, allows local transactions to be started in an XA connection. The only restriction is that the local transaction must be ended (committed or rolled back) before starting a global transaction in the connection.

See Also: Oracle Call Interface Programmer's Guide for more information on global transactions.

### The xa_open String Has Been Modified

Two new parameters have been added. They are:

- `Loose_Coupling`

  This parameter has a boolean value and should not be set to true when connected to an Oracle7 Server. If set to true, it indicates that global transaction branches will be loosely coupled, that is, locks will not be shared between branches.

- `SesWt`

  This parameter's value indicates the time-out limit when waiting for a transaction branch that is being used by another session. If Oracle cannot switch to the transaction branch within `SesWt` seconds, `XA_RETRY` will be returned.

Two parameters have been made obsolete and should only be used when connected to an Oracle Server Release 7.3.

- `GPWD`

  The group password is not used by Oracle8. A session that is logged in with the same user name as the session that created a transaction branch will be allowed to switch to the transaction branch.

- `SesCacheSz`

  This parameter is not used by Oracle8 because session caching has been eliminated.

## General Issues and Restrictions

### Database Links

Oracle XA applications can access other Oracle Server databases through database links, with the following restrictions:

- Use the Multi-Threaded Server configuration.

  This means the transaction processing monitors (TPMs) will use shared servers to open the connection to Oracle. The O/S network connection required for the database link will be opened by the dispatcher instead of the Oracle server process. Thus, when a particular service or RPC completes, the transaction can be detached from the server so that it can be used by other services or RPCs.

- Access to the other database must use SQL*Net Version 2 or Net8.
- The other database being accessed should be another Oracle Server database.

Assuming that these restrictions are satisfied, Oracle Server will allow such links and will propagate the transaction protocol (prepare, rollback, and commit) to the other Oracle Server databases.

WARNING: If these restrictions are not satisfied, when you use database links within an XA transaction, it creates an O/S network connection in the Oracle Server that is connected to the TPM server process. Since this O/S network connection cannot be moved from one process to another, you cannot detach from this server. When you access a database through a database link, you will receive an ORA#24777 error.

If using the Multi-Threaded Server configuration is not possible then, access the remote database through the Pro*C/C++ application using EXEC SQL AT syntax.

The parameter `open_links_per_instance` specifies the number of migratable open database link connections. These `dblink` connections are used by XA transactions so that the connections are cached after a transaction is committed. Another transaction is free to use the dblink connection provided the user that created the connection is the same as the user who created the transaction. This parameter is different from the `open_links` parameter, which is the number of dblink connections from a session. The `open_links` parameter is not applicable to XA applications.

### Oracle Parallel Server Option

You can recover failed transactions from any instance of Oracle Parallel Server. You can also heuristically commit in-doubt transactions from any instance. An XA recover call will give a list of all prepared transactions for all instances.

### SQL-based Restrictions

SQL-based restrictions are listed in this section.

#### Rollbacks and Commits

Because the transaction manager is responsible for coordinating and monitoring the progress of the global transaction, the application should not contain any Oracle Server-specific statement that independently rolls back or commits a global transaction. However, you can use rollbacks and commits in a local transaction.

Do not use `EXEC` `SQL` `ROLLBACK` `WORK` for precompiler applications when you are in the middle of a global transaction. Similarly, an OCI application should not execute `OCITransRollback`(), or the Version 7 equivalent `orol`(). You can roll back a global transaction by calling`tx_rollback`().

Similarly, a precompiler application should not have the `EXEC` `SQL` `COMMIT` WORK statement in the middle of a global transaction. An OCI application should not execute `OCITransCommit`() or the Version 7 equivalent `ocom`(). Instead, use `tx_commit`() or `tx_rollback`() to end a global transaction.

#### DDL Statements

Because a DDL SQL statement such as `CREATE` `TABLE` implies an implicit commit, the Oracle XA application cannot execute any DDL SQL statements.

#### Session State

Oracle does not guarantee that session state will be valid between services. For example, if a service updates a session variable (such as a global package variable), another service that executes as part of the same global transaction may not see the change. Use savepoints only within a service. The application must not refer to a savepoint that was created in another service. Similarly, an application must not attempt to fetch from a cursor that was executed in another service.

#### SET TRANSACTION

Do not use the `SET` `TRANSACTION` `READ` `ONLY` | `READ` `WRITE` | `USE` `ROLLBACK` `SEGMENT` `SQL` statement.

#### Connecting or Disconnecting with EXEC SQL

Do not use the `EXEC` `SQL` command to connect or disconnect. That is, do not use `EXEC` `SQL` `COMMIT` `WORK` `RELEASE` or `EXEC` `SQL` `ROLLBACK` `WORK` `RELEASE`.

### Miscellaneous XA Issues

Note the following additional information about Oracle XA:

#### Transaction Branches

Oracle Server transaction branches within the same global transaction can share locks in either a tightly or loosely coupled manner. However, if the branches are on different instances when running Oracle Parallel Server, then they will be loosely coupled.

In tightly coupled transaction branches, the locks are shared between the transaction branches. This means that updates performed in one transaction branch can be seen in other branches that belong to the same global transaction before the update is committed. The Oracle Server obtains the DX lock before executing any statement in a tightly coupled branch. Hence, the advantage of using loosely coupled transaction branches is that there will be more concurrency (because a lock is not obtained before the statement is executed). The disadvantage is that all the transaction branches must go through the two phases of commit, that is, XA one phase optimization cannot be used. These trade-offs between tightly coupled branches and loosely coupled branches are illustrated in Table 18-1.

##### Table 18-1 Tightly and Loosely Coupled Tranaction Branches

Attribute | Tightly Coupled Branches | Loosely Coupled Branches
--- | --- | ---
Two Phase Commit | Read-only Optimization [prepare for all branches, commit for last branch] | Two phases [prepare and commit for all branches]
Serialization | Database Call | None

#### Association Migration

The Oracle Server does not support association migration (a means whereby a transaction manager may resume a suspended branch association in another branch).

#### Asynchronous Calls

The optional XA feature asynchronous XA calls is not supported.

#### Initialization Parameters

Set the `transactions init.ora` parameter to the expected number of concurrent global transactions.

The parameter `open_links_per_instance` specifies the number of migratable open database link connections. These dblink connections are used by XA transactions so that the connections are cached after a transaction is committed.

See Also: "Database Links" on page 18-6 for further information.

#### Maximum Connections per Thread

The maximum number of `xa_opens` per thread is now 32. Previously, it had been 8.

#### Installation

No scripts need be executed to use XA. It is necessary, however, to run the xaview.sql script to run Release 7.3 applications with the Oracle8 Server. Grant the `SELECT` privilege on `SYS`.`DBA_PENDING_TRANSACTIONS` to all users that connect to Oracle through the XA interface.

#### Compatibility

The XA library supplied with Release 7.3 can be used with a Release 8.0 Oracle Server. You must use the Release 7.2 XA library with a Release 7.2 Oracle Server. You can use the 8.0 library with a Release 7.3 Oracle Server. There is only one case of backward compatibility: an XA application that uses Release 8.0 OCI will work with a Release 7.3 Oracle Server, but only if you use `sqlld2` and obtain an `lda_def` before executing SQL statements. Client applications must remember to convert the Version 7 LDA to a service handle using `OCILdaToSvcCtx`() after completing the OCI calls.

### Basic Architecture

The Oracle XA library is an external interface that allows global transactions to be coordinated by a transaction manager other than the Oracle8 Server. This allows inclusion of non-Oracle8 Server entities called resource managers (RM) in distributed transactions.

The Oracle XA library conforms to the X/Open Distributed Transaction Processing (DTP) software architecture's XA interface specification.

See Also: For a general overview of XA, including basic architecture, see X/Open CAE Specification - Distributed Transaction Processing: The XA Specification. You can obtain a copy of this document by requesting X/Open Document No. XO/CAE/91/300 or ISBN 1 872630 24 3 from: X/Open Company, Ltd., 1010 El Camino Real, Suite 380, Menlo Park, CA 94025, U.S.A.

### X/Open Distributed Transaction Processing(DTP)

The X/Open DTP architecture defines a standard architecture or interface that allows multiple application programs to share resources, provided by multiple, and possibly different, resource managers. It coordinates the work between application programs and resource managers into global transactions.

Figure 18-1 illustrates a possible X/Open DTP model.

A resource manager (RM) controls a shared, recoverable resource that can be returned to a consistent state after a failure. For example, Oracle8 Server is an RM and uses its redo log and undo segments to return to a consistent state after a failure. An RM provides access to shared resources such as a database, file systems, printer servers, and so forth.

A transaction manager (TM) provides an application program interface (API) for specifying the boundaries of the transaction and manages the commit and recovery procedures.

Normally, Oracle8 Server acts as its own TM and manages its own commit and recovery. However, using a standards-based TM allows Oracle8 Server to cooperate with other heterogeneous RMs in a single transaction.

A TM is usually a component provided by a transaction processing monitor (TPM) vendor. The TM assigns identifiers to transactions, and monitors and coordinates their progress. It uses Oracle XA library subroutines to tell Oracle8 Server how to process the transaction, based on its knowledge of all RMs in the transaction. You can find a list of the XA subroutines and their descriptions later in this section.

An application program (AP) defines transaction boundaries and specifies actions that constitute a transaction. For example, an AP can be a precompiler or OCI program. The AP operates on the RM's resource through the RM's native interface, for example SQL. However, it starts and completes all transaction operations via the transaction manager through an interface called TX. The AP itself does not directly use the XA interface

#### Figure 18-1 One Possible DTP Model

.

Note: The naming conventions for the TX interface and associated subroutines are vendor-specific, and may differ from those used here. For example, you may find that the `tx_open` call is referred to as `tp_open` on your system. To check terminology, see the documentation supplied with the transaction processing monitor.

### Transaction Recovery Management

The Oracle XA library interface follows the two-phase commit protocol, consisting of a prepare phase and a commit phase, to commit transactions.

In phase one, the prepare phase, the TM asks each RM to guarantee the ability to commit any part of the transaction. If this is possible, then the RM records its prepared state and replies affirmatively to the TM. If it is not possible, the RM may roll back any work, reply negatively to the TM, and forget any knowledge about the transaction. The protocol allows the application, or any RM, to roll back the transaction unilaterally until the prepare phase is complete.

In phase two, the commit phase, the TM records the commit decision. Then the TM issues a commit or rollback to all RMs which are participating in the transaction.

Note: a TM can issue a commit for an RM only if all RMs have replied affirmatively to phase one.

### Oracle XA Library Interface Subroutines

The Oracle XA library subroutines allow a TM to instruct an Oracle8 Server what to do about transactions. Generally, the TM must "open" the resource (using `xa_open`). Typically, this will result from the AP's call to `tx_open`. Some TMs may call `xa_open` implicitly, when the application begins. Similarly, there is a close (using `xa_close`) that occurs when the application is finished with the resource. This may be when the AP calls `tx_close` or when the application terminates.

There are several other tasks the TM instructs the RMs to do. These include among others:

- starting a new transaction and associating it with an ID
- rolling back a transaction
- preparing and committing a transaction

### XA Library Subroutines

The following XA Library subroutines are available:

`xa_open` | Connects to the resource manager.
`xa_close` | Disconnects from the resource manager.
`xa_start` | Starts a new transaction and associate it with the given transaction ID (XID), or associates the process with an existing transaction.
`xa_end` | Disassociates the process from the given XID.
`xa_rollback` | Rolls back the transaction associated with the given XID.
`xa_prepare` | Prepares the transaction associated with the given XID. This is the first phase of the two-phase commit protocol.
`xa_commit` | Commits the transaction associated with the given XID. This is the second phase of the two-phase commit protocol.
`xa_recover` | Retrieves a list of prepared, heuristically committed or heuristically rolled back transaction.
`xa_forget` | Forgets the heuristic transaction associated with the given XID.

In general, the AP does not need to worry about these subroutines except to understand the role played by the `xa_open` string.

### Extensions to the XA Interface

Two functions have been added to the XA interface, one for returning the OCI service handle associated with an XA connection, and one for returning an XA error code.

1. `OCISvcCtx *xaoSvcCtx(text *dbname)`:

   This function returns the OCI service handle for a given XA connection. The dbname parameter must be the same as the dbname parameter passed in the `xa_open` string. OCI applications can use this routing instead of the `sqlld2` calls to obtain the connection handle. Hence, OCI applications need not link with the SQLLIB library. The service handle can be converted to the Version 7 OCI logon data area(LDA) using `OCISvcCtxToLda`() [Version 8 OCI]. Client applications must remember to convert the Version 7 LDA to a service handle using `OCILdaToSvcCtx`() after completing the OCI calls.

2. `OCIEnv *xaoEnv(text *dbname)`:

   This function returns the OCI environment handle for a given XA connection. The dbname parameter must be the same as the dbname parameter passed in the `xa_open` string.

3. `int xaosterr(OCISvcCtx *SvcCtx, sb4 error)`:

   This function, only applicable to dynamic registration, converts an Oracle error code to an XA error code. The first parameter is the service handle used to execute the work in the database. The second parameter is the error code that was returned from Oracle. Use this function to determine if the error returned from an OCI command was caused because the `xa_start` failed. The function returns `XA_OK` if the error was not generated by the XA module and a valid XA error if the error was generated by the XA module.

### Transaction Processing Monitors (TPMs)

A transaction processing monitor (TPM) coordinates the flow of transaction requests between the client processes that issue requests and the back-end servers that process them. Basically, it coordinates transactions that require the services of several different types of back-end processes, such as application servers and resource managers that are distributed over a network.

The TPM synchronizes any commits and rollbacks required to complete a distributed transaction. The transaction manager (TM) portion of the TPM is responsible for controlling when distributed commits and rollbacks take place. Thus, if a distributed application program is written to take advantage of a TPM, the TM portion of the TPM is responsible for controlling the two-phase commit protocol. The RMs enable the TMs to do this.

Because the TM controls distributed commits or rollbacks, it must communicate directly with Oracle (or any other resource manager) through the Oracle XA library interface.

### Required Public Information

As a resource manager, Oracle is required to publish the following information.

`xa_switch_t` structures | The Oracle Server `xa_switch_t` structure name for static registration is `xaosw`. The Oracle Server `xa_switch_t` structure name for dynamic registration is `xaoswd`. These structures contain entry points and other information for the resource manager.
`xa_switch_t` resource mgr | The Oracle Server resource manager name within the `xa_switch_t` structure is `Oracle_XA`.
`close string` | The close string used by `xa_close` () is ignored and is allowed to be null.
open string | The format of the open string used by `xa_open` () is described in detail in "Developing and Installing Applications That Use the XA Libraries" on page 18-16.
libraries | Libraries needed to link applications using Oracle XA have operating system-specific names. It is similar to linking an ordinary precompiler or OCI program except you may have to link any TPM-specific libraries. If you are not using `sqllib`, make sure to link with `$ORACLE_HOME/lib/xaonsl.o`.
requirements | A purchased and installed distributed database option.

### Registration

Dynamic and static registration are supported by the Oracle8 Server. The basic possibilities are shown in Table 18-2.

##### Table 18-2 XA Registration

Client | Server | XA Registration
--- | --- | ---
8.0 XA application | 8.0 | Dynamic
8.0 XA application | 7.3 | Static
7.3 XA application | 8.0 | Static

## Developing and Installing Applications That Use the XA Libraries

This section discusses developing and installing Oracle8 Server applications. It describes the responsibilities of both the DBA, or system administrator, and the application developer. It also defines how to construct the open string.

### Responsibilities of the DBA or System Administrator

The responsibilities of the DBA or system administrator are

1. Define the open string with the application developer's help.

   This is described in "Defining the xa_open String" on page 18-17.

2. Make sure the `DBA_PENDING_TRANSACTIONS` view exists on the database.

##### For Oracle Server Release 7.3:

  Make sure `V$XATRANS$` exists.

  This view should have been created during the XA library installation. You can manually create the view if needed by running the SQL script `XAVIEW`.`SQL`. This SQL script should be executed as the Oracle user `SYS`. Grant the `SELECT` privilege to the `V$XATRANS$` view for all Oracle Server accounts which will be used by Oracle XA library applications.

  See Also: Your Oracle operating system-specific documentation contains the location of the `XAVIEW`.`SQL` script.

##### For Oracle Server Release 8.0:

  Grant the select privilege to the `DBA_PENDING_TRANSACTIONS` view for all Oracle Server user(s) specified in the`xa_open` string.

- Install the resource manager, using the open string information, into the TPM configuration, following the TPM vendor instructions.

  The DBA or system administrator should be aware that a TPM system will start the process that connects to an Oracle8 Server. See your TPM documentation to determine what environment exists for the process and what user ID it will have.

  Be sure that correct values are set for `ORACLE_HOME` and `ORACLE_SID`.

  Next, grant the user ID write permission to the directory in which the XA trace file will be written. See "Defining the xa_open String" on page 18-17 for information on how to specify a sid or a trace directory that is different from the defaults.

  Also be sure to grant the user the `SELECT` privilege on `DBA_PENDING_TRANSACTIONS`.

- Start up the relevant databases to bring Oracle XA applications on-line.

  This should be done before starting any TPM servers.

  ### Responsibilities of the Application Developer

  The application developer's responsibilities are

  1. Define the open string with the DBA or system administrator's help.

     Defining the open string is described later in this section.

  2. Develop the applications.

     Observe special restrictions on transaction-oriented SQL statements for precompilers. See "Interfacing to Precompilers and OCIs" on page 18-22.

  3. Link the application according to TPM vendor instructions.

  ## Defining the xa_open String

  The open string is used by the transaction monitor to open the database. The maximum number of characters in an open string is 256.

  This section covers:

  - Syntax of the xa_open String
  - Required Fields
  - Optional Fields

  ### Syntax of the xa_open String

      Oracle_XA{+required_fields...} [+optional_fields...]

  where required_fields are:

    `Acc=P`//

  or

    `Acc=P`/user/password

    `SesTm`=session_time_limit

  and where optional_fields are:

    `DB`=db_name

    `LogDir`=log_dir

    `MaxCur`=maximum_#_of_open_cursors

    `SqlNet`=connect_string

    `Loose_Coupling`=true/false

    `SesWt`=session_wait_limit

    `Threads`=true/false

    Note: You can enter the required fields and optional fields in any order when constructing the open string. All field names are case insensitive. Their values may or may not be case-sensitive depending on the platform. There is no way to use the "+" character as part of the actual information string.

  ### Required Fields

  Required fields for the open string are described in this section.

  `Acc=P`//

  or

  Acc=P/user/password

  `Acc` | Specifies user access information
  `P` | Indicates that explicit user and password information is provided.
  `P//` | Indicates that no explicit user or password information is provided and that the operating system authentication form will be used. For more information see Oracle8 Administrator's Guide.
  user | A valid Oracle Server account.
  password | The corresponding current password.

  For example, `Acc=P/scott/tiger` indicates that user and password information is provided. In this case, the user is `scott` and the password is `tiger`.

  As previously mentioned, make sure that `scott` has the `SELECT` privilege on the `DBA_PENDING_TRANSACTIONS` table.

  `Acc=P`// indicates that no user or password information is provided, thus defaulting to operating system authentication.

  `SesTm`=session_time_limit

  `SesTm` | Specifies the maximum length of time a transaction can be inactive before it is automatically aborted by the system.
  session_time_limit | This value should be the maximum time allowed in a transaction between one service and the next, or a service and the commit or rollback of the transaction. For example, if the TPM uses remote procedure calls between the client and the servers, then SesTM applies to the time between the completion of one RPC and the initiation of the next RPC, or the `tx_commit`, or the `tx_rollback`. The unit for this time limit is in seconds. The value of 0 indicates no limit, but entering a value of 0 is strongly discouraged. For example, `SesTM=15` indicates that the session idle time limit is 15 seconds.

  ### Optional Fields

  Optional fields are described below.

  `DB`=db_name

  `DB` | Specifies the database name.
  db_name | Indicates the name used by Oracle precompilers to identify the database. Application programs that use only the default database for the Oracle precompiler (that is, they do not use the AT clause in their SQL statements) should omit the `DB`=db_name clause in the open string. Applications that use explicitly named databases should indicate that database name in their `DB`=db_name field. Version 7 OCI programs need to call the `sqlld2`() function to obtain the correct `lda_def`, which is the equivalent of a service context. Version 8 OCI programs need to call the `xaoSvcCtx` function to get the `OCISvcCtx` service context. The db_name is not the sid and is not used to locate the database to be opened. Rather, it correlates the database opened by this open string with the name used in the application program to execute SQL statements. The sid is set from either the environment variable `ORACLE_SID` of the TPM application server or the sid given in the Net8 (formerly, SQL*Net) clause in the open string. The Net8 clause is described later in this section. Some TPM vendors provide a way to name a group of servers that use the same open string. The DBA may find it convenient to choose the same name both for that purpose and for db_name.

  For example, `DB=payroll` indicates that the database name is "payroll", and that the application server program will use that name in AT clauses.

  `LogDir`=log_dir

  LogDir | Specifies the directory on a local machine where the Oracle XA library error and tracing information may be logged.
  log_dir | Indicates the pathname of the directory where the tracing information should be stored. The default is `$ORACLE_HOME/rdbms/log` if ORACLE_HOME is set, otherwise it is the current directory.

  For example, `LogDir=/xa_trace` indicates that the error and tracing information is located under the `/xa_trace` directory.

  Note: Ensure that the directory you specify for logging exists and the application server can write to it.

  `MaxCur`=maximum_#_of_open_cursors

  MaxCur | Specifies the number of cursors to be allocated when the database is opened. It serves the same purpose as the precompiler option `maxopencursors`.
  maximum_#_of_ open_cursors | Indicates the number of open cursors to be cached.

  For example, `MaxCur=5` indicates that the precompiler should try to keep five open cursors cached.

  Note: This parameter overrides the precompiler option `maxopencursors` that you might have specified in your source code or at compile time.

  See Also: Chapter 8 in Pro*C/C++ Precompiler Programmer's Guide. for more information on `maxopencursors.`

  SqlNet=db_link

  SqlNet | Specifies the Net8 (formerly, SQL*Net) database link.
  db_link | Indicates the string to use to log on to the system. The syntax for this string is the same as that used to set the TWO-TASK environment variable.

  For example, `SqlNet=hqfin@NEWDB` indicates the database with sid=`NEWDB` accessed at host `hqfin` by TCP/IP.

  The `SqlNet` parameter can be used to specify the `ORACLE_SID` in cases where you cannot control the server environment variable. It must also be used when the server needs to access more than one Oracle Server database. To use the Net8 string without actually accessing a remote database, use the Pipe driver.

  For example:

           SqlNet=localsid1

  where:

  `localsid1` | is an alias defined in the Net8 `tnsnames.ora` file.

  Make sure that all databases to be accessed with a Net8 database link have an entry in /`etc/oratab`.

  `Loose_Coupling`=true/false

  `Loose_Coupling` | Specifies whether locks will be shared between branches. This parameter should not be set to true when connected to an Oracle7 Server. The default value is False.
  true/false | If locks are shared between branches, the setting is false.

  `SesWt`=session_wait_limit

  `SesWt` | Specifies the time-out limit when waiting for a transaction branch that is being used by another session. The default value is 60 seconds.
  session_wait_limit | The number of seconds Oracle will wait before XA_RETRY is returned.

  `Threads`=true/false

  Threads | Specifies whether the application is multi-threaded. The default value is False.
  true/false | If the application is multi-threaded, the setting is true.

  ## Interfacing to Precompilers and OCIs

  This section describes how to use the Oracle XA library with precompilers and Oracle Call Interfaces (OCIs).

  ### Using Precompilers with the Oracle XA Library

  When used in an Oracle XA application, cursors are valid only for the duration of the transaction. Explicit cursors should be opened after the transaction begins, and closed before the commit or rollback.

  There are two options to choose from when interfacing with precompilers:

  - using precompilers with the default database
  - using precompilers with a named database

  The following examples use the precompiler Pro*C/C++.

  #### Using Precompilers with the Default Database

  To interface to a precompiler with the default database, make certain that the `DB`=db_name field, used in the open string, is not present. The absence of this field indicates the default connection, and only one default connection is allowed per process.

  The following is an example of an open string identifying a default Pro*C/C++ connection.

      ORACLE_XA+SqlNet=host@MAIL+ACC=P/scott/tiger
        +SesTM=10+LogDir=/usr/local/logs

  Note that the `DB`=db_name is absent, indicating an empty database ID string.

  The syntax of a SQL statement would be:

      EXEC SQL UPDATE EMP SET SAL = sal*1.5;

  #### Using Precompilers with a Named Database

  To interface to a precompiler with a named database, include the `DB`=db_name field in the open string. Any database you refer to must reference the same db_name you specified in the corresponding open string.

  An application may include the default database, as well as one or more named databases, as shown in the following examples.

  For example, suppose you want to update an employee's salary in one database, his department number (DEPTNO) in another, and his manager in a third database. You would configure the following open strings in the transaction manager:

      ORACLE_XA+DB=MANAGERS+SqlNet=hqfin@SID1+ACC=P/scott/tiger
        +SesTM=10+LogDir=/usr/local/xalog
      ORACLE_XA+DB=PAYROLL+SqlNet=SID2+ACC=P/scott/tiger
        +SesTM=10+LogDir=/usr/local/xalog
      ORACLE_XA+SqlNet=hqemp@SID3+ACC=P/scott/tiger
        +SesTM=10+LogDir=/usr/local/xalog

  Note that there is no `DB`=db_name field in the last open string.

  In the application server program, you would enter declarations such as:

      EXEC SQL DECLARE PAYROLL DATABASE;
      EXEC SQL DECLARE MANAGERS DATABASE;

  Again, the default connection (corresponding to the third open string that does not contain the `db_name` field) needs no declaration.

  When doing the update, you would enter statements similar to the following:

      EXEC SQL AT PAYROLL UPDATE EMP SET SAL=4500 WHERE EMPNO=7788;
      EXEC SQL AT MANAGERS UPDATE EMP SET MGR=7566 WHERE EMPNO=7788;
      EXEC SQL UPDATE EMP SET DEPTNO=30 WHERE EMPNO=7788;

  There is no AT clause in the last statement because it is referring to the default database.

  In Oracle precompilers Release 1.5.3 or later, you can use a character host variable in the AT clause, as the following example shows:

      EXEC SQL BEGIN DECLARE SECTION;
        DB_NAME1 CHARACTER(10);
        DB_NAME2 CHARACTER(10);
      EXEC SQL END DECLARE SECTION;
          .
          .
      SET DB_NAME1 = 'PAYROLL'
      SET DB_NAME2 = 'MANAGERS'
          .
          .
      EXEC SQL AT :DB_NAME1 UPDATE...
      EXEC SQL AT :DB_NAME2 UPDATE...

  WARNING: Oracle recommends against using XA applications to create connections. Any work performed would be outside the global transaction, and would have to be committed separately.

  ### Using OCI with the Oracle XA Library

  OCI applications that use the Oracle XA library should not call `OCISessionBegin`() (`olon`() or `orlon`() in Version 7) to log on to the resource manager. Rather, the logon should be done through the TPM. The applications can execute the function `xaoSvcCtx`() (`sqlld2`() in Version 7) to obtain the service context (`lda` in Version 7) structure they need to access the resource manager.

  Because an application server can have multiple concurrent open Oracle Server resource managers, it should call the function `xaoSvcCtx`() with the correct arguments to obtain the correct service context.

  ##### For Release 7.3

  If `DB`=db_name is not present in the open string, then execute:

      sqlld2(lda, NULL, 0);

  to obtain the `lda` for this resource manager.

  Alternatively, if `DB`=db_name is present in the open string, then execute:

      sqlld2(lda, db_name, strlen(db_name));

  to obtain the `lda` for this resource manager.

  ##### For Release 8.0

  If `DB`=db_name is not present in the open string, then execute:

      xaoSvcCtx(NULL);

  to obtain the `xaoSvcCtx` for this resource manager.

  Alternatively, if `DB`=db_name is present in the open string, then execute:

      xaoSvcCtx(db_name);

  to obtain the `OCISvcCtx` for this resource manager.

  Note Also: Oracle Call Interface Programmer's Guide. for more information about using the `OCISvcCtx`

  ## Transaction Control

  This section explains how to use transaction control within the Oracle XA library environment.

  When the XA library is used, transactions are not controlled by the SQL statements which commit or roll back transactions. Rather, they are controlled by an API accepted by the TM which starts and stops transactions. Most of the TMs use the TX interface for this. It includes the following functions:

  `tx_open` | logs into the resource manager(s)
  `tx_close` | logs out of the resource manager(s)
  `tx_begin` | starts a new transaction
  `tx_commit` | commits a transaction
  `tx_rollback` | rolls back the transaction

  Most TPM applications are written using a client-server architecture where an application client requests services and an application server provides services. The examples that follow use such a client-server model. A service is a logical unit of work, which in the case of the Oracle Server as the resource manager, comprises a set of SQL statements that perform a related unit of work.

  For example, when a service named "credit" receives an account number and the amount to be credited, it will execute SQL statements to update information in certain tables in the database. In addition, a service might request other services. For example, a "transfer fund" service might request services from a "credit" and "debit" service.

  Usually application clients request services from the application servers to perform tasks within a transaction. However, for some TPM systems, the application client itself can offer its own local services.

  You can encode transaction control statements within either the client or the server; as shown in the examples.

  To have more than one process participating in the same transaction, the TPM provides a communication API that allows transaction information to flow between the participating processes. Examples of communications APIs include RPC, pseudo-RPC functions, and send/receive functions.

  Because the leading vendors support different communication functions, the examples that follow use the communication pseudo-function `tpm_service` to generalize the communications API.

  X/Open has included several alternative methods for providing communication functions in their preliminary specification. At least one of these alternatives is supported by each of the leading TPM vendors.

  ### Examples of Precompiler Applications

  The following examples illustrate precompiler applications. Assume that the application servers have already logged onto the TPM system, in a TPM-specific manner.

  The first example shows a transaction started by an application server, and the second example shows a transaction started by an application client.

  ##### Example 1: Transaction started by an application server

  Client:

      tpm_service("ServiceName");              /*Request Service*/

  Server:

      ServiceName()
      {
      <get service specific data>
      tx_begin();                             /* Begin transaction boundary*/
      EXEC SQL UPDATE ....;

      /*This application server temporarily becomes*/
      /*a client and requests another service.*/

      tpm_service("AnotherService");
      tx_commit();                             /*Commit the transaction*/
      <return service status back to the client>
      }

  ##### Example 2: Transaction started by an application client.

  Client:

      tx_begin();                            /* Begin transaction boundary */
      tpm_service("Service1");
      tpm_service("Service2");
      tx_commit();                           /* Commit the transaction */

  Server:

      Service1()
      {
      <get service specific data>
      EXEC SQL UPDATE ....;
      <return service status back to the client>
      }
      Service2()
      {
      <get service specific data>
      EXEC SQL UPDATE ....;
      ...
      <return service status back to client>
      }

  ## Migrating Precompiler or OCI Applications to TPM Applications

  To migrate existing precompiler or OCI applications to a TPM application using the Oracle XA library, you must do the following:

  1. Reorganize the application into a framework of "services".

     This means that application clients request services from application servers.

     Some TPMs require the application to use the `tx_open` and `tx_close` functions, whereas other TPMs do the logon and logoff implicitly.

     If you do not specify the `sqlnet` parameter in your open string, the application will use the default Net8 driver. Thus, you need to be sure that the application server is brought up with the `ORACLE_HOME` and `ORACLE_SID` environment variables properly defined. This is accomplished in a TPM-specific fashion. See your TPM vendor documentation for instructions on how to accomplish this.

  2. Ensure that the application replaces the regular connect and disconnect statements.

     For example, replace the connect statements `EXEC` `SQL` `CONNECT` (for precompilers) or `OCISessionBegin`() (for OCIs) by `tx_open`(). Replace the disconnect statements `EXEC` `SQL` `COMMIT`/`ROLLBACK` `RELEASE` `WORK` (for precompilers), or `OCISessionEnd`() (for OCIs) by `tx_close()`. The V7 equivalent for `OCISessionBegin`() was `olon()` and for `OCISessionEnd`(), `ologof`().

  3. Ensure that the application replaces the regular commit/rollback statements and begins the transaction explicitly.

     For example, replace the commit/rollback statements `EXEC` `SQL` `COMMIT`/`ROLLBACK` `WORK` (for precompilers), or `ocom`()`/oro`l() (for OCIs) by `tx_commit`()/`tx_rollback`() and start the transaction by calling `tx_begin`().

  4. Ensure that the application resets the fetch state prior to ending a transaction. In general,`release_cursor=no` should be used. Use `release_cursor=yes` only when certain that a statement will be executed only once.

  Table 18-3 lists the TPM functions that replace regular Oracle commands when migrating precompiler or OCI applications to TPM applications.

  ##### Table 18-3 TPM Replacement Commands

  Regular Oracle Commands | TPM Functions
  `CONNECT` user/password | `tx_open` (possibly implicit)
  implicit start of transaction | `tx_begin`
  `SQL` | service that executes the SQL
  `COMMIT` | `tx_commit`
  `ROLLBACK` | `tx_rollback`
  disconnect | `tx_close` (possibly implicit)
  `SET` `TRANSACTION` `READ` `ONLY` | illegal

  ## XA Library Thread Safety

  If you use a transaction monitor that supports threads, the Oracle XA library allows you to write applications that are thread safe. Certain issues must be kept in mind, however.

  A thread of control (or thread) refers to the set of connections to resource managers. In an unthreaded system, each process could be considered a thread of control, since each process has its own set of connections to resource managers and each process maintains its own independent resource manager table.

  In a threaded system, each thread has an autonomous set of connections to resource managers and each thread maintains a private resource manager table. This private resource manager table must be allocated for each new thread and de-allocated when the thread terminates, even if the termination is abnormal.

  Note: In an Oracle system, once a thread has been started and establishes a connection, only that thread can use that connection. No other thread can make a call on that connection.

  ### The Open String Specification

  The `xa_open` string parameter, `xa_info`, provides the clause, Threads=, which must be specified as true to enable the use of threads by the transaction monitor. The default is false. Note that, in most cases, threads will be created by the transaction monitor and that the application will not know when a new thread is created. Therefore, it is advisable to allocate a service context (`lda` in Version 7) on the stack within each service that is written for a transaction monitor application. Before doing any Oracle-related calls in that service, the `xaoSvcCtx` (`sqlld2` for Version 7 OCI) function must be called and the service context initialized. This LDA can then be used for all OCI calls within that service.

  ### Restrictions

  The following restrictions apply when using threads:

  - Any Pro* or OCI code that executes as part of the application server process on the transaction monitor cannot be threaded unless the transaction monitor is explicitly told when each new application thread is started. This is typically accomplished by using a special C compiler provided by the transaction monitor vendor.
  - The Pro* statements, `EXEC` `SQL` `ALLOCATE` and `EXEC` `SQL` `USE` are not supported. Therefore, when threading is enabled, embedded SQL statements cannot be used across non-XA connections.

  ## Troubleshooting

  This section discusses how to find information in case of problems or system failure. It also discusses trace files and recovery of pending transactions.

  ### Trace Files

  The Oracle XA library logs any error and tracing information to its trace file. This information is useful in supplementing the XA error codes. For example, it can indicate whether an `xa_open` failure is caused by an incorrect open string, failure to find the Oracle Server instance, or a logon authorization failure.

  The name of the trace file is:

  `xa`_db_namedate.`trc`

  where db_name is the database name you specified in the open string field `DB`=db_name, and date is the date when the information is logged to the trace file.

  If you do not specify `DB`=db_name in the open string, it automatically defaults to the name "NULL".

  #### The xa_open string DbgFl

  Normally, the XA trace file is opened only if an error is detected. The `xa_open` string DbgFl provides a tracing facility to record additional detail about the XA library. By default, its value is zero. It can be set to any combination of the following values. Note that they are independent, so to get printout from two or more flags, each must be set.

  - `0x1` Trace the entry and exit to each procedure in the XA interface. This can be useful in seeing exactly what XA calls the TP Monitor is making and what transaction identifier it is generating.
  - `0x2` Trace the entry to and exit from other non-public XA library routines. This is generally of use only to Oracle developers.
  - `0x4` Trace various other "interesting" calls made by the XA library, such as specific calls to the Oracle Call Interface. This is generally of use only to Oracle developers.

  #### Trace File Locations

  The trace file can be placed in one of the following locations:

  - The trace file can be created in the `LogDir` directory as specified in the open string.
  - If you do not specify `LogDir` in the open string, then the Oracle XA application attempts to create the trace file in the `$ORACLE_HOME/rdbms/log` directory, if it can determine where `$ORACLE_HOME` is located.
  - If the Oracle XA application cannot determine where `$ORACLE_HOME` is located, then the trace file is created in the current working directory.

  ### Trace File Examples

  Examples of two types of trace files are discussed below:

  The example, xa_NULL040292.trc, shows a trace file that was created on April 2, 1992. Its `DB` field was not specified in the open string when the resource manager was opened.

  The example, xa_Finance121591.trc, shows a trace file was created on December 15, 1991. Its `DB` field was specified as "Finance" in the open string when the resource manager was opened.

  Note: multiple Oracle XA library resource managers with the same `DB` field and `LogDir` field in their open strings log all trace information that occurs on the same day to the same trace file.

  Each entry in the trace file contains information that looks like this:

      1032.12345.2:  ORA-01017:  invalid username/password;  logon denied
      1032.12345.2:  xaolgn:  XAER_INVAL;  logon denied

  where "1032" is the time when the information is logged, "12345" is the process ID (PID), "2" is the resource manager ID, `xaolgn` is the module name, `XAER_INVAL` was the error returned as specified in the XA standard, and `ORA`-`1017` is the Oracle Server information that was returned.

  ### In-doubt or Pending Transactions

  In-doubt or pending transactions are transactions that have been prepared, but not yet committed to the database.

  Generally, the transaction manager provided by the TPM system should resolve any failure and recovery of in-doubt or pending transactions. However, the DBA may have to override an in-doubt transaction in certain circumstances, such as when the in-doubt transaction is:

  - locking data that is required by other transactions
  - not resolved in a reasonable amount of time

  For more information about overriding in-doubt transactions in the circumstances described above, or about how to decide whether the in-doubt transaction should be committed or rolled back, see the TPM documentation.

  ### Oracle Server SYS Account Tables

  There are four tables under the Oracle Server `SYS` account that contain transactions generated by regular Oracle Server applications and Oracle XA applications. They are `DBA_PENDING_TRANSACTIONS`, `V$GLOBAL_TRANSACTIONS,` `DBA_2PC_PENDING` and `DBA_2PC_NEIGHBORS`

  For transactions generated by Oracle XA applications, the following column information applies specifically to the `DBA_2PC_NEIGHBORS` table.

  - the `DBID` column is always `xa_orcl`
  - the `DBUSER_OWNER` column is always db_name `xa.oracle.com`

  Remember that the db_name is always specified as `DB`=db_name in the open string. If you do not specify this field in the open string, then the value of this column is `NULLxa.oracle.com` for transactions generated by Oracle XA applications.

  For example, you could use the SQL statement below to obtain more information about in-doubt transactions generated by Oracle XA applications.

      SELECT * FROM DBA_2PC_PENDING p, DBA_2PC_NEIGHBORS n
        WHERE p.LOCAL_TRAN_ID = n.LOCAL_TRAN_ID
          AND
          n.DBID = 'xa_orcl';

  Alternatively, if you know the format `ID` used by the transaction processing monitor, you can use `DBA_PENDING_TRANSACTIONS` or `V$GLOBAL_TRANSACTIONS`. While `DBA_PENDING_TRANSACTIONS` gives a list of both active and failed prepared transactions, `V$GLOBAL_TRANSACTIONS` gives a list of all active global transactions.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index