17
Java Transaction API

JDBC is used to connect to database resources. However, to include all changes to multiple databases within a transaction, you must use the JDBC connections within a JTA global transaction, which is described fully in this chapter. The process of including database SQL updates within a transaction is referred to as enlisting a database resource.

This chapter assumes that you have a working knowledge of JTA. The discussion focuses mostly on examples and explaining the differences between Sun Microsystems's JTA specification and the Oracle JTA implementation. See http://www.javasoft.com for the Sun Microsystems JTA specification.

• Transaction Overview

• JTA Summary

• Binding Transactional Objects in the Namespace

• JTA Client-Side Demarcation

• JTA Server-Side Database Enlistment

• Configuring Two-Phase Commit Engine

• Creating DataSource Objects Dynamically

• JDBC Restrictions

Transaction Overview

Transactions manage changes to multiple databases within a single application as a unit of work. That is, if you have an application that manages data within one or more databases, you can ensure that all changes in all databases are committed at the same time if they are managed within a transaction.

Transactions are described in terms of ACID properties, which are as follows:

• Atomic: all changes to the database made in a transaction are rolled back if any change fails.

• Consistent: the effects of a transaction take the database from one consistent state to another consistent state.

• Isolated: the intermediate steps in a transaction are not visible to other users of the database.

• Durable: when a transaction is completed (committed or rolled back), its effects persist in the database.

The JTA implementation, specified by Sun Microsystems, relies heavily on the JDBC 2.0 specification and XA architecture. The result is a complex requirement on applications in order to ensure that the transaction is managed completely across all databases. Sun Microsystems's specifies Java Transaction API (JTA) 1.0.1 and JDBC 2.0 on http://www.javasoft.com.

You should be aware of the following when using JTA within the Oracle9i environment:

• Global and Local Transactions

• Demarcating Transactions

• Enlisting Resources

• Two-Phase Commit

• Enlisting Resources

• JTA Limitations

Global and Local Transactions

Whenever your application connected to a database using JDBC or a SQL server, you were creating a transaction. However, the transaction involved only the single database and all updates made to the database were committed at the end of these changes. This is referred to as a local transaction.

A global transaction involves a complicated set of management objects--objects that track all of the objects and databases involved in the transaction. These global transaction objects--TransactionManager and Transaction--track all objects and resources involved in the global transaction. At the end of the transaction, the TransactionManager and Transaction objects ensure that all database changes are atomically committed at the same time.

Within a global transaction, you cannot execute a local transaction. If you try, the following error will be thrown:

ORA-2089 "COMMIT is not allowed in a subordinate session."

Some SQL commands implicitly execute a local transaction. All SQL DDL statements, such as "CREATE TABLE", implicitly starts and commits a local transaction under the covers. If you are involved in a global transaction that has enlisted the database that the DDL statement is executing against, the global transaction will fail.

Demarcating Transactions

A transaction is said to be demarcated, which means that each transaction has a definite beginning and end point. For example, in an interactive tool such as SQL*Plus, a transaction is started with the SQL BEGIN statements. Then, each SQL DML statement is part of the transaction. A transaction ends when a SQL COMMIT or ROLLBACK statement is issued.

For JTA, transactions are demarcated programmatically through the UserTransaction object, which must be bound within the namespace. The client or server starts the transaction by invoking the UserTransaction.begin method; it ends the transaction by invoking either the UserTransaction.commit or UserTransaction.rollback methods.

The originating client or object that starts the transaction must also end the transaction with a commit or rollback. If the client begins the transaction, calls out to a server object, the client must end the transaction after the invoked method returns. The invoked server object cannot end the transaction.

UserTransaction Interface

The following are the methods that you can use for transaction demarcation. These methods are defined within the javax.transaction.UserTransaction interface:

public abstract void begin() throws NotSupported, SystemException;

Creates a new transaction and associates the transaction with the thread.

Exceptions:

• NotSupportedException: Thrown if the thread is already involved with a transaction. Nested transactions are not supported.

• SystemException: Thrown if an unexpected error condition occurs.

  public abstract void commit() throws RollbackException, HeuristicMixedException,
    HeuristicRollbackException, SecurityException, IllegalStateException,
    SystemException; 
  

Completes the existing transaction by saving all changes to resources involved in the transaction. The thread is disassociated from this transaction when this method finishes.

Exceptions:

• RollbackException: Thrown if any resource within the transaction could not commit successfully. All resource changes are rolled back.

• HeuristicMixedException: Thrown to indicate that some of the resources were committed; some were rolled back.

• HeuristicRollbackException: Thrown to indicate that some updates to resources involved in the transaction were rolled back.

• SecurityException: Thrown when the thread is not allowed to commit the transaction based on a security violation.

• IllegalStateException: Thrown if the current thread has not been associated with a transaction. This occurs if you try to commit a transaction that was never started.

