This chapter provides information for troubleshooting problems that can occur when using the Teradata JDBC Driver.
Error 804 with SQLState 08S01 and the error message "Socket communication failure for Packet receive" (or "Packet transmit") means that a network communication failure occurred.
[Error 804] [SQLState 08S01] Socket communication failure for Packet receive ...
[Error 804] [SQLState 08S01] Socket communication failure for Packet transmit ...
A network communication failure can occur due to a variety of reasons. Here is a list of common causes of connectivity problems, in order from most likely to less likely:
Using Teradata JDBC Driver 17.20 with Java 7 to connect to a database with HTTPS/TLS enabled may encounter javax.net.ssl.SSLHandshakeException: Received fatal alert: handshake_failure
This failure can occur because early builds of Java 7 do not enable TLSv1.2 automatically.
The solution is to explicitly enable TLSv1.2 by specifying the Java command line argument: -Dhttps.protocols=TLSv1.2
Another solution is to switch to a later build of Java 7. Build 1.7.0_131-b31 and later enable TLSv1.2 by default.
Teradata Database V2R6.2 introduced support for the SQL data type BIGINT (64-bit integer) and introduced the Large Decimal feature, which expands the maximum precision for the DECIMAL data type to DECIMAL(38). Teradata Database V2R6.1 and earlier releases are limited to a maximum precision of DECIMAL(18).
Maximum precision varies by database release. This affects how numeric data is handled in the Teradata JDBC Driver. If Large Decimal is not supported, the maximum precision for BigDecimal is 18. If Large Decimal is supported, the maximum precision value is 38.
The Teradata JDBC Driver modification allows the PreparedStatement.setBigDecimal method to throw a DataTruncation exception for BigDecimal values that have precision values greater than the maximum precision.
When the PreparedStatement setBigDecimal method is used to bind multiple values to a parameter, the Teradata JDBC Driver determines the largest number of integral digits bound to the parameter, and then the fractional digits for each of the values is rounded as necessary to fit within the database limit of maximum precision for a DECIMAL value. The method PreparedStatement.setLong in the Teradata JDBC Driver throws a DataTruncation exception if the maximum precision value is greater than 18 and the SQL data type BIGINT is not supported for the current database.
Using connection parameter CHARSET=UTF8 with fixed-width CHAR data type result set column values adds trailing space padding per the database's Character Export Width behavior. The CHAR(n) data type is a fixed-width data type (holding n characters), and the database reserves a fixed number of bytes for the CHAR(n) data type in response spools and in network message traffic.
UTF8 is a variable-width character encoding scheme that requires a varying number of bytes for each character. When the UTF8 session character set is used, the database reserves the maximum number of bytes that the CHAR(n) data type could occupy in response spools and in network message traffic. When the UTF8 session character set is used, the database appends padding characters to the tail end of CHAR(n) values smaller than the reserved maximum size, so that the CHAR(n) values all occupy the same fixed number of bytes in response spools and in network message traffic. In contrast, when using the UTF16 session character set, no character padding is added.
The following example illustrates how to 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.
CREATE TABLE MyTable (Column1 CHAR(10), Column2 CHAR(10))
SELECT Column1, Column2 FROM MyTable
SELECT CAST(Column1 AS VARCHAR(10)), TRIM(TRAILING FROM Column2) FROM MyTable
CREATE VIEW MyView (C1, C2) AS SELECT CAST(Column1 AS VARCHAR(10)), TRIM(TRAILING FROM Column2) FROM MyTable
Alternatively, connection parameter CHARSET=UTF16 is recommended for applications that require fixed-width CHAR data values without trailing space padding.
The Teradata JDBC Driver does not support specifying USING clauses in any kind of SQL request. The Teradata JDBC Driver does not support named parameter markers. The Teradata JDBC Driver only supports unnamed question-mark parameter markers.
Specifying a USING clause in a SQL request will cause a database error such as the following:
[Error 3889] [SQLState 42000] Too many USING clauses in a request.
The solution is to remove the USING clause from the SQL request, replace any named parameter markers with question-mark parameter markers, and use JDBC PreparedStatement/CallableStatement setXXX methods to bind values to the question-mark parameter markers.
The following error may be seen when creating or dropping a database object, such as a table or stored procedure. It will include an error code of 2631 and an SQL state of 40001, which indicates that this is a retryable error:
com.teradata.jdbc.jdbc_4.util.JDBCException:[Teradata Database]: Transaction ABORTed due to deadlock.
If this error occurs, the application can choose to wait a short time and then resubmit the failed create or drop operation.
The following error may be seen when using JDBC FastLoad and calling a PreparedStatement setter method. It will include an error code of 2631 and an SQL state of 40001, which indicates that this is a retryable error:
com.teradata.jdbc.jdbc_4.util.JDBCException:[Teradata Database]: Transaction ABORTed due to deadlock
If this error occurs, the application can choose to wait a short time and then call the PreparedStatement setter method again. Note that error 2631 may be in a chain of exceptions; it therefore is necessary to walk down the chain of exceptions to get to error 2631.
A potential deadlock condition can occur with two separate applications, or a single application using two threads, with each thread or application having its own JDBC connection to the database.
The problem occurs when one connection is inserting data into a table, while the other connection is attempting to read data from the same table.
When using the default transaction isolation level of TRANSACTION_SERIALIZABLE, the following error may be seen on the thread or application that is reading from the table, approximately 2 to 5 minutes after the situation occurs. It includes an error code of 2631.
com.teradata.jdbc.jdbc_4.util.JDBCException:[Teradata Database]: Transaction ABORTed due to deadlock.
If this error occurs, either:
Note: The transaction level is set using the java.sql.Connection.setTransactionIsolation method. Though this prevents the problem from occurring, it has the side effect of allowing dirty, non-repeatable, and phantom reads. Whether or not this is acceptable must be determined on an individual application basis.
The database offers Large Object (LOB) data types for storing binary data (BLOB) and character data (CLOB). The database also offers complex data types that are based on LOBs, such as XML, JSON, and ST_Geometry. In addition, User-Defined Types (UDTs) can be created that are based on LOBs. The Teradata JDBC Driver supports all LOB data types, and also supports data types based on LOBs.
In the database, up to 32 LOB columns can be defined in a table. BLOBs and CLOBs closely resemble the VARBYTE and VARCHAR data types.
LOB values up to 2 GB in size are supported. For a result set containing LOB columns, the database transmits LOB locators to the Teradata JDBC Driver, so only the LOB locators (not the LOB values) are counted towards the database's limit on ResultSet row size. The application can retrieve Blob or Clob objects from a ResultSet and subsequently insert those Blob or Clob objects into another table. Because LOB locators are transmitted between the database and the Teradata JDBC Driver, this process avoids transmitting potentially large LOB values back and forth over the network.
The most likely cause of the following error from the database is that the application is not properly closing ResultSet objects and Statement objects.
SQLState: HY000
Message: [Teradata Database] : Response limit exceeded.
Vendor: 3130
This error message refers to the response from an SQL request, which is the output from a single-statement SQL request or a multi-statement SQL request.
The response limit is a limit imposed by the database of a maximum of 16 open responses per connection.
To solve this issue, examine applications to verify that they have proper exception handling, with finally blocks coded to ensure that ResultSet objects, Statement objects, LOB InputStream, and LOB Reader objects are always closed as soon as they are no longer needed. If the application modifies LOBs, then Blob and Clob objects must be freed in finally blocks.
Beginning with Teradata JDBC Driver 14.00.00.28, result set holdability CLOSE_CURSORS_AT_COMMIT is supported. A "holdable" result set stays open across a commit, and is the default behavior provided by the Teradata JDBC Driver. If the application does not need holdable result sets, then the application can specify CLOSE_CURSORS_AT_COMMIT holdability, so that the Teradata JDBC Driver automatically closes result sets when an auto-commit or an explicit commit occurs. Refer to the Connection.setHoldability method for more information.
The application cannot rely on garbage collection to close ResultSet objects, Statement objects, LOB InputStream, and LOB Reader objects, since the Java programming language does not guarantee the timeliness of garbage collection. The FINALIZE_AUTO_CLOSE connection parameter is available beginning with Teradata JDBC Driver version 14.00.00.08. The FINALIZE_AUTO_CLOSE connection parameter controls the Teradata JDBC Driver's behavior during garbage collection. For more information, see Making a Database Connection.
Beginning with Teradata JDBC Driver version 14.00.00.08, if the LOG=INFO connection parameter is specified, then the Teradata JDBC Driver will log information about all outstanding open responses when a database Error 3130 occurs. The logged information will include each open response's request number, date/time of submission, submitter thread ID, SQL request text, and submitter call stack. This information can be useful to an application developer to help pinpoint which places in the application are submitting SQL requests and not subsequently closing them.
...
2011-12-21.08:56:41.701 TERAJDBC4 INFO [Thread-8] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@17757ad Response limit exceeded. Open response 11 of 16 is request number 154 submitted 2011-12-21.08:56:40.562 by [Thread-8]
SELECT * FROM MyTable ORDER BY 1
at com.teradata.jdbc.jdbc_4.ResponseTracker.notifyReceiveResponse(ResponseTracker.java:64)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:168)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:121)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:112)
at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:373)
at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:315)
at com.teradata.jdbc.jdbc_4.TDStatement.doNonPrepExecuteQuery(TDStatement.java:303)
at com.teradata.jdbc.jdbc_4.TDStatement.executeQuery(TDStatement.java:1067)
at ...
Sometimes it is not possible to modify applications that do not properly close ResultSet objects and Statement objects; for example, if the application is a third-party application and the source code is not available.
If the application does not use LOBs, and the application does not use Scrollable Result Sets, and the application does not use Updatable ResultSets, and the application requires more than 16 open responses per connection, then the connection parameter LOB_SUPPORT=OFF can be used as a workaround. For more information about the LOB_SUPPORT connection parameter, see Making a Database Connection.
The database transmits ResultSet LOB columns to the Teradata JDBC Driver as LOB locators. The database requires use of the KeepResponse mode in conjunction with LOB locators.
When KeepResponse mode is not used, the database automatically closes a response when the response:
When connection parameter LOB_SUPPORT=OFF is specified, the Teradata JDBC Driver does not use the KeepResponse mode, which means that LOBs cannot be used. This also means that the database automatically closes responses in the two situations listed above, helping an application to avoid reaching the database limit of 16 open responses per connection.
When connection parameter LOB_SUPPORT=OFF is specified, Scrollable Result Sets and Updatable Result Sets cannot be used. Requesting a Scrollable Result Set and/or Updatable Result Set throws an exception due to a database error:
[Teradata Database] : Parcel kind or ordering is invalid.
When connection parameter LOB_SUPPORT=OFF is specified, the Teradata JDBC Driver does not use the KeepResponse mode; and therefore, the database's Merge Prefetch feature may ignore the value specified with the Statement.setFetchSize method and/or the ResultSet.setFetchSize method.
Beginning with Teradata JDBC Driver 16.20.00.11, the classpath must list terajdbc4.jar.
With older versions of the Teradata JDBC Driver, the classpath must list terajdbc4.jar and tdgssconfig.jar.
If you receive a ClassNotFoundException for com.teradata.jdbc.TeraDriver, then the problem may be due to the classpath not being set, or the classpath being set incorrectly, such that terajdbc4.jar cannot be found. The terajdbc4.jar file must be listed on the classpath.
Beginning with Teradata JDBC Driver 16.20.00.11, tdgssconfig.jar is no longer used and should not be listed on the classpath.
With older versions of the Teradata JDBC Driver, tdgssconfig.jar must be listed on the classpath.
If you receive a "UserFile parameter null" error, then the problem might be due to the classpath not being set, or the classpath being set incorrectly such that tdgssconfig.jar cannot be found.
If you receive one of the following exceptions:
then the problem may be due to the classpath not being set, or the classpath being set incorrectly, such that tdgssconfig.jar cannot be found.
There are many places where the classpath can be set, including, but not limited to:
Check the following entries in DNS or the /etc/hosts file:
Remember that the format of a COP name is dbcnameCOPn, where dbcname must begin with an alphabetic character.
If the performance of the application seems very slow, here are some recommendations for improvement:
One example would be an INSERT statement that is submitted many times, but with different inserted data values each time. Another example would be a SELECT statement that is submitted many times, but with different comparison values in WHERE-clause conditions each time.
If data values are specified as literals in the SQL statement, and the SQL statement is changed with different literal data values upon each submission, then the database must parse the SQL statement each time before executing it.
For situations like these, a PreparedStatement should be used instead. The SQL statement must have a ? placeholder for each data value that will be changed per submission.
The application must prepare the SQL statement once, using the Connection.prepareStatement method. For each submission, the application must bind all the data values using the PreparedStatement.setXXX methods, and then the application must execute the PreparedStatement.
The application can repeat the bind and execute steps over and over, with different bound data values each time. This technique provides a substantial performance improvement, because the database only needs to parse the SQL statement once, and can re-execute the parsed statement over and over.
When the setBinaryStream, setAsciiStream, and setCharacterStream methods are used, the Teradata JDBC Driver sends LOB data to the database separately from other bound parameter values, so that LOB values do not count towards the database limit on total bytes of bound parameter values per inserted row.
To improve the performance of a PreparedStatement INSERT, that is inserting one or more small ( <= 64000 bytes) LOB values per row, the setString method is used to bind a value to a CLOB column, and the setBytes method is used to bind a value to a BLOB column. The SQL INSERT statement must cast the ? parameter marker to a CLOB or BLOB, respectively.
INSERT INTO MyTable(id,clob_col) VALUES(?,CAST(? AS CLOB))
prepStmt.setInt(1,id);
prepStmt.setString(2,"abc");
Using the setBytes method with a CAST expression forces the Teradata JDBC Driver to send the bound parameter value as a VARBYTE value, so it is limited to 64000 bytes, even though the destination column may be a BLOB that can hold values larger than 64000 bytes.
Using the setString method with a CAST expression forces the Teradata JDBC Driver to send the bound parameter value as a VARCHAR value, so it limited to 64000 bytes, even though the destination column may be a CLOB that can hold values larger than 64000 bytes. If a Unicode session character set (UTF8 or UTF16) is used, and/or if the destination column is designated CHARACTER SET UNICODE, then the database will convert the bound parameter value into two-byte Unicode characters. The value after conversion is limited to 64000 bytes.
This technique works only if the total size of all the bound parameter values does not exceed the database limit on total bytes for all the bound parameter values for an inserted row. This technique should only be used when performance is critical, and it is known in advance that the total size of all the bound parameter values, including LOB values, does not exceed 64000 bytes per inserted row.
This technique is subject to a further limitation such that the total size of all the bound parameter values must not exceed the database limit on total bytes for all the bound parameter values for an inserted row, after any necessary character set conversions have been performed by the database. If a Unicode session character set (UTF8 or UTF16) is used, and/or if a destination character column is designated CHARACTER SET UNICODE, then the database will convert all the bound parameter values that are character data types (CHAR, VARCHAR, CLOB) into two-byte Unicode characters. These two-byte Unicode characters are counted towards the database limit on total bytes for all the bound parameter values for an inserted row.
Beginning with Teradata Database 12.0, when the application requests the ResultSet type to be ResultSet.TYPE_SCROLL_INSENSITIVE, the Teradata JDBC Driver is able to quickly and efficiently skip to the next result of a multi-statement request by using cursor positioning to position to the last row of the current result set. If forward-only result sets are used, the same skipping operation will require the JDBC driver to fetch all rows of the current result set first, which can take significantly longer.
The following methods will create statements that return TYPE_SCROLL_INSENSITIVE result sets:
Connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
Connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
Connection.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)
This avoids the time-consuming COP discovery process. The Teradata JDBC Driver will attempt to connect to the first IP address returned by the DNS lookup, and will use subsequent IP addresses in case of a connection failure. Use DNS round-robin to distribute Teradata JDBC Driver connections across all available nodes.
A Sun Java HotSpot 64-Bit Server Virtual Machine Error (HotSpot Virtual Machine Error ID: 4E4154495645294E53543F494116140E435050006F) has been observed on Linux Red Hat Advanced Server 2.1/3.0 Itanium platform for some client applications using Teradata JDBC Driver 3.2.
The JVM error has been observed with the following system configuration:
This HotSpot JVM error on Linux RedHat Advanced Server 2.1/3.0 Itanium platform has been reported to the Sun Microsystems bug report system (Incident Review ID: 311761).
Error Message: [Teradata Database] : The UserId, Password or Account is invalid.
Error Code: 8017
This exception typically occurs due to invalid user, password, or account information; however, this exception can also occur in the following situations.
Cause |
The user does not have the "logon with null password" permission when using the Browser, JWT, Kerberos, or LDAP logon mechanisms. |
Solution |
Grant the permission to the user with the "grant logon with null password" command. |
Cause |
LOGMECH=LDAP is specified, and the username/password credentials are redundantly provided in the both LOGDATA and as separate connection parameters. |
Solution |
Identify LDAP users via LOGDATA, or via separate username/password parameters, but not both simultaneously. |
Cause |
One of the EBCDIC-variant session character sets is specified, with the database in Japanese Language Support mode, and the UserID, Password, or Account is in the Kanji character set. |
Solution |
Specify a non-EDCDIC-variant session character set when the UserID, Password, or Account is in the Kanji character set. |
If you have trouble getting Kerberos to work properly, check the following tables for messages and solutions to common errors.
|
Error Message |
GSS Exception: No valid credentials provided (Mechanism level: Failed to find any Kerberos Ticket) |
|||
|
Cause |
kinit was never run. |
|||
|
Solution |
Run the kinit program that resides in the jre/bin directory of your Java JDK. |
|||
|
Error Message |
java.lang.SecurityException: Unable to locate a login configuration |
|||
|
Cause |
Failed to specify a configuration file. |
|||
|
Solution |
Specify the configuration file by using the JVM option -Djava.security.auth.login.config or by modifying the appropriate java.security file. The steps to do this are outlined in Meeting Kerberos Prerequisites. |
|||
|
Error Message |
javax.security.auth.login.Login Exception: No Login Modules configured for com.sun.security.jgss.initiate |
|||
|
Cause |
The following information is missing or misspelled in you configuration information: com.sun.security.jgss.initiate { com.sun.security.auth.module.Krb5LoginModule sufficient useTicketCache=true; }; |
|||
|
Solution |
Ensure that the above information is present in your Login Configuration Information. See Meeting Kerberos Prerequisites for information on setting Login Configuration. |
|||
|
Error Message |
javax.security.auth.login.LoginException: Pre-authentication information was invalid |
|||
|
Cause |
One of:
|
|||
|
Solution |
Validate the configuration file in c:/winnt Validate Username and Password used. Note that the username must be specified exactly as it appears in Windows Active Directory Users. |
|||
|
Error Message |
[Teradata Database]: Invalid password |
|||
|
Cause |
This can occur when using Kerberos Single Sign-On when
|
|||
|
Solution |
Ensure that the same password is being used for both system logon and for the database user password. This can be done by logging on without Kerberos with the username and password. If that isn't the cause, then the database user needs to be configured for SSO via the "grant logon with null password" command by a database administrator. |
|||
|
Error Message |
GSSException: No valid credentials provided (Mechanism level: Failed to find any Kerberos Ticket) |
|||
|
Cause |
Did not specify -Djavax.security.auth.useSubjectCredsOnly=false |
|||
|
Solution |
Add the -Djavax.security.auth.useSubjectCredsOnly=false to the script that runs your application. |
|||
|
Error Message |
KrbException: Invalid option setting in ticket request. (101) (Mechanism level: Failed to find any Kerberos Ticket) |
|||
|
Cause |
kinit was not run using the "-f" or forwardable option. |
|||
|
Solution |
Run the kinit program that resides in the jre/bin directory of the Java JDK with the forwardable option set. For example, kinit -f |
|||
|
Error Message |
GSSException: Invalid name provided (Mechanism level: Could not load configuration file C:\Documents and Settings\Administrator\WINDOWS\krb5.ini (The system can not find the file specified)) |
|||
|
Cause |
When using Microsoft Windows and Terminal Services is enabled, the locations where the krb5.ini file is seached can change. |
|||
|
Solution |
If you wish to keep the krb5.ini file in c:\winnt, add the following to the command line that runs your Java program |
|||
|
Error Message |
GSSException: Defective token detected (Mechanism level: AP_REP token id does not match!) |
|||
|
Cause |
This can be due to computer clock synchronization problems. The default value for allowable clock skew is normally 5 minutes. |
|||
|
Solution |
Check the system time on the involved systems. Synchronize the time on those systems. |
|||
|
Error Message |
The security of this connection may be compromised because a missing token was discovered during decryption |
|||
|
Cause |
This error may occur on a Linux client if the following lines are missing from the [libdefaults] entry of the krb5.conf file.
default_tkt_enctypes = arcfour-hmac rc4-hmac |
|||
|
Solution |
Add default_tkt_enctypes and default_tgs_enctypes as shown above to the krb5.conf file under the [libdefaults] entry. |
|||
LDAP does not support Teradata JDBC Driver logon from z/OS.
An attempt to log on using the LDAP mechanism results in an SQLException with the following error, even when valid information is specified for the LOGDATA parameter (set using URL or DataSource):
SQLState: 28000 Error code: 8017
Message: [Teradata Database]: The UserId, Password or Account is invalid.
If an SQLException is encountered during JDBC FastLoad, it may be part of a chain of SQL exceptions. To get the complete picture of the cause for the SQLException, it is necessary to walk through the whole chain of SQL exceptions.
Likewise, if an SQLWarning is encountered during JDBC FastLoad, it may be part of a chain of SQL warnings. To get a complete picture of the cause for the SQLWarning, it is necessary to walk through the whole chain of SQL warnings.
For example:
try {
PreparedStatement pstmt = con.prepareStatement("INSERT INTO ...");
try {
SQLWarning w = con.getWarnings();
while (w != null) {
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState() +
", Error Code = " + w.getErrorCode() +
"\n" + sw.toString());
w = w.getNextWarning();
}
// using JDBC FastLoad
w = pstmt.getWarnings();
} finally {
pstmt.close();
}
} catch (SQLException e) {
while (e != null) {
StringWriter sw = new StringWriter();
e.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + e.getSQLState() +
", Error Code = " + e.getErrorCode() +
"\n" + sw.toString());
e = e.getNextException();
}
}
When using JDBC FastLoad, details on data errors may be included in the chain of SQL exceptions mentioned above. Some data error details may be quite lengthy. They are in one of two temporary error tables mentioned in Considerations When Using JDBC FastLoad. For more details on the format of the two temporary error tables, see the section on Error Table Formats in the Teradata FastLoad Reference.
Information on why JDBC FastLoad was not activated can be found in the SQLWarning of a connection, which might be part of a chain of SQL warnings. To get the complete picture of the cause for the SQLWarning, it is necessary to scroll through the whole chain of SQL warnings.
Information on why JDBC FastLoad was not activated also can be obtained by specifying LOG=INFO in the URL connection string. Search for "FastLoad " (note the space after FastLoad) in the resulting LOG output. The same search can be used to find out if JDBC FastLoad was activated.
Here is a sample LOG output that shows JDBC FastLoad was not activated:
Cannot FastLoad because statement is NOT an INSERT!
Here is a sample LOG output that shows JDBC FastLoad was activated:
FastLoad found 2 AMP(s) in anmpc2 and created 2 FastLoadConnection(s) and 2 FastLoadPreparedStatement(s) with SESSIONS=8.
If an SQLException is encountered during JDBC FastExport, it might be part of a chain of SQL exceptions. To get the complete picture of the cause for the SQLException, it is necessary to walk through the whole chain of SQL exceptions. Likewise, if an SQLWarning is encountered during JDBC FastExport, it might be part of a chain of SQL warnings. To get the complete picture of the cause for the SQLWarning, it is necessary to walk through the whole chain of SQL warnings.
For example:
try {
PreparedStatement pstmt = con.prepareStatement("SELECT ... FROM ...");
try {
// using JDBC FastExport
SQLWarning w = pstmt.getWarnings();
while (w != null) {
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState() +
", Error Code = " + w.getErrorCode() +
"\n" + sw.toString());
w = w.getNextWarning();
}
} finally {
pstmt.close();
}
} catch (SQLException e) {
while (e != null) {
StringWriter sw = new StringWriter();
e.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + e.getSQLState() +
", Error Code = " + e.getErrorCode() +
"\n" + sw.toString());
e = e.getNextException();
}
}
Information on why JDBC FastExport was not activated can be found in an SQLWarning of a Connection, which might contain a chain of SQL warnings. To get the complete picture of the cause for the SQLWarning, it is necessary to walk through the whole chain of SQL warnings.
Information on why JDBC FastExport was not activated can also be obtained by specifying LOG=INFO in the URL connection string.
Cannot FastExport because statement is not a SELECT!
FastExport found 2 AMP(s) in anmpc2 and created 2 FastExportConnection(s) and 2 FastExportPreparedStatement(s) with SESSIONS=8.
If an SQLException is encountered by the JDBC Monitor, it may be part of a chain of SQL exceptions. To obtain the complete picture of the cause for the SQLException, it is necessary to step through the entire chain of SQL exceptions.
Likewise, if an SQLWarning is encountered by the JDBC Monitor, it may be part of a chain of SQL warnings. To obtain the complete picture of the cause for the SQL Warning, it is necessary to step through the entire chain of SQL warnings.
Here is an example:
try {
PreparedStatement pstmt = con.prepareStatement("MONITOR VERSION");
try {
//bind input values (not shown)
boolean resultSetAvailable = pstmt.execute();
//get ResultSet (not shown)
SQLWarning w = pstmt.getWarnings();
while (w != null)
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw,true));
System.out.printIn("SQL State = " + w.getSQLState() +
", Error Code = " + w.getErrorCode() +
"\n" + sw.toString());
w = w.getNextWarning();
}
} finally {
pstmt.close();
}
} catch (SQLException e) {
while (e != null) {
StringWriter sw = new StringWriter();
e.printStackTrace(new PrintWriter(sw, true));
System.out.printIn("SQL State = " + e.getSQLState() +
", Error Code = " + e.getErrorCode() +
"\n" + sw.toString());
e = e.getNextException();
}
}
DatabaseMetaData methods compose queries against the Data Dictionary views. Slow performance of queries against Data Dictionary views can sometimes be due to a lack of accurate, up-to-date statistics.
It is recommended that the database administrator execute the following SQL commands on a regular basis to collect statistics on certain Data Dictionary columns and indexes.
collect statistics on DBC.AccessRights column (DatabaseId)
collect statistics on DBC.AccessRights column (FieldId)
collect statistics on DBC.AccessRights column (GrantorId)
collect statistics on DBC.AccessRights column (Partition)
collect statistics on DBC.AccessRights column (Partition, UserId)
collect statistics on DBC.AccessRights column (UserId)
collect statistics on DBC.AccessRights column (UserId, DatabaseId)
collect statistics on DBC.AccessRights column (UserId, TVMId)
collect statistics on DBC.AccessRights column (TVMId)
collect statistics on DBC.AccessRights column (TVMId, DatabaseId)
collect statistics on DBC.AccessRights index (UserId, DatabaseId) -- for TD 14.10 and earlier
collect statistics on DBC.AccessRights index (UserId) -- for TD 15.0 and later
collect statistics on DBC.AccessRights index (TVMId) -- for TD 14.10 and earlier
collect statistics on DBC.DatasetSchemaInfo column (DatasetSchemaId) -- for TD 16.0 and later
collect statistics on DBC.DBase column (DatabaseId)
collect statistics on DBC.DBase column (DatabaseId, DatabaseName)
collect statistics on DBC.DBase column (DatabaseId, ZoneId) -- for TD 15.0 and later
collect statistics on DBC.DBase column (DatabaseName)
collect statistics on DBC.DBase column (DatabaseName, DatabaseId)
collect statistics on DBC.DBase column (DatabaseNameI)
collect statistics on DBC.DBase column (JournalId)
collect statistics on DBC.DBase index (DatabaseId)
collect statistics on DBC.Indexes column (CreateUID)
collect statistics on DBC.Indexes column (FieldId)
collect statistics on DBC.Indexes column (IndexNumber)
collect statistics on DBC.Indexes column (IndexType)
collect statistics on DBC.Indexes column (LastAlterUID)
collect statistics on DBC.Indexes column (TableId, DatabaseId)
collect statistics on DBC.Indexes column (TableId, FieldId)
collect statistics on DBC.Indexes column (TableId, IndexNumber, DatabaseId)
collect statistics on DBC.Indexes column (UniqueFlag)
collect statistics on DBC.Indexes column (UniqueFlag, CreateUID)
collect statistics on DBC.Indexes column (UniqueFlag, FieldId)
collect statistics on DBC.Indexes column (UniqueFlag, LastAlterUID)
collect statistics on DBC.Indexes index (TableId)
collect statistics on DBC.ObjectUsage column (DatabaseId) -- for TD 14.0 and later
collect statistics on DBC.ObjectUsage column (DatabaseId, ObjectId) -- for TD 14.0 and later
collect statistics on DBC.ObjectUsage column (DatabaseId, ObjectId, IndexNumber) -- for TD 14.0 and later
collect statistics on DBC.ObjectUsage column (FieldId) -- for TD 14.0 and later
collect statistics on DBC.ObjectUsage column (IndexNumber) -- for TD 14.0 and later
collect statistics on DBC.ObjectUsage column (ObjectId) -- for TD 14.0 and later
collect statistics on DBC.ObjectUsage column (ObjectId, FieldId) -- for TD 14.0 and later
collect statistics on DBC.ObjectUsage column (UsageType) -- for TD 14.0 and later
collect statistics on DBC.Owners column (OwneeId)
collect statistics on DBC.Owners column (OwnerId, OwneeId)
collect statistics on DBC.Owners index (OwnerId)
collect statistics on DBC.RoleGrants column (RoleId)
collect statistics on DBC.RoleGrants index (GranteeId)
collect summary statistics on DBC.RoleGrants -- for TD 14.0 and later
collect statistics on DBC.Roles column (RoleId)
collect statistics on DBC.Roles column (RoleNameI)
collect statistics on DBC.StatsTbl column (IndexNumber) -- for TD 13.0 and later
collect statistics on DBC.StatsTbl column (StatsType) -- for TD 13.0 and later
collect statistics on DBC.TempTables column (BaseTableId)
collect summary statistics on DBC.TempTables -- for TD 14.0 and later
collect statistics on DBC.TVFields column (CreateUID)
collect statistics on DBC.TVFields column (DatabaseId)
collect statistics on DBC.TVFields column (DatasetSchemaId) -- for TD 16.0 and later
collect statistics on DBC.TVFields column (FieldId)
collect statistics on DBC.TVFields column (FieldName)
collect statistics on DBC.TVFields column (FieldName, TableId)
collect statistics on DBC.TVFields column (FieldType)
collect statistics on DBC.TVFields column (LastAlterUID)
collect statistics on DBC.TVFields column (TableId)
collect statistics on DBC.TVFields column (TableId, DatabaseId)
collect statistics on DBC.TVFields column (TableId, FieldId)
collect statistics on DBC.TVFields column (TableId, FieldId, DatabaseId)
collect statistics on DBC.TVFields column (TableId, FieldName)
collect statistics on DBC.TVFields column (UDTypeId) -- for V2R6.1 and later
collect statistics on DBC.TVFields column (UDTName) -- for V2R6.1 and later
collect statistics on DBC.TVM column (CommitOpt) -- for TD 12.0 and later
collect statistics on DBC.TVM column (CreateUID)
collect statistics on DBC.TVM column (CreatorName)
collect statistics on DBC.TVM column (DatabaseId)
collect statistics on DBC.TVM column (DatabaseId, TVMId)
collect statistics on DBC.TVM column (LastAlterUID)
collect statistics on DBC.TVM column (TableKind)
collect statistics column (TableKind (char(1), character set latin, casespecific)) as ST_TVM_TK0 on DBC.TVM -- for TD 15.0 and later
collect statistics column (TableKind (char(1), character set latin, not casespecific)) as ST_TVM_TK1 on DBC.TVM -- for TD 15.0 and later
collect statistics on DBC.TVM column (TVMId)
collect statistics on DBC.TVM column (TVMId, TVMName)
collect statistics on DBC.TVM column (TVMName)
collect statistics on DBC.TVM column (TVMNameI)
collect statistics on DBC.TVM column (DatabaseId, TVMName)
collect statistics on DBC.TVM index (DatabaseId, TVMNameI)
collect statistics on DBC.UDFInfo column (DatabaseId)
collect statistics on DBC.UDFInfo column (DatabaseId, FunctionName)
collect statistics on DBC.UDFInfo column (FunctionId)
collect statistics on DBC.UDFInfo column (FunctionName)
collect statistics on DBC.UDFInfo column (FunctionType)
collect statistics using MaxValueLength 1 column (FunctionType, FunctionName) on DBC.UDFInfo -- for TD 14.0 and later
collect statistics using MaxValueLength 5 column (FunctionType, FunctionName) on DBC.UDFInfo -- for TD 14.0 and later
collect statistics on DBC.UDTInfo column (TypeId) -- for V2R6.1 and later
collect statistics on DBC.UDTInfo column (TypeKind) -- for V2R6.1 and later
collect statistics on DBC.UDTInfo column (TypeName) -- for V2R6.1 and later
Note: Upgrade to Teradata JDBC Driver 15.00.00.13 or later in order to avoid the Slow Logon on Linux problem.
Java applications using the Teradata JDBC Driver might experience a slow logon process when running on a Linux system, varying from several additional seconds to several minutes. This occurs when using the TD2 mechanism, which is the default mechanism for a JDBC connection to the database. The cause is underlying problems with random number generation, and is documented by the following Java bugs:
http://bugs.java.com/bugdatabase/view_bug.do?bug_id=6202721 SHA1PRNG reads from /dev/random even if /dev/urandom selected
http://bugs.java.com/bugdatabase/view_bug.do?bug_id=6366924 securerandom.source and/or java.security.egd dont work in 1.5.0_05
Note that the presence of these problems does not guarantee that every logon is slow. Test programs that recreate the problem can run successfully for many iterations before the problem appears. Run the following Java program if you suspect this problem.
If there are no problems, the program should finish in a few seconds. If the slow logon problem is present, then it may take several minutes for the program to run. The program prints iteration count for each test run and thus slow progress can be determined by pauses in the program’s output.
// This program tests to see if there are delay problems associated the
// use of SecureRandom
import java.security.SecureRandom;
import java.util.*;
import java.math.*;
class secRandomPrb {
public static void main(String args[]) throws Exception {
System.out.println();
System.out.println("Pass 1: getInstance:");
runit(true);
System.out.println();
System.out.println("Pass 2: secureRandom:");
runit(false);
}
public static void runit(boolean secRand) {
SecureRandom srand;
BigInteger x;
int ctr = 0;
try {
for (ctr = 0; ctr < 1000; ctr++) {
if (secRand == true)
srand = SecureRandom.getInstance("SHA1PRNG");
else
srand = new SecureRandom();
byte[] seed = srand.generateSeed(8);
srand.setSeed(seed);
x = new BigInteger(512, srand);
int i = x.intValue();
System.out.print(ctr + " ");
}
}
catch (Exception ex) {
System.out.println("Exception: " + ex);
}
}
}
The best way to resolve the Slow Logon on Linux problem is to upgrade to Teradata JDBC Driver 15.00.00.13 or later.
When using an older version of the Teradata JDBC Driver, the Slow Logon on Linux problem can be avoided by specifying /dev/./urandom as a link for /dev/random or as the securerandom.source.
This can be done by Using a Command Line Argument or Changing /dev/random to be a symbolic link.
The recommended workaround is to specify a command line argument for starting Java JVM. This allows the change to be made only to Java programs that may encounter this problem. The command line setting of:
-Djava.security.egd=file:/dev/./urandom
will avoid the problem. Note the extra "." directory between dev and urandom. This is required. The above test program can be run with this setting as follows:
java -Djava.security.egd=file:/dev/./urandom secRandomPrb
Note that this same property is available in the security file in $JAVA_HOME/jre/lib/security/java.security file. Though normally, a change to the java.security file should provide the same effect, our testing has shown that changes to that file will not resolve the problem when using JDK 5.0.
This change requires root permission and will change the setting of /dev/random for ALL programs on your system. You can log in as root and execute the following commands:
mv /dev/random /dev/random.real
ln -s /dev/./urandom /dev/random
After this change is made, the slow logon problem will be avoided. Verify this by running the test program provided above.