How to use the Teradata JDBC Driver with Apache Tomcat Date: September 16, 2008
Abstract: The Teradata JDBC Driver Connection Pooling facility can greatly improve application performance. The steps used to enable it for the Teradata JDBC Driver will vary with the application server being used. This paper outlines the steps and parameters required to use the Teradata JDBC Driver with Tomcat.
TERADATA CONFIDENTIAL
Copyright © 2005-2009 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.
Table of Contents
1.1. Assumptions
1.2. Purpose and Organization
1.3. Version Information
1.4. Supported Platform Matrix
1.5. C/C++ and Java Application Sharing of TeraGSS Security Configuration
2.1. Tomcat Data Source Definition
2.1.1. Prerequisites
2.1.2. Context Configuration
2.1.3. Description of <Resource> element attributes
2.2. Accessing the Data Source from a Java Servlet
3.1. Session Defaults Warning
1. Introduction
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.
1.1. Assumptions
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.
1.2. Purpose and Organization
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.
1.3. Version Information
Tomcat versions used are:
1.4. Supported Platform Matrix
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 |
| WebSphere 7.0 with IBM JVM 6.0 | u | u | u | 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 |
| WebLogic 10.3 with Sun JVM 6.0 | u | u | u | s | s | s |
| WebLogic 10.3 with JRockit JVM 6.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 |
| Tomcat 6.0 with Sun JVM 6.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.
1.5. C/C++ and Java Application Sharing of TeraGSS Security Configuration
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.
2. Tomcat 5.5 and Tomcat 6.0
2.1. Tomcat Data Source Definition
In Tomcat 5.5 and 6.0, a DataSource can be configured in a Context. A Context is an element in a XML file which represents a web application, which is running within a particular host.
2.1.1 Prerequisites
The following instructions assume that Tomcat is installed in the C:\Tomcat directory.
In Tomcat 5.5, files terajdbc4.jar and tdgssconfig.jar should be copied to the C:\Tomcat\common\lib directory.
In Tomcat 6.0, files terajdbc4.jar and tdgssconfig.jar should be copied to the C:\Tomcat\lib directory.
Note: When using the TTU 8.2 Teradata JDBC Driver 3.4 and earlier, tdgssjava.jar must also be included. This file is not used by the TTU 12.0 Teradata JDBC Driver and later.
Note: If you are sharing the TeraGSS Security Configuration (see section 1.5), replace tdgssconfig.jar with the directory containing TdgssUserConfigFile.xml
2.1.2 Context Configuration
Context elements may be explicitly defined:
- In the C:\Tomcat\conf\context.xml file. The Context element information will be loaded by all webapps. This is recommended for use with the Teradata JDBC driver.
- In the C:\Tomcat\conf\conf\[enginename]\[hostname]\context.xml.default file. The Context element information will be loaded by all webapps of that host.
- In individual files (with a ".xml" extension) in the C:\Tomcat\conf\conf\[enginename]\[hostname]\ directory. The name of the file (without the .xml extension) will be used as the context path. Multi-level context paths may be defined using the # character, i.e. context#path.xml. The default web application may be defined by using a file called ROOT.xml.
- If the previous file was not found for this application, in an individual file at \META-INF\context.xml inside the application files.
- Inside a Host element in the main conf\server.xml. This is not recommended by the Apache Tomcat documentation, because Tomcat must be restarted for the main conf\server.xml file to be reloaded.
The following instructions assume the use of the recommended technique of using the main conf\context.xml.
The following Resource definition should be placed in between the <context>....</context> element in conf\context.xml.
<Resource name="jdbc/TeradataDS"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.teradata.jdbc.TeraDriver"
url="jdbc:teradata://whomooz"
username="guest"
password="please"
maxActive="8"
maxIdle="8"
maxWait="-1" />
2.1.3 Description of <Resource> element attributes.
Attribute | Description | Example |
name | The name of the resource to be created, relative to the java:comp/env context. This must have prefix "jdbc/". | jdbc/TeradataDS |
auth | Specify whether the web Application code signs on to the corresponding resource manager programatically, or whether the Container will sign on to the resource manager on behalf of the application. The value of this attribute must be Application or Container. | Container |
type | The fully qualified Java class name expected by the web application when it performs a lookup for this resource. | javax.sql.DataSource |
driverClassName | The fully qualified Java class name of the Teradata JDBC driver.
Use com.teradata.jdbc.TeraDriver with the TTU 12.0 Teradata JDBC Driver and later.
Use com.ncr.teradata.TeraDriver with the TTU 8.2 Teradata JDBC Driver 3.4 and earlier. | com.teradata.jdbc.TeraDriver |
url | The connection URL to be passed to Teradata JDBC driver to establish a connection. | jdbc:teradata://whomooz |
UserName | The username for the Teradata Database. | guest |
Password | A valid password for the user name. | please |
maxActive | The maximum number of active connections that can be allocated from this pool at the same time.
Note: Tomcat documentation indicates that zero or a negative number may be specified to indicate unlimited; however, there are reports that some versions of Tomcat may hang when zero is specified. | Default is 8 |
maxIdle | The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit. | Default is 8 |
maxWait | The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely. | Default is -1 |
removeAbandoned | The removeAbandoned parameter controls Tomcat's behavior regarding orphaned connections. If set to true, a connection is considered abandoned and eligible to be reclaimed by the connection pool if it has been idle longer than the removeAbandonedTimeout. Setting this to true can recover connections from poorly written applications which fail to close a connection. Care must be taking when setting this to true, because Tomcat uses limited information in determining whether a connection is idle. For example, traversing a result set doesn't count as being used. | Default is false |
2.2. Accessing the Data Source from a Java Servlet
The following code creates the initial context:
Context ctx = new InitialContext(); To perform a JNDI lookup to obtain the Data Source, the following code can be used:
DataSource ds = (DataSource) ctx.lookup(jndiName); Note: The jndiName must use prefix "java:/comp/env" .
To obtain a connection from the Data Source, the following code can be used:
Connection con = ds.getConnection(); At this point, the connection obtained behaves the same way as one that was obtained from the Driver Manager.
Note:Tomcat 5.5 and 6.0 both do not support the Datasource.getConnection(username,password) method.
If this method is used, then the following exception will be thrown:
java.lang.UnsupportedOperationException: Not supported by BasicDataSource at
org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:899)
...
See Appendix A for a complete application.
3. Troubleshooting
3.1. Session Defaults Warning
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.
Appendix A: Tomcat Code Samples
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();
}
}