Teradata JDBC Driver Frequently Asked Questions


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.

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 are padded with trailing spaces per the Teradata Database's Character 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. Alternatively, connection parameter CHARSET=UTF16 is recommended for applications that require fixed-width CHAR data values without trailing space padding.


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

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

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 setFetchSize method is supported beginning with Teradata JDBC Driver 13.10.00.25 and Teradata Database 13.10.

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. When the application specifies a row count with the setFetchSize method, the Teradata Database uses that row count possibly to reduce the size of the subsequent response messages, but never to increase the size above 1MB.


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.

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 batch size of 50,000 to 100,000 should be considered the minimum for JDBC FastLoad; you may need to increase your JVM heap size to accommodate that. 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 Unity Director?

Answer

Teradata Session Reconnect is available beginning with Teradata JDBC Driver version 13.10.00.24. Beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.12, Teradata Session Reconnect can be augmented with the Recoverable Network Protocol so that reconnection is supported for a variety of failure events, including transient network failures. Prior to Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.12, Teradata Session Reconnect only supports reconnection after a Teradata Database restart; it does not support reconnection after other events, such as transient network failures.

Teradata Session Reconnect and Recoverable Network Protocol are enabled or disabled through a combination of Teradata Database and Teradata JDBC Driver configuration parameters; specifically, the Teradata Database dbscontrol fields RedriveProtection (67), RedriveDefaultParticipation (68), and DisableRecoverableNetProtocol (77), and the Teradata JDBC Driver connection parameters REDRIVE, RECONNECT_COUNT, and RECONNECT_INTERVAL.

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.

Teradata Session Reconnect may or may not be useful in an application server environment with connection pooling. The reconnection attempt must occur within a limited time to be successful; that limited time is dictated by the Teradata Database gtwcontrol -t option for the disconnected session reconnect timeout. 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 remains unused past the limited time cannot be reconnected to the Teradata Database.

If you are using Unity Director, then you should set your application server's connection pool purge policy to "bad connection only", because Unity 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 Unity 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.