• SystemException: Thrown if an unexpected error condition occurs.

  public abstract void rollback() throws IllegalStateException, SecurityException,
    SystemException;
  
  

Roll back the transaction associated with the current thread.

Exceptions:

• SecurityException: Thrown when the thread is not allowed to roll back the transaction based on a security violation.

• IllegalStateException: Thrown if the current thread has not been associated with a transaction. This occurs if you try to roll back a transaction that was never started.

• SystemException: Thrown if an unexpected error condition occurs.

  public abstract int getStatus() throws SystemException;
  
  

Retrieve the transaction status associated with the current thread.

Exceptions:

• SystemException: Thrown if an unexpected error condition occurs.

  public abstract void setRollbackOnly() throws IllegalStateException,
    SystemException;
  
  

Modify the transaction associated with the current thread so that the outcome results in a rollback.

Exceptions:

• IllegalStateException: Thrown if the current thread has not been associated with a transaction. This occurs if you try to set for a roll back a transaction that was never started.

• SystemException: Thrown if an unexpected error condition occurs.

  public abstract setTransactionTimeout(int seconds) throws SystemException;
  
  

Set the timeout value in seconds for the transaction associated with this current thread.

Exceptions:

• SystemException: Thrown if an unexpected error condition occurs.

Enlisting Resources

Each resource, such as an Oracle database, that you want managed in the global transaction must be enlisted in that transaction. The transaction manager tracks all resources involved in the global transaction.

While there are several methods for retrieving a JDBC connection to a database, only one of these methods causes the database to be included in a JTA transaction. The following table lists the normal methods for retrieving JDBC connections:

Table 17-1 JDBC Methods

Retrieval Method	Description
OracleDriver(). defaultConnection()	Pre-JDBC 2.0 method for retrieving the local connection. Use only within local transactions.
DriverManager.getConnection ("jdbc:oracle:kprb:")	Pre-JDBC 2.0 method for retrieving the local connection. Use only within local transactions.
DataSource.getConnection ("jdbc:oracle:kprb:")	JDBC 2.0 method for retrieving connections to the local databases. Can be used for JTA transactions.

Of these methods, only the DataSource object can be used to include a database resource in the global transaction. You must bind any DataSource object to be used within a JTA transaction with the -jta option of the bindds command. This binds an Oracle-specific JTA DataSource within the namespace. You cannot use any other type of DataSource for global transactions.

In order to ensure that the statements are included within a global transaction, you must do the following:

1. Bind a JTA DataSource object (OracleJTADataSource) in the JNDI namespace. There are several types of DataSource objects that you can bind. You must bind the JTA type in order for this database to be included in the global transaction. You must retrieve the connection within the context of a global transaction; that is, retrieve the connection after the begin method of the UserTransaction object.

2. The method must retrieve the DataSource object from the JNDI namespace after the global transaction has started.

3. Retrieve the connection object from this DataSource object using the getConnection method.

An object--client or server object--opens a JDBC connection to a remote database through the DataSource object within the context of a global transaction. The client can open connections to remote databases through any client JDBC driver. Server objects can only open connections to databases through either a JDBC KPRB driver or a JDBC server-side Thin driver.

If your transaction involves more than one database, you must specify an Oracle9i database as the two-phase commit engine. See "Configuring Two-Phase Commit Engine" for more information.

Note: At this time, the Oracle JTA implementation does not support including non-Oracle databases in a global transaction.

Two-Phase Commit

One of the primary advantages for a global transaction is the number database resources managed as a single unit within the transaction. If your global transaction involves more than one database resource, you must specify a two-phase commit engine, which is an Oracle9i database designated to manage the changes to all databases within the transaction. The two-phase commit engine is responsible for ensuring that when the transaction ends, all changes to all databases are either totally committed or fully rolled back.

On the other hand, if your global transaction has multiple server objects, but only a single database resource, you do not need to specify a two-phase commit engine. The two-phase commit engine is required only to synchronize the changes for multiple databases. If you have only a single database, single-phase commit can be performed by the transaction manager.

Note: Your two-phase commit engine can be any Oracle9i database. It can be the database where your server object exists, or even a database that is not involved in the transaction at all. See "Configuring Two-Phase Commit Engine" for a full explanation of the two-phase commit engine setup.

Figure 17-1 shows two databases enlisted in the transaction and another database that is designated as the two-phase commit engine. Databases A and B are enlisted when a JDBC connection is opened, within a global transaction, to each database. The two-phase commit engine must be identified within the UserTransaction object and database links from the two-phase commit engine to each database involved in the transaction must be created. See "Configuring Two-Phase Commit Engine" on how to perform this setup.

When the global transaction ends, the two-phase commit engine ensures that all changes made to the databases A and B are committed or rolled back simultaneously.

Figure 17-1 Two-Phase Commit for Global Transactions

