Oracle7 Server Distributed Systems Manual, Vol. 1 Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Application Development


This chapter describes the special considerations that are necessary if you are designing an application to run in a distributed system. Oracle7 Server Concepts describes how Oracle eliminates much of the need to design applications specifically to work in a distributed environment. The topics covered include:

The Oracle7 Server Application Developer's Guide provides a complete discussion of implementing Oracle7 applications. This chapter provides information specific to development for an Oracle7 distributed environment. Basic replication and the advanced replication option are discussed in Oracle7 Server Distributed Systems, Volume II. See the Trusted Oracle7 Server Administrator's Guide for additional information about designing an application to run in a multilevel distributed system.


Development Tools

Although applications can be created using 3GL, 4GL, or SQL code, Oracle's Cooperative Development Environment (CDE) tool set provides a unified system for developing Oracle7 distributed applications.

Case Tools

Oracle's primary CDE tools are:

Oracle's CDE tools assist you in all phases of application development from design through implementation. It also simplifies the task of porting existing applications to different operating systems and platforms

Additional Information: See the CASE tool set documentation for full explanations of how to use CASE*Designer, CASE*Dictionary, and CASE*Generator to develop your applications.


Factors Affecting the Distribution of an Application's Data

In a distributed database environment, you should coordinate with the database administrator to determine the best location for the data. Some issues to consider are:


Naming Objects

You should decide when you want to use partial and complete global object names in the definition of views, synonyms, and procedures. Keep in mind that database names should be stable and databases should not be unnecessarily moved within a network.

In a distributed system, each database must have a unique global name. The global name is composed of the database name and the network domain that contains the database. Each object in the database then has a global object name consisting of the object name and the global database name. Because Oracle ensures that the object name is unique within a database, you can ensure that it is unique across all databases by assigning unique global database names. You should coordinate with your database administrator on this task, as it is usually the DBA who is responsible for assigning database names. See "Global Naming Issues," [*], for more information on object naming.


Controlling Connections Established by Database Links

When a global object name is referenced in a SQL statement or remote procedure call, database links establish a connection to a session in the remote database on behalf of the local user. The remote connection and session are only created if the connection has not already been established previously for the local user session.

The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application (or user) explicitly terminates them. Terminating remote connections established using database links is useful for disconnecting high cost connections (such as long distance phone connections) that are no longer required by the application.

The application developer or user can close (terminate) a remote connection and session using the ALTER SESSION command with the CLOSE DATABASE LINK parameter. For example, assume you issue the following query:

SELECT * FROM emp@sales;
COMMIT;

The following statement terminates the session in the remote database pointed to by the SALES database link:

ALTER SESSION CLOSE DATABASE LINK sales;

If partial global database names are specified, the local Oracle expands the name using the network domain of the local database.

Note: Before closing a database link, you must first close all cursors that use the link and then end your current transaction if it uses the link.

To close a database link connection in your user session, you must have the ALTER SESSION system privilege.


Referential Integrity in a Distributed System

Oracle does not permit declarative referential integrity constraints to be defined across nodes of a distributed system (that is, a declarative referential integrity constraint on one table cannot specify a foreign key that references a primary or unique key of a remote table). However, parent/child table relationships across nodes can be maintained using triggers. For more information about triggers that enforce referential integrity, see Chapter 7 of Oracle7 Server Concepts.

Note: If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can limit the accessibility of not only the parent table, but also the child table.

For example, assume that the child table is in the SALES database and the parent table is in the HQ database. If the network connection between the two databases fails, some DML statements against the child table (those that insert rows into the child table or update a foreign key value in the child table) cannot proceed because the referential integrity triggers must have access to the parent table in the HQ database.


Distributed Queries

You can use a trigger or stored procedure to create a distributed query. This distributed query is decomposed by the local Oracle into a corresponding number of remote queries, which are sent to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.

If a portion of a distributed statement fails, for example, due to an integrity constraint violation, Oracle returns error number ORA-02055. Subsequent statements or procedure calls return error number ORA-02067 until a rollback or rollback to savepoint is issued.

You should design your application to check for any returned error messages that indicate that a portion of the distributed update has failed. If you detect a failure, you should rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.


Handling Errors in Remote Procedures

When a procedure is executed locally or at a remote location, four types of exceptions can occur:

When using local procedures, all of these messages can be trapped by writing an exception handler, such as shown in the following example:

EXCEPTION
   WHEN ZERO_DIVIDE THEN
   /* ...handle the exception */

Notice that the WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following example:

...
null_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(null_salary, -20101);
...
RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
...
WHEN null_salary THEN
...

When calling a remote procedure, exceptions are also handled by creating a local exception handler. The remote procedure must return an error number to the local, calling procedure, which then handles the exception as shown in the previous example. Because PL/SQL user-defined exceptions always return ORA-06510 to the local procedure, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.


