TERADATA CONFIDENTIAL

Copyright © 2005-2008 by Teradata Corporation.

All Rights Reserved.

This document, which includes the information contained herein,: (i) is the exclusive property of Teradata Corporation; (ii) constitutes Teradata confidential information; (iii) may not be disclosed by you to third parties; (iv) may only be used by you for the exclusive purpose of facilitating your internal Teradata-authorized use of the Teradata product(s) described in this document to the extent that you have separately acquired a written license from Teradata for such product(s); and (v) is provided to you solely on an "as-is" basis. In no case will you cause this document or its contents to be disseminated to any third party, reproduced or copied by any means (in whole or in part) without Teradata's prior written consent. Any copy of this document, or portion thereof, must include this notice, and all other restrictive legends appearing in this document. Note that any product, process or technology described in this document may be the subject of other intellectual property rights reserved by Teradata and are not licensed hereunder. No license rights will be implied. Use, duplication or disclosure by the United States government is subject to the restrictions set forth in DFARS 252.227-7013 (c) (1) (ii) and FAR 52.227-19. Other brand and product names used herein are for identification purposes only and may be trademarks of their respective companies.

The Teradata JDBC Driver provides an open interface to the Teradata Database. When the Teradata JDBC Driver is used in an application server, significant performance gains can be realized by using connection pools instead of non-pooled connections. This paper outlines the specific steps and settings required to enable connection pooling for the Teradata JDBC Driver with a supported application server.

This paper assumes that the reader is already familiar with the application server being used and has copied the Teradata JDBC Driver product to a system containing this application server. You should also know how to start the appropriate administrative console and have the required permissions that allow you to change operating parameters.

Though an understanding of the Java programming language shouldn't be necessary for learning how to set up Connection Pools, it is necessary to understand how to use them after they have been set up.

Please note that the screen shots used in the examples were taken on a Microsoft Windows system. The actual interface that you see on different platforms supported by the application servers may vary.

The purpose of this paper is to provide all of the details necessary to set up and use Connection Pools for the Teradata JDBC Driver with a supported application server.

Java Virtual Machine (JVM) and Application Server support by TTU/JDBC Driver release

u=unsupported / s=supported

	TTU 7.1 JDBC 3.1	TTU 8.0 JDBC 3.2	TTU 8.1 JDBC 3.3	TTU 8.2 JDBC 3.4	TTU 12.0 JDBC 12.0	TTU 13.0 JDBC 13.0
JVM 1.3.x	s	u	u	u	u	u
JVM 1.4.x	s	s	s	s	s	s
JVM 5.0	u	s	s	s	s	s
JVM 6.0	u	u	u	s	s	s
WebSphere 5.0 with IBM JVM 1.3.1	s	u	u	u	u	u
WebSphere 5.1 with IBM JVM 1.4.1	u	s	s	s	s	s
WebSphere 6.0 with IBM JVM 1.4.2	u	s	s	s	s	s
WebSphere 6.1 with IBM JVM 5.0	u	s	s	s	s	s
WebLogic 7.0 with Sun JVM 1.3.1	s	u	u	u	u	u
WebLogic 7.0 with JRockit JVM 1.3.1	u	u	u	u	u	u
WebLogic 8.1 with Sun JVM 1.4.1 or 1.4.2	s	s	s	s	s	s
WebLogic 8.1 with JRockit JVM 1.4.1 or 1.4.2	u	u	s	s	s	s
WebLogic 9.0 with Sun JVM 5.0	u	u	s	s	s	s
WebLogic 9.0 with JRockit JVM 5.0	u	u	s	s	s	s
WebLogic 9.1 with Sun JVM 5.0	u	u	s	s	s	s
WebLogic 9.1 with JRockit JVM 5.0	u	u	s	s	s	s
WebLogic 9.2 with Sun JVM 5.0	u	u	s	s	s	s
WebLogic 9.2 with JRockit JVM 5.0	u	u	s	s	s	s
WebLogic 10.0 with Sun JVM 5.0	u	u	u	s	s	s
WebLogic 10.0 with JRockit JVM 5.0	u	u	u	s	s	s
ColdFusion MX 6.1 Server Configuration with Sun JVM 1.4.2	s	s	s	s	s	s
ColdFusion MX 6.1 JRun 4 Configuration with Sun JVM 1.4.2	s	s	s	s	s	s
ColdFusion MX 6.1 J2EE Configuration in third-party application server	u	u	u	u	u	u
ColdFusion MX 7 Server Configuration with Sun JVM 1.4.2	s	s	s	s	s	s
ColdFusion MX 7 JRun 4 Configuration with Sun JVM 1.4.2	s	s	s	s	s	s
ColdFusion MX 7 J2EE Configuration in third-party application server	u	u	u	u	u	u
ColdFusion 8 Server Configuration with Sun JVM 1.4.2	u	u	u	s	s	s
ColdFusion 8 Server Configuration with Sun JVM 5.0	u	u	u	s	s	s
ColdFusion 8 Server Configuration with Sun JVM 6.0	u	u	u	s	s	s
ColdFusion 8 Multiserver (JRun 4) Configuration with Sun JVM 1.4.2	u	u	u	s	s	s
ColdFusion 8 Multiserver (JRun 4) Configuration with Sun JVM 5.0	u	u	u	s	s	s
ColdFusion 8 Multiserver (JRun 4) Configuration with Sun JVM 6.0	u	u	u	s	s	s
ColdFusion 8 J2EE Configuration in third-party application server	u	u	u	u	u	u
JBoss 3.2.3 with Sun JVM 1.4.2	u	s	s	s	s	s
JBoss 4.0 with Sun JVM 1.4.2	u	u	s	s	s	s
JBoss 4.0 with Sun JVM 5.0	u	u	s	s	s	s
SAP Web AS 6.40 with Sun JVM 1.4.2	u	s	s	s	s	s
Tomcat 5.5 with Sun JVM 5.0	u	u	u	s	s	s
Tomcat 6.0 with Sun JVM 5.0	u	u	u	s	s	s