Text description of the illustration jta_jdbc.gif

JTA Limitations

The following are the portions of the JTA specification that Oracle9i does not support.

Nested Transactions

Nested transactions are not supported in this release. If you attempt to begin a new transaction before committing or rolling back any existing transaction, the transaction service throws a SubtransactionsUnavailable exception.

Interoperability

The transaction services supplied with this release do not interoperate with other JTA implementations.

Timeouts

Oracle only supports a database resource idle timeout through the UserTransaction object. Thus, Oracle does not recommend that you use timeouts.

JTA Summary

The following sections summarize the details for demarcating the transaction and enlisting the database in the transaction. These details are explained and demonstrated in the rest of the chapter. However, these tables provide a reference point for you.

Environment Initialization

Before you can retrieve the UserTransaction or DataSource bound objects from the JNDI namespace, you must provide the following before the JNDI lookup:

• authentication information, such as username and password

• namespace URL

  Table 17-2 Environment Setup For Transactional Object Retrieval

  Source	Qualifiers	Environment Setup
  		Setup includes authentication information, and namespace URL
  Client	Retrieves a remote object or a remote database connection.	Must always provide the environment setup before retrieving the UserTransaction from a remote JNDI provider. All JNDI providers are remote from a true client.
  Server	Can use in-session activation to retrieve a local object or local database connection.	If the JNDI provider is within the same database as the object, it can use in-session lookup. Since the server uses its own session for the lookup, no setup is required.
  	Retrieves a remote object or a remote database connection.	The JNDI provider is remote, so the server object must always provide the environment setup.

Methods for Enlisting Database Resources

The DataSource object is used to explicitly enlist the database in the JTA transaction. In order for the database to be correctly enlisted, the DataSource must be bound correctly, and the retrieval mechanism can be one of three methods. These are discussed below:

Table 17-3 JDBC 2.0 DataSource Overview  

	JDBC 2.0 DataSource
Binding	You must bind a JTA DataSource into the namespace with the bindds command. The bindds command must contain the -dstype jta option.
Retrieving DataSource object from remote JNDI provider	Provide the environment Hashtable, which contains authentication information and namespace URL. Retrieve the DataSource object through a JNDI lookup that contains the "jdbc_access://" prefix.
Retrieving DataSource object from local JNDI provider	Retrieve the DataSource object using in-session activation. Environment setup and "jdbc_access://" prefix is not required.

Summary of Single-Phase and Two-Phase Commit

Table 17-4 summarizes the single-phase commit scenario. It covers the JNDI binding requirements and the application implementation runtime requirements.

Table 17-4 Single-Phase Commit

Aspect	Description
Binding	No binding required for UserTransaction. The UserTransaction object is created for you. Bind a DataSource object using the bindds command.
Runtime	Retrieve the UserTransaction either through a JNDI lookup. Your runtime is responsible for starting and terminating the transaction. If using the DataSource object to manage SQL DML statements within the transaction, retrieve the DataSource.

Table 17-5 summarizes the two-phase commit scenario.

Table 17-5 Two-Phase Commit Requirements  

Aspect	Requirements
Binding UserTransaction One of two scenarios:	Scenario one is where you bind the UserTransaction WITH a username and password that is to be used to complete all global transactions started from this UserTransaction. You bind a UserTransaction object with the fully-qualified database address of the two-phase commit engine and its username and password. You bind DataSource objects for each database involved in the transaction with a fully-qualified public database link from the two-phase commit engine to itself.
	Scenario two is where you bind the UserTransaction WITHOUT a username and password. Thus, the username that is used when retrieving the UserTransaction is the user that completes the transaction. You bind a UserTransaction object with the fully-qualified database address of the two-phase commit engine. You bind DataSource objects for each database involved in the transaction with a fully-qualified public database link from the two-phase commit engine to itself.
Binding DataSource	You must bind a JTA DataSource for each database involved in the transaction. You must create public database links, as discussed in the System Administration section.
System Administration	The user that completes the transaction (as described in the binding section) must have the privilege to commit the transaction on all included databases. There are one of two methods for ensuring that the user can complete the transaction. - If the username is not bound with the UserTransaction object, the user that retrieves the UserTransaction both starts and stops the transaction. Thus, this user must be created on all involved database in order to be able to open a session to all databases. - If the username is bound with the UserTransaction object is different than the user that retrieves the UserTransaction object, the username bound with the UserTransaction object must be given explicit privilege to complete a transaction it did not start. Thus, make sure that this user exists on each database in order to open sessions to all databases and grant it the "CONNECT, REMOVE, CREATE SESSION, and FORCE ANY TRANSACTION" privileges on each database. Create public database links from the two-phase commit engine to each database involved.
Runtime	Runtime requirements are the same as indicated in the single-phase commit table.

Binding Transactional Objects in the Namespace

For most global transactions, you will need to bind at least one of the following objects in the namespace:

• UserTransaction object

• DataSource object

Bind UserTransaction Object in the Namespace

The bindut command binds a UserTransaction object in the namespace. This object is used for demarcation of global transactions.

You must bind a UserTransaction object for both single and two-phase commit transactions through the bindut command of the sess_sh tool.

The options used to bind a UserTransaction object depend on whether the transaction uses a single or two-phase commit, as described below:

Single-Phase Commit Binding for UserTransaction

Single-phase commit requires the JNDI bound name for the UserTransaction object. You do not need to provide the address to a two-phase commit engine. For example, the following binds a UserTransaction with the name of "/test/myUT" that exists for a single-phase commit transaction:

bindut /test/myUT

To bind a UserTransaction object to the name "/test/myUT" in the namespace located on nsHost through the sess_sh command, execute the following:

sess_sh -service jdbc:oracle:thin:@nsHost:5521:ORCL -user SCOTT -password TIGER
& bindut /test/myUT

Two-Phase Commit Binding for UserTransaction

Two-phase commit binding requires the JNDI bound name for the UserTransaction object and the address to a two-phase commit engine. You provide a URL for the two-phase commit engine in the bindut command, which can be either a JDBC URL or a sess_iiop URL.

Note: The client needs the same information to retrieve the UserTransaction as you give within the bindut command.

In addition, you can bind a username and password with the UserTransaction object.

• If you do not bind a username and password with the UserTransaction, the user that retrieved the UserTransaction will be the same user that is used to perform the commit or rollback for the two-phase commit on all involved databases.

• If you bind a username and password with the UserTransaction, then this is the username that the two-phase commit will be committed or rolled back with on all involved databases. The transaction will be started by the user that retrieves the UserTransaction object; it will be completed by the user bound with the UserTransaction object.

The username that is used to commit or rollback the two-phase commit transaction must be created on the two-phase commit engine and on each database involved in the transaction. It needs to be created so that it can open a session from the two-phase commit engine to each of the involved databases using database links. Secondly, it must be granted the CONNECT, RESOURCE, CREATE SESSION privileges to be able to connect to each of these databases. For example, if the user that is needed for completing the transaction is SCOTT, you would do the following on the two-phase commit engine and each database involved in the transaction:

CONNECT SYSTEM/MANAGER;
CREATE USER SCOTT IDENTIFIED BY SCOTT;
GRANT CONNECT, RESOURCE, CREATE SESSION TO SCOTT;

Lastly, if you bound a username and password with the UserTransaction object, it will be using a different username to finalize the transaction than the username used to start the transaction. For this to be allowed, you must grant the FORCE ANY TRANSACTION privileges on each database involved in the transaction in order for two separate users to start and stop the transaction. If SCOTT is the username bound with the UserTransaction object, you would need to do the following in addition to the previous grant:

GRANT FORCE ANY TRANSACTION TO SCOTT;

The following binds a UserTransaction with the name of "/test/myUT" and a two-phase commit engine at "2pcHost" using a JDBC URL:

bindut /test/myUT -url jdbc:oracle:thin:@2pcHost:5521:ORCL

To bind the UserTransaction in the namespace designating the two-phase commit engine at dbsun.mycompany.com with a sess_iiop URL:

bindut /test/myUT -url sess_iiop://dbsun.mycompany.com:2481:ORCL

When the transaction commits, the UserTransaction communicates with the two-phase engine designated in the -url option to commit all changes to all included databases. In this example, the username and password were not bound with the UserTransaction object, so the user that retrieves the UserTransaction object from the JNDI namespace is used to start and stop the transaction. Thus, this user must exist on all involved databases and the two-phase commit engine. The UserTransaction tracks all databases involved in the transaction; the two-phase commit engine uses the database links for these databases to complete the transaction.

Note: If you change the two-phase commit engine, you must update all database links on all DataSource objects involved in the transaction, and rebind the UserTransaction.

Bind DataSource Object in the Namespace

The bindds command binds a DataSource object in the JNDI namespace. In order to enlist any database in a global transaction--including the local database--you must bind a JTA DataSource object to identify each database included in the transaction. There are multiple types of DataSource objects for use with certain scenarios. However, for use with JTA transactions, you must bind a JTA DataSource object, also known as an OracleJTADataSource object, to identify each database included in the transaction. See the bindds command of the sess_sh tool in the Oracle9i Java Tools Reference for a description of other DataSource object types.

Single-Phase Commit Scenario

In a single-phase commit scenario, the transaction only includes a single database in the transaction. Since no coordination for updates to multiple databases is needed, you do not need to specify a coordinator. Instead, you simply provide the JNDI bound name and the URL address information for this database within the OracleJTADataSource object. You do not need to provide a database link for a transaction coordinator.

