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.

Parameter

Description

ACCOUNT

The account to be charged on network-attached clients. Maximum length of account id is 30 characters.

CHARSET

Character set for a particular session to the Teradata Database. The supported character sets include:

  • 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

To use UTF16 with Kerberos or Lightweight Directory Access Protocol (LDAP) authentication, the Teradata JDBC Driver must be Version 3.3 or later, and the Teradata Database must be V2R6.1 or later.

This parameter is supported for the Default Connection for Java Stored Procedures. Only three character sets are supported for Java Stored Procedures: ASCII, UTF16, and UTF8.

The default value is ASCII.

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 beginning with Teradata JDBC Driver version 15.00.00.16.

COMPAT_DBS

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option is required if the following options are specified:

COMPAT_ISAUTOINC COMPAT_ISCURRENCY COMPAT_ISSIGNED COMPAT_ISSEARCH COMPAT_ISREADONLY COMPAT_ISWRITABLE COMPAT_ISDEFWRIT COMPAT_GETSCHEMA COMPAT_GETTABLE

to call the following ResultSetMetaData methods:

isAutoIncrement() isCurrency() isSigned() isSearchable() isReadOnly() isWritable() isDefinitelyWritable() getSchemaName() getTableName().

Otherwise, DriverManager.getConnection() throws an SQLException.

COMPAT_ISAUTOINC

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option provides the default return value for the method call: resultSetMetaData.isAutoIncrement().

When COMPAT_DBS=true, the values from the Teradata Database are used if provided by the database; the COMPAT_ISAUTOINC value is used only when the database does not provide the value.

When COMPAT_DBS=false, this default COMPAT_ISAUTOINC value is always used, regardless of whether the database returns the data.

COMPAT_ISCURRENCY

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option provides the default return value for the method call: resultSetMetaData.isCurrency().

When COMPAT_DBS=true, the values from the Teradata Database are used if provided by the database; the COMPAT_ISCURRENCY value is used only when the database does not provide the value.

When COMPAT_DBS=false, this default COMPAT_ISCURRENCY value is always used, regardless of whether the database returns the data.

COMPAT_ISSIGNED

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option provides the default return value for the method call: resultSetMetaData.isSigned().

When COMPAT_DBS=true, the values from the Teradata Database are used if provided by the database; the COMPAT_ISSIGNED value is used only when the database does not provide the value.

When COMPAT_DBS=false, this default COMPAT_ISSIGNED value is always used, regardless of whether the database returns the data.

COMPAT_ISSEARCH

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option provides the default return value for the method call: resultSetMetaData.isSearchable().

When COMPAT_DBS=true, the values from the Teradata Database are used if provided by the database; the COMPAT_ISSEARCH value is used only when the database does not provide the value.

When COMPAT_DBS=false, this default COMPAT_ISSEARCH value is always used, regardless of whether the database returns the data.

COMPAT_ISREADONLY

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option provides the default return value for the method call:

resultSetMetaData.isReadOnly()

When COMPAT_DBS=true, the values from the Teradata Database are used if provided by the database; the COMPAT_ISREADONLY value is used only when the database does not provide the value.

When COMPAT_DBS=false, this default COMPAT_ISREADONLY value is always used, regardless of whether the database returns the data.

COMPAT_ISWRITABLE

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option provides the default return value for the method call: resultSetMetaData.isWritable().

When COMPAT_DBS=true, the values from the Teradata Database are used if provided by the database; the COMPAT_ISWRITABLE value is used only when the database does not provide the value.

When COMPAT_DBS=false, this default COMPAT_ISWRITABLE value is always used, regardless of whether the database returns the data.

COMPAT_ISDEFWRIT

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option provides the default return value for the method call:

resultSetMetaData.isDefinitelyWritable()

When COMPAT_DBS=true, the values from the Teradata Database are used if provided by the database; the COMPAT_ISDEFWRIT value is used only when the database does not provide the value.

