Using the Teradata JDBC Driver

This chapter describes using the Teradata JDBC Driver software in Java programs, and guides you through the process of getting the Teradata JDBC Driver running on site:

Importing the SQL Package and Loading the Teradata JDBC Driver

The JDBC application must have the SQL Interface package imported and the Teradata JDBC Driver loaded.

Note:  The information in this section applies primarily to standalone Java applications. This information does not apply to a J2EE application deployed to an application server environment such as WebSphere or WebLogic. Application servers provide their own mechanisms for defining the classpath, and application servers are responsible for loading the JDBC driver classes.

To import the SQL Interface package and load the Teradata JDBC Driver

  1. Verify that the following Teradata files are on the Java development platform:
  2. Check CLASSPATH to see if it includes the following Java archive files:
  3. Place the following line near the top of the program:

    import java.sql.*;

  4. Use the following statement to load and register the Teradata JDBC Driver:

    Class.forName("com.teradata.jdbc.TeraDriver");

Making a Teradata Database Connection

JDBC Type 3 Driver to Type 4 Driver Migration

The Type 3 Teradata JDBC Driver is no longer included on the Installation CDs and tapes for Teradata Tools and Utilities. Related software, such as the JDBC Gateway and its administration GUI, is also no longer included.

To run applications previously written for the Type 3 driver it is necessary to move applications to the Type 4 Teradata JDBC Driver.

To do this, change the application's connection Universal Resource Locator (URL) as follows:

       JDBC Type 3:   jdbc:teradata://gwhost:port/DatabaseServerName

       JDBC Type 4:   jdbc:teradata://DatabaseServerName

Applets

The applet security model only permits applets to connect to the system from which the applet was downloaded. The following three-tier application architecture is no longer supported:

This release of the Teradata JDBC Driver supports the following application architecture.

The Type 4 Teradata JDBC Driver Connection URL

To access a Teradata Database from a Java program, use the java.sql.DriverManager.getConnection method to obtain a new java.sql.Connection object from the DriverManager.

The java.sql.DriverManager.getConnection method takes a URL string as an argument. The URL string identifies a Teradata Database, and the DriverManager uses the URL prefix "jdbc:teradata://" to select the Teradata JDBC Driver for the Connection.

Description

Example Teradata JDBC Driver Connection URL

No connection parameters

jdbc:teradata://DatabaseServerName

 

DatabaseServerName is the hostname of the Teradata Database.

One connection parameter

jdbc:teradata://DatabaseServerName/ParameterName=Value

 

Connection parameters are optional. The first ParameterName is separated from the DatabaseServerName by a forward slash character.

Two or more connection parameters

jdbc:teradata://DatabaseServerName/ParameterName=Value,ParameterName=Value

 

When two or more connection parameters are specified, the parameters must be separated by commas. Enclose the value in single-quotes if the value contains a comma.

 

ParameterName is a connection parameter, and Value is a value for the parameter, as described in the next table.

The following table lists the Teradata JDBC Driver connection URL parameters and values. Beginning with Teradata JDBC Driver 16.00.00.28, the Teradata JDBC Driver validates URL connection parameters and throws SQLException for an invalid parameter name and/or invalid value.

Parameter

Description

ACCOUNT

Specifies an account string to override the default account string defined for the Teradata Database user. Accounts are used by the Teradata Database for workload management and resource usage monitoring.

Beginning with Teradata Database 14.10, the maximum length of an account string is 128 characters. The maximum length of an account string is 30 characters for Teradata Database 14.0 and earlier releases.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 15.10.00.08.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

CHARSET

Specifies the session character set for encoding and decoding character data transferred to and from the Teradata Database. The following session character sets are supported:

  • ASCII
  • EBCDIC037_0E
  • EBCDIC273_0E
  • EBCDIC277_0E
  • HANGULEBCDIC933_1II
  • HANGULKSC5601_2R4
  • KANJIEBCDIC5026_0I
  • KANJIEBCDIC5035_0I
  • KANJIEUC_0U
  • KANJISJIS_0S
  • LATIN1_0A
  • LATIN9_0A
  • LATIN1252_0A
  • SCHEBCDIC935_2IJ
  • SCHGB2312_1T0
  • TCHBIG5_1R0
  • TCHEBCDIC937_3IB
  • UTF8
  • UTF16

The default value is ASCII.

Beginning with Teradata Database V2R6.1 and Teradata JDBC Driver 3.3, UTF16 can be used in conjunction with Kerberos or Lightweight Directory Access Protocol (LDAP) authentication.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures, but only ASCII, UTF8, or UTF16 may be specified.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is available for JDBC FastLoad CSV connections, but only ASCII or UTF8 may be specified.

This parameter is available for JDBC Monitor connections, but only certain character sets may be specified, depending on the Monitor version. Refer to JDBC Monitor for details.

This parameter is available for Raw connections.

CHATTER

CHATTER values are ON (the default) or OFF.

  • When set to ON, JDBC FastLoad and JDBC FastLoad CSV transmit data to the Teradata Database in small messages. This setting is intended for low-latency networks.
  • When set to OFF, JDBC FastLoad and JDBC FastLoad CSV transmit data to the Teradata Database in large messages. This setting is intended for high-latency networks.

The CHATTER connection parameter is available with Teradata JDBC Driver versions 15.00.00.16 through 15.10.0.24. Beginning with Teradata JDBC Driver 15.10.0.25, the CHATTER connection parameter is no longer available, and is replaced by the MAX_MESSAGE_BODY connection parameter.

This parameter is not available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is not available for JDBC FastExport connections.

This parameter is available for JDBC FastLoad and JDBC FastLoad CSV connections with Teradata JDBC Driver versions 15.00.00.16 through 15.10.0.24.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

CLIENT_CHARSET

Specifies the Java character set for encoding and decoding character data transferred to and from the Teradata Database. This overrides the Teradata JDBC Driver's normal mapping of Teradata session character sets to Java character sets.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

Note:  Not recommended for normal use. Use at your own risk. Data corruption will occur if the wrong Java character set is specified. Teradata cannot provide any guarantees of data fidelity or quality when this connection parameter is used.

COLUMN_NAME

Controls the behavior of the ResultSetMetaData getColumnName and getColumnLabel methods.

  • COLUMN_NAME=OFF (the default) specifies that the ResultSetMetaData.getColumnName method should return the AS-clause name if available, or the column name if available, or the column title, and specifies that the ResultSetMetaData.getColumnLabel method should return the column title.
  • COLUMN_NAME=ON specifies that, when StatementInfo parcel support is available, the ResultSetMetaData.getColumnName method should return the column name if available, and specifies that the ResultSetMetaData.getColumnLabel method should return the AS-clause name if available, or the column name if available, or the column title. This option has no effect when StatementInfo parcel support is unavailable.

The JDBC escape functions {fn teradata_provide(request_scope_column_name_on)} and {fn teradata_provide(request_scope_column_name_off)} take priority over the COLUMN_NAME connection parameter, for that particular SQL request.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 16.00.00.28.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 16.00.00.28.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

CONNECT_FAILURE_TTL

CONNECT_FAILURE_TTL = time-to-live in seconds

This option enables the Teradata JDBC Driver to remember the time of the last connection failure for each IP address/port combination. Also, the Teradata JDBC Driver skips connection attempts to that IP address/port during subsequent logins for the number of seconds specified by the Connect Failure time-to-live (CONNECT_FAILURE_TTL) value.

Omitting or setting the value to zero disables this feature.

The recommended value for this feature is half the restart time of the corresponding Teradata Database.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

Note:  This feature applies to TCP socket connection failures and does not apply to hostname look up during COP Discovery. There is no need for such a connection parameter (for example, COP_DISCOVERY_TTL) because this can be done using a JVM setting: the Java security property networkaddress.cache.negative.ttl as defined in the InetAddress Caching section of this document.

CONNECT_FUNCTION

Specifies whether the Teradata Database should allocate a Logon Sequence Number (LSN) for this session, or associate this session with an existing LSN.

  • CONNECT_FUNCTION=0 (the default) specifies that this session should not have an LSN.
  • CONNECT_FUNCTION=1 specifies that the Teradata Database should allocate an LSN for this session.
  • CONNECT_FUNCTION=2 specifies that the Teradata Database should associate this session with an existing LSN. With this option, the existing LSN must be specified with the LOGON_SEQUENCE_NUMBER connection parameter. The Teradata Database only permits sessions for the same user to share an LSN.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

COP

Specifies whether COP Discovery is performed.

  • COP=ON (the default) specifies that the Teradata JDBC Driver performs COP Discovery.
  • COP=OFF turns off COP Discovery, so that the Teradata JDBC Driver does not search for COP suffixes, and uses only the specified hostname.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

COPLAST

Specifies how COP Discovery determines the last COP hostname.

  • When COPLAST=OFF is specified, or the COPLAST connection parameter is omitted, or COP Discovery is disabled via the COP=OFF connection parameter, then the Teradata JDBC Driver will not perform a DNS lookup for the coplast hostname.
  • When COPLAST=ON is specified, and COP Discovery is enabled, then the Teradata JDBC Driver will first perform a DNS lookup for a coplast hostname to obtain the IP address of the last COP hostname before performing COP Discovery. Subsequently, during COP Discovery, the Teradata JDBC Driver will stop searching for COP hostnames when either an unknown COP hostname is encountered, or a COP hostname is encountered whose IP address matches the IP address of the coplast hostname.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 16.00.00.28.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 16.00.00.28.

This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 16.00.00.28.

This parameter is available for Raw connections beginning with Teradata JDBC Driver 16.00.00.28.

DATABASE

DATABASE=default database name

Specifies a default database for use after logon, to override the default database defined for the Teradata Database user.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is available for JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 15.10.00.08.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

Note:  The DATABASE connection parameter works normally for a user with an expired password when the NEW_PASSWORD connection parameter is also specified. However, the DATABASE connection parameter has no effect for a conditional connection that is established for a user with an expired password when the NEW_PASSWORD connection parameter is not specified. In this situation, the application can submit a MODIFY USER statement to assign a new password to the user. After the new password has been set, the application can submit a DATABASE statement to change the default database for the connection.

DBS_PORT

Connects to the Teradata Database on the Transmission Control Protocol/Internet Protocol (TCP/IP) port specified. The default Teradata Database port is 1025.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

DDSTATS

DDSTATS values are ON or OFF:

  • When set to ON, the Teradata JDBC Driver's DatabaseMetaData methods execute an EXPLAIN command with DIAGNOSTIC HELPSTATS ON prior to executing each query against the Data Dictionary. If the Teradata Database recommends any COLLECT STATS commands, then the recommended commands are logged at the ERROR logging level. The recommended COLLECT STATS commands are NOT executed. The Teradata Database administrator must execute the COLLECT STATS commands separately. DDSTATS=ON will have a performance impact. DDSTATS=ON is a troubleshooting tool only, and is not recommended for normal production usage.
  • When set to OFF (the default), the Teradata JDBC Driver's DatabaseMetaData methods do not execute EXPLAIN commands.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 13.00.00.25.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 13.00.00.25.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

ENCRYPTDATA

ENCRYPTDATA values are ON or OFF:

  • When set to OFF (the default), data exchanged between the Teradata JDBC Driver and the Teradata Database is not encrypted.
  • When set to ON, data exchanged between the Teradata JDBC Driver and the Teradata Database is encrypted. This provides greater security, though performance is impacted.

Encryption algorithms are associated with Logon Mechanisms, so the LOGMECH connection parameter governs which encryption algorithm is used when data encryption occurs.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

Note:  Beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.15, data encryption occurs if required by centralized administration, or if the application specifies the ENCRYPTDATA=ON connection parameter. Encryption required by centralized administration overrides the ENCRYPTDATA=OFF connection parameter.

FIELD_SEP

Specifies a field separator for use with JDBC FastLoad CSV only. The default separator is ',' (comma). Refer to the section Field Separators Supported by JDBC FastLoad CSV for more details.

This parameter is not available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is not available for JDBC FastExport or JDBC FastLoad connections.

This parameter is available for JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 13.00.00.26.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

FINALIZE_AUTO_CLOSE

FINALIZE_AUTO_CLOSE values are OFF (default) or ON:

  • When set to OFF (the default), the Teradata JDBC Driver provides the JDBC 4.0 API Specification behavior such that JDBC objects are not closed automatically during finalize. The application is responsible for closing or freeing JDBC objects.
  • When set to ON, the Teradata JDBC Driver provides the JDBC 3.0 API Specification behavior to close JDBC objects during finalize. This will have a performance impact on garbage collection, and is not recommended.

Java programming best practice is to avoid finalize methods altogether. If a finalize method is used, best practice is to minimize its processing time, and to avoid operations that can take a long time, such as network communications. The JDBC 3.0 API Specification contradicted these best practices by requiring a JDBC Driver to close JDBC objects automatically during garbage collection. The JDBC 4.0 API Specification dropped the requirement for automatic closing of JDBC objects during garbage collection, so the JDBC 4.0 API Specification is in agreement with these best practices.

Garbage collection can be blocked indefinitely when FINALIZE_AUTO_CLOSE is set to ON, and the Teradata JDBC Driver does not receive a response from the Teradata Database after sending a message to the Teradata Database to close the response spool.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 14.00.00.08.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 14.00.00.08.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

GETURL_CREDENTIALS

GETURL_CREDENTIALS values are OFF (default) or ON:

  • When set to OFF (the default), the URL string returned by the DatabaseMetaData getURL method will not contain the USER, PASSWORD, or NEW_PASSWORD connection parameters.
  • When set to ON, the DatabaseMetaData getURL method will return a URL string containing the USER, PASSWORD, and NEW_PASSWORD connection parameters, if available. This option should be used with caution, since exposing the connection's credentials may pose a security risk.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 16.00.00.28.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 16.00.00.28.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

GOVERN

GOVERN values are ON (default) or OFF:

  • When set to ON (the default), permits Teradata Active System Management (TASM) to delay a JDBC FastLoad/FastExport operation. When TASM is enabled in the Teradata Database, FastLoad/FastExport operations are controlled by TASM, which may delay or reject a FastLoad/FastExport operation according to the TASM rules. If TASM is disabled, a FastLoad/FastExport operation will not be delayed, although it may still be rejected if it exceeds the maximum permitted number of concurrent FastLoad/FastExport operations. The limit is configured in the Teradata Database.
  • When set to OFF, prevents TASM from delaying a JDBC FastLoad/FastExport operation. Beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.0.0.17, a JDBC FastLoad/FastExport operation will be rejected with error code 3151 when TASM is enabled for workload definitions or throttle rules. In prior versions of the Teradata JDBC Driver and Teradata Database, a JDBC FastLoad/FastExport operation will be rejected, but never delayed, if the FastLoad/FastExport operation exceeds a TASM limit or system limit, and a SQLException will be thrown with error code 2633. This behavior will occur when TASM is disabled, and this behavior will occur when TASM is enabled and throttle rules are in use. However, GOVERN OFF may not have the desired effect when TASM is enabled and workload definitions are in use.
  GOVERN OFF GOVERN ON
Throttle rule: JDBC FastLoad/FastExport Provides fail-fast behavior with error 3151 beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.0.0.17. There is no effect with prior versions of the Teradata Database and Teradata JDBC Driver. Provides delay
Throttle rule: 3rd-party non-Teradata Provides fail-fast behavior with error 2633 No effect
Workload definition: JDBC FastLoad/FastExport Provides fail-fast behavior with error 3151 beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.0.0.17. There is no effect with prior versions of the Teradata Database and Teradata JDBC Driver. Provides delay
Workload definition: 3rd-party non-Teradata Does not provide fail-fast behavior No effect

This parameter is not available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

LITERAL_UNDERSCORE

Many DatabaseMetaData methods have arguments to specify LIKE-predicate patterns to match object names. These method arguments have a name ending in the word Pattern, such as schemaPattern and tableNamePattern.

Within a pattern, the percent-sign character " % " is a wildcard that matches any sequence of zero or more characters, and the underscore character " _ " is a wildcard that matches any single character. To match a literal wildcard character in an object name, the wildcard character must be escaped (preceded) by a backslash character " \ ".

LITERAL_UNDERSCORE=ON will automatically escape all unescaped underscore characters in DatabaseMetaData method pattern arguments. The default is OFF.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 15.10.00.14.

This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0.

This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 15.10.00.14.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

LOB_SUPPORT

LOB_SUPPORT=OFF disables LOB support and related features.

LOB_SUPPORT=ON (the default) enables Large Object (LOB) support and a collection of related features. LOB_SUPPORT must be enabled if the application:

  • Selects or inserts LOB data
  • Uses Scrollable Result Sets
  • Uses Updatable Result Sets

When LOB_SUPPORT is ON (the default), the application is limited to 16 open responses from the Teradata Database. See Working with LOBs if you receive Teradata Database Error 3130 "Response limit exceeded".

Turning off LOB_SUPPORT and subsequently attempting to use scrollable result sets or updatable result sets causes the driver to downgrade the result set to forward-only (scrollable result sets) or read-only (updatable result sets). In addition, a SQLWarning is returned to indicate the downgrade.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

LOB_TEMP_TABLE

Specifies the name of a table with the following columns: id integer, bval blob, cval clob

The user or the application must have previously created the table. The Teradata JDBC Driver will not automatically create the table. See Updatable LOBs for more information.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

LOG

Specifies the logging level (verbosity) for the a connection. Logging is always enabled. The logging levels are listed in order from terse to verbose.

  • LOG=ERROR causes error messages for that connection to be printed to System.out. This is the default value.
  • LOG=TIMING causes error and timing messages for that connection to be printed to System.out.
  • LOG=INFO causes error, timing, and informational messages for that connection to be printed to System.out.
  • LOG=DEBUG causes error, timing, informational, and debug messages for that connection to be printed to System.out. This parameter value is supported for the Default Connection for Java External Stored procedures.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

LOGDATA

Specifies additional data needed by a logon mechanism, such as a secure token, Distinguished Name, or a domain/realm name. LOGDATA values are specific to each logon mechanism. LOGDATA is not used with the TD2 mechanism.

  • LOGDATA for the KRB5 mechanism can contain the Kerberos username, instance, realm, and password. Use is optional, the current user can logon without supplying this information. The sequence @@ always precedes the password. For example:

    LOGDATA=user1@ESDOM.ESDEV.TDAT@@mypassword

  • LOGDATA for the LDAP mechanism can contain spaces, commas, and single quotes. When specifying this parameter for LDAP in a JDBC connection URL, the LOGDATA parameter should be enclosed in single quotes. Within the quoted string, a double-single quote can be used to represent an instance of a single quote. When specifying this parameter with a DataSource, no special processing is required. For example:

    LOGDATA='authcid=username password=userpassword'

    or

    LOGDATA=username@@userpassword

When the username is specified in the LOGDATA parameter, then the username must not be specified with the DriverManager.getConnection method, and the username must not be specified with the USER connection parameter.

When the password is specified in the LOGDATA parameter, then the password must not be specified with the DriverManager.getConnection method, and the password must not be specified with the PASSWORD connection parameter.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

LOGMECH

Specifies the Logon Mechanism, which determines the connection's authentication and encryption capabilities. Refer to the Teradata Database Security Administration reference for more information about logon mechanisms.

  • LOGMECH=TD2 uses Teradata Method 2
  • LOGMECH=KRB5 uses Kerberos V5
  • LOGMECH=LDAP uses Lightweight Directory Access Protocol
  • LOGMECH=TDNEGO automatically selects the appropriate logon mechanism, available beginning with Teradata Database 15.10 and Teradata JDBC Driver 15.10.00.31

When the LOGMECH parameter is omitted, the Teradata JDBC Driver uses the local default mechanism. If no local default mechanism is defined, then the Teradata JDBC Driver uses the default mechanism indicated by the Teradata Database.

Not all logon mechanisms are available in all environments. If you specify a logon mechanism that is not available in your environment, then the JDBC connection attempt will fail, and a SQLException will be thrown.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

LOGON_SEQUENCE_NUMBER

Specifies an existing Logon Sequence Number (LSN) to associate this session with.

This connection parameter must be used in conjunction with the CONNECT_FUNCTION=2 connection parameter. The Teradata Database only permits sessions for the same user to share an LSN.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

MAYBENULL

Controls the behavior of the ResultSetMetaData.isNullable method.

  • MAYBENULL=OFF (the default) specifies that, when StatementInfo parcel support is available, the return value of the ResultSetMetaData.isNullable method is determined from the StatementInfo parcel IsNullable field provided by the Teradata Database.
  • MAYBENULL=ON specifies that, when StatementInfo parcel support is available, the return value of the ResultSetMetaData.isNullable method is determined from the StatementInfo parcel MayBeNull field provided by the Teradata Database.

This parameter has no effect when StatementInfo parcel support is unavailable.

The JDBC escape function {fn teradata_provide(request_scope_maybenull_on)} takes priority over the MAYBENULL connection parameter, for that particular SQL request.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 16.00.00.28.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 16.00.00.28.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

MAX_MESSAGE_BODY

Specifies the maximum Response Message size in bytes.

This connection parameter is available beginning with Teradata JDBC Driver 15.10.0.25.

  • Beginning with Teradata JDBC Driver 15.10.0.25 and Teradata Database 16.0, the maximum Teradata Database Response Message size is 16MB. When the MAX_MESSAGE_BODY connection parameter is omitted, the default is a maximum Response Message size of 2MB.
  • Beginning with Teradata JDBC Driver 15.10.0.25, for Teradata Database releases prior to Teradata Database 16.0, the maximum Teradata Database Response Message size is 1MB. When the MAX_MESSAGE_BODY connection parameter is omitted, the default is a maximum Response Message size of 1MB.
  • For Teradata JDBC Driver versions prior to Teradata JDBC Driver 15.10.0.25, the maximum Teradata Database Response Message size is 1MB. The MAX_MESSAGE_BODY connection parameter is not available prior to Teradata JDBC Driver 15.10.0.25.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is not available for Raw connections.

When the MAX_MESSAGE_BODY connection parameter is specified, the maximum Response Message size is the smaller of the MAX_MESSAGE_BODY value and the maximum Teradata Database Response Message size.

Note:  Performance may be hurt by specifying a smaller MAX_MESSAGE_BODY value than the default maximum Response Message size. This is not recommended.

Performance may be improved by specifying a larger MAX_MESSAGE_BODY value than the default maximum Response Message size, but JVM memory consumption will increase. You must ensure that sufficient JVM memory is available.

NEW_PASSWORD

This connection parameter enables an application to change an expired password automatically.

When a connection is established for a user with an expired password, it is a conditional connection. The only SQL request that can be submitted on a conditional connection is a MODIFY USER statement that assigns a new password to the user.

Each Teradata Database user is allowed only one conditional connection at a time. To submit a new password for the user, all previously established connections for the user need to be ended, across all Teradata client products and interfaces such as Basic Teradata Query (BTEQ), SQL Assistant (SQLA), the Teradata JDBC Driver, and so forth.

When a conditional connection is established for a user with an expired password and the NEW_PASSWORD parameter:

  • Is specified and set to a non-empty password value–the Teradata JDBC Driver automatically submits a MODIFY USER statement to the Teradata Database to update the user's password. The Connection.getWarnings method returns null.
  • Is not specified–the Connection.getWarnings method returns an SQLWarning with Teradata Database error code 3032 to indicate an expired password. The application can submit a MODIFY USER statement to assign a new password to the user.

After the MODIFY USER statement successfully changes the expired password, the connection is no longer conditional—the connection can be used normally.

Note that when the NEW_PASSWORD connection property is specified for an application server DataSource and the Teradata JDBC Driver automatically changes the expired password, the original password defined in the application server DataSource is not updated.

The application can either:

  • Make the original password and the NEW_PASSWORD value the same, which allows a single DataSource to be used, or
  • Define multiple DataSources in a cycle whose passwords chain to each other so that each DataSource's NEW_PASSWORD value matches the next DataSource's password. The application needs to keep track of which DataSource is the current DataSource. This technique is complex, but it offers the security of being able to specify the set of passwords in DataSource definitions separate from the application and to control centrally from the Teradata Database when the password should change.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

PARTITION

Specifies the Teradata Database partition for the Connection.

  • PARTITION=DBC/SQL (the default) directs the Teradata JDBC Driver to connect to the standard DBC/SQL partition.
  • PARTITION=MONITOR directs the Teradata JDBC Driver to connect to the Monitor partition.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is not available for JDBC FastExport, JDBC FastLoad, or JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

PASSWORD

Specifies the password for the Connection when the DriverManager.getConnection method's password argument is omitted or null.

This connection parameter is primarily intended for use with the single-argument DriverManager.getConnection(String url) method.

When the two-argument DriverManager.getConnection(String url, Properties info) method is used, a non-null "password" property value in the Properties info argument will override this connection parameter.

When the three-argument DriverManager.getConnection(String url, String user, String password) method is used, a non-null password argument will override this connection parameter.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 14.00.00.13.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 14.00.00.13.

This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 14.00.00.13.

This parameter is available for Raw connections beginning with Teradata JDBC Driver 14.00.00.13.

PREP_SUPPORT

Specifies whether the Teradata Database performs a prepare operation when a PreparedStatement or CallableStatement is created.

  • PREP_SUPPORT=ON (the default) specifies that the Teradata Database performs a prepare operation when a PreparedStatement or CallableStatement is created. A prepare operation is required for the proper functioning of many Teradata JDBC Driver features.
  • PREP_SUPPORT=OFF avoids a prepare operation when a PreparedStatement or CallableStatement is created. This option is not recommended for production use, and is only intended for troubleshooting performance issues in a test environment.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0.