XA Library-Related Information

General Information about the Oracle XA Library

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

Additional Information: For information on library linking filenames, see your Oracle operating system- specific documentation.

README.doc

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

Basic Architecture

The Oracle XA library is an external interface that allows global transactions to be coordinated by a transaction manager other than the Oracle7 Server. This allows inclusion of non-Oracle7 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.

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 7 - 1 illustrates a possible X/Open DTP model.

Figure 7 - 1. One Possible DTP Model

A resource manager (RM) controls a shared, recoverable resource that can be returned to a consistent state after a failure. For example, Oracle7 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, Oracle7 Server acts as its own TM and manages its own commit and recovery. However, using a standards-based TM allows Oracle7 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 Oracle7 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.

Note: The naming conventions for your 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 on terminology, see the documentation supplied with your 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 that a TM can issue a commit for an RM only if all RMs have replied affirmatively to phase one.

Compatibility Issues

The XA library supplied with release 7.3 can be used only against a release 7.3 server. You must use the release 7.2 XA library against a 7.2 server.

Oracle XA Library Interface Subroutines

The Oracle XA library subroutines allow a TM to instruct Oracle7 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:

XA Library Subroutines

The following XA Library subroutines are available:

xa_open

connect to the resource manager

xa_close

disconnect from the resource manager

xa_start

start a new transaction and associate it with the given transaction ID (XID), or associate the process with an existing transaction

xa_end

disassociate the process from the given XID

xa_rollback

roll back the transaction associated with the given XID

xa_prepare

prepare the transaction associated with the given XID. This is the first phase of the two-phase commit protocol.

xa_commit

commit the transaction associated with the given XID. This is the second phase of the two-phase commit protocol.

xa_recover

retrieve a list of prepared, heuristically committed or heuristically rolled back transactions

xa_forget

forget the heuristic transaction associated with the given XID

xa_complete

wait for completion of an asynchronous operation

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

Transaction Processing Monitors (TPMs)

Under UNIX, for example, 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, a TPM 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). It communicates through the Oracle XA library interface.

Required Public Information

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

xa_switch_t structure

The Oracle Server xa_switch_t structure name is xaosw. This structure contains 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 may be null.

open string

The format of the open string used by xa_open () is described in detail in the section titled "Developing and Installing Applications that use the XA Libraries" [*].

libraries

Libraries needed to link applications using Oracle XA have operating system-specific names. In general, it is similar to linking an ordinary precompiler or OCI program except with one additional library ($ORACLE_HOME/lib/libxa.a), and any TPM-specific libraries.

See your Oracle operating system-specific documentation and the Oracle XA README.doc file to find the correct library name.

Use of the Oracle XA library restricts the use of SQL to connect to and disconnect from the database and to perform transaction operations. These restrictions are described in "Interfacing to Precompilers and OCIs" [*].

requirements

You must have purchased and installed the distributed database option.

Additional XA Issues

Note the following additional information about Oracle XA issues:

DDL statements

Oracle applications in global transactions may not perform DDL statements (like CREATE TABLE, DROP TABLE, CREATE INDEX) because they force a commit of pending work.

global and local transactions

When an application uses the XA library to connect to the Oracle Server, that application can only operate on global transactions. The Oracle Server does not allow local transactions when it is using XA.

transaction branches

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

read-only

Oracle Server supports the read-only optimization.

association migration

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

dynamic registration

The optional XA feature dynamic registration is not supported.

asynchronous calls

The optional XA feature asynchronous XA calls is not supported.


Developing and Installing Applications that Use the XA Libraries

This section discusses developing and installing Oracle7 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:

Additional Information: See your Oracle operating system-specific documentation for the location of the XAVIEW.SQL script.

Responsibilities of the Application Developer

The application developer's responsibilities are:


Defining the 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. The syntax of the open string is summarized as follows; later sections provide more detail on required and optional fields.

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

GPwd=P/group_password

LogDir=log_dir

MaxCur=maximum_#_of_open_cursors

SqlNet=connect_string

Note the following:

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 "Managing Users and Resources," in Chapter 12 of the

Oracle7 Server 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 V$XATRANS$ 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 deleted 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.

OCI programs need to call the sqlld2() function to obtain the correct connection. See "Using OCIs with the Oracle XA Library," [*].

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 SQL*Net clause in the open string. The SQL*Net 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.

For more information about precompilers (specifically Pro*C/C++), see "Interfacing to Precompilers and OCIs" [*].

GPwd=P/group_password

GPwd

Specifies the server security group password.

P

Indicates that an explicit server security group password is currently provided.

group_password

Indicates the actual server security group password.

