Teradata Download Center / Teradata JDBC Driver / Teradata JDBC Documentation

WebLogic Application Server "How-To" Guide

tnolan — Tue, 07 Apr 2009 18:31:37 GMT

How to use the Teradata JDBC Driver with WebLogic Server

Date: January 7, 2009

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 WebLogic application server.

TERADATA CONFIDENTIAL

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.

Appendix A: WebLogic Code Samples

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

The application server versions used are:

WebLogic Server 8.1
WebLogic Server 9.0
WebLogic Server 9.1
WebLogic Server 9.2
WebLogic Server 10.0
WebLogic Server 10.3

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

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.

1.6. Overview - Data Source in WebLogic Server

This section provides a quick overview of Data Source configuration in WebLogic. More detail is provided in subsequent sections.

The Teradata JDBC Driver can be used as a WebLogic Server data source. Within WebLogic Server, the use of a DataSource object, instead of DriverManager, is the preferred means of connecting to a data source.

To use the DataSource interface within WebLogic Server, the JDBC Connection Pool and JDBC Data Source will need to be configured for the Teradata JDBC Driver.

When configuring the JDBC Connection Pool through WebLogic server Admin Console, the following Teradata-specific configuration parameters are needed.

For the "Choose database" pane:

Database Type: Other
Database Driver: Other

For the "JDBC Data Source Properties" pane for WebLogic 9.x and WebLogic 10.x:

Database Type: Other
Database Driver: Other

NOTE: In WebLogic 9.1, "Other" is not available as a Database Type. Instead, an arbitrary value for the database type can be chosen. The database type of PointBase has been tested to work successfully with the Teradata JDBC Driver.

For the "Define and test connection" pane:

Driver Classname 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	`jdbc:teradata://`DataSourceName

NOTE: In WebLogic 9.x and WebLogic 10.x, the Driver Classname and the URL is defined in the "Test Database Connection" pane.

When configuring the JDBC Data Source through WebLogic server Admin Console, the following parameter settings are required for setting up a plain data source and TXDataSource.

For a "Plain Data Source":

[WebLogic 8.1]

Checkbox "Honor Global Transactions" should be unchecked.
Checkbox "Emulate Two-Phase Commit for non-XA Driver" should be checked.

[WebLogic 9.x and WebLogic 10.x]

Checkbox "Supports Global Transactions" should be unchecked.
Checkbox "Emulate Two-Phase Commit" should be unchecked.

For a "TXDataSource":

[WebLogic 8.1]

Checkbox "Honor Global Transactions" should be checked.
Checkbox "Emulate Two-Phase Commit for non-XA Driver" should be checked.

[WebLogic 9.x and WebLogic 10.x]

Checkbox "Supports Global Transactions" should be checked.
Checkbox "Emulate Two-Phase Commit" should be checked.

2. WebLogic 8.1

The recommended way to create a Connection Pool within WebLogic is to use the graphical interface of the administrative server.

2.1. Connection Pool via Administrative Console

2.1.1. Prerequisites

Prior to starting WebLogic, you must ensure that the CLASSPATH of the server refers to the jar files that make up the Teradata JDBC Driver.

For example, if you copied all of these to C:\TeraJDBC, then your CLASSPATH would need to include:

C:\TeraJDBC\terajdbc4.jar
C:\TeraJDBC\tdgssconfig.jar

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. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → BEA WebLogic Platform 8.1 → Examples → WebLogic Server Examples → Server Admin Console

2.1.3. Open JDBC Connection Pools

In the left pane, click to expand the "examples" node

Click to expand the "Services" node

Click to expand the "JDBC" node in Services

Click on "Connection Pools" in JDBC

The right pane will show all of the Connection Pools currently defined.

Click "Configure a new JDBC Connection Pool..."

2.1.4. Set Connection Pool Parameters

Select "Other" for the Database Type

"Other" is automatically selected for the Database Driver

Press "Continue"

Next, the details of the Teradata Connection Pool must be filled in.

2.1.5. Create the Pool

The values required to create the connection pool are listed in the following table. Please note that the username of "guest" and password of "please" are examples and should be replaced by the actual username/password combination for your Teradata Database system.

Field	Value	Example
Name	The name of the connection pool. This value is user-defined.	tera4
Driver Classname	The name of the class that implements the java.sql.Driver interface. 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	Defines the URL that will be passed to the driver. The example Teradata Database name is "whomooz". You should use the actual Teradata Database hostname for your site. Please note that any connection URL parameter such as "CHARSET" or "TMODE" must be set within this URL parameter. These CANNOT be set within the properties panel.	jdbc:teradata://whomooz
Database User Name	The Teradata Database username.	guest
Password	The password of the Teradata Database user. This can be used to override the password value in Properties. It allows you to avoid storing cleartext passwords. After this value is applied it will not be visible as cleartext in the panel.	please

Fill in the connection properties as shown in the figure below.

Click "Test Driver Configuration" to check for errors. If there are no errors, click "Continue".

2.1.6. Assign the Pool

To create and deploy the pool click "Create and deploy" as shown in the figure below.

2.2. Create JDBC Data Source

The JDBC Data Source allows you to access a connection pool. Using a Data Source enables an application to be independent of both the actual database being accessed and the type of JDBC driver being used.

2.2.1. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → BEA WebLogic Platform 8.1 → Examples → WebLogic Platform → Server Admin Console

2.2.2. Open JDBC Data Sources

In the left pane, click to expand the "examples" node

Click to expand the "Services" node

Click to expand the "JDBC" node in Services

Click the "Data Sources" node in JDBC

The right pane will show all of the Data Sources currently defined.

Select "Configure a new JDBC Data Source"

2.2.3. Set Data Source Parameters

The following table lists the parameters that must be set for a Data Source.

Field	Value	Example
Name	The user-defined display name for this DataSource.	tera4DataSource
JNDI Name	User-defined JNDI name of the DataSource.	tera4DS
Honor Global Transactions	Specifies whether the Data Source can be treated as a javax.sql.XADataSource that supports distributed transactions. This box should be checked if you are using Container Managed Persistence (CMP) Entity Beans. Otherwise, it should remain unchecked.	(unchecked)
Emulate Two-Phase Commit for non-XA Driver	Specifies that the JDBC driver is not a javax.sql.XADataSource, and that the JDBC driver does not support distributed transactions. The Teradata JDBC Driver does not currently support XA or distributed transactions.	(checked)

Enter the appropriate Teradata Database parameters, as shown in the figure below. Be sure that "Emulate Two-Phase Commit for non-XA Driver" is checked. Press "Continue" when you are finished.

Next, associate the JDBC data source with a connection pool, as shown in the figure below.

Select the connection pool name you defined in the last step from the Pool Name combo box.

Press "Continue".

2.2.4. Assign the Data Source

Select the target server. In this example, select "examplesServer" as shown in the figure below.

Press "Create" to create the data source.

2.3. Accessing the DataSource from a Java Application

2.3.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

WebLogic requires a valid Teradata Database username and password to be specified in the connection pool definition. The Teradata Database username and password specified in the connection pool definition are the ones that are always used to connect to the Teradata Database.

When the application calls DataSource.getConnection(), then the username and password specified in the connection pool definition are used to connect to the Teradata Database.

When the application calls DataSource.getConnection(username,password), then the username and password arguments to getConnection are validated against the WebLogic server realm.

If there is no WebLogic user defined in the WebLogic server realm that corresponds to the username argument to getConnection, then WebLogic will throw the following SQLException:

Message: "User: YourUserId, failed to be authenticated."
SQLState: null
Error code: 0

If the username and password arguments to getConnection are successfully validated against the WebLogic server realm, then the username and password specified in the connection pool definition are used to connect to the Teradata Database.

Note: The username and password arguments to getConnection are totally separate from the username and password specified in the connection pool definition. The username and password arguments to getConnection are used by WebLogic only, and are validated against the WebLogic server realm. The username and password arguments to getConnection are NOT used for the Teradata Database.

Note: This behavior of WebLogic differs from the JDBC API standard, and it differs from the behavior of other application servers.

2.3.2. Using the DataSource

The following code creates the initial context:

Context ctx = new InitialContext();

To perform a JNDI lookup to obtain the DataSource, the following code can be used:

DataSource ds = (DataSource) ctx.lookup(jndiName);

To obtain a connection from the DataSource, 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.

See Appendix A for a complete application.

2.4. Performance Parameters

The following performance parameters are visible after selecting a specific Connection Pool and then selecting the "Connections" tab within the WebLogic Administrator Console. Their setting can impact the performance of your application.

Maximum Capacity - too small of a value here can cause users of the pool to waste time waiting for someone to release a connection.

2.4.1 Advanced Options

Login Delay - this defaults to zero and should remain at that value. Inserting a value here could cause unnecessary delays.

Test Created Connections - selecting this option will hurt your performance with no apparent benefit to your application.

Test Reserved Connections - this determines whether connections are tested before being given to a client. Turning on this option will cause your application to run slightly slower, but it will take advantage of WebLogic's automatic testing of connections, so that your application won't need to handle the situation where the connection it gets is not working. Turning off this option will cause your application to run slightly faster, but it will not use WebLogic's automatic testing of connections, so that your application will need to handle the situation where the connection it gets is not working.

Test Table Name - a table the DataSource user has access to which contains a small number of rows. The SQL executed will be: select count(*) from TestTableName

Also note that there is a Monitoring Tab available that can help you track the performance of a Connection Pool.

3. WebLogic 9.x and WebLogic 10.x

The recommended way to create a Data Source and Connection Pool within WebLogic is to use the graphical interface of the administrative server.

3.1. Create JDBC Data Source

The creation of a Data Source and Connection Pool differs between WebLogic 9.x,WebLogic 10.x and previous versions of WebLogic that are supported by the Teradata JDBC Driver. In WebLogic 9.x and WebLogic 10.x, the Data Source and Connection Pool are not defined separately, instead the Connection Pool is created within the Data Source creation when defining the connection properties and parameters to test the data source configuration.

3.1.1. Prerequisites

Prior to starting WebLogic, you must ensure that the CLASSPATH of the server refers to the jar files that make up the Teradata JDBC Driver.

For example, if you copied all of these to C:\TeraJDBC, then your CLASSPATH would need to include:

C:\TeraJDBC\terajdbc4.jar
C:\TeraJDBC\tdgssconfig.jar

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

3.1.2. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → BEA Products → Examples → WebLogic Server → Start Examples Server

When the browser window is displayed for WEBLOGIC SERVER 9.x or WEBLOGIC SERVER 10.x Getting Started, Click "Start the Administration Console" at the top right.

Enter your username and password (default is weblogic)

3.1.3. Open JDBC Data Sources

In the left pane for Domain Structure, click to expand the "Services" node.

Click to expand the "JDBC" node in Services.

Click on "Data Sources" in JDBC.

The right pane will show all of the Data Sources currently defined.

Click "Lock & Edit" in the left pane for the Change Center.

Click "New" in the Data Sources pane.

3.1.4. Set Data Source Parameters

The following table lists the parameters that must be set for a Data Source.

Field	Value	Example
Name	The user-defined display name for this Data Source.	tera4DataSource
JNDI Name	User-defined JNDI name of the Data Source.	tera4DS
Database Type	The database type to be used. NOTE: In WebLogic 9.1, "Other" is not available as a database type. Since neither Teradata nor Other is available in this list, an arbitrary value for the database type can be chosen. The database type of PointBase has been tested to work successfully with the Teradata JDBC Driver.	Other
Database Driver	The database driver to use to create database connections.	Other

Enter the appropriate parameters, as shown in the figure below. Click "Next" when you are finished.

3.1.5. Set the Transaction Options

For the Transaction Options shown below, uncheck "Supports Global Transactions". Click "Next" when you are finished.

NOTE: In WebLogic 9.x and Weblogic 10.x, an option for transaction processing, e.g. Emulate Two-Phase Commit, can be set only if you selected Supports Global Transactions. For TXDataSource, both of the check boxes for "Supports Global Transactions" and "Emulate Two-Phase Commit" should be checked.

3.1.6. Set the Connection Properties

The following table lists the parameters to be set for Connection Properties which will be used to create the connection pool for this data source.

Please note that the username of "guest" and password of "please" are examples and should be replaced by the actual username/password combination for your Teradata Database system.

Field	Value	Example
Database Name	The example Teradata Database name is "whomooz". You should use the actual Teradata Database hostname for your site.	whomooz
Host Name	The name or IP address of the database server.	whomooz
Port	The port on the database server used to connect to the Teradata Database. NOTE: This port number setting is not currently used by the Teradata JDBC Driver to connect to the Teradata Database. However, this port number setting may be supported by the Teradata JDBC Driver in the future, so it should be set correctly for your Teradata Database system. The Teradata Database normally uses port 1025. If your Teradata Database system uses a port other than 1025, then the port number can be specified with the DBS_PORT connection URL parameter. Setting the connection URL is shown in the next section.	1025
Database User Name	The Teradata Database username.	guest
Password	The password of the Teradata Database user. This can be used to override the password value in Properties. It allows you to avoid storing cleartext passwords. After this value is applied it will not be visible as cleartext in the panel.	please

Enter the appropriate parameters, as shown in the figure below. Click "Next" to continue.

3.1.7. Test the Database Connection and Connection Properties

The following table lists the parameters that must be set for the Test Database Connection.

Field	Value	Example
Driver Class Name	The name of the class that implements the java.sql.Driver interface. This value will be used to define the driver class name for the connection pool for this data source. 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	Defines the URL that will be passed to the driver. The example Teradata Databasename is "whomooz". You should use the actual Teradata Database hostname for your site. This value will be used to define the URL for the connection pool for this data source. Please note that any connection URL parameter such as "CHARSET" or "TMODE" must be set within this URL parameter. These CANNOT be set within the properties panel.	jdbc:teradata://whomooz
Database User name	The Teradata Database username.	guest
Password	The password of the Teradata Database user. This can be used to override the password value in Properties. It allows you to avoid storing cleartext passwords. After this value is applied it will not be visible as cleartext in the panel.	please
Confirm Password	Re-enter the password of the Teradata Database user.	please
Properties	The properties to pass to the JDBC driver when creating the database connection.	user=guest
Test Table Name	The table name or SQL statement to use to test the database connection. NOTE: This property is optional.	SQL select count(*) from TestTableName

Enter the appropriate parameters, as shown in the figure below. Click "Test Configuration" when you are finished. In the Messages window, you should receive "Connection test succeeded." if your connection was successful. Click "Next" to continue.

3.1.8. Assign the Data Source

Select the target to deploy the JDBC Data Source. examplesServer is used for this Data Source.

Click "Finish" to complete the Data Source creation.

Click the "Activate Change" button in the Change Center to activate the changes.

The Data Source that you just created should now appear in the table for the Data Sources for this domain.

3.2. Accessing the Data Source from a Java Application

3.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

When the application calls DataSource.getConnection(), then the username and password specified in the connection pool definition are used to connect to the Teradata Database.

When the application calls DataSource.getConnection(username,password), then the username and password arguments to getConnection are validated against the WebLogic server realm.

If there is no WebLogic user defined in the WebLogic server realm that corresponds to the username argument to getConnection, then WebLogic will throw the following SQLException:

Message: "User: YourUserId, failed to be authenticated."
SQLState: null
Error code: 0

Note: The username and password arguments to getConnection are totally separate from the username and password specified in the connection pool definition. The username and password arguments to getConnection are used by WebLogic only, and are validated against the WebLogic server realm. The username and password arguments to getConnection are NOT used for the Teradata Database.

Note: This behavior of WebLogic differs from the JDBC API standard, and it differs from the behavior of other application servers.

3.2.2. Using the Data Source

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

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.

See Appendix A for a complete application.

3.3. Performance Parameters

The following performance parameters are visible after selecting Connection Pool under the "Configuration" tab for a Data Source within the WebLogic Administrator Console. Their setting can impact the performance of your application.

Maximum Capacity - This value specifies the maximum number of connections that the pool can contain. If this value is set smaller than the number of connections normally in use, then application response time may be negatively impacted as the application must wait for connections to be returned to the pool before proceeding.

3.3.1 Advanced Options

Login Delay - this defaults to zero and should remain at that value. Inserting a value here could cause unnecessary delays.

Test Connections on Reserve - this determines whether connections are tested before being given to a client. Turning on this option will cause your application to run slightly slower, but it will take advantage of WebLogic's automatic testing of connections, so that your application won't need to handle the situation where the connection it gets is not working. Turning off this option will cause your application to run slightly faster, but it will not use WebLogic's automatic testing of connections, so that your application will need to handle the situation where the connection that is obtained from the pool may not work.

Test Table Name - a table the Data Source user has access to which contains a small number of rows. The SQL executed will be: select count(*) from TestTableName

Also note that there is a Monitoring Tab available that can help you track the performance of a data source.

4. WebLogic 10.3

The recommended way to create a Data Source and Connection Pool within WebLogic is to use the graphical interface of the administrative server.

4.1. Create JDBC Data Source

The creation of a Data Source and Connection Pool differs between WebLogic 10.x and pre 9.1 versions of WebLogic that are supported by the Teradata JDBC Driver. In WebLogic 10.x, the Data Source and Connection Pool are not defined separately, instead the Connection Pool is created within the Data Source creation when defining the connection properties and parameters to test the data source configuration.

4.1.1. Prerequisites

Prior to starting WebLogic, you must ensure that the CLASSPATH of the server refers to the jar files that make up the Teradata JDBC Driver.

For example, if you copied all of these to C:\TeraJDBC, then your CLASSPATH would need to include:

C:\TeraJDBC\terajdbc4.jar
C:\TeraJDBC\tdgssconfig.jar

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

4.1.2. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → Oracle WebLogic → WebLogic Server 10gR3 → Examples → Start Examples Server

When the browser window is displayed for WEBLOGIC SERVER Getting Started, Click "Start the Administration Console" at the top right.

Enter your username and password (default is weblogic)

4.1.3. Open JDBC Data Sources

In the left pane for Domain Structure, click to expand the "Services" node.

Click to expand the "JDBC" node in Services.

Click on "Data Sources" in JDBC.

The right pane will show all of the Data Sources currently defined.

Click "New" in the Data Sources pane.

4.1.4. Set Data Source Parameters

The following table lists the parameters that must be set for a Data Source.

Field	Value	Example
Name	The user-defined display name for this Data Source.	tera4DataSource
JNDI Name	User-defined JNDI name of the Data Source.	tera4DS
Database Type	The database type to be used.	Other
Database Driver	The database driver to use to create database connections.	Other

Enter the appropriate parameters, as shown in the figure below. Click "Next" when you are finished.

4.1.5. Set the Transaction Options

For the Transaction Options shown below, uncheck "Supports Global Transactions". Click "Next" when you are finished.

4.1.6. Set the Connection Properties

The following table lists the parameters to be set for Connection Properties which will be used to create the connection pool for this data source.

Please note that the username of "guest" and password of "please" are examples and should be replaced by the actual username/password combination for your Teradata Database system.

Field	Value	Example
Database Name	The example Teradata Database name is "xpsys03". You should use the actual Teradata Database hostname for your site.	xpsys03
Host Name	The name or IP address of the database server.	xpsys03
Port	The port on the database server used to connect to the Teradata Database. NOTE: This port number setting is not currently used by the Teradata JDBC Driver to connect to the Teradata Database. However, this port number setting may be supported by the Teradata JDBC Driver in the future, so it should be set correctly for your Teradata Database system. The Teradata Database normally uses port 1025. If your Teradata Database system uses a port other than 1025, then the port number can be specified with the DBS_PORT connection URL parameter. Setting the connection URL is shown in the next section.	1025
Database User Name	The Teradata Database username.	guest
Password	The password of the Teradata Database user. This can be used to override the password value in Properties. It allows you to avoid storing cleartext passwords. After this value is applied it will not be visible as cleartext in the panel.	please

Enter the appropriate parameters, as shown in the figure below. Click "Next" to continue.

4.1.7. Test the Database Connection and Connection Properties

The following table lists the parameters that must be set for the Test Database Connection.

Field	Value	Example
Driver Class Name	The name of the class that implements the java.sql.Driver interface. This value will be used to define the driver class name for the connection pool for this data source. 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	Defines the URL that will be passed to the driver. The example Teradata Databasename is "xpsys03". You should use the actual Teradata Database hostname for your site. This value will be used to define the URL for the connection pool for this data source. Please note that any connection URL parameter such as "CHARSET" or "TMODE" must be set within this URL parameter. These CANNOT be set within the properties panel.	jdbc:teradata://xpsys03
Database User name	The Teradata Database username.	guest
Password	The password of the Teradata Database user. This can be used to override the password value in Properties. It allows you to avoid storing cleartext passwords. After this value is applied it will not be visible as cleartext in the panel.	please
Confirm Password	Re-enter the password of the Teradata Database user.	please
Properties	The properties to pass to the JDBC driver when creating the database connection.	user=guest
Test Table Name	The table name or SQL statement to use to test the database connection. NOTE: This property is optional.	SQL select count(*) from TestTableName

4.1.8. Assign the Data Source

Select the target to deploy the JDBC Data Source. examplesServer is used for this Data Source.

Click "Finish" to complete the Data Source creation.

The Data Source that you just created should now appear in the table for the Data Sources for this domain.