Use the bindds command of the sess_sh tool to bind an DataSource object in the namespace. The full command is detailed in the Oracle9i Java Tools Reference. For example, the following binds an OracleJTADataSource with the name of "/test/empDS" that exists within a single-phase commit transaction with the bindds command:

bindds /test/empDS -url jdbc:oracle:thin:@empHost:5521:ORCL -dstype jta

After binding the DataSource object in the namespace, the server can enlist the database within a global transaction.

Two-Phase Commit Scenario

If multiple databases are to be included in the global transaction, you will need a two-phase commit engine, which is an Oracle9i database that is configured to be the transaction coordinator. Basically, the two-phase commit engine must have database links to each of the databases involved in the transaction. When the transaction ends, the transaction manager notifies the two-phase commit engine to either coordinate the commit of all changes to all involved databases or coordinate a roll back of these same changes.

In order to facilitate this coordination, you must configure the following:

1. Your system administrator must create fully-qualified public database links from the two-phase commit engine (Oracle9i database) to each database involved in the transaction. These database link names must be included when binding the OracleJTADataSource object.

2. Bind a JTA DataSource (OracleJTADataSource) object for each database in the transaction. You must include the following in the bindds command:
   1. The JNDI bound name for the object

   2. The URL for creating a connection to the database

   3. The fully-qualified public database link from the two-phase commit engine to this database

      Note: In a two-phase commit scenario, the DataSource object is bound, with respect to the two-phase commit engine. If you change the two-phase commit engine, you must update all database links, and rebind all concerned DataSource and UserTransaction objects.

The following example binds the empDS JTA DataSource into the namespace with 2pcToEmp as the database link name created on the two-phase commit engine:

% bindds /test/empDS -url jdbc:oracle:thin:@dbsun:5521:ORCL

	-dstype jta -dblink 2pcToEmp.oracle.com

JTA Client-Side Demarcation

For JTA, client-side demarcated transactions are programmatically demarcated through the UserTransaction interface (see "UserTransaction Interface"). A UserTransaction object must be bound with the bindut command into the namespace (see "Bind UserTransaction Object in the Namespace"). With client-side transaction demarcation, the client controls the transaction. The client starts a global transaction by invoking the UserTransaction begin method; it ends the transaction by invoking either the commit or rollback methods. In addition, the client must always set up an environment including a Hashtable with authentication information and namespace location URL.

To include a remote database within the transaction from a client, you must use a DataSource object, which has been bound in the namespace as a JTA DataSource. Then, invoke the getConnection method of the DataSource object after the transaction has started, and the database is included in the global transaction. See "Enlisting Resources" for more information.

The following must occur in the client runtime to demarcate the transaction:

1. Initialize a Hashtable environment with the namespace address and authentication information.

2. Retrieve the UserTransaction object from the namespace within the client logic. When you retrieve the UserTransaction object from the client, the URL must consist of "jdbc_access://" prefix before the JNDI name.

3. Start the global transaction within the client using UserTransaction.begin().

4. Enlist any database resources to be included in the transaction by opening a connection to the specified database, as follows:
   1. Retrieve the DataSource object from the namespace within the client logic. When you retrieve the DataSource object from any client, the URL must consist of "jdbc_access://" prefix before the JNDI name.

   2. Open a connection to the database through DataSource.getConnection method.

5. Retrieve the object reference.

6. Invoke any object methods to be included in the transaction.

7. Invoke SQL DML statements against any enlisted databases. SQL DDL creates a local transaction that will abort the global transaction. Thus, SQL DDL cannot be executed within a JTA transaction.

8. End the transaction through UserTransaction.commit() or UserTransaction.rollback().

Example 17-1 shows a client that invokes a server object and enlists a single database within the transaction.

Example 17-1 Employee Client Code for Client Demarcated Transaction

Before starting the client, you must first bind the UserTransaction and DataSource objects in the JNDI namespace. See "Bind UserTransaction Object in the Namespace" and "Bind DataSource Object in the Namespace" for directions on the binding these objects.

sess_sh -service jdbc:oracle:thin:@nsHost:5521:ORCL -user SCOTT -password TIGER
> bindut /test/myUT
> bindds /test/DataSource/empDB -url jdbc:oracle:thin:@empHost:5521:ORCL 

                               -dstype jta

Developing the Client Application

//Set up the service URL to where the UserTransaction object 
//is bound. Since from the client, the connection to the database
//where the namespace is located can be communicated with over either 
//a Thin or OCI JDBC driver. This example uses a Thin JDBC driver.
String namespaceURL = "jdbc:oracle:thin:@nsHost:1521:ORCL";