When COMPAT_DBS=false, this default COMPAT_ISDEFWRIT value is always used, regardless of whether the database returns the data.

COMPAT_GETSCHEMA

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option provides the default return value for the method call:

resultSetMetaData.getSchemaName()

When COMPAT_DBS=true, the values from the Teradata Database are used if provided by the database; the COMPAT_GETSCHEMA value is used only when the database does not provide the value.

When COMPAT_DBS=false, this default COMPAT_GETSCHEMA value is always used, regardless of whether the database returns the data.

COMPAT_GETTABLE

This parameter is only needed for Teradata Database V2R6.1 and earlier.

This option provides the default return value for the method call:

resultSetMetaData.getTableName().

When COMPAT_DBS=true, the values from the Teradata Database are used if provided by the database; the COMPAT_GETTABLE value is used only when the database does not provide the value.

When COMPAT_DBS=false, this default COMPAT_GETTABLE value is always used, regardless of whether the database returns the data.

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.

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 Inet Address Caching section of this document.

COP

This parameter determines whether COP discovery is performed.

COP=ON 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.

The default is COP=ON.

DATABASE

DATABASE=default database name

This parameter is used when the application requires that a default database is set at logon time.

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.

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.

The DDSTATS connection parameter is available beginning with Teradata JDBC Driver version 13.00.00.25.

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.

Note:  Beginning with Teradata Database 15.0 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 available beginning with Teradata JDBC Driver version 13.00.00.26.

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.

The FINALIZE_AUTO_CLOSE connection parameter is available beginning with Teradata JDBC Driver version 14.00.00.08.

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.

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

LOB_SUPPORT

LOB_SUPPORT=ON enables Large Object (LOB) support when connecting to a Teradata Database. The default is ON.

This switch must be turned ON if the application:

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

Turning LOB_SUPPORT ON forces the application to have no more than 16 open requests at one time, including any SQL request–insert, delete, and update.

This parameter is supported for the Default Connection for Java External Stored procedures.

See Large Object Interface if you receive "Response limit exceeded" errors.

Turning LOB_SUPPORT OFF while using 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, an SQLWarning is returned to indicate the downgrade.

LOB_TEMP_TABLE

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.

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.

LOGDATA

This represents additional data needed by a mechanism. This is where information such as a secure token, Distinguished Name, or a domain/realm name can be passed for processing. LOGDATA use varies with the mechanism selected. LOGDATA is not used with the TD1 or TD2 mechanisms.

  • KRB5 – Can contain 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

  • LDAP – A Lightweight Directory Access Protocol (LDAP) LOGDATA can contain spaces, commas, and single quotes. When using this parameter with a DriverManager getConnection method, the LDAP LOGDATA parameter is always enclosed in single quotes. Within that string, a double-single quote can be used to represent an instance of a single quote. When setting 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.

LOGMECH

This is the Logon Mechanism. It determines both the sort of authentication used and the type of encryption to support on the connection. More detail on the mechanisms supported is available in Security Administration.

  • TD1 – Teradata Method 1
  • TD2 – Teradata Method 2 (stronger encryption than method 1)
  • KRB5 – Kerberos V5
  • LDAP – Lightweight Directory Access Protocol

Use a user-defined name in cases where the user-defined methods have been created.

In the case where no LOGMECH is specified, the Teradata JDBC Driver uses the local default mechanism. If this default doesn't exist, the default mechanism, as specified by the Teradata Database, is used.

In the case where a LOGMECH value is requested that cannot be supported, an SQL exception is thrown.

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.

PARTITION

Specifies the Teradata Database partition for the Connection. The default is DBC/SQL.

  • DBC/SQL – directs the Teradata JDBC Driver to connect to the standard DBC/SQL partition.
  • MONITOR – directs the Teradata JDBC Driver to connect to the Monitor partition.

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 connection parameter is available beginning with Teradata JDBC Driver version 14.00.00.13.

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.

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.

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 connection parameter is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.12.

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.

