Chapter 9. JDBC Quality of Services and Best Practices

Quality of service refers to transaction services, security services, connection caching, and other infrastructure services. Connection caching was addressed in Chapter 7. This chapter looks at transaction services in JDBC, including local transaction, distributed transactions, and transaction save-point, and then looks at security services such as SSL/JSSE support. Finally, we’ll look at some programming tips and logging/tracing.

Transaction Services

Transactions

A transaction is a unit of work, which has the generally defined and well-known ACID properties (i.e., Atomicity, Consistency, Isolation, and Durability). In the Oracle database environment, a transaction begins at the first SQL statement that follows the most recent commit, rollback, or connect statement; however, even though a SELECT statement may follow a COMMIT or ROLLBACK, only DML (Insert, Update, Delete, SELECT FOR UPDATE) and SET statements are regarded as starting point(s) of a new database transaction. The javax.sql.Connection provides commit and rollback methods for demarcating local transactions.

AutoCommit

Autocommit mode is a Boolean value (i.e., false/true), which instructs the database whether or not to automatically commit after every SQL operation. By default, autocommit is set to true for new connection objects. For better application performance, disable autocommit mode by invoking setAutoCommit() method on the connection object and use the explicit commit() or rollback() methods.

// It's faster when auto commit is off
conn.setAutoCommit (false);

Note that DDL statements (e.g., CREATE TABLE) are systematically auto-committed.

Transaction Isolation Levels

JDBC defines the following transaction isolation levels: TRANSACTION_NONE, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, and TRANSACTION_SERIALIZABLE, but ultimately the JDBC driver can only expose what the underlying datasource supports.

Table 9.1 summarizes the isolation levels defined by ANSI SQL 92 and the ones supported by the Oracle database. However, the Oracle database furnishes multilevel read consistency and ensures nonblocking reads, which is the goal of READ UNCOMMITTED.

Table 9.1. ANSI SQL 92 Isolation Levels

ANSI SQL 92 Isolation Levels

Dirty Read

Non RepeatableRead

Phantom Insert Possible

Oracle Database

READ UNCOMMITTED

Yes

Yes

Yes

 

READ COMMITTED

No

Yes

Yes

X

REPEATABLE READ

No

No

Yes

READ-ONLY

SERIALIZABLE

No

No

No

X

The Oracle JDBC drivers support only TRANSACTION_READ_COMMITTED and TRANSACTION_SERIALIZABLE isolation levels.

connAttr.setProperty("TRANSACTION_ISOLATION",
"TRANSACTION_SERIALIZABLE");
...
oconn = ods.getConnection(connAttr); // retrieve connection

The default RDBMS isolation is returned by the getDefaultTransactionIsolation method.

oconn.getMetaData().getDefaultTransactionIsolation ();

Similarly, the isolation level of an OracleConnection can be set and retrieved by the getTransactionIsolation and setTransactionIsolation methods.

oconn.getTransactionIsolation ();

Transaction SavePoint Support

Transaction savepoint is a well-known RDBMS mechanism, which allows setting intermediate control points (partial commit or rollback points) within a local transaction (not supported for global/distributed transactions). JDBC 3.0 now specifies a standard interface for exposing transaction savepoint to Java developers.

The Oracle JDBC furnishes the standard java.sql.Savepoint for JDK 1.4 and up environments and oracle.jdbc.OracleSavepoint for pre-JDK 1.4 environments.

A savepoint is created using either Connection.setSavepoint(), which returns java.sql.Savepoint object, or OracleConnection.oracleSetSavepoint(), which returns oracle.jdbc.OracleSavepoint object. A savepoint is either named by a string provided during its creation or automatically assigned an integer id.

Savepoint svpt1 = conn.setSavepoint("SVPT1");
Savepoint svpt3 = conn.setSavepoint();

The following methods retrieve the name or the id of the savepoint. Retrieving a name from an unnamed savepoint or retrieving an id from a named savepoint throws a SQL Exception.

svpt.getSavepointName();
svpt.getSavepointId();

Once a savepoint has been set, the transaction can be rolled back to that savepoint using either Connection.rollback (Savepoint svpt) or OracleConnection.oracleRollback (OracleSavepoint svpt).

conn.rollback(svpt1);

Rolling back to a savepoint that has been released throws a SQL Exception.

The JDBC standard also specifies a method to explicitly release/cancel savepoints by using Connection.releaseSavepoint(Savepoint svpt). The Oracle JDBC does not currently offer proper support for explicit save-point releasing; however, when a transaction is committed or rolled back, all savepoints created as part of the transaction are implicitly/automatically released (i.e., become invalid). (See Listing 9.1.)

Example 9.1. TransSavepoint.java

/*  Transaction SavePoint Sample Code
 *
 * The program uses the "DEPT" table and performs
 * following operations
 *   checks Savepoint support
 *   insert a new dept row
 *   set a first named Savepoint
 *   insert another dept row
 *   set a second named Savepoint
 *   set an unamed Savepoint
 *   rollback to first named Savepoint
 *   commit ==> the DEPT table should only show one new row
 *
 */
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
public class TransSavepoint
{
  public static void main(String args[]) throws SQLException
  {
    Connection conn = null;
    Statement  stmt = null;
    ResultSet  rset = null;
    int        rows = 0;
    try
    {
     // Create an OracleDataSource

     OracleDataSource ods = new OracleDataSource();
     // Set the URL, using TNS Alias with JDBC-Thin
     String url = "jdbc:oracle:thin:scott/tiger@inst1";
     ods.setURL(url);
    conn = ods.getConnection(); // Retrieve a connection

     // Disable Auto Commit for Transaction Savepoint
     conn.setAutoCommit(false);
     // Create a Statement
      stmt = conn.createStatement();
     // Cleanup changes by previous executions
      stmt.execute("DELETE FROM dept WHERE deptno >= 90");

     // Check Savepoint support
      DatabaseMetaData dbmd = conn.getMetaData();
      print("Checking savepoint support ...");
      if (dbmd.supportsSavepoints()) print("Savepoint supported");
      else
        print("Savepoint not supported");

      // Insert a new record into the "dept" table
       print("Insert dept(91, 'DEPT91''Tumbuctou') ...");
       rows =
       stmt.executeUpdate("insert into dept values (91,
           'DEPT91','Tumbuctou')");

      // Establish first named savepoint
       print("Establish svpt1 ...");
       Savepoint svpt1 = conn.setSavepoint("SVPT1");

      // Insert second record into the "dept" table
       print("Insert record(92, 'DEPT92', 'New York') ...");
       rows =
       stmt.executeUpdate("insert into dept values (92, 'DEPT92','New
            York')");

      // Establish second named savepoint
       print("Establish named savepoint svpt2 ...");
       Savepoint svpt2 = conn.setSavepoint("SVPT2");

      // Establish an unamed savepoint
       print("Establish svpt3 ...");
 Savepoint svpt3 = conn.setSavepoint();

// Insert a third record into the "dept" table
 print("Insert dept(93, 'DEPT93', 'Paris') ...");
 rows =
 stmt.executeUpdate("insert into dept values (93, 'DEPT93',
     'Istanbul')");

// Check names and ids of established Savepoints
 print("The name of txn savepoint #1 is: " +
      svpt1.getSavepointName());
 print("The name of txn savepoint #2 is: " +
      svpt2.getSavepointName());
 print("The id of txn savepoint #3 is: " +
      svpt3.getSavepointId());

// Rollback to the first savepoint
 print("Rollback to SVPT1...");
 conn.rollback(svpt1);

// Commit the transaction
 print("Commit the transaction ...");
 conn.commit();

 print("Checking changes to Dept ...");
 rset =
  stmt.executeQuery ("select * from dept where deptno >= 90");

// Print the result
 while (rset.next ())
 {
  print ("DeptNo: " + rset.getString (1));
  print ("DeptName: " + rset.getString (2));
  print ("DeptLoc: " + rset.getString (3));
 }

// Close the ResultSet
 rset.close();

// Cleanup table to original state
 stmt.execute("DELETE FROM dept WHERE deptno >= 100");
       // Close the Statement
        stmt.close();

      // Close the Connection
       conn.close();
     } catch(SQLException sqle) {
      print("Unexpected SQL Exception " + sqle.getMessage());
      sqle.printStackTrace();
     } catch(Exception exc) {
      print("Unexpected Exception " + exc.getMessage());
      exc.printStackTrace();
     }
  }

  static void print(String mesg)
  {
    System.out.println(mesg);
  }
}

$ javac TransSavepoint.java
$ java -Doracle.net.tns_admin=$TNS_ADMIN TransSavepoint

Checking savepoint support ...
Savepoint supported
Insert dept(91, 'DEPT91''Tumbuctou') ...
Establish svpt1 ...
Insert record(92, 'DEPT92', 'New York') ...
Establish named savepoint svpt2 ...
Establish svpt3 ...
Insert dept(93, 'DEPT93', 'Paris') ...
The name of txn savepoint #1 is: SVPT1
The name of txn savepoint #2 is: SVPT2
The id of txn savepoint #3 is: 1
Rollback to SVPT1...
Commit the transaction ...
Checking changes to Dept ...
DeptNo: 91
DeptName: DEPT91
DeptLoc: Tumbuctou

Global/Distributed Transaction

The X/Open Distributed Transaction Model

The X/Open Distributed Transaction Processing (DTP) specifies a standard model for distributed/global transactions, which includes the following components:

  • Application Component, which implements transactional operations and programmatically demarcates transactions using the TX interface.

  • Resource Manager, which is an X/Open XA-compliant component such as RDBMS; it manages persistent and stable data storage systems.

  • Transaction Manager, which implements the two-phase commit protocol. This protocol allows the transaction manager and the resource manager to preserve the ACID properties (i.e., “all or nothing”); in other words, either all the resource managers commit the transaction (“all”) or they all rollback (“nothing”).

  • Communication Resource Manager, which allows interoperability between transaction managers in different transaction processing domains.

Figure 9.1 illustrates the key components within a single DTP domain.

The Distributed Transaction Processing Model

Figure 9.1. The Distributed Transaction Processing Model

JDBC Support for Global Transactions

JTA is a high-level transaction management API, used in Java EE DTP environments by resource managers and transactional applications, the JTA libraries are also built into JDK 1.4 and JDK 1.5 (for pre-1.4 JDK, you need supplemental jta.jar and jndi.jar). JTA defines UserTransaction, TransactionManager, XAResource, and Transaction interfaces.

  • The javax.transaction.UserTransaction interface provides methods for the user application to demarcate transactions programmatically, including begin(), commit(), rollback, getStatus() setTransactionTimeout(), and setRollbackOnly().

  • The javax.transaction.TransactionManager interface provides methods that allow an application server to manage transaction boundaries, including begin(), commit(), rollback, getStatus() setTransactionTimeout(), setRollbackOnly(), suspend(), resume(), and getTransaction().

  • The javax.transaction.xa.XAResource interface is the Java mapping of the X/Open XA interface. It provides the following methods: commit(), end(), forget(), getTransactionTimeout(), isSameRM(), prepare(), recover(), rollback(), setTransactionTimeout(), and start(). These methods are used by resource managers and transaction managers to associate a global transaction and a database connection (or a message service connection). JDBC (or a JMS provider) implements this interface to provide the underlying Java XA support necessary for JTA transactions. The oracle.jdbc.xa.OracleXAResource class implements the javax.transaction.xa.XAResource interface.

  • The javax.transaction.xa.Xid interface is the Java mapping of the X/Open transaction identifier XID structure. The Xid must be globally unique and contains the formatId, the global transaction id (gtrid), and branch qualifier id (bqual). It provides the following methods: getBranchQualifier(), getFormatId(), and getGlobalTransactionId(). The oracle.jdbc.xa.OracleXid class implements the javax.transaction.xa.Xid interface, in addition to the isLocalTransaction() method. See the “Distributed Transaction” chapter in the Oracle JDBC Users Guide for more details on the XAResource flags and Xids.

  • The javax.transaction.xa.XAException interface defines an exception thrown by the resource manager for consumption/processing by the transaction coordinator/manager when it encounters an error specific to the transaction involved. The oracle.jdbc.xa.OracleXAException extends the javax.transaction.xa.XAException and furnishes the getOracleError(), getXAError(), and getXAErrorMessage() methods.

