Numeric Data Truncation

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

Character Export Width

Retrieving fixed character fields (for example, C01 CHAR(10)) utilizing the UTF8 session character set might result in padded strings. This is due to the database export factor utilized when translating characters to the session character set.

The recommended workaround is to cast the char field to a varchar.

For example:

Original SQL

SELECT col1, col2 FROM myTable

Using cast

SELECT CAST(col1 AS VARCHAR(10)), CAST (col2 AS VARCHAR(10)) FROM myTable

Transaction Isolation, Concurrency, and Deadlock

Create and Drop

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:

If this error occurs, the application can choose to wait a short time and then resubmit the failed create or drop operation.

JDBC FastLoad

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:

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.

Transaction Isolation

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

Use a transaction isolation level of TRANSACTION_READ_UNCOMMITTED on the connection reading from the table.

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.

Large Object Interface

Description

The LOB data type categories for the Teradata Database include BLOB (binary data) and CLOB (character data). The Teradata JDBC Driver supports both data types for the Type 4 driver.

Number of LOB Columns

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 Size Limits

LOB values up to 2 GB in size are supported. For a result set containing LOB columns, the Teradata Database transmits LOB locators to the Teradata JDBC Driver, so only
the LOB locators (not the LOB values) are counted towards the Teradata 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 Teradata Database and
the Teradata JDBC Driver, this process avoids transmitting potentially large LOB values back and forth over the network.

Response Limit Exceeded Error

The most likely cause of the following error from the Teradata Database is that the application is not properly closing ResultSet objects and Statement objects.

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 Teradata Database of a maximum of 16 open responses per connection.

For a single-statement SQL request, the response remains open until either the ResultSet object or the Statement object is closed.

For a multi-statement SQL request, the response remains open until either all the ResultSet objects, or the Statement object is closed.

For an InputStream obtained from Blob.getBinaryStream or Clob.getAsciiStream, or for a Reader obtained from Clob.getCharacterStream, the response remains open until the InputStream or Reader object is closed.

After a Blob or Clob is modified using one of the methods to update the LOB, the response remains open until the Blob.free or Clob.free method is called.
The Blob.free and Clob.free methods are defined beginning with JDK 6.0. An application must use reflection to call Blob.free or Clob.free with JDK 1.4 or 5.0.

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 Teradata 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 Teradata 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 Teradata Database Connection.

The Teradata Database transmits ResultSet LOB columns to the Teradata JDBC Driver as LOB locators. The Teradata Database requires use of the KeepResponse mode
in conjunction with LOB locators.

When KeepResponse mode is not used, the Teradata Database automatically closes a response when the response:

Is small enough to fit in a single message from the Teradata Database to the Teradata JDBC Driver; or

Spans multiple messages, and the application has read through the response up to the beginning of the last response message

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 Teradata Database automatically closes responses in the two situations listed above, helping an application to avoid reaching the
Teradata 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 Teradata 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
Teradata Database's Merge Prefetch feature may ignore the value specified with the Statement.setFetchSize method and/or the ResultSet.setFetchSize method.

Checking the Environment Parameters

CLASSPATH

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.

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. The tdgssconfig.jar file must be listed on the classpath.

If you receive one of the following exceptions:

NullPointerException at com.teradata.tdgss.jtdgss.TdgssConfigApi.GetMechanisms

IllegalArgumentException “InputStream cannot be null” at javax.xml.parsers.DocumentBuilder.parse, at com.teradata.tdgss.jtdgss.TdgssParseXml.parse

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:

The CLASSPATH environment variable set in a login script, a profile, the system profile, a shell script, or a batch file.

On the Java command line, using the -classpath option, as typed by the user.

On the Java command line, using the -classpath option, as specified in a shell script or batch file.

In an application server’s classpath configuration for a DataSource definition.

No matter where or how you set the classpath, the classpath used for the Teradata JDBC Driver must include:

Use PreparedStatement where possible. This applies whenever the same SQL statement is submitted many times, but data values differ for each submission.

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 Teradata 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 Teradata Database only needs to parse the SQL statement once, and can re-execute the parsed statement over and over.

Inserting Small LOB Values. The recommended technique for inserting LOB values is to use a PreparedStatement INSERT with ? parameter markers for all column values to be inserted. Use the setBinaryStream method for binding BLOB values to the parameter markers corresponding to BLOB columns, then use the setAsciiStream or setCharacterStream method for binding CLOB values to the parameter markers corresponding to CLOB columns.