This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

RECONNECT_COUNT

Enables Teradata Session Reconnect. Specifies the maximum number of times that the Teradata JDBC Driver will attempt to reconnect the session. See Teradata Session Reconnect for more information.

  • Teradata Session Reconnect is enabled by specifying RECONNECT_COUNT or RECONNECT_INTERVAL, or by enabling Recoverable Network Protocol.
  • When RECONNECT_COUNT is omitted, but Teradata Session Reconnect is enabled with other connection parameters or configuration settings, then the default value for RECONNECT_COUNT is 11 attempts.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is available for Raw connections.

Note:  Specifying this parameter will override the normal fail-fast behavior for connection failures.

RECONNECT_INTERVAL

Enables Teradata Session Reconnect. Specifies the number of seconds that the Teradata JDBC Driver will wait in between attempts to reconnect the session. See Teradata Session Reconnect for more information.

  • Teradata Session Reconnect is enabled by specifying RECONNECT_COUNT or RECONNECT_INTERVAL, or by enabling Recoverable Network Protocol.
  • When RECONNECT_INTERVAL is omitted, but Teradata Session Reconnect is enabled with other connection parameters or configuration settings, then the default value for RECONNECT_INTERVAL is 30 seconds.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is available for Raw connections.

Note:  Specifying this parameter will override the normal fail-fast behavior for connection failures.

REDRIVE

Enables Teradata Session Reconnect, and also enables automatic redriving of SQL requests interrupted by database restart. See Teradata Session Reconnect for more information.

  • REDRIVE=0 disables the use of Control Data, disables Recoverable Network Protocol, and disables automatic Redrive of SQL requests. This setting is the default for TYPE=RAW connections.
  • REDRIVE=1 solicits the use of Control Data, but disables Recoverable Network Protocol, and disables automatic Redrive of SQL requests.
  • REDRIVE=2 solicits the use of Control Data, solicits Recoverable Network Protocol, but disables automatic Redrive of SQL requests.
  • REDRIVE=3 solicits the use of Control Data, solicits Recoverable Network Protocol, and specifies no preference with respect to automatic Redrive of SQL requests. This setting is the default for SQL connections.
  • REDRIVE=4 solicits the use of Control Data, solicits Recoverable Network Protocol, and solicits automatic Redrive of SQL requests.

This parameter is available for SQL connections beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.12.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.12.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is available for Raw connections.

Note:  The application is not guaranteed to receive the functionality that it solicits with the REDRIVE connection parameter. The Teradata Database determines whether the functionality is provided or not, depending on the Teradata Database dbscontrol fields RedriveProtection (67), RedriveDefaultParticipation (68), and DisableRecoverableNetProtocol (77).

RUNSTARTUP

Specifies the RUNSTARTUP value as OFF or ON. The default is OFF.

  • When omitted, or set to OFF, specifies that the user's STARTUP SQL request is not executed.
  • When set to ON, the user's STARTUP SQL request is executed after logon. For more information, refer to User STARTUP SQL Request.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 15.10.00.08.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

SESSIONS

Specifies the number of FastLoad or FastExport connections to be created, where 1 <= number of FastLoad or FastExport connections <= number of AMPs.

This parameter is not available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

Note:  It is recommended to omit the SESSIONS parameter and let the Teradata Database determine the appropriate number of FastLoad or FastExport connections.

SIP_SUPPORT

Controls whether the Teradata Database and Teradata JDBC Driver use StatementInfo Parcel (SIP) to convey metadata.

  • SIP_SUPPORT=ON (the default) specifies that the Teradata Database and Teradata JDBC Driver use SIP to convey metadata. SIP is required for the proper functioning of many Teradata Database and Teradata JDBC Driver features.
  • SIP_SUPPORT=OFF disables the use of SIP to convey metadata. This option is not recommended for production use, and is only intended for troubleshooting performance issues in a test environment.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0.

This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

SP_SPL

Specifies behavior for creating or replacing Teradata stored procedures.

  • SP_SPL=SPL (the default) directs the Teradata Database to store Stored Procedure Language (SPL) source text when a stored procedure is created.
  • SP_SPL=NOSPL directs the Teradata Database not to store Stored Procedure Language (SPL) source text. If a stored procedure is created without source text, the SHOW PROCEDURE statement will return an error.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

STRICT_ENCODE

Specifies behavior for encoding character data to transmit to the Teradata Database. The default is OFF.

  • When set to ON, the Teradata JDBC Driver will throw a SQLException when the SQL request text or any parameter values contain a character that cannot be represented in the session character set.
  • When set to OFF or omitted, the Teradata JDBC Driver will substitute a question mark for each character that cannot be represented in the session character set.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 14.10.00.18.

This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 14.10.00.18.

This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 14.10.00.18.

This parameter is not available for Raw connections.

Note:  Specifying STRICT_ENCODE=ON may reduce performance for transmitting character data to the Teradata Database. STRICT_ENCODE=ON is intended for use with session character sets other than UTF8 and UTF16, because all Unicode characters can be represented in the UTF8 and UTF16 session character sets.

In a JDK 5.0 or later environment, STRICT_ENCODE=ON can be used with all supported session character sets.

In a JDK 1.4.2 environment, STRICT_ENCODE=ON cannot be used with the following session character sets:

  • EBCDIC037_0E
  • EBCDIC273_0E
  • EBCDIC277_0E
  • HANGULEBCDIC933_1II
  • SCHEBCDIC935_2IJ
  • TCHEBCDIC937_3IB
  • KANJIEBCDIC5026_0I
  • KANJIEBCDIC5035_0I

TCP

Specifies one or more TCP socket settings, separated by plus signs ("+").

The following TCP socket settings may be specified:

  • KEEPALIVE - Turns on TCP keepalive. The default, if omitted, is for TCP keepalive to be turned off.
  • NODELAY - Turns on TCP nodelay, which disables Nagle's algorithm. The default, if omitted, is for Nagle's algorithm to be used.
  • SENDn - Sets the TCP socket send buffer size to the number of bytes specified by the integer value n. Beginning with Teradata JDBC Driver 15.10.0.25, the default, if omitted, is to use the underlying platform/operating system default send buffer size. With prior versions of the Teradata JDBC Driver, the default, if omitted, is a TCP socket send buffer size of 65156 bytes.
  • RECEIVEn - Sets the TCP socket receive buffer size to the number of bytes specified by the integer value n. Beginning with Teradata JDBC Driver 15.10.0.25, the default, if omitted, is to use the underlying platform/operating system default receive buffer size. With prior versions of the Teradata JDBC Driver, the default, if omitted, is a TCP socket receive buffer size of 65156 bytes.
  • NOLINGER - Turns linger off for TCP socket close. This setting is mutually exclusive with LINGERn. The default, if omitted, is LINGER10.
  • LINGERn - Turns linger on for TCP socket close, and lingers for n seconds. The default, if omitted, is LINGER10 (linger for 10 seconds).
  • TRAFFICn - Sets the TCP traffic class to the integer value n. The default, if omitted, is for the TCP traffic class not to be set explicitly, thereby using the JVM default. For more information, please refer to the javadoc for the java.net.Socket.setTrafficClass method.

The SENDn and RECEIVEn settings are only requests, or hints, to the underlying platform/operating system's TCP stack. Some platforms/operating systems may round the requested values up or down, or may ignore the requested values entirely.

The SENDn and RECEIVEn settings are separate from the maximum Response Message size. For more information, refer to the description of the MAX_MESSAGE_BODY connection parameter.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is available for Raw connections.

 

Examples:

  • TCP=NOLINGER - Turns linger off for TCP socket close.
  • TCP=LINGER30 - Turns linger on for TCP socket close, and lingers for 30 seconds.
  • TCP=SEND8192+RECEIVE8192 - Requests 8KB TCP socket send and receive buffer sizes. Some platforms/operating systems may round the requested values up or down, or may ignore the requested values entirely.

Settings may be specified in any order. For example, TCP=NODELAY+KEEPALIVE is equivalent to TCP=KEEPALIVE+NODELAY.

TMODE

Specifies the transaction mode for the connection.

  • TMODE=ANSI uses American National Standards Institute (ANSI) transaction semantics. This mode is recommended.
  • TMODE=TERA uses legacy Teradata transaction semantics. This mode is only recommended for legacy applications that require Teradata transaction semantics.
  • TMODE=DEFAULT (the default) uses the default transaction mode configured for the Teradata Database, which may be either ANSI or TERA mode.

See Transaction Mode for more information regarding the ANSI and Teradata transaction modes.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is available for Raw connections.

TNANO

TNANO=number of fractional digits

Specifies the fractional seconds precision for all java.sql.Time values bound to a PreparedStatement or CallableStatement and transmitted to the Teradata Database as TIME or TIME WITH TIME ZONE values.

This parameter can potentially increase or decrease (truncate) the number of fractional digits that are transmitted to the Teradata Database. For example, if the TNANO=3 connection parameter is specified, then the java.sql.Time value 10:02:30 is transmitted to the Teradata Database as the TIME value 10:02:30.000.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures.

This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is not available for Raw connections.

TRUSTED_SQL

TRUSTED_SQL values are ON and OFF. The default is OFF.

This connection parameter is only effective if the database user making this request was given "GRANT CONNECT THROUGH" rights "WITH TRUST_ONLY". If "GRANT CONNECT THROUGH" rights were given without the "TRUST_ONLY" option, the TRUSTED _SQL parameter has no effect.

  • When set to OFF, the Teradata Database will reject any attempt to use the SET QUERY_BAND command to set or remove the current proxy user if the database user making this request was given "GRANT CONNECT THROUGH" rights "WITH TRUST_ONLY".
  • When set to ON, the Teradata Database will allow the SET QUERY_BAND command to set or remove the current proxy user. This capability can be disabled on a per-request basis by including the {fn teradata_untrusted} escape function in the SQL to be run.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

TSNANO

TSNANO=number of fractional digits

Specifies the fractional seconds precision for all java.sql.Timestamp values bound to a PreparedStatement or CallableStatement and transmitted to the Teradata Database as TIMESTAMP or TIMESTAMP WITH TIME ZONE values.

This parameter can potentially increase or decrease (truncate) the number of fractional digits that are transmitted to the Teradata Database. For example, if the TSNANO=3 connection parameter is specified, then the java.sql.Timestamp value 2016-05-17 10:02:30.123456 is transmitted to the Teradata Database as the TIMESTAMP value 2016-05-17 10:02:30.123.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures.

This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is available for JDBC Monitor connections.

This parameter is not available for Raw connections.

TYPE

Specifies the type of protocol to be used with the Teradata Database for SQL statements. Options are:

  • TYPE=DEFAULT directs the Teradata JDBC Driver to use the standard protocol for all SQL statements
  • TYPE=FASTLOAD directs the Teradata JDBC Driver to use the FastLoad protocol for FastLoad-capable SQL INSERT statements and the standard protocol for all other SQL statements
  • TYPE=FASTLOADCSV directs the Teradata JDBC Driver to use the FastLoad protocol for FastLoadCSV-capable SQL INSERT statements. Other SQL statements are not supported by this type of connection. This feature is available beginning with Teradata JDBC Driver 13.00.00.26.
  • TYPE=FASTEXPORT directs the Teradata JDBC Driver to use the FastExport protocol for FastExport-capable SQL SELECT statements and the standard protocol for all other SQL statements
  • TYPE=RAW constructs a Raw Connection that is equivalent to CLI's Buffer Mode, in which the application takes responsibility for composing messages sent to the Teradata Database and parsing messages received from the Teradata Database.

This parameter is available for SQL connections.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections. Instead, specify PARTITION=MONITOR to create a JDBC Monitor connection.

This parameter is available for Raw connections.

USER

Specifies the username for the Connection when the DriverManager.getConnection method's user argument is omitted or null.

This connection parameter is primarily intended for use with the single-argument DriverManager.getConnection(String url) method.

When the two-argument DriverManager.getConnection(String url, Properties info) method is used, a non-null "user" or "username" property value in the Properties info argument will override this connection parameter.

When the three-argument DriverManager.getConnection(String url, String user, String password) method is used, a non-null user argument will override this connection parameter.

This parameter is available for SQL connections beginning with Teradata JDBC Driver 14.00.00.13.

This parameter is not available for the Default Connection for Java Stored Procedures.

This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 14.00.00.13.

This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 14.00.00.13.

This parameter is available for Raw connections beginning with Teradata JDBC Driver 14.00.00.13.

USEXVIEWS

Specifies which Data Dictionary views should be queried to return result sets from DatabaseMetaData methods.

  • USEXVIEWS=OFF (the default) directs the Teradata JDBC Driver to query non-X views to obtain DatabaseMetaData.
  • USEXVIEWS=ON directs the Teradata JDBC Driver to query X views to obtain DatabaseMetaData. The Teradata Database's Data Dictionary X views are limited to providing information about database objects that the user has access to. The X views examine database object access rights, imposing a performance penalty and, as a result, the use of X views is slower than non-X views.

Refer to Teradata Database Data Dictionary for additional information on X views.

This parameter is available for SQL connections.

This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0.

This parameter is available for JDBC FastExport and JDBC FastLoad connections.

This parameter is not available for JDBC FastLoad CSV connections.

This parameter is not available for JDBC Monitor connections.

This parameter is not available for Raw connections.

Data Source Interface

When using an application server, the preferred way to obtain a java.sql.Connection is to use the java.sql.DataSource interface.

DataSource allows the encapsulation all of the parameters associated with obtaining a database connection in one object that has a logical name. You can then just ask for a connection to that name (for example, Teradata1) and not have to be aware of the numerous parameters that are required to fulfill this request.

A DataSource must be created before it can be used. This job is normally done by a system administrator. The work involved amounts to setting the parameters associated with a Driver Manager URL into the DataSource object and saving it as a file or network‑addressable resource.

The names of these parameters must be identical to their names used in the URL, including capitalization. For example, if CHARSET is used to set the character set property, then CharSet, CHARSet or CHARSEt would not set it. Additional properties dataSourceName, description, user and password are only available at the DataSource level. Beginning with Teradata JDBC Driver 16.00.00.28, the Teradata JDBC Driver validates Data Source property values and throws SQLException for an invalid property value.

Refer to TeraDataSource Class for additional information.

DataSources are accessed with the Java Naming and Directory Interface (JNDI). For more information, visit http://www.oracle.com/technetwork/java/jndi/

With pooled connections, it is very important that the connection be closed when the user no longer needs it. Otherwise, it is not returned to the connection pool. When using pooled connections, it is advisable to have a finally block after try/catch blocks to ensure that connections are closed.

Session Attributes Warning

The Teradata Database does not provide a facility to reset a session's attributes. Therefore, the user of a connection pool data source must be aware that any commands that affect session attributes must not be used. Any changes to session attributes continue to be in effect for the next unsuspecting user of that connection.

Session attributes that MUST NOT BE CHANGED include:

COP Discovery

The Teradata JDBC Driver provides Communications Processor (COP) discovery behavior when the COP=ON connection parameter is specified, or when the COP connection parameter is omitted.

A Teradata Database system can be composed of multiple Teradata Database nodes. One or more of the Teradata Database nodes can be configured to run the Teradata Database Gateway process. Each Teradata Database node that runs the Teradata Database Gateway process is termed a Communications Processor, or COP. COP Discovery refers to the procedure of identifying all the available COP hostnames and their IP addresses. COP hostnames can be defined in DNS, or can be defined in the client system's hosts file. Teradata strongly recommends that COP hostnames be defined in DNS, rather than the client system's hosts file. Defining COP hostnames in DNS provides centralized administration, and enables centralized changes to COP hostnames if and when the Teradata Database is reconfigured.

Beginning with Teradata JDBC Driver 16.00.00.28, the COPLAST connection parameter specifies how COP Discovery determines the last cop hostname. When the COPLAST=OFF connection parameter is specified, or the COPLAST connection parameter is omitted, or COP Discovery is disabled via the COP=OFF connection parameter, then the Teradata JDBC Driver will not perform a DNS lookup for the coplast hostname.

Beginning with Teradata JDBC Driver 16.00.00.28, when the COPLAST=ON connection parameter is specified, and COP Discovery is enabled, then the Teradata JDBC Driver will first perform a DNS lookup for a coplast hostname to obtain the IP address of the last COP hostname before performing COP Discovery. Subsequently, during COP Discovery, the Teradata JDBC Driver will stop searching for COP hostnames when either an unknown COP hostname is encountered, or a COP hostname is encountered whose IP address matches the IP address of the coplast hostname.

When performing COP Discovery, the Teradata JDBC Driver starts with cop1, which is appended to the database hostname, and then proceeds with cop2, cop3, ..., copN. The Teradata JDBC Driver supports domain-name qualification for COP Discovery and the coplast hostname. Domain-name qualification is recommended, because it can improve performance by avoiding unnecessary DNS lookups for DNS search suffixes.

The following table illustrates the DNS lookups performed for a hypothetical three-node Teradata Database system named "whomooz".

 

No domain name qualification

With domain name qualification (Recommended)

Application-specified Teradata Database hostname

whomooz

whomooz.domain.com

DNS lookups with COP Discovery turned on, and COPLAST=OFF or omitted

 

(Default behavior)

whomoozcop110.0.0.1

whomoozcop210.0.0.2

whomoozcop310.0.0.3

whomoozcop4 → undefined

 

DNS lookups are performed until an unknown COP hostname is encountered.

whomoozcop1.domain.com10.0.0.1

whomoozcop2.domain.com10.0.0.2

whomoozcop3.domain.com10.0.0.3

whomoozcop4.domain.com → undefined

DNS lookups with COP Discovery turned on, and COPLAST=ON

whomoozcoplast10.0.0.3

whomoozcop110.0.0.1

whomoozcop210.0.0.2

whomoozcop310.0.0.3

 

DNS lookups are performed until a COP hostname is found whose IP address matches the coplast hostname, or an unknown COP hostname is encountered.

whomoozcoplast.domain.com10.0.0.3

whomoozcop1.domain.com10.0.0.1

whomoozcop2.domain.com10.0.0.2

whomoozcop3.domain.com10.0.0.3

DNS lookup with COP Discovery turned off (COP=OFF)

whomooz → round-robin list 10.0.0.1, 10.0.0.2, 10.0.0.3

 

Round-robin is only possible with DNS, not with the client system hosts file.

whomooz.domain.com → round-robin list 10.0.0.1, 10.0.0.2, 10.0.0.3

The Teradata JDBC Driver supports the definition of multiple IP addresses for COP hostnames and non-COP hostnames. The Teradata JDBC Driver calls the Java API method InetAddress.getAllByName to obtain all the IP addresses defined for each hostname.

For the first access to a particular Teradata Database system, the Teradata JDBC Driver generates a random number to index into the list of COPs. For each subsequent access, the Teradata JDBC Driver increments the saved index until it wraps around to the first position. This behavior provides load distribution across all defined COPs.

The Teradata JDBC Driver masks connection failures to down COPs, thereby hiding most connection failures from the client application. An exception is thrown to the application only when all the COPs are down for that database. If a COP is down, the next COP in the sequence (including a wrap-around to the first COP) receives extra connections that were originally destined for the down COP. When multiple IP addresses are defined in DNS for a COP, the Teradata JDBC Driver will attempt to connect to each of the COP's IP addresses, and the COP is considered down only when connection attempts fail to all of the COP's IP addresses.

If COP Discovery is turned off, or no COP hostnames are defined in DNS, the Teradata JDBC Driver connects directly to the database hostname provided in the connection URL. This permits load distribution schemes other than the COP Discovery approach. For example, round-robin DNS or a TCP/IP load distribution product can be used. COP Discovery takes precedence over simple database hostname lookup. To use an alternative load distribution scheme, either ensure that no COP hostnames are defined in DNS, or specify the COP=OFF connection parameter.

InetAddress Caching

The JVM caches DNS lookups, and the Teradata JDBC Driver does not maintain its own cache of DNS name resolutions. The administrator can use the standard JVM system properties for cache control as defined in the javadoc for the InetAddress class.

The InetAddress class caches both successful and unsuccessful host name resolutions. The positive caching guards against DNS spoofing attacks, and the negative caching improves performance.

By default, the result of positive host name resolutions are cached forever, because there is no general rule to decide when it is safe to remove cache entries. The result of an unsuccessful host name resolution is cached for a very short period of time (10 seconds) to improve performance.

Under certain circumstances where it can be determined that DNS spoofing attacks are not possible, a Java security property can be set to a different Time-to-live (TTL) value for positive caching. Likewise, a system administrator can configure a different negative caching TTL value when needed. Two Java security properties control the TTL values used for positive and negative host name resolution caching:

networkaddress.cache.ttl (default: -1) 

indicates the caching policy for successful name lookups from the name service. The value is specified as an integer to indicate the number of seconds to cache the successful lookup.

A value of -1 indicates cache forever.

networkaddress.cache.negative.ttl (default: 10) 

indicates the caching policy for unsuccessful name lookups from the name service. The value is specified as an integer to indicate the number of seconds to cache the failure for unsuccessful lookups.

A value of 0 indicates never cache.

A value of -1 indicates cache forever.

Stored Password Protection

Overview

Teradata JDBC Driver Stored Password Protection enables an application to provide a JDBC connection password in encrypted form to the Teradata JDBC Driver, and also enables an application to provide the NEW_PASSWORD connection parameter's value in encrypted form.

Stored Password Protection is available beginning with Teradata JDBC Driver 16.00.00.24.

There are several different ways that an application may specify a password to the Teradata JDBC Driver, all of which may use an encrypted password:

  1. A login password specified as the third argument to the DriverManager.getConnection(String,String,String) method.
  2. A login password specified as the "password" property to the DriverManager.getConnection(String,Properties) method.
  3. A login password specified as the PASSWORD connection URL parameter with the DriverManager.getConnection(String) method.
  4. A login password specified within the LOGDATA connection URL parameter with any variant of the DriverManager.getConnection method.
  5. A login password specified as the DataSource or ConnectionPoolDataSource password parameter.
  6. A login password specified within the DataSource or ConnectionPoolDataSource LOGDATA parameter.
  7. A new password specified as the NEW_PASSWORD connection URL parameter with any variant of the DriverManager.getConnection method.
  8. A new password specified as the DataSource or ConnectionPoolDataSource NEW_PASSWORD parameter.

If the password, however specified, begins with the prefix "ENCRYPTED_PASSWORD(" then the specified password must follow this format:

   ENCRYPTED_PASSWORD(PasswordEncryptionKeyResourceName,EncryptedPasswordResourceName)

The PasswordEncryptionKeyResourceName must be separated from the EncryptedPasswordResourceName by a single comma.

The PasswordEncryptionKeyResourceName specifies the name of a resource that contains the password encryption key and associated information. The EncryptedPasswordResourceName specifies the name of a resource that contains the encrypted password and associated information. The two resources are described below.

When an encrypted password is specified for the PASSWORD, NEW_PASSWORD, and/or LOGDATA connection URL parameters, the value must be enclosed in single quotes, to enclose the "ENCRYPTED_PASSWORD(" syntax's comma separator for the resource names, otherwise that comma would be interpreted as a separator for the next connection URL parameter.

Program TJEncryptPassword

TJEncryptPassword.java is a sample program to create encrypted password files for use with Teradata JDBC Driver Stored Password Protection.

This program works in conjunction with Teradata JDBC Driver Stored Password Protection. This program creates the files containing the password encryption key and encrypted password, which can be subsequently specified to the Teradata JDBC Driver via the "ENCRYPTED_PASSWORD(" syntax.

You are not required to use this program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files. The only requirement is that the files must match the format expected by the Teradata JDBC Driver, which is documented below.

This program encrypts the password and then immediately decrypts the password, in order to verify that the password can be successfully decrypted. This program mimics the implementation of the Teradata JDBC Driver's password decryption, and is intended to openly illustrate its operation and enable scrutiny by the community.

The encrypted password is only as safe as the two files. You are responsible for restricting access to the files containing the password encryption key and encrypted password. If an attacker obtains both files, the password can be decrypted. The operating system file permissions for the two files should be as limited and restrictive as possible, to ensure that only the intended operating system userid has access to the files.

The two files can be kept on separate physical volumes, to reduce the risk that both files might be lost at the same time. If either or both of the files are located on a network volume, then an encrypted wire protocol can be used to access the network volume, such as sshfs, encrypted NFSv4, or encrypted SMB 3.0.

This program accepts eight command-line arguments:

Argument

Description

1. Transformation

Specifies the transformation argument for the Cipher.getInstance method.

Example: AES/CBC/NoPadding

2. KeySizeInBits

Specifies the keysize argument for the KeyGenerator.init method.

Specify -default to use the transformation's default key size.

Example: -default

 

To use AES with a 192-bit or 256-bit key, the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files must be downloaded from Oracle and installed in your JRE.

Example: 256

3. MAC

Specifies the algorithm argument for the Mac.getInstance method.

MAC algorithm HmacSHA256 is available with JDK 5 and later. MAC algorithm HmacSHA1 is available with JDK 1.4.2.

Example: HmacSHA256

4. PasswordEncryptionKeyFileName

Specifies a filename in the current directory, a relative pathname, or an absolute pathname.

The file is created by this program. If the file already exists, it will be overwritten by the new file.

Example: PassKey.properties

5. EncryptedPasswordFileName

Specifies a filename in the current directory, a relative pathname, or an absolute pathname.

The filename or pathname that must differ from the PasswordEncryptionKeyFileName.