    try{
...
// XA operations
...
} catch(OracleXAexception oraxae){
   int oraerr = oraxae.getOracleError();
   System.out.println("Oracle XA Exception" + oraerr);
} catch (XAException xae) {
 // generic XA Exceptions
}

See Table 29-2 in the Oracle JDBC 10g reference guide for a mapping between Oracle XA Error codes and XA Errors.

JDBC XADataSource and XAConnection Interfaces

The javax.sql.XADataSource interface defines a factory for XAConnection objects. The oracle.jdbc.xa.OracleXADataSource class in both oracle.jdbc.xa.client (for client-side JDBC) and oracle.jdbc.xa.server (for RDBMS-side JDBC) packages implement javax.sql.XADataSource.

The javax.sql.XAConnection interface defines the getXAResource() method. The XAConnection.getXAResource() method returns an XAResource object, which is used to associate and enlist the XAConnection object with the current transaction. The oracle.jdbc.xa.OracleXAConnection class implements the javax.sql.XAConnection interface and extends oracle.jdbc.pool.OraclePooledConnection ( which implements the javax.sql.PooledConnection interface). However, although the Oracle JDBC may cache OracleXAConnection objects, as of Oracle Database 10g Release 2, this feature is not publicly available. Consequently, when connection caching is enabled on an OracleXADataSource, invoking OracleXADataSource.getXAConnection(...) will fail; to get an XAConnection object Java applications should call OracleXADataSource.getConnection(...) instead.

Native XA Support in Oracle JDBC

In pre-10g releases of the Oracle JDBC, XA operations were sent to the RDBMS using stored procedures. Starting with Oracle Database 10 g Release 1, Oracle JDBC furnishes a more efficient/direct or native XA mechanism, thereby improving performance. Native XA is the default mode for JDBC-Thin (pure Java) drivers, while native XA has to be explicitly enabled for JDBC-OCI using the datasource property tnsEntry (the property is set to the value of the TNS entry name).

ods.setTNSEntryName("tnsalias");

ods.getTNSEntryName();

Two-Phase Commit Optimization

Upon initiating the prepare() phase of the two-phase commit, if two or more branches of a distributed transaction use the same Oracle database instance—that is, the OracleXAResource instances are associated to the same Resource Manager (i.e., the Oracle database)—and all but one branch return XA_RDONLY, and only one branch returns XA_OK or a failure, then the Oracle JDBC XA performs Commit or Rollback only on the branch, which returns XA_OK or a failure. The transaction coordinator uses the isSameRM() method on the OracleXAResource object (it also takes an OracleXAResource as parameter) to make such a determination.

Listing 9.2 summarizes the required steps for performing a distributed transaction with an optimized two-phase commit.

Example 9.2. JdbcXa.java

/*
 * XA 2PC code sample (mix application and TM dialogs with RM)

 */
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.xa.*;
import javax.transaction.xa.*;
import oracle.jdbc.xa.client.OracleXADataSource;

public class JdbcXa
{
  public static void main (String args [])throws SQLException
  {
   try
   {

   // Create a XADataSource instance
   OracleXADataSource oxads = new OracleXADataSource();

   // Set the URL, using TNS Alias with JDBC-Thin
   String url = "jdbc:oracle:thin:scott/tiger@inst1";
   oxads.setURL(url);

   // Set User/Pass, not currenrly retrieved from URL
   // for XADataSources
    oxads.setUser("SCOTT");
    oxads.setPassword("TIGER");

   // XA Connection Caching is not yet exposed publicly
   // oxads.setConnectionCachingEnabled(true);
   // get XA connection from XADataSource

   XAConnection xaconn1 = oxads.getXAConnection();
   XAConnection xaconn2 = oxads.getXAConnection();

   // Get the pysical connections
   OracleConnection conn1 =
(OracleConnection)xaconn1.getConnection();
   Statement stmt1 = conn1.createStatement ();

   // Get another physical conn. from the same data source
   OracleConnection conn2 =
(OracleConnection)xaconn2.getConnection();
   Statement stmt2 = conn2.createStatement ();

   // Get the XA Resources
   XAResource oxar1 = xaconn1.getXAResource();
    XAResource oxar2 = xaconn2.getXAResource();

    // Create the Xids With the Same Global Ids
    Xid xid1 = createXid(1);
    Xid xid2 = createXid(2);

    // Start the Resources
    oxar1.start (xid1, XAResource.TMNOFLAGS);
    oxar2.start (xid2, XAResource.TMNOFLAGS);

   // DO some work through xacon1
    stmt1.executeUpdate("insert into dept values (91, 'DEPT91',
'Tumbuctou')");

    // Do some work through xacnn2
     stmt2.executeUpdate("insert into emp values (9999, 'XA EMP',
 'XATEST', 7566, '29-OCT-05', 99, 0, 10)");

    // END both the branches
    oxar1.end(xid1, XAResource.TMSUCCESS);
    oxar2.end(xid2, XAResource.TMSUCCESS);

    // Prepare the RMs
    int xaprp1 = oxar1.prepare (xid1);
    int xaprp2 = oxar2.prepare (xid2);
    System.out.println("Return value for prepare 1 is " + xaprp1);
    System.out.println("Return value for prepare 2 is " + xaprp2);
    boolean xa_commit = true;

    //  Check the return from the prepare phase
    if (!((xaprp1 == XAResource.XA_OK) || (xaprp1 ==
 XAResource.XA_RDONLY)))
    xa_commit = false;
    if (!((xaprp2 == XAResource.XA_OK) || (xaprp2 ==
 XAResource.XA_RDONLY)))
    xa_commit = false;
    System.out.println("xa_commit is " + xa_commit);

    // If both branches are from same RM, the TM does XA optimization
    System.out.println("Is oxar1 same as oxar2 ? " +
 oxar1.isSameRM(oxar2));
    // Determine which branch to commit or rollback
    if (xaprp1 == XAResource.XA_OK)
     if (xa_commit)
       oxar1.commit (xid1, false);
     else
       oxar1.rollback (xid1);
    if (xaprp2 == XAResource.XA_OK)
     if (xa_commit)
       oxar2.commit (xid2, false);
     else
       oxar2.rollback (xid2);

   // Close the XA connections and resources
     stmt1.close(); conn1.close();
     stmt2.close(); conn2.close();

  /*
   * Print the changes to the tables and clean up
   */

      // Create an OracleDataSource
      OracleDataSource ods = new OracleDataSource();
      // Set the URL, using TNS Alias with JDBC-Thin
      ods.setURL(url);

      // Retrieve a connection <<-- Naive approach,
     // see connection sharing b/w local and global transaction, next

      conn1 = (OracleConnection) ods.getConnection();

      stmt1 = conn1.createStatement();
      // Print the newly added row in dept table
     ResultSet
          rset1 = stmt1.executeQuery ("select * from dept where deptno
>= 91");
     while (rset1.next ())
     {
       print ("
DeptNo: " + rset1.getString (1));
       print ("DeptName: " + rset1.getString (2));
       print ("DeptLoc: " + rset1.getString (3));
     }
      rset1.close();
     // Clean up our changes
     stmt1.execute("DELETE FROM dept WHERE deptno >= 91");

     // Print the newly added row in emp table
     rset1 = stmt1.executeQuery ("select * from emp where empno >=
9999");
     while (rset1.next ())
     {
       print ("
EmpNo: " + rset1.getString (1));
       print ("EmpName: " + rset1.getString (2));
       print ("EmpJob: " + rset1.getString (3));
       print ("Manager: " + rset1.getString (4));
       print ("HireDate: " + rset1.getString (5));
      }
     rset1.close();
      // Clean up our changes
     stmt1.execute("DELETE FROM emp WHERE empno >= 9999");
     conn1.commit(); // commit deletions in both tables
     rset1.close();
     stmt1.close();

  } catch (SQLException sqe){
    sqe.printStackTrace();
  } catch (XAException xae){
    if (xae instanceof OracleXAException) {
    System.out.println("XA Error is " +
    ((OracleXAException)xae).getXAError());
    System.out.println("SQL Error is " +
    ((OracleXAException)xae).getOracleError());
    }

 }

}

static Xid createXid(int bids)
 throws XAException
 {
  byte[] gid = new byte[1]; gid[0]= (byte) 9;
  byte[] bid = new byte[1]; bid[0]= (byte) bids;
  byte[] gtrid = new byte[64];
  byte[] bqual = new byte[64];
  System.arraycopy (gid, 0, gtrid, 0, 1);
  System.arraycopy (bid, 0, bqual, 0, 1);
  Xid xid = new OracleXid(0x1234, gtrid, bqual);
  return xid;
 }

static void print(String mesg)
  {
    System.out.println(mesg);
  }
}

$ javac JdbcXa.java
$ java -Doracle.net.tns_admin=$TNS_ADMIN JdbcXa

Return value for prepare 1 is 3
Return value for prepare 2 is 0
xa_commit is true
Is oxar1 same as oxar2 ? true

DeptNo: 91
DeptName: DEPT91
DeptLoc: Tumbuctou

EmpNo: 9999
EmpName: XA EMP
EmpJob: XATEST
Manager: 7566
HireDate: 2005-10-29 00:00:00.0

Connection Sharing between Local and Global Transactions

JDBC 3.0 specifies the ability to alternatively share the same connection between a local and a global transaction. A JDBC connection can only be in one of three states or modes: NO_TXN, LOCAL_TXN, or GLOBAL_TXN.

  • NO_TXN: At creation, every connection is in this state, and then it transitions to either LOCAL_TXN or GLOBAL_TXN.

  • LOCAL_TXN: The connection is being used by a local transaction; autocommit is disabled.

  • GLOBAL_TXN: The connection being used by a global transaction.

A connection can participate alternatively in local and global transactions, as illustrated by Figure 9.2. The first thing to notice is that there is no direct transition to and from LOCAL_TXN mode from and to GLOBAL_TXN.

State Transition

Figure 9.2. State Transition

NO_TXN -> LOCAL_TXN and LOCAL_TXN-> NO_TXN

  • The connection state changes into LOCAL_TXN when AutoCommit is set to false and DML operations (i.e., Insert, Update, Delete) are performed.

  • The connection returns to NO_TXN state when DDL (i.e., Create, Alter), commit(), or rollback() operations are performed.

// Transition: NO_TXN -> LOCAL_TXN
xconn.setAutoCommit(false);

// Create a Statement and do some work
Statement stmt = xconn.createStatement ();
stmt.executeUpdate("insert into dept values (91,
'DEPT91', 'Tumbuctou')");

// Transition: LOCAL_TXN-> NO_TXN
xconn.commit();

NO_TXN -> GLOBAL_TXN and GLOBAL_TXN -> NO_TXN

  • The connection goes in GLOBAL_TXN state when a start() operation is performed on an XAResource object.

  • The connection returns to NO_TXN when an end() operation is performed on an XAResource object.

// Transition NO_TXN -> GLOBAL_TXN
XAResource oxar = pc.getXAResource();
Xid xid = createXid ();
oxar.start (xid, XAResource.TMNOFLAGS);

// Create a Statement and do some work
stmt = xconn.createStatement ();
stmt2.executeUpdate("insert into emp values (9999, 'XA
EMP', 'XATEST', 7566, '29-OCT-05', 99, 0, 10)");

// Transition GLOBAL_TXN -> NO_TXN
oxar.end(xid, XAResource.TMSUCCESS);
oxar.commit (xid, true);

Security Services

Oracle JDBC Support for SSL

SSL in a Nutshell

The Secure Socket Layer (SSL) is a protocol for secure client/server communications in connected and Web environments (i.e., unconnected). SSL works on top of the TCP/IP protocol and uses various cryptography mechanisms to achieve strong authentication (beyond traditional username/password), data privacy, and ensure integrity. These mechanisms include secret key cryptology (i.e., DES, 3DES, RC2, RC4), public-key cryptography (i.e., RSA, DH), public-key certificates (i.e., VeriSign, Entrust, GTE Cyber-Trust), cryptographic hash functions (i.e., MD5, SHA), and message authentication code (MAC).

For starters, here is some lexicon:

  • Certificate: A digitally signed document that binds a public key with an entity. The certificate can be used to verify that the public key belongs to that individual.

  • Certificate authority (CA): An entity that issues digitally signed certificates.

  • Certificate signing request (CSR): Contains the identity of the certificate requester, as well as the public key of the server on which the certificate will be installed.

  • Cipher suite: A set of cryptographic algorithms (public-key exchange algorithm, key agreement algorithms, and cryptographic hash functions) and key sizes used to encrypt data sent over the network.

  • Private key: A secret key, used to decrypt a message that has been encrypted using the corresponding public key. It is also used to sign certificates. The certificate is verified using the corresponding public key.

  • Public key: A public key used for encrypting the message sent over SSL. It is also used to verify that a certificate has been signed by the corresponding private key.

  • Root certificate: A trusted root certificate or self-signed certificate that identifies a CA.

  • Session key: A pair of public_key/private_key for the duration of a session. The public key is provided by the receiver and used by the sender to encrypt sent messages, while the private key is not shared and used by the receiver to decrypt received messages.

  • Wallet: A password-protected container for storing private keys, certificates, and trusted certificates used by SSL for authentication and signing

During the establishment of secure communication, the client and the server perform a one-time SSL handshake, during which both parties negotiate and agree on the cipher suite and the encryption algorithm to use. At 20,000-feet view, the handshake process can be described as follows:[1]

  1. The client requests a secure communication with the server.

  2. The server sends back its public key to the client. Optionally, the server also sends its certificate and root certificate (for verification by the client), and requests the client’s certificate for authentication purposes.

  3. The client sends its public key to the server. Optionally, the client verifies that the server’s certificate and root certificate are signed by a trusted certificate authority, and if requested, the client also sends its certificate and root certificate back to the server for verification.

  4. The client and the server each generate a session key using public-key/private-key cryptography.

  5. At this stage, all subsequent communications between the client and the server are encrypted and decrypted using this set of session keys and the negotiated cipher suite.

Java Support for SSL (JSSE)

JSSE is a Java implementation of Secure Socket Layer (SSL) and Transport Layer Security (TLS); it furnishes classes and interfaces for creating and configuring key managers, trust managers, secure socket factories, and so on. Since release 1.4 of Java Standard Edition (i.e., JDK 1.4), JSSE has been an integral part of the Java security arsenal (it was optional before), along with the Java Cryptography Extension (JCE) and the Java Authentication and Authorization Service (JAAS).

The JSSE API is available in the javax.net, javax.net.ssl, and java.security.cert packages. It defines the following properties (i.e., system properties or security properties) for configuring SSL:

  • javax.net.ssl.keyStore: Location of the certificate container (no default value) prop.put("javax.net.ssl.keyStore","<file_location>| NONE");

  • javax.net.ssl.keyStoreType: The type of keystore (default value = KeyStore.getDefaultType()). prop.put("javax.net.ssl.keyStoreType","JKS");

  • javax.net.ssl.keyStorePassword: Protects the content of a keystore (no default). prop.put("javax.net.ssl.keyStorePassword","<password>");

  • javax.net.ssl.trustStore: A set of certificates that the SSL client uses to validates the received certificate the default value is either:

    $JAVA_HOME/lib/security/jssecacerts

    if it exists, otherwise:

    $JAVA_HOME/lib/security/cacerts).

prop.put("javax.net.ssl.trustStore", "<valid
location>");

  • javax.net.ssl.trustStoreType: The type of trustStore (default value = KeyStore.getDefaultType()). prop.put("javax.net.ssl.trustStoreType", "JKS");

  • javax.net.ssl.trustStorePassword: Used to protect the truststore

    prop.put("javax.net.ssl.trustStorePassword", "<password>");

These properties can be set at the launch of the JDK VM, as follows:

$java -Djavax.net.ssl.keyStore=~/keystore
      -Djavax.net.ssl.keyStorePassword=key123456 JdbcApp

These properties can also be set at the connection object level, overriding the settings through system properties.

ods.setConnectionProperties(info);

Configuring and Using the Oracle JDBC Drivers with SSL

The Oracle Advanced Security (ASO) option provides data privacy, data integrity, strong authentication, and single sign-on. Table 9.2 summarizes the ASO security mechanisms supported in Oracle JDBC drivers, as of 10g Release 2. Various data encryption and data integrity algorithms over SSL are supported for both drivers (see algorithms supported in JDBC-Thin versus JDBC-OCI in Table 9.2), while SSL authentication is only supported for JDBC-OCI through third-party digital certificates (Entrust-PKI).

Table 9.2. Oracle Advanced Security Support in JDBC

 

Data Encryption Algorithms

Strong Authentication (Third-Party Adapters)

Data Integrity Algorithms

JDBC-Thin

DES, 3DES, RC4

 

MD5

JDBC-OCI

DES, 3DES, RC4, AES

Kerberos, Radius, DCE, SSL (digital certificates)

MD5, SHA-1

How does SSL work with the Oracle JDBC and the Oracle database?

  1. The JDBC code seeks a connection to the Oracle database, using SSL.

  2. JDBC and the database perfom a handshake, during which the server authenticates itself to the driver and both agree on the cipher suite to use.

  3. Once the SSL handshake is successfully completed, JDBC can access the database using SSL.

  4. The Oracle database server authenticates the user in the JDBC URL through a non-SSL authentication method,[2] such as password authentication (all driver types). For JDBC-OCI, a strong authentication mechanism such as Kerberos, RADIUS, DCE, or SSL (digital certificates) can be used to authenticate the user.

  5. Upon authentication, the Oracle database server grants access and authorization to the user, and then the user can access the database securely through JDBC, using SSL.

As of 10g Release 2, the Oracle JDBC drivers support two SSL implementations:

  • Standard JSSE, which can be used with standard client-side certificates stored in truststored

  • Oracle’s own JavaSSL implementation, which can be used with client-side Oracle wallets

In the rest of this section, we’ll only look at how the Oracle JDBC-Thin works with JSSE; see the Oracle JDBC documentation[3] and/or white paper on the Oracle JDBC portal[4] regarding the configuration of JDBC-OCI with JSSE and also using Oracle JavaSSL with both JDBC-Thin and JDBC-OCI.

The steps for configuring JDBC-Thin with SSL consist of (1) creating and storing the server certificates within an Oracle wallet, (2) configuring the database for SSL, (3) creating and storing client certificates in a keystore, (4) configuring JDBC-Thin for SSL, and (5) using JDBC-Thin with SSL.

Creating and Storing Server Certificates in an Oracle Wallet

This step consists of the following operations: create an Oracle wallet, create a certificate request, obtain the corresponding certificate and a trusted certificate from a CA, and, finally, store the certificates in the Oracle wallet. If you already have your certificates, you can just use the command-line utility orapki to create the wallet, store the certificates, save the wallet in a convenient location (i.e., $TNS_ADMIN/jdbcssl), and export the certificates as well under the same directory tree.

orapki wallet create –wallet ~/jdbcssl –auto-login

auto-login avoids furnishing the password at each operation; however, operations that make changes to the content of the wallet still require keying in the password.

...
orapki wallet add –wallet ~/jdbcssl –trusted_cert –cert ~/
root_cert.txt

The rest of this step gives examples of accomplishing the various operations listed previously.

Start the Oracle Wallet Manager GUI tool:

  • On Windows, select Start, Programs, Oracle–HOME_NAME, Integrated Management Tools, Wallet Manager

  • On UNIX, enter owm.

Creating a Wallet

  1. Select New from the Wallet menu.

  2. If it displays "Your default wallet directory does not exist. Do you want to create it?" Click Yes.

  3. Provide a password and confirm in the New Wallet dialog box.

  4. The default Wallet type is “Standard.” Click OK.

  5. "A new empty wallet has been created. Do you want to create a certificate request at this time?"

    If you choose Yes, then go to the next section, step 2.

    If you choose No, then go to the next section, step 1.

Creating a Certificate Request

Assume the following distinguished name (DN):

'CN=myserver,OU=america,O=oracle,L=redwood
shores,ST=ca,C=US'

  • Select Add Certificate Request in the Operations menu.

  • Fill in the Create Certificate Request dialog box, and click OK.

    Use the default Key Size of 1024.

  • On Success, click OK.

Obtaining and Importing a Certificate

Obtain a free valid certificate for test purposes from a CA. In this example, we went to Thawte, but you can get it from your preferred/alternate CA.

  1. Point your browser to: www.thawte.com/cgi/server/test.exe.

  2. Fill in the required information, and click Submit.

  3. On “The Test CA Root Certificate is available in text format here.”

    Click “here” to land in a new browser window.

  4. Provide the required information on this page, and Accept.

  5. Save the thawte-roots.zip file on your disk.

  6. Extract and rename the Thawte Test Root_TEXT.txt file to root_cert.txt.

  7. Select Import Trusted Certificate from the Operations menu.

  8. Select Paste the certificate, and click OK.

  9. Paste the contents of the root_cert.txt file in the Import Trusted Certificate dialog box, and click OK.

    A new trusted certificate called “Thawte Test CA Root” is added to the Trusted Certificates node.

  10. Go to the first browser window, and click next.

  11. Accept the default values and click next.

  12. Select the certificate request, and copy the text in the Certificate Request field.

  13. Paste the text in the text area in the browser, and click next. The certificate is displayed.

  14. Select Import User Certificate from the Operations menu.

  15. Select Paste the certificate, and click OK.

  16. Copy the certificate text displayed on the browser window, paste it into the Import Certificate dialog box, and click OK. The certificate you requested will now show a Ready status.

Saving the Wallet and Certificate

Save your newly created wallet and the certificate, as follows:

  1. Create the following directory tree (myserver is the common name [CN] of the distinguished name [DN])

    $TNS_ADMIN/jdbcssl/wallet
$TNS_ADMIN/jdbcssl /wallet/myserver

  2. Copy the root_cert.txt file to the wallet directory.

  3. From the Wallet menu, select the Wallet node on the Oracle Wallet Manager, and Save As.

  4. Traverse to the wallet directory in the Select Directory dialog box or provide the wallet path, as suggested in step 1, in the Directory field in the dialog box, and click OK.

  5. Select the user certificate with status equals Ready, and Save As from the Wallet menu.

  6. Browse the myserver directory in the Select Directory dialog box or provide the path, as suggested in step 1, in the Directory field in the dialog box, and click OK.

  7. Select Auto Login from the Wallet menu.

  8. Select Save from the Wallet menu.

    The directory structure will look like this:

$TNS_ADMIN/jdbcssl
$TNS_ADMIN/jdbcssl/root_cert.txt
$TNS_ADMIN/jdbcssl/myserver
$TNS_ADMIN/jdbcssl/myserver/cwallet.sso
$TNS_ADMIN/jdbcssl/myserevr/ewallet.p12

Configure the Oracle Database for SSL

The database certificate is contained in a server-side wallet; you need to make a few changes to RDBMS configuration files (sqlnet.ora and listener.ora) to reflect the wallet location, protocol (tcps), SSL authentication, and port.

Setting ssl_client_authentication to true in both configuration files will instruct the RDBMS to authenticate the client, during the SSL handshake.

Perform the following steps to configure the database:

  • Stop the Net listener:

    $ lsnrctl stop listener

  • Edit the sqlnet.ora file in $ORACLE_HOME/network/admin/ by adding the following lines and save:

    SSL_VERSION=3.0
ssl_client_authentication=true
WALLET_LOCATION=
  (SOURCE=
     (METHOD=FILE)
     (METHOD_DATA=
       (DIRECTORY=jdbcssl/myserver)))

  • Edit the listener.ora file in $ORACLE_HOME/network/admin/ by adding a new entry as shown and save:

secure_lsnr=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=tcps)
      (HOST=<hostname.company.com>)
      (PORT=<port>)))
 ssl_client_authentication=false
WALLET_LOCATION=
  (SOURCE=
     (METHOD=FILE)
     (METHOD_DATA=
         (DIRECTORY=jdbcssl/myserver)))
SID_LIST_secure_lsnr=
  (SID_LIST=
  (SID_DESC=
       (SID_NAME=<sid>)
       (ORACLE_HOME=<ORACLE_HOME_PATH>)))

Restart the listener:

> lsnrctl start secure_lsnr
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-NOV-2005
20:21:48
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Starting /kmensah_lab/oracle/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /kmensah_lab/oracle/work/listener.ora
Log messages written to /kmensah_lab/oracle/network/log/
secure_lsnr.log
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=stadp15.us.oracle.com)(POR
T=13333)))
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=stadp15.us.oracle.com)(POR
T=13333)))
STATUS of the LISTENER
------------------------
Alias                     secure_lsnr
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 -
Production
Start Date                05-NOV-2005 20:21:48
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /kmensah_lab/oracle/work/listener.ora
Listener Log File         /kmensah_lab/oracle/network/log/
secure_lsnr.log
Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=stadp15.us.oracle.com)(POR
T=13333)))
Services Summary...
Service "lab" has 1 instance(s).
  Instance "lab", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

