18
Oracle XA

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.

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.

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.

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...

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.

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()/orol() (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

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.

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.

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_namexa.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.