The file is created by this program. If the file already exists, it will be overwritten by the new file.

Example: EncPass.properties

6. Hostname

Specifies the Teradata Database hostname.

Example: whomooz

7. Username

Specifies the Teradata Database username.

Example: guest

8. Password

Specifies the Teradata Database password to be encrypted.

Unicode characters in the password can be specified with the \uXXXX escape sequence.

Example: please

Prerequisites for using TJEncryptPassword

Complete instructions for how to download, compile, and run the Teradata JDBC Driver sample programs are available here.

The following list is a brief summary of the necessary steps to prepare for using the TJEncryptPassword program:

Example Commands

The following commands assume that the files extracted from samples.jar, the compiled class files, and the Teradata JDBC Driver jar files are all located in the current directory. The current directory must be specified on the classpath.

The TJEncryptPassword program uses the Teradata JDBC Driver to log on to the specified Teradata Database using the encrypted password, so the TJEncryptPassword program must have access to the Teradata JDBC Driver jar files on the classpath.

On Windows, the classpath must be specified with semicolon separators: java -cp .;terajdbc4.jar;tdgssconfig.jar

On all other platforms, the classpath must be specified with colon separators: java -cp .:terajdbc4.jar:tdgssconfig.jar

The following example command illustrates running the TJEncryptPassword program on Windows, using a 256-bit AES key available with the JCE Unlimited Strength Jurisdiction Policy Files, and using the HmacSHA256 algorithm available beginning with JDK 5.

java -cp .;terajdbc4.jar;tdgssconfig.jar TJEncryptPassword AES/CBC/NoPadding 256 HmacSHA256 PassKey.properties EncPass.properties whomooz guest please

The following example command illustrates running the TJEncryptPassword program on Windows, using the default AES key size due to lacking the JCE Unlimited Strength Jurisdiction Policy Files, and using the HmacSHA256 algorithm available beginning with JDK 5.

java -cp .;terajdbc4.jar;tdgssconfig.jar TJEncryptPassword AES/CBC/NoPadding -default HmacSHA256 PassKey.properties EncPass.properties whomooz guest please

The following example command illustrates running the TJEncryptPassword program on Windows, using the HmacSHA1 algorithm available with JDK 1.4.2.

java -cp .;terajdbc4.jar;tdgssconfig.jar TJEncryptPassword AES/CBC/NoPadding -default HmacSHA1 PassKey.properties EncPass.properties whomooz guest please

Password Encryption Key File Format

You are not required to use the TJEncryptPassword program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files, but the files must match the format expected by the Teradata JDBC Driver.

The password encryption key file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.

The file must contain the following string properties:

Property

Description

version=1

The version number must be 1.

This property is required.

transformation=TransformationName

This value must be a valid transformation argument for the Cipher.getInstance method.

This property is required.

algorithm=AlgorithmName

This value must correspond to the algorithm portion of the transformation.

This value must be a valid algorithm argument for the KeyGenerator.getInstance method.

This property is required.

match=MatchValue

The password encryption key and encrypted password files must contain the same match value.

The match values are compared to ensure that the two specified files are related to each other, serving as a "sanity check" to help avoid configuration errors.

This property is required.

 

Note: The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password.

key=HexDigits

This value is the password encryption key, encoded as hex digits.

This property is required.

mac=AlgorithmName

This value must be a valid algorithm argument for the Mac.getInstance method.

Teradata JDBC Driver Stored Password Protection performs Encrypt-then-MAC for protection from a padding oracle attack.

This property is required.

mackey=HexDigits

This value is the MAC key, encoded as hex digits.

This property is required.

Encrypted Password File Format

The encrypted password file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.

The file must contain the following string properties:

Property

Description

version=1

The version number must be 1.

This property is required.

match=MatchValue

The password encryption key and encrypted password files must contain the same match value.

The match values are compared to ensure that the two specified files are related to each other, serving as a "sanity check" to help avoid configuration errors.

This property is required.

 

Note: The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password.

password=HexDigits

This value is the encrypted password, encoded as hex digits.

This property is required.

params=HexDigits

This value contains the cipher algorithm parameters, if any, encoded as hex digits.

Some ciphers need algorithm parameters that cannot be derived from the key, such as an initialization vector.

This property is optional, depending on whether the cipher algorithm has associated parameters.

hash=HexDigits

This value is the expected message authentication code (MAC), encoded as hex digits.

After encryption, the expected MAC is calculated using the ciphertext, transformation name, and algorithm parameters if any.

Before decryption, the Teradata JDBC Driver calculates the MAC using the ciphertext, transformation name, and algorithm parameters if any, and verifies that the calculated MAC matches the expected MAC. If the calculated MAC differs from the expected MAC, then either or both of the files may have been tampered with.

This property is required.

Transformation, Key Size, and MAC

A transformation is a string that describes the set of operations to be performed on the given input, to produce transformed output.

A transformation always includes the name of a cryptographic algorithm such as DES or AES, and may optionally be followed by a feedback mode and padding scheme.

The JDK 7 javadoc for javax.crypto.Cipher indicates that every Java implementation must support the following transformations:

   AES/CBC/NoPadding
   AES/CBC/PKCS5Padding
   AES/ECB/NoPadding
   AES/ECB/PKCS5Padding
   DES/CBC/NoPadding
   DES/CBC/PKCS5Padding
   DES/ECB/NoPadding
   DES/ECB/PKCS5Padding
   DESede/CBC/NoPadding
   DESede/CBC/PKCS5Padding
   DESede/ECB/NoPadding
   DESede/ECB/PKCS5Padding
   RSA/ECB/PKCS1Padding
   RSA/ECB/OAEPWithSHA-1AndMGF1Padding
   RSA/ECB/OAEPWithSHA-256AndMGF1Padding

Teradata JDBC Driver Stored Password Protection uses a symmetric encryption algorithm such as DES or AES, in which the same secret key is used for encryption and decryption of the password. Teradata JDBC Driver Stored Password Protection does not use an asymmetric encryption algorithm such as RSA, with separate public and private keys.

Teradata JDBC Driver Stored Password Protection hides the password length in the encrypted password file by extending the length of the UTF8-encoded password with trailing null bytes. The length is extended to the next 512-byte boundary.

The strength of the encryption depends on your choice of cipher algorithm and key size.

Resource Names

The TJEncryptPassword program has command-line arguments PasswordEncryptionKeyFileName and EncryptedPasswordFileName to specify filenames.

In contrast, the Teradata JDBC Driver's "ENCRYPTED_PASSWORD(" syntax uses resource names, rather than filenames, in order to offer more flexibility for file storage location and access.

   ENCRYPTED_PASSWORD(PasswordEncryptionKeyResourceName,EncryptedPasswordResourceName)

Files created by the TJEncryptPassword program are subsequently accessed as resources by the Teradata JDBC Driver. The resource names include a prefix to indicate how the resource must be accessed. If the resource name begins with the "classpath:" prefix, then the Teradata JDBC Driver loads the resource from the classpath. If you specify a resource name with the "classpath:" prefix, then you must ensure the resource is available on the classpath for the Teradata JDBC Driver.

For security, classpath resources are required to have specific resource name prefixes. The PasswordEncryptionKeyResourceName must begin with "PassKey" and the EncryptedPasswordResourceName must begin with "EncPass".

Example:

   ENCRYPTED_PASSWORD(classpath:PassKeyJohnDoe.properties,classpath:EncPassJohnDoe.properties)

If the resource name begins with a prefix other than "classpath:", then the Teradata JDBC Driver loads the resource via the new URL(resourcename).openStream() method. Non-classpath resources are not required to have specific resource name prefixes. You must ensure that non-classpath resources are accessible by the Teradata JDBC Driver.

The resource name can begin with the "file:" prefix and specify a relative pathname for the Teradata JDBC Driver to load the resource from a relative-pathname file.

Example with files in current directory:

   ENCRYPTED_PASSWORD(file:JohnDoeKey.properties,file:JohnDoePass.properties)

Example with relative paths:

   ENCRYPTED_PASSWORD(file:../dir1/JohnDoeKey.properties,file:../dir2/JohnDoePass.properties)

The resource name can begin with the "file:" prefix and specify an absolute pathname for the Teradata JDBC Driver to load the resource from an absolute-pathname file.

Example with absolute paths on Windows:

   ENCRYPTED_PASSWORD(file:c:/dir1/JohnDoeKey.properties,file:c:/dir2/JohnDoePass.properties)

Example with absolute paths on Linux:

   ENCRYPTED_PASSWORD(file:/dir1/JohnDoeKey.properties,file:/dir2/JohnDoePass.properties)

Teradata JDBC Driver Actions

The two resource names specified for an encrypted password must be accessible to the Teradata JDBC Driver and must conform to the properties file formats described above. The Teradata JDBC Driver throws SQLException if the resource name begins with the "classpath:" prefix, but the resource is not available on the classpath. The Teradata JDBC Driver will also throw SQLException if a non-classpath resource is not accessible. The Teradata JDBC Driver throws SQLException if the resources do not conform to the required properties file formats.

The Teradata JDBC Driver verifies that the match values in the two resources are present, and match each other. The Teradata JDBC Driver throws SQLException if the match values differ from each other. The match values are compared to ensure that the two specified resources are related to each other, serving as a "sanity check" to help avoid configuration errors. The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password.

Before decryption, the Teradata JDBC Driver calculates the MAC using the ciphertext, transformation name, and algorithm parameters if any, and verifies that the calculated MAC matches the expected MAC. The Teradata JDBC Driver throws SQLException if the calculated MAC differs from the expected MAC, to indicate that either or both of the resources may have been tampered with.

For a logon password, the Teradata JDBC Driver uses the decrypted password string to log on to the Teradata Database. For a new password, the Teradata JDBC Driver uses the decrypted password string with the MODIFY USER command to update an expired password.

Client System Information

When the Teradata JDBC Driver establishes a connection to the Teradata Database, the Teradata JDBC Driver transmits information about the client system and client software to the Teradata Database. The Teradata Database records this information in Data Dictionary system tables, to enable analysis of client system demographics by database administrators. The following sections describe the Client Attributes feature and the LogonSource column.

Client Attributes

Beginning with Teradata Database 14.0 and Teradata JDBC Driver 13.10.00.21, the Client Attributes feature records a variety of information about the client system and client software in the system tables DBC.SessionTbl and DBC.EventLog. The Client Attributes feature is intended to be a replacement for the information recorded in the LogonSource column of the system tables DBC.SessionTbl and DBC.EventLog.

The Client Attributes are recorded at session logon time. Subsequently, the system views DBC.SessionInfoV and DBC.LogOnOffV can be queried to obtain information about the client system and client software on a per-session basis. Client Attribute values may be recorded in the database in either mixed-case or in uppercase, depending on the session character set and other factors. Analysis of recorded Client Attributes must flexibly accommodate either mixed-case or uppercase values.

Warning:  

The information in this section is subject to change in future releases of the Teradata JDBC Driver. Client Attributes can be "mined" for information about client system demographics; however, any applications that parse Client Attribute values will have to be changed if Client Attribute formats are changed in the future.

Client Attributes are not intended to be used for workload management. Instead, query bands are intended for workload management. Any use of Client Attributes for workload management may break if Client Attributes are changed, or augmented, in the future.

Client Attribute

Source

Description

MechanismName

Teradata Database

The connection's logon mechanism; for example, TD2, LDAP, etc.

ClientIpAddress

Teradata Database

The client IP address, as determined by the Teradata Database

ClientTcpPortNumber

Teradata Database

The connection's client TCP port number, as determined by the Teradata Database

ClientIPAddrByClient

Teradata JDBC Driver

The client IP address, as determined by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33.

ClientPortByClient

Teradata JDBC Driver

The connection's client TCP port number, as determined by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33.

ClientJDBCDriverVersion

Teradata JDBC Driver

The Teradata JDBC Driver version number. This Client Attribute is available in the DBC.LogOnOffV view on Teradata Database systems with the fix for DBS DR 174576.

ClientProgramName

Teradata JDBC Driver

The class name, method name, and source line (if available) of the current thread's last stack element at the time the connection is established

ClientSystemUserId

Teradata JDBC Driver

The Java system property user.name

ClientOsName

Teradata JDBC Driver

The Java system properties os.name, os.version, and os.arch, concatenated together and separated by spaces

ClientProcThreadId

Teradata JDBC Driver

The JVM process ID as reported by the java.lang.management.RuntimeMXBean.getName method. This Client Attribute is available on JDK 5.0 and later.

ClientJavaVersion

Teradata JDBC Driver

The Java system property java.version

ClientVmName

Teradata JDBC Driver

The Java system properties java.vm.vendor, java.vm.name, and java.vm.version, concatenated together and separated by spaces

ClientTdHostName

Teradata JDBC Driver

Beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33, this Client Attribute records the Teradata Database hostname as specified by the application, without any COP suffix.
Prior to Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33, this Client Attribute records the Teradata Database hostname as specified by the application, followed by a semicolon, followed by the COP-suffixed hostname and/or IP address of the Teradata Database node, followed by a colon, followed by the destination port number of the TCP connection to the Teradata Database node.

ClientCOPSuffixedHostName

Teradata JDBC Driver

The COP-suffixed Teradata Database hostname chosen by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33. Beginning with Teradata JDBC Driver 15.10.00.19, this Client Attribute is not available when a literal IP address is specified for the Teradata Database, instead of a hostname.

ServerIPAddrByClient

Teradata JDBC Driver

The Teradata Database node's IP address, as determined by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33.

ServerPortByClient

Teradata JDBC Driver

The destination port number of the TCP connection to the Teradata Database node, as determined by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33.

ClientAttributesEx

Teradata JDBC Driver

Additional Client Attributes are available in this column as a list of name=value pairs, each terminated by a semicolon. Individual values can be accessed using the NVP system function.

  • JAVA — the Java system property java.version
  • MEM — the JVM's maximum available memory
  • TZ — the JVM default time zone
  • CID — the connection ID (the hash code of a private object used by the connection)
  • TYPE — the TYPE connection parameter
  • GOV — Y/N indicator whether the connection is governed by workload management (applies to JDBC FastLoad and JDBC FastExport)
  • SCS — the session character set
  • CCS — the connection's Java character set
  • LOB — Y/N indicator for LOB support
  • SIP — Y/N indicator for StatementInfo parcel support
  • TM — transaction mode indicator A (ANSI) or T (TERA). Client Attribute available beginning with Teradata JDBC Driver 14.00.00.33.
  • ENC — Y/N indicator for data encryption. Client Attribute available beginning with Teradata JDBC Driver 14.00.00.33.
  • SE — Y/N indicator for strict encoding of character data. Client Attribute available beginning with Teradata JDBC Driver 14.10.00.18.
  • RED — Redrive and Recoverable Network Protocol information. Client Attribute available beginning with Teradata JDBC Driver 14.10.00.23.

LogonSource Column

Beginning with Teradata Database 14.0, the LogonSource column is considered obsolete and has been superseded by the Client Attributes feature. The LogonSource column may be deprecated and subsequently removed in future releases of the Teradata Database.

When the Teradata JDBC Driver establishes a connection to the Teradata Database, the Teradata JDBC Driver composes a string value that is stored in the LogonSource column of the system tables DBC.SessionTbl and DBC.EventLog. The LogonSource column is included in system views such as DBC.SessionInfoV and DBC.LogOnOffV. All LogonSource values provided by Teradata JDBC Driver and other clients are recorded in the database in uppercase.

The Teradata JDBC Driver follows the format documented in the Teradata Data Dictionary, section "System Views Columns Reference", for network-attached LogonSource values. Network-attached LogonSource values have eight fields, separated by whitespace. The Teradata Database composes fields 1 through 3; the Teradata JDBC Driver composes fields 4 through 8.

  1. The literal "(TCP/IP)", to indicate the connection type
  2. TCP port number on the client system, in hexadecimal
  3. IP address of the client system
  4. Teradata Database hostname, known as the Teradata Directory Program Identifier "TDPID"
  5. Client process/thread identifier
  6. Client system user ID
  7. Program used on the client system
  8. The literal "01 LSS", to indicate the LogonSource string version 01

Fields 4 through 8 are described in detail in the following sections.

Warning:  

The information in this section is subject to change in future releases of the Teradata JDBC Driver. Teradata strongly recommends that applications do not parse LogonSource values. Any applications that parse LogonSource values will have to be changed if the LogonSource format is changed in the future.

All client information refers to the system running JVM containing the Teradata JDBC Driver. Typically, this is an application server. Client information does not refer to any other clients, such as a web browser, that may be communicating with the application server.

Example LogonSource value

                                                                                                   1         1         1

         1         2         3         4         5         6         7         8         9         0         1         2

12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678

<--set by Teradata Database--><-------------------------------------------set by client---------------------------------------->

(TCP/IP) 11AB 153.64.135.140  CHARON;CHARONCOP1/192.168.16.144:1025     CID=C2A132   ROOTUSER JDBC03.02.00.00;1.4.2_01   01 LSS

(TCP/IP) 11AB 153.64.135.140  CHARON.SD.TERADATA.COM;CHARONCOP1.SD.TERA CID=C2A132   ROOTUSER JDBC03.02.00.00;1.4.2_01   01 LSS

----------------------------- ----------------------------------------- ------------ -------- -------------------------- ------

                                       1         2         3         4           1                     1         2

                              12345678901234567890123456789012345678901 123456789012 12345678 12345678901234567890123456 123456

                                       Truncated to the space            Will be     Trunc'ed    Trunc'ed to 26 chars    Always

                                  remaining in the 97 chars, after       12 chars   to 20 chars      (may be less)       6 chars

                                 the subsequent fields are composed      or less   (may be less)

Fields are separated from each other by exactly one space character.

Field 4 - TDPID (target Teradata Database hostname) Field

The TDPID field is composed of:

This TPID field is truncated to the space remaining in the 97 chars, after all the other fields are composed.

An example value for this field when an application specifies the Teradata Database hostname "CHARON" is: CHARON;CHARONCOP1/192.168.16.144:1025

An example truncated value for this field when an application specifies a fully-qualified Teradata Database hostname of "CHARON.SD.TERADATA.COM" is: CHARON.SD.TERADATA.COM;CHARONCOP1.SD.TERA

Field 5 - Client Process ID/Thread ID Field

Note:  The Teradata JDBC Driver does not provide the Java Thread ID for this field.

In an application server environment, threads are not tied to particular database connections, so any particular thread can execute requests on a connection originally created by a different thread.

To avoid potential confusion, the Teradata JDBC Driver provides a connection ID for field 5 containing the LogonSource value. The connection ID is also provided in exception and log messages, which enable connection ID values to be correlated between LogonSource values and exception and log messages. The Teradata JDBC Driver always prefixes connection ID values with the prefix "cid=", to make it easy to distinguish connection ID values from other values.

The connection ID is the hash code of a private object used by the connection. It provides a simple unique identifier for a particular connection to the Teradata Database.

The Client Process/Thread ID field is composed of:

An example value for this field is: CID=1E51060

Field 6 - Client Process User Field

The Client Process User field is composed of System.getProperty("user.name")

This field is truncated to 20 chars, but can be shorter.

An example value for this field is: ROOTUSER

Field 7 - Client Program Name Field

The Client Program Name field is composed of:

This field is truncated to 26 chars, but can be shorter.

An example value for this field is: JDBC03.02.00.00;1.4.2_04

Field 8 - LogonSource string version 1

This field is composed of 01 LSS.

User STARTUP SQL Request

Teradata Database CREATE USER and MODIFY USER commands provide STARTUP clauses for specifying certain initial session settings. In addition, the Teradata JDBC Driver provides connection parameters and corresponding DataSource properties for specifying certain initial session settings.

Some session settings are specified only by executing an SQL DDL session command. The following table lists many of the session settings, and indicates which initial settings are specified with a CREATE/MODIFY USER clause or a JDBC Connection parameter. The complete list of SET SESSION commands is available in the Teradata Database Reference / SQL Data Definition Language Syntax and Examples.

Session Setting

SQL DDL Session Command

CREATE/MODIFY USER Clause

JDBC Connection Parameter

Only Available as Session Command

Volatile tables

CREATE VOLATILE TABLE

-

-

Yes

Current database

DATABASE

DEFAULT DATABASE

DATABASE

No

Diagnostic settings

DIAGNOSTIC ... FOR SESSION

-

-

Yes

Session query band

SET QUERY_BAND ... FOR SESSION

-

-

Yes

Current role

SET ROLE

DEFAULT ROLE

-

No

Current account

SET SESSION ACCOUNT ... FOR SESSION

ACCOUNT

ACCOUNT

No

Unicode Pass Through

SET SESSION CHARACTER SET UNICODE PASS THROUGH ON

-

-

Yes

Current transaction isolation

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL

-

-

Yes

Collation sequence

SET SESSION COLLATION

COLLATION

-

No

Temporal qualifier

SET SESSION CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME

-

-

Yes

Current database

SET SESSION DATABASE

DEFAULT DATABASE

DATABASE

No

Date format

SET SESSION DATEFORM

DATEFORM

-

No

Function tracing

SET SESSION FUNCTION TRACE

-

-

Yes

Replication services control

SET SESSION OVERRIDE REPLICATION

-

-

Yes

Current time zone

SET TIME ZONE

TIME ZONE

-

No

Unicode Pass Through support is available beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.08.

The user's STARTUP SQL request can be used to establish initial session settings that cannot be set with a CREATE/MODIFY USER clause or a JDBC Connection parameter. For example, the following command sets a STARTUP SQL request for user "susan" to change the transaction isolation to read-uncommitted.

MODIFY USER susan AS STARTUP='SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL RU'

The Teradata JDBC Driver RUNSTARTUP=ON connection parameter must be specified to execute the user's STARTUP SQL request after logon. The default behavior is RUNSTARTUP=OFF. If the RUNSTARTUP connection parameter is omitted, then the user's STARTUP SQL request will not be executed.

Teradata Session Reconnect

A Java application uses a JDBC java.sql.Connection object to interact with a Teradata Database session. The JDBC connection can be disconnected from the Teradata Database session in various ways outside the control of the Teradata JDBC Driver, such as:

Teradata Session Reconnect is available beginning with Teradata JDBC Driver 13.10.00.24. When Teradata Session Reconnect is enabled, the Teradata JDBC Driver will attempt to reconnect the JDBC connection to the Teradata Database session after a communication failure. Teradata Session Reconnect is enabled when one or more of the following conditions is satisfied:

The maximum possible elapsed time for reconnect attempts is:

    (ReconnectCount - 1) * ReconnectInterval

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

Recoverable Network Protocol is enabled or disabled through a combination of Teradata Database and Teradata JDBC Driver configuration parameters; specifically, the Teradata Database dbscontrol fields RedriveProtection (67), RedriveDefaultParticipation (68), and DisableRecoverableNetProtocol (77), and the Teradata JDBC Driver connection parameter REDRIVE with level 2 or higher.

When Teradata Session Reconnect is disabled, and a communication failure occurs, the operation in progress fails, the Teradata JDBC Driver closes the JDBC connection and throws a SQLException with SQLState 08S01.

When Teradata Session Reconnect is enabled, but Recoverable Network Protocol is not in effect, and a communication failure occurs, the operation in progress fails, the Teradata JDBC Driver attempts to reconnect and throws a SQLException with one of the following error codes to indicate the outcome of the reconnect attempt:

Reconnect is never attempted if a communication failure occurs while a JDBC connection is being closed.

The Teradata Database enforces a limited time period for reconnecting to a session after a Teradata Database restart. The amount of time is set using the Teradata Database utility program "gtwcontrol". The standard value is 20 minutes. The Teradata Database will reject all reconnect attempts after the time period expires.

When Teradata Session Reconnect is enabled, but Recoverable Network Protocol is not in effect, a significant part of each session's state is discarded when a Teradata Database restart occurs.

Applications that use Teradata Session Reconnect without Recoverable Network Protocol must be designed to accommodate the possible loss of session state at any point in time. Teradata Session Reconnect without Recoverable Network Protocol is not recommended for use with JDBC connection pools.

When Teradata Session Reconnect, Recoverable Network Protocol, and automatic Redrive of SQL requests are all in effect, and a communication failure occurs, the operation in progress fails, but is redriven automatically, the session's state is preserved, the Teradata JDBC Driver attempts to reconnect and does not throw an exception if the reconnect was successful.

Transaction Mode

The TMODE connection parameter enables an application to specify the transaction mode for the connection.

While ANSI mode is generally recommended, please note that every application is different, and some applications may need to use TERA mode. The following differences between ANSI and TERA mode might affect a typical user or application:

  1. Silent truncation of inserted data occurs in TERA mode, but not ANSI mode. In ANSI mode, the Teradata Database returns an error instead of truncating data.
  2. Tables created in ANSI mode are MULTISET by default. Tables created in TERA mode are SET tables by default.
  3. For tables created in ANSI mode, character columns are CASESPECIFIC by default. For tables created in TERA mode, character columns are NOT CASESPECIFIC by default.
  4. In ANSI mode, character literals are CASESPECIFIC. In TERA mode, character literals are NOT CASESPECIFIC.

The last two behavior differences, taken together, may cause character data comparisons (such as in WHERE clause conditions) to be case-insensitive in TERA mode, but case-sensitive in ANSI mode. This, in turn, can produce different query results in ANSI mode versus TERA mode. Comparing two NOT CASESPECIFIC expressions is case-insensitive regardless of mode, and comparing a CASESPECIFIC expression to another expression of any kind is case-sensitive regardless of mode. You may explicitly CAST an expression to be CASESPECIFIC or NOT CASESPECIFIC to obtain the character data comparison required by your application.