SESSIONS

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

The default value is 8 or the number of AMPs if the number of AMPs is less than 8.

Note:  It is generally recommended to use the default value; that is, omit the SESSIONS parameter and let the Teradata JDBC Driver create the appropriate number of FastLoad or FastExport connections.

SP_SPL

This parameter is only used when creating or replacing Teradata stored procedures.

This parameter is supported for the Default Connection for Java External Stored procedures.

  • SPL – The Teradata Database will store Stored Procedure Language (SPL) source text when a stored procedure is created using this connection. SPL is the default value.
  • NOSPL – The Teradata Database will not store Stored Procedure Language (SPL) source text. If a stored procedure is created without source text, the SHOW PROCEDURE SQL statement will returns an error/failure.

STRICT_ENCODE

Specifies whether STRICT_ENCODE is ON or OFF. 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 connection parameter is available beginning with Teradata JDBC Driver version 14.10.00.18.

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. The default, if omitted, is a TCP 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. 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 Teradata Database Response Message size. The Teradata JDBC Driver always notifies the Teradata Database that it should use its maximum supported Response Message size, which is 1MB for Teradata Database V2R6.0 and later, and is 64KB for Teradata Database V2R5.1 and earlier.

 

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.

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

Refer to SQL Request and Transaction Processing for more information regarding the ANSI and Teradata transaction modes.

TNANO

TSNANO

TNANO is the fractional seconds precision for the time datatype. If this value is set, then a preparedStatement.setTime() results in a time with the specified precision. For example, if TNANO is set to 3, then 10:02:30 is sent to the database as 10:02:30.000. This the same with TSNANO, except that it is used for timestamps.

TNANO and TSNANO parameters support the Default Connection for Java External Stored procedures.

If using stored procedures with time or timestamp parameters, then these URL parameters are to match the fractional second precision of the stored procedure parameters.

Example URL jdbc:teradata://whomooz/TSNANO=6,TNANO=3

The TNANO and TSNANO connection parameters are ignored for JDBC FastLoad and FastExport 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.

TYPE

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

  • DEFAULT – directs the Teradata JDBC Driver to use the standard protocol for all SQL statements
  • 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
  • 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 version 13.00.00.26.
  • 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
  • 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.

The default is DEFAULT.

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 connection parameter is available beginning with Teradata JDBC Driver version 14.00.00.13.

USEXVIEWS

The USEXVIEWS parameter instructs the Teradata JDBC Driver to use X views for retrieval of DatabaseMetaData instead of non-X views (USEXVIEWS=ON). The X views of various tables used to obtain metadata provide extra security checks and generally return less data than a corresponding selection of a non-X view. These security checks do have 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.

The default is USEXVIEWS=OFF.

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.

Refer to TeraDataSource Class for additional information.

DataSources are accessed with the JNDI. For more information, go to:

http://java.sun.com/products/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 Defaults Warning

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

Session Parameters That Must Not be Changed

Session parameters that MUST NOT BE CHANGED include:

COP Discovery

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

The Teradata JDBC Driver first finds all defined COPs. The driver starts with COP1, which is appended to the database hostname, and then proceeds with COP2, COP3, ..., COPn until an undefined name is reached.

For the first access to that database, 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.