Server security groups provide an optional extra layer of protection between different applications running against the same Oracle Server instance. If no server security group option is specified, then the application using this open string will be part of an Oracle Server-defined server security group.

A transaction must be executed wholly within a server security group. For example, if a debit application specifies a different Oracle Server security group than a credit application, then the two may not be used in the same transaction.

For example, GPwd=P/banking indicates that the current server group password is "banking".

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, otherwise useful trace files may be lost without any error indication.

MaxCur=maximum_#_of_open_cursors

MaxCur

Specifies the number of cursors to be alocated 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.

For more information on maxopencursors, see Chapter 6, Running the Oracle Precompilers," in the Programmer's Guide to the Oracle Precompilers.

SqlNet=db_link

SqlNet

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

See your SQL*Net documentation for information about SQL*Net database links.

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 SQL*Net string without actually accessing a remote database, use the Pipe driver.

For example:

SqlNet=localsid1

where:

localsid1

is an alias defined in the SQL*net tnsnames.ora file.

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


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. Also, you must use the release_cursor=yes option when compiling your precompiler application.

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

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 as defined by sqllib, 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+GPwD=P/mailgrp
	+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, her department number (DEPTNO) in another, and her 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
	+GPwd+P/pay+SesTM=10+LogDir=/usr/local/xalog
ORACLE_XA+DB=PAYROLL+SqlNet=SID2+ACC=P/scott/tiger
	+GPwd=P/mgr+SesTM=10+LogDir=/usr/local/xalog
ORACLE_XA+SqlNet=hqemp@SID3+ACC=P/scott/tiger+GPwd=P/mgr
	+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 version 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...

Additional Information: For more information on concurrent logons, see the Programmer's Guide to the Oracle Precompilers.

Note: Applications using XA should not create Oracle Server database connections of their own. Any work performed by them would be outside the global transaction, and may confuse the connection information used by the Oracle XA library.

Using OCI with the Oracle XA Library

OCI applications that use the Oracle XA library should not call olon() or orlon() to log on to the resource manager. Rather, the logon should be done through the TPM. The applications can execute the function sqlld2() to obtain the lda 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 sqlld2() with the correct arguments to obtain the correct lda structure.

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.

Additional Information: For more information about using the sqlld2() function, see the Programmer's Guide to the Oracle Call Interface.


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 *other 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:

The following table lists the TPM functions that replace regular Oracle commands when migrating precompiler or OCI applications to TPM applications.

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)
SAVEPOINT savepoint illegal
SET TRANSACTION READ ONLY illegal
Table 7 - 1. 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 deallocated when the thread terminates, even if the termination is abnormal.

Note that, 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 to 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.

OCI Clients

The open string parameter, Threads, must be specified as yes and the client must written to allocate new logon area (LDA). 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 an LDA on the stack within each service that is written for a transaction monitor application. Before doing any Oracle-related calls in that service, the sqlld2 function must be called and the LDA initialized. This LDA can then be used for all OCI calls within that service.

Restrictions

The following restrictions apply when you are using threads:


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 login 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 trace file can be placed in one of the following locations:

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 that 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:  xa_switch rtn ORA-22

where "1032" is the time when the information is logged, "12345" is the process ID (PID), "2" is the resource manager ID, xa_switch is the module name, and ORA-22 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:

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 your TPM documentation.

Additional Information: For more information on pending transactions, see Chapter 5.

Oracle Server SYS Account tables

There are two tables under the Oracle Server SYS account that contain transactions generated by regular Oracle Server applications and Oracle XA applications. They are 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.

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';


Restrictions

General Restrictions

General restrictions for the Oracle XA library are listed in this section.

Database Links

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

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 linked 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, and when you complete a service or RPC, you will receive an ORA#23 message.

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.

Oracle Parallel Server Option

On some platforms, you cannot use the Oracle XA library together with the Oracle Parallel Server option. To run the Oracle XA library with the Parallel Server option, the platform's implementation of the distributed lock manager must support transaction-based rather than process-based deadlock detection.

Additional Information: For more information about the Parallel Server and the Oracle XA library, see your Oracle operating system-specific documentation.

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 transaction, the application should not contain any Oracle Server-specific statement that independently rolls back or commits a transaction.

Do not use EXEC SQL ROLLBACK WORK for precompiler applications. Similarly, an OCI application should not execute orol(). You can roll back a transaction by the initiator by calling tx_rollback().

Similarly, a precompiler application should not have the EXEC SQL COMMIT WORK statement. An OCI application should not execute ocom(). Instead, use tx_commit() or tx_rollback() to end a 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.

Savepoint

Do not use savepoint. For example, do not use:

EXEC SQL SAVEPOINT savepointname.

SET TRANSACTION

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

Connecting or Disconnecting with EXECSQL

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.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index