4.2. Accessing the Data Source from a Java Application

4.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

When the application calls DataSource.getConnection(), then the username and password specified in the connection pool definition are used to connect to the Teradata Database.

When the application calls DataSource.getConnection(username,password), then the username and password arguments to getConnection are validated against the WebLogic server realm.

If there is no WebLogic user defined in the WebLogic server realm that corresponds to the username argument to getConnection, then WebLogic will throw the following SQLException:

Message: "User: YourUserId, failed to be authenticated."
SQLState: null
Error code: 0

Note: This behavior of WebLogic differs from the JDBC API standard, and it differs from the behavior of other application servers.

4.2.2. Using the Data Source

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

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.

See Appendix A for a complete application.

4.3. Performance Parameters

The following performance parameters are visible after selecting Connection Pool under the "Configuration" tab / "Connection Pool" subtab - for a Data Source within the WebLogic Administrator Console. Their setting can impact the performance of your application.

4.3.1 Advanced Options

Login Delay - this defaults to zero and should remain at that value. Inserting a value here could cause unnecessary delays.

Test Table Name - a table the Data Source user has access to which contains a small number of rows. The SQL executed will be: select count(*) from TestTableName

Also note that there is a Monitoring Tab available that can help you track the performance of a data source.

5. Troubleshooting

5.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.

5.2. Preparation of CMP Entity Beans for Deployment

To manage transactions correctly for contained-managed persistence (CMP) entity beans, the WebLogic EJB container requires a TXDataSource (not a plain data source) to be specified.

[WebLogic 8.1]

To configure a new TXDataSource using WebLogic server Admin Console, both the "Honor Global Transactions" checkbox and "Emulate Two-Phase Commit for non-XA Driver" checkbox need to be checked.

[WebLogic 9.x and WebLogic 10.x]

To configure a new TXDataSource using WebLogic server Admin Console, both the "Supports Global Transactions" checkbox and "Emulate Two-Phase Commit" checkbox need to be checked.

Appendix A: WebLogic 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();
   }
 }

WebSphere Application Server "How-To" Guide

tnolan — Tue, 07 Apr 2009 18:30:43 GMT

How to use the Teradata JDBC Driver with IBM WebSphere Application Server and WebSphere Studio Application Developer 5.1.2

Date: February 4, 2009

TERADATA CONFIDENTIAL

1. Introduction

2. IBM WebSphere 5.1

2.1. Connection Pool via Administrative Console
2.1.1. Start the Administrative Console
2.1.2. Add the Teradata JDBC Driver
2.1.3. Configure the Driver
2.1.4. Create the Data Source
2.1.5. Configure the Data Source
2.1.6. Set Data Source Custom Properties
2.2. Accessing the DataSource via a Java Servlet
2.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)
2.2.2. Using the DataSource
2.3. Accessing the DataSource with WebSphere Java 2 Security Enforced
2.3.1. Enforce Java 2 security on WebSphere 5.1
2.3.2. Using the DataSource
2.4. Performance Parameters

3. IBM WebSphere 6.0

3.1. Connection Pool via Administrative Console
3.1.1. Start the Administrative Console
3.1.2. Add the Teradata JDBC Driver
3.1.3. Configure the Driver
3.1.4. Create the Data Source
3.1.5. Configure the Data Source
3.1.6. Set Data Source Custom Properties
3.2. Accessing the DataSource via a Java Servlet
3.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)
3.2.2. Using the DataSource
3.3. Accessing the DataSource with WebSphere Java 2 Security Enforced
3.3.1. Enforce Java 2 security on WebSphere 6.0
3.3.2. Using the DataSource
3.4. Performance Parameters

4. IBM WebSphere 6.1

4.1. Connection Pool via Administrative Console
4.1.1. Start the Administrative Console
4.1.2. Add the Teradata JDBC Driver
4.1.3. Configure the Driver
4.1.4. Create the Data Source
4.1.5. Configure the Data Source
4.1.6. Set Data Source Custom Properties
4.2. Accessing the DataSource via a Java Servlet
4.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)
4.2.2. Using the DataSource
4.3. Accessing the DataSource with WebSphere Java 2 Security Enforced
4.3.1. Enforce Java 2 security on WebSphere 6.1
4.3.2. Using the DataSource
4.4. Performance Parameters

5. IBM WebSphere 7.0

5.1. Connection Pool via Administrative Console
5.1.1. Start the Administrative Console
5.1.2. Add the Teradata JDBC Driver
5.1.3. Configure the Driver
5.1.4. Create the Data Source
5.1.5. Configure the Data Source
5.1.6. Set Data Source Custom Properties
5.2. Accessing the DataSource via a Java Servlet
5.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)
5.2.2. Using the DataSource
5.3. Accessing the DataSource with WebSphere Java 2 Security Enforced
5.3.1. Enforce Java 2 security on WebSphere 6.1
5.3.2. Using the DataSource
5.4. Performance Parameters

6. WebSphere Studio Application Developer

6.1. Connection Pool via Application Developer Interface
6.1.1. Start the Administrative Console
6.1.2. Open the Server Perspective
6.1.3. Create Server and Server Configuration
6.1.4. Create Server Data Source
6.1.5. Configure Data Source

7. Troubleshooting

7.1. JNDI Call Failure
7.2. Servlet Version
7.3. Generic Data Store Helper
7.4. Session Defaults Warning
7.5. Preparation of CMP Entity Beans for Deployment

Appendix A: WebSphere Code Samples

1. Introduction

1.1. Assumptions

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.

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

The application server versions used are:

IBM WebSphere 5.1
IBM WebSphere 6.0
IBM WebSphere 6.1
IBM WebSphere 7.0

Other tools covered are:

IBM WebSphere Studio Application Developer 5.1.2

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

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.

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.

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.

1.5. C/C++ and Java Application Sharing of TeraGSS Security Configuration

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.

jar uvf tdgssconfig.jar TdgssUserConfigFile.xml

1.6. Overview - Data Source in WebSphere Application Server

The Teradata JDBC Driver can be used as a WebSphere version 5.x data source or a WebSphere version 4.x data source.

To use the DataSource interface within WebSphere Application Server, the JDBC Provider and JDBC Data Source will need to be configured for Teradata JDBC type 4 driver.

When configuring the JDBC Provider through WebSphere server Admin Console, the following Teradata-specific configuration parameters are needed.

Classpath:	`terajdbc4.jar` `tdgssconfig.jar` 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`
Implementation Classname:	`com.teradata.jdbc.TeraConnectionPoolDataSource` Use `com.teradata.jdbc.TeraConnectionPoolDataSource` with the TTU 12.0 Teradata JDBC Driver and later. Use `com.ncr.teradata.TeraConnectionPoolDataSource` with the TTU 8.2 Teradata JDBC Driver 3.4 and earlier.

When configuring the JDBC Data Source for the Teradata JDBC Provider, the following Custom Properties are needed.

DSName: the Teradata data source name or IP address
user: the username to connect to the data source
password: the password to connect to the data source

For container-managed persistence (CMP) entity beans: On the data source "Configuration" pane, check the checkbox "Use this Data Source in container managed persistence (CMP)."

1.7. Overview - Data Source in WebSphere Studio Application Developer 5.1.2

To use the DataSource interface within WebSphere Studio Application Developer 5.1.2 test server, the JDBC Provider and JDBC Data Source will need to be configured for Teradata JDBC type 4 driver

When configuring the JDBC Provider for the WebSphere Studio Application Developer 5.1.2 Test Server, the following Teradata-specific configuration parameters are needed:

Database type:	user-defined
JDBC provider type:	user-defined JDBC Provider
Implementation class name:	`com.teradata.jdbc.TeraConnectionPoolDataSource` Use `com.teradata.jdbc.TeraConnectionPoolDataSource` with the TTU 12.0 Teradata JDBC Driver and later. Use `com.ncr.teradata.TeraConnectionPoolDataSource` with the TTU 8.2 Teradata JDBC Driver 3.4 and earlier.
Class path:	`terajdbc4.jar` `tdgssconfig.jar` 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`

When configuring the JDBC Data Source for the Teradata JDBC Provider, the following parameters are needed:

Data source helper class name:	`com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper`
DSName:	the Teradata Database hostname
user:	the Teradata Database username
password:	the Teradata Database user's password

For container-managed persistence (CMP) entity beans, on the data source "Configuration" dialog window, check the checkbox "Use this Data Source in container managed persistence (CMP)."

2. IBM WebSphere 5.1

A Connection Pool is created via the graphical interface of the administrative server.

Please note that there is more than one GUI interface available for the IBM WebSphere product. The information provided here, using WebSphere Application Server 5.1, should be sufficient to allow a user to accomplish the same tasks with the other available GUIs.

2.1. Connection Pool via Administrative Console

2.1.1. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → IBM WebSphere → Application Server v5.1 → Administrative Console

2.1.2. Add the Teradata JDBC Driver

Under "Resources", Click "JDBC Providers" in the left pane.

Press "New" in the right pane.

In the next Window, select "User Defined JDBC Driver" from the list of JDBC Providers in the right pane and click "OK".

2.1.3. Configure the Driver

Now the Driver must be configured as shown below.

The required configuration information is listed in the following table.

Field	Value	Example
Name	The name of the driver. This is user-defined.	Teradata Driver
Description	User-defined.	Teradata JDBC
Classpath	The full path name of the jar files that make up the Teradata JDBC Driver. The example assumes that you have copied these files to the C:\TeraJDBC directory on your system. A path on a Linux or UNIX machine would use forward slashes to separate its components.	`C:\TeraJDBC\terajdbc4.jar` `C:\TeraJDBC\tdgssconfig.jar` 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`
Implementation Classname	The name of the Teradata JDBC Driver data source class.	`com.teradata.jdbc.TeraConnectionPoolDataSource` Use `com.teradata.jdbc.TeraConnectionPoolDataSource` with the TTU 12.0 Teradata JDBC Driver and later. Use `com.ncr.teradata.TeraConnectionPoolDataSource` with the TTU 8.2 Teradata JDBC Driver 3.4 and earlier.

Press "OK" to submit the changes.

Note that "TeradataDriver" will now appear in the list of JDBC Providers. To create a data source, click "TeradataDriver".

2.1.4. Create the Data Source

The following figure shows how to create a Data Source.

Click "New" in the "Data Sources" pane. Next, the connection pool is defined.

Note: A version 4.x Data Source can also be configured. The required fields are a subset of version 5.x and so are not listed here. The "Database name", "Default User Id" and "Default User password" of a version 4.x data source will override any values defined as custom properties.

2.1.5. Configure the Data Source

Configure the Data Source by entering the data as shown in the figure below.

The required configuration information is listed in the following table.

Field	Value	Example
Name	The user-defined display name for this resource.	Teradata
JNDI Name	User-defined JNDI name of the DataSource.	jdbc/teradata4
Container Managed Persistence	Set if Data Source will be used for CMP of EJBs.	Check this box if the Data Source will be used with CMP entity beans. Otherwise, leave it unchecked.

After the configuration information is filled in, click "OK".

Now, scroll to the bottom of the right pane and click "Custom Properties".

2.1.6. Set Data Source Custom Properties

The following figure shows how to set the custom properties of the Data Source such as user name, password and data source name.

Click "New" to create a custom property for each of the values shown in the table below. All of this data is defined by the user.

Note: The "Database name", "Default User Id" and "Default User password" of a version 4.x data source will override any values defined as custom properties

Name	Description	Value
DSName	The hostname of the Teradata Database.	whomooz
user	The Teradata Database username.	guest
password	The password.	please

In addition to the above parameters, all of the parameters that can be specified in a Teradata JDBC connection URL can also be specified here.

When using TTU 8.0 Teradata JDBC Driver 3.2, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.02.00, B035-2403-104A for more details.
When using TTU 8.1 Teradata JDBC Driver 3.3, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.03.00, B035-2403-115A for more details.
When using TTU 8.2 Teradata JDBC Driver 3.4, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.04.00, B035-2403-096A for more details.
When using TTU 12.0 Teradata JDBC Driver, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 12.00.00, B035-2403-067A for more details.
When using TTU 13.0 Teradata JDBC Driver, please refer to the Teradata JDBC Driver User Guide, Release 13.00.00, B035-2403-088A for more details.

Click "Save" to save your changes. The connection can be tested by selecting the data source and clicking "Test Connection" as shown in the figure below. Please note that the values shown above specify a specific account on a specific database. You will need to modify the value portion of these properties to match the Teradata Database you are accessing.

You should see a message similar to "Test Connection for datasource Teradata on server server1 at node CHARLIE was successful."

2.2. Accessing the DataSource via a Java Servlet

A Java servlet can access a data source to get pooled connections.

2.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

With WebSphere, a JDBC Provider is defined to specify information about the JDBC driver. After the JDBC Provider has been defined, then one or more Data Sources can be configured for the JDBC Provider. After each Data Source has been defined, Custom Properties can be configured for each Data Source.

The Data Source's "user" and "password" Custom Properties are optional.

If "user" and "password" Custom Properties are defined, then they serve as the default values when an application calls DataSource.getConnection().
If "user" and "password" Custom Properties are defined, then the application can override those default values by calling DataSource.getConnection(username,password).
If "user" and "password" Custom Properties are not defined, then an application must call DataSource.getConnection(username,password) in order to connect to the database.

In other words, user and password values must be specified either as Custom Properties, or as arguments to DataSource.getConnection(username,password), or both; and when both are specified, then the arguments to DataSource.getConnection(username,password) override the "user" and "password" Custom Properties defined for a Data Source.

A SQLException will be thrown if user and password values are not specified in either Custom Properties, or as arguments to DataSource.getConnection(username,password).

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata JDBC Driver] : Single Sign-On NOT supported for Mechanism TD2.
SQLState: HY000
Error code: 1032

Message: [Teradata Database] : User identification is not authorized.
SQLState: 08004
Error code: 3004

A SQLException will be thrown if user and password values are specified, but the specified user or password are invalid. Note that invalid user and password values specified as arguments to DataSource.getConnection(username,password) will override correct user and password values specified in Custom Properties. When DataSource.getConnection(username,password) is called but authentication fails, WebSphere does not "fallback" to use the values specified in Custom Properties.

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata Database] : Invalid password.
SQLState: 28000
Error code: 3003

Message: [Teradata Database] : User identification is not authorized.
SQLState: 08004
Error code: 3004

2.2.2. Using the DataSource

The servlet is accessed via a URL similar to the following:

http://server/servlet/servletname

In the example, the username, password and datasource name are all specified in the URL parameters:

http://charlie/servlet/SampleJdbcServlet?user=guest&password=please&datasource=teradata4

Note that user-defined variables are in italics.

Accessing the URL Parameters

 String user = req.getParameter("user"); 
 String password = req.getParameter("password"); 
 String datasource = req.getParameter("datasource");

Creating the Initial Naming Context

Context ctx = new InitialContext();

Lookup the Data Source

DataSource ds = (DataSource) ctx.lookup("jdbc/" + datasource);

Use the Data Source to get Connections

Connection con = ds.getConnection(user, password);

At this point, the connection obtained behaves the same way as one obtained from the Driver Manager.

See Appendix A for a complete Application.

2.3. Accessing the DataSource with WebSphere Java 2 Security Enforced

An application can access a data source when WebSphere Java 2 security is enforced.

2.3.1. Enforce Java 2 security on WebSphere 5.1

To enforce Java 2 security on WebSphere 5.1, follow the procedures below:

Logon to WebSphere 5.1 administrative console
Select and expand "Security" on the left navigation pane
Click on Global security
Check the checkbox for "Enforce Java 2 security"
Click "Apply"
Click "Save" to save your workspace changes to the master configuration
The server may need to be restarted for the changes to take effect

2.3.2. Using the DataSource

The type 4 Teradata Driver for the JDBC Interface mimics the Communication Processor (COP) discovery behavior of CLI. The type 4 driver first finds all defined COPs. Starting with "COP1", which is appended to the database hostname, and then proceeds with "COP2", "COP3", "..., COPn" until an undefined name is reached.

The above COP discovery approach used by Teradata JDBC driver requires security permissions to be defined in WebSphere dynamic policy files, including app.policy and was.policy. The permission definitions in these dynamic policy files will be described below.

Defining permissions in app.policy

The app.policy file is located in the following WebSphere directory location:

<WebSphere-Installation-Root>/config/cells/<cell-name>/nodes/<node-name>

The app.policy file contains default permissions for all of the enterprise applications in the cell. If security permissions are required for all of the enterprise applications in the cell, these permissions can be defined in app.policy, however, if some security permissions only apply to some specific WebSphere Application Server enterprise application, those permissions must be defined in was.policy, which will be described in the following section.

WebSphere Application Server has the following default permission defined in the app.policy file:

permission java.net.SocketPermission "*", "connect";

This permission is included in the following segment in app.policy:

 grant codeBase "file:${webComponent}" {
 permission java.io.FilePermission "${was.module.path}${/}-", "read, write";
 permission java.lang.RuntimePermission "loadLibrary.*";
 permission java.lang.RuntimePermission "queuePrintJob";
 permission java.net.SocketPermission "*", "connect";
 permission java.util.PropertyPermission "*", "read";
 };

The Teradata JDBC Driver's COP discovery technique is compatible with the default permission in the default app.policy file. However, when a site removes that default permission in order to tighten security, then additional specific permissions are required to support the Teradata JDBC Driver's COP discovery.

Assume there are n nodes of Teradata Database system, then the following permissions are required:

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";

These permissions must be inserted into app.policy like:

 grant codeBase "file:${webComponent}" {
 permission java.io.FilePermission "${was.module.path}${/}-", "read, write";
 permission java.lang.RuntimePermission "loadLibrary.*";
 permission java.lang.RuntimePermission "queuePrintJob";
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 permission java.util.PropertyPermission "*", "read";
 };

NOTE: If these permissions apply to a specific application only, they should not be defined in app.policy and instead they must be defined in was.policy. The permission definition in was.policy is described below.

Defining permissions in was.policy

The was.policy file is packaged in an enterprise archive (EAR) file. The was.policy file contains application-specific permissions for an WebSphere Application Server enterprise application.

The following permissions will be required in was.policy to meet the requirements for the COP discovery approach of Teradata JDBC driver. It should be noted that these permissions in was.policy are for the specific application only where it is packaged.

Assume there are n nodes of Teradata Database system, then following permissions must be defined:

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";

These permissions must be inserted into was.policy like:

 grant codeBase "file:${webComponent}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 };

or:

 grant codeBase "file:${application}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 };

Note: When more nodes are added to the Teradata Database system, for example, m more nodes, the following additional permissions must be added to was.policy (if these permissions need to apply to all the applications in the cell, then they must be defined in the app.policy file):

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 permission java.net.SocketPermission "hostnameCOPn+1", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn+m", "connect";

Besides the above SocketPermissions, RuntimePermission and FilePermission may also be needed. Depending upon WebSphere application server configurations and the enterprise application structures, the following permissions may be required in was.policy (if these permissions need to apply to all the applications in the cell, then they must be defined in the app.policy file).

Note that you need to substitute the actual directory where the Teradata JDBC Driver is located for "C:/teradatajdbcdriver" shown in the examples below.

 grant codeBase "file:${application}" {
 permission java.lang.RuntimePermission "getClassLoader";
 };
 
 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/terajdbc4.jar", "read";
 };
 
 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/tdgssconfig.jar", "read";
 };

An additional permission must be granted when using the TTU 8.2 Teradata JDBC Driver 3.4 and earlier. This permission is not required when using the TTU 12.0 Teradata JDBC Driver and later.

 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/tdgssjava.jar", "read";
 };

Expected java.security.AccessControlException in WebSphere SystemOut.log file

The COP discovery approach used by Teradata JDBC driver first finds all defined COPs. Starting with "COP1", which is appended to the database hostname, and then proceeds with "COP2", "COP3", "..., COPn" until an undefined name is reached. When this undefined name is encountered, a java.security.AccessControlException is thrown by WebSphere Java 2 security permission configuration. The Teradata JDBC driver handles this Java 2 security exception properly so the COP discovery process will be completed successfully and the application will be able to use the data source without any issues.

However, the above Java 2 security exception will still be logged into WebSphere SystemOut.log file since this AccessControlException is generated by WebSphere Java 2 security configuration and the Teradata JDBC driver has no control over WebSphere Java 2 security configuration.

Assume there are 4 nodes of Teradata Database system, and the following permissions have been defined in was.policy:

 grant codeBase "file:${webComponent}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 permission java.net.SocketPermission "hostnameCOP3", "connect";
 permission java.net.SocketPermission "hostnameCOP4", "connect";
 };

The WebSphere Java 2 security exception similar to the following contents will be logged into SystemOut.log file:

 [11/13/06 14:15:20:836 PST]  c6cf176 SecurityManag W SECJ0314W: Current Java 2 Security policy reported a potential violation of Java 2 Security Permission. Please refer to Problem Determination Guide for further information.
 
 Permission:
 
       hostnameCOP5 : access denied (java.net.SocketPermission hostnameCOP5 resolve)
 
 Code:
 
      com.teradata.jdbc.jdbc_4.io.TDNetworkIO1_3Impl  in  {file:/C:/teradatajdbcdriver/terajdbc4.jar}
 
 Stack Trace:
 
 java.security.AccessControlException: access denied (java.net.SocketPermission ginghamCOP5 resolve)
  at java.security.AccessControlContext.checkPermission(AccessControlContext.java(Compiled Code))
  at java.security.AccessController.checkPermission(AccessController.java(Compiled Code))
  at java.lang.SecurityManager.checkPermission(SecurityManager.java(Compiled Code))
  at com.ibm.ws.security.core.SecurityManager.checkPermission(SecurityManager.java(Compiled Code))
  at java.lang.SecurityManager.checkConnect(SecurityManager.java:1054)
  at java.net.InetAddress.getAllByName0(InetAddress.java:1009)
  at java.net.InetAddress.getAllByName0(InetAddress.java:990)
  at java.net.InetAddress.getAllByName(InetAddress.java:984)

NOTE: Since the Teradata JDBC driver COP discovery logic handles this Java 2 security exception properly so the application will be able to use the data source without any issues.

2.4. Performance Parameters

After a DataSource has been selected and its properties have been displayed, select "Connection Pool" in the "Additional Properties" panel. This will display the values for properties such as "Connection Timeout", "Max Connections" etc.

Max Connections - too small of a value here can cause users of the pool to waste time waiting for someone to release a connection.

On the page where these values are set, the help text, which can be displayed by clicking the "[i]" box at the right of value, will provide detailed information on the settings and information on how these settings can impact performance.

3. IBM WebSphere 6.0

A Connection Pool is created via the graphical interface of the administrative server.

Please note that there is more than one GUI interface available for the IBM WebSphere product. The information provided here, using WebSphere Application Server 6.0, should be sufficient to allow a user to accomplish the same tasks with the other available GUIs.

3.1. Connection Pool via Administrative Console

3.1.1. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → IBM WebSphere → Application Server v6 → Profiles → default → Administrative console

3.1.2. Add the Teradata JDBC Driver

Under "Resources", Click "JDBC Providers" in the left pane.

Press "New" in the right pane.

In the next Window, select "User Defined" from the list of "select Database type" in the right pane and click "Next".

3.1.3. Configure the Driver

Now the Driver must be configured as shown below.

The required configuration information is listed in the following table.

Field	Value	Example
Name	The name of the driver. This is user-defined.	Teradata Driver
Description	User-defined.	Teradata JDBC
Classpath	The full path name of the jar files that make up the Teradata JDBC Driver. The example assumes that you have copied these files to the C:\TeraJDBC directory on your system. A path on a Linux or UNIX machine would use forward slashes to separate its components.	`C:\TeraJDBC\terajdbc4.jar` `C:\TeraJDBC\tdgssconfig.jar` 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`
Implementation Classname	The name of the Teradata JDBC Driver data source class.	`com.teradata.jdbc.TeraConnectionPoolDataSource` Use `com.teradata.jdbc.TeraConnectionPoolDataSource` with the TTU 12.0 Teradata JDBC Driver and later. Use `com.ncr.teradata.TeraConnectionPoolDataSource` with the TTU 8.2 Teradata JDBC Driver 3.4 and earlier.