If no HostNameCOPnnn names are defined in the Domain Name System (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, a 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 HostNameCOPnnn names are defined in DNS, or specify the COP=OFF connection parameter.

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:

InetAddress Caching

The InetAddress class caches to store successful as well as unsuccessful host name resolutions. The positive caching is there to guard against DNS spoofing attacks; while the negative caching is used to improve 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 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.

LogonSource Format

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 Data Dictionary System Views LogonSource column, which is included in system views such as DBC.SessionInfo and DBC.LogOnOff.

Note:  All LogonSource values provided by Teradata JDBC Driver and other clients are entered into 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. 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 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         2         3         4         5         6         7    c

12345678901234567890123456789012345678901234567890123456789012345678901   o

                                                                          n b

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

(TCP/IP) 11AB 153.64.135.140  CHARON;CHARONCOP1/153.64.116.144:1025       i l

(TCP/IP) 11AB 153.64.135.140  CHARON.SANDIEGOCA.COM;CHARONCOP1.SAND       n o

----------------------------- -----------------------------------------   u w

                                       1         2         3         4    e

                              12345678901234567890123456789012345678901   d

                                       Truncated to the space          

                                  remaining in the 97 chars, after     

                                 the subsequent fields are composed    

 c f   7       8         9        10        11        12          

 o r   234567890123456789012345678901234567890123456789012345678  

 n o                                                              

 t m  <------------------set by client------------------------->  

 i      CID=C2A132   ROOTUSER JDBC03.02.00.00;1.4.2_01   01 LSS   

 n a    CID=C2A132   ROOTUSER JDBC03.02.00.00;1.4.2_01   01 LSS   

 u b    ------------ -------- -------------------------- ------   

 e o             1                     1         2                

 d v    123456789012 12345678 12345678901234567890123456 123456   

   e                                                              

         Will be     Trunc'ed    Trunc'ed to 26 chars    Hardcoded

         12 chars   to 20 chars      (may be less)       6 chars  

         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 "cs4300s1" is: CS4300S1;CS4300S1COP1/153.64.116.95:1025

An example truncated value for this field when an application specifies a Teradata Database hostname of "cs4300s1.sandiegoca.teradata.com" is: CS4300S1.SANDIEGOCA.TERADATA.COM;CS4300S1COP1.SAN

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 the connection object. 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 all the session settings, and indicates which initial settings are specified with a CREATE/MODIFY USER clause or a JDBC Connection parameter.

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

Current transaction isolation

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL

-

-

Yes

Collation sequence

SET SESSION COLLATION

COLLATION

-

No

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

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'

Note:  The Teradata JDBC Driver RUNSTARTUP=ON connection parameter must be specified to execute the user’s STARTUP SQL request after logon.

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

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 code for the type of database being used. 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

Escape clauses for date and time literals use the following syntax:

{literal-type 'value'}

where literal-type is one of the types listed in the following table.

Date and Time Literals

Literal Type

Meaning

Format

d

Date

yyyy-mm-dd

t

Time

hh:mm:ss

ts

Timestamp

yyyy-mm-dd hh:mm:ss[.f...]

Scalar Functions

The syntax for JDBC scalar functions is the following:

{fn function_name()}

The following tables list the scalar functions which are supported by both the X/Open Call-Level Interface (CLI) for JDBC and the Teradata JDBC Driver.

Numeric Functions

ABS(number)

ACOS(float)

ASIN(float)

ATAN(float)

ATAN2(float1, float2)

COS(float)

COT(float)

EXP(float)

LOG(float)

MOD(integer1, integer2)

PI()

SIN(float)

SQRT(float)

TAN(float)

String Functions

CONCAT(string1, string2)

LCASE(string)

LENGTH(string)

LOCATE(string1, string2[, start])

LTRIM(string)

RTRIM(string)

SOUNDEX(string)

SUBSTRING(string, start, length)

UCASE(string)

System Functions

DATABASE()

IFNULL(expression, value)

USER()

Time and Date Functions

CURDATE()

CURTIME()

DAYOFMONTH(date)

HOUR(time)

MINUTE(time)

MONTH(date)

NOW()

SECOND(time)

TIMESTAMPADD(interval, count, timestamp)

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

YEAR(date)

The Teradata Database supports the following JDBC intervals for use with the TIMESTAMPADD and TIMESTAMPDIFF functions:

Conversion Functions

Escape clauses for data type conversion use the following syntax:

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:

Marking SQL as Untrusted

The escape clause syntax to mark SQL as untrusted is:

{fn teradata_untrusted} untrustedSQL

This function is used to mark SQL 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.

With either command, the query band can be specified as an SQL string literal enclosed within single quotes. With SET QUERY_BAND FOR TRANSACTION, it is strongly recommended to specify the query band using a question-mark parameter marker and the PreparedStatement.setString method.

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 session and transaction query band name/value pairs. The second method returns the query band value for the specified name.

With both methods, transaction query band values take precedence over session query band values. If the same query band name is active for both session and transaction, only the transaction query band value will be returned.

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

Teradata Database 13.10 supports Trusted Sessions Enhanced Security that 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 version 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 version 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 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( ...

// Supported 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

 

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 version 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

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

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]

Select access on DBC.UDTInfo

Uses HELP COLUMN and HELP TYPE to obtain information about view columns

getCrossReference

Select access on DBC.All_RI_Parents[V][X]

getExportedKeys

Select access on DBC.All_RI_Parents[V][X]

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

Teradata JDBC Driver 14.00.00.23 and earlier use HELP STATISTICS and HELP INDEX

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

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

Select access on DBC.UDTTransform

Select access on DBC.UDFInfo

ResultSetMetaData Methods

All ResultSetMetaData methods are supported with Teradata Database V2R6.2 and later. With Teradata Database V2R6.1 and earlier releases, the Teradata JDBC Driver throws an SQL Exception when the following nine ResultSetMetaData methods are invoked:

These nine methods are supported when connected to Teradata Database V2R6.2 and later, but are not supported with Teradata Database V2R6.1 and earlier releases. To use these methods in your applications with Teradata Database V2R6.1 and earlier releases, supply the following URL options:

COMPAT_DBS=true/false

COMPAT_ISAUTOINC=true/false

COMPAT_ISCURRENCY=true/false

COMPAT_ISSIGNED=true/false

COMPAT_ISSEARCH=true/false

COMPAT_ISREADONLY=true/false

COMPAT_ISWRITABLE=true/false

COMPAT_ISDEFWRIT=true/false

COMPAT_GETSCHEMA=ReturnValue

COMPAT_GETTABLE=ReturnValue

Refer to the tables in Making a Teradata Database Connection for more details about how to specify these URL options in the driver connection URL string.

The URL option COMPAT_DBS is always required to use any of these nine methods; the corresponding COMPAT_xxx is also needed. For example, to use resultSetMetaData.isAutoIncrement() method, the URL options COMPAT_DBS and COMPAT_ISAUTOINC are required.

COMPAT_DBS=true specifies that the values from the Teradata Database are used if provided by the database; the COMPAT_xxx values are used only when the database does not provide the values. COMPAT_DBS=false specifies that the other COMPAT_xxx values are always used, regardless of whether the database returns the data.

Note:  During connection time, if URL options COMPAT_xxx values are used but COMPAT_DBS is not being used, JDBC driver throws a SQLException. When invoking these methods, both COMPAT_DBS and the corresponding COMPAT_xxx URL options are required; otherwise, a SQLException is thrown.

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.ResultSet resultSet), various ResultMetaData methods are called by this constructor.