The following listener security checklist is excerpted from the Oracle Security Users Guide:

  • Restrict the privileges of the listener, so that it cannot read or write files in the database or the Oracle server address space.

  • Protect the listener with a password.

  • Prevent online administration.

  • Use SSL when administering the listener.

  • Remove the external procedure configuration from the listener.ora file if you do not intend to use such procedures.

  • Use the same port(s) on firewalls for secure (SSL) communication.

    The Oracle Advanced Security supports two types of firewalls:

    • Application proxy-based firewalls, such as Network Associates Gauntlet or Axent Raptor

    • Stateful packet inspection firewalls, such as Check Point Firewall-1 or Cisco PIX Firewall

Some firewall vendors furnish an Oracle Net Firewall Proxy kit to optimize the network traffic.

Creating and Storing Client Certificates in a Truststore

On the client side (i.e., JDBC), unlike Oracle’s JavaSSL, JSSE does not recognize Oracle wallets, so you need to create a truststore and optionally a client-side certificate (if SSL_CLIENT_AUTHENTICATION has been set to TRUE).

  • Create a subdirectory myclient under $TNS_ADMIN/jdbcssl.

  • Copy the root_cert.txt file to $TNS_ADMIN/jdbcssl on the client machine.

  • Create a truststore using the keytool utility (part of the JDK). The truststore will contain the root certificate and the user certificate that will be used during the SSL handshake.