The Teradata Database Reference / SQL Request and Transaction Processing recommends that ANSI mode be used for all new applications. The primary benefit of using ANSI mode is that inadvertent data truncation is avoided. In contrast, when using TERA mode, silent data truncation can occur when data is inserted, because silent data truncation is a feature of TERA mode.

A drawback of using ANSI mode is that you can only call stored procedures that were created using ANSI mode, and you cannot call stored procedures that were created using TERA mode. It may not be possible to switch over to ANSI mode exclusively, because you may have some legacy applications that require TERA 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.

Refer to the Teradata Database Reference / SQL Request and Transaction Processing for complete information regarding the differences between ANSI and TERA transaction modes.

Auto-Commit

Auto-commit is a basic feature of the JDBC API Specification, and the Teradata JDBC Driver appropriately implements auto-commit on and off functionality for both ANSI and TERA mode.

When a connection is first established, it begins with the default JDBC auto-commit setting, which is on (true). When auto-commit is on, the JDBC Driver is solely responsible for managing transactions, and the JDBC Driver commits each SQL request that is successfully executed. An application should not execute any transaction management SQL commands when auto-commit is on. An application should not call the Connection.commit method or the Connection.rollback method when auto-commit is on.

An application can manage transactions itself by calling the Connection.setAutoCommit method with a false argument to turn off auto-commit. When auto-commit is off, the JDBC Driver leaves the current transaction open after each SQL request is executed, and the application is responsible for committing or rolling back the transaction by calling the Connection.commit or the Connection.rollback method, respectively.

Best practices recommend that an application avoid executing database-vendor-specific transaction management commands such as BT, ET, ABORT, COMMIT, or ROLLBACK, because those kind of commands differ from one vendor to another. (They even differ between Teradata's two modes ANSI and TERA.) Instead, best practices recommend that an application only call the standard JDBC API methods Connection.setAutoCommit, Connection.commit and Connection.rollback for transaction management.

  1. When auto-commit is on in ANSI mode, the Teradata JDBC Driver automatically executes COMMIT WORK after every successful SQL request.
  2. When auto-commit is off in ANSI mode, the Teradata JDBC Driver does not automatically execute COMMIT WORK. When the application calls the Connection.commit method, then the Teradata JDBC Driver executes COMMIT WORK.
  3. When auto-commit is on in TERA mode, the Teradata JDBC Driver does not execute BT or ET, unless the application explicitly executes BT or ET commands itself, which is not recommended.
  4. When auto-commit is off in TERA mode, the Teradata JDBC Driver executes BT before submitting the application's first SQL request of a new transaction. When the application calls the Connection.commit method, then the Teradata JDBC Driver executes ET until the transaction is complete.

As part of the wire protocol between the Teradata Database and Teradata client interface software (such as the Teradata JDBC Driver), each message transmitted from the Teradata Database to the client has a bit designated to indicate whether the session has a transaction in progress or not. Thus, the client interface software is kept informed as to whether the session has a transaction in progress or not.

In TERA mode with auto-commit off, when the application uses the Teradata JDBC Driver to execute a SQL request, if the session does not have a transaction in progress, then the Teradata JDBC Driver automatically executes BT before executing the application's SQL request. Subsequently, in TERA mode with auto-commit off, when the application uses the Teradata JDBC Driver to execute another SQL request, and the session already has a transaction in progress, then the Teradata JDBC Driver has no need to execute BT before executing the application's SQL request.

In TERA mode, BT and ET pairs can be nested, and the Teradata Database keeps track of the nesting level. The outermost BT/ET pair defines the transaction scope; inner BT/ET pairs have no effect on the transaction because the Teradata Database does not provide actual transaction nesting. To commit the transaction, ET commands must be repeatedly executed until the nesting is unwound. The Teradata wire protocol bit (mentioned earlier) indicates when the nesting is unwound and the transaction is complete. When the application calls the Connection.commit method in TERA mode, the Teradata JDBC Driver repeatedly executes ET commands until the nesting is unwound and the transaction is complete.

In rare cases, an application may not follow best practices and may explicitly execute transaction management commands. Such an application must turn off auto-commit before executing transaction management commands such as BT, ET, ABORT, COMMIT, or ROLLBACK. The application is responsible for executing the appropriate commands for the transaction mode in effect. TERA mode commands are BT, ET, and ABORT. ANSI mode commands are COMMIT and ROLLBACK. An application must take special care when opening a transaction in TERA mode with auto-commit off. In TERA mode with auto-commit off, when the application executes a SQL request, if the session does not have a transaction in progress, then the Teradata JDBC Driver automatically executes BT before executing the application's SQL request. Therefore, the application should not begin a transaction by executing BT.

// TERA mode example showing undesirable BT/ET nesting

con.setAutoCommit(false);

stmt.execute("BT"); // BT automatically executed by the JDBC Driver before this, and produces a nested BT

stmt.execute("insert into mytable1 values(1, 2)");

stmt.execute("insert into mytable2 values(3, 4)");

stmt.execute("ET"); // unwind nesting

stmt.execute("ET"); // complete transaction

// TERA mode example showing how to avoid BT/ET nesting

con.setAutoCommit(false);

stmt.execute("insert into mytable1 values(1, 2)"); // BT automatically executed by the JDBC Driver before this

stmt.execute("insert into mytable2 values(3, 4)");

stmt.execute("ET"); // complete transaction

Please note that neither previous example shows best practices. Best practices recommend that an application only call the standard JDBC API methods Connection.setAutoCommit, Connection.commit and Connection.rollback for transaction management.

// Example showing best practice

con.setAutoCommit(false);

stmt.execute("insert into mytable1 values(1, 2)");

stmt.execute("insert into mytable2 values(3, 4)");

con.commit();

SQL Literals and SQL Injection Attacks

Applications that compose SQL requests containing SQL string literals must take care to properly escape single-quote characters. An SQL string literal is enclosed by single-quotes, for example: 'New York'. To use a single-quote character in an SQL string literal, the single-quote character must be repeated, for example: 'Joe"s Diner'.

Applications that compose SQL requests based on user input, such that the user input is directly substituted into the SQL text string, may be vulnerable to an SQL Injection attack.

For example, an application prompts the user to enter a person's last name, and then composes a query to search for all people with that last name:

String sql = "SELECT * FROM customer WHERE lastname = '" + lastname + "'"

In this example, notice that the hardcoded part of the SQL text includes the single‑quote characters to be used on each side of the SQL string literal. This technique works only if the user's input value never includes any single quotes.

However, if the user specifies "O'Malley" as a last name, then the application will erroneously compose the following query, which is then rejected by the database as a syntax error:

SELECT * FROM customer WHERE lastname = 'O'Malley'

An SQL Injection attack takes advantage of this kind of defect in the application code to do something malicious. For example, the malicious input value would be:

 x';delete from important_table;select 'x

which would cause the application to compose the SQL text string:

SELECT * FROM customer WHERE lastname = 'x';delete from important_table;select 'x'

In this example, the malicious user carefully chose a value that works with the hardcoded single‑quote characters on each side of the input value. The database would successfully execute the SQL string and, assuming that the malicious user had write-access to the important table, would delete all the rows from the important table, with a result being a denial of service.

Applications must be coded to handle single-quote characters, and to correctly compose SQL requests.

The most common recommendation to protect against SQL Injection attack is to use prepared statements so that users cannot modify the SQL text; the user's input values are only used as bind parameter values for prepared statements.

If it is not possible for the application to use prepared statements, such as for composing Data Definition Language (DDL) commands, then another common recommendation is to validate and escape the user's input values. For example:

JDBC Escape Clauses

When JDBC escape clause processing is enabled, the Teradata JDBC Driver looks for any escape syntax and translates it into native SQL syntax for the Teradata Database. This makes escape syntax independent of any database.

The default for JDBC escape clause processing is ENABLED. Escape clause processing can be disabled for the Classes Statement and RowSet by calling the methods:

The following is the generic syntax for escape clauses:

{keyword parameters...}

Teradata JDBC Driver supports the following types of escape clauses:

Date and Time Literals

Literal Type

Format

Notes

Date

{d 'yyyy-mm-dd'}

 

Time

{t 'hh:mm:ss'}

 

Timestamp

{ts 'yyyy-mm-dd hh:mm:ss'}
{ts 'yyyy-mm-dd hh:mm:ss.f'}

The decimal point and fractional digits may be omitted, or 1 to 6 fractional digits f may be specified after a decimal point

Scalar Functions

The following tables list the JDBC Escape Syntax scalar functions that are supported by the Teradata JDBC Driver.

Numeric Function

Returns

Notes

{fn ABS(number)}

Absolute value of number

 

{fn ACOS(float)}

Arccosine, in radians, of float

 

{fn ASIN(float)}

Arcsine, in radians, of float

 

{fn ATAN(float)}

Arctangent, in radians, of float

 

{fn ATAN2(y, x)}

Arctangent, in radians, of y / x

The parameter order differs from the JDBC API Specification

{fn CEILING(number)}

Smallest integer greater than or equal to number

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn COS(float)}

Cosine of float radians

 

{fn COT(float)}

Cotangent of float radians

Available beginning with Teradata JDBC Driver 15.00.00.22

{fn DEGREES(number)}

Degrees in number radians

Available beginning with Teradata JDBC Driver 15.00.00.22

{fn EXP(float)}

e raised to the power of float

 

{fn FLOOR(number)}

Largest integer less than or equal to number

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn LOG(float)}

Natural (base e) logarithm of float

 

{fn LOG10(float)}

Base 10 logarithm of float

Available beginning with Teradata JDBC Driver 15.00.00.22

{fn MOD(integer1, integer2)}

Remainder for integer1 / integer2

 

{fn PI()}

The constant pi, approximately equal to 3.14159...

 

{fn POWER(number, integer)}

number raised to integer power

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn RADIANS(number)}

Radians in number degrees

Available beginning with Teradata JDBC Driver 15.00.00.22

{fn RAND(seed)}

A random float value such that 0 ≤ value < 1

The seed is ignored. Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn ROUND(number, places)}

number rounded to places

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn SIGN(number)}

-1 if number is negative; 0 if number is 0; 1 if number is positive

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn SIN(float)}

Sine of float radians

 

{fn SQRT(float)}

Square root of float

 

{fn TAN(float)}

Tangent of float radians

 

{fn TRUNCATE(number, places)}

number truncated to places

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

String Function

Returns

Notes

{fn ASCII(string)}

ASCII code of the first character in string

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn CHAR(code)}

Character with ASCII code

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn CHAR_LENGTH(string)}

Length in characters of string

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn CHARACTER_LENGTH(string)}

Length in characters of string

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn CONCAT(string1, string2)}

String formed by concatenating string1 and string2

 

{fn DIFFERENCE(string1, string2)}

A number from 0 to 4 that indicates the phonetic similarity of string1 and string2 based on their Soundex codes

A larger return value indicates greater phonetic similarity; 0 indicates no similarity, 4 indicates strong similarity. Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn INSERT(string1, position, length, string2)}

String formed by replacing the length-character segment of string1 at position with string2

Available beginning with Teradata Database 15.0 and Teradata JDBC Driver 15.00.00.22

{fn LCASE(string)}

String formed by replacing all uppercase characters in string with their lowercase equivalents

 

{fn LEFT(string, count)}

Leftmost count characters of string

Available beginning with Teradata JDBC Driver 15.00.00.22

{fn LENGTH(string)}

Length in characters of string

 

{fn LOCATE(string1, string2)}

Position in string2 of the first occurrence of string1

Returns 0 if string2 does not contain string1

{fn LTRIM(string)}

String formed by removing leading spaces from string

 

{fn OCTET_LENGTH(string)}

Length in octets of string

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn POSITION(string1 IN string2)}

Position in string2 of the first occurrence of string1

Returns 0 if string2 does not contain string1. Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn REPEAT(string, count)}

String formed by repeating string count times

Available beginning with Teradata Database 15.0 and Teradata JDBC Driver 15.00.00.22

{fn REPLACE(string1, string2, string3)}

String formed by replacing all occurrences of string2 in string1 with string3

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn RIGHT(string, count)}

Rightmost count characters of string

Available beginning with Teradata Database 15.0 and Teradata JDBC Driver 15.00.00.22

{fn RTRIM(string)}

String formed by removing trailing spaces from string

 

{fn SOUNDEX(string)}

Soundex code for string

 

{fn SPACE(count)}

String consisting of count spaces

Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22

{fn SUBSTRING(string, position, length)}

The length-character segment of string at position

 

{fn UCASE(string)}

String formed by replacing all lowercase characters in string with their uppercase equivalents

 

System Function

Returns

Notes

{fn DATABASE()}

Current default database name

 

{fn IFNULL(expression, value)}

expression if expression is not NULL, or value if expression is NULL

 

{fn USER()}

Logon user name

The current authorized user name may differ from the logon user name after SET QUERY_BAND sets a proxy user

Time/Date Function

Returns

Notes

{fn CURDATE()}

Current date

 

{fn CURRENT_DATE()}

Current date

Available beginning with Teradata JDBC Driver 15.00.00.22

{fn CURRENT_TIME()}

Current time

Available beginning with Teradata JDBC Driver 15.00.00.22

{fn CURRENT_TIMESTAMP()}

Current date and time

Available beginning with Teradata JDBC Driver 15.00.00.22

{fn CURTIME()}

Current time

 

{fn DAYOFMONTH(date)}

An integer from 1 to 31 indicating the day of month in date

 

{fn EXTRACT(field FROM value)}

The field component of the date and/or time value

field may be one of the following:

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND

Available beginning with Teradata JDBC Driver 15.00.00.22

{fn HOUR(time)}

An integer from 0 to 23 indicating the hour of time

 

{fn MINUTE(time)}

An integer from 0 to 59 indicating the minute of time

 

{fn MONTH(date)}

An integer from 1 to 12 indicating the month of date

 

{fn NOW()}

Current date and time

 

{fn SECOND(time)}

An integer from 0 to 59 indicating the second of time

 

{fn TIMESTAMPADD(interval, count, timestamp)}

Timestamp formed by adding count interval(s) to timestamp

interval must be one of the following:

  • SQL_TSI_YEAR
  • SQL_TSI_MONTH
  • SQL_TSI_DAY
  • SQL_TSI_HOUR
  • SQL_TSI_MINUTE
  • SQL_TSI_SECOND

{fn TIMESTAMPDIFF(interval, timestamp1, timestamp2)}

The number of interval(s) by which timestamp2 exceeds timestamp1

interval must be one of the following:

  • SQL_TSI_YEAR
  • SQL_TSI_MONTH
  • SQL_TSI_DAY
  • SQL_TSI_HOUR
  • SQL_TSI_MINUTE
  • SQL_TSI_SECOND

{fn YEAR(date)}

The year of date

 

Conversion Functions

Escape clauses for data type conversion use the following syntax:

{fn CONVERT(value, SQLtype)}

where SQLtype is one of the data types listed in the following table.

Conversion Functions

DATE

DECIMAL

DOUBLE

INTEGER

FLOAT

LONGVARCHAR

SMALLINT

TIME

TIMESTAMP

TINYINT

VARBINARY

VARCHAR

CHAR

BINARY

LIKE Predicate Escape Characters

Teradata JDBC Driver supports ESCAPE characters for LIKE SQL statements with the following syntax:

{escape 'escape-character'}

This escape clause specifies the escape character so wildcards such as %, _ can be interpreted literally in an SQL LIKE statement.

Outer Joins

The Teradata JDBC Driver supports escape clauses for outer joins. The following is the syntax for an outer join:

{oj outer-join}

In this language rule, outer-join structure is:

table {LEFT|RIGHT|FULL} OUTER JOIN {table | outer-join}

ON search-condition

Calls to Stored Procedures

The escape clause syntax for a call to a stored procedure is the following:

{call procedure_name(?, ?, . . .)}

The following are true in Escape clauses:

Effective with Teradata Tools and Utilities 08.01.00 and the Teradata JDBC Driver Release 03.03.00, parentheses are no longer added to an SQL CALL statement that doesn't use JDBC escape syntax. This behavior change allows an application's SQL statements to be passed to the Teradata Database unmodified when JDBC escape syntax is not used.

It is strongly recommended that applications always use standard vendor-independent JDBC Escape Syntax to call stored procedures. When JDBC Escape Syntax is used to call a stored procedure, as in:

{call storedproc}

then the Teradata JDBC Driver modifies the SQL statement as needed to satisfy the Teradata Database's precise syntax requirements. JDBC Escape Syntax functionality for calling stored procedures has not changed with this release of the Teradata JDBC Driver.

Applications that inadvertently relied on the behavior of previous Teradata JDBC Driver releases to modify SQL CALL statements that did not use JDBC Escape Syntax may need to be modified either:

Connection Functions

The following table lists the JDBC Escape Syntax functions that are intended for use with the Connection.nativeSQL method. These functions provide information about the connection, or control the behavior of the connection.

Connection Function

Returns

Notes

{fn teradata_amp_count}

The number of AMPs of the Teradata Database system

Returns locally-cached information and avoids a round-trip to the database.

{fn teradata_getloglevel}

Current log level

Returns one of the values for the LOG connection parameter.

{fn teradata_logon_sequence_number}

Session's Logon Sequence Number, if available

Returns locally-cached information and avoids a round-trip to the database.

{fn teradata_provide(host_id)}

Session's host ID

Returns locally-cached information and avoids a round-trip to the database.

Returns the same value as select LogicalHostId from DBC.SessionInfoV where Session = SessionNo

Available beginning with Teradata JDBC Driver 16.00.00.03.

{fn teradata_provide(java_charset_name)}

Java charset name specified by the CLIENT_CHARSET connection parameter, or the Java charset name that corresponds to the session character set if the CLIENT_CHARSET connection parameter is omitted.

Available beginning with Teradata JDBC Driver 16.00.00.03.

{fn teradata_provide(session_charset_code)}

Session character set code

Available beginning with Teradata JDBC Driver 16.00.00.03.

{fn teradata_provide(session_charset_name)}

Session character set name

Available beginning with Teradata JDBC Driver 16.00.00.03.

{fn teradata_provide(transaction_mode)}

Session's transaction mode, ANSI or TERA

Returns locally-cached information and avoids a round-trip to the database.

Available beginning with Teradata JDBC Driver 16.00.00.03.

{fn teradata_session_number}

Session number

Returns locally-cached information and avoids a round-trip to the database.

Returns the same value as select Session

{fn teradata_setloglevel(LogLevel)}

Empty string

Changes the connection's log level to the specified level.

LogLevel must be one of the values for the LOG connection parameter.

{fn teradata_socket_info}

A string of information about the TCP socket connection to the Teradata Database

The format of the returned information is subject to change. An application should not rely on the specific format of the infomation.

{fn teradata_useansidate}

Empty string

Switches to the default Y2K-compliant behavior for java.sql.Date values passed to the PreparedStatement/CallableStatement setDate or setObject methods, and transmitted to destination CHAR/VARCHAR columns and parameters.

{fn teradata_useintegerdate}

Empty string

Switches to non-Y2K-compliant behavior for java.sql.Date values passed to the PreparedStatement/CallableStatement setDate or setObject methods, and transmitted to destination CHAR/VARCHAR columns and parameters.

Request-Scope Functions

The following table lists the JDBC Escape Syntax functions that are intended for use with the Connection.createStatement, Connection.prepareStatement, or Connection.prepareCall method. These functions control the behavior of the corresponding Statement, PreparedStatement, or CallableStatement, and are limited in scope to the particular SQL request in which they are specified.

Request-Scope Function

Returns

Notes

{fn teradata_auto_out_param}

Empty string

Specifies that Statement and PreparedStatement will skip the validation of unset parameter markers, and automatically treat any unset parameter marker as an OUT parameter. This enables the use of Statement and PreparedStatement to CALL to a stored procedure with OUT parameters.

An exception is thrown if this function is used with a CallableStatement.

Available beginning with Teradata JDBC Driver 15.10.00.09.

{fn teradata_call_param_rs}

Empty string

Specifies that when Statement or PreparedStatement is used to execute a CALL to a stored procedure, the stored procedure's INOUT and OUT parameter output values will be returned as a result set.

An exception is thrown if this function is used with a CallableStatement.

Available beginning with Teradata JDBC Driver 15.10.00.09.

{fn teradata_failfast}

Empty string

Specifies that this SQL request should be rejected ("fail fast") instead of delayed by a workload management rule or throttle.

Available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.17.

{fn teradata_provide(request_scope_column_name_off)}

Empty string

Specifies that the ResultSetMetaData.getColumnName method should return the AS-clause name if available, or the column name if available, or the column title, and specifies that the ResultSetMetaData.getColumnLabel method should return the column title.

Takes priority over the COLUMN_NAME connection parameter for this SQL request.

Available beginning with Teradata JDBC Driver 16.00.00.28

{fn teradata_provide(request_scope_column_name_on)}

Empty string

Specifies that, when StatementInfo parcel support is available, the ResultSetMetaData.getColumnName method should return the the column name if available, and specifies that the ResultSetMetaData.getColumnLabel method should return the AS-clause name if available, or the column name if available, or the column title.

This function has no effect when StatementInfo parcel support is unavailable.

Takes priority over the COLUMN_NAME connection parameter for this SQL request.

Available beginning with Teradata JDBC Driver 16.00.00.28

{fn teradata_provide(request_scope_maybenull_on)}

Empty string

Specifies that, when StatementInfo parcel support is available, the return value of the ResultSetMetaData.isNullable method is determined from the StatementInfo parcel MayBeNull field provided by the Teradata Database.

This function has no effect when StatementInfo parcel support is unavailable.

Takes priority over the MAYBENULL connection parameter for this SQL request.

Available beginning with Teradata JDBC Driver 16.00.00.28

{fn teradata_untrusted}

Empty string

Marks the SQL request as untrusted. See TRUSTED SQL and QUERY BAND Impersonation for more information.

Query Banding

Teradata Database 12.0 introduced Query Banding. A query band is a set of name-value pairs that can be set on a session or a transaction to identify an SQL request's originating source.

Why is Query Banding Needed?

Use Case Scenario

In a web-based Java application, connection pools exist on the web servers. All the connections in the pools are for the same Teradata user; for example, appuser. This is commonly known as a robot user. The application uses browser cookies to identify the end-users who are using the web browsers.

Every time the user clicks a link on a web page, the user's web browser sends a request to the web server and transmits the browser cookie as part of the request. The web server invokes the application to process the request. The application does something; for example, submits a query to Teradata. Immediately before submitting the query to Teradata, the application first submits the following statements:

PreparedStatement pstmt = conn.prepareStatement("Set QUERY_BAND=? FOR TRANSACTION");

pstmt.setString (1, "custIdFromCookie=46734832");

pstmt.executeUpdate ();

Then, the application can submit its query:

SELECT col1, col2 FROM TableName WHERE condition1 = ? AND condition2 = ?

After the query returns a result set, the application outputs an HTML page to the web browser.

Use Case Scenario

Syntax

The query band is passed to the Teradata Database as a list of name=value pairs in a string. The application defines both the names and the values. A query band can be set for the transaction and/or for the session. Beginning with Teradata Database 15.10, a default query band can be set for a profile.

For more information on these SQL commands, refer to the Teradata Database SQL Data Definition Language reference.

The query band syntax rules are as follows:

Example

To set a query band value in a transaction using an SQL string literal with a non‑PreparedStatement:

stmt = conn.createStatement();

stmt.executeUpdate("SET QUERY_BAND='Org=Finance; report=EndOfYear; universe=west;' FOR TRANSACTION");

Example

To set a query band value in a transaction using a PreparedStatement:

pstmt=conn.prepareStatement("SET QUERY_BAND=? FOR TRANSACTION");