// lookup usertransaction object in the namespace
//1.(a) Authenticate to the database.
// create InitialContext and initialize for authenticating client
Hashtable env = new Hashtable ();
env.put (Context.URL_PKG_PREFIXES, "oracle.aurora.jndi");
env.put (Context.SECURITY_PRINCIPAL, "SCOTT");
env.put (Context.SECURITY_CREDENTIALS, "TIGER");
env.put (Context.SECURITY_AUTHENTICATION, ServiceCtx.NON_SSL_LOGIN);
//1.(b) Specify the location of the namespace where the transaction objects
//   are bound.
env.put(jdbc_accessURLContextFactory.CONNECTION_URL_PROP, namespaceURL);
Context ic = new InitialContext (env);

//2. Retrieve the UserTransaction object from JNDI namespace
ut = (UserTransaction)ic.lookup ("jdbc_access://test/myUT");

//3. Start the transaction
ut.begin();

//4.(a) Retrieve the DataSource (that was previously bound with bindds in
// the namespace. After retrieving the DataSource...
// get a connection to a database. You need to provide authentication info
// for a remote database lookup, similar to what you would do from a client.
// In addition, if this was a two-phase commit transaction, you must provide
// the username and password.
DataSource ds = (DataSource)ic.lookup ("jdbc_access://test/empDB");

//4.(b). Get connection to the database through DataSource.getConnection
// in this case, the database requires the same username and password as
// set in the environment.
Connection conn = ds.getConnection ("SCOTT", "TIGER");

//5. Retrieve the server object reference 
// lookup employee object in the namespace
Employee employee = (Employee)ic.lookup (sessiiopURL + objectName);

//6. Perform business logic.
...

//7. Close the database connection.
conn.close ();

//8. End the transaction
//Commit the updated value
ut.commit ();
}

JTA Server-Side Database Enlistment

If the database where the server is located also contains the namespace, you can do an in-session lookup. That is, you do not need to provide any environment setup. However, if the server is located on a separate machine than the namespace, you must initialize the environment exactly as specified in "JTA Client-Side Demarcation". This section assumes that the namespace and the server object exist on the same machine and demonstrates an in-session lookup of the UserTransaction and DataSource objects.

You do the following for a server to demarcate the transaction and enlist a database within the transaction:

1. The system administrator binds the required JTA objects in the namespace.
   1. Bind the UserTransaction object within the namespace.

   2. Bind the DataSource object within the namespace.

      Note: If using more than one database, you will need to setup for a two-phase commit. See "Configuring Two-Phase Commit Engine" for more information.

2. Retrieve the UserTransaction object from the namespace using in-session activate. No environment is required and no URL prefix is required. The lookup requires only the JNDI name of the bound object.

3. Start the global transaction within the client using UserTransaction.begin().

4. Enlist any database resources to be included in the transaction by opening a JDBC connection to the specified database, as follows:
   1. Retrieve the DataSource object from the namespace using in-session activate. No URL prefix is required. The lookup requires only the JNDI name of the bound object.

   2. Open a connection to the database through the DataSource.getConnection(). If the username and password that was used to authenticate to this server is the correct authentication information, provide no arguments. However, if the authentication requires a different username and password, provide these as input parameters to the getConnection() method call.

5. Invoke SQL DML statements against any enlisted databases.

6. Close any open database connections.

7. End the transaction through UserTransaction.commit() or UserTransaction.rollback().

Example 17-2 Enlist Database in Single Phase Transaction

The following example shows how a server object can enlist the local database in the transaction. The global transaction is initialized, the connection is retrieved through the DataSource.getConnection() method, and statements are executed against the local database. These statements are committed when the global transaction is committed.

Since the UserTransaction and DataSource objects are bound in the namespace local to this host, you can perform an in-session activation. This means that no environment information is necessary (unless performing a two-phase commit scenario) and the lookup uses only the JNDI name, without the "jdbc_access://" prefix.

//retrieve the initial context.
InitialContext ic = new InitialContext ();

// lookup the usertransaction 
UserTransaction ut = (UserTransaction)ic.lookup ("/test/myUT");

//start the transaction
ut.begin ();

// get a connection to the local database. If this was a two-phase commit 
// transaction, you would provide the username and password for the 2pc engine
DataSource ds = (DataSource)ic.lookup ("/test/empDS");

// get connection to the local database through DataSource.getConnection
Connection conn = ds.getConnection ("SCOTT", "TIGER");

//perform your SQL against the database.
//prepare and execute a sql statement. retrieve the employee's selected benefits
PreparedStatement ps =
    conn.prepareStatement ("update emp set ename = :(employee.name), 

	sal = :(employee.salary) where empno = :(employee.number)");
    .... //do work
 ps.close();
}

//close the connection
conn.close();

// commit the transaction
ut.commit ();

//return the employee information.
return new EmployeeInfo (name, empno, (float)salary);

Example 17-3 Using SQLJ with Explicit Enlistment

As in Example 17-2, you would retrieve the JTA DataSource from the JNDI provider, retrieve the connection, retrieve a context from that connection, and then provide the context on the SQLJ command-line.