Please refer to the Teradata JDBC Driver User Guide for each Teradata JDBC Driver release, to obtain the list of supported combinations of operating systems, CPUs, and 32/64 bit modes for that Teradata JDBC Driver release.

Please refer to the application server vendor's documentation to obtain the list of supported combinations of operating systems, CPUs, and 32/64 bit modes for each application server version.

The Teradata JDBC Driver is supported for use with all combinations of operating systems, CPUs, and 32/64 bit modes that are supported by both the application server version and the Teradata JDBC Driver release.

For Java software, an operating environment consists of the complete combination of the following items:

• the specific version of the underlying operating system
• the specific JVM version
• the 32-bit or 64-bit mode of the JVM
• the specific version of the application server, if an application server is used.

Generally, the Teradata JDBC Driver is tested in a particular operating environment before that operating environment is supported for use with the Teradata JDBC Driver. Such testing is needed for the following reasons:

• to verify the correct operation of the Teradata JDBC Driver
• to identify the minimum patch level needed for the underlying operating system version
• to identify the minimum patch level needed for the JVM version
• to ensure documentation, such as workarounds or Tech alerts, is available for the Global Support Center (GSC), so that it can support customers.

If a problem is encountered while using the Teradata JDBC Driver in an unsupported environment, then the GSC will attempt to reproduce the problem in the closest supported environment. If the problem can be reproduced in a supported environment, and the problem is determined to be due to the Teradata JDBC Driver, then the problem will be corrected and retested using the supported platform.

C/C++ applications that communicate with the Teradata Database use the TeraGSS security library. If C/C++ and Java applications are deployed to the same physical machine, then Java applications can be configured to use the TeraGSS security library's User Configuration File.

In this deployment scenario, Java applications do not use the tdgssconfig.jar file that is included in the Teradata JDBC Driver download package. Instead, the classpath for Java applications must include the TeraGSS directory that contains the TeraGSS User Configuration File, TdgssUserConfigFile.xml, as follows:

For shared TeraGSS configuration using the	The classpath must include
TTU 12.0 Teradata JDBC Driver and later	terajdbc4.jar The directory containing TdgssUserConfigFile.xml
TTU 8.2 Teradata JDBC Driver 3.4 and earlier	terajdbc4.jar tdgssjava.jar The directory containing TdgssUserConfigFile.xml

For standalone Java applications, this is done via the applications' classpath. For J2EE applications deployed to an application server environment, the Data Source classpath is modified.

Not all classloaders support the specification of a directory on the classpath. This deployment technique can only be used with classloaders that support the specification of a directory on the classpath.

Some application servers, such as SAP Web Application Server, only support the use of jar files when defining the classpath for a JDBC Data Source. If the application server or environment does not support the specification of a directory on the classpath, then C/C++ and Java applications cannot directly share the same TeraGSS User Configuration File.