Press "OK" to submit the changes.

Note that "TeradataDriver" will now appear in the list of JDBC Providers. To create a data source, click "TeradataDriver".

3.1.4. Create the Data Source

The following figure shows how to create a Data Source.

Click "New" in the "Data Sources" pane. Next, the connection pool is defined.

3.1.5. Configure the Data Source

Configure the Data Source by entering the data as shown in the figure below.

The required configuration information is listed in the following table.

Field	Value	Example
Name	The user-defined display name for this resource.	Teradata
JNDI Name	User-defined JNDI name of the DataSource.	jdbc/teradata4
Container Managed Persistence	Set if Data Source will be used for CMP of EJBs.	Check this box if the Data Source will be used with CMP entity beans. Otherwise, leave it unchecked.
Data store helper class	Data store helper classes provided by WebSphere	Select the option "Generic data store helper".

After the configuration information is filled in, click "OK".

Now, under "Additional Properties" in the right pane, click "Custom Properties".

3.1.6. Set Data Source Custom Properties

The following figure shows how to set the custom properties of the Data Source such as user name, password and data source name.

Click "New" to create a custom property for each of the values shown in the table below. All of this data is defined by the user.

Note: The "Database name", "Default User Id" and "Default User password" of a version 4.x data source will override any values defined as custom properties

Name	Description	Value
DSName	The hostname of the Teradata Database.	whomooz
user	The Teradata Database username.	guest
password	The password.	please

In addition to the above parameters, all of the parameters that can be specified in a Teradata JDBC connection URL can also be specified here.

When using TTU 8.0 Teradata JDBC Driver 3.2, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.02.00, B035-2403-104A for more details.
When using TTU 8.1 Teradata JDBC Driver 3.3, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.03.00, B035-2403-115A for more details.
When using TTU 8.2 Teradata JDBC Driver 3.4, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.04.00, B035-2403-096A for more details.
When using TTU 12.0 Teradata JDBC Driver, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 12.00.00, B035-2403-067A for more details.
When using TTU 13.0 Teradata JDBC Driver, please refer to the Teradata JDBC Driver User Guide, Release 13.00.00, B035-2403-088A for more details.

You should see a message similar to "Test Connection for datasource Teradata on server server1 at node CHARLIE was successful."

3.2. Accessing the DataSource via a Java Servlet

A Java servlet can access a data source to get pooled connections.

3.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

The Data Source's "user" and "password" Custom Properties are optional.

If "user" and "password" Custom Properties are defined, then they serve as the default values when an application calls DataSource.getConnection().
If "user" and "password" Custom Properties are defined, then the application can override those default values by calling DataSource.getConnection(username,password).
If "user" and "password" Custom Properties are not defined, then an application must call DataSource.getConnection(username,password) in order to connect to the database.

A SQLException will be thrown if user and password values are not specified in either Custom Properties, or as arguments to DataSource.getConnection(username,password).

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata JDBC Driver] : Single Sign-On NOT supported for Mechanism TD2.
SQLState: HY000
Error code: 1032

Message: [Teradata Database] : User identification is not authorized.
SQLState: 08004
Error code: 3004

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata Database] : Invalid password.
SQLState: 28000
Error code: 3003

Message: [Teradata Database] : User identification is not authorized.
SQLState: 08004
Error code: 3004

3.2.2. Using the DataSource

The servlet is accessed via a URL similar to the following:

http://server/servlet/servletname

In the example, the username, password and datasource name are all specified in the URL parameters:

http://charlie/servlet/SampleJdbcServlet?user=guest&password=please&datasource=jdbc/teradata4

Note that user-defined variables are in italics.

Accessing the URL Parameters

 String user = req.getParameter("user"); 
 String password = req.getParameter("password"); 
 String datasource = req.getParameter("datasource");

Creating the Initial Naming Context

Context ctx = new InitialContext();

Lookup the Data Source

DataSource ds = (DataSource) ctx.lookup("jdbc/" + datasource);

Use the Data Source to get Connections

Connection con = ds.getConnection(user, password);

At this point, the connection obtained behaves the same way as one obtained from the Driver Manager.

See Appendix A for a complete Application.

3.3. Accessing the DataSource with WebSphere Java 2 Security Enforced

An application can access a data source when WebSphere Java 2 security is enforced.

3.3.1. Enforce Java 2 security on WebSphere 6.0

To enforce Java 2 security on WebSphere 6.0, follow the procedures below:

Logon to WebSphere 6.0 administrative console
Select and expand "Security" on the left navigation pane
Click on Global security
Check the checkbox for "Enforce Java 2 security"
Click "Apply"
Click "Save" to save your workspace changes to the master configuration
The server may need to be restarted for the changes to take effect

3.3.2. Using the DataSource

Defining permissions in app.policy

The app.policy file is located in the following WebSphere directory location:

<WebSphere-Installation-Root>/profiles/<profile-name>/config/cells/<cell-name>/nodes/<node-name>

WebSphere Application Server has the following default permission defined in the app.policy file:

permission java.net.SocketPermission "*", "connect";

This permission is included in the following segment in app.policy:

 grant codeBase "file:${webComponent}" {
 permission java.io.FilePermission "${was.module.path}${/}-", "read, write";
 permission java.lang.RuntimePermission "loadLibrary.*";
 permission java.lang.RuntimePermission "queuePrintJob";
 permission java.net.SocketPermission "*", "connect";
 permission java.util.PropertyPermission "*", "read";
 };

Assume there are n nodes of Teradata Database system, then the following permissions are required:

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";

These permissions must be inserted into app.policy like:

 grant codeBase "file:${webComponent}" {
 permission java.io.FilePermission "${was.module.path}${/}-", "read, write";
 permission java.lang.RuntimePermission "loadLibrary.*";
 permission java.lang.RuntimePermission "queuePrintJob";
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 permission java.util.PropertyPermission "*", "read";
 };

Defining permissions in was.policy

The was.policy file is packaged in an enterprise archive (EAR) file. The was.policy file contains application-specific permissions for an WebSphere Application Server enterprise application.

Assume there are n nodes of Teradata Database system, then following permissions must be defined:

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";

These permissions must be inserted into was.policy like:

 grant codeBase "file:${webComponent}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 };

or:

 grant codeBase "file:${application}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 };

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 permission java.net.SocketPermission "hostnameCOPn+1", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn+m", "connect";

Note that you need to substitute the actual directory where the Teradata JDBC Driver is located for "C:/teradatajdbcdriver" shown in the examples below.

 grant codeBase "file:${application}" {
 permission java.lang.RuntimePermission "getClassLoader";
 };
 
 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/terajdbc4.jar", "read";
 };
 
 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/tdgssconfig.jar", "read";
 };

An additional permission must be granted when using the TTU 8.2 Teradata JDBC Driver 3.4 and earlier. This permission is not required when using the TTU 12.0 Teradata JDBC Driver and later.

 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/tdgssjava.jar", "read";
 };

In WebSphere Application Server 6.0/6.1, applications must have the appropriate thread permissions specified in the was.policy or app.policy file. Without the thread permissions specified, the application cannot manipulate threads and WebSphere Application Server throws a java.security.AccessControlException. Therefore, the following code must be added to was.policy or app.policy file for an application to manipulate threads:

 grant codeBase "file:${application}" {
 permission java.lang.RuntimePermission "stopThread";
 permission java.lang.RuntimePermission "modifyThread";
 permission java.lang.RuntimePermission "modifyThreadGroup";
 };

Expected java.security.AccessControlException in WebSphere SystemOut.log file

Assume there are 4 nodes of Teradata Database system, and the following permissions have been defined in was.policy:

 grant codeBase "file:${webComponent}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 permission java.net.SocketPermission "hostnameCOP3", "connect";
 permission java.net.SocketPermission "hostnameCOP4", "connect";
 };

The WebSphere Java 2 security exception similar to the following contents will be logged into SystemOut.log file:

 [11/13/06 14:15:20:836 PST]  c6cf176 SecurityManag W SECJ0314W: Current Java 2 Security policy reported a potential violation of Java 2 Security Permission. Please refer to Problem Determination Guide for further information.
 
 Permission:
 
       hostnameCOP5 : access denied (java.net.SocketPermission hostnameCOP5 resolve)
 
 Code:
 
      com.teradata.jdbc.jdbc_4.io.TDNetworkIO1_3Impl  in  {file:/C:/teradatajdbcdriver/terajdbc4.jar}
 
 Stack Trace:
 
 java.security.AccessControlException: access denied (java.net.SocketPermission ginghamCOP5 resolve)
  at java.security.AccessControlContext.checkPermission(AccessControlContext.java(Compiled Code))
  at java.security.AccessController.checkPermission(AccessController.java(Compiled Code))
  at java.lang.SecurityManager.checkPermission(SecurityManager.java(Compiled Code))
  at com.ibm.ws.security.core.SecurityManager.checkPermission(SecurityManager.java(Compiled Code))
  at java.lang.SecurityManager.checkConnect(SecurityManager.java:1054)
  at java.net.InetAddress.getAllByName0(InetAddress.java:1009)
  at java.net.InetAddress.getAllByName0(InetAddress.java:990)
  at java.net.InetAddress.getAllByName(InetAddress.java:984)

NOTE: Since the Teradata JDBC driver COP discovery logic handles this Java 2 security exception properly so the application will be able to use the data source without any issues.

3.4. Performance Parameters

Max Connections - too small of a value here can cause users of the pool to waste time waiting for someone to release a connection.

4. IBM WebSphere 6.1

A Connection Pool is created via the graphical interface of the administrative server.

Please note that there is more than one GUI interface available for the IBM WebSphere product. The information provided here, using WebSphere Application Server 6.1, should be sufficient to allow a user to accomplish the same tasks with the other available GUIs.

4.1. Connection Pool via Administrative Console

4.1.1. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → IBM WebSphere → Application Server v6.1 → Profiles → AppSrv01 → Administrative console

4.1.2. Add the Teradata JDBC Driver

Under "Resources", click "JDBC" and then "JDBC Providers" in the left pane.

Select appropriate scope for the JDBC provider in the right pane.

Press "New" in the right pane.

The following are the three configuration steps for creating a new JDBC provider:

Create new JDBC provider
Enter database class path information
Summary

The next section discusses these configuration steps and the required configuration information.

4.1.3. Configure the Driver

Now the Driver must be configured as shown below.

The following screen shot illustrates the first step: Create new JDBC provider

The required configuration information is listed in the following table.

Field	Value	Example
Database type	The database type being used.	User-defined
Implementation Classname	The name of the Teradata JDBC Driver data source class.	`com.teradata.jdbc.TeraConnectionPoolDataSource` Use `com.teradata.jdbc.TeraConnectionPoolDataSource` with the TTU 12.0 Teradata JDBC Driver and later. Use `com.ncr.teradata.TeraConnectionPoolDataSource` with the TTU 8.2 Teradata JDBC Driver 3.4 and earlier.
Name	The name of the driver. This is user-defined.	Teradata Driver
Description	User-defined.	Teradata JDBC

Press "Next" to move to the second step.

The following screen shot illustrates the second step: Enter database class path information

The required configuration information is listed in the following table.

Field	Value	Example
Class path	The full path name of the jar files that make up the Teradata JDBC Driver. The example assumes that you have copied these files to the C:\TeraJDBC directory on your system. A path on a Unix machine would use forward slashes to separate its components.	`C:\TeraJDBC\terajdbc4.jar` `C:\TeraJDBC\tdgssconfig.jar` 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`

Press "Next" to move to the third step.

The following screen shot illustrates the third step: Summary

Press "Finish" to submit the changes.

Note that "Teradata Driver" will now appear in the list of JDBC Providers. Click "Teradata Driver", the following screen shot shows the newly created JDBC provider information.

To create a data source, click "Data Sources" on the right side in the pane.

4.1.4. Create the Data Source

The following figure shows how to create a Data Source.

Click "New" in the "Data Sources" pane.

The following are the three configuration steps for creating a data source:

Enter basic data source information
Enter database specific properties for data source
Summary

The next section discusses these configuration steps and the required configuration information.

4.1.5. Configure the Data Source

Configure the Data Source by entering the data as shown below.

The following screen shot illustrates the first step: Enter basic data source information

The required configuration information is listed in the following table.

Field	Value	Example
Data source name	The user-defined display name for this resource.	Teradata
JNDI Name	User-defined JNDI name of the DataSource.	jdbc/teradata4

Press "Next" to move to the second step.

The following screen shot illustrates the second step: Enter database specific properties for data source

The required configuration information is listed in the following table.

Field	Value	Example
Data store helper class name	Data store helper classes provided by WebSphere	Select the default value "com.ibm.websphere.rsadapter.GenericDataStoreHelper".
Container Managed Persistence	Set if Data Source will be used for CMP of EJBs.	Check this box if the Data Source will be used with CMP entity beans. Otherwise, leave it unchecked.

Press "Next" to move to the third step.

The following screen shot illustrates the third step: Summary

Press "Finish" to submit the changes.

The following screen shot shows the newly created data source information.

Now, under "Additional Properties" in the right pane, click "Custom properties".

4.1.6. Set Data Source Custom Properties

In the "Custom properties" pane, WebSphere 6.1 lists all possible custom properties based on Teradata JDBC driver jar files in the class path. You can insert the values for the properties you are trying to define, or you can create a custom property by clicking "New" button.

The following figures show how to set the custom properties of the Data Source such as user name, password and data source name.

Set custom property "user":

Set custom property "DSName":

Set custom property "password":

The required custom property information is listed in the following table.

Name	Description	Value
DSName	The hostname of the Teradata Database.	whomooz
user	The Teradata Database username.	guest
password	The password.	please

Note: The "Database name", "Default User Id" and "Default User password" of a version 4.x data source will override any values defined as custom properties

In addition to the above parameters, all of the parameters that can be specified in a Teradata JDBC connection URL can also be specified here.

When using TTU 8.0 Teradata JDBC Driver 3.2, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.02.00, B035-2403-104A for more details.
When using TTU 8.1 Teradata JDBC Driver 3.3, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.03.00, B035-2403-115A for more details.
When using TTU 8.2 Teradata JDBC Driver 3.4, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.04.00, B035-2403-096A for more details.
When using TTU 12.0 Teradata JDBC Driver, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 12.00.00, B035-2403-067A for more details.
When using TTU 13.0 Teradata JDBC Driver, please refer to the Teradata JDBC Driver User Guide, Release 13.00.00, B035-2403-088A for more details.

You should see a message similar to "The test connection operation for data source Teradata on server server1 at node WUSJY185005-ZBRNode01 was successful with 1 warning(s). View JVM logs for further details.".

The warning message corresponds to a log entry generated by WebSphere: "DSRA0174W: Warning: GenericDataStoreHelper is being used" and does not indicate any actual problems with the datasource.

4.2. Accessing the DataSource via a Java Servlet

A Java servlet can access a data source to get pooled connections.

4.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

The Data Source's "user" and "password" Custom Properties are optional.

If "user" and "password" Custom Properties are defined, then they serve as the default values when an application calls DataSource.getConnection().
If "user" and "password" Custom Properties are defined, then the application can override those default values by calling DataSource.getConnection(username,password).
If "user" and "password" Custom Properties are not defined, then an application must call DataSource.getConnection(username,password) in order to connect to the database.

A SQLException will be thrown if user and password values are not specified in either Custom Properties, or as arguments to DataSource.getConnection(username,password).

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata JDBC Driver] : Single Sign-On NOT supported for Mechanism TD2.
SQLState: HY000
Error code: 1032

Message: [Teradata Database] : User identification is not authorized.
SQLState: 08004
Error code: 3004

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata Database] : Invalid password.
SQLState: 28000
Error code: 3003

Message: [Teradata Database] : User identification is not authorized.
SQLState: 08004
Error code: 3004

4.2.2. Using the DataSource

The servlet is accessed via a URL similar to the following:

http://server/servlet/servletname

In the example, the username, password and datasource name are all specified in the URL parameters:

http://charlie/servlet/SampleJdbcServlet?user=guest&password=please&datasource=jdbc/teradata4

Note that user-defined variables are in italics.

Accessing the URL Parameters

 String user = req.getParameter("user"); 
 String password = req.getParameter("password"); 
 String datasource = req.getParameter("datasource");

Creating the Initial Naming Context

Context ctx = new InitialContext();

Lookup the Data Source

DataSource ds = (DataSource) ctx.lookup("jdbc/" + datasource);

Use the Data Source to get Connections

Connection con = ds.getConnection(user, password);

At this point, the connection obtained behaves the same way as one obtained from the Driver Manager.

See Appendix A for a complete Application.

4.3. Accessing the DataSource with WebSphere Java 2 Security Enforced

An application can access a data source when WebSphere Java 2 security is enforced.

4.3.1. Enforce Java 2 security on WebSphere 6.1

To enforce Java 2 security on WebSphere 6.1, follow the procedures below:

Logon to WebSphere 6.1 administrative console
Select and expand "Security" on the left navigation pane
Click on "Secure administration, applications, and infrastructure"
In "Java 2 security" section on "Configuration" pane, check the checkbox for "Use Java 2 security to restrict application access to local resources"
Click "Apply"
Click "Save" to save your workspace changes to the master configuration
The server may need to be restarted for the changes to take effect

4.3.2. Using the DataSource

Defining permissions in app.policy

The app.policy file is located in the following WebSphere directory location:

<WebSphere-Installation-Root>/profiles/<profile-name>/config/cells/<cell-name>/nodes/<node-name>

WebSphere Application Server has the following default permission defined in the app.policy file:

permission java.net.SocketPermission "*", "connect";

This permission is included in the following segment in app.policy:

 grant codeBase "file:${webComponent}" {
 permission java.io.FilePermission "${was.module.path}${/}-", "read, write";
 permission java.lang.RuntimePermission "loadLibrary.*";
 permission java.lang.RuntimePermission "queuePrintJob";
 permission java.net.SocketPermission "*", "connect";
 permission java.util.PropertyPermission "*", "read";
 };

Assume there are n nodes of Teradata Database system, then the following permissions are required:

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";

These permissions must be inserted into app.policy like:

 grant codeBase "file:${webComponent}" {
 permission java.io.FilePermission "${was.module.path}${/}-", "read, write";
 permission java.lang.RuntimePermission "loadLibrary.*";
 permission java.lang.RuntimePermission "queuePrintJob";
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 permission java.util.PropertyPermission "*", "read";
 };

Defining permissions in was.policy

The was.policy file is packaged in an enterprise archive (EAR) file. The was.policy file contains application-specific permissions for an WebSphere Application Server enterprise application.

Assume there are n nodes of Teradata Database system, then following permissions must be defined:

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";

These permissions must be inserted into was.policy like:

 grant codeBase "file:${webComponent}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 };

or:

 grant codeBase "file:${application}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 };

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 permission java.net.SocketPermission "hostnameCOPn+1", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn+m", "connect";

Note that you need to substitute the actual directory where the Teradata JDBC Driver is located for "C:/teradatajdbcdriver" shown in the examples below.

 grant codeBase "file:${application}" {
 permission java.lang.RuntimePermission "getClassLoader";
 };
 
 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/terajdbc4.jar", "read";
 };
 
 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/tdgssconfig.jar", "read";
 };

An additional permission must be granted when using the TTU 8.2 Teradata JDBC Driver 3.4 and earlier. This permission is not required when using the TTU 12.0 Teradata JDBC Driver and later.

 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/tdgssjava.jar", "read";
 };

 grant codeBase "file:${application}" {
 permission java.lang.RuntimePermission "stopThread";
 permission java.lang.RuntimePermission "modifyThread";
 permission java.lang.RuntimePermission "modifyThreadGroup";
 };

Expected java.security.AccessControlException in WebSphere SystemOut.log file

Assume there are 4 nodes of Teradata Database system, and the following permissions have been defined in was.policy:

 grant codeBase "file:${webComponent}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 permission java.net.SocketPermission "hostnameCOP3", "connect";
 permission java.net.SocketPermission "hostnameCOP4", "connect";
 };

The WebSphere Java 2 security exception similar to the following contents will be logged into SystemOut.log file:

 [11/13/06 14:15:20:836 PST]  c6cf176 SecurityManag W SECJ0314W: Current Java 2 Security policy reported a potential violation of Java 2 Security Permission. Please refer to Problem Determination Guide for further information.
 
 Permission:
 
       hostnameCOP5 : access denied (java.net.SocketPermission hostnameCOP5 resolve)
 
 Code:
 
      com.teradata.jdbc.jdbc_4.io.TDNetworkIO1_3Impl  in  {file:/C:/teradatajdbcdriver/terajdbc4.jar}
 
 Stack Trace:
 
 java.security.AccessControlException: access denied (java.net.SocketPermission ginghamCOP5 resolve)
  at java.security.AccessControlContext.checkPermission(AccessControlContext.java(Compiled Code))
  at java.security.AccessController.checkPermission(AccessController.java(Compiled Code))
  at java.lang.SecurityManager.checkPermission(SecurityManager.java(Compiled Code))
  at com.ibm.ws.security.core.SecurityManager.checkPermission(SecurityManager.java(Compiled Code))
  at java.lang.SecurityManager.checkConnect(SecurityManager.java:1054)
  at java.net.InetAddress.getAllByName0(InetAddress.java:1009)
  at java.net.InetAddress.getAllByName0(InetAddress.java:990)
  at java.net.InetAddress.getAllByName(InetAddress.java:984)

NOTE: Since the Teradata JDBC driver COP discovery logic handles this Java 2 security exception properly so the application will be able to use the data source without any issues.

4.4. Performance Parameters

Maximum Connections - too small of a value here can cause users of the pool to waste time waiting for someone to release a connection.

On the page where these values are set, the help text, which can be displayed by moving the mouse pointer over the property name, will provide detailed information on the settings and information on how these settings can impact performance.

5. IBM WebSphere 7.0

A Connection Pool is created via the graphical interface of the administrative server.

Please note that there is more than one GUI interface available for the IBM WebSphere product. The information provided here, using WebSphere Application Server 7.0, should be sufficient to allow a user to accomplish the same tasks with the other available GUIs.

5.1. Connection Pool via Administrative Console

5.1.1. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → IBM WebSphere → Application Server v7.0 → Profiles → AppSrv01 → Administrative console

5.1.2. Add the Teradata JDBC Driver

Under "Resources", click "JDBC" and then "JDBC Providers" in the left pane.

Select appropriate scope for the JDBC provider in the right pane.

Press "New" in the right pane.

The following are the four configuration steps for creating a new JDBC provider:

Create new JDBC provider
Enter database class path information
Summary

The next section discusses these configuration steps and the required configuration information.

5.1.3. Configure the Driver

Now the Driver must be configured as shown below.

The following screen shot illustrates the first step: Create new JDBC provider

The required configuration information is listed in the following table.

Field	Value	Example
Database type	The database type being used.	User-defined
Implementation Classname	The name of the Teradata JDBC Driver data source class.	`com.teradata.jdbc.TeraConnectionPoolDataSource` Use `com.teradata.jdbc.TeraConnectionPoolDataSource` with the TTU 12.0 Teradata JDBC Driver and later. Use `com.ncr.teradata.TeraConnectionPoolDataSource` with the TTU 8.2 Teradata JDBC Driver 3.4 and earlier.
Name	The name of the driver. This is user-defined.	Teradata Driver
Description	User-defined.	Teradata JDBC

Press "Next" to move to the second step.

The following screen shot illustrates the second step: Enter database class path information

The required configuration information is listed in the following table.

Field	Value	Example
Class path	The full path name of the jar files that make up the Teradata JDBC Driver. The example assumes that you have copied these files to the C:\TeraJDBC directory on your system. A path on a Unix machine would use forward slashes to separate its components.	`C:\TeraJDBC\terajdbc4.jar` `C:\TeraJDBC\tdgssconfig.jar` 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`