The following command uses a keytool to import the CA trusted certificate into the keystore:

$ cd $TNS_ADMIN/jdbcssl

$ keytool -import -v -alias trustroot -file ./root_cert.txt -
keypass <password> -keystore truststore -storepass <password>

Owner: CN=Thawte Test CA Root, OU=TEST TEST TEST, O=Thawte
Certification, ST=FOR TESTING PURPOSES ONLY, C=ZA
Issuer: CN=Thawte Test CA Root, OU=TEST TEST TEST, O=Thawte
Certification, ST=FOR TESTING PURPOSES ONLY, C=ZA
Serial number: 0
Valid from: Wed Jul 31 17:00:00 PDT 1996 until: Thu Dec 31
13:59:59 PST 2020
Certificate fingerprints:
        MD5:  5E:E0:0E:1D:17:B7:CA:A5:7D:36:D6:02:DF:4D:26:A4
         SHA1:
39:C6:9D:27:AF:DC:EB:47:D6:33:36:6A:B2:05:F1:47:A9:B4:DA:EA
Trust this certificate? [no]:  yes
Certificate was added to keystore
[Storing truststore]
$

Creating the Client Certificate (Optional)

This step is needed only if SSL_CLIENT_AUTHENTICATION has been turned on, on the server.

  • Use keytool to create a new pair of private/public keys with the following DN:

    CN=myclient, OU=america, O=oracle, L=redwood shores,