All ResultSetMetaData methods are supported with Teradata Database V2R6.2 and later. With Teradata Database V2R6.1 and earlier releases, the Teradata JDBC Driver will throw SQLException when the following six ResultSetMetaData methods are invoked through JdbcRowSetImpl(java.sql.ResultSet resultSet):

To use the constructor, JdbcRowSetImpl(java.sql.ResultSet resultSet), supply the following URL options:

COMPAT_DBS=true/false

COMPAT_ISAUTOINC=true/false

COMPAT_ISCURRENCY=true/false

COMPAT_ISSIGNED=true/false

COMPAT_ISSEARCH=true/false

COMPAT_GETSCHEMA=ReturnValue

COMPAT_GETTABLE=ReturnValue

Refer to the section ResultSetMetaData Methods for more details about how to specify these URL options in the driver connection URL string.

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 methods that allow selection of the mechanism, and allow data encryption to be turned on or off at the connection level. The selected mechanism determines the level of encryption that is used on the connection. See Security Administration for details on these mechanisms.

Single Sign On (SSO) is only supported with the Kerberos logon mechanism. Data encryption is supported with all logon mechanisms.

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

Prerequisites

Logons with any mechanism other than TD1 and 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:

Table 15 lists the prerequisites for each of the methods.

Table 15: Prerequisites 