Press "Next" to move to the third step.

The following screen shot illustrates the third step: Summary

Press "Finish" to submit the changes.

Note that "Teradata Driver" will now appear in the list of JDBC Providers. Click "Teradata Driver", the following screen shot shows the newly created JDBC provider information.

To create a data source, click "Data Sources" on the right side in the pane.

5.1.4. Create the Data Source

The following figure shows how to create a Data Source.

Click "New" in the "Data Sources" pane.

The following are the four configuration steps for creating a data source:

Enter basic data source information
Enter database specific properties for data source
Enter security aliases
Summary

The next section discusses these configuration steps and the required configuration information.

5.1.5. Configure the Data Source

Configure the Data Source by entering the data as shown below.

The following screen shot illustrates the first step: Enter basic data source information

The required configuration information is listed in the following table.

Field	Value	Example
Data source name	The user-defined display name for this resource.	Teradata
JNDI Name	User-defined JNDI name of the DataSource.	jdbc/teradata4

Press "Next" to move to the second step.

The following screen shot illustrates the second step: Enter database specific properties for data source

The required configuration information is listed in the following table.

Field	Value	Example
Data store helper class name	Data store helper classes provided by WebSphere	Select the default value "com.ibm.websphere.rsadapter.GenericDataStoreHelper".
Container Managed Persistence	Set if Data Source will be used for CMP of EJBs.	Check this box if the Data Source will be used with CMP entity beans. Otherwise, leave it unchecked.

Press "Next" to move to the third step.

The following screen shot illustrates the third step: Setup security aliases

Accept the defaults of "none".

The required configuration information is listed in the following table.

Field	Value
Component Managed Authentication alias	None
Mapping-configuaration	None
Component Managed Authentication alias	None

The following screen shot illustrates the fourth step: Summary

Press "Finish" to submit the changes.

The following screen shot shows the newly created data source information.

Now, under "Additional Properties" in the right pane, click "Custom properties".

5.1.6. Set Data Source Custom Properties

In the "Custom properties" pane, WebSphere 7.0 lists all possible custom properties based on Teradata JDBC driver jar files in the class path. You can insert the values for the properties you are trying to define, or you can create a custom property by clicking "New" button.

The following figures show how to set the custom properties of the Data Source such as user name, password and data source name.

Set custom property "user":

Set custom property "DSName":

Set custom property "password":

The required custom property information is listed in the following table.

Name	Description	Value
DSName	The hostname of the Teradata Database.	xpsys03
user	The Teradata Database username.	guest
password	The password.	please

Note: The "Database name", "Default User Id" and "Default User password" of a version 4.x data source will override any values defined as custom properties

In addition to the above parameters, all of the parameters that can be specified in a Teradata JDBC connection URL can also be specified here.

When using TTU 8.0 Teradata JDBC Driver 3.2, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.02.00, B035-2403-104A for more details.
When using TTU 8.1 Teradata JDBC Driver 3.3, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.03.00, B035-2403-115A for more details.
When using TTU 8.2 Teradata JDBC Driver 3.4, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 03.04.00, B035-2403-096A for more details.
When using TTU 12.0 Teradata JDBC Driver, please refer to the "Teradata Driver for the JDBC Interface" User Guide, Release 12.00.00, B035-2403-067A for more details.
When using TTU 13.0 Teradata JDBC Driver, please refer to the Teradata JDBC Driver User Guide, Release 13.00.00, B035-2403-088A for more details.

The warning message corresponds to a log entry generated by WebSphere: "DSRA0174W: Warning: GenericDataStoreHelper is being used" and does not indicate any actual problems with the datasource.

5.2. Accessing the DataSource via a Java Servlet

A Java servlet can access a data source to get pooled connections.

5.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

The Data Source's "user" and "password" Custom Properties are optional.

If "user" and "password" Custom Properties are defined, then they serve as the default values when an application calls DataSource.getConnection().
If "user" and "password" Custom Properties are defined, then the application can override those default values by calling DataSource.getConnection(username,password).
If "user" and "password" Custom Properties are not defined, then an application must call DataSource.getConnection(username,password) in order to connect to the database.

A SQLException will be thrown if user and password values are not specified in either Custom Properties, or as arguments to DataSource.getConnection(username,password).

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata JDBC Driver] : Single Sign-On NOT supported for Mechanism TD2.
SQLState: HY000
Error code: 1032

Message: [Teradata Database] : User identification is not authorized.
SQLState: 08004
Error code: 3004

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata Database] : Invalid password.
SQLState: 28000
Error code: 3003

Message: [Teradata Database] : User identification is not authorized.
SQLState: 08004
Error code: 3004

5.2.2. Using the DataSource

The servlet is accessed via a URL similar to the following:

http://server/servlet/servletname

In the example, the username, password and datasource name are all specified in the URL parameters:

http://charlie/servlet/SampleJdbcServlet?user=guest&password=please&datasource=jdbc/teradata4

Note that user-defined variables are in italics.

Accessing the URL Parameters

 String user = req.getParameter("user"); 
 String password = req.getParameter("password"); 
 String datasource = req.getParameter("datasource");

Creating the Initial Naming Context

Context ctx = new InitialContext();

Lookup the Data Source

DataSource ds = (DataSource) ctx.lookup("jdbc/" + datasource);

Use the Data Source to get Connections

Connection con = ds.getConnection(user, password);

At this point, the connection obtained behaves the same way as one obtained from the Driver Manager.

See Appendix A for a complete Application.

5.3. Accessing the DataSource with WebSphere Java 2 Security Enforced

An application can access a data source when WebSphere Java 2 security is enforced.

5.3.1. Enforce Java 2 security on WebSphere 7.0

To enforce Java 2 security on WebSphere 7.0, follow the procedures below:

Logon to WebSphere 7.0 administrative console
Select and expand "Security" on the left navigation pane
Click on "Global Security"
In "Java 2 security" section on "Configuration" pane, check the checkbox for "Use Java 2 security to restrict application access to local resources"
Click "Apply"
Click "Save" to save your workspace changes to the master configuration
The server may need to be restarted for the changes to take effect

5.3.2. Using the DataSource

Defining permissions in app.policy

The app.policy file is located in the following WebSphere directory location:

<WebSphere-Installation-Root>/profiles/<profile-name>/config/cells/<cell-name>/nodes/<node-name>

WebSphere Application Server has the following default permission defined in the app.policy file:

permission java.net.SocketPermission "*", "connect";

This permission is included in the following segment in app.policy:

 grant codeBase "file:${webComponent}" {
 permission java.io.FilePermission "${was.module.path}${/}-", "read, write";
 permission java.lang.RuntimePermission "loadLibrary.*";
 permission java.lang.RuntimePermission "queuePrintJob";
 permission java.net.SocketPermission "*", "connect";
 permission java.util.PropertyPermission "*", "read";
 };

Assume there are n nodes of Teradata Database system, then the following permissions are required:

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";

These permissions must be inserted into app.policy like:

 grant codeBase "file:${webComponent}" {
 permission java.io.FilePermission "${was.module.path}${/}-", "read, write";
 permission java.lang.RuntimePermission "loadLibrary.*";
 permission java.lang.RuntimePermission "queuePrintJob";
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 permission java.util.PropertyPermission "*", "read";
 };

Defining permissions in was.policy

The was.policy file is packaged in an enterprise archive (EAR) file. The was.policy file contains application-specific permissions for an WebSphere Application Server enterprise application.

Assume there are n nodes of Teradata Database system, then following permissions must be defined:

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";

These permissions must be inserted into was.policy like:

 grant codeBase "file:${webComponent}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 };

or:

 grant codeBase "file:${application}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 };

 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn", "connect";
 permission java.net.SocketPermission "hostnameCOPn+1", "connect";
 ......
 permission java.net.SocketPermission "hostnameCOPn+m", "connect";

Note that you need to substitute the actual directory where the Teradata JDBC Driver is located for "C:/teradatajdbcdriver" shown in the examples below.

 grant codeBase "file:${application}" {
 permission java.lang.RuntimePermission "getClassLoader";
 };
 
 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/terajdbc4.jar", "read";
 };
 
 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/tdgssconfig.jar", "read";
 };

An additional permission must be granted when using the TTU 8.2 Teradata JDBC Driver 3.4 and earlier. This permission is not required when using the TTU 12.0 Teradata JDBC Driver and later.

 grant codeBase "file:${application}" {
 permission java.io.FilePermission
 "C:/teradatajdbcdriver/tdgssjava.jar", "read";
 };

 grant codeBase "file:${application}" {
 permission java.lang.RuntimePermission "stopThread";
 permission java.lang.RuntimePermission "modifyThread";
 permission java.lang.RuntimePermission "modifyThreadGroup";
 };

Expected java.security.AccessControlException in WebSphere SystemOut.log file

Assume there are 4 nodes of Teradata Database system, and the following permissions have been defined in was.policy:

 grant codeBase "file:${webComponent}" {
 permission java.net.SocketPermission "hostnameCOP1", "connect";
 permission java.net.SocketPermission "hostnameCOP2", "connect";
 permission java.net.SocketPermission "hostnameCOP3", "connect";
 permission java.net.SocketPermission "hostnameCOP4", "connect";
 };

The WebSphere Java 2 security exception similar to the following contents will be logged into SystemOut.log file:

 [11/13/06 14:15:20:836 PST]  c6cf176 SecurityManag W SECJ0314W: Current Java 2 Security policy reported a potential violation of Java 2 Security Permission. Please refer to Problem Determination Guide for further information.
 
 Permission:
 
       hostnameCOP5 : access denied (java.net.SocketPermission hostnameCOP5 resolve)
 
 Code:
 
      com.teradata.jdbc.jdbc_4.io.TDNetworkIO1_3Impl  in  {file:/C:/teradatajdbcdriver/terajdbc4.jar}
 
 Stack Trace:
 
 java.security.AccessControlException: access denied (java.net.SocketPermission ginghamCOP5 resolve)
  at java.security.AccessControlContext.checkPermission(AccessControlContext.java(Compiled Code))
  at java.security.AccessController.checkPermission(AccessController.java(Compiled Code))
  at java.lang.SecurityManager.checkPermission(SecurityManager.java(Compiled Code))
  at com.ibm.ws.security.core.SecurityManager.checkPermission(SecurityManager.java(Compiled Code))
  at java.lang.SecurityManager.checkConnect(SecurityManager.java:1054)
  at java.net.InetAddress.getAllByName0(InetAddress.java:1009)
  at java.net.InetAddress.getAllByName0(InetAddress.java:990)
  at java.net.InetAddress.getAllByName(InetAddress.java:984)

NOTE: Since the Teradata JDBC driver COP discovery logic handles this Java 2 security exception properly so the application will be able to use the data source without any issues.

5.4. Performance Parameters

Maximum Connections - too small of a value here can cause users of the pool to waste time waiting for someone to release a connection.

6. WebSphere Studio Application Developer

The Data Source is created via the Application Developer graphical interface.

Note: This example requires that the optional "WebSphere Application Server V5.1 Integrated Test Environment" has been installed with Application Developer.

6.1. Connection Pool via Application Developer Interface

6.1.1. Start the Administrative Console

Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → IBM WebSphere Studio → Application Developer 5.1.2

6.1.2. Open the Server Perspective

Select Window → open Perspective → Server

In the left "Navigator" Pane, Right click to select

New → Server and Server Configuration

6.1.3. Create Server and Server Configuration

Select the values of the "Server name" and "Server Type" as noted in the following table.

Field	Value	Example
Server Name	The name you will later use to refer to this Server and Server Configuration.	TeraServer
Server Type	Identifies the development environment.	WebSphere Version 5.1 Test Environment

Now, click on the "Next" button to continue.

For this exercise, we will stay with the default HTTP port number of 9080.

Click on "Finish".

6.1.4. Create Server Data Source

In the left pane, expand "Servers" and select the entry with the name of the new server we just added.

Now select the "Data Source" tab at the bottom of the top right pane.

6.1.5. Configure Data Source

To the right of "JDBC provider list", click the "Add" button.

For "Database type", Select "User-defined".

For "JDBC provider type", select "User-defined JDBC provider".

Click the "Next" button.

Add the appropriate information to define the JDBC provider.