//retrieve the initial context.
InitialContext ic = new InitialContext ();

// lookup the usertransaction 
UserTransaction ut = (UserTransaction)ic.lookup ("/test/myUT");

//start the transaction
ut.begin ();

// get a connection to the local database. If this was a two-phase commit 
// transaction, you would provide the username and password for the 2pc engine
DataSource ds = (DataSource)ic.lookup ("/test/empDS");

// get connection to the local database through DataSource.getConnection
Connection conn = ds.getConnection ("SCOTT", "TIGER");

//setup the context for issuing SQLJ against the database
DefaultContext defCtx = new DefaultContext (conn);

//issue SQL DML statemetns against the database
#sql [defCtx] { update emp set ename = :(remoteEmployee.name), 
	sal = :(remoteEmployee.salary) 
	  where empno = :(remoteEmployee.number) };

//close the connection
conn.close();

// commit the transaction
ut.commit ();

//return the employee information.
return new EmployeeInfo (name, empno, (float)salary);

Configuring Two-Phase Commit Engine

When multiple databases are included in a global transaction, the changes to these resources must all be committed or rolled back at the same time. That is, when the transaction ends, the transaction manager contacts a coordinator--also known as a two-phase commit engine--to either commit or roll back all changes to all included databases. The two-phase commit engine is an Oracle9i database that is configured with the following:

• Fully-qualified database links from the itself to each of the databases involved in the transaction. When the transaction ends, the two-phase commit engine communicates with the included databases over their fully-qualified database links.

• A user that is designated to create sessions to each database involved and is given the responsibility of performing the commit or rollback. The user that does the communication must be created on all involved databases and be given the appropriate privileges.

In order to facilitate this coordination, you must configure the following:

1. Designate an Oracle9i database as the two-phase commit engine.

2. Configure fully-qualified public database links (using the CREATE DATABASE LINK command) from the two-phase commit engine to each database that may be involved in the global transaction. This is necessary for the two-phase commit engine to communicate with each database at the end of the transaction. These database link names must be included when binding the JTA DataSource (OracleJTADataSource) object.

3. Bind a JTA DataSource (OracleJTADataSource) object for each database in the transaction. You must include the following in the bindds command:
   1. The JNDI bound name for the object

   2. The URL for creating a connection to the database

   3. The fully-qualified database link from the two-phase commit engine to this database

      Provide the fully-qualified database link name in the -dblink option of bindds for each individual database when binding that database's DataSource into the namespace.

      bindds /test/empDS -url jdbc:oracle:thin:@empHost:5521:ORCL 
      
      	-dstype jta -dblink 2pcToEmp.oracle.com
      

      Note: In a two-phase commit scenario, the DataSource object is bound, with respect to the two-phase commit engine. If you change the two-phase commit engine, you must update all database links, and rebind all concerned DataSource and UserTransaction objects.

4. Create the user on the two-phase commit engine that facilitates the two-phase commit. This user will open sessions to each resource involved in the transaction and complete the transaction. To do this, the user must be created on each database and granted CONNECT, RESOURCE, and CREATE SESSION privileges. If the user that completes the transaction is different from the user that starts the transaction, you also need to grant the FORCE ANY TRANSACTION privilege. These privileges must be granted on all databases included in the transaction.

   The decision on whether the FORCE ANY TRANSACTION privilege is needed is determined by whether you bound a username and password with the UserTransaction object.

   • If you do not bind a username and password with the UserTransaction, the user that retrieved the UserTransaction will be the same user that is used to perform the commit or rollback for the two-phase commit on all involved databases.

   • If you bind a username and password with the UserTransaction, then this is the username that the two-phase commit will be committed or rolled back with on all involved databases. The transaction will be started by the user that retrieves the UserTransaction object.

   Both types of users must be created, so that it can open a session from the two-phase commit engine to each of the involved databases. Secondly, it must be granted the CONNECT, RESOURCE, CREATE SESSION privileges to be able to connect to each of these databases. For example, if the user that is needed for completing the transaction is SCOTT, you would do the following on the two-phase commit engine and each database involved in the transaction:

   CONNECT SYSTEM/MANAGER;
   CREATE USER SCOTT IDENTIFIED BY SCOTT;
   GRANT CONNECT, RESOURCE, CREATE SESSION TO SCOTT;
   

   Lastly, if you bound a username and password with the UserTransaction object, it will be using a different username to finalize the transaction than the username used to start the transaction. For this to be allowed, you must grant the FORCE ANY TRANSACTION privileges on each database involved in the transaction in order for two separate users to start and stop the transaction.

   The advantage of binding a username with the UserTransaction is that it is treated as a global user is always committing all transactions started with this UserTransaction object. Thus, if you have more than one JTA transactions, you will only have to create one user and grant privileges to that user on all involved databases.

   For example, if SCOTT is the username bound with the UserTransaction object, you would need to do the following in addition to the previous grant:

   GRANT FORCE ANY TRANSACTION TO SCOTT;
   
   