pstmt.setString (1, "Org = Finance; report = EndOfYear; universe=west;);

pstmt.executeUpdate ();

Example

To clear a query band value in a transaction:

stmt = conn.CreateStatement();

stmt.executeUpdate("SET QUERY_BAND = NONE for TRANSACTION");

Retrieving Query Band Values

Beginning with Teradata JDBC Driver 14.00.00.33, applications running in a JDK 6.0 or later environment can retrieve query band values with the following methods:

The first method returns a Properties object containing all active query band name/value pairs. The second method returns the query band value for the specified name.

If the same query band name is active for multiple contexts, only one of the corresponding query band values will be returned by these methods. Transaction query band values take precedence over session query band values, which take precedence over profile query band values. Profile query band values are supported beginning with Teradata Database 15.10 and Teradata JDBC Driver 15.00.00.23.

Recommended Query Band Names

The following are standard Client Info property names defined by the JDBC 4.0 specification:

For applications that need to use query band name-value pairs that correspond to the standard Client Info properties, Teradata recommends using standard Client Info property names as QueryBand names.

An application is not limited to using only standard Client Info properties. For QueryBand name-value pairs that do not correspond to the standard Client Info properties, the application is free to use any legal query band name that is supported by the Teradata Database.

Uses for the Query Band

Trusted SQL and Query Band Impersonation

Beginning with Teradata Database 13.10, Trusted Sessions Enhanced Security prevents the use of the SET QUERY_BAND SQL to set or remove the current proxy user. This is accomplished by categorizing all SQL requests as trusted or untrusted.

Applications that compose all their own SQL requests and trust all their own SQL requests, do not use this feature. This feature is used by applications working with both trusted and untrusted SQL requests. An example of an untrusted SQL request is a SQL request obtained from a user. An untrusted SQL request might contain an SQL Injection attempt by a malicious user.

Applications working with both trusted and untrusted SQL requests use this feature by following this process:

This escape function downgrades an SQL request from trusted to untrusted. No mechanism is provided to upgrade an SQL request from untrusted to trusted because it might be exploited by an SQL Injection attack.

Date, Time, Timestamp Values and Time Zones

java.sql.Date, Time, and Timestamp Objects

The following table describes the behavior of the valueOf and toString methods of the java.sql.Date, Time, and Timestamp classes.

Method

Action

valueOf

Constructs a value relative to the JVM default timezone.

toString

Prints the value relative to the JVM default timezone.

The Teradata JDBC Driver's PreparedStatement and CallableStatement setter methods setDate, setTime, and setTimestamp send to the Teradata Database the java.sql.Date, Time, or Timestamp value that matches what the java.sql.Date, Time, or Timestamp object's toString method would print at the time that the setter method is called, meaning the value is relative to the JVM default timezone at the time that the setter method is called.

Therefore, the application should ensure that the JVM default timezone in effect when the java.sql.Date, Time, or Timestamp object is created, is also in effect when application calls setDate, setTime, or setTimestamp.

Sending Date Values

Method

Without Calendar

With Calendar

setDate

Sends a SQL data type DATE value to the Teradata Database.

 

The DATE value matches what the java.sql.Date object's toString method would print at the time that the setter method is called, meaning the value is relative to the JVM default timezone at the time that the setter method is called.

Sends a SQL data type DATE value to the Teradata Database.

 

Teradata JDBC Driver versions 13.00.00.09 and later use the Calendar argument of setDate to convert the java.sql.Date value into UTC, before sending the DATE value to the Teradata Database. This provides the expected behavior when the destination column is a DATE, but can produce unexpected results when the destination column is a TIMESTAMP or TIMESTAMP WITH TIME ZONE.

 

With Teradata JDBC Driver versions prior to 13.00.00.09, the Calendar argument is ignored, and the method behaves exactly as setDate without Calendar.

Because the Teradata Database does not provide a DATE WITH TIME ZONE data type, it is recommended that applications call setDate without Calendar only, and avoid calling setDate with Calendar.

Sending Time Values

Method

Without Calendar

With Calendar

setTime

Sends a SQL data type TIME value to the Teradata Database.

 

The TIME value matches what the Time object's toString method would print at the time that the setter method is called, meaning the value is relative to the JVM default timezone at the time that the setter method is called.

Sends a SQL data type TIME WITH TIME ZONE to the Teradata Database.

 

The TIME portion matches what the Time object's toString method would print at the time that the setter method is called, meaning the value is relative to the JVM default timezone at the time that the setter method is called.

 

The Calendar argument's timezone is sent as a separate TIME ZONE field. The Teradata JDBC Driver accepts any Calendar argument from the application; the Teradata JDBC Driver cannot and does not restrict the Calendar argument's timezone to match the timezone that was in effect when the Time object was originally created.

Because Time values do not have an associated date, Daylight Savings Time is not applicable for Time values, and Daylight Savings Time is ignored for the Calendar argument's timezone. The TIME ZONE field is sent to the database as the time zone's raw offset from UTC, and is not affected by Daylight Savings Time.

Sending Timestamp Values

Method

Without Calendar

With Calendar

setTimestamp

Sends a SQL data type TIMESTAMP value to the Teradata Database.

 

The TIMESTAMP value matches what the Timestamp object's toString method would print at the time that the setter method is called, meaning the value is relative to the JVM default timezone at the time that the setter method is called.

Sends a SQL data type TIMESTAMP WITH TIME ZONE to the Teradata Database.

 

The TIMESTAMP portion matches what the Timestamp object's toString method would print at the time that the setter method is called, meaning the value is relative to the JVM default timezone at the time that the setter method is called.

 

The Calendar argument's timezone is sent as a separate TIME ZONE field. The Teradata JDBC Driver accepts any Calendar argument from the application; the Teradata JDBC Driver cannot and does not restrict the Calendar argument's timezone to match the timezone that was in effect when the Timestamp object was originally created.

Daylight Savings Time is not used for Timestamp values. The Teradata Database stores TIMESTAMP WITH TIME ZONE values as a raw offset from UTC. For consistency with the Teradata Database, the Teradata JDBC Driver ignores Daylight Savings Time for the Calendar argument's timezone. The TIME ZONE field is sent to the database as the time zone's raw offset from UTC, and is not affected by Daylight Savings Time.

An application that requires TIMESTAMP WITH TIME ZONE values to reflect Daylight Savings Time must specify a Calendar argument using a custom timezone.

For example, to indicate Eastern Standard Time, an application may specify a Calendar argument as follows:

prepstmt.setTimestamp(index, timestamp,
  Calendar.getInstance(TimeZone.getTimeZone("GMT-05:00")));

To indicate Eastern Daylight Time, an application may specify a Calendar argument as follows:

prepstmt.setTimestamp(index, timestamp,
  Calendar.getInstance(TimeZone.getTimeZone("GMT-04:00")));

Using setObject to Send Time With Time Zone and Timestamp With Time Zone Values

Beginning with Teradata JDBC Driver 14.10.00.26, an application can use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as a TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE data type. Prior to Teradata JDBC Driver 14.10.00.26, the setTime(Time, Calendar) method was required to bind a TIME WITH TIME ZONE value, and the setTimestamp(Timestamp, Calendar) method was required to bind a TIMESTAMP WITH TIME ZONE value. Those methods continue to work as before. Applications only need to use the new functionality for situations that the setTime/setTimestamp methods do not support; in particular, binding TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE attribute values of UDTs and Period Data Types.

The application must compose each data value as a java.sql.Struct value with two attributes. The first attribute of the Struct must be a java.sql.Time or a java.sql.Timestamp value. The second attribute of the Struct must be a Calendar value whose TimeZone attribute specifies the desired time zone. A Struct composed in this way is treated the same as the setTime/setTimestamp methods with Calendar argument. Refer the text above for a description of the behavior of the setTime/setTimestamp methods with Calendar argument.

The application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application uses instances of that class to compose TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE values.

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT+08:00")) ;

Time time = Time.valueOf( ...

Timestamp ts = Timestamp.valueOf( ...

// Assuming a table with two columns: TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE

PreparedStatement ps = con.prepareStatement ("INSERT INTO MyTable VALUES(?,?)") ;

ps.setObject(1, new MyStruct("TIME WITH TIME ZONE", new Object [] {time, cal})) ;

ps.setObject(2, new MyStruct("TIMESTAMP WITH TIME ZONE", new Object [] {ts, cal})) ;

Receiving DATE, TIME, and TIMESTAMP Values

Beginning with Teradata JDBC Driver 13.00.00.09, the ResultSet and CallableStatement interfaces' getDate, getTime, and getTimestamp methods with Calendar argument will modify the Calendar argument's TimeZone attribute to provide the time zone portion of TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE data values received from the Teradata Database.

Also beginning with Teradata JDBC Driver 13.00.00.09, the ResultSet and CallableStatement interfaces' getDate, getTime, and getTimestamp methods provide implicit data type conversions for DATE, TIME, and TIMESTAMP data values received from the Teradata Database.

While Teradata Database TIME values may contain up to 6 digits of fractional seconds, java.sql.Time values are limited to milliseconds precision - only 3 digits of fractional seconds. An application can work around the limitation of java.sql.Time values by retrieving Teradata Database TIME values using the getTimestamp method, which will provide an implicit data type conversion from TIME to java.sql.Timestamp, and will preserve all the digits of the TIME value's fractional seconds.

The following table describes the behavior of the ResultSet and CallableStatement interfaces' getDate, getTime, and getTimestamp methods.

Method

Receive DATE value

Receive TIME value

Receive TIME WITH TIME ZONE value

Receive TIMESTAMP value

Receive TIMESTAMP WITH TIME ZONE value

getDate without Calendar

The DATE value is converted to a java.sql.Date object such that the DATE value matches what the java.sql.Date object's toString method would print.

Not allowed.

Not allowed.

First, the TIMESTAMP value is converted to a DATE value by setting the time fields to 00:00:00.

 

Then, the DATE value is converted to a java.sql.Date object such that the DATE value matches what the java.sql.Date object's toString method would print.

First, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value by truncating the time zone field.

 

Next, the TIMESTAMP value is converted to a DATE value by setting the time fields to 00:00:00.

 

Finally, the DATE value is converted to a java.sql.Date object such that the DATE value matches what the java.sql.Date object's toString method would print.

getDate with Calendar

First, the DATE value is combined with time fields 00:00:00, and combined with the Calendar argument's TimeZone, to create a TIMESTAMP WITH TIME ZONE value.

 

Next, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value in UTC.

 

Next, the TIMESTAMP value is converted to a DATE value by setting the time fields to 00:00:00.

 

Finally, the DATE value is converted to a java.sql.Date object such that the DATE value matches what the java.sql.Date object's toString method would print.

Not allowed.

Not allowed.

First, the TIMESTAMP value is combined with the Calendar argument's TimeZone to create a TIMESTAMP WITH TIME ZONE value.

 

Next, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value in UTC.

 

Next, the TIMESTAMP value is converted to a DATE value by setting the time fields to 00:00:00.

 

Finally, the DATE value is converted to a java.sql.Date object such that the DATE value matches what the java.sql.Date object's toString method would print.

First, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value by truncating the time zone field.

 

Separately, the time zone field is stored in the Calendar argument's TimeZone.

 

Next, the TIMESTAMP value is converted to a DATE value by setting the time fields to 00:00:00.

 

Finally, the DATE value is converted to a java.sql.Date object such that the DATE value matches what the java.sql.Date object's toString method would print.

getTime without Calendar

Not allowed.

The TIME value is converted to a Time object such that the TIME value matches what the Time object's toString method would print.

 

The TIME value's fractional seconds are truncated to 3 digits, because Time objects only have milliseconds precision.

First, the TIME WITH TIME ZONE value is converted to a TIME value by truncating the time zone field.

 

Then, the TIME value is converted to a Time object such that the TIME value matches what the Time object's toString method would print.

 

The TIME value's fractional seconds are truncated to 3 digits, because Time objects only have milliseconds precision.

First, the TIMESTAMP value is converted to a TIME value by setting the date fields to 1970-01-01.

 

Then, the TIME value is converted to a Time object such that the TIME value matches what the Time object's toString method would print.

 

The TIME value's fractional seconds are truncated to 3 digits, because Time objects only have milliseconds precision.

First, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value by truncating the time zone field.

 

Next, the TIMESTAMP value is converted to a TIME value by setting the date fields to 1970-01-01.

 

Finally, the TIME value is converted to a Time object such that the TIME value matches what the Time object's toString method would print.

 

The TIME value's fractional seconds are truncated to 3 digits, because Time objects only have milliseconds precision.

getTime with Calendar

Not allowed.

First, the TIME value is combined with the Calendar argument's TimeZone to create a TIME WITH TIME ZONE value.

 

Next, the TIME WITH TIME ZONE value is converted to a TIME value in UTC.

 

Finally, the TIME value is converted to a Time object such that the TIME value matches what the Time object's toString method would print.

 

The TIME value's fractional seconds are truncated to 3 digits, because Time objects only have milliseconds precision.

First, the TIME WITH TIME ZONE value is converted to a TIME value by truncating the time zone field.

 

Separately, the time zone field is stored in the Calendar argument's TimeZone.

 

Then, the TIME value is converted to a Time object such that the TIME value matches what the Time object's toString method would print.

 

The TIME value's fractional seconds are truncated to 3 digits, because Time objects only have milliseconds precision.

First, the TIMESTAMP value is combined with the Calendar argument's TimeZone to create a TIMESTAMP WITH TIME ZONE value.

 

Next, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value in UTC.

 

Next, the TIMESTAMP value is converted to a TIME value by setting the date fields to 1970-01-01.

 

Finally, the TIME value is converted to a Time object such that the TIME value matches what the Time object's toString method would print.

 

The TIME value's fractional seconds are truncated to 3 digits, because Time objects only have milliseconds precision.

First, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value by truncating the time zone field.

 

Separately, the time zone field is stored in the Calendar argument's TimeZone.

 

Next, the TIMESTAMP value is converted to a TIME value by setting the date fields to 1970-01-01.

 

Finally, the TIME value is converted to a Time object such that the TIME value matches what the Time object's toString method would print.

 

The TIME value's fractional seconds are truncated to 3 digits, because Time objects only have milliseconds precision.

getTimestamp without Calendar

First, the DATE value is combined with time fields 00:00:00 to create a TIMESTAMP value.

 

Then, the TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

First, the TIME value is combined with date fields 1970-01-01 to create a TIMESTAMP value.

 

Then, the TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

First, the TIME WITH TIME ZONE value is converted to a TIME value by truncating the time zone field.

 

Next, the TIME value is combined with date fields 1970-01-01 to create a TIMESTAMP value.

 

Finally, the TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

The TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

First, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value by truncating the time zone field.

 

Then, the TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

getTimestamp with Calendar

First, the DATE value is combined with time fields 00:00:00, and combined with the Calendar argument's TimeZone, to create a TIMESTAMP WITH TIME ZONE value.

 

Next, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value in UTC.

 

Finally, the TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

First, the TIME value is combined with the Calendar argument's TimeZone to create a TIME WITH TIME ZONE value.

 

Next, the TIME WITH TIME ZONE value is converted to a TIME value in UTC.

 

Next, the TIME value is combined with date fields 1970-01-01 to create a TIMESTAMP value.

 

Finally, the TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

First, the TIME WITH TIME ZONE value is converted to a TIME value by truncating the time zone field.

 

Separately, the time zone field is stored in the Calendar argument's TimeZone.

 

Next, the TIME value is combined with date fields 1970-01-01 to create a TIMESTAMP value.

 

Finally, the TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

First, the TIMESTAMP value is combined with the Calendar argument's TimeZone to create a TIMESTAMP WITH TIME ZONE value.

 

Next, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value in UTC.

 

Finally, the TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

First, the TIMESTAMP WITH TIME ZONE value is converted to a TIMESTAMP value by truncating the time zone field.

 

Separately, the time zone field is stored in the Calendar argument's TimeZone.

 

Then, the TIMESTAMP value is converted to a Timestamp object such that the TIMESTAMP value matches what the Timestamp object's toString method would print.

Session DateForm

The session's current DateForm primarily dictates how DATE values are transmitted from the Teradata Database to the Teradata JDBC Driver. The session's current DateForm also dictates how the Teradata Database performs implicit conversions from a PreparedStatement/CallableStatement setString value to a destination DATE column or parameter.

ANSIDate is the recommended DateForm. When the session's current DateForm is ANSIDate, then the Teradata Database transmits DATE values to the Teradata JDBC Driver as 10-character values in the form "YYYY-MM-DD", and the Teradata Database rejects non-Y2K-compliant implicit conversions from a PreparedStatement setString value to a destination DATE column or parameter. (Teradata Database error 2666 is returned in this situation.)

The IntegerDate DateForm is provided for legacy applications. When the session's current DateForm is IntegerDate, then the Teradata Database transmits DATE values to the Teradata JDBC Driver as 4-byte binary values, and the Teradata Database accepts non-Y2K-compliant implicit conversions from a PreparedStatement setString value to a destination DATE column or parameter. (The Teradata Database uses 19 as the century digits for the year in this situation.)

It is possible to specify a DateForm attribute for a Teradata Database user with the CREATE USER command or the MODIFY USER command. When a session is first established, the session's current DateForm defaults to the user's DateForm, if available; or to the system default defined by the DATEFORM parameter in the DBSControl record.

The HELP SESSION command shows the session's current DateForm.

The session's current DateForm can be changed by executing the SQL commands SET SESSION DATEFORM=ANSIDATE or SET SESSION DATEFORM=INTEGERDATE. It is not recommended for a Java application to execute the SET SESSION DATEFORM commands using a pooled connection within an application server.

Receiving DATE Values from the Teradata Database

With TTU 8.1 and earlier releases of the Teradata JDBC Driver, less accurate information was provided for DATE values when the session's current DateForm is ANSIDate. For a DATE value received from the Teradata Database, the ResultSetMetadata getColumnType method returned java.sql.Types.CHAR, the ResultSetMetadata getColumn ClassName method returned java.lang.String, and the ResultSet getObject method returned a String value.

Beginning with TTU 8.2 Teradata JDBC Driver 3.4, more accurate information is provided for DATE values when the session's current DateForm is ANSIDate. For a DATE value received from the Teradata Database, the ResultSetMetadata getColumnType method returns java.sql.Types.DATE, the ResultSetMetadata getColumnClassName method returns java.sql.Date, and the ResultSet getObject method returns a java.sql.Date value.

Sending DATE Values to the Teradata Database

Beginning with Teradata JDBC Driver 12.0 and Teradata Database 12.0, the Teradata JDBC Driver sends java.sql.Date values to the Teradata Database as DATE values using the ANSIDate DateForm. This provides Y2K-compliant implicit conversion for java.sql.Date values that are specified with the PreparedStatement/CallableStatement setDate or setObject methods, and sent to destination CHAR/VARCHAR columns and parameters.

A legacy application requiring non-Y2K-compliant behavior can use Teradata-specific Escape Syntax functions introduced in Teradata JDBC Driver 12.0. The DateForm in effect at the time that an application calls the PreparedStatement/CallableStatement setDate or setObject method is the DateForm that is used for the corresponding DATE value sent to the Teradata Database.

// DateForm=IntegerDate provides non-Y2K-compliant implicit

// conversions from java.sql.Date to CHAR/VARCHAR

connection.nativeSQL("{fn teradata_useintegerdate}");

// DateForm=ANSIDate provides Y2K-compliant implicit conversion 

// from java.sql.Date to CHAR/VARCHAR (the default behavior)

connection.nativeSQL("{fn teradata_useansidate}");

Teradata Database releases 12.0 through 12.0.1.1 only accept DATE values whose DateForm matches the session's current DateForm. When the session's current DateForm is:

This restriction was removed beginning with Teradata Database 12.0.1.2.

Session Time Zone

All TIME and TIMESTAMP data is associated with time zones explicitly or implicitly. The user's default time zone is in effect initially when a connection (session) is established with the Teradata Database. If no default time zone is defined for the user, then the system default time zone is in effect initially for a connection. The connection's time zone can be changed by the SET TIME ZONE statement. For more information, see SET TIME ZONE in SQL Data Definition Language.

The PreparedStatement interface defines setTime and setTimestamp methods both with and without a Calendar argument:

TIME and TIMESTAMP table columns and stored procedure parameters can be declared with or without a time zone field: TIME, TIME WITH TIME ZONE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE:

Table 12 illustrates how the Teradata Database uses the combination of the input data value and its implicitly or explicitly associated time zone, and whether the destination is declared with or without a time zone field, to determine the resulting data value that is stored in a TIME or TIMESTAMP destination.

Table 12: Determining Data Values Stored in a TIME or TIMESTAMP Destination

Connection's Time Zone

Calendar Argument Used?

Destination Data Type (table column or stored procedure parameter) Includes Time Zone Field?

Result

Examples

UTC +00:00

No

  • PreparedStatement setTime without Calendar argument
  • PreparedStatement setTimestamp without Calendar argument

No

  • TIME
  • TIMESTAMP

Therefore, destination data values are relative to UTC

Teradata Database assumes that input data value is relative to the connection's time zone and the Teradata Database does no conversion because the connection's time zone is UTC

setTime(int index, Time x) x = 17:10:47:046 produces column value: 17:10:47.046000

setTimestamp(int index, Timestamp x) x = 2006-04-12 17:10:47:046 produces column value: 2006-04-12 17:10:47.046000

-10:00

No

  • PreparedStatement setTime without Calendar argument
  • PreparedStatement setTimestamp without Calendar argument

No

  • TIME
  • TIMESTAMP

Therefore, destination data values are relative to UTC

Teradata Database assumes that input data value is relative to the connection's time zone and the Teradata Database converts the input data value to UTC before putting the data value into the destination

setTime(int index, Time x) x = 17:10:47:046 produces column value: 03:10:47.046000

setTimestamp(int index, Timestamp x) x = 2006-04-12 17:10:47:046 produces column value: 2006-04-13 03:10:47.046000

Not relevant

Yes

  • Prepared Statement setTime with Calendar argument
  • Prepared Statement setTimestamp with Calendar argument

No

  • TIME
  • TIMESTAMP

Therefore, destination data values are relative to UTC

Teradata Database recognizes that the input data value includes an explicit time zone, and the Teradata Database converts the input data value to UTC before putting the data value into the destination

setTime(int index, Time x, Calendar cal) x = 17:10:47:046 Calendar time zone = -08:00 produces column value: 01:10:47.046000

setTimestamp(int index, Time x, Calendar cal) x = 2006-04-12 17:10:47:046; Calendar time zone = -08:00 produces column value: 2006-04-13 01:10:47.046000

Not relevant

Yes

  • Prepared Statement setTime with Calendar argument
  • Prepared Statement setTimestamp with Calendar argument

Yes

  • TIME WITH TIME ZONE
  • TIMESTAMP WITH TIME ZONE

Teradata Database recognizes that input data value includes an explicit time zone, and the Teradata Database does no conversion and puts the input data value and its time zone into the destination

setTime(int index, Time x, Calendar cal) x = 17:10:47:046 Calendar time zone = -08:00 produces column value: 17:10:47.046000-08:00

setTimestamp(int index, Time x, Calendar cal) x = 2006-04-12 17:10:47:046 Calendar time zone = -08:00 produces column value: 2006-04-12 17:10:47.046000‑08:00

UTC +00:00

No

  • Prepared Statement setTime without Calendar argument
  • Prepared Statement setTimestamp without Calendar argument

Yes

  • TIME WITH TIME ZONE
  • TIMESTAMP WITH TIME ZONE

Teradata Database assumes that input data value is relative to the connection's time zone and the Teradata Database does no conversion and puts the input data value and the connection's time zone into the destination

setTime(int index, Time x) x = 17:10:47:046 produces column value: 17:10:47.046000+00:00

setTimestamp(int index, Time x) x = 2006-04-12 17:10:47:046 produces column value: 2006-04-12 17:10:47.046000‑00:00

-10:00

No

  • Prepared Statement setTime without Calendar argument
  • Prepared Statement setTimestamp without Calendar argument

Yes

  • TIME WITH TIME ZONE
  • TIMESTAMP WITH TIME ZONE

Teradata Database assumes that input data value is relative to the connection's time zone and the Teradata Database does no conversion and puts the input data value and the connection's time zone into the destination

setTime(int index, Time x) x = 17:10:47:046 produces column value: 17:10:47.046000-10:00

setTimestamp(int index, Time x) x = 2006-04-12 17:10:47:046 produces column value: 2006-04-12 17:10:47.046000‑10:00

Stored Procedure TIME and TIMESTAMP INOUT Parameters

The Teradata Database forces the output value for an INOUT parameter, as set by a stored procedure, to conform exactly to the same data type attributes as the bound input value for the INOUT parameter.

When calling a stored procedure having INOUT parameters of type TIME WITH TIME ZONE and or TIMESTAMP WITH TIME ZONE, the application must bind values using the CallableStatement methods setTime or setTimestamp, respectively, with a Calendar argument. If the Calendar argument is not used, the Teradata Database returns error 3996 (Right truncation of string data). Also, the setNull method cannot be used to bind a NULL value to an INOUT parameter of type TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE. You must instead specify a null data value, using the setTime or setTimestamp method with a Calendar argument.

The Teradata Database does not currently provide or permit implicit or explicit data type conversions for TIME and TIMESTAMP values that reduce the number of fractional digits of the value.

The Teradata JDBC Driver connection parameters TNANO and TSNANO exist to work around this Teradata Database limitation. The TNANO and TSNANO connection parameters are awkward to use, however, because they force all TIME data values to have the same number of fractional digits (as specified by TNANO), and or force all TIMESTAMP data values to the same data type attributes as the bound input value for the INOUT parameter.

This limitation particularly affects stored procedure TIME and TIMESTAMP INOUT parameters, because of how the Teradata Database forces the output value for an INOUT parameter, as set by a stored procedure, to conform exactly to the same data type attributes as the bound input value for the INOUT parameter.

For example, the following stored procedure might encounter this problem:

REPLACE PROCEDURE MyProc(INOUT p1 TIMESTAMP(2), INOUT p1 TIMESTAMP(5))

Regarding NULL TIME and TIMESTAMP values bound to PreparedStatement or CallableStatement parameters, when the TNANO or TSNANO connection parameter is not specified, the Teradata JDBC Driver has default behavior that assumes that each NULL TIME or TIMESTAMP value, respectively, has zero fractional digits. This default behavior works with all possible INSERT and UPDATE destination TIME and TIMESTAMP columns, regardless of the number of fractional digits used in declaration. This default behavior does not work with stored procedure INOUT parameters declared with more than zero fractional digits.

Until the Teradata Database is enhanced, applications that call stored procedures with TIME and TIMESTAMP INOUT parameters must follow these guidelines:

  1. The TNANO and TSNANO connection parameters must be specified
  2. The stored procedure TIME and TIMESTAMP INOUT parameters' fractional digits must all be declared to be the same and they must match the TNANO and TSNANO connection parameters, respectively.

Multi-Statement Requests

Multi-statement requests can be performed by executing one SQL statement consisting of multiple SQL commands, separated by semicolons. Multi-statement requests can be executed using Statement.execute() method. The application can retrieve the result using Statement.getResultSet() or Statement.getUpdateCount() methods and in case multiple results are returned, then the application must use Statement.getMoreResults() method to iterate through these results.

Teradata Database Macros

A Teradata Database macro consists of one or more SQL statements. Macros can be executed using Statement.execute() method. The application can retrieve the result using Statement.getResultSet() or Statement.getUpdateCount() methods and in case multiple results are returned, then the application must use Statement.getMoreResults() method to iterate through these results.

Creating User-Defined Functions and External Stored Procedures

User-Defined Functions

Use the CREATE/REPLACE FUNCTION statement to create a User-Defined Function (UDF); it always returns a result set. The Statement.executeQuery() or Statement.execute() methods are used to execute this statement.

For more information regarding UDFs, refer to User-Defined Functions in SQL External Routine Programming.

External Stored Procedures

Use the CREATE/REPLACE PROCEDURE statement to create an External Stored Procedure (XSP). This can be executed using the Statement.execute() or Statement.executeUpdate() methods. The Statement.getWarnings() method can be used to retrieve the SQLWarning returned by the Statement object.

For more information regarding XSPs, refer to External Stored Procedures in SQL External Routine Programming.

For more information on Java XSPs, refer to the section in this chapter, Java External Stored Procedures.

Source File Locations for JDBC

The Teradata JDBC Driver can create UDFs, UDMs, or XSPs from source files that are stored on the server or the client. Source files stored on the client must be transferred from the client node to the server node.

For security purposes, the Teradata JDBC Driver uses the classpath to load all resources. This requires the source file on the client to be on the classpath. Once the classpath is set, the Teradata JDBC Driver can transfer the source file to the server node.

User-Defined Types

Creating User-Defined Types (UDTs)

Creating a UDT using Teradata JDBC Driver is done in a similar manner to creating other kinds of database objects. A Java application can call the Statement execute or executeUpdate method to issue the appropriate CREATE command.

The CREATE/REPLACE TYPE command is used to create a UDT. After the CREATE/REPLACE TYPE command is executed by the Teradata Database, the output messages from the command can be obtained by the application as a chain of SQLWarning objects from the Statement getWarnings method.

The CREATE TYPE command must be followed by CREATE METHOD, CREATE FUNCTION, CREATE TRANSFORM, and CREATE ORDERING commands as needed to fully create the type.

When creating UDMs and UDFs, if the "CREATE METHOD" or "CREATE FUNCTION" statements indicate that the source file containing the method definition is located on the client, then the source file must be available as a resource on the classpath. Teradata JDBC Driver automatically loads the resource from classpath and transfers it to the Teradata Database.

For more information on these SQL commands, refer to the Teradata Database SQL Data Definition Language reference.

Transferring UDT Values To and From the Database

Teradata JDBC Driver supports the following functionality for UDTs.

UDT Metadata

UDT metadata is available from DatabaseMetaData, ResultSetMetaData, and ParameterMetaData methods.

MetaData Method

UDT MetaData

Description

DatabaseMetaData

getAttributes

Retrieves a description of the given attribute of the given type for a UDT that is available in the given schema and catalog (catalog should be null because no catalog is supported in Teradata. For UDT attributes as UDT types, the type name is fully qualified. The UDT attribute type is returned in the ATTR_TYPE_NAME column.

DatabaseMetaData

getUDTs

Retrieves a description of the UDTs defined in a particular schema. Schema-specific UDTS can have type STRUCT and DISTINCT. (Type JAVA_OBJECT is not supported in Teradata.) The UDT name is returned in the TYPE_NAME column. If there is an entry for the UDT in the connection's type map, the Java class name mapped to the UDT is returned in the CLASS_NAME column.

DatabaseMetaData

getColumns

Retrieves a description of table columns available in the specified schema and catalog. (Catalog should be null because no catalog is supported in Teradata.) For columns as UDT types, the type name is fully qualified. The UDT name is returned in the TYPE_NAME column.

DatabaseMetaData

getProcedureColumns

Retrieves a description of stored procedure parameter and result columns in the specified schema and catalog. (Catalog should be null because no catalog is supported in Teradata.) For columns as UDT types, the type name is fully qualified. The UDT name is returned in the TYPE_NAME column.

DatabaseMetaData

getBestRowIdentifier

Retrieves a description of a table's optimal set of columns that uniquely identified a row in the specified schema and catalog. (Catalog should be null because no catalog is supported in Teradata.) For columns as UDT types, the type name is fully qualified. The UDT name is returned in the TYPE_NAME column.

DatabaseMetaData

getTypeInfo

Retrieves a description of all the standard SQL types supported by this database. Teradata supports SQL distinct types and structured types; as a result, the returned result set includes one row with a TYPE_NAME of DISTINCT and a DATA_TYPE of java.sql.Types.DISTINCT, and one row with a TYPE_NAME of STRUCT and a DATA_TYPE of java.sql.Types.STRUCT.

ResultSetMetaData

getColumnTypeName

If the column type is a UDT, then a fully qualified type name is returned.

ResultSetMetaData

getColumnClassName

If the column type is a UDT and there is an entry for the UDT in the connection's type map, the Java class name mapped to the UDT is returned.

ParameterMetaData

getParameterTypeName

If the column type is a UDT, then a fully qualified type name is returned.

ParameterMetaData

getParameterClassName

If the column type is a UDT and there is an entry for the UDT in the connection's type map, the Java class name mapped to the UDT is returned.

UDT Limitations

The Geospatial data types cannot be used with java.sql.Struct.

The ResultSetMetaData getColumnDisplaySize method returns zero for Structured UDT column values.

When a UDT attribute is a LOB data type, the application cannot set the LOB attribute value using an InputStream. The application must set the LOB attribute value using a Blob or Clob value obtained from the getBlob or getClob method, respectively.

A Struct object created by the Connection createStruct method is not subject to JDBC custom type mapping. Its getAttributes method will return the attribute values specified at the time of creation.

Period Data Types

Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.00.00.18, Period data types can be used with java.sql.Struct.

The application must compose each Period data value as a java.sql.Struct value with two attributes. The first attribute corresponds to the start of the period, and the second attribute corresponds to the end of the period. For more information regarding Period data types, refer to Period Data Types in the Teradata Database SQL Data Types and Literals reference.

Example of Sending PERIOD(TIME WITH TIME ZONE) values

The application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application uses instances of that class to compose PERIOD(TIME WITH TIME ZONE) values.

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT+08:00")) ;

Time time1 = Time.valueOf( ...

Time time2 = Time.valueOf( ...

// Available beginning with Teradata JDBC Driver 14.10.00.26

Struct timetz1 = new MyStruct("TIME WITH TIME ZONE", new Object [] {time1, cal}) ;

Struct timetz2 = new MyStruct("TIME WITH TIME ZONE", new Object [] {time2, cal}) ;

// Assuming a table with two columns: INTEGER and PERIOD(TIME WITH TIME ZONE)

PreparedStatement ps = con.prepareStatement ("INSERT INTO MyTable VALUES(?,?)") ;

ps.setInt(1, id) ;

ps.setObject(2, new MyStruct("PERIOD(TIME WITH TIME ZONE)", new Object [] {timetz1, timetz2})) ;

ARRAY Data Type

Beginning with Teradata Database 14.0 and Teradata JDBC Driver 14.00.00.04, the java.sql.Array interface and the SQL ARRAY data type are supported. A Teradata SQL ARRAY is defined with one or more dimensions, and is used to store many values of the same data type sequentially or in a matrix-like format.

Array Rules and Limitations

An application can use the Connection createArrayOf method to compose a java.sql.Array value to send to the Teradata Database. The createArrayOf method's Object[] argument can be a one-dimensional Object[] array or a multi-dimensional Object[][][]... array, and the array must follow these rules.

For more information regarding the SQL ARRAY data type, refer to ARRAY/VARRAY Data Type in the Teradata Database SQL Data Types and Literals reference.

XML Data Type

Beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.19, the java.sql.SQLXML interface and the SQL XML data type are supported. An application can use the Teradata JDBC Driver to send XML data to the Teradata Database and retrieve XML data from the Teradata Database. The java.sql.SQLXML interface is available in JDK 6.0 and later. SQLXML is not available in JDK 5.0 and earlier.

SQLXML Functionality and Limitations

For more information regarding the SQL XML data type, refer to XML Data Type in the Teradata Database SQL Data Types and Literals reference.

NUMBER Data Type

Beginning with Teradata Database 14.0 and Teradata JDBC Driver 14.00.00.09, the SQL NUMBER data type is supported. The JDBC data type identifier Types.NUMERIC corresponds to the SQL NUMBER data type.

Beginning with Teradata Database 14.0 and Teradata JDBC Driver 14.00.00.09, when an application binds a BigDecimal value to a PreparedStatement parameter marker, the Teradata JDBC Driver's default behavior is to send the BigDecimal value to the Teradata Database as the SQL NUMBER data type. An application can override this behavior to force the Teradata JDBC Driver to send a BigDecimal value to the Teradata Database as the SQL DECIMAL data type. Correspondingly, a SQL NUMBER value received from the Teradata Database will be presented to the application as Types.NUMERIC, and as a BigDecimal value.

With prior software versions, the Teradata JDBC Driver sends a BigDecimal value to the Teradata Database as the SQL DECIMAL data type, and the SQL NUMBER data type is not supported.

PreparedStatement/CallableStatement Method

Teradata Database 14.0 and later, with NUMBER Data Type

Teradata Database 13.10 and earlier

setBigDecimal

Send as NUMBER

Send as DECIMAL

setObject with BigDecimal data value and no targetSqlType argument

Send as NUMBER

Send as DECIMAL

setObject with BigDecimal data value and targetSqlType argument Types.NUMERIC

Send as NUMBER

Send as DECIMAL

setObject with BigDecimal data value and targetSqlType argument Types.DECIMAL

Send as DECIMAL

Send as DECIMAL

setNull with targetSqlType argument Types.NUMERIC

Send as NUMBER

Send as DECIMAL

setNull with targetSqlType argument Types.DECIMAL

Send as DECIMAL

Send as DECIMAL

The Teradata Database requires all data values in a PreparedStatement batch bound to a particular parameter marker to be of the same data type. The application must bind null and non-null values appropriately to ensure that all values bound to a parameter marker are either NUMBER or DECIMAL, not a combination of the two.

For more information regarding the SQL NUMBER data type, refer to Numeric Data Types in the Teradata Database SQL Data Types and Literals reference.

Interval Data Types

Beginning with Teradata JDBC Driver 14.10.00.26, an application can use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as an Interval data type. Prior to Teradata JDBC Driver 14.10.00.26, the setString method was required to bind an Interval value. The setString method continues to work as before for Interval values. Applications only need to use the new functionality for situations that the setString method does not support. These situations are shown below in the examples.

The application must compose each Interval data value as a java.sql.Struct value with one attribute. The one and only attribute of the Struct must be a String value, containing the character representation of the Interval value.

The application is responsible for ensuring that the number of Interval digits in the String matches the default number of interval digits for the Interval data type. For INTERVAL...SECOND data types, the application is also responsible for ensuring that the number of fractional digits in the String matches the default number of fractional digits for the Interval data type. The following table lists the SQL type name and the required number of digits for each Interval data type.

SQL Type Name

Number of Interval Digits

Number of Fractional Digits

INTERVAL YEAR

2

0

INTERVAL YEAR TO MONTH

2

0

INTERVAL MONTH

2

0

INTERVAL DAY

2

0

INTERVAL DAY TO HOUR

2

0

INTERVAL DAY TO MINUTE

2

0

INTERVAL DAY TO SECOND

2

6

INTERVAL HOUR

2

0

INTERVAL HOUR TO MINUTE

2

0

INTERVAL HOUR TO SECOND

2

6

INTERVAL MINUTE

2

0

INTERVAL MINUTE TO SECOND

2

6

INTERVAL SECOND

2

6

The application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application uses instances of that class to compose Interval values.

Example of Invoking the Teradata Database Built-In EXTRACT Function

This example sends an INTERVAL YEAR TO MONTH value of 56 years and 3 months, and the EXTRACT YEAR FROM expression will extract the value 56. The WHERE clause condition tests whether the number of years is more than 50. In this case, a single-row ResultSet will be returned, containing the value 'Y'.

  PreparedStatement ps = con.prepareStatement("SELECT 'Y' WHERE EXTRACT(YEAR FROM ?) > 50") ;

  ps.setObject(1, new MyStruct("INTERVAL YEAR TO MONTH", new Object [] {"56-03"})) ;

  ResultSet rs = ps.executeQuery() ;

Example of Implicit Type Conversion for an Interval Value

This example inserts an INTERVAL YEAR value into an INTERVAL YEAR TO MONTH destination column, relying on the Teradata Database to perform an implicit type conversion from INTERVAL YEAR to INTERVAL YEAR TO MONTH.

  // Assuming a table with a single INTERVAL YEAR TO MONTH column

  PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?)") ;

  ps.setObject(1, new MyStruct("INTERVAL YEAR", new Object [] {"56"})) ;

  ps.executeUpdate() ;

Example of Inserting Both NULL and non-NULL Interval Values in a PreparedStatement Batch

This example shows a PreparedStatement batch insert of NULL and non-NULL INTERVAL YEAR values into an INTERVAL YEAR destination column.

  // Assuming a table with a single INTERVAL YEAR column

  PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?)") ;

  ps.setObject(1, new MyStruct("INTERVAL YEAR", new Object [] {"56"})) ;

  ps.addBatch() ;

  ps.setObject(1, new MyStruct("INTERVAL YEAR", new Object [] {null})) ;

  ps.addBatch() ;

  ps.executeBatch() ;

JSON Data Type

Beginning with Teradata Database 15.0 and Teradata JDBC Driver 15.00.00.11, the JSON data type is supported. The JDBC API does not yet define a standard JSON data type, so the Teradata JDBC Driver offers Teradata-specific functionality for an application to use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as a JSON data type. Applications can also insert VARCHAR and CLOB values into JSON destination columns. In order for the application to fully take advantage of the JSON data type's built-in functions, the JSON question-mark parameter marker should be set using a Struct value. This can be seen in the example shown below.

The Teradata Database returns a JSON value as a CLOB value. An application can use the following metadata methods to differentiate between a JSON value and an actual CLOB value. These methods return "JSON" to indicate a JSON value, and return "CLOB" to indicate a CLOB value.

When an application uses the Teradata-specific functionality of specifying a JSON value as a Struct value, the Struct value must contain one of the following attributes.

Furthermore, when the Struct contains a Reader attribute, the Struct must also contain a second attribute that is an Integer type specifying the number of characters in the stream.

When an application uses the Teradata-specific functionality of specifying a JSON value as a Struct value, the application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application uses instances of that class to compose JSON values.

Example of Invoking the JSON Data Type's Combine Method

This example combines two JSON values resulting in a single JSON object. In this case, a single-row ResultSet will be returned, containing the value {"name" : "Jim","name" : "Joe"}.

  PreparedStatement ps = con.prepareStatement("SELECT CAST(? AS JSON).combine(?)") ;

  ps.setObject(1, new MyStruct("JSON", new Object [] {"{\"name\" : \"Jim\"}"})) ;

  ps.setObject(2, new MyStruct("JSON", new Object [] {"{\"name\" : \"Joe\"}"})) ;

  ResultSet rs = ps.executeQuery() ;

Example of Inserting Both NULL and non-NULL JSON Values in a PreparedStatement Batch

This example shows a PreparedStatement batch insert of NULL and non-NULL JSON values into a JSON destination column.

  // Assuming a table with an INTEGER column and a JSON column

  PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?, ?)") ;

  ps.setInt(1, 123) ;

  ps.setObject(2, new MyStruct("JSON", new Object [] {"{\"name\" : \"Kimberly\"})) ;

  ps.addBatch() ;

  ps.setInt(1, 456) ;

  ps.setObject(2, new MyStruct("JSON", new Object [] {null})) ;

  ps.addBatch() ;

  ps.executeBatch() ;

JSON Data Type Incompatibility Errors

If an application attempts to use the Teradata-specific functionality of specifying a JSON value as a Struct value with Teradata Database 15.0 or later, in conjunction with an old version of the Teradata JDBC Driver that does not support the JSON data type, then the following exception may be thrown. The solution is to upgrade to a newer version of the Teradata JDBC Driver that supports the JSON data type.

[Error 3922] [SQLState HY000] Invalid Repr in DataInfo Parcel.

 

If an application attempts to use the Teradata-specific functionality of specifying a JSON value as a Struct value with Teradata Database 14.10 or earlier, in conjunction with a version of the Teradata JDBC Driver that supports the JSON data type, then the following exception may be thrown. The solution is to upgrade to Teradata Database 15.0 or later.

[Error 1451] [SQLState HY000] Teradata Database JSON data type support is required, and StatementInfo parcel support must be enabled

 

If an application executes a query that returns a JSON data value from the Teradata Database while using an old version of the Teradata JDBC Driver that does not support the JSON data type, then the following exception may be thrown. The solution is to upgrade to a newer version of the Teradata JDBC Driver that supports the JSON data type.

[Error 1245] [SQLState HY000] Unable to obtain data value because the Teradata Database indicated that the data type is ambiguous

 

DATASET Data Type

Beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.23, the DATASET data type is supported for the Avro storage format. The JDBC API does not yet define a standard DATASET data type, so the Teradata JDBC Driver offers Teradata-specific functionality for an application to use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as an Avro format DATASET data type. Applications can also insert VARBYTE and BLOB values into Avro format DATASET destination columns. In order for the application to fully take advantage of the DATASET data type's built-in functions, the DATASET question-mark parameter marker should be set using a Struct value. This can be seen in the example shown below.

The Teradata Database returns an Avro-formatted DATASET value as a BLOB value. An application can use the following metadata methods to differentiate between an Avro-formatted DATASET value and an actual BLOB value. These methods return "DATASET STORAGE FORMAT AVRO" to indicate an Avro-formatted DATASET value, and return "BLOB" to indicate a BLOB value.

When an application uses the Teradata-specific functionality of specifying an Avro-formatted value as a Struct value, the Struct value must contain one of the following attributes.

Furthermore, when the Struct contains an InputStream attribute, the Struct must also contain a second attribute that is an Integer type specifying the number of bytes in the stream.

To use the Teradata-specific functionality of specifying an Avro-formatted DATASET value as a Struct value, the application must provide a class that implements the java.sql.Struct interface.

public class MyStruct implements java.sql.Struct

{

  private String m_typeName ;

  private Object [] m_attributes ;

  public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }

  public String getSQLTypeName() { return m_typeName ; }

  public Object [] getAttributes() { return m_attributes ; }

  public Object [] getAttributes(java.util.Map map) { return m_attributes ; }

}

The application then uses instances of that class to compose Avro-formatted DATASET values.

Example of Invoking the AVRO_CHECK Function

This example validates the Avro-formatted DATASET value. In this case, a single-row ResultSet will be returned containing the value "OK".

  PreparedStatement ps = con.prepareStatement("SELECT AVRO_CHECK(?)") ;

  ps.setObject(1, new MyStruct("DATASET STORAGE FORMAT AVRO", new Object [] {avroInputStream, nLength})) ;

  ResultSet rs = ps.executeQuery() ;

Example of Inserting Both NULL and non-NULL Avro-formatted DATASET Values in a PreparedStatement Batch

This example shows a PreparedStatement batch insert of NULL and non-NULL Avro-formatted DATASET values into an Avro format DATASET destination column.

  // Assuming a table with an INTEGER column and a DATASET STORAGE FROMAT AVRO column

  PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?, ?)") ;

  ps.setInt(1, 123) ;

  ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT AVRO", new Object [] {avroInputStream, nAvroLength})) ;

  ps.addBatch() ;

  ps.setInt(1, 456) ;

  ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT AVRO", new Object [] {null})) ;

  ps.addBatch() ;

  ps.executeBatch() ;

DATASET Data Type Incompatibility Errors

If an application executes a query that returns an Avro-formatted DATASET data value from the Teradata Database while using an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the data type will be returned as a BLOB.

If an application attempts to use the Teradata-specific functionality of binding an Avro-formatted DATASET value as a Struct value with Teradata Database 16.0 or later, in conjunction with an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the following exception may be thrown. The solution is to upgrade to a newer version of the Teradata JDBC Driver that supports the DATASET data type.

[Error 3922] [SQLState HY000] Invalid Repr in DataInfo Parcel.

 

If an application attempts to use the Teradata-specific functionality of binding an Avro-formatted DATASET value as a Struct value with Teradata Database 15.10 or earlier, in conjunction with a version of the Teradata JDBC Driver that supports the Avro format DATASET data type, then the following exception may be thrown. The solution is to upgrade to Teradata Database 16.0 or later.

[Error 1509] [SQLState HY000] Teradata Database DATASET STORAGE FORMAT AVRO data type support is required, and StatementInfo parcel support must be enabled

 

Updatable LOBs

Temporary Table

Before LOB updates can be used with the Teradata JDBC Driver, a table must be created with the following columns:

For normal application usage, the Teradata Database Administrator creates the table as a Global Temporary Table (GTT). However, the table could be a regular table for special usage cases, such as for debugging.

The id integer column is intended to be the primary index. If desired, it could be specified as a unique primary index.

When the GTT is created with CREATE TABLE, the ON COMMIT PRESERVE ROWS clause must be specified, so that the Teradata JDBC Driver can manipulate LOBs across transactions.

In the example that follows, the table name JdbcLobUpdate is just a suggestion; any name can be chosen for the table:

create global temporary table JdbcLobUpdate(

    id integer not null,

    bval blob,

    cval clob character set unicode)

  unique primary index upi_JdbcLobUpdate(id)

  on commit preserve rows

Connection Parameter

The Connection parameter LOB_TEMP_TABLE must be set to the name chosen for the temporary table.

LOB_TEMP_TABLE=tableName

A database name can be optionally specified:

LOB_TEMP_TABLE=databaseName.tableName

Update LOB

If an application wants to update an existing LOB value in a table, then after calling any LOB update methods, the application must also execute an UPDATE statement to put the modified LOB value back into the original row.

Teradata JDBC Driver returns true from DatabaseMetaData locatorsUpdateCopy to indicate that the implementation updates a copy of the LOB.

ResultSet rs = stmt.executeQuery("SELECT id,data FROM datatab");

rs.next();

int id = rs.getInt(1);

Blob data = rs.getBlob(2);

int numWritten = data.setBytes(1, val);

if (dbmd.locatorsUpdateCopy() == true){

  PreparedStatement ps = conn.prepareStatement(

   "UPDATE datatab SET data = ? WHERE id = ?");

  try {

    ps.setBlob(1, data);

    ps.setInt(2, id);

    ps.executeUpdate();

  } finally {

    ps.close();

  }

}

Calling free

The free method releases the resources held by a Blob or Clob object. After free has been called, the object cannot be used.

The free method is available beginning with Teradata JDBC Driver 14.00.00.08. If the application updates a Blob or Clob object, then the application must call free when the application is done with the object; otherwise, Teradata Database Error 3130 (Response Limit Exceeded) may occur.

With JDK 6.0 and later, the free method is defined in the java.sql.Blob and java.sql.Clob interfaces, and the free method can be called directly by the application. With JDK 5.0 and earlier, reflection must be used to call the free method.

Row Numbers and Update Counts Exceeding Integer.MAX_VALUE

While the Teradata Database can accommodate tables containing many billions of rows, the JDBC API methods that work with row numbers or update counts use a signed 32-bit integer (a Java int) to represent a row number or update count. The largest positive value that a signed 32-bit integer can hold is approximately two billion, and is denoted by the constant Integer.MAX_VALUE.

Row Numbers

Some ResultSet methods accept a row number argument or return a row number. While a large result set may contain more than two billion rows, the ResultSet methods are limited to accessing only the initial number of rows, such that the row number is less than Integer.MAX_VALUE.

Update Counts

Beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.25, for row count values received from the Teradata Database that are too large to fit into a signed 32-bit integer, the Teradata JDBC Driver will return an update count of Integer.MAX_VALUE to the application, and will provide a SQLWarning with error code 1474 that lists the actual row count in the message text.

The following JDBC API methods provide this behavior.

JDBC Interface

JDBC Method

Statement

int [] executeBatch ()

int executeUpdate (String sql)

int executeUpdate (String sql, int autoGeneratedKeys)

int executeUpdate (String sql, int [] columnIndexes)

int executeUpdate (String sql, String [] columnNames)

int getUpdateCount ()

PreparedStatement, CallableStatement

int executeUpdate ()

Multi-Threading Issues

Using multi-threading can improve an application's performance. Determine which requests can run at the same time and then using multiple concurrent sessions, submit a request on each session. It is strongly discouraged to try and submit more than one concurrent request per session.

Note:  The Teradata Database does not support more than one active request per session. If the application attempts to do this, then the Teradata JDBC Driver blocks until the previous request has returned. This may negatively impact performance.

Table 13 contains JDBC Object Thread safety information.

Table 13: JDBC Object Thread Safety

JDBC Object

Thread Safety

Connection

Connection objects are fully thread-safe, and can be used by multiple threads. However, only one request is executed at a time. Subsequent requests block until the previous request completes executing.

Statement, PreparedStatement, CallableStatement

Statement and its subclass objects are generally not thread-safe. One specific case is supported. A second thread can call the cancel() method to interrupt an executing request.

ResultSet

ResultSet objects are not thread-safe. A ResultSet object should only be used by a single thread. A ResultSet object contains state information that is not thread-safe, such as the wasNull state to indicate whether the most recently called getter method returned a NULL.

Blob, Clob

Blob and Clob objects are not thread-safe. A Blob or Clob object should only be used by a single thread. A Blob/Clob object should be retrieved from a ResultSet by the thread that owns the ResultSet using the ResultSet.getBlob/getClob method. After the Blob/Clob object has been retrieved from a ResultSet, it can be used by a different thread. Thus, multiple threads can be used to access LOB data; however, each Blob/Clob object itself should only be used by one thread.

Data Dictionary Access by DatabaseMetaData Methods

Several of the Teradata JDBC Driver DatabaseMetaData methods submit queries to the Teradata Database on behalf of the application that calls the DatabaseMetaData methods. The application designer must ensure that the application has sufficient privileges to access the necessary Data Dictionary tables and views.

The following table lists each of the DatabaseMetaData methods that query Data Dictionary tables and/or views, and lists the necessary access for each method.

Beginning with Teradata JDBC Driver 13.00.00.25, Data Dictionary V views are used when connected to Teradata Database 12.0 or later. In the table below, the [V] suffix indicates whether the V view will be conditionally used depending on the Teradata Database version.

The USEXVIEWS connection parameter controls whether the normal views or the X views are used. In the table below, the [X] suffix indicates whether the X view will be conditionally used depending on the setting of the USEXVIEWS connection parameter.

DatabaseMetaData Method

Privileges Needed

getAttributes

Select access on DBC.Tables[V][X]

Select access on DBC.Columns[V][X]

Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33

Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33

getBestRowIdentifier

Select access on DBC.Tables[V][X]

Select access on DBC.Columns[V][X]

Select access on DBC.Indices[V][X]

Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33

Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33

getColumnPrivileges

Select access on DBC.Tables[V][X]

Select access on DBC.Columns[V][X]

Select access on DBC.AllRights[V][X]

getColumns

Select access on DBC.Tables[V][X]

Select access on DBC.Columns[V][X], prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.20

Select access on DBC.ColumnsJQV[X], beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.20

Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33

Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33

Uses HELP COLUMN and HELP TYPE to obtain information about view columns prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.20

getCrossReference

Select access on DBC.All_RI_Parents[V][X]

getExportedKeys

Select access on DBC.All_RI_Parents[V][X]

getFunctions

Select access on DBC.Tables[V][X]

Select access on DBC.Functions[V][X]

getFunctionColumns

Select access on DBC.Tables[V][X]

Select access on DBC.Functions[V][X]

Select access on DBC.Columns[V][X]

Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33

Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33

getImportedKeys

Select access on DBC.All_RI_Parents[V][X]

getIndexInfo

Select access on DBC.Indices[V][X], beginning with Teradata JDBC Driver 14.00.00.24

Select access on DBC.IndexStats[V], beginning with Teradata JDBC Driver 14.00.00.24

Select access on DBC.Tables[V][X], beginning with Teradata JDBC Driver 14.00.00.24

Uses HELP STATISTICS and HELP INDEX prior to Teradata JDBC Driver 14.00.00.24

getPrimaryKeys

Select access on DBC.Indices[V][X]

getProcedureColumns

Select access on DBC.Tables[V][X]

Select access on DBC.Columns[V][X]

Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33

Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33

getProcedures

Select access on DBC.Tables[V][X]

getSchemas

Select access on DBC.Databases[V][X]

getTablePrivileges

Select access on DBC.Tables[V][X]

Select access on DBC.AllRights[V][X]

getTables

Select access on DBC.Tables[V][X]

getUDTs

Select access on DBC.Tables[V][X]

Select access on DBC.Columns[V][X]

Select access on DBC.UDFInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33

Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33

Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33

Select access on DBC.UDTTransform, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33

Select access on DBC.UDTTransformV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33

Using the Sun JDK 5.0 Implementation of JDBC RowSet Interface

The Teradata JDBC Driver works with the Sun JDK 5.0 implementation of JDBC RowSet Interface.

When using com.sun.rowset.JdbcRowSetImpl(java.sql.Connection connection) and com.sun.rowset.JdbcRowSetImpl(String url, String user, String password), it calls Connection.prepareStatement(String sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE). However, for database versions prior to 12.0, CONCUR_UPDATABLE is not supported.

The Teradata JDBC Driver downgrades the unsupported ResultSet concurrency (CONCUR_UPDATABLE) to the supported concurrency (CONCUR_READ_ONLY) for database versions prior to 12.0 or when the fetched result set is not updatable for Teradata Database 12.0 and newer versions. Refer to Making a Result Set Updatable for more details about how to use updatable result set.

Encryption, Authentication, and Authorization

Teradata JDBC Driver password encryption was released with Teradata Tools and Utilities 7.1. The functionality was then extended in Teradata Tools and Utilities 8.0 using Logon Mechanisms to support:

The Logon Mechanisms supported are:

The Teradata JDBC Driver provides connection parameters to select the mechanism, and to turn data encryption on or off for the connection. The selected mechanism determines the level of encryption that is used on the connection. Single Sign On (SSO) is only supported with the Kerberos logon mechanism. See Security Administration for details on these mechanisms.

The Teradata JDBC Driver always uses encrypted logons, meaning that the logon password is encrypted in transit over the network to the Teradata Database.

In this context, "data encryption" refers to the encryption of non-logon message traffic. By default, the Teradata JDBC Driver only encrypts logons, and does not encrypt non-logon message traffic. Specify the JDBC connection parameter ENCRYPTDATA=ON for the Teradata JDBC Driver to encrypt non-logon message traffic.

URL and DataSource Parameters

URL and DataSource parameters in Teradata Tools and Utilities 8.0 or later and Teradata Database V2R6.0 or later support this feature using the following parameters:

Parameter Descriptions

Table 14 provides URL and data source parameters information. Descriptions of methods that allow the getting and setting of the parameters are listed in TeraDataSource Class.

Table 14: URL and DataSource Parameters 

URL and Data Source Parameter

Description

LOGMECH

The LOGMECH parameter specifies the mechanism selected.

LOGDATA

The LOGDATA parameter carries information used by those mechanisms that require information beyond the normal Teradata username and password.

ENCRYPTDATA

The ENCRYPTDATA parameter controls the encryption of traffic to and from the Teradata Database. Specifying ENCRYPTDATA=ON enables encryption.

Beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.15, data encryption occurs if required by centralized administration, or if the application specifies the ENCRYPTDATA=ON connection parameter. Encryption required by centralized administration overrides the ENCRYPTDATA=OFF connection parameter.

"Centralized administration" for encryption refers to configuring an LDAP directory and/or the Teradata Database to ensure message traffic encryption between Teradata client software and the Teradata Database.

Please refer to the Teradata Database Security Administration book, Chapter "Managing Network Security Policy", Section "Configuring a Confidentiality QOP Policy" for how to configure an LDAP directory to require message traffic encryption for particular users.

Please refer to the Teradata Database Security Administration book, Chapter "Managing Network Security Policy", Section "Requiring Confidentiality" for how to use the gtwcontrol command to require message traffic encryption for all Teradata Database users.

Prerequisites

Logons with any mechanism other than TD2 using the UTF16 session character set are not accepted if either the Teradata JDBC Driver release is prior to 3.3 or the Teradata Database release is prior to V2R6.1. If such a logon is attempted, one of the following SQLExceptions is thrown:

The following table lists the prerequisites for each of the methods.

Method

Connection parameter

Description

Teradata Method 2

LOGMECH=TD2

Does not require any special setup, and can be used immediately

Kerberos

LOGMECH=KRB5

Requires a significant number of administration tasks on the machine that is running the Teradata JDBC Driver. See Meeting Kerberos Prerequisites for more detail.

To use UTF16 with Kerberos authentication, the Teradata JDBC Driver must be version 3.3 or later, and the Teradata Database must be V2R6.1 or later.

LDAP

LOGMECH=LDAP

Requires a significant amount of administration effort to set up the LDAP environment. These tasks are covered in Security Administration.

Once they are complete, LDAP can be used without any additional work required on the machine that is running the Teradata JDBC Driver.

To use UTF16 with LDAP authentication, the Teradata JDBC Driver must be version 3.3 or later, and the Teradata Database must be V2R6.1 or later.

TDNEGO

LOGMECH=TDNEGO

Automatically selects the appropriate logon mechanism, available beginning with Teradata Database 15.10 and Teradata JDBC Driver 15.10.00.31

Meeting Kerberos Prerequisites

Prior to operating with Kerberos, the system administrator must perform the following prerequisites:

Set up a Kerberos Domain and Realm

A Kerberos domain and realm must be set up for the machines that are to be in the domain. A system administrator must perform this work. See Security Administration.

Define Users within Active Directory

All Kerberos users must be defined within the Active Directory in the Windows domain. Kerberos usernames are case-sensitive. It is important that users log on to the their machines exactly as they are defined within Active Directory. Although a user defined in an Active Directory as DR818999 could successfully log onto the domain as dr818999 (notice the case change), for Kerberos to work, the user must log in to the domain as DR818999.

Note:  Ensure that the definition of the user within the Active Directory has the "Do not require Kerberos preauthentication" checked.

Ensure that each system has a krb5.ini or krb5.conf file

The essential Kerberos configuration information is specified in the krb5.ini file on Windows or the krb5.conf file on other platforms. Java searches for krb5.ini or krb5.conf in the following order.

The krb5.ini file on Windows is the equivalent of the krb5.conf file that is the standard for both MIT and Heimdal Kerberos. The details of the various settings can be found in the MIT Kerberos documentation.

The following file is an example and must be modified to reflect your actual domain, realm, and Key Distribution Center (KDC).

[libdefaults]
ticket_lifetime = 6000
default_realm = ESROOTDOM.ESDEV.TDAT
clockskew = 13000
default_tkt_enctypes = des-cbc-md5
default_tgs_enctypes = des-cbc-md5
checksum_type=2
[realms]
ESROOTDOM.ESDEV.TDAT = {
    kdc = esroot.esrootdom.esdev.tdat:88
    default_domain = esrootdom
}
[domain_realm]
esrootdom = {
    .esrootdom = ESROOTDOM.ESDEV.TDAT
    esrootdom = ESROOTDOM.ESDEV.TDAT
}

Naming Conventions for Realm Names and Hostnames

DNS domain names and hostnames are case-insensitive and, by convention, are lowercase.

In contrast, Kerberos realm names are uppercase and are case-sensitive. The Kerberos realm name is the uppercase version of the domain name.

Kerberos authentication will not work unless the Kerberos realm name is specified in uppercase in the krb5.ini or krb5.conf file.

Run kinit

For SSO, users must be able to run the kinit program, which obtains and caches a Kerberos Ticket-Granting-Ticket. This program can be found in the jre/bin directory of the Java JDK. An example follows:

C:\j2sdk1.4.2_04\jre\bin>kinit
Password for DR818999@ESROOTDOM.ESDEV.TDAT:Mypassword
New ticket is stored in cache file C:\Documents and Settings\DR818999\krb5cc_DR818999

Credential Delegation and Teradata Unity Director

When using Kerberos and Teradata Unity Director, Credential Delegation must be enabled. To use Credential Delegation, obtain a Ticket-Granting-Ticket that is forwardable. This is done using the forwardable option of kinit, for example:

kinit -f

This option must be used in order for Teradata Unity Director to route authentication requests to the Teradata Database.

Set the forwardable=true option in the krb5.ini (Windows) or krb5.conf (Linux) file under the [libdefaults] section. The previous krb5.ini or krb5.conf file modified to use credential delegation is similar to:

[libdefaults]
forwardable = true
ticket_lifetime = 6000
default_realm = ESROOTDOM.ESDEV.TDAT
clockskew = 13000
default_tkt_enctypes = des-cbc-md5
default_tgs_enctypes = des-cbc-md5
checksum_type=2
[realms]
ESROOTDOM.ESDEV.TDAT = {
    kdc = esroot.esrootdom.esdev.tdat:88
    default_domain = esrootdom
}
[domain_realm]
esrootdom = {
    .esrootdom = ESROOTDOM.ESDEV.TDAT
    esrootdom = ESROOTDOM.ESDEV.TDAT
}

Verify Login Configuration Information

Kerberos authentication requires the following Login Configuration file, which can be stored in a directory that you choose.

com.sun.security.jgss.initiate
{
  com.sun.security.auth.module.Krb5LoginModule sufficient useTicketCache=true;
};
other
{
  com.sun.security.auth.module.Krb5LoginModule required;
};

You can specify the Login Configuration filename with the java.security.auth.login.config Java system property.

For example, if the Login Configuration file is named TeraJDBC.config and is located in the current directory, specify the following JVM command-line option.

-Djava.security.auth.login.config=TeraJDBC.config

Alternatively, the Login Configuration file can be specified as a system-wide setting. Edit the java.security file located in the JRE's lib/security directory, and add a login.config.url.N property.

Examine the java.security file to determine whether you have any existing login.config.url.N properties. You must choose a value for N that does not conflict with any of your existing properties, and the values of N must be consecutively numbered.

The following example shows a single login.config.url.N value named login.config.url.1. In this example, the configuration file is located at C:\dmr\TeraJDBC.config.

login.config.url.1=file:C:/dmr/TeraJDBC.config

Note that the property value URL must be specified with forward slashes. On Windows, substitute backslashes with forward slashes.

Windows Registry setting

The normal configuration for Microsoft Windows does not permit the export of a session key for a Kerberos Ticket-Granting Ticket (TGT). In order to use Kerberos SSO, you must change a Windows registry setting to enable the export of a session key for a Kerberos TGT.

  HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters
  Value Name: AllowTgtSessionKey
  Value Type: REG_DWORD
  Value:      0x01  (default is 0)

When an Active Directory account belongs to the local Administrators group on the client PC, Windows will not export a session key for a Kerberos TGT, even when the AllowTgtSessionKey registry value is set to 0x1. Therefore, it is not possible to use Kerberos SSO for an account that belongs to the local Administrators group on the client PC.

Specify the JVM Option

To enable SSO, the JVM option must be supplied:

-Djavax.security.auth.useSubjectCredsOnly=false

Enable Kerberos SSO

To enable the Kerberos SSO logon, the user defined in the Teradata Database must have the same password as the user's logon password and support SSO. To support SSO, the DDL statement

grant logon with null password

is required.

For example, to provide the needed permission for user dr818999, use the following grant logon:

grant logon on all to dr818999 with null password;

More detail on this statement can be found in the Teradata Database SQL Data Definition Language.

Server-Side Default Authentication Mechanism

The Teradata Security Administrator can change the default authentication mechanism from TD2 to a different mechanism on the server side (Teradata Database). However, the Administrator must be aware that certain mechanisms are only supported on certain platforms. For example, with Teradata Database 14.0 and earlier, Kerberos support is limited to Windows clients only.

Extra preparation is needed when changing the server-side default mechanism to a mechanism that is not supported on all the client platforms in use at the site. Before changing the server-side default mechanism, all Java applications on client platforms that don't support the planned server-side default mechanism must be configured to explicitly specify a supported authentication mechanism using the LOGMECH= connection parameter.

For example, consider a site using Teradata Database 14.0 or earlier with Java applications deployed to both Windows and Linux clients. If the Administrator decides to change the server-side default mechanism from TD2 to Kerberos, the Administrator must first verify that the Java applications deployed on Linux specify the LOGMECH=TD2 connection parameter.

C and Java Application Sharing of XML Configuration

C/C++ applications that communicate with the Teradata Database use the TeraGSS security library. If C/C++ and Java applications are deployed to the same physical machine, then Java applications can be configured to use the TeraGSS security library's XML security configuration file.

In this deployment scenario, Java applications do not use the tdgssconfig.jar file that is included in the Teradata JDBC Driver download package. Instead, the classpath for Java applications is set to include the TeraGSS directory that contains TdgssUserConfigFile.xml.

The classpath must be set to include the following:

Not all classloaders support the specification of a directory on the classpath. This deployment technique can only be used with classloaders that support the specification of a directory on the classpath. If the application server or environment does not support the specification of a directory on the classpath, then C/C++ and Java applications cannot directly share the same TeraGSS User Configuration File.

For application servers or environments that do not support the specification of a directory on the classpath, the TeraGSS User Configuration File can only be shared indirectly, and an extra step must be performed to enable this indirect sharing.

A jar update command must be executed to take the TeraGSS User Configuration File from the TeraGSS directory and to put the TeraGSS User Configuration File into the tdgssconfig.jar file from the Teradata JDBC Driver download package:

jar uvf tdgssconfig.jar TdgssUserConfigFile.xml

Each time the TeraGSS User Configuration File is modified, the jar update command must be executed again. The application server or environment must be restarted so that the modified tdgssconfig.jar is used.

Generated Keys

In version 3.4 of the Teradata JDBC Driver, the following methods were added or enabled to support generated keys:

Multi-Statement Requests

If generated keys are being requested for a multi-statement request, then the application can retrieve the generated keys for the first statement by calling getGeneratedKeys(). It is then necessary to call getMoreResults() before each additional call to getGeneratedKeys(). If the statement is not an INSERT statement, then an empty result set is returned.

The JDBC spec does not state how an application would obtain multiple generated key result sets from a multi-statement request. The JDBC spec does not mandate or prohibit using getMoreResults() to advance to the next generated key result set. This is a design choice for the Teradata JDBC Driver that seemed to be the most obvious and intuitive choice.

PreparedStatement Batch Requests

If generated keys are being retrieved for a PreparedStatement batch request, then the rows are coalesced into a single auto-generated key result set, which is returned from getGeneratedKeys(). The maximum number of inserts in a batch request is limited to 1024.

INSERT ... SELECT Statement

If the request is an INSERT ... SELECT statement, then multiple rows are returned in the result set. The rows are not in any specific order.

Exceptions

Error 1125

One or more of the generated keys specified do not match a column name in the table. Change the list of column names to match the column names in the table where the row is being inserted may be returned for:

Error 1126

One or more of the generated key indexes are invalid. Change the indexes so that they are greater than 0 and less than or equal to the number of columns in the table where the row is being inserted may be returned for:

Error 1127

Column names array or column index array cannot be null may be returned if any of the methods are called that contain a null value for the column names array or the column index array.

Error 1128

AutoGenerated Keys are not supported with this release of database software. The database must be running V2R6.2 or higher may be returned if any of the methods are called that request Auto Generated Keys.

Statement.executeUpdate()

The following exceptions are currently being used for PreparedStatement.executeUpdate(), but are now used for Statement.executeUpdate() where auto-generated keys are being requested.

Upsert Statements are not Supported

UPSERT statements are not supported with getGenerated Keys. If generatedKeys are requested after an UPSERT statement, an empty result set is returned.

ParameterMetaData and Ambiguous Types

When using ParameterMetaData, there are cases where the data type of a parameter may be ambiguous. This can happen when a ? parameter is used in certain expressions or used in the invocation of an overloaded UDF or UDM. Consider the following examples:

        Simple INSERT Operation:

    INSERT INTO T1 VALUES (?, ?, ?);

This is a straightforward operation in which the three parameters, indicated by (?, ?, ?), are inserted into the table T1. In this instance, there is a simple assignment of each of the parameters to a column or field in the table. The parameter metadata returned is clear; it is the metadata describing each of the columns that the parameter data populates.

        INSERT Operation with Expressions:

    INSERT INTO T1 VALUES (? * 3, ? + 1, ? MOD 3);

In this example, each of the columns of the target table is to be assigned the results of expressions: ? * 3, ? + 1, and ? MOD 3. The metadata associated with the three parameters has become ambiguous. since it could map to more than one SQL type. In this case, the data type of the parameter is considered unknown, and the method java.sql.ParameterMetaData.getParameterType() returns java.sql.Types.NULL.

Table 16 outlines what is returned in cases where the data type of a parameter is ambiguous, and is considered an unknown data type.

Table 16: Data Type Ambiguous or Unknown

java.sql.ParameterMetaData Method

Value Returned for an Unknown Data Type

String getParameterClassName(int param)

null

int getParameterType(int param)

java.sql.Types.NULL

String getParameterTypeName(int param)

null

int getPrecision(int param)

0

int getScale(int param)

0

int isNullable(int param)

ParameterMetaData.parameterNullableUnknown

boolean isSigned(int param)

false

int getParameterMode(int param)

ParameterMetaData.parameterModeUnknown

When a ? parameter is specified in the select list of a SELECT statement, the ResultSetMetaData may be ambiguous, in addition to the ParameterMetaData.

Example

    SELECT ?

Note:  Question-mark parameter markers may be used in a select-list within a SELECT statement beginning with Teradata Database V2R6.2.0.19.

Table 17 outlines what is returned in cases where the data type of a parameter is ambiguous, and is considered an unknown data type.

Table 17: Data Type Ambiguous or Unknown

java.sql.ResultSetMetaData Method

Value Returned for an Unknown Data Type

getCatalogName

"" (a zero-length string)

getColumnClassName

null

getColumnDisplaySize

0

getColumnType

java.sql.Types.NULL

getColumnTypeName

null

getPrecision

0

getScale

0

getSchemaName

"" (a zero-length string)

getTableName

"" (a zero-length string)

isAutoIncrement

false

isCaseSensitive

false

isCurrency

false

isDefinitelyWritable

false

isNullable

ResultSetMetaData.columnNullableUnknown

isReadOnly

false

isSearchable

false

isSigned

false

isWritable

false

Java External Stored Procedures

The Java External Stored Procedure (XSP) portion of the ANSI SQL standard is provided by Teradata Database 12.0 or later, when used in conjunction with the Teradata JDBC Driver 12.0 or later. This includes the SQLJ database and tables, jar file installation, Java XSP definition, and Java XSP access to the JDBC default connection.

For more information, refer to SQL External Routine Programming.

Use Java XSPs in the following manner:

Once created, access the Java routine in the same manner as any XSP. Java XSPs can execute SQL code using the standard JDBC driver interface. Since the stored procedure is running on the database and is invoked from within a logged-on session, a connection URL of jdbc:default:connection should be used.

JAR Files

A jar file contains a collection of Java classes. The classes (compiled Java bytecodes) are referenced when an external Java routine is created using the EXTERNAL NAME clause. The jar files are created outside of the database. Before the classes can be referenced, they must be registered and copied into the SQL environment. Once a jar is installed onto the database, its content can't be changed in any way–it can only be deleted or replaced in its entirety.

A jar file is not global but is only available to the user that installed it using a call to SQLJ.INSTALL_JAR(). Like the infrastructure for C/C++ UDFs and XSPs, a directory is created on the server for each database that contains a jar. A C/C++ DLL created for one or more UDFs or XSPs in a given database is not accessible to other users or databases; the same is true for jar files. In connection with this, no new access rights are created for jar files. Therefore, user‑database A cannot create an XSP that references a jar installed in user‑database B. However, user‑database A can be granted access to a Java XSP that has been created in user‑database B by using the same access rights designed for C/C++ UDFs/XSPs. A model that could optionally be followed for Java XSPs is to install all jars and create all Java XSPs in the same database, and then grant access to these Java XSPs to all users who will need to execute them.

The jar files are installed, replaced, deleted, or path-specified by the following XSPs:

Transferring Java XSP From the Client to the DBS Server

If using JDBC, use jar files for XSPs that are stored on the server or the client. A jar file for a Java XSP that is on the client must be transferred from the client node to the server node. For security purposes, the Teradata JDBC Driver uses the classpath to load all resources. The jar file that contains Java XSPs must not be on the classpath itself. Instead, the container of the jar file must be on the classpath. For example, if the jar file is located in a directory on the file system, then the directory name must be present in the classpath. As another example, the jar file may be located inside a war file or an ear file, because the application server will automatically make the contents of the war file or ear file available on the classpath. Once the class path is set, the Teradata JDBC Driver can transfer the source file to the server node.

The following is code from a JDBC sample class using SQLJ to install and transfer a jar file from the client to the DBS server:

stmt.executeUpdate("{call sqlj.install_jar('cj!SampleXJSP.jar', 'SampleXJSP',0)}");

This example gives the location of the jar file using the locspec parameter. The <locspec> specifies where the originating jar file is located. If the <location designator> specifies 'CJ!' then the jar is located on the client in the client-interpreted location specified by the class path for the Teradata JDBC Driver. If the <location designator> specifies 'SJ!' then the jar is located on the database server using the <server jar path>.

Defining the SQL Routines

After the jar file is installed, the next step is to create the Java class procedure DeptJobInfo. For example:

REPLACE PROCEDURE getDeptJobInfo

(IN name VARCHAR(30), OUT dept VARCHAR(50), OUT job VARCHAR(300))

LANGUAGE JAVA MODIFIES SQL DATA

PARAMETER STYLE JAVA

EXTERNAL NAME 'SampleXJSP:DeptJobInfo.getDeptJobInfo';

Parameter Usage Example

The following example shows a procedure definition for various parameter types. SQL statement:

REPLACE PROCEDURE getEmpInfo

(IN name VARCHAR(30), OUT id INTEGER, OUT dept VARCHAR(50),

OUT job VARCHAR(300), OUT res CLOB)

LANGUAGE JAVA MODIFIES SQL DATA

PARAMETER STYLE JAVA

EXTERNAL NAME 'SampleXJSP:EmpInfo.getEmpInfo(

java.lang.String,

java.lang.Integer[],

java.lang.String[],

java.lang.String[],

java.sql.Clob[])';

Source code for the Java stored procedure defined above:

public class EmpInfo

{

  public static void getEmpInfo(String name,

                        java.lang.Integer[] id,

                        String[] dept,

                        String[] job,

                        java.sql.Clob[] res) throws SQLException

  {

    /* Establish default connection.*/

    Connection con =     DriverManager.getConnection("jdbc:default:connection");

    String query = "SELECT empID, empDept, empJob, empResume" +

             "FROM employee 2" +

             "WHERE empName = ?;";

    /* Executing the command */

    PreparedStatement pStmt = con.prepareStatement(query);

    try

    {

      pStmt.setString(1, name);

      ResultSet rs = pStmt.executeQuery();

      boolean more = rs.next();

      if(more)

      {

        id[0] = new java.lang.Integer(rs.getInt(1));

        dept[0] = rs.getString(2);

        job[0] = rs.getString(3);

        res[0] = rs.getClob(4);

      }

    }

    finally

    {

      pStmt.close();

    }

}

The parameters in the Java XSP provided here are explicitly mapped from the SQL types to the Java types. These mappings also can be implicit. The mappings for external Java XSP parameters from SQL types to Java types are defined in SQL Data Types Mapping.

Default Connection

The Java XSP in the previous example is running on the database and is invoked from within a logged-on session. As a result, the connection URL being used is jdbc:default:connection. This creates a default connection that participates in the caller's session and current transaction. No logoff occurs when the connection's close method is called since the default connection uses the same session as the caller. The default connection is only accessible from one thread; specifically, the thread that invoked the Java XSP.

Description

Example JDBC Default Connection URL

No connection parameters

jdbc:default:connection

One connection parameter

jdbc:default:connection/ParameterName=Value

 

Connection parameters are optional. The first ParameterName follows a forward slash character.

Two or more connection parameters

jdbc:default:connection/ParameterName=Value,ParameterName=Value

 

When two or more connection parameters are specified, the parameters must be separated by commas. Enclose the value in single-quotes if the value contains a comma.

 

ParameterName is a connection parameter, and Value is a value for the parameter.

Invoking a Java XSP from JDBC

Calling a Java XSP from a JDBC client is the same as invoking any stored procedure. The following example uses a CallableStatement:

String sCall = "{call getEmpInfo(?,?,?,?,?)}";

String sName = "Brian Lee";

// Creating a CallableStatement object, representing

// a precompiled SQL statement and preparing the callable

// statement for execution.

CallableStatement cStmt = con.prepareCall(sCall);

// Setting up input parameter value

cStmt.setString(1, sName);

// Setting up output parameters for data retrieval by

// declaring parameter types.

cStmt.registerOutParameter(2, Types.INTEGER);

cStmt.registerOutParameter(3, Types.VARCHAR);

cStmt.registerOutParameter(4, Types.VARCHAR);

cStmt.registerOutParameter(5, Types.CLOB);

System.out.printIn("\n Calling the procedure with '"

           + sName + '"...");

// Making a procedure call

cStmt.executeUpdate();

// Displaying procedure call result

System.out.printIn(" Call successful.");

System.out.printIn("\n Displaying output of the call to"

           + "getEmpInfo(...):");

System.outprintIn("\n" + sName);

System.out.printIn("---------------");

int id = cStmt.getInt(2);

System.out.printIn(" Employee ID : " + id);

System.out.printIn(" Department : " + cStmt.getString(3));

System.out.printIn(" Job Description : " + cStmt.getString(4));

System.out.print(" Resume: ");

// Writing CLOB data out to a file for review

createClobFile(cStmt.getClob(5),(id + "resumeT20604.txt"));

Transaction Semantics and Java XSPs

Transaction semantics (ANSI or Teradata) are set when a session is logged on, and cannot be subsequently changed. When using the Teradata JDBC Driver, the transaction semantics are specified using the TMODE connection parameter. The default connection used by a Java XSP always inherits the transaction semantics used to establish the caller's session.

Limitations

The following SQL statements are not supported in a Java stored procedure when being used to generate a dynamic result set. This also means that they cannot be mixed with other SQL statements in a multi-statement request when some of these other SQL statements are used to generate a dynamic result set:

The following restrictions apply when returning auto-generated keys result sets from a Java stored procedure:

A Java stored procedure can never directly or indirectly call another Java stored procedure.

An IN or INOUT LOB parameter cannot be updated in a Java Stored Procedure.

The LOB_TEMP_TABLE used in a Java Stored Procedure must be separate from the one used by the JDBC connection calling the Java Stored Procedure.

Teradata Database 12.0 and 13.0 support Java Stored Procedures compiled with JDK 1.4.2 or JDK 5.0 only.

Java Stored Procedures compiled with JDK 6.0 are supported beginning with Teradata Database 13.10.

Updatable Result Sets

Making a Result Set Updatable

A default ResultSet object is returned when calling the following methods:

This default ResultSet object is not updatable and has a cursor that moves forward only.

It is possible to produce ResultSet objects that are scrollable and updatable by calling the following methods:

To make the ResultSet objects from the above methods updatable, the following requirements need to be satisfied:

Non-updatable Result Set

The Teradata JDBC Driver attempts to satisfy updating, inserting, and deleting requests from the result set fetched from the single table or multiple joined tables. However, there are several cases where the returned result set from Teradata Database is not updatable, including:

Inner Joins

There are no issues with using updatable result set with inner joins since only matched rows are selected from the inner-joined tables without NULL values padded for unmatched rows.

However, if the result set fetched from multiple inner-joined tables doesn't meet the following unique index requirement for all tables with columns contained in the result set, the methods updateRow and deleteRow fail and the Teradata JDBC Driver throws an SQLException, and method insertRow returns an error message from Teradata Database.

The fetched result set must contain a column that is the only member of a unique index or a column that is a member of one or more unique indexes on the table, and all the columns of at least one unique index have been selected in the result set.

Outer Joins

If the result set fetched from multiple outer-joined tables doesn't meet the following unique index requirement for all tables with columns contained in the result set, the methods updateRow and deleteRow fail and the Teradata JDBC Driver throws a SQLException, and method insertRow returns an error message from Teradata Database.

The fetched result set must contain a column that is the only member of a unique index, or a column that is a member of one or more unique indexes on the table, and all the columns of at least one unique index have been selected in the result set.

Also, the Teradata JDBC Driver is only able to permit a result set row from a join to be updated if the unique index column(s) selected from the above unique index requirement is(are) not NULL. However, outer joins could involve NULL values for these unique index columns for one or more joined table(s). In this case, updateRow() and deleteRow() operations fail and the Teradata JDBC Driver throws a SQLException.

Using an Updatable Result Set

The following are some common scenarios for using an updatable result set with the Teradata JDBC Driver:

Result Set Type and Concurrency Upgrading and Downgrading

There are some scenarios where the result set type and concurrency need to be upgraded or downgraded.

Scenario 1: Type Upgrading

The Teradata JDBC Driver implements updatable result set as result set type ResultSet.TYPE_SCROLL_INSENSITIVE.

When users attempt to use result set type

ResultSet.TYPE_FORWARD_ONLY and concurrency mode

ResultSet.CONCUR_UPDATABLE in the following methods:

and the following requirements are satisfied:

the fetched result set type is upgraded to ResultSet.TYPE_SCROLL_INSENSITIVE, and an SQLWarning is added to the connection object.

Scenario 2: Concurrency Downgrading

When users attempt to use result set concurrency

ResultSet.CONCUR_UPDATABLE in the following methods:

and the following requirements are not satisfied:

the fetched result set concurrent mode is downgraded to ResultSet.READ_ONLY, and an SQLWarning is added to the connection object.

Exceptions

The following are Teradata JDBC Driver exception scenarios when using updatable result set:

Stored Procedure Dynamic Result Sets

A stored procedure that returns dynamic result sets is similar to any multi-statement request:

The result sets are dynamic; therefore, it is not possible to look at the metadata for the results until after the statement is executed.

Special Floating Point Values

The Teradata Database does not provide complete support for the special floating point values positive infinity, negative infinity, and Not a Number (NaN). It is possible for a Java application to use a PreparedStatement to bind and insert special floating point values into a FLOAT column in the Teradata Database. However, because special floating point values are not fully supported by the Teradata Database, incorrect results and Teradata Database errors might occur for SELECT statements with WHERE clause conditions that reference special floating point values.

PreparedStatement Batch

A PreparedStatement batch provides efficient inserting, updating, or deleting data where the SQL statement remains the same and only the data values differ for each submission.

Insert performance depends on many factors, such as the number of columns, the column data types, the data value sizes, and so on. Table 18 provides general comparisons of different insert techniques, ordered from slowest to fastest.

Table 18: Insert Performance

Throughput

Insert Technique

Comments

Lowest

SQL non-prepared statement insert using literal data values

SQL PreparedStatement insert using question-mark parameters (non-batch)

Significantly faster than the previous approach

SQL PreparedStatement batch insert using question-mark parameters, with the recommended batch size. A batch size of roughly 5,000 to 10,000 works well for most applications.

Can be 10 to 40 times faster than the previous approach

Highest

JDBC FastLoad PreparedStatement batch using question-mark parameters, with the recommended batch size. A batch size of roughly 50,000 to 100,000 works well for most applications.

Can be 3 to 10 times faster than the previous approach. JDBC FastLoad is only recommended for loading large amounts of data (at least 100,000 rows total).

PreparedStatement BatchUpdateException Handling

Beginning with Teradata Database 13.10 and Teradata JDBC Driver 13.00.00.16, PreparedStatement batch execution can return individual success and error conditions for each parameter set.

An application using the PreparedStatement executeBatch method must have a catch-block for BatchUpdateException and the application must examine the error code returned by the BatchUpdateException getErrorCode method.

When an application encounters a BatchUpdateException, the application iterates over the integer updateCount array returned by the BatchUpdateException getUpdateCounts method. Each integer in the updateCount array sequentially corresponds to a parameter set in the PreparedStatement batch.

The parameter set was executed successfully when the integer is greater than or equal to zero, or if the integer is equal to Success.SUCCESS_NO_INFO. The application takes no additional actions.

When the integer is equal to Statement.EXECUTE_FAILED, the parameter set failed to process. The application must handle each parameter set with an integer equal to Statement.EXECUTE_FAILED.

Table 19: BatchUpdateException Handling
New Functionality
Old Functionality

When the BatchUpdateException error code is 1338, the new functionality is available. BatchUpdateException error code 1338 indicates that each non-successful update count corresponds to a chained SQLException.

When the BatchUpdateException error code is not 1338, the new functionality is not available and chained SQLExceptions do not exist for each nonsuccessful update count. This will always be the case with Teradata Database 13.0 or earlier, or with Teradata JDBC Driver versions prior to 13.00.00.16. When using Teradata Database 13.10 or later with Teradata JDBC Driver 13.00.00.16 or later, this may happen if the error occurs before any rows are successfully processed by the Teradata Database.

The application obtains the SQLException chain returned by the BatchUpdateException getNextException method. As the application examines each integer in the updateCount array, the application walks the SQLException chain. Each SQLException in the chain sequentially corresponds to a failed parameter set.

  • When the SQLException getErrorCode method returns a negative error code, the application should do whatever is appropriate for a rejected parameter set, such as adding the parameter set to an error file.

  • When the individual execution succeeds, the application takes no additional steps

  • When the SQLException getErrorCode method returns a non-negative error code, the parameter set must be resubmitted individually using the PreparedStatement executeUpdate method.

  • When the individual execution fails or when the SQLException getErrorCode method returns a negative error code, the application does whatever is appropriate for a rejected parameter set, such as add the parameter set to an error file.

Each failed parameter set must be resubmitted individually using the PreparedStatement executeUpdate method.

  • If the individual execution succeeds, then the application does not need to do anything further for that parameter set.

  • If the individual execution fails, then the application should do whatever is appropriate for a rejected parameter set, such as adding the parameter set to an error file.

JDBC FastLoad

JDBC FastLoad provides a method for quickly loading large amounts of data into an empty destination table in a Teradata Database. The actual performance of JDBC FastLoad varies, depending on the application and database configuration.

For example, given an unconstrained network, JDBC FastLoad may be three to 10 times faster than the corresponding SQL PreparedStatement batched insert. In other words, JDBC FastLoad may take only 10% to 33% of the time for the equivalent SQL PreparedStatement batch insert.

Enabling JDBC FastLoad

JDBC FastLoad is enabled with TYPE=FASTLOAD in the URL connection string. When enabled, the FastLoad protocol is used with the Teradata Database for FastLoad‑capable SQL INSERT statements. For all other SQL statements, including SQL INSERT statements not FastLoad capable, the standard protocol is used with the Teradata Database.

In order to qualify for JDBC FastLoad, the SQL INSERT statement must meet the following criteria:

Considerations When Using JDBC FastLoad

JDBC FastLoad can offer improved performance, but it may not be the right choice for all applications. The following factors must be considered when evaluating JDBC FastLoad for possible use by your application.

JDBC Data Types Supported by JDBC FastLoad

Not all of the JDBC data types supported by the Teradata JDBC Driver are supported by JDBC FastLoad; for example, BLOB and CLOB are not supported. Likewise, not all of the JDBC data type conversions supported by the Teradata JDBC Driver are supported by JDBC FastLoad.

JDBC Escape Functions in Support of JDBC FastLoad

Connection.nativeSQL("{fn teradata_amp_count()}")

Returns the number of AMPs configured for a Teradata Database. The information helps in determining the maximum number of JDBC FastLoad connections that can be created.

Connection.nativeSQL("{fn teradata_logon_sequence_number()}")

Returns comma-separated pairs of a JDBC FastLoad-capable PreparedStatement.hashCode() and the associated Logon Sequence Number (LSN) of any JDBC FastLoad PreparedStatement created by this Connection.

For example, a string of "6166383,1850,22323092,1851" indicates that 6166383 and 22323092 are hash codes of a JDBC FastLoad PreparedStatement and 1850 and 1851 are the respective LSNs.

The information helps in finding the DBC.SessionInfo.SessionNo of JDBC FastLoad connections, as is shown in Program Examples. However, note than an LSN can only be observed when auto-commit mode is false and at least one column value is bound beforehand using the JDBC FastLoad-capable PreparedStatement.

JDBC FastLoad CSV

JDBC FastLoad CSV provides a method for quickly loading large amounts of data into an empty destination table in a Teradata Database. The application must provide the data as an InputStream containing variable-length text in Comma Separated Values (CSV) format. The default separator is ',' (comma). Other separators are supported, as indicated in Field Separators Supported by JDBC FastLoad CSV. This feature is available beginning with Teradata JDBC Driver 13.00.00.26.

Enabling JDBC FastLoad CSV

JDBC FastLoad CSV is enabled with TYPE=FASTLOADCSV in the URL connection string. When enabled, the FastLoad protocol is used with the Teradata Database for FastLoadCSV-capable SQL INSERT statements. Other kinds of SQL statements are not supported by a JDBC FastLoad CSV connection.

In order to qualify for JDBC FastLoad CSV, the SQL INSERT statement must meet the following criteria:

Considerations When Using JDBC FastLoad CSV

JDBC FastLoad CSV can offer improved performance, but it may not be the right choice for all applications. The following factors must be considered when evaluating JDBC FastLoad CSV for possible use by your application.

JDBC Data Types Supported by JDBC FastLoad CSV

Not all of the JDBC data types supported by the Teradata JDBC Driver are supported by JDBC FastLoad CSV; for example, BLOB, CLOB, and BINARY are not supported. Likewise, not all of the JDBC data type conversions supported by the Teradata JDBC Driver are supported by JDBC FastLoad CSV.

Field Separators Supported by JDBC FastLoad CSV

JDBC FastLoad CSV uses field separators in the InputStream data set of variable-length text to separate columns of data. The default separator is ',' (comma), but it can be changed to any other character from '\u0000' to '\u007f' of the Unicode Basic Multilingual Plane character set, except for the following characters:

Commonly-used separators, other than comma, are '\u003b' (';' semicolon), '\u007c' ('|' vertical line), and '\u0009' ('\t' tab).

Most printable separator characters, such as semicolon or vertical line, can be specified in the Teradata JDBC Driver Connection URL with no escaping or quoting needed. For example, a vertical line field separator is specified as follows within the Teradata JDBC Driver Connection URL.

            Connection con = DriverManager.getConnection(
                "jdbc:teradata://MySystem/FIELD_SEP=|,TYPE=FASTLOADCSV", user, password);

A Unicode escape sequence must be used when a non-printable or special character is used as the field separator. For example, a tab field separator is specified as follows within the Teradata JDBC Driver Connection URL. Note that when a backslash ('\') is included in a Java string literal, it must be escaped with a preceding backslash character.

            Connection con = DriverManager.getConnection(
                "jdbc:teradata://MySystem/FIELD_SEP=\\u0009,TYPE=FASTLOADCSV", user, password);

Some printable characters are significant for the Teradata JDBC Driver Connection URL itself. Either a Unicode escape sequence may be used, or the field separator may be enclosed within single quotes. For example, when a comma is explicitly specified as the field separator, it must be enclosed by single quotes within the Teradata JDBC Driver Connection URL.

            Connection con = DriverManager.getConnection(
                "jdbc:teradata://MySystem/FIELD_SEP=',',TYPE=FASTLOADCSV", user, password);

If the data values in the data set might contain all the common field separator and quote characters, then the ASCII Unit Separator (US) control character ('\u001f') can be used as the field separator, since it is the control character designated for this purpose.

JDBC FastExport

JDBC FastExport provides a method for quickly retrieving large amounts of data from a Teradata Database table or view. The actual performance of JDBC FastExport varies, depending on the application and database configuration.

For example, given an unconstrained network, JDBC FastExport may be two to three times faster than the corresponding SQL PreparedStatement select. In other words, JDBC FastExport may take only 33% to 50% of the time for the equivalent SQL PreparedStatement select.

Enabling JDBC FastExport

JDBC FastExport is enabled with TYPE=FASTEXPORT in the URL connection string. When enabled, the FastExport protocol is used with the Teradata Database for FastExport-capable SQL SELECT statements. For all other SQL statements, including SQL SELECT statements not FastExport-capable, the standard protocol is used with the Teradata Database.

To qualify for JDBC FastExport, the SQL SELECT statement must meet the following criteria:

Considerations when Using JDBC FastExport

JDBC Data Types Supported by JDBC FastExport

Not all of the JDBC data types supported by the Teradata JDBC Driver are supported by JDBC FastExport; for example, BLOB and CLOB are not supported.

JDBC Escape Functions in Support of JDBC FastExport

JDBC Monitor

JDBC Monitor provides a method for accessing and using standard performance monitoring and production control functions contained within the Teradata Database.

Enabling JDBC Monitor

JDBC Monitor is enabled with the PARTITION=MONITOR connection parameter. When enabled, the Monitor protocol is used exclusively to communicate with the Teradata Database. Only Teradata Database PM/API Monitor commands can be executed with the Monitor protocol. SQL DML and DDL statements cannot be executed using the Monitor protocol.

Considerations When Using JDBC Monitor

Teradata Database PM/API Statements Supported by JDBC Monitor

The Teradata Database PM/API statements supported by JDBC Monitor are listed in the following tables. Refer to Workload Management API: PM/API and Open API for details of each Monitor statement.

Table 20 describes the IDENTIFY statement.

Table 20: IDENTIFY Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

2 (host_id)

setShort

The logical ID of a host (or client)

Note:  Nullable

3 (session_no)

setInt

Session number. A combination of host_id and session_no identifies a user causing a block.

Note:  Nullable

4 (database_id)

setInt

ID of the database for this session

Note:  Nullable

5 (user_id)

setInt

ID of the user for this session

Note:  Nullable

6 (table_id)

setInt

Unique ID of a table

Note:  Nullable

Table 21 describes the MONITOR PHYSICAL CONFIG statement.

Table 21: MONITOR PHYSICAL CONFIG Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

Table 22 describes the MONITOR PHYSICAL RESOURCE statement.

Table 22: MONITOR PHYSICAL RESOURCE Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

Table 23 describes the MONITOR PHYSICAL SUMMARY statement. If MONITOR PHYSICAL SUMMARY is executed for a Teradata Database running on MP-RAS platforms, the Teradata JDBC Driver may throw chained SQL exceptions with error codes 1214 and 1178. This is due to a Teradata Database defect that has been fixed in the following releases: 6.0.2.54, 6.1.1.58.

Table 23: MONITOR PHYSICAL SUMMARY Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

Table 24 describes the MONITOR SESSION statement. SET SESSION RATE must have been executed to set a valid session rate before MONITOR SESSION can succeed.

Table 24: MONITOR SESSION Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

2 (host_id)

setShort

The logical ID of a host (or client)

Note:  Nullable

3 (session_no)

setInt

Session number. A combination of host_id and session_no identifies a user causing a block.

Note:  Nullable

4 (user_name)

setString

Name of the user or database that is running this session

Note:  Nullable

Table 25 describes the MONITOR SQL statement. If MONITOR SQL is used to monitor SQL statements longer than 64000 bytes, the result set may be corrupted with non-printable characters when printable characters are expected. This is due to a Teradata Database defect that has been fixed in the following releases: 6.0.2.51, 6.1.1.54, and 6.2.1.5.

Table 25: MONITOR SQL Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

Note:  A version id of "2" MUST NEVER be bound when running MONITOR SQL or the Teradata JDBC Driver will throw chained SQL exceptions with error codes 1214 and 1178. This is due to a Teradata Database defect. Version id "2" is an old version that shouldn't be used.

2 (host_id)

setShort

The logical ID of a host (or client)

Note:  Nullable

3 (session_no)

setInt

Session number. A combination of host_id and session_no identifies a user causing a block.

Note:  Nullable

4 (RunPEVprocNo)

setShort

The PE vproc number where the session runs

Note:  Nullable

Note:  The argument "RunPEVprocNo" was added after Teradata Database V2R5.1 and is not available in older versions.

Table 26 describes the MONITOR VERSION statement.

Table 26: MONITOR VERSION Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

Table 27 describes the MONITOR VIRTUAL CONFIG statement.

Table 27: MONITOR VIRTUAL CONFIG Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

Table 28 describes the MONITOR VIRTUAL RESOURCE statement.

Table 28: MONITOR VIRTUAL RESOURCE Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

Table 29 describes the MONITOR VIRTUAL SUMMARY statement.

Table 29: MONITOR VIRTUAL SUMMARY Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

Table 30 describe the SET SESSION RATE statement.

Table 30: SET SESSION RATE Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

2 (sample_rate)

setShort

Value of the sample interval

3 (local_change)

setString

Types of session to which this rate change applies

Note:  Nullable

Table 31 describes the TDWM STATISTICS statement.

Table 31: TDWM STATISTICS Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

2 (request_flag)

setShort

Indicates the type of request

Table 32 describes the TDWM SUMMARY statement.

Table 32: TDWM SUMMARY Statement

Parameter

PreparedStatement

Description

1 (mon_ver_id)

setShort

Monitor software version id

Raw Connection

Overview

The Teradata JDBC Driver provides a Raw Connection feature that is analogous to CLI's buffer mode. An application obtains a Raw Connection by specifying the TYPE=RAW connection parameter.

When a Java application uses a Raw Connection, the application is responsible for composing the entire request message as a Java byte array, and the Teradata JDBC Driver provides the entire response message from the Teradata Database to the application as another Java byte array.

Creating a Raw Connection

The TYPE=RAW connection parameter must be specified in order to obtain a Raw Connection.

Connection con = DriverManager.getConnection ("jdbc:teradata://mysystem/TYPE=RAW", "guest", "please") ;

Other connection parameters can be specified in addition to TYPE=RAW, such as the TMODE and LOG connection parameters.

Connection con = DriverManager.getConnection ("jdbc:teradata://mysystem/TYPE=RAW,TMODE=TERA,LOG=DEBUG", "guest", "please") ;

Using a Raw Connection

A Raw Connection can only provide a PreparedStatement object. A Raw Connection cannot provide a regular Statement object, and cannot provide a CallableStatement object. The application must use the Connection prepareStatement method.

The application must specify null as the argument for the Raw Connection's prepareStatement method, because the Teradata JDBC Driver does not process SQL request text for a Raw Connection. That is the application's responsibility.

The PreparedStatement setBytes method is the only data binding method supported for a Raw Connection's PreparedStatement object. The application must bind a byte array as parameter 1 (one). The bound byte array must be an entire request message, including the message header and the message body. The Teradata JDBC Driver automatically sets the Session Number and the Authentication Value in the request message header, but the application is responsible for setting all the other message header fields.

The PreparedStatement executeQuery method is the only method supported for sending the request message to the Teradata Database.

            byte [] abyRequestMsg = new byte [nRequestMsgSize] ;
            // ... application composes request message ...
            PreparedStatement ps = con.prepareStatement (null) ;
            try {
              ps.setBytes (1, abyRequestMsg) ;
              ResultSet rs = ps.executeQuery () ;
              try {
                rs.next () ;
                byte [] abyResponseMsg = rs.getBytes (1) ;
                // ... application processes response message ...
              } finally {
                rs.close () ;
              }
            } finally {
              ps.close () ;
            }
            

The PreparedStatement executeQuery method returns immediately after sending the request message to the Teradata Database, and does not wait for the Teradata Database to respond. This behavior provides the maximum control and flexibility to the application. The application can choose to send an Asynchronous Abort Message.

The ResultSet next method does a blocking socket read, and waits until the Teradata Database returns the response message. A Raw Connection's ResultSet contains only one row and one column value.

The ResultSet getBytes method is the only data retrieval method supported for a Raw Connection's ResultSet object. The application must request column value 1 (one). The returned byte array is the entire response message from the Teradata Database. The application is wholly responsible for interpreting the response message, including any Error or Failure parcels.