For application servers or environments that do not support the specification of a directory on the classpath, the TeraGSS User Configuration File can only be shared indirectly, and an extra step must be performed to enable this indirect sharing.

A jar "update" command must be executed to take the TeraGSS User Configuration File from the TeraGSS directory and to put the TeraGSS User Configuration File into the tdgssconfig.jar file from the Teradata JDBC Driver download package via:

jar uvf tdgssconfig.jar TdgssUserConfigFile.xml

Each time the TeraGSS User Configuration File is modified, the jar "update" command must be executed again. The application server or environment must be restarted so that the modified tdgssconfig.jar will be used.

Teradata does not provide any means to "reset" a connection. Therefore, the user of a connection pool data source must avoid using any commands that affect session defaults. Any changes to session defaults will continue to be in effect for the next unsuspecting user of that connection.

Session parameters that must not be changed include:

• Database (SET SESSION DATABASE command)
• Collation (SET SESSION COLLATION command)
• Character Set
• Transaction Semantics
• Dateform (SET SESSION DATEFORM command)
• Timezone (SET TIME ZONE command)
• Default date format
• QueryBand (SET QUERY_BAND ... FOR SESSION command, introduced with Teradata Database 12.0)

The SET QUERY_BAND ... FOR TRANSACTION command is recommended as an alternative to SET QUERY_BAND ... FOR SESSION, because SET QUERY_BAND ... FOR TRANSACTION is limited in scope to the current transaction.

Sample code to access a previously created Connection Pool with username and password passed as parameters.

import java.sql.*;
import javax.sql.*;
import java.io.*;
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class SampleJdbcServlet extends HttpServlet
{
  public void doGet(HttpServletRequest req,HttpServletResponse res)
    throws ServletException, IOException
  {
    String user = req.getParameter("user");
    String password = req.getParameter("password");
    String datasource = req.getParameter("datasource");
    res.setContentType("text/html");
    PrintWriter out = res.getWriter();
    out.println("<html><body><pre>");
    try
    {
      Context ctx = new InitialContext();

      out.println("Looking up datasource " + datasource);
      DataSource ds = (DataSource) ctx.lookup(datasource);

      out.println("Establishing connection...");
      out.println("User: " + user + "");
      out.println("Password: " + password);

      Connection con = ds.getConnection(user, password);
      out.println("Connection obtained is: " + con);

      con.close();
      out.println("Connection.isClosed returns: " + con.isClosed());
    }
    catch (SQLException ex)
    {
      out.println("*** SQLException caught ***");
      while (ex != null)
      {
        out.println("Message: " + ex.getMessage ());
        out.println("SQLState: " + ex.getSQLState ());
        out.println("ErrorCode: " + ex.getErrorCode ());
        ex.printStackTrace (out);
        ex = ex.getNextException ();
      }
    }
    catch (java.lang.Exception ex)
    {
      out.println("*** Exception caught ***");
      ex.printStackTrace (out);
    }

    out.println("SampleJdbcServlet finished.");
    out.println("</pre></body></html>");
    out.close();
  }
}


Sample code to access a previously created Connection Pool using Data Source default username and password.

import java.sql.*;
import javax.sql.*;
import java.io.*;
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class SampleJdbcServlet extends HttpServlet
{
  public void doGet(HttpServletRequest req,HttpServletResponse res)
    throws ServletException, IOException
  {
    String datasource = req.getParameter("datasource");
    res.setContentType("text/html");
    PrintWriter out = res.getWriter();
    out.println("<html><body><pre>");
    try
    {
      Context ctx = new InitialContext();

      out.println("Looking up datasource " + datasource);
      DataSource ds = (DataSource) ctx.lookup(datasource);

      out.println("Establishing connection...");
      
      Connection con = ds.getConnection();
      out.println("Connection obtained is: " + con);

      con.close();
      out.println("Connection.isClosed returns: " + con.isClosed());
    }
    catch (SQLException ex)
    {
      out.println("*** SQLException caught ***");
      while (ex != null)
      {
        out.println("Message: " + ex.getMessage ());
        out.println("SQLState: " + ex.getSQLState ());
        out.println("ErrorCode: " + ex.getErrorCode ());
        ex.printStackTrace (out);
        ex = ex.getNextException ();
      }
    }
    catch (java.lang.Exception ex)
    {
      out.println("*** Exception caught ***");
      ex.printStackTrace (out);
    }

    out.println("SampleJdbcServlet finished.");
    out.println("</pre></body></html>");
    out.close();
  }
}