ST=ca, C=US.
$ keytool -genkey -v -alias myclient -keyalg RSA
-dname 'CN=myclient,OU=america,O=oracle,
L=redwood shores,ST=ca,C=US' -keypass <password>
-keystore <keystore> -storepass <password>

  • Create a certificate request as certreq.txt.

    $ keytool -certreq -alias myclient
  -file certreq.txt –keypass <password>
  -keystore <keystore>  -storepass <password>

  • Get a signed certificate from a CA using the same steps used in the server case.

    (Note that the certificate must be in PKCS #7 format.)

    Save it in response.txt.

  • Import the certificate into your keystore. Note that if the certificate was obtained through Thawte, keytool does not recognize it as a trusted source. Answer Yes to the following question: “Do you want to install it anyway?”

    $ keytool -import -v -alias myclient
-file response.txt -keypass <password>
–keystore <keystore> -storepass <password>

  • Import the trusted certificate into the keystore:

    > keytool -import -v -noprompt -alias trustroot
-file ./root_cert.txt -keypass <password>
-keystore keystore -storepass <password>

  • Use the –list option to verify:

    > keytool -list -storepass <password>
  -keystore keystore

Configuring the JDBC-Thin Driver for SSL

SSL is automatically used for JDBC-Thin when the connection description contains “(PROTOCOL=tcps).”

  1. Confirm ojdbc14.jar in the CLASSPATH.

  2. Modify the tnsnames.ora file as follows, to ensure database connection over SSL:

    secure_client=
 (DESCRIPTION=
   (ADDRESS=
     (PROTOCOL=tcps)
     (HOST==<hostname.company.com> )
     (PORT=<port>))
    (CONNECT_DATA=(SERVICE_NAME=lab))
    (SECURITY=

(SSL_SERVER_CERT_DN="CN=myserver,OU=america,O=oracle,L=
redwood shores,
      ST=ca,C=US)))

Using JDBC-Thin with SSL

At this stage, we are in a position to use JDBC with SSL; however, it is highly recommended to always get SSL working in SQLPlus first (using the secure TNS alias), before getting it to work in JDBC. Notice the following connection properties:

  • oracle.net.ssl_version: Specifies the SSL version to be used by the driver; default value is ANY.

    prop.put("oracle.net.ssl_version", "3.0")

  • oracle.net.wallet_location: If set, it overrides the standard JSSE properties javax.net.ssl.keyStore and javax.net.ssl.trustStore; no default value.

    prop.put("oracle.net.wallet_location", "<location>");

  • oracle.net.cipher_suites: A comma-separated list of strings, which controls the algorithm and key sizes for data integrity and encryption between the SSL implementation and the RDBMS; no default value.

    prop.put("oracle.net.cipher_suites",
"SSL_DH_DSS_WITH_DES_CBC_SHA");

  • oracle.net.ssl_server_dn_match: Matches the distinguished name (DN) with the service name; the possible values are TRUE/

FALSE/ON/OFF/YES/NO
prop.put("oracle.net.ssl_server_dn_match", "TRUE");

JdbcSSL.java
============
/* sample using JSSE SSL
 * This sample shows how to call a PL/SQL function that opens
 * a cursor and get the cursor back as a Java ResultSet.
 *
 * sqlType CURSOR corresponds to "ref cursor". open the cursor
 * by specifying CURSOR type at register method. retrieve the
 * value by getObject method.
 */

import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import java.security.*;

class JdbcSSL
{
  public static void main (String args [])
       throws SQLException
  {
    String url = "jdbc:oracle:thin:@secure_client";

   //set properties
   java.util.Properties info = new java.util.Properties();
   System.setProperty("oracle.net.tns_admin", "<your
$TNS_ADMIN>");
   info.put ("javax.net.ssl.keyStore",<$TNS_ADMIN>/jdbcssl/
keystore");
   info.put ("javax.net.ssl.keyStoreType","JKS");
   info.put ("javax.net.ssl.keyStorePassword","<password>");

   // if you have a trustStore (SSL_CLIENT_AUTHENTICATION on)
   String trustStore = "/home/kmensah/jdbcssl/truststore";
   info.setProperty("javax.net.ssl.trustStore", trustStore);
   info.setProperty("javax.net.ssl.trustStorePassword",
"<password>");

   // optional settings

   //Cipher suite      //
info.put("oracle.net.ssl_cipher_suites","SSL_DH_DSS_WITH_DES_
CBC_SHA"// );

// Force dn to match service name
// info.put("oracle.net.ssl_server_dn_match","TRUE");

 // Create a OracleDataSource instance and set properties
 OracleDataSource ods = new OracleDataSource();
  ods.setUser("hr");
  ods.setPassword("hr");
  ods.setURL(url);
  ods.setConnectionProperties(info);

  // Connect to the database
  Connection conn = ods.getConnection();
  ...
  ...

  stmt.close();
  conn.close();

 }
}

To conclude this section, please note that state regulations in countries such as the United States prohibit the use of SSL authentication concurrently with non-SSL authentication, as well as the use of SSL encryption concurrently with non-SSL encryption.

Tips and Best Practices

This section is a list of tips for understanding common error messages and performance optimizations. This section also describes end-to-end tracing, common errors, optimizing result set retrieval, and logging service.

End-to-End Tracing

The Oracle database and the Oracle Application Server have been instrumented to allow gathering metrics for tracking and correlating all operations performed by a session or a module (i.e., a browser, a thread). The following metrics are supported: Action, Module, ClientId, and ExecutionContextId (also known as ECID).

  • Client ID (i.e., thread-id): For tracking which client invoked work on a connection

  • The Service Module or Service Action: Used to tag and trace units of work (transaction blocks) for a given session

  • The Execution Context Id (also known as ECID): A unique identifier associated with a thread of execution

The following SQL statement retrieves the current module (i.e., SQL*Plus):

$ sqlplus system/xxxxx

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 26 19:18:00 2005
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Production
With the Partitioning and Data Mining options

SQL> col module format a12
SQL> col client_identifier format a20
SQL> select action, module, client_identifier from V$SESSION where
username ='SYSTEM'

ACTION                           MODULE       CLIENT_IDENTIFIER
-------------------------------- ------------ --------------------
                                 SQL*Plus
SQL>

The Oracle JDBC 10g drivers expose the database metrics to Java applications and Java containers, such as the Oracle Application server, which complement the database metrics through the DMS framework.

JDBC supports these metrics at the connection level with the following methods:

  • getEndToEndECIDSequenceNumber(): Gets the current end-to-end tracing context id sequence number. As you can observe in Listing 9.3, the sequence number is incremented by the database, allowing the requestor to sort the call sequence (and/or the error sequence) in the order they occurred. Its value could be:

  • The value passed in the most recent call to setEndToEndMetrics

  • The value returned by the database after the most recent statement execution

  • The value incremented by JDBC diagnostic messages

    short seqnum =
((OracleConnection)conn).getEndToEndECIDSequenceNumber();

    End-to-end metrics are enabled in JDBC by invoking the setEndToEndMetrics method.

  • setEndToEndMetrics(java.lang.String[] metrics, short sequenceNumber): It sets the values of the tracing metrics. The indices of the array of metrics are defined in the OracleConnection class.

  • END_TO_END_STATE_INDEX_MAX: The size of the string array containing the metrics values.

    short seqnum = 100;
String[] metric =
new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];

  • END_TO_END_ACTION_INDEX: The index of the ACTION metrics; the maximum length of these metrics is 32 bytes/characters.

    metric[OracleConnection.END_TO_END_ACTION_INDEX] =
"ActionMetrics";
conn.setEndToEndMetrics(metric,seqnum);

  • END_TO_END_MODULE_INDEX: The index of the MODULE metrics; the maximum length of these metrics is 48 bytes/characters.

    metric[OracleConnection.END_TO_END_MODULE_INDEX] =
"ModuleMetrics";

conn.setEndToEndMetrics(metric,seqnum);

  • END_TO_END_CLIENTID_INDEX: The index of the CLIENTID metrics; the maximum length of these metrics is 64 bytes/characters.

    metric[OracleConnection.END_TO_END_CLIENTID_INDEX] =
"ClientIdMetrics";

conn.setEndToEndMetrics(metric,seqnum);

  • END_TO_END_ECID_INDEX: The index of the ECID metrics; the maximum length of these metrics is 64 bytes/characters.

    metric[OracleConnection.END_TO_END_ECID_INDEX] =
"ECIDMetrics";

conn.setEndToEndMetrics(metric,seqnum);

  • getEndToEndMetrics(): Gets the values of the end-to-end metrics, if any. Does not include the sequence number.

Example 9.3. myEnd2EndMetrcis.java

import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

public class myEnd2EndMetrics
{
  public static void main(String[] args) throws SQLException
  {
    //Create an OracleDataSource
    OracleDataSource ods = new OracleDataSource();
    // Set the URL, using TNS Alias with JDBC-Thin
    // Use a schema, which can see/read V$SESSION
    String url = "jdbc:oracle:thin:<user>/<password>@inst1";
    ods.setURL(url);
    // Retrieve a connection
    OracleConnection conn = (OracleConnection)ods.getConnection();
    short seqnum = 20;
    String[] metric = new
String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
    metric[OracleConnection.END_TO_END_ACTION_INDEX] =
"ActionMetrics";
    metric[OracleConnection.END_TO_END_MODULE_INDEX] =
"ModuleMetrics";
    metric[OracleConnection.END_TO_END_CLIENTID_INDEX] =
"ClientIdMetrics";
    metric[OracleConnection.END_TO_END_ECID_INDEX] = "ECIDMetrics";

    conn.setEndToEndMetrics(metric,seqnum);

    try {
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery
      ("select ACTION,MODULE,CLIENT_IDENTIFIER from V$SESSION where
USERNAME='SYSTEM'");

      seqnum =
((OracleConnection)conn).getEndToEndECIDSequenceNumber();
      System.out.println("*** Sequence Number = " + seqnum);
      while (rs.next()) {
       System.out.println("*** Action = " + rs.getString(1));
       System.out.println("*** Module = " + rs.getString(2));
       System.out.println("*** Client_identifier = " +
rs.getString(3));
      }

      stmt.close();
      String[] metrics =
((OracleConnection)conn).getEndToEndMetrics();
      System.out.println("*** End-to-end Metrics: "+metrics[0]+",
"+metrics[1]+
              ", "+metrics[2]+", "+metrics[3]);
     } catch (SQLException sqle) {
        sqle.printStackTrace();
     }
  }
}

$javac myEnd2EndMetrics.java
$java -Doracle.net.tns_admin=$TNS_ADMIN myEnd2EndMetrics
*** Sequence Number = 22
*** Action = ActionMetrics
*** Module = ModuleMetrics
*** Client_identifier = ClientIdMetrics
*** End-to-end Metrics: ActionMetrics, ClientIdMetrics, ECIDMetrics,
ModuleMetrics
$

Common Errors

"No more data to read from socket."

This message happens when the JDBC-Thin driver times out while waiting to get more bytes from the server (and the server is finished!). This is usually a result of the crash of a server process; see the server logs for more details.

"Bigger type length than expected."

This message happens when using older JDBC-Thin drivers with newer RDBMS releases; it indicates that the driver is expecting a UB2 but got a UB4. Check that such a bug has not already been fixed for the version of the driver in question.

Optimizing Result Set Retrieval

  • Result sets are usually retrieved using a while loop:

    while (rs.next()) {
  // retrieve and process the row/columns
}

    Such a while loop is generic enough to accommodate the retrieval of 0 or <n> rows. If Row-Prefetch has not been set (i.e., defaults to 1), then the driver sends a FETCH(1) command per roundtrip until it gets an error; however, if you know you will be retrieving only a single row, setting the Row-Prefetch size to 2 (see Chapter 8) will save the extra roundtrip.

  • Alternatively, using an if statement with a default Row-Prefetch does not incur an additional roundtrip.

    if (rs.next()) {
// retrieve and process the row/columns
}

  • If you are using JDBC-Thin, and retrieving 0 or 1 row, by setting the useFetchSizeWithLongColumn connection property to true, the driver will perform PARSE, EXECUTE, and FETCH all together in a single roundtrip.

Logging Service

Since Oracle JDBC 10g, you can initiate JDBC logging using the java.util.logging package, which comprises the following objects: Logger, LogRecord, Handler, Level, Filter, and Formatter. See the java.util.logging[5] for more details.

Logging Levels

The tracing levels are represented by java.util.logging.Level. The user controls the tracing by setting specific levels on one or more loggers.

The most useful predefined levels are the following:

  • OFF: Disables logging

  • CONFIG: SQL statements, low data volume

  • INFO: JDBC API level tracing, low data volume

  • SEVERE: SQLExceptions, unrecoverable error conditions, low data volume

  • WARNING: SQLWarnings, recoverable error conditions, low data volume

  • FINE: Public JDBC APIs, function entry/return, medium data volume

Note

Not all levels are listed. Java developers may define additional levels using a distinct integer value (each level is uniquely identified by an integer value).

Loggers

A logger is associated with every JDBC package; as of version 10.2.0.1, some of the tracing loggers are as follows:

  • oracle.jdbc

  • oracle.jdbc.driver

  • oracle.jdbc.thin

  • oracle.jdbc.pool

  • oracle.jdbc.xa

  • oracle.jdbc.oci

  • oracle.jdbc.datum

  • oracle.jdbc.adt

  • oracle.jdbc.conversion

  • oracle.jdbc.sqlj

  • oracle.jdbc.jpub

  • oracle.jdbc.level

A logger handler exports the log messages from the logger in question

A formatter is associated with the logger handler for writing log records. The SimpleFormatter and XMLFormatter are available by default.

  • The simple formatter prints a summary of the LogRecord in a human-readable format.

  • The XML formatter outputs a LogRecord into a standard XML format.

Tracing JDBC Using System Properties

Here are the steps for enabling logging through system properties and a sample properties file:

  1. Use JDK 1.4 and the debug version of the driver (i.e., ojdbc14_g.jar). You may also use ojdbc14dms.jar, which lets you trace SQL execution, connections, and public JDBC methods.

  2. Edit the OracleLog.properties file with the following contents:

    # Handler
# default output file location = user's home directory.
handlers= java.util.logging.ConsoleHandler,
java.util.loggingFileHandler
.level= INFO
java.util.logging.FileHandler.pattern = jdbc.log
java.util.logging.FileHandler.limit = 50000
java.util.logging.FileHandler.count = 1
java.util.logging.FileHandler.formatter =
java.util.logging.XMLFormatter

# Speficy the trace/debug level for targeted packages
# Predefined levels are: ALL, SEVERE, WARNING, INFO,
CONFIG, FINE, FINER,
# OFF
# Setting ConsoleHandler level to SEVERE avoids
duplicate output from logger
java.util.logging.ConsoleHandler.level = SEVERE
java.util.logging.ConsoleHandler.formatter =
java.util.logging.SimpleFormatter
oracle.jdbc.level = CONFIG# Uncomment and/or change the
levels for more detail
oracle.jdbc.level = FINE
oracle.jdbc.driver.level = FINE
#oracle.sql.level = FINE
#oracle.jdbc.pool.level = FINE

  3. Set the oracle.jdbc.Trace property, as follows:

java -Doracle.jdbc.Trace=true -
Djava.util.logging.config.file=<properties file
location> <program name>

Conclusion

This chapter concludes the JDBC part of the book. At this stage, you have a better understanding of Oracle database programming using Java in the database (with server-side JDBC) and client-side JDBC. However, you might feel that the JDBC API is too fine grained and would love to have a coarse-grained API; this is exactly the purpose of SQLJ, our next topic.


[2] SSL authentication is not currently supported in Oracle JDBC-Thin.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.210.205