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.