Method

Description

Teradata Method 1

Does not require any special setup, and can be used immediately

Teradata Method 2

Does not require any special setup, and can be used immediately

Kerberos

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

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.

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

For Windows, ensure that each Windows user has a krb5.ini file in the C:\WINNT directory. For Linux, the file /etc/krb5.conf supports all users. The system administrator must set up a krb5.ini file in the C:\WINNT directory for each Windows user. It 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. An example of this file follows.

This file is an example and must be modified to reflect the actual domain, realm, and 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
}

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 Query Director

When using Kerberos and Teradata Query Director (TQD), 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 TQD to route authentication requests to the Teradata Database.

Set the forwardable=true option in the C:\WINNT\krb5.ini (Windows) or /etc/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 requires the following Login Configuration information:

com.sun.security.jgss.initiate
{
  com.sun.security.auth.module.Krb5LoginModule sufficient useTicketCache=true;
};
other
{
  com.sun.security.auth.module.Krb5LoginModule required;
};

The information can reside in a file (to be used selectively) or be set for all users by modifying the java.security file.

To put the information in a file, specify the location of that file with the JVM directive:

-Djava.security.auth.login.config

For example, if the Login Configuration file resided in a file called TeraJDBC.config in the working directory, specify:

-Djava.security.auth.login.config=TeraJDBC.config

as a JVM directive to use this file.

To make it a system-wide setting, do the following:

Search for login.config.url to see where this property is to reside. The value specifies where the file holding the configuration settings can be found. The values of n in login.cfg.url.n must be consecutively numbered.

The following example shows only one login.config.url value. In this example, the configuration file is located at C:\dmr\TeraJDBC.config.

login.config.url.1=file:C:/dmr/TeraJDBC.config

This URL always uses forward slashes.

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 with Teradata Database. Versions of Teradata Database earlier than V2R6.2.0.19 do not support question-mark parameter markers.

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:

String sInstallJar = "call sqlj.install_jar(‘cj!SampleXJSP.jar’, ‘SampleXJSP’,0);";

stmt.executeUpdate(sInstallJar);

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.

  • CHARSET=charset name   Only ASCII, UTF8, and UTF16 are supported. ASCII is the default.
  • LOB_SUPPORT={ON/OFF}
  • LOG={ERROR/TIMING/INFO/DEBUG}
  • SP_SPL={SPL/NOSPL}
  • TNANO=number of fractional digits
  • TSNANO=number of fractional digits

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.

Teradata Database 12.0 and 13.0 do not support Java Stored Procedures compiled with JDK 6.0. Only Java Stored Procedures compiled with JDK 1.4.2 or JDK 5.0 are supported.

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 version 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 Field Separator (FS) control character ('\u001c') 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 PARTITION=MONITOR in the URL connection string. When enabled, the Monitor protocol is used with the Teradata Database for all SQL statements. To qualify for JDBC Monitor, the SQL statement must meet the following criteria:

Considerations When Using JDBC Monitor

JDBC Data Types Supported by JDBC Monitor

Not all of the JDBC data types supported by the Teradata JDBC Driver are supported by JDBC Monitor; for example, BLOB and CLOB. Likewise, not all of the JDBC data type conversions supported by the Teradata JDBC Driver are supported by 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.