5. Bind a UserTransaction into the namespace. You must provide the two-phase commit engine's fully-qualified database address. At this point, you should decide (based on the discussion in step 3) on whether to bind it with a username and password. The following assumes a global username is bound with the UserTransaction.

   bindut /test/myUT -url sess_iiop://dbsun.mycompany.com:2481:ORCL 
   
   	-user SCOTT -password TIGER
   
   

Example 17-4 Two-Phase Commit Example

The following example shows a server object that performs an in-session activation to retrieve both the UserTransaction and DataSource objects that have been bound locally. The UserTransaction was bound with the two-phase commit engine's URL, username, and password. The DataSource objects were all bound with the proper database links.

//with the environment set, create the initial context.
InitialContext ic = new InitialContext ();
UserTransaction ut = (UserTransaction)ic.lookup ("/test/myUT");

//With the same username and password for the 2pc engine,
// lookup the local datasource and a remote database.
DataSource localDS = (DataSource)ic.lookup ("/test/localDS");

//remote lookup requires environment setup
Hashtable env = new Hashtable ();
env.put (Context.URL_PKG_PREFIXES, "oracle.aurora.jndi");
env.put (Context.SECURITY_PRINCIPAL, user);
env.put (Context.SECURITY_CREDENTIALS, password);
env.put (Context.SECURITY_AUTHENTICATION, ServiceCtx.NON_SSL_LOGIN);
env.put(jdbc_accessURLContextFactory.CONNECTION_URL_PROP, namespaceURL);
Context ic = new InitialContext (env);
 
//retrieve the DataSource for the remote database
DataSource remoteDS = (DataSource)ic.lookup ("jdbc_access://test/NewYorkDS");

//retrieve connections to both local and remote databases
Connection localConn = localDS.getConnection ();
Connection remoteConn = remoteDS.getConnection ();
...
//close the connections
localConn.close();
remoteConn.close();

//end the transaction
 ut.commit();

Creating DataSource Objects Dynamically

If you want to bind only a single DataSource object in the namespace to be used for multiple database resources, you must do the following:

1. Bind the DataSource without specifying the URL, host, port, SID, or driver type. Thus, you execute the bindds tool with only the -dstype jta option, as follows:

   sess_sh -service jdbc:oracle:thin:@nsHost:5521:ORCL -user SCOTT -password 
   TIGER
   & bindds /test/empDS -dstype jta
   
   

2. Retrieve the DataSource in your code. When you perform the lookup, you must cast the returned object to OracleJTADataSource instead of DataSource. The Oracle-specific version of the DataSource class contains methods to set the DataSource properties.

3. Set the following properties:
   • Set the URL with the OracleJTADataSource.setURL method

   • Fully-qualified database link if using two-phase commit engine with the OracleJTADataSource.setDBLink method

4. Retrieve the connection through the OracleJTADataSource.getConnection method as indicated in the other examples.

Example 17-5 Retrieving Generic DataSource

The following example retrieves a generically bound DataSource from the namespace using in-session lookup and initializes all relevant fields.

//retrieve an in-session generic DataSource object
OracleJTADataSource ds =

      (OracleJTADataSource)ic.lookup ("java:comp/env/test/empDS");

//set all relevant properties for my database
//URL is for a local database so use the KPRB URL
ds.setURL ("jdbc:oracle:kprb:");
//Used in two-phase commit, so provide the fully qualified database link that 
//was created from the two-phase commit engine to this database
ds.setDBLink("localDB.oracle.com");

//Finally, retrieve a connection to the local database using the DataSource
Connection conn = ds.getConnection ();

JDBC Restrictions

If you are using JDBC calls in your server object to update a database, and you have an active transaction context, you should not also use JDBC to perform transaction services, by calling methods on the JDBC connection. Do not code JDBC transaction management methods. For example:

Connection conn = ...
...
conn.commit();  // DO NOT DO THIS!!

Doing so will cause a SQLException to be thrown. Instead, you must commit using the UserTransaction object retrieved to handle the global transaction. When you commit using the JDBC connection, you are instructing a local transaction to commit, not the global transaction. When the connection is involved in a global transaction, trying to commit a local transaction within the global transaction causes an error to occur.

In the same manner, you must also avoid doing direct SQL commits or rollbacks through JDBC. Code the object to either handle transactions directly using the UserTransaction interface.

Within a global transaction, you cannot execute a local transaction. If you try, the following error will be thrown:

ORA-2089 "COMMIT is not allowed in a subordinate session."

Some SQL commands implicitly execute a local transaction. All SQL DDL statements, such as "CREATE TABLE", implicitly starts and commits a local transaction under the covers. If you are involved in a global transaction that has enlisted the database that the DDL statement is executing against, the global transaction will fail.