Field	Value	Example
Name	The name of the JDBC provider.	Teradata JDBC
Description	Your description of the provider.	Teradata JDBC provider
Implementation Classname	The name of the class that implements `javax.sql.ConnectionPoolDataSource`	`com.teradata.jdbc.TeraConnectionPoolDataSource` Use `com.teradata.jdbc.TeraConnectionPoolDataSource` with the TTU 12.0 Teradata JDBC Driver and later. Use `com.ncr.teradata.TeraConnectionPoolDataSource` with the TTU 8.2 Teradata JDBC Driver 3.4 and earlier.
Class path	The full path name of the jar files that make up the Teradata JDBC Driver. The example assumes that you have copied these files to the C:\TeraJDBC directory on your system.	`C:\TeraJDBC\terajdbc4.jar` `C:\TeraJDBC\tdgssconfig.jar` 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`

Click on the "Finish" button and the TeraJDBC provider and

"Teradata JDBC" will now be visible in the JDBC Provider List.

Highlight the "Teradata JDBC" entry and then click the "Add" button to the right of the "Data Source defined in the JDBC provider section above" panel.

Highlight "User-defined JDBC provider:" and select "Version 5.0 data source".

Click on "Next".

Fill in the entries as noted in the following table.

Field	Value	Example
Name	The name of the Data Source. This should already be filled in for you.	TeraDataSource
JNDI Name	User-defined JNDI name of the DataSource.	jdbc/terajdbc1
Data source helper class name	The Data source helper class is an IBM-specific feature.	com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
Use this Data Source in Container Managed persistence (CMP)	Set if Data Source will be used for CMP of EJBs.	Check this box if the Data Source will be used with CMP entity beans. Otherwise, leave it unchecked.

Click the "Finish" button.

The new "TeraDataSource" will be visible in the Data Source list for Teradata JDBC.

To the right of "Resource properties defined in the data source selected above", click the "Add" button and then add the following properties.

Name	Value	Type	Example
DSName	The hostname of the Teradata Database.	String	whomooz
user	The Teradata Database username.	String	guest
password	The password.	String	please

The final screen then appears as:

7. Troubleshooting

7.1. JNDI Call Failure

JNDI calls may fail when using a non-IBM JDK with the WebSphere examples. When trying to create a data source using the sample code and a non-IBM JDK, you may see the following error message:

 javax.naming.ServiceUnavailableException: NULL returned when resolving initial reference=NameService 
  at com.ibm.ws.naming.util.WsnInitCtxFactory.getCosRootContext(WsnInitCtxFactory.java:452) 
  at com.ibm.ws.naming.util.WsnInitCtxFactory.getRootJndiContext(WsnInitCtxFactory.java:337) 
  at com.ibm.ws.naming.util.WsnInitCtxFactory.getInitialContext(WsnInitCtxFactory.java:200) 
  HELP Exp: NULL returned when resolving initial reference=NameService

This problem can be resolved by using the JDK that is included with the WebSphere product for compilation.

7.2. Servlet Version

In WebSphere, it is possible to create either a version 4.x or version 5.x data source.

A 2.2 servlet must use a version 4.x data source and a 2.3 servlet must use a version 5.x data source. The servlet version is specified in the web.xml file.

The following line specifies a 2.3 servlet:

 <!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">

7.3. Generic Data Store Helper

In WebSphere Studio Application Developer, when using the Data Store helper class of com.ibm.websphere.rsadapter.GenericDataStoreHelper" you may see a message stating "warning: Generic Data Store helper is being used". This message can be safely ignored.

7.4. Session Defaults Warning

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)

7.5. Preparation of CMP Entity Beans for Deployment

The Teradata Driver for the JDBC Interface can support both container-managed persistence (CMP) and bean-managed persistence (BMP) entity beans. For container-managed persistence (CMP) entity beans, WebSphere does not have native support for Teradata within its automated CMP deployment tools.

However, WebSphere can produce SQL 92 and SQL 99 level compatible SQL, and it is compatible with Teradata SQL in most cases. The DDL produced by WebSphere during bean deployment can be executed on Teradata without modification in most cases.

Appendix A: WebSphere Code Samples

Note: The creation of a WebSphere datasource "ds1" creates a JNDI name of "jdbc/ds1" for the datasource. The name with the "jdbc" prefix must be used when accessing the following servlet.

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();
   }
 }

FAQ Frequently Asked Questions

tnolan — Thu, 30 Oct 2008 15:09:27 GMT

Question

I upgraded to the Teradata JDBC Driver 12.0.0.104 or later release and now I am getting error messages like the following:

TERAJDBC4 ERROR ... The com.ncr.teradata.TeraDriver class name is deprecated. Please use the com.teradata.jdbc.TeraDriver class name instead.

TERAJDBC4 ERROR ... The com.ncr.teradata.TeraConnectionPoolDataSource class name is deprecated. Please use the com.teradata.jdbc.TeraConnectionPoolDataSource class name instead.

Can I ignore this error message? How can I get rid of this error message?

Answer

These are not really error messages. They are actually warning messages.

New Teradata JDBC Driver class names are available.

For JDBC URL connections: com.teradata.jdbc.TeraDriver
For WebSphere Data Sources: com.teradata.jdbc.TeraConnectionPoolDataSource

You can ignore these warning messages temporarily. The old class names will continue to work; however, the warning message will be printed as a reminder to switch over to the new class names.

The old class names will be provided in a limited number of future releases. After that, the old class names will be removed.

Question

I downloaded the Teradata JDBC Driver 12.0.0.104 or later release and the download package did not contain the tdgssjava.jar file.

Where did that file go? Do I need to remove that file from my classpath?

Answer

The tdgssjava.jar file is no longer required by the Teradata JDBC Driver. The classes formerly in tdgssjava.jar now reside in terajdbc4.jar. This reduces the number of jar files from three to two, and simplifies deployment and maintenance.

After upgrading to Teradata JDBC Driver 12.0.0.104 or later, you should remove tdgssjava.jar from your classpath.

However, if you accidentally leave the reference to tdgssjava.jar on your classpath, it should not cause a problem. All tdgssjava class files were repackaged to be in the "com.teradata…" package, and the Teradata JDBC Driver only uses the tdgssjava classes in the new package structure. If you accidentally leave the reference to the old obsolete tdgssjava.jar on your classpath, the new Teradata JDBC Driver will not use the old tdgssjava.jar at all.

Question

How can I determine the version number for terajdbc4.jar ?

Answer

If you have WinZip installed, you can open the jar file and view the MANIFEST.MF file, which contains the version number.

If WinZip isn't available, then use the jar command from the JDK to extract the MANIFEST.MF file.

jar xvf terajdbc4.jar META-INF/MANIFEST.MF

Question

What Teradata JDBC Driver connection parameter values are recommended? What are the benefits and drawbacks of the recommended connection parameter values?

Answer

Our general recommendation is to specify TMODE=ANSI and CHARSET=UTF8. However, please note that every application is different, and some applications may need different settings.

The Teradata Database SQL Reference/Statement and Transaction Processing recommends that ANSI transaction mode be used for all new applications. The primary benefit of using TMODE=ANSI (ANSI transaction mode) is that inadvertent data truncation is avoided. In contrast, when using TERA transaction mode, silent data truncation can occur when data is inserted, because silent data truncation is a feature of TERA transaction mode. In addition, using TMODE=ANSI avoids error 3510 ("Too many END TRANSACTION statements") reported in DBQL.

The drawback of using TMODE=ANSI is that you can only call stored procedures that were created using ANSI transaction mode, and you will not be able to call stored procedures that were created using TERA transaction mode. It may not be possible to switch over to ANSI transaction mode exclusively, because you may have some legacy applications that require TERA transaction mode to work properly. You can work around this drawback by creating your stored procedures twice, in two different users/databases, once using ANSI mode, and once using TERA mode.

The Teradata JDBC Driver User Guide strongly recommends that Java applications store character data in Unicode columns in the Teradata Database, and use the UTF8 session character set (connection parameter CHARSET=UTF8). This avoids conversions between character sets, and ensures end-to-end fidelity of character data.

The drawback of using CHARSET=UTF8 is that fixed-width CHAR data type result set column values will be subject to extra trailing space padding per the Teradata Database Export Width behavior. You can work around this drawback by using CAST or TRIM in SQL SELECT statements, or in views, to convert fixed-width CHAR data types to VARCHAR.

Question

When I use the Teradata JDBC Driver to execute the query "SELECT TIME", the ResultSetMetaData says the returned column's data type is FLOAT, and getColumnClassName indicates java.lang.Double. Is this a bug in the JDBC Driver? Why doesn't "SELECT TIME" return a java.sql.Time object?

Answer

The Teradata JDBC Driver is behaving correctly when you execute "SELECT TIME". The Teradata Database SQL Reference/Functions and Operators says "The Teradata system value TIME is encoded as a REAL and is not compatible with ANSI TIME or TIME WITH TIME ZONE."

We recommend that you use the ANSI-standard CURRENT_TIME function instead of the legacy Teradata TIME function. The CURRENT_TIME function will return a TIME WITH TIME ZONE value, and a java.sql.Time object.

Question

I see error 3510 ("Too many END TRANSACTION statements") reported in DBQL, but my Java application doesn't get corresponding SQLExceptions from the Teradata JDBC Driver. Are the 3510 errors expected? Can I ignore them? Can I eliminate them?

Answer

3510 errors in DBQL typically do not indicate any problem, and can be ignored. Error 3510 is reported in DBQL when using TERA transaction mode, and it indicates that the Teradata Database has received an extra "ET" command to end a transaction: BT, INSERT, ET, ET.

3510 errors do not occur in ANSI transaction mode. When using ANSI transaction mode, multiple COMMIT statements are accepted without error: INSERT, COMMIT, COMMIT.

When the Teradata JDBC Driver operates in TERA transaction mode, extra ET commands may be sent to the Teradata Database due to a variety of different factors. For example, the JDBC specification says that a JDBC Driver must perform a commit operation when the Connection.setAutoCommit method is called. Therefore, the Teradata JDBC Driver sends ET (in TERA transaction mode) when the Connection.setAutoCommit method is called. Another situation can arise with application server connection pool management -- application servers typically call Connection.commit for safety when an application returns a JDBC connection to the connection pool. If the application had called Connection.commit itself before returning the connection to the pool, then this would result in two sequential calls to Connection.commit, in turn causing the Teradata JDBC Driver to submit two sequential ET commands.

The Teradata JDBC Driver does not attempt to determine when an ET or COMMIT command is truly needed, nor does it attempt to minimize the number of submitted ET or COMMIT commands. Instead, the Teradata JDBC Driver conservatively submits an ET or COMMIT command whenever it might be needed. This is intended to reduce the risk of lost data and wrong results.

Since 3510 errors are expected and do not indicate a problem, the Teradata JDBC Driver intercepts them and does not throw a corresponding SQLException to the Java application.

Question

What do the "cid=NNNN" and "sess=NNNN" identifiers mean when they appear in the DBC.SessionInfo.LogonSource column, and in SQLException messages thrown by the Teradata JDBC Driver?

Answer

The "cid" means "connection ID". The JDBC Driver User Guide Chapter 2 section "LogonSource Format" says "The connection ID is the hash code of the connection object. It provides a simple unique identifier for a particular connection to the Teradata Database." The main purpose of the connection ID is to provide an identifier for situations when a session number is not available.

The "sess" means "session number". A nonzero number indicates a Teradata Database session number, but when a SQLException message contains "sess=0" (the session number is zero), it means that the session never got logged on from the Teradata JDBC Driver's point of view.

The Teradata JDBC Driver doesn't zero out the session number after a session is logged off. The connection continues to remember its session number even after it's logged off.

Question

Your documentation says that the setFetchSize method is supported by the Teradata JDBC Driver, but is not supported by the Teradata Database. What does that mean? Does using setFetchSize provide any value when reading large result sets from Teradata? Does the Teradata JDBC Driver load the entire result set into memory or does it buffer it and read it off the wire in chunks as the application iterates through the data?

Answer

The Teradata Database does not yet provide "fetch size" functionality, so the Teradata JDBC Driver's setFetchSize method has no effect.

The good news is that the Teradata JDBC Driver reads only one response message at a time from the Teradata Database, as the application fetches through the result set. The Teradata JDBC Driver does not read the entire result set into memory. The Teradata Database will fit as many rows as it can into each response message, which can be up to 1MB in size for a SQL connection, or up to 64KB in size for a FastExport or Monitor partition connection.

Question

I'm trying to use JDBC FastLoad -- I was using the PreparedStatement executeUpdate method, but I got an exception "This method is not implemented". I saw that your JDBC FastLoad sample program uses PreparedStatement batch insert, so I was able to get my program to work after switching to the PreparedStatement addBatch and executeBatch methods. Does JDBC FastLoad only work with PreparedStatement batch insert?

Answer

Yes, JDBC FastLoad only works with PreparedStatement batch inserts. JDBC FastLoad is not appropriate for all applications. The Teradata JDBC Driver User Guide Chapter 2 contains a section titled "Considerations When Using JDBC FastLoad". One of the bullet points in that section says "Supports batch inserts only". While JDBC FastLoad can improve performance, it does have several limitations. Please review the "Considerations When Using JDBC FastLoad" before using JDBC FastLoad in your application.

Question

I have questions about JDBC FastLoad scalability. My understanding about PreparedStatement addBatch is that the rows accumulate on the client until executeBatch sends them to the Teradata Database. I'm worried about overflowing the JVM heap, so I want to call executeBatch periodically to send rows to the database. But I also know that FastLoad can only insert rows into an empty table, so I am worried that the second executeBatch will hit an error. What are the best practices for using JDBC FastLoad?

Answer

We recommend that autocommit be set to false (meaning turned off) for a JDBC FastLoad Connection, then JDBC FastLoad PreparedStatement executeBatch can be executed multiple times. JDBC FastLoad PreparedStatement addBatch saves the data in memory on the heap, and JDBC FastLoad PreparedStatement executeBatch transmits the data to the Teradata Database where it is placed in a temporary holding area. With autocommit set to false, JDBC FastLoad PreparedStatement executeBatch won't transfer the data to the actual table. The data can accumulate in the holding area until JDBC FastLoad Connection commit instructs the Teradata Database to transfer the data from the holding area to the actual table.

The Teradata JDBC Driver User Guide currently contains an incorrect recommendation -- it recommends a batch size of 500 to 1000 for JDBC FastLoad. This is too small. Our new recommendation is that an application should set autocommit to false for JDBC FastLoad, and use as large a batch size as possible with respect to their JVM heap size. A future revision of the JDBC Driver User Guide will recommend a batch size of 50,000 to 100,000 for JDBC FastLoad. If an application uses a very large batch size, then JDBC FastLoad performance can be comparable to standalone FastLoad utility performance.

Question

Does the Teradata JDBC Driver support the Teradata-proprietary reconnect protocol? How does that work in an application server environment with connection pooling? How does that work with Teradata Query Director?

Answer

Application servers have a "purge policy" to govern the behavior of the connection pool when a connection goes bad. JDBC drivers indicate to the application server that a connection has gone bad by throwing a SQLException with a SQLState attribute = 08S01.

The system administrator can set the connection pool purge policy to (1) "bad connection only", in which case only the one bad connection would be ejected from the pool, or set the purge policy to (2) "all connections", in which case all the connections would be ejected from the pool, on the theory that if one connection has gone bad, all the other connections are probably bad also.

Subsequently, as connections are requested from the pool, the application server will create new JDBC connections as needed if the pool doesn't contain enough to satisfy the incoming requests.

The Teradata JDBC Driver does not currently support the Teradata-proprietary reconnect protocol. This feature corresponds to JDBC RFC DR 107800.

The Teradata-proprietary reconnect protocol may have limited usefulness in an application server environment. The reconnection attempt must occur within a limited time to be successful. Each connection in the pool would have to be used within the limited time in order for the reconnection to be successful; however, connections in a pool may be idle for extended periods of time. No background processing occurs for idle JDBC connections. Each connection that happened to remain unused past the limited time would not be able to be reconnected to the Teradata Database.

If you are using Teradata Query Director, then you should set your application server's connection pool purge policy to "bad connection only", because Teradata Query Director takes care of redirecting requests to another Teradata Database. If your connection pool purge policy is mistakenly set to "all connections", then you will not benefit from Teradata Query Director for JDBC connections.

Question

The Teradata JDBC Driver is throwing SQLException with getErrorCode equal to 802 and a message containing either "Timeout occurred for Packet receive" or "HY000 802 : Timeout Packet stream read error". Also, the Teradata JDBC Driver is printing log messages that say "TERAJDBC4 ERROR ... Read message ... timed out" followed by "TERAJDBC4 ERROR ... Packet receive ... SocketTimeoutException". In the Teradata Database's DBQL, I see error 3110 "The transaction was aborted by the user". Why is this happening?

Answer

These exceptions and messages are expected when you use the Statement setQueryTimeout method. When your application specifies a timeout with the Statement setQueryTimeout method, then the Teradata JDBC Driver will use a timeout for Socket read method calls. Java throws SocketTimeoutException from a Socket read method call when the specified timeout expires. The Teradata JDBC Driver catches that exception and attempts to send an asynchronous Abort Request message to the Teradata Database in order to abort the SQL request in progress. If the Teradata Database was able to respond to the asynchronous Abort Request and abort the SQL request, then the Teradata Database will record error 3110 ("The transaction was aborted by the user") in DBQL.

Note that the Statement setQueryTimeout method only applies to executing a SQL request. The Statement setQueryTimeout method does not apply to any other operations that the Teradata Database performs, such as preparing a SQL request, fetching ResultSet rows from the spool, releasing the spool when a ResultSet is closed, committing a transaction, or rolling back a transaction. There is no method defined for an application to specify a timeout for these other kinds of operations.

Java Virtual Machine (JVM) and Application Server Support

tnolan — Mon, 29 Sep 2008 13:27:59 GMT

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

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.

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.

ColdFusion Application Server "How-To" Guide

tnolan — Wed, 17 Sep 2008 16:39:18 GMT

How to use the Teradata JDBC Driver with Adobe ColdFusion and JRun

Date: September 16, 2008

TERADATA CONFIDENTIAL

1. Introduction

2. Adobe ColdFusion MX 6.1

2.1. Connection Pool via Administrative Console
2.1.1. Start the Administrative Console
2.1.2. Set Classpath
2.1.3. Add a New Data Source
2.1.4. Set the Data Source Properties
2.2. Data Source Usage

3. Adobe ColdFusion MX 7

3.1. Connection Pool via Administrative Console
3.1.1. Start the Administrative Console
3.1.2. Set Classpath
3.1.3. Add a New Data Source
3.1.4. Set the Data Source Properties
3.2. Data Source Usage

4. Adobe ColdFusion 8

4.1. Connection Pool via Administrative Console
4.1.1. Start the Administrative Console
4.1.2. Set Classpath
4.1.3. Add a New Data Source
4.1.4. Set the Data Source Properties
4.2. Data Source Usage

5. Adobe JRun 4

5.1. Connection Pool via Administrative Console
5.1.1. Start the Administrative Console
5.1.2. Set Classpath
5.1.3. Add a New Data Source
5.1.4. Set the Data Source Properties
5.2. Data Source Usage

6. Troubleshooting

6.1. Session Defaults Warning
6.2. Unsupported Method

Appendix A: ColdFusion Code Sample

Appendix B: JRun Code Samples

1. Introduction

1.1. Assumptions

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.

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

The application server versions used are:

Adobe ColdFusion MX 6.1
Adobe ColdFusion MX 7
Adobe ColdFusion 8
Adobe JRun 4

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

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.

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.

1.5. C/C++ and Java Application Sharing of TeraGSS Security Configuration

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.

jar uvf tdgssconfig.jar TdgssUserConfigFile.xml

1.6. Sandbox Security Permissions for Teradata Files

In ColdFusion, Sandbox Security can be enabled. If this is enabled and the Teradata JDBC Driver files are defined in a sandbox, then READ permission must be granted for the Teradata JDBC Driver files. Either the full path name for each file, or the directory containing these files, will require READ permission.

terajdbc4.jar
tdgssconfig.jar

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.

Without READ permission, possible errors that may be returned are "Error Executing Database Query" or "GSSException: Failure unspecified at GSS-API level (Mechanism level: LibFile parameter null)".

For information on setting up Sandbox Security, please refer to the ColdFusion documentation.

2. Adobe ColdFusion MX 6.1

A Data Source is created via the Administrative Console. Connection Pooling is set to "on" by default via an Advanced Setting within the Data Source titled "Maintain Connections".

2.1. Connection Pool via Administrative Console

2.1.1. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → Adobe → Adobe ColdFusion MX → Administrator

2.1.2. Set Classpath

In the left pane, under "SERVER SETTINGS", click on "Java and JVM"

The right pane will have a "Class Path" entry. Enter the full path names of the Teradata JDBC Driver files terajdbc4.jar and tdgssconfig.jar.

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

Click "Submit Changes" to apply these changes and restart the ColdFusion MX service to make them take effect.

2.1.3. Add a New Data Source

In the left pane, under "DATA & SERVICES", click on "Data Sources".

Enter a "Data Source Name" and select a Driver and then click "Add".

Field	Value	Example
Data Source Name	The name you will later use to refer to this Data Source.	TeraJDBC4
Driver	The Driver to use. Since the Teradata Driver is not listed, select "Other".	Other

2.1.4. Set the Data Source Properties

The values required to create the connection pool are outlined in the following table. Please note that the username of "guest" and password of "please" are examples and should be replaced by the actual username/password combination for your Teradata Database system.

Field	Value	Example
CF Data Source Name	This field was carried over from the previous form. It can be used by ColdFusion scripts to access this Data Source.	TeraJDBC4
JDBC URL	Defines the URL that will be passed to the driver. The example Teradata Database name is "whomooz". You should use the actual Teradata Database hostname for your site. Please note that any connection URL parameter such as "CHARSET" or "TMODE" must be set within this URL parameter. These CANNOT be set within the properties panel.	jdbc:teradata://whomooz
Driver Class	The name of the class that implements the java.sql.Driver interface. 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
Driver Name	An optional name that you want to assign to this driver.	Teradata
Username	The Teradata Database username.	guest
Password	The password of the user defined by "Username". After this value is applied it will not be visible as cleartext in the panel.	please

After entering the required information, click on the "Submit" button to test the Data Source.

The final screen should say "datasource updated successfully" at the top and your new Data Source should be visible in the list of "Connected Data Sources", in the bottom table in the right pane.

2.2. Data Source Usage

ColdFusion provides its own markup language for using Data Sources. A full example with instructions for use can be found in Appendix A.

3. Adobe ColdFusion MX 7

A Data Source is created via the Administrative Console. Connection Pooling is set to "on" by default via an Advanced Setting within the Data Source titled "Maintain Connections".

3.1. Connection Pool via Administrative Console

3.1.1. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → Adobe → Adobe ColdFusion MX 7 → Administrator

3.1.2. Set Classpath

In the left pane, under "SERVER SETTINGS", click on "Java and JVM"

The right pane will have a "ColdFusion Class Path" entry. Enter the full path names of the Teradata JDBC Driver files terajdbc4.jar and tdgssconfig.jar.

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

Click "Submit Changes" to apply these changes and restart the ColdFusion MX service to make them take effect.

3.1.3. Add a New Data Source

In the left pane, under "DATA & SERVICES", click on "Data Sources".

Enter a "Data Source Name" and select a Driver and then click "Add".

Field	Value	Example
Data Source Name	The name you will later use to refer to this Data Source.	TeraJDBC4
Driver	The Driver to use. Since the Teradata Driver is not listed, select "Other".	Other

3.1.4. Set the Data Source Properties

Field	Value	Example
CF Data Source Name	This field was carried over from the previous form. It can be used by ColdFusion scripts to access this Data Source.	TeraJDBC4
JDBC URL	Defines the URL that will be passed to the driver. The example Teradata Database name is "whomooz". You should use the actual Teradata Database hostname for your site. Please note that any connection URL parameter such as "CHARSET" or "TMODE" must be set within this URL parameter. These CANNOT be set within the properties panel.	jdbc:teradata://whomooz
Driver Class	The name of the class that implements the java.sql.Driver interface. 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
Driver Name	An optional name that you want to assign to this driver.	Teradata
User Name	The Teradata Database username.	guest
Password	The password of the user defined by "User Name". After this value is applied it will not be visible as cleartext in the panel.	please

After entering the required information, click on the "Submit" button to test the Data Source.

The final screen should say "datasource updated successfully" at the top and your new Data Source should be visible in the list of "Connected Data Sources", in the bottom table in the right pane.

3.2. Data Source Usage

ColdFusion provides its own markup language for using Data Sources. A full example with instructions for use can be found in Appendix A.

4. Adobe ColdFusion 8

A Data Source is created via the Administrative Console. Connection Pooling is set to "on" by default via an Advanced Setting within the Data Source titled "Maintain Connections".

4.1. Connection Pool via Administrative Console

4.1.1. Start the Administrative Console

Use the Desktop "Start" button as follows:

Start → Programs → Adobe → ColdFusion 8 → Administrator

4.1.2. Set Classpath

In the left pane, under "SERVER SETTINGS", click on "Java and JVM"

The right pane will have a "ColdFusion Class Path" entry. Enter the full path names of the Teradata JDBC Driver files terajdbc4.jar and tdgssconfig.jar.

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

Click "Submit Changes" to apply these changes and restart the ColdFusion service to make them take effect.

4.1.3. Add a New Data Source

In the left pane, under "DATA & SERVICES", click on "Data Sources".

Enter a "Data Source Name" and select a Driver and then click "Add".

Field	Value	Example
Data Source Name	The name you will later use to refer to this Data Source.	TeraJDBC4
Driver	The Driver to use. Since the Teradata Driver is not listed, select "Other".	Other

4.1.4. Set the Data Source Properties

Field	Value	Example
CF Data Source Name	This field was carried over from the previous form. It can be used by ColdFusion scripts to access this Data Source.	TeraJDBC4
JDBC URL	Defines the URL that will be passed to the driver. The example Teradata Database name is "whomooz". You should use the actual Teradata Database hostname for your site. Please note that any connection URL parameter such as "CHARSET" or "TMODE" must be set within this URL parameter. These CANNOT be set within the properties panel.	jdbc:teradata://whomooz
Driver Class	The name of the class that implements the java.sql.Driver interface. 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
Driver Name	An optional name that you want to assign to this driver.	Teradata
User Name	The Teradata Database username.	guest
Password	The password of the user defined by "User Name". After this value is applied it will not be visible as cleartext in the panel.	please

After entering the required information, click on the "Submit" button to test the Data Source.

The final screen should say "datasource updated successfully" at the top and your new Data Source should be visible in the list of "Connected Data Sources", in the bottom table in the right pane.

4.2. Data Source Usage

ColdFusion provides its own markup language for using Data Sources. A full example with instructions for use can be found in Appendix A.

5. Adobe JRun

A Data Source is created via the Management Console. Connection Pooling is enabled by checking the "Pool Connections" checkbox on the Data Source definition pane.

5.1. Connection Pool via Management Console

In the following examples, all changes are made to the "default" server. The actual server that you choose will depend on your JRun configuration.

5.1.1. Start the Management Console

Use the Desktop "Start" button as follows:

Start → Programs → Adobe JRun 4 → JRun Management Console

For ColdFusion 8:

Start → Programs → Adobe → ColdFusion 8 → JRun Management Console

5.1.2. Set Classpath

In the left pane, under "default", click on "Settings" and then "JVM Settings".

The right pane will have a "Classpaths" entry. Enter the full path names of the Teradata JDBC Driver files terajdbc4.jar and tdgssconfig.jar.

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

Click "Update" to apply these changes and restart the JRun 4 server to make them take effect.

5.1.3. Add a New Data Source

In the left pane, under "default", click on "Resources" and then "JDBC Data Sources".

Enter a "Data Source Name" and select a Driver and then click "Add".

Field	Value	Example
Data Source Name	The name you will later use to refer to this Data Source.	TeraJDBC4
Database Driver	The Driver to use. Since the Teradata Driver is not listed, select "Not-Listed".	Not-Listed

5.1.4. Set the Data Source Properties

The values required to create the data source are outlined in the following table. Please note that the username of "guest" and password of "please" are examples and should be replaced by the actual username/password combination for your Teradata Database system.

Field	Value	Example
Data Source Name	This field was carried over from the previous form. It can be used by JRun 4 clients to access this Data Source.	TeraJDBC4
JNDI Name	The JNDI name that can be used to look up this data source on the server	jdbc/TeradataDS
Driver Class Name	The name of the class that implements the java.sql.Driver interface. 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	Defines the URL that will be passed to the driver. The example Teradata Database name is "whomooz". You should use the actual Teradata Database hostname for your site. Please note that any connection URL parameter such as "CHARSET" or "TMODE" must be set within this URL parameter. These CANNOT be set within the properties panel.	jdbc:teradata://whomooz
Username	The Teradata Database username.	guest
Password	The password of the user defined by "Username". After this value is applied it will not be visible as cleartext in the panel.	please
Pool Connections Pool Statements Native Results	All should remain checked which is their default value.

After entering the required information, click on the "Submit" button to finish the data source creation.

The final screen should say "Data source has been created" at the top and your new Data Source should be visible in the list of "JDBC Data Sources", in the bottom table in the right pane.

5.2. Data Source Usage

See Appendix B for an example of a servlet that can be used to access the Data Source.

6. Troubleshooting

6.1. Session Defaults Warning

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)

6.2. Unsupported Method

It is important to ensure that a DataSource's "Native Results" checkbox is checked. (See Section 5.1.4.) When it is not checked, the following error message may be seen:

 [Teradata JDBC Driver] : Method not supported with this release of the Teradata Database: use the COMPAT_DBS and COMPAT_ISAUTOINC connection options

Appendix A: ColdFusion Code Sample

The following sample code uses the Data Source that was created earlier in ColdFusion via the Administrator console.

The actual script is:

 <html>
 <head>
 <title>A ColdFusion Page</title>
 </head>
 <body>
 This is a ColdFusion page.
 <br>
 <cfquery name="DBSINFO" datasource="TeraJDBC4">
    select InfoKey, InfoData from dbc.dbcinfo
 </cfquery>
 <cfoutput query="DBSINFO">
 #InfoKey#, #InfoData#<br>
 </cfoutput>
 </body>
 </html>

When this script is put into a file C:\CFusionMX\wwwroot\test.cfm

then it can be invoked from a browser via an URL http://localhost:8500/test.cfm

and will produce output similar to the following:

 This is a ColdFusion page.
 VERSION, 06.00.00.00
 RELEASE, V2R.06.00.00.00

Appendix B: JRun Code Samples

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();
   }
 }

JBoss Application Server "How-To" Guide

tnolan — Wed, 17 Sep 2008 16:38:21 GMT

How to use the Teradata JDBC Driver with JBoss Application Server

Date: September 16, 2008

TERADATA CONFIDENTIAL

1. Introduction

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. JBoss 3.2.3 and JBoss 4.0

2.1. Prerequisites
2.2. JBoss Data source Definition
2.3. Viewing the Data source
2.4. Additional Requirements for CMP entity Beans
2.5. Data sources and Enterprise Applications
2.5.1. Bean Managed Persistence
2.5.2. Container Managed Persistence
2.6. Using the DataSource

3. Troubleshooting

3.1. Session Defaults Warning
3.2. User Transaction Restrictions

Appendix A: JBoss Code Samples

1. Introduction

1.1. Assumptions

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.

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

The application server versions used are:

JBoss 3.2.3
JBoss 4.0

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

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.

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.

1.5. C/C++ and Java Application Sharing of TeraGSS Security Configuration

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.

jar uvf tdgssconfig.jar TdgssUserConfigFile.xml

2. JBoss 3.2.3 and JBoss 4.0

The definition and availability of a Data source for JBoss is defined by XML files. The ones we will be creating/modifying in the following sections are:

Files	Purpose
.\server\default\deploy\teradata-ds.xml	Defines a Teradata Datasource
.\server\default\conf\standardjbosscmpjdbc.xml	Required for CMP entity beans
ejb-jar.xml	Deployment Descriptors for EJBs
jboss.xml	JBoss Deployment Descriptor

2.1. Prerequisites

In all of the following examples, we assume that the Teradata JDBC install location is C:\TeraJDBC and JBoss is installed at C:\JBossHome. You should modify these values to reflect your actual installation.

Install JBoss 3.2.3 or JBoss 4.0
Copy the Teradata JDBC files from the Teradata JDBC install area to the JBoss install location. The files terajdbc4.jar and tdgssconfig.jar should be copied to the directory C:\JBossHome\server\default\lib.

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), then remove the tdgssconfig.jar file from C:\JBossHome\server\default\lib and add the file TdgssUserConfigFile.xml to C:\JBossHome\server\default\conf

2.2. JBoss Data source Definition

A Data source is defined in JBoss via an xml file in the deploy directory which ends with the suffix "ds.xml". A Teradata Datasource will be defined by creating a "teradata-ds.xml" file in C:\JBossHome\server\default\deploy. The following is a sample "terdata-ds.xml" file, which you can be used as a template for creating your own data source.

 <?xml version="1.0" encoding="UTF-8"?>
 <datasources>
   <local-tx-datasource>
     <jndi-name>TeradataDS</jndi-name>
     <connection-url>jdbc:teradata://cyclops/TMODE=ANSI</connection-url>
     <driver-class>com.teradata.jdbc.TeraDriver</driver-class>
     <user-name>guest</user-name>
     <password>please</password>
   </local-tx-datasource>
 </datasources>

XML-TAG	Description	Example
jndi name	Java Naming & Directory Interface designation for this Data Source. Note that this name is relative to the "java:" prefix.	TeradataDS
connection url	Defines the URL that will be passed to the driver.	jdbc:teradata://Cyclops/TMODE=ANSI
driver-class	The name of the class that implements the java.sql.Driver interface. 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
user-name	The username for the Teradata Database.	guest
password	The password of the user defined by "Username"	please

The file "teradata-ds.xml" creates a data source which uses the J2EE Connector Architecture(JCA) implementation for JBoss. The JCA configuration format is described in the DTD file docs/dtd/jboss-ds_1_0.dtd. For more information on the JBoss JCA implementation refer to "JBoss Admin. and Development Guide 3.2.3" or "JBoss 4 Application Server Guide."

Note: XA-compliant data sources are not supported by the Teradata driver.

2.3. Viewing the Data source

At this point, the JNDI name of the data source can be viewed on the jmx-console web console for JBoss by using the following steps:

Start JBoss application server (Windows)
- Start a command prompt window
- cd C:\JBossHome\bin
- execute run.bat
Bring up the web console by going to the following URL in your web browser:
http://localhost:8080/jmx-console/
Select the jboss:service=JNDIView link
Invoke the java.lang.String list() button

The JNDI name for the Teradata Data Source (i.e. TeradataDS) should appear under the java: Namespace as the screen capture above shows.

2.4. Additional Requirements for CMP entity Beans

For CMP Entity Beans you need to add the following xml tags to the file

C:\JBossHome\server\default\conf\standardjbosscmp-jdbc.xml

 <type-mapping>
    <name>teradata</name>
    <row-locking-template/>
    <pk-constraint-template>CONSTRAINT ?1 PRIMARY KEY (?2)</pk-constraint-template>
    <fk-constraint-template>ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>
    <alias-header-prefix>t</alias-header-prefix>
    <alias-header-suffix>_</alias-header-suffix>
    <alias-max-length>32</alias-max-length>
    <subquery-supported>true</subquery-supported>
    <true-mapping>1</true-mapping>
    <false-mapping>0</false-mapping>
    <function-mapping>
       <function-name>count</function-name>
       <function-sql>count(?1)</function-sql>
    </function-mapping>
    <function-mapping>
       <function-name>locate</function-name>
       <function-sql>locate(CAST(?1 as VARCHAR(254)),CAST(?2 as VaRCHAR(254)),?3)</function-sql>
    </function-mapping>
    <mapping>
       <java-type>java.lang.Boolean</java-type>
       <jdbc-type>BIT</jdbc-type>
       <sql-type>SMALLINT</sql-type>
    </mapping>
    <mapping>
       <java-type>java.lang.Short</java-type>
       <jdbc-type>SMALLINT</jdbc-type>
       <sql-type>SMALLINT</sql-type>
    </mapping>
    <mapping>
       <java-type>java.lang.Integer</java-type>
       <jdbc-type>INTEGER</jdbc-type>
       <sql-type>INTEGER</sql-type>
    </mapping>
    <mapping>
       <java-type>java.lang.Long</java-type>
       <jdbc-type>BIGINT</jdbc-type>
       <sql-type>BIGINT</sql-type>
    </mapping>
    <mapping>
       <java-type>java.lang.Float</java-type>
       <jdbc-type>REAL</jdbc-type>
       <sql-type>REAL</sql-type>
    </mapping>
    <mapping>
       <java-type>java.lang.Double</java-type>
       <jdbc-type>DOUBLE</jdbc-type>
       <sql-type>DOUBLE PRECISION</sql-type>
    </mapping>
    <mapping>
       <java-type>java.lang.Character</java-type>
       <jdbc-type>CHAR</jdbc-type>
       <sql-type>CHAR</sql-type>
    </mapping>
    <mapping>
       <java-type>java.lang.String</java-type>
       <jdbc-type>VARCHAR</jdbc-type>
       <sql-type>VARCHAR(254)</sql-type>
    </mapping>
    <mapping>
       <java-type>java.sql.Date</java-type>
       <jdbc-type>DATE</jdbc-type>
       <sql-type>DATE</sql-type>
    </mapping>
    <mapping>
       <java-type>java.sql.Time</java-type>
       <jdbc-type>TIME</jdbc-type>
       <sql-type>TIME</sql-type>
    </mapping>
    <mapping>
       <java-type>java.sql.Timestamp</java-type>
       <jdbc-type>TIMESTAMP</jdbc-type>
       <sql-type>TIMESTAMP</sql-type>
    </mapping>
    <!-- this one gives errors with DB2 V8.1
         and at least the NET driver
    <mapping>
       <java-type>java.lang.Object</java-type>
       <jdbc-type>JAVA_OBJECT</jdbc-type>
       <sql-type>BLOB(2000)</sql-type>
    </mapping>
    -->
    <mapping>
       <java-type>java.lang.Object</java-type>
       <jdbc-type>VARBINARY</jdbc-type>
       <sql-type>BLOB(2000)</sql-type>
    </mapping>
 </type-mapping>

The standard deployment descriptor to configure the JBoss CMP container is the standardjbosscmp-jdbc.xml file. Complete documentation of the XML tags for standardjbosscmp-jdbc.xml is in the directory C:\JBossHome\docs\dtd

2.5. Data sources and Enterprise Applications

2.5.1. Bean Managed Persistence

Bean Managed Persistence (BMP) applications need to reference a Data source in the ejb-jar.xml file as the example below shows:

 <resource-ref>
    <res-ref-name>teraDB</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
 </resource-ref>

ejb-jar.xml is the standard deployment descriptor file for enterprise java beans. Complete documentation of the XML tags for ejb-jar.xml is in the directory:

C:\JBossHome\docs\dtd

In addition, the jboss.xml file must reference the JDBC data source directly as follows:

 <resource-ref>
    <res-ref-name>teraDB</res-ref-name>
    <jndi-name>java:/TeradataDS</jndi-name>
 </resource-ref>

The jboss.xml is the deployment descriptor which has JBoss-specific information. Complete documentation of the XML tags for jboss.xml is in the directory C:\JBossHome\docs\dtd\

2.5.2. Container Managed Persistence

Container Managed Persistence (CMP) entity beans need a reference to the Teradata Data source in the jbosscmp-jdbc.xml file as the example below shows:

 <?xml version="1.0" encoding="UTF-8"?>
 <jbosscmp-jdbc>
    <defaults>
       <datasource>java:/TeradataDS</datasource>
       <datasource-mapping>teradata</datasource-mapping>
    </defaults>
 </jbosscmp-jdbc>

The jbosscmp-jdbc.xml file replaces standardjbosscmp-jdbc.xml file for a specific application. The complete documentation of the XML tags for jbosscmp-jdbc.xml file is in the directory C:\JBossHome\docs\dtd

2.6. Using the DataSource

The following code creates the initial context:

Context ctx = new InitialContext();

To perform a JNDI lookup to obtain the DataSource, the following code can be used:

DataSource ds = (DataSource) ctx.lookup(jndiName);

To obtain a connection from the DataSource, the following code can be used:

Connection con = ds.getConnection();

See Appendix A for a complete application.

3. Troubleshooting

3.1. Session Defaults Warning

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)

3.2. User Transaction Restrictions

User transactions will not work in standalone Client Applications. J2EE user transactions require the "java:comp" JNDI environment to work with JBoss. User transactions can not be run from a standalone application because standalone clients do not access to the JBoss name space "java:comp". All Servlets, Session and Entity beans can use user transactions because these components run inside the JBoss application server which has access to JNDI name space "java:comp".

Appendix A: JBoss Code Samples

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();
   }
 }

SAP Application Server "How-To" Guide

tnolan — Wed, 17 Sep 2008 16:37:14 GMT

How to use the Teradata JDBC Driver with SAP Web Application Server (WAS) 6.40 and SAP BW 3.5 Universal Data Connect (UD Connect) Integration

Date: September 16, 2008

TERADATA CONFIDENTIAL

1. Introduction

2. SAP Web Application Server 6.40

2.1. Data Source via Visual Administrator
2.1.1. Prerequisites
2.1.2. Start the Administration Tool
2.1.3. Define the JDBC Connector Driver
2.1.4. Configure the JDBC Connector DataSource
2.2. Accessing the DataSource via a Java Servlet
2.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)
2.2.2. Using the DataSource
2.3. Performance Parameters

3. SAP BW 3.5 UD Connect Integration

3.1. Assumptions and Prerequisites
3.2. Teradata Data Modeling
3.2.1. Database Modeling
3.2.2. InfoCube DataSources
3.2.3. Master Data DataSources
3.2.4. Aggregate Join Indices
3.3. Java Connector and RFC Provider via Visual Administrator
3.3.1. Start the Administration Tool
3.3.2. Define the JDBC Connector Driver
3.3.3. Configure the Java Connector
3.3.4. Configure the RFC Provider in Visual Administrator
3.4. BW DataSource via SAP BW Administrator
3.4.1. Start SAP Front End Logon GUI
3.4.2. Configure the RFC Provider in SAP GUI
3.4.3. Start BW Administrator Workbench
3.4.4. Create BW Generic DataSource
3.4.4.1. Prepare Required InfoObjects
3.4.4.2. Create and Configure InfoSource
3.4.4.3. Create and Activate BW DataSource
3.4.5. Connect DataSource to Data Target for Characteristic InfoObject
3.4.6. Connect DataSource to Remote InfoCube
3.5. Performance Parameters

4. Troubleshooting

4.1. JDBC Driver Debugging
4.2. SQL Trace
4.3. BW System Trace

Appendix A: SAP Web Application Server Code Samples

1. Introduction

1.1. Assumptions

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.

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

The application server versions used are:

SAP Web Application Server 6.40

Other tools covered are:

SAP BW 3.5

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

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.

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.

1.5. C/C++ and Java Application Sharing of TeraGSS Security Configuration

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.

jar uvf tdgssconfig.jar TdgssUserConfigFile.xml

1.6. Overview - Data Source in SAP Web Application Server 6.40

This section provides a quick overview of Data Source configuration in SAP WAS 6.40. More details are provided in subsequent sections.

The Teradata JDBC driver can be used as an SAP WAS 6.40 data source. Within SAP WAS 6.40, the use of a DataSource object, instead of DriverManager, is the preferred means of connecting to a data source.

To use the DataSource interface within SAP WAS 6.40, the JDBC Connector driver definition and JDBC Data Source will need to be configured for Teradata JDBC type 4 driver.

When defining the JDBC Connector driver through SAP Visual Administrator, the following Teradata JDBC driver jar files are needed.

terajdbc4.jar
tdgssconfig.jar

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.

When configuring the JDBC Connector Data Source through SAP Visual Administrator, the following Teradata-specific configuration parameters are needed.

JDBC Version	`1.x (no XA support)`
Driver Classname 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`
Database URL	`jdbc:teradata://`DataSourceName
User	the username to connect to the data source
Password	the password to connect to the data source
SQL Engine	`Vendor SQL`