When the setBinaryStream, setAsciiStream, and setCharacterStream methods are used, the Teradata JDBC Driver sends LOB data to the Teradata Database separately from other bound parameter values, so that LOB values do not count towards the Teradata Database limit of 64000 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 Teradata 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 Teradata Database limit of 64000 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 Teradata Database limit of 64000 bytes for all the bound parameter values for an inserted row, after any necessary character set conversions have been performed by the Teradata 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 Teradata 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 Teradata Database limit of 64000 bytes for all the bound parameter values for an inserted row.

Use executeBatch() where possible. Whenever there are many insert, update, or delete statements that can be submitted together, use the executeBatch() method rather that executeUpdate() or execute(). However, the total buffer length is limited to approximately 1 MB. Using executeBatch() instead of executeUpdate() can improve your performance by more than 50%.

Use connection pooling provided by an application server. Connection pooling is a technique used for sharing server resources among requesting clients. It allows multiple clients to share a cached set of connection objects that provide access to the database. It improves performance by eliminating the overhead associated with establishing a new database connection for each request. However, there are some restrictions. Since it is not currently possible to reset a database connection, users of connection pooling must not change the following session parameters because these changes will be inherited by the next user of the connection:

Database

Collation

Character Set

Transaction Semantics

Dateform

Timezone

Default Date Format

Use multi-threading. Where possible, use multi-threading with multiple sessions for those requests that can be processed at the same time. It is important to remember, however, not to have multiple concurrent requests on a single session. Teradata does not support this and even though the driver will accept this, it blocks until the current request is complete. This may actually degrade performance. For improved performance, use concurrent sessions with each session running only one request at a time.

Use a Transaction isolation level of TRANSACTION_READ_UNCOMMITTED. This feature can speed up access to data though it comes at the cost of encountering dirty reads, non-repeatable reads, and phantom reads. Whether or not this is suitable should be determined on an individual application basis.

Use TYPE_SCROLL_INSENSITIVE result sets. These can improve performance when used with queries which can return large multiple result sets that do not require all rows to be processed.

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:

Use a single Teradata Database hostname in DNS. Improve connection time with the following steps.

Define a single DNS name with multiple IP addresses for the Teradata Database, such that each IP address corresponds to a Teradata Database node running a DBS Gateway.
Omit the IP addresses of nodes that don't normally run a DBS Gateway, such as Hot Standby Nodes in most configurations. Enable DNS round-robin for the IP addresses, so that connections are distributed across all the nodes.

Specify COP=OFF for Teradata JDBC Driver connections

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.

One of the EBCDIC-variant session character sets is specified, with Teradata 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.

Troubleshooting Kerberos

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.

Validate the configuration file in c:/winnt Validate Username and Password used. Note that the username must be types in “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

the Teradata Database user was not defined with the same password as their System logon password

the Teradata Database user was not configured to use SSO

Solution

Ensure that the same password is being used for both system logon and for the Teradata 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 Teradata Database user needs to be configured for SSO via the “grant logon with null password” command by a Teradata 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 -Djava.security.krb5.conf=C:/WINNT/krb5.iniOtherwise set -Djava.security.krb5.conf to whatever location holds the krb5.ini file

Troubleshooting JDBC FastLoad

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.

Troubleshooting JDBC FastExport

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.

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.

The following sample LOG output shows that JDBC FastExport was not activated:

Cannot FastExport because statement is not a SELECT!

The following sample LOG output shows that JDBC FastExport was activated:

FastExport found 2 AMP(s) in anmpc2 and created 2 FastExportConnection(s) and 2 FastExportPreparedStatement(s) with SESSIONS=8.

Troubleshooting JDBC Monitor

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 Performance

DatabaseMetaData methods compose queries against the Teradata Database 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 Teradata 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.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.Owners column (OwneeId)

collect statistics on DBC.Owners column (OwnerId, OwneeId)

collect statistics on DBC.Owners index (OwnerId)

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 (FieldId)

collect statistics on DBC.TVFields column (FieldName)

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, FieldName)

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 (LastAlterUID)

collect statistics on DBC.TVM column (TableKind)

collect statistics on DBC.TVM column (TVMId)

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.UDTInfo column (TypeKind) -- for V2R6.1 and later

collect statistics on DBC.UDTInfo column (TypeName) -- for V2R6.1 and later

Slow Logon on Linux

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 Teradata Database.
The cause is underlying problems with random number generation, and is documented by the following Java bugs:

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.

Using a Command Line Argument

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.

Changing /dev/random to be a symbolic link

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.