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.

Table of Contents

1. Introduction

2. WebLogic 8.1

3. WebLogic 9.x and WebLogic 10.x

4. WebLogic 10.3

5. Troubleshooting

Appendix A: WebLogic Code Samples

1. Introduction

1.1. Assumptions

1.2. Purpose and Organization

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

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

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:

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

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.

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.

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:

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

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

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

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.

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.

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.

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.

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)

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.

3.2.2. Using the Data Source

The following code creates the initial context:

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

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

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

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.

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.

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.

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.

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.

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.

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.

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)

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.

4.2.2. Using the Data Source

The following code creates the initial context:

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

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

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.

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.

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

5. Troubleshooting

5.1. Session Defaults Warning

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