1.7. Overview - Data Source in SAP BW 3.5 UDC Integration

To use Teradata JDBC driver with SAP BW 3.5 UDC intetgration, the Java Connectors will need to be configured through Connector Container section and RFC destination will need to be defined through JCo RFC Provider section in the Visual Administrator,

When configuring the Java Connectors in the Visual Administrator, the following Teradata-specific configuration parameters are needed:

DriverName 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`
FixedSchema	the database schema name
URL	`jdbc:teradata://`DataSourceName
UserName	the username to connect to the database schema
Password	the password to connect to the database schema

2. SAP Web Application Server 6.40

The recommended way to create the data source within SAP is to use the graphical interface via SAP Visual Administrator.

2.1. Data Source via Visual Administrator

2.1.1. Prerequisites

Prior to define JDBC Connector driver in Visual Administrator, the following Teradata JDBC Driver jar files need to be available on the server:

terajdbc4.jar
tdgssconfig.jar

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.

Some earlier Teradata JDBC driver versions contain the TeraGSS security library's user configuration file "TdgssUserConfigFile.xml" instead of the above tdgssconfig.jar file. When C/C++ and Java applications are deployed to the same physical machine, the Java applications can be configured to use this user configuration file. However, SAP Web Application Server 6.40 doesn't support the specification of a directory on the classpath, this TeraGSS user configuration file can only be shared by C/C++ and Java applications 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 it into the tdgssconfig.jar file:

jar uvf tdgssconfig.jar TdgssUserConfigFile.xml

2.1.2. Start the Administration Tool

The Administration Tool of the SAP J2EE Engine (Visual Administrator) is a GUI interface that allows the users to adminstrater Java services within a cluster and provides remote monitoring and management of these services.

For the user who will run the administration tool (for example, <SAPSID>adm), make sure that SAPINST_JRE_HOME environment variable contains the path to the Java Development Kit (JDK) of the J2EE Engine.

Note: On UNIX, make sure that the DISPLAY environment variable is set to <host_name>:0.0, where <host_name> is the host on which the administration tool will be displayed.

Enter the following command to start the administration tool:
UNIX:
/usr/sap/<SAPSID>/<Instance_Name>/j2ee/admin/go
Windows:
<driver>:\usr\sap\<SAPSID>\<Instance_Name>\j2ee\admin\go.bat

The "J2EE Engine - Administration" screen with the dialog box "Connect to J2EE Enngine" appears.

For SAP J2EE standalone system, choose "Connect" to use the "Default" login and enter the password for the Administrator user of the J2EE engine.

For SAP J2EE add-in system, the "Default" login can not be used. The following steps need to be performed:

Choose "New"
Enter a display name and choose "Direct Connection to a dispatcher Node"
Choose "Next"
Enter the following information:
- User Name: J2EE_ADMIN
- Host: <host_name> of the J2EE Engine
- Port: 5<Instance_Number_of_SAP_J2EE_Engine>04
Choose "Save" and connect with the new login account by choosing "Connect"
Enter password for the J2EE_ADMIN user and choose "Connect"

2.1.3. Define the JDBC Connector Driver

After connecting to the J2EE Engine, expand "Cluster", and then "Server", and then "Services".
Scroll down and click "JDBC Connector". On the right pane, click on "Drivers", and then click the paper icon to create a new driver definition.

In the dialog box, enter the name of the JDBC driver definition, and click "OK".

Navigate to the directory where you store the JDBC driver jar files, and select a .jar file, and click "OK".

A confirmation screen will appear asking "Do you want to select other files ?", click "Yes" to add more jar files.

All the Teradata JDBC driver jar files are needed for this step. Make sure you go back to add more jar files after each selected jar file. After finishing all the jar files, click "No". Now the new JDBC Connector driver is created with the specified jar files.

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.

2.1.4. Configure the JDBC Connector DataSource

Now the JDBC Connector driver has been configured as shown below.

On the right pane of the "JDBC Connector" section in the Visual Administrator, click on "DataSources", and then click the paper icon to create a new DataSource definition.

The new DataSource definition template includes 4 tabs: "Main", "Additional", "DB Initialization", "Monitoring".

The following inputs are needed for the "Main" tab:

Name	the new DataSource name
Description	the description for the DataSource
Driver Name	use the pull-down arrow to choose the driver defined in the above step
JDBC Version	1.x (no XA support)
Driver Class 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
Database URL	jdbc:teradata://DataBaseServer/parameter_list
User	the username to connect to the database
Password	the password to connect to the database

Some aliases can be added for referencing this DataSource.

On the "Additional" tab, make sure "Vendor SQL" is selected for "SQL Engine".

On the "DB Initialization" tab, SQL statements can be added and executed to verify the DataSource connection.

After the above configurations are done for the new DataSource, click the save icon to save the DataSource configuration. The "Application Name" on the "Main" tab will be automatically generated.

2.2. Accessing the DataSource via a Java Servlet

A Java servlet can access a data source to get pooled connections.

2.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

With SAP, a JDBC Connector driver is defined to specify information about the JDBC driver. After the JDBC Connector driver has been defined, then one or more DataSources can be configured for the JDBC driver. During the process of defining each DataSource, the driver access parameters can be configured for each DataSource.

Generally the user and password for the database need to be entered on the "Main" tab. The DataSource will use this default user/password pair to access the database via the driver when an application calls DataSource.getConnection(). The following cases illustrate how these default user/password pair is used:

If "user" and "password" parameters are defined in the DataSource definition, then they serve as the default values when an application calls DataSource.getConnection().
If "user" and "password" parameters are defined in the DataSource definition, then the application can override those default values by calling DataSource.getConnection(username,password).
If "user" and "password" parameters are not defined completely (but "User" input can not be empty), then an application must call DataSource.getConnection(username,password) in order to connect to the database.

In other words, user and password values must be specified either as default parameters in the DataSource definition, or as arguments to DataSource.getConnection(username,password), or both; and when both are specified, then the arguments to DataSource.getConnection(username,password) override the "user" and "password" Custom Properties defined for a Data Source.

NOTE: the User parameter input in the DataSource definition can not be empty, otherwise the following exception will be generated:

java.rmi.RemoteException: Cannot deploy application sap.com/JDBCConnector_<DataSourceName>.xml

A SQLException will be thrown if either the password value is not specified in DataSource definition (user value can not be empty), or user and password values are not specified as arguments to DataSource.getConnection(username,password).

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata Database] : The UserId, Password or Account is invalid.
SQLState: 08003
Error code: 0

Message: [Teradata Database] : The parcel stream is invalid.
SQLState: 08003
Error code: 0

A SQLException will be thrown if user and password values are specified, but the specified user or password are invalid. Note that invalid user and password values specified as arguments to DataSource.getConnection(username,password) will override correct user and password values specified in Custom Properties. When DataSource.getConnection(username,password) is called but authentication fails, SAP does not "fallback" to use the values specified in Custom Properties.

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

Message: [Teradata Database] : Invalid password.
SQLState: 08003
Error code: 0

Message: [Teradata Database] : User identification is not authorized.
SQLState: 08003
Error code: 0

2.2.2. Using the DataSource

The servlet is accessed via a URL similar to the following:

http://server/servlet/servletname

In the example, the username, password and datasource name are all specified in the URL parameters:

http://charlie/servlet/SampleJdbcServlet?user=guest&password=please&datasource=teradata4

Note that user-defined variables are in italics.

Accessing the URL Parameters

 String user = req.getParameter("user"); 
 String password = req.getParameter("password"); 
 String datasource = req.getParameter("datasource");

Creating the Initial Naming Context

Context ctx = new InitialContext();

Lookup the Data Source

DataSource ds = (DataSource) ctx.lookup(datasource);

Use the Data Source to get Connections

Connection con = ds.getConnection();

At this point, the connection obtained behaves the same way as one obtained from the Driver Manager.

See Appendix A for a complete Application.

2.3. Performance Parameters

The Connection Pooling properties can be configured through the "Additional" tab in the DataSource definition window. After DataSource is defined and saved, its parameters and properties can be modified and these new changes can be saved.

The following parameters are related to the DataSource performance:

Initial Connections - generally use the default value (0).

Maximum Connections - too small of a value here can cause users of the pool to waste time waiting for someone to release a connection.

Maximum Time to Wait for Connection - generally use the default value (60).

Expiration - specify if the connections in the pool will be expired and removed.

Connection Lifetime - specify how many seconds a connection may stay in pool before it is closed.

Cleanup Thread - schedule a thread that periodically cleans up unused connection objects at a regular interval that you set in seconds.

Generally the Java applications will close the connections and return the connections back to the connection pool after finishing all the queries. However, some applications and clients, such as Business Explorer, don't close the connections they have acquired and/or other resources such as ResultSets when returning the connections to connection pool, then sometimes issues will occur, such as exceptions like "[Teradata DBMS]: Response limit exceeded". This exception when more than 15 time queries are executed within the ResultSet in the connection, but the ResultSet is not closed when returning to connection pool. These connections that are not closed and/or have unreleased resources like ResulSets should be regularly cleaned up, and the three parameters "Expiration", "Connection Lifetime", and "Cleanup Thread" can be configured for this purpose.

3. SAP BW 3.5 UD Connect Integration

SAP BW 3.5 introduces BI Java Connector that is a resource adapter compliant with the Java Connector Architecture (JCA) and is deployed into SAP NetWeaver's J2EE server. After configuring the JDBC driver, Java Connector and RFC provider on the J2EE Visual Administrator, SAP BW system can access data from Teradata to: load Master Data directly (without having to land a flat file), extract data to build a Basic Cube, or query Teradata through queries written against a Remote InfoCube.

There are 4 main components in the SAP BW system architecture system:

1. Business Explorer:
The Business Explorer (BEx) front-end provides for the end-user experience of either launching pre-built BW reports/queries or allowing interactive ad-hoc query capabilities from the Query Designer. Any of the certified BW front-end tools can be leveraged in this solution. The BEx front-end interfaces to the next component - the BW Server.

2. SAP BW Server:
The SAP BW Server houses the BW OLAP processor which interfaces to BW InfoCubes. InfoCubes are built from BW InfoObjects (Characteristics & Key Figures). Mapping BW InfoObjects to Teradata relational objects, and creating a DataSource and InfoSource for BW targets (Remote InfoCubes or Master Data) are performed in the BW Administrator (a UD Connect component also resides on the BW Server - a generic DataSource generator with an object mapping GUI to assist in creating these objects). Once the DataSource is created, it can be used to connect a Remote Info-Cube or to enable loading Master Data directly from Teradata. The BW Server interfaces to the next component "the Java Service" via an RFC mechanism.

3. UC Connect Java Components:
The Java component of UD Connect resides in the SAP Web Application Server. It is responsible for the communication between Data Sources and SAP BW. Data Sources and the Source Objects (i.e., in Teradata) are addressable via the BI Java Connectors. Via the SAP Java Connector (JCo), UD Connect provides stateless Session Beans to enable this communication. The Session Beans call Function Modules in BW via an RFC connection between the J2EE Engine and the BW Server. The JDBC BI Connector interfaces to Teradata via the Teradata Driver for the JDBC Interface.

4. Teradata Data Warehouse:
The Teradata Data Warehouse processes the SQL request, utilizing Aggregate Join Indexes if possible. After execution of the SQL Statement, the answer set is streamed back to the J2EE Engine, and then rendered in the end-user front-end tool of choice (for a Remote InfoCube target), or loaded into BW Master Data repository.

3.1. Assumptions and Prerequisites

The following assumptions and prerequisites are required for configuring and using SAP BW UD Connect system:

1. SAP NewWeaver 4.0 ABAP and Java systems have been installed completely

2. SAP BW 3.5 system is available

3. SAP NetWeaver 04 system has been upgraded with Support Package Stack 13 or higher

4. Prior to define JDBC Connector driver in Visual Administrator, the following Teradata JDBC Driver jar files need to be available on the server:

terajdbc4.jar
tdgssconfig.jar

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.

3.2. Teradata Data Modeling

Teradata V2R5.1 or greater is recommended due to potential leveraging of Aggregate Join Indexing to maximize the OLAP user experience. Additionally, it will be beneficial to utilize the Join Elimination feature due to UD Connect requirements to connect a BW DataSource to only one database object (table or view).

3.2.1. Database Modeling

Prior to configure and use SAP BW 3.5 UD Connect system, some basic BW data model will need to be prepared and documented to include the following items: dimensions, BW InfoObject technical names, types, datatypes, lengths, relationships of all objects to each other, etc.

Corresponding objects in Teradata data warehouse will need to be identified and modeled appropriately to map to each BW InfoObject. Completing the object mapping is highly recommended before proceeding.

The SAP BW UD Connect features limit the use of one database object (table or view) per BW DataSource. Therefore, creation of a set of database views on top of the Teradata relational datasource will be needed.

3.2.2. InfoCube DataSources

Since only one database object can be assigned to a BW DataSource, unless your database schema on Teradata is completely denormalized into one table, a view may be needed to pre-join any dimension tables to the fact table.

If dimension tables are not used to model hierarchical relationships, and only used to model descriptive information for key values, joining these in a view may not be necessary. You specific data model will dictate this decision.

A possible view to model would include all Characteristic InfoObjects (keys) from all dimensions plus all Key Figure InfoObjects included in the desired InfoCube. The name of this view or table will be selected for the "UD Connect Source Object" parameter used in DataSource creation.

Based on your particular model, it may be important to implement soft R.I. to enable the Join Elimination feature in Teradata, given the requirement to pre-join structures in a view for presentation to UD Connect.

An Aggregate Join Index (AJI) structure mirroring the denormalized view structure may be desireable. See section below on AJIs for more details

3.2.3. Master Data DataSources

It may be useful to construct a set of views of Master Data that mirrors the Communication Structure for the data target, and use this view in the "UD Connect Source Object" element when creating the DataSource. The required objects will be driven by the user's particular implementation, however, the following are recommended for Text Master Data and Attribute Master Data:

For Text Master Data, usually the format for the Communication Structure is the Characteristic key followed by one or more of the InfoObjects associated with Text Master Data descriptions. If your Characteristics are 'language-dependent' or time-dependent, you may require additional fields (Language Key, Date From/To) as well. Data lengths between the source object and Text Descriptions should be consistent.

Specifically for Text Master Data loading: Match the text description length in the Teradata table (possibly with a CAST statement in a view) with the corresponding BW Text field length definition(s).

InfoObject lengths for Text Master Data are as follows:

Description	InfoObject	BW length
Text: Short Description	0TXTSH	20
Text: Medium Description	0TXTMD	40
Text: Long Description	0TXTLG	60

For Attribute Master Data, a possible format is the Characteristic InfoObject key followed by all assigned Attributes (keys). If you require 'time-dependent' Attributes, additional fields may be required. Examples for Texts and Attributes are included in the step-by-step instructions for Creating a DataSource.

3.2.4. Aggregate Join Indices

The mechanism for creating and deploying Aggregate Join Indices (AJIs) in dimensional schema environments often yields significant performance improvements, and should be considered when better performance is important in the system.

An aggregate join index is a database object created using the CREATE JOIN INDEX statement and specifying one or more columns that are derived from an aggregate expression. This feature is similar to other vendor's implementation of "Materialized Views"; however Teradata AJIs cannot be directly addressed by the end user. The Teradata Optimizer determines if it is a more cost-effective access path.

The primary function of an Aggregate Join Index is to provide the Teradata Optimizer with a performant, cost-effective means for satisfying any query that specifies a frequently made aggregation operation on one or more columns. In other words, Aggregate Join Indexes permit you to define a persistent summary table without violating the normalization of the database schema. This allows a join index to precompute an aggregate value that would otherwise potentially require a table scan and sort operation.

Aggregate join indexes can be especially helpful for queries that roll up values for dimensions other that the primary key dimension, which would otherwise require re-distribution. An aggregate join index can be used to cover aggregate queries that only consider a subset of groups contained in the join index or have more join tables than the join index. In order to allow the aggregate join index to be used in this way, its definition must satisfy the following conditions:

The grouping clause must include all columns that are specified in the grouping clause of the query
All columns in the query WHERE clause that join to tables not in the aggregate join index must be part of the join index definition

Note: Referential Integrity (can be Soft, Hard or Batch) may be required in your implementation to facilitate the Optimizer choosing the AJI, especially if Dimension Tables are included in the Join Index definition. Utilizing Soft RI enables the "Join Elimination" feature which allows a "Broad AJI" to cover queries that reflect a subset of the AJI.

Your DDL should contain a REFERENCES clause similar to this:
FOREIGN KEY ( SubClassID ) REFERENCES WITH NO CHECK OPTION RDBENCHMARK.SUBCLASS_V ( SubClassID ))
Also, be sure to make all relationship columns - both PK and FK columns in every table in the join path defined as NOT NULL.

Sample AJI Syntax (using the SNOWFLAKE Data Model example):

CREATE JOIN INDEX rdbenchmark.x_Broad2_attr_ji ,NO FALLBACK ,CHECKSUM = DEFAULT AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),rdbenchmark.item.ItemID ,
rdbenchmark.color_v.ColorID ,rdbenchmark.size_v.SizeID ,rdbenchmark.subclass_v.SubClassID ,
rdbenchmark.class_v.ClassID ,rdbenchmark.department.deptid ,
rdbenchmark.the_week_v.WeekID ,rdbenchmark.the_month_v.MonthID ,
rdbenchmark.the_quarter_v.QuarterID ,rdbenchmark.the_year_v.YearID ,
rdbenchmark.location.LocID ,rdbenchmark.district_v.DistID ,
SUM(rdbenchmark.sales_history.ItemQty )(FLOAT, NAMED ItemQty ),
SUM(rdbenchmark.sales_history.ItemRev )(FLOAT, NAMED ItemRev ),
SUM(rdbenchmark.sales_history.ItemCost )(FLOAT, NAMED ItemCost )
FROM rdbenchmark.size_v ,rdbenchmark.color_v ,rdbenchmark.sales_history ,
rdbenchmark.item ,rdbenchmark.subclass_v ,rdbenchmark.class_v ,
rdbenchmark.department ,rdbenchmark.the_day_v ,rdbenchmark.the_week_v ,
rdbenchmark.the_month_v ,rdbenchmark.the_quarter_v ,rdbenchmark.the_year_v ,
rdbenchmark.location ,rdbenchmark.district_v
WHERE ((((((((((((rdbenchmark.sales_history.ItemID = rdbenchmark.item.ItemID )
AND (rdbenchmark.item.ColorID = rdbenchmark.color_v.ColorID ))
AND (rdbenchmark.item.SizeID = rdbenchmark.size_v.SizeID ))
AND (rdbenchmark.item.SubClassID = rdbenchmark.subclass_v.SubClassID ))
AND (rdbenchmark.subclass_v.ClassID = rdbenchmark.class_v.ClassID ))
AND (rdbenchmark.class_v.DeptID = rdbenchmark.department.deptid ))
AND (rdbenchmark.sales_history.DayDT = rdbenchmark.the_day_v.DayDT ))
AND (rdbenchmark.the_day_v.WeekID = rdbenchmark.the_week_v.WeekID ))
AND (rdbenchmark.the_week_v.MonthID = rdbenchmark.the_month_v.MonthID ))
AND (rdbenchmark.the_month_v.QuarterID = rdbenchmark.the_quarter_v.QuarterID ))
AND (rdbenchmark.the_quarter_v.YearID = rdbenchmark.the_year_v.YearID ))
AND (rdbenchmark.sales_history.LocID = rdbenchmark.location.LocID ))
AND (rdbenchmark.location.DistID = rdbenchmark.district_v.DistID )
GROUP BY rdbenchmark.item.ItemID ,rdbenchmark.color_v.ColorID ,
rdbenchmark.size_v.SizeID ,rdbenchmark.subclass_v.SubClassID ,
rdbenchmark.class_v.ClassID ,rdbenchmark.department.deptid ,
rdbenchmark.the_week_v.WeekID ,rdbenchmark.the_month_v.MonthID ,
rdbenchmark.the_quarter_v.QuarterID ,rdbenchmark.the_year_v.YearID ,
rdbenchmark.location.LocID ,rdbenchmark.district_v.DistID
PRIMARY INDEX ( ItemID ,ColorID ,SizeID ,SubClassID ,ClassID ,
deptid ,WeekID ,MonthID ,QuarterID ,YearID ,LocID ,DistID );

If AJIs are utilized, Teradata V2R5.1 at a minimum is highly recommended (although not required), due to Optimizer improvements and exploitation of the AJI structures.

In addition to AJIs, one might want to consider Partitioned Primary Index implementations. For example, use AJI down to week-level aggregation, but partition the fact table on date to resolve date-based queries. This keeps the AJI lean while exploiting other Teradata features.

AJIs are not mandatory for this solution, but using AJIs can significantly improve performance.

3.3. Java Connector and RFC Provider via Visual Administrator

3.3.1. Start the Administration Tool

Note: On UNIX, make sure that the DISPLAY environment variable is set to <host_name>:0.0, where <host_name> is the host on which the administration tool will be displayed.

Enter the following command to start the administration tool:
UNIX:
/usr/sap/<SAPSID>/<Instance_Name>/j2ee/admin/go
Windows:
<driver>:\usr\sap\<SAPSID>\<Instance_Name>\j2ee\admin\go.bat

The "J2EE Engine - Administration" screen with the dialog box "Connect to J2EE Enngine" appears.

For SAP J2EE standalone system, choose "Connect" to use the "Default" login and enter the password for the Administrator user of the J2EE engine.

For SAP J2EE add-in system, the "Default" login can not be used. The following steps need to be performed:

Choose "New"
Enter a display name and choose "Direct Connection to a dispatcher Node"
Choose "Next"
Enter the following information:
- User Name: J2EE_ADMIN
- Host: <host_name> of the J2EE Engine
- Port: 5<Instance_Number_of_SAP_J2EE_Engine>04
Choose "Save" and connect with the new login account by choosing "Connect"
Enter password for the J2EE_ADMIN user and choose "Connect"

3.3.2. Define the JDBC Connector Driver

In the dialog box, enter the name of the JDBC driver definition, and click "OK".

Navigate to the directory where you store the JDBC driver jar files, and select a .jar file, and click "OK".

A confirmation screen will appear asking "Do you want to select other files ?", click "Yes" to add more jar files.

All the Teradata JDBC Driver jar files are needed for this step. Make sure you go back to add more jar files after each selected jar file. After finishing all the jar files, click "No". Now the new JDBC Connector driver is created with the specified jar files.

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.

Now the JDBC Connector driver has been configured as shown below.

3.3.3. Configure the Java Connector

In the Visual Administrator window, expand "Cluster", and then "Server", and then "Services".
Scroll down and click "Connector Container".
On the right pane, under "Connectors" tree, locate "sap.com/com.sap.ip.bi.sdk.dac.connector.jdbc" and expand it,
Click on the default Java Connector "SDK_JDBC" (its name is "BI_JDBC_CONNECTOR") to bring up the Java Connector configuration window.

The default Java Connector configuration template includes 5 tabs: "General", "Resource Adapter", "Managed Connection Factory", "Security", and "Drivers".

In the "Resource Adapter" tab, click "Add" to add the previously defined JDBC Connector driver to the "Loader References", as shown below.

Note: after the string "library:", the Loader Reference name must match the defined JDBC driver name, such as "library:terajdbc". Click OK.

Now the new library has been added into the Loader References:

Once the JDBC driver has been added in the Loader References, click "Managed Connection Factory" tab.

Then click "Properties" tab to display the Teradata specific configuration properties screen. Highlight each configuration property and enter user-specified value for the property key, and then click "Add" to make the changes.

The following configuration properties are needed for the "Properties" tab:

DriverName 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
FixedCatalog	leave blank
FixedSchema	<database_schema_name>
URL	jdbc:teradata://DataBaseServer/parameter_list
UserName	the username to connect to the database
Password	the password to connect to the database

When all the changes for the configuration properties are completed, click "save" button to save the configurations.

SAP Java Connector system provides a smoke test to validate if these configurations for UD Connect are correct. The smoke test will test this default Java Connector "BI_JDBC_CONNECTOR" to verify the connectivity to the specified Teradata database system in the properties tab by reading all the tables in that database.

To run the smoke test, execute the following URL in the browser (server and port are the placeholders for your SAP system):
http://<server>:<port>/TJdbc/servlet/TestJdbc

Note: port number is like 5<Instance_Number_of_SAP_J2EE_Engine>00, such as 50000 when Instance_Number_of_SAP_J2EE_Engine is 00.

The following is an example output of the smoke test:

After the smoke test has successfully validated the default Java Connector configuration, this default Java Connector needs to be cloned based on the following procedures to be accessible during the mapping process in SAP BW system.

To clone the default Java Connector, highlight the "SDK_JDBC" connector in the Connector Container window, click the double paper icon to clone this connector.

The following pop-up screen appears asking the "resource adapter jndi name" for the new Java Connector being cloned for:

Make sure the jndi name starts with "SDK_" in order for it to visible during the mapping process in SAP BW system, such as SDK_BI_JDBC_CONNECTOR.

Now the Java Connectors have been configured as shown below.

3.3.4. Configure the RFC Provider in Visual Administrator

After configuring the Java Connectors in the Visual Administrator, an RFC destination must be registered to enable the communication between SAP BW server and the SAP J2EE engine.

An RFC is a protocol used by SAP that allows communication from SAP BW system to external data sources. The RFC server you define here will be the same RFC destination that will be defined on the BW system side as well. This allows the RFC when started to register itself to the SAP BW 3.5 system. The following describes the procedures for registering the SAP BW system as an RFC destination in the Visual Administrator.

In the Visual Administrator window, expand "Cluster", and then "Server", and then "Services".
Scroll down and click "JCo RFC Provider".
On the right pane, click "Runtime" tab, and a new RFC destination template is displayed under "Bundles" tab.
Enter all the needed parameters for both the "RFC destination" and "Repository" sections.

The following configuration parameters are needed for the "RFC destination" section:

Program ID: <user-defined JCo Server ID>
Gateway host: <gateway_host_name>
Gateway service: <gateway_server_name>
Number of processes(1..100): <number_of_servers_running_simultaneously>

Note: by default the Number of processes will be set to 20. This number is configurable to a maximum value of 100. To change this number click on the "Properties" tab in the "JCo RFC Provider" window, and highlight the value field of MaxProcesses and enter the Number of processes you would like to set. Click "Update" to add the changes. Also the MaxConnections can be set to a maximum value of 100. The MaxProcesses and MaxConnections parameters should be configured based on the BW system environment configuration, for example, higher values are needed for complex multiple-provider BW systems.

The following configuration parameters are needed for the "Repository" section:

Application server host: <application_server_host>
System number: <sap_system_number>
Client: <client_number>
Language: <connection_language_code>
User: <client_connection_user_name>
Password: <client_connection_password>

If the SAP BW system is a Unicode system, make sure the "Unicode" box is checked.

When finishing all these parameters, click on "Set" to add the new RFC destination.

Note: after successful RFC destination configurations, a running man ico is displayed beside the RFC destination:

The same RFC destination configuration will need to be performed in SAP BW system environment through SAP GUI, This configuration procedures will be discussed in the following BW Administrator section.

3.4. BW DataSource via SAP BW Administrator

3.4.1. Start SAP Front End Logon GUI

Use the Desktop "Start" button as follows:

Start → Programs → SAP Front End → SAP Logon

The "SAP Logon 640" window appears. Then click "New Item" to create a new entry for the SAP system.

The following information is needed for creating the new entry:

Description: the description name for the SAP system entry
Application Server: <SAP_server_host_name>
System ID: <SAP_system_id>
System Number: <SAP_system_instance_number>
SAP System: R/3

After the needed parameters are saved, click "Log on". On the Logon Window, the client number, user, and password are required to log on to the system.

3.4.2. Configure the RFC Provider in SAP GUI

The RFC destination configurations have been defined in Visual Administrator in previous section above. These RFC destinations are also required to be configured in SAP GUI so the communication between SAP BW system and J2EE engine can be enabled.

In SAP Logon GUI, use "RFC Destinations (Display/Maintain)" (transaction SM59) to enter the "Display and Maintain RFC Destinations" screen. Click "Create" to create a new RFC destination.

Enter RFC Destination name (for consistency it is recommended that the RFC destination name should be the same as the Program ID)
Enter Connection type as 'T'
Select radio button "Registered Server Program"
Enter Program ID name (this name is the RFC destination name defined in Visual Administrator)
Enter Gateway host and Server, when finished click Save

There are 2 test buttons that can be used to verify the RFC configurations. Click "Test Connection" will verify the communication between SAP BW system and J2EE engine, and "Unicode Test" button is used to verify if the SAP system is a Unicode system. The following screen shot displays a successful RFC connection.

3.4.3. Start BW Administrator Workbench

All the following procedures for creating BW DataSources, InfoObjects, and InfoCubes will be performed in SAP BW Administrator Workbench.

In the SAP Logon GUI, use "BW Adminstrator Workbench" (transaction RSA1) to start BW Administrator Workbench.

In BW Administrator Workbench, the following processes will be done for SAP BW UD Connect system configurations:

1. Creation of Generis DataSource
The following section illustrates the detailed procedures for the generic process of creating the DataSource. This process is similar regardless of what type of data target is desired (InfoCube, Text or Attribute Master Data), however the selection of InfoObjects to include in the DataSource will vary based on the intended data target.

2. Connecting DataSource to Data Target through InfoSource
Once the DataSource is successfully created, the procedures for connecting the Data Source to the target will vary based if the target object is Master Data or a Remote InfoCube. These procedures will be discussed in the section below.

3.4.4. Create BW Generic DataSource

SAP BW system can access data from Teradata database systems through UD Connect integration system. DataSources are responsible for extracting and staging data from source systems like Teradata database system. The DataSources subdivide the data that is provided by a source system into self-contained business areas.

There are 2 scenarios covering the data acess from BW system to Teradata database system: Master data load (Attributes or Text only with current UD Connect integration system for Teradata system) or Remote InfoCube.

Master data is data that remains unchanged over a long period of time. Master data contains information that is always needed in the same way. Characteristics can bear master data in BW (attributes or text only).

Remote InfoCube can be used as an InfoProvider in BEx Reporting. You can access the characteristics and key figures defined for an InfoCube in the Query Definition in the BEx Web or in the BEx Analyzer.

The following prerequisites are required for creating Generic DataSources:

Teradata data modeling implementation described in earlier sections
Complete configurations for Connector Container and JCo RFC Provider in Visual Administrator
- RFC Destination: the RFC destination defined in Visual Adminstrator
- UD Connect Java Source: the BI Java Connector defined in Visual Administrator
- UD Connect Source Object: the relevant Teradata database table or view

The following sections describe the procedures for creating and activating BW DataSource.

3.4.4.1. Prepare Required InfoObjects

In the BW Administrator Workbench, highlight "InfoObjects" on the left pane under Modeling section, on the right pane, right click on the InfoObjects column and select "Create InfoArea..." to create an InfoArea for the target InfoObjects.

Highlight the InfoArea, and right click on it to select "Create InfoObject Catalog..." to create an InfoObject Catalog to hold the target InfoObjects. After the InfoObject Catalog is created, right click on it to create all required InfoObjects for the intended BW Generic DataSource.

The following configuration parameters are needed for creating a Characteristic InfoObject:

Name: InfoObject Name
Long description: the long description for the InfoObject
Short description: the short description for the InfoObject
General Tab
- Data Type: NUMC - Character string
- Length: length of the data
- Convers. Rout.: leave blank
Master data/texts Tab
- Short text exists: checked if there existing short-length text data
- Medium text exists: checked if there existing medium-length text data
- Texts language dependent: unchecked
- Character is data target/InfoProvider: checked
- InfoArea: the InfoArea name

The Key Figure InfoObjects are used in the InfoCube implementation. To create a Key Figure InfoObject, the following configuration parameters are required:

Name: InfoObject Name
Long description: the long description for the InfoObject
Short description: the short description for the InfoObject
Type/unit Tab
- Type/data Type: the data type for the Key Figure

After all the required configuration parameters are completed, save and activate the InfoObject.

3.4.4.2. Create and Configure InfoSource

In the BW Administrator Workbench, highlight "InfoSources" on the left pane under Modeling section, on the right pane, right click on the InfoSources column and select "Create application component..." to create an application for the target InfoSources and DataSources.

Highlight the application, and right click on it to select "Create InfoSource..." to create an InfoSource.

The "Create InfoSource" window appears. The following parameters are needed for this step:

Flexible Update in any Data Target (Except Hierarchies: checked
InfoSource: the InfoSource name
Long description: the long description for the InfoSource

Confirm to create the InfoSource object.

From InfoSources Window, right click on the newly created InfoSource name and select Change

In the InfoSource Change Screen, add the required InfoObjects to the "Communication Structure". For example, for Text Master Data Load InfoSource, a Characteristic InfoObject and some Text InfoObjects (such as Short, Medium or Long Text) are needed to create a simple Text Master Data Load InfoSource; for Attribute Master Data Load InfoSource, a primary Characteristic InfoObject and some Attribute InfoObjects are used to create an Attribute Master Dara Load InfoSource; for a Remote InfoCube, all of the Characteristic InfoObjects (key fields) in the Dimensions and KeyFigure InfoObjects plus some Calendar InfoObjects can be used for constructing the InfoCube targets.

After all these configurations are done, save and activate the InfoSource object. Select yes on the dialog window for the question: "Do you want to activate all independent transfer programs ?".

3.4.4.3. Create and Activate BW DataSource

When the above InfoSource object has been created, the BW DataSource can be created through the InfoSource. The following describes the procedures for creating and activating the BW DataSource.

In the InfoSource change screen, select the top menu "Extras" and then click "Create BW DataSource with UD Connect".

The "Assigning UD Connect Source Object to BW DS and Extr.Gen." window appears. The following parameters are needed for this step:

RFC Destination: the RFC Destination to the J2EE Java Service
UD Connect Source: the BI Java Connector defined in J2EE Container COnnector Service
UD Connect Source Obj.: the relevant Teradata database table or view

Click "Extract Source Object Elements" to populate the Source Object Elements. BW will attempt to heuristically map the objects. If the field mapping is not done by the system, highlight each object (one at a time) on the left hand side of the BW DataSource Fields and the Source Object Elements and then use the left arrow to transfer the mapping.

Once the field mapping is completed, select the "Generate Datasource (for UD Connect)" button. Select 'Yes' to the pop-up dialog windows if the proposals are acceptable.

You will be returned to the InfoSource (Communication Structure / Transfer Structure) screen. Click "Transfer_Structure/Transfer_Rules" to expand the section. Choose "IDoc" for "Transfer Method".

Save and activate the InfoSource object.

3.4.5. Connect DataSource to Data Target for Characteristic InfoObject

In the BW Administrator Workbench, highlight "InfoProvider" on the left pane under Modeling section, on the right pane, locate the InfoArea name that was created in the previous section. Right click on the InfoArea name and select "Insert Characteristic as a Data Target".

Enter the InfoObject name and click OK. If the InfoObject does not appear in the drop down, just type it in manually. The following screen shows the result after the above operation.

When you exit and return to the InfoProvide screen, the screen will be updated as follows:

Right click on the Characteristic Data Target and select "Create Update Rules". Select the correct InfoSource name defined previously in the above section, and press "Enter". Save and activate the Update Rules. The update rule appears under the Data Target name.

After the Update Rules are created, the procedures for creating InfoPackages will be performed. The following discusses the steps for creating InfoPackages.

In the BW Administrator Workbench, highlight "InfoSources" on the left pane under Modeling section, on the right pane, locate the DataSource name under the corresponding application and InfoSource names that have been defined in the above sections. Right click on the DataSource name and select "Create InfPackage".

Enter the InfoPackage description and click Save button to save the definition. The Scheduler (Maintain InfoPackage) screen appears.

The Scheduler (Maintain InfoPackage) screen includes 5 tabs: "Data Selection", "Processing", "Data Targets", "Update", and "Schedule".

In the "Data Selection" tab, highlight the InfoObject rows and click "Check".

In the "Processing" tab, configure the Update Data selection (use the default is recommended).

In the "Data Targets" tab, choose "Select Data Targets" and check the checkbox for the Data Target.

Click Save button to save the changes. In the "Schedule" tab, choose "Start Data Load Immediately" and click "Start" to run immediately, or choose "Start Later in Background" to schedule for future extraction.

Click Monitor icon to monitor the data load. The following screen shows a successful data load of the Text Master Data.

The following describes the way to view the loaded Master Data. In the BW Administrator Workbench, highlight "InfoObjects" on the left pane under Modeling section, on the right pane, locate the target InfoObject to enter the InfoObject screen. Click on the "Master data/texts" tab, double click on the Text table name in the box.

The Display Table screen appears. Click the menu button "Contents" to bring up the "Data Browser" window for the table, and then click the menu button "Execute" to display the data.

3.4.6. Connect DataSource to Remote InfoCube

Enter the technical name and description for the InfoCube, and select the corresponding InfoSource name under InfoCube Type section.

Click "paper" icon to create the cube.

The Edit InfoCube screen includes 3 tabs: "Characteristics", "Time characteristics", and "Key figures".

In the "Characteristics" tab, select "Dimensions..." tab to assign characteristics to dimensions. On the "Define Dimensions" screen, use the "Define" tab to enter descriptions for the dimensions. Use the "Assign" tab to assign the characteristics to dimensions, Click green mark when done.

Save and activate the InfoCube object.

The next step is to assign a source system to the InfoCube. On the "InfoProvider" screen, right click the Remote InfoCube and select "Assign Source Systems".

Highlight the needed source system and click save button.

Now the source system has been added to the Remote InfoCube.

3.5. Performance Parameters

When defining and configuring the Java Connectors through Connector Container service in the Visual Administrator, some configurations with the connections can be used to enhance performance and avoid run-time issues.

The Connection Definition properties for the Java Connectors can be configured through the "Connection Definition" tab under "Managed Connection Factory" tab in the "Connector Container" service in Visual Administrator.

The following parameters are related to the Connection run-time performance:

Expiration - specify if the connections in the pool will be expired and removed.

Connection Lifetime - specify how many seconds a connection may stay in pool before it is closed.

Cleanup Thread - schedule a thread that periodically cleans up unused connection objects at a regular interval that you set in seconds.

Maximum Connections - too small of a value here can cause users of the pool to waste time waiting for someone to release a connection.

Maximum Time to Wait for Connection - generally use the default value (120).

4. Troubleshooting

4.1. JDBC Driver Debugging

Teradata JDBC driver can output the necessary detailed info and error messages for application debugging. To enable the debugging message availability, some configurations with Visual Administrator are required, which are described below.

In Visual Administrator, expand "Cluster", and then "Server", and then "Services".
Scroll down and click "Log Configurator". On the right pane, click on "Location" tab, and then expand System item. Click on "out" line to configure the System.out configurations. Use the drop-down menu to select "Debug". Click on Save and select "Apply to current node only".

To enable the JDBC debugging with Data Source definition with "JDBC Connector" service, follow the descriptions below.

On the right pane of the "JDBC Connector" section in the Visual Administrator, click on "DataSources", Expand the target Data Source definition and open the "Main" tab. The Database URL property needs to be added with the additional parameter "LOG=debug". And then save the changes.

The following procedures illustrate the approach to enable the JDBC debugging with Java Connector configurations with "Connector Container" service.

On the right pane of "Connector Container" section, locate "sap.com/com.sap.ip.bi.sdk.dac.connector.jdbc" and expand it to locate the target Java Connector. Open the "Managed Connection Factory" tab and click the "Properties" tab. Highlight the URL key to add the additional parameter "LOG=debug" in the URL string value. Click on the Save button to save the changes.

The debugging output messages will be stored in the following locations after the configurations are completed:

UNIX:
/usr/sap/<SAPSID>/<Instance_Name>/j2ee/cluster/server0/log
Windows: <driver>:\usr\sap\<SAPSID>\<Instance_Name>\j2ee\cluster\server0\log

4.2. SQL Trace

SQL tracing message can be captured on Teradata via standard Query Logging facilities (DBQL):

Rudimentary SQL logging syntax:
BEGIN QUERY LOGGING WITH SQL LIMIT SQLTEXT=10000 ON USERNAME;

When query capture is complete, end query logging with:
END QUERY LOGGING WITH SQL ON USERNAME;

Display the captured SQL with the following:
DATABASE DBC;
SEL USERID, STARTTIME,LOGONDATETIME, DATABASENAMEI, QUERYTEXT FROM QRYLOG Q, DBASE D
WHERE Q.USERID=D.DATABASEID
AND DATABASENAMEI='USERNAME'
ORDER BY STARTTIME DESC , LOGONDATETIME DESC

Cleanup the captured SQL logs with:
DEL FROM DBQLOGTBL WHERE
DBC.QRYLOG.USERID=DBC.DBASE.DATABASEID
AND DBASE.DATABASENAMEI='USERNAME'

4.3. BW System Trace

SAP BW system outputs an SDK_BI_Trace.log file that displays the SQL that was generated base on the query created by the end user. The log file also shows the database table name and all columns that were selected as well as the total time it took for query processing.

The BW system tracing output messages are located in the following locations:

UNIX:
/usr/sap/<SAPSID>/<Instance_Name>/j2ee/cluster/server0/BI_SDK_Trace.log
Windows:
<driver>:\usr\sap\<SAPSID>\<Instance_Name>\j2ee\cluster\server0\BI_SDK_Trace.log

Note: Please Refer to SAP BI UD Connect: Performance Issues guide for more information about the trace that is retrieved from this log file. The guide can be found at: http://help.sap.com/saphelp_nw04/helpdata/en/6c/7ffb3f6c78ee28e10000000a1550b0/frameset.htm Under Information Integration click on Business Intelligence (BI).

Appendix A: SAP Web Application Server Code Samples

Note: The creation of an SAP Web Application Server 6.40 datasource "ds1" creates a JNDI name of "jdbc/ds1" for the datasource. The name with the "jdbc" prefix must be used when accessing the following servlet.

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();
   }
 }

Teradata Download Center / Teradata JDBC Driver / Teradata JDBC Documentation

WebLogic Application Server "How-To" Guide

Table of Contents

1. Introduction

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

For shared TeraGSS configuration using the

The classpath must include

1.6. Overview - Data Source in WebLogic Server

2. WebLogic 8.1

2.1. Connection Pool via Administrative Console

2.1.1. Prerequisites

2.1.2. Start the Administrative Console

2.1.3. Open JDBC Connection Pools

2.1.4. Set Connection Pool Parameters

2.1.5. Create the Pool

Field

Value

Example

Name

Driver Classname

URL

Database User Name

Password

2.1.6. Assign the Pool

2.2. Create JDBC Data Source

2.2.1. Start the Administrative Console

2.2.2. Open JDBC Data Sources

2.2.3. Set Data Source Parameters

Field

Value

Example

Name

JNDI Name

Honor Global Transactions

Emulate Two-Phase Commit for non-XA Driver

2.2.4. Assign the Data Source

2.3. Accessing the DataSource from a Java Application

2.3.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

2.3.2. Using the DataSource

2.4. Performance Parameters

2.4.1 Advanced Options

3. WebLogic 9.x and WebLogic 10.x

3.1. Create JDBC Data Source

3.1.1. Prerequisites

3.1.2. Start the Administrative Console

3.1.3. Open JDBC Data Sources

3.1.4. Set Data Source Parameters

Field

Value

Example

Name

JNDI Name

Database Type

Database Driver

3.1.5. Set the Transaction Options

3.1.6. Set the Connection Properties

Field

Value

Example

Database Name

Host Name

Port

Database User Name

Password

3.1.7. Test the Database Connection and Connection Properties

Field

Value

Example

Driver Class Name

URL

Database User name

Password

Confirm Password

Properties

Test Table Name

3.1.8. Assign the Data Source