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:
This section lists the necessary steps for a standalone Java application to use the Teradata JDBC Driver.
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.
import java.sql.*;
Class.forName("com.teradata.jdbc.TeraDriver");
To access a 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 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 database. |
One connection parameter |
jdbc:teradata://DatabaseServerName/ParameterName=Value
Connection parameters are optional. The first ParameterName is separated from the DatabaseServerName by a forward slash character. |
Two or more connection parameters |
jdbc:teradata://DatabaseServerName/ParameterName=Value,ParameterName=Value
When two or more connection parameters are specified, the parameters must be separated by commas. Enclose the value in single-quotes if the value contains a comma.
ParameterName is a connection parameter, and Value is a value for the parameter, as described in the next table.
|
The following table lists the Teradata JDBC Driver connection URL parameters and values. Beginning with Teradata JDBC Driver 16.00.00.28, the Teradata JDBC Driver validates URL connection parameters and throws SQLException for an invalid parameter name and/or invalid value.
Parameter |
Description |
|||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Specifies an account string to override the default account string defined for the database user. Accounts are used by the database for workload management and resource usage monitoring. Beginning with Teradata Database 14.10, the maximum length of an account string is 128 characters. The maximum length of an account string is 30 characters for Teradata Database 14.0 and earlier releases. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 15.10.00.08. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
Specifies a command to override the default command to open the browser for Browser Authentication, which is chosen with the LOGMECH=BROWSER connection parameter. Browser Authentication is supported for Windows and macOS. Browser Authentication is not supported for other operating systems.
The specified command must include a placeholder token, literally specified as
This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.01. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.01. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.01. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.01. |
||||||||||||||||
Specifies the number of seconds to wait before closing the browser tab after Browser Authentication is completed. The behavior is under the browser's control, and not all browsers support automatic closing of browser tabs. Typically, the tab used to log on will remain open indefinitely, but the second and subsequent tabs will be automatically closed. When the BROWSER_TAB_TIMEOUT connection parameter is omitted, the default is 5 seconds. Specify 0 (zero) to close the tab immediately. Specify -1 to turn off automatic closing of browser tabs. Browser Authentication is supported for Windows and macOS. Browser Authentication is not supported for other operating systems. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.20.00.09. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.20.00.09. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.20.00.09. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.20.00.09. |
||||||||||||||||
Specifies the number of seconds that the Teradata JDBC Driver will wait for Browser Authentication to complete. When the BROWSER_TIMEOUT connection parameter is omitted, the default is 180 seconds (3 minutes). Browser Authentication is supported for Windows and macOS. Browser Authentication is not supported for other operating systems. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.17. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.17. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.17. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.17. |
||||||||||||||||
Specifies the session character set for encoding and decoding character data transferred to and from the Teradata Database. The following session character sets are supported:
The default value is ASCII. Beginning with Teradata Database V2R6.1 and Teradata JDBC Driver 3.3, UTF16 can be used in conjunction with Kerberos or Lightweight Directory Access Protocol (LDAP) authentication. This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures, but only ASCII, UTF8, or UTF16 may be specified. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is available for JDBC FastLoad CSV connections, but only ASCII or UTF8 may be specified. This parameter is available for JDBC Monitor connections, but only certain character sets may be specified, depending on the Monitor version. Refer to JDBC Monitor for details. This parameter is available for Raw connections. |
||||||||||||||||
CHATTER values are ON (the default) or OFF.
The CHATTER connection parameter is available with Teradata JDBC Driver versions 15.00.00.16 through 15.10.0.24. Beginning with Teradata JDBC Driver 15.10.0.25, the CHATTER connection parameter is no longer available, and is replaced by the MAX_MESSAGE_BODY connection parameter. This parameter is not available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport connections. This parameter is available for JDBC FastLoad and JDBC FastLoad CSV connections with Teradata JDBC Driver versions 15.00.00.16 through 15.10.0.24. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the Java character set for encoding and decoding character data transferred to and from the database. This overrides the Teradata JDBC Driver's normal mapping of Teradata session character sets to Java character sets. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. Note: Not recommended for normal use. Use at your own risk. Data corruption will occur if the wrong Java character set is specified. Teradata cannot provide any guarantees of data fidelity or quality when this connection parameter is used. |
||||||||||||||||
Specifies how LOGMECH=CODE Device Code Flow will display the verification URL and code. This parameter is optional for LOGMECH=CODE and is ignored for other LOGMECH values.
This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.17. |
||||||||||||||||
Controls the behavior of the ResultSetMetaData getColumnName and getColumnLabel methods.
The JDBC escape functions This parameter is available for SQL connections beginning with Teradata JDBC Driver 16.00.00.28. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 16.00.00.28. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
CONNECT_FAILURE_TTL=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 database. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. Note: This feature applies to TCP socket connection failures and does not apply to hostname look up during COP Discovery. There is no need for such a connection parameter (for example, COP_DISCOVERY_TTL) because this can be done using a JVM setting: the Java security property networkaddress.cache.negative.ttl as defined in the InetAddress Caching section of this document. |
||||||||||||||||
Specifies whether the database should allocate a Logon Sequence Number (LSN) for this session, or associate this session with an existing LSN.
This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
Specifies whether COP Discovery is performed.
This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
Specifies how COP Discovery determines the last COP hostname.
This parameter is available for SQL connections beginning with Teradata JDBC Driver 16.00.00.28. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 16.00.00.28. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 16.00.00.28. This parameter is available for Raw connections beginning with Teradata JDBC Driver 16.00.00.28. |
||||||||||||||||
DATABASE=default database name Specifies a default database for use after logon, to override the default database defined for the database user. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is available for JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 15.10.00.08. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. Note: The DATABASE connection parameter works normally for a user with an expired password when the NEW_PASSWORD connection parameter is also specified. However, the DATABASE connection parameter has no effect for a conditional connection that is established for a user with an expired password when the NEW_PASSWORD connection parameter is not specified. In this situation, the application can submit a MODIFY USER statement to assign a new password to the user. After the new password has been set, the application can submit a DATABASE statement to change the default database for the connection. |
||||||||||||||||
Connects to the database on the specified TCP/IP port for non-HTTPS connections. The default port is 1025 for non-HTTPS connections to the database. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
DDSTATS values are ON or OFF:
This parameter is available for SQL connections beginning with Teradata JDBC Driver 13.00.00.25. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 13.00.00.25. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
ENCRYPTDATA values are ON or OFF:
Encryption algorithms are associated with Logon Mechanisms, so the LOGMECH connection parameter governs which encryption algorithm is used when data encryption occurs. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. Note: Beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.15, data encryption occurs if required by centralized administration, or if the application specifies the ENCRYPTDATA=ON connection parameter. Encryption required by centralized administration overrides the ENCRYPTDATA=OFF connection parameter. |
||||||||||||||||
Specifies the maximum number of times that JDBC FastLoad will attempt to query FastLoad Error Table 1 after a JDBC FastLoad operation. The default value is 21 attempts when this parameter is omitted. After a FastLoad operation, the database stores rejected rows in an error table. There may be a delay before the rows are available in the error table, depending on the number of rejected rows and the workload of the database system. JDBC FastLoad uses the information obtained from FastLoad Error Table 1 to compose the BatchUpdateException that indicates to the application which rows were rejected. This parameter is not available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport connections. This parameter is available for JDBC FastLoad connections beginning with Teradata JDBC Driver 16.00.00.32. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the number of milliseconds that JDBC FastLoad will wait in between attempts to query FastLoad Error Table 1 after a JDBC FastLoad operation. The default value is 500 milliseconds when this parameter is omitted. This parameter is not available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport connections. This parameter is available for JDBC FastLoad connections beginning with Teradata JDBC Driver 16.00.00.32. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the suffix for the name of FastLoad Error Table 1 created by JDBC FastLoad and JDBC FastLoad CSV. The default suffix is Refer to Considerations When Using JDBC FastLoad and Considerations When Using JDBC FastLoad CSV for more details. This parameter is not available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport connections. This parameter is available for JDBC FastLoad and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 16.00.00.31. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the suffix for the name of FastLoad Error Table 2 created by JDBC FastLoad and JDBC FastLoad CSV. The default suffix is Refer to Considerations When Using JDBC FastLoad and Considerations When Using JDBC FastLoad CSV for more details. This parameter is not available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport connections. This parameter is available for JDBC FastLoad and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 16.00.00.31. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the database name for the FastLoad error tables created by JDBC FastLoad and JDBC FastLoad CSV. When this parameter is omitted, the default behavior is to create the FastLoad error tables in the same database as the destination table being loaded. Refer to Considerations When Using JDBC FastLoad and Considerations When Using JDBC FastLoad CSV for more details. This parameter is not available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport connections. This parameter is available for JDBC FastLoad and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 16.00.00.31. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies a field separator for use with JDBC FastLoad CSV only. The default separator is ',' (comma). Refer to the section Field Separators Supported by JDBC FastLoad CSV for more details. This parameter is not available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport or JDBC FastLoad connections. This parameter is available for JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 13.00.00.26. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
FINALIZE_AUTO_CLOSE values are OFF (default) or ON:
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 database after sending a message to the database to close the response spool. This parameter is available for SQL connections beginning with Teradata JDBC Driver 14.00.00.08. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 14.00.00.08. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
FLATTEN values are OFF (default) or ON:
This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.05. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 17.10.00.05. This parameter is available for JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.05. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.05. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.05. |
||||||||||||||||
GETURL_CREDENTIALS values are OFF (default) or ON:
This parameter is available for SQL connections beginning with Teradata JDBC Driver 16.00.00.28. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 16.00.00.28. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
GOVERN values are ON (default) or OFF:
This parameter is not available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Connects to the database on the specified TCP/IP port for HTTPS/TLS connections. The default port is 443 for HTTPS/TLS connections to the database. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.07. |
||||||||||||||||
Uses the specified proxy server for HTTPS/TLS connections to the database and to Identity Provider endpoints. Specify the proxy server with a URL that must begin with The driver connects to the proxy server using a non-TLS HTTP connection, then uses the HTTP CONNECT method to establish an HTTPS/TLS connection to the destination. When this parameter is omitted, the default behavior beginning with Teradata JDBC Driver 20.00.00.12 is to use the Java system proxy settings specified with This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.12. |
||||||||||||||||
Specifies the proxy server password for the proxy server identified by the HTTPS_PROXY parameter. This parameter may only be specified in conjunction with the HTTPS_PROXY parameter. When this parameter is omitted, no proxy server password is provided to the proxy server identified by the HTTPS_PROXY parameter. This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.12. |
||||||||||||||||
Specifies the proxy server username for the proxy server identified by the HTTPS_PROXY parameter. This parameter may only be specified in conjunction with the HTTPS_PROXY parameter. When this parameter is omitted, no proxy server username is provided to the proxy server identified by the HTTPS_PROXY parameter. This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.12. |
||||||||||||||||
Specifies the JSON Web Signature (JWS) algorithm to sign the JWT Bearer Token for client authentication. This parameter is optional for LOGMECH=BEARER and is ignored for other LOGMECH values.
This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.17. |
||||||||||||||||
Specifies the file name of the X.509 certificate PEM file that contains the public key corresponding to the private key from JWS_PRIVATE_KEY. This parameter is optional for LOGMECH=BEARER and is ignored for other LOGMECH values.
This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.17. |
||||||||||||||||
Specifies the file name of the PEM or JWK file containing the private key to sign the JWT Bearer Token for client authentication. This parameter is required for LOGMECH=BEARER and is ignored for other LOGMECH values.
This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.17. |
||||||||||||||||
Many DatabaseMetaData methods have arguments to specify LIKE-predicate patterns to match object names. These method arguments have a name ending in the word Pattern, such as schemaPattern and tableNamePattern. Within a pattern, the percent-sign character " % " is a wildcard that matches any sequence of zero or more characters, and the underscore character " _ " is a wildcard that matches any single character. To match a literal wildcard character in an object name, the wildcard character must be escaped (preceded) by a backslash character " \ ". LITERAL_UNDERSCORE=ON will automatically escape all unescaped underscore characters in DatabaseMetaData method pattern arguments. The default is OFF. This parameter is available for SQL connections beginning with Teradata JDBC Driver 15.10.00.14. This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 15.10.00.14. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
LOB_SUPPORT=OFF disables LOB support and related features. LOB_SUPPORT=ON (the default) enables Large Object (LOB) support and a collection of related features. LOB_SUPPORT must be enabled if the application:
When LOB_SUPPORT is ON (the default), the application is limited to 16 open responses from the database. See Working with LOBs if you receive database Error 3130 "Response limit exceeded". Turning off LOB_SUPPORT and subsequently attempting to use scrollable result sets or updatable result sets causes the driver to downgrade the result set to forward-only (scrollable result sets) or read-only (updatable result sets). In addition, a SQLWarning is returned to indicate the downgrade. This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the name of a table with the following columns: id integer, bval blob, cval clob The user or the application must have previously created the table. The Teradata JDBC Driver will not automatically create the table. See Updatable LOBs for more information. This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the logging level (verbosity) for the connection. Logging is always enabled. The logging levels are listed in order from terse to verbose.
This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
Specifies additional data needed by a logon mechanism, such as a secure token, Distinguished Name, or a domain/realm name. LOGDATA values are specific to each logon mechanism. LOGDATA is not used with the TD2 mechanism.
When the username is specified in the LOGDATA parameter, then the username must not be specified with the DriverManager.getConnection method, and the username must not be specified with the USER connection parameter. When the password is specified in the LOGDATA parameter, then the password must not be specified with the DriverManager.getConnection method, and the password must not be specified with the PASSWORD connection parameter. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
Specifies the Logon Mechanism, which determines the connection's authentication and encryption capabilities. Refer to the Teradata Vantage™ Security Administration reference for more information about logon mechanisms. The database user must have the "logon with null password" permission to log on using LOGMECH=KRB5 Single Sign On (SSO) or any of the OpenID Connect (OIDC) methods: BEARER, BROWSER, CODE, CRED, JWT, ROPC, and SECRET.
When the LOGMECH parameter is omitted, the Teradata JDBC Driver uses the local default mechanism. If no local default mechanism is defined, then the Teradata JDBC Driver uses the default mechanism indicated by the database. Not all logon mechanisms are available in all environments. If you specify a logon mechanism that is not available in your environment, then the JDBC connection attempt will fail, and a SQLException will be thrown. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
Specifies an existing Logon Sequence Number (LSN) to associate this session with. This connection parameter must be used in conjunction with the CONNECT_FUNCTION=2 connection parameter. The database only permits sessions for the same user to share an LSN. A logon sequence number groups multiple sessions together for workload management.
Using a logon sequence number is a three-step process.
First, a control session is established with CONNECT_FUNCTION=1, which directs the database to allocate a logon sequence number.
Second, the logon sequence number is obtained from the control session by calling the This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
Controls the behavior of the
This parameter has no effect when StatementInfo parcel support is unavailable. The JDBC escape function This parameter is available for SQL connections beginning with Teradata JDBC Driver 16.00.00.28. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata JDBC Driver 16.00.00.28. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
MAX_MESSAGE_BODY=size in bytes Specifies the maximum Response Message size in bytes. This connection parameter is available beginning with Teradata JDBC Driver 15.10.0.25.
This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is not available for Raw connections. When the MAX_MESSAGE_BODY connection parameter is specified, the maximum Response Message size is the smaller of the MAX_MESSAGE_BODY value and the maximum database Response Message size. Note: Performance may be hurt by specifying a smaller MAX_MESSAGE_BODY value than the default maximum Response Message size. This is not recommended. Performance may be improved by specifying a larger MAX_MESSAGE_BODY value than the default maximum Response Message size, but JVM memory consumption will increase. You must ensure that sufficient JVM memory is available. |
||||||||||||||||
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 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:
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:
This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the OpenID Connect (OIDC) Client ID to use for Browser Authentication and other OIDC methods.
When the OIDC_CLIENTID connection parameter is omitted, the default Client ID comes from the database's Browser Authentication is supported for Windows and macOS. Browser Authentication is not supported for other operating systems. This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.17. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.17. |
||||||||||||||||
Specifies the OpenID Connect (OIDC) scope to use for Browser Authentication and other OIDC methods.
When the OIDC_SCOPE connection parameter is omitted, the default scope is Browser Authentication is supported for Windows and macOS. Browser Authentication is not supported for other operating systems. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.25. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.25. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.25. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.25. |
||||||||||||||||
Specifies the kind of OIDC token to use for Browser Authentication and other OIDC methods. When the OIDC_TOKEN connection parameter is omitted, the access_token is used. Specify OIDC_TOKEN=id_token to use the id_token instead of the access_token. Browser Authentication is supported for Windows and macOS. Browser Authentication is not supported for other operating systems. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.25. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.25. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.25. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.25. |
||||||||||||||||
Specifies the database partition for the Connection.
This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport, JDBC FastLoad, or JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
Specifies the password for the Connection when the DriverManager.getConnection method's password argument is omitted or null. This connection parameter is primarily intended for use with the single-argument DriverManager.getConnection(String url) method. When the two-argument DriverManager.getConnection(String url, Properties info) method is used, a non-null "password" property value in the Properties info argument will override this connection parameter. When the three-argument DriverManager.getConnection(String url, String user, String password) method is used, a non-null password argument will override this connection parameter. This parameter is available for SQL connections beginning with Teradata JDBC Driver 14.00.00.13. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 14.00.00.13. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 14.00.00.13. This parameter is available for Raw connections beginning with Teradata JDBC Driver 14.00.00.13. |
||||||||||||||||
Specifies whether the database performs a prepare operation when a PreparedStatement or CallableStatement is created.
This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies a matching pattern for hostnames and addresses to bypass the proxy server identified by the HTTPS_PROXY parameter. This parameter may only be specified in conjunction with the HTTPS_PROXY parameter. Separate multiple hostnames and addresses with a vertical bar When this parameter is omitted, the default pattern is When the proxy server identified by the HTTPS_PROXY parameter is bypassed, then the Java system proxy settings are used. If no Java system proxy settings are defined, then a direct connection is made to the destination. This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.12. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.12. |
||||||||||||||||
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.
This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is available for Raw connections. Note: Specifying this parameter will override the normal fail-fast behavior for connection failures. |
||||||||||||||||
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.
This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is available for Raw connections. Note: Specifying this parameter will override the normal fail-fast behavior for connection failures. |
||||||||||||||||
Enables Teradata Session Reconnect, and also enables automatic redriving of SQL requests interrupted by database restart. See Teradata Session Reconnect for more information.
This parameter is available for SQL connections beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.12. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections beginning with Teradata Database 14.10 and Teradata JDBC Driver 15.00.00.12. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is available for Raw connections. Note: The application is not guaranteed to receive the functionality that it solicits with the REDRIVE connection parameter.
The database determines whether the functionality is provided or not, depending on the
database |
||||||||||||||||
Specifies the RUNSTARTUP value as OFF or ON. The default is OFF.
This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 15.10.00.08. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the number of FastLoad or FastExport connections to be created, where 1 <= number of FastLoad or FastExport connections <= number of AMPs. This parameter is not available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. Note: It is recommended to omit the SESSIONS parameter and let the database determine the appropriate number of FastLoad or FastExport connections. |
||||||||||||||||
Controls whether the database and Teradata JDBC Driver use StatementInfo Parcel (SIP) to convey metadata.
This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Controls how small LOB values are received from the database. Small LOB values are pre-fetched from the database before the application explicitly reads data from Blob/Clob objects.
The default threshold is 1000 bytes when this parameter is omitted. This parameter is available for SQL connections beginning with Teradata Database 15.10 and Teradata JDBC Driver 15.10.00.05. This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. Note: Use caution when specifying this parameter. Performance may be improved by specifying a threshold larger than the default, but JVM memory consumption will increase. You must ensure that sufficient JVM memory is available. Be aware that small LOB values compete for space in each message with other non-LOB data values, and you may also need to increase the maximum message size with the MAX_MESSAGE_BODY connection parameter. |
||||||||||||||||
Controls how small LOB values are transmitted to the database.
The default threshold is 1000 bytes when this parameter is omitted. This parameter is available for SQL connections beginning with Teradata Database 15.10 and Teradata JDBC Driver 15.10.00.05. This parameter is not available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. Note: Use caution when specifying this parameter. Performance may be improved by specifying a threshold larger than the default, but JVM memory consumption will increase. You must ensure that sufficient JVM memory is available. Be aware that small LOB values compete for space in each message with other non-LOB data values, and you may also need to increase the maximum message size with the MAX_MESSAGE_BODY connection parameter. |
||||||||||||||||
Specifies behavior for creating or replacing Teradata stored procedures.
This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the file name of a PEM file that contains Certificate Authority (CA) certificates for use with SSLMODE=VERIFY-CA or VERIFY-FULL. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.07. |
||||||||||||||||
Specifies a directory of PEM files that contain
Certificate Authority (CA) certificates for use with SSLMODE=VERIFY-CA or VERIFY-FULL.
Only files with an extension of This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.07. |
||||||||||||||||
Specifies the TLS cipher for HTTPS/TLS connections. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30. Omitting this parameter is recommended. If omitted, the database and client will choose the most appropriate TLS cipher. Use this parameter only for testing the performance of the specified TLS cipher. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.07. |
||||||||||||||||
Controls TLS certificate revocation checking for HTTPS/TLS connections when SSLMODE is set to VERIFY-FULL. This parameter is ignored unless SSLMODE is set to VERIFY-FULL.
This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.02. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.02. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.02. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.02. |
||||||||||||||||
Controls Java debug logging for HTTPS/TLS connections. The default is OFF. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30.
This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.07. |
||||||||||||||||
Specifies the mode for connections to the database. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30.
This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.07. |
||||||||||||||||
Specifies the TLS protocol for HTTPS/TLS connections. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30. The default is TLSv1.2 if omitted. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.07. |
||||||||||||||||
Specifies the file name of a Java TrustStore file that contains trusted database server certificates and/or Certificate Authority (CA) certificates for use with SSLMODE=VERIFY-CA or VERIFY-FULL. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.07. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.07. |
||||||||||||||||
Specifies the password for the Java TrustStore file identified by the SSLTRUSTSTORE connection parameter. When this parameter is omitted, no password is specified for the Java TrustStore file. This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.14. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.14. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.14. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.14. |
||||||||||||||||
Specifies the type for the Java TrustStore file identified by the SSLTRUSTSTORE connection parameter. When this parameter is omitted, the Java TrustStore file is assumed to be the Java default TrustStore type. This parameter is available for SQL connections beginning with Teradata JDBC Driver 20.00.00.14. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 20.00.00.14. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 20.00.00.14. This parameter is available for Raw connections beginning with Teradata JDBC Driver 20.00.00.14. |
||||||||||||||||
Specifies behavior for encoding character data to transmit to the database. The default is OFF.
This parameter is available for SQL connections beginning with Teradata JDBC Driver 14.10.00.18. This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 14.10.00.18. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 14.10.00.18. This parameter is not available for Raw connections. Note: Specifying STRICT_ENCODE=ON may reduce performance for transmitting character data to the 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:
|
||||||||||||||||
Specifies behavior for checking connection parameter names. The default is ON.
This parameter is not available for a DataSource or ConnectionPoolDataSource. It is not possible to specify an unexpected connection parameter name for a DataSource or ConnectionPoolDataSource. This parameter is available for SQL connections beginning with Teradata JDBC Driver 17.10.00.18. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 17.10.00.18. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 17.10.00.18. This parameter is available for Raw connections beginning with Teradata JDBC Driver 17.10.00.18. |
||||||||||||||||
Specifies one or more TCP socket settings, separated by plus signs ("+"). The following TCP socket settings may be specified:
The SENDn and RECEIVEn settings are only requests, or hints, to the underlying platform/operating system's TCP stack. Some platforms/operating systems may round the requested values up or down, or may ignore the requested values entirely. The SENDn and RECEIVEn settings are separate from the maximum Response Message size. For more information, refer to the description of the MAX_MESSAGE_BODY connection parameter. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is available for Raw connections.
Examples:
Settings may be specified in any order. For example, TCP=NODELAY+KEEPALIVE is equivalent to TCP=KEEPALIVE+NODELAY. |
||||||||||||||||
Specifies the transaction mode for the connection.
See Transaction Mode for more information regarding the ANSI and Teradata transaction modes. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is available for Raw connections. |
||||||||||||||||
TNANO=number of fractional digits Specifies the fractional seconds precision for all java.sql.Time values bound to a PreparedStatement or CallableStatement and transmitted to the database as TIME or TIME WITH TIME ZONE values. This parameter can potentially increase or decrease (truncate) the number of fractional digits that are transmitted to the database. For example, if the TNANO=3 connection parameter is specified, then the java.sql.Time value 10:02:30 is transmitted to the database as the TIME value 10:02:30.000. This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
TRUSTED_SQL 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.
This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
TSNANO=number of fractional digits Specifies the fractional seconds precision for all java.sql.Timestamp values bound to a PreparedStatement or CallableStatement and transmitted to the database as TIMESTAMP or TIMESTAMP WITH TIME ZONE values. This parameter can potentially increase or decrease (truncate) the number of fractional digits that are transmitted to the database. For example, if the TSNANO=3 connection parameter is specified, then the java.sql.Timestamp value 2016-05-17 10:02:30.123456 is transmitted to the database as the TIMESTAMP value 2016-05-17 10:02:30.123. This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures. This parameter is not available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies the type of protocol to be used with the database for SQL statements. Options are:
This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. Instead, specify PARTITION=MONITOR to create a JDBC Monitor connection. This parameter is available for Raw connections. |
||||||||||||||||
Specifies the username for the Connection when the DriverManager.getConnection method's user argument is omitted or null. This connection parameter is primarily intended for use with the single-argument DriverManager.getConnection(String url) method. When the two-argument DriverManager.getConnection(String url, Properties info) method is used, a non-null "user" or "username" property value in the Properties info argument will override this connection parameter. When the three-argument DriverManager.getConnection(String url, String user, String password) method is used, a non-null user argument will override this connection parameter. This parameter is available for SQL connections beginning with Teradata JDBC Driver 14.00.00.13. This parameter is not available for the Default Connection for Java Stored Procedures. This parameter is available for JDBC FastExport, JDBC FastLoad, and JDBC FastLoad CSV connections beginning with Teradata JDBC Driver 14.00.00.13. This parameter is available for JDBC Monitor connections beginning with Teradata JDBC Driver 14.00.00.13. This parameter is available for Raw connections beginning with Teradata JDBC Driver 14.00.00.13. |
||||||||||||||||
Specifies which Data Dictionary views should be queried to return result sets from DatabaseMetaData methods.
Refer to Teradata Vantage™ Data Dictionary for additional information on X views. This parameter is available for SQL connections. This parameter is available for the Default Connection for Java Stored Procedures beginning with Teradata Database 16.0. This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
||||||||||||||||
Specifies whether to allow XML External Entity Referencing for DocumentBuilderFactory during the parsing of XML values returned from the database.
Refer to XML External Entity (XXE) Processing for additional information on this parameter. This parameter is available for SQL connections. This parameter is not available for the Default Connection for Java Stored Procedures This parameter is available for JDBC FastExport and JDBC FastLoad connections. This parameter is not available for JDBC FastLoad CSV connections. This parameter is not available for JDBC Monitor connections. This parameter is not available for Raw connections. |
When using an application server, the preferred way to obtain a java.sql.Connection is to use the java.sql.DataSource interface.
DataSource allows the encapsulation all of the parameters associated with obtaining a database connection in one object that has a logical name. You can then just ask for a connection to that name (for example, Teradata1) and not have to be aware of the numerous parameters that are required to fulfill this request.
A DataSource must be created before it can be used. This job is normally done by a system administrator. The work involved amounts to setting the parameters associated with a Driver Manager URL into the DataSource object and saving it as a file or network‑addressable resource.
The names of these parameters must be identical to their names used in the URL, including capitalization. For example, if CHARSET is used to set the character set property, then CharSet, CHARSet or CHARSEt would not set it. Additional properties dataSourceName, description, user and password are only available at the DataSource level. Beginning with Teradata JDBC Driver 16.00.00.28, the Teradata JDBC Driver validates Data Source property values and throws SQLException for an invalid property value.
Refer to TeraDataSource Class for additional information.
DataSources are accessed with the Java Naming and Directory Interface (JNDI). For more information, visit http://www.oracle.com/technetwork/java/jndi/
With pooled connections, it is very important that the connection be closed when the user no longer needs it. Otherwise, it is not returned to the connection pool. When using pooled connections, it is advisable to have a finally block after try/catch blocks to ensure that connections are closed.
The database does not provide a facility to reset a session's attributes. Therefore, the user of a connection pool data source must be aware that any commands that affect session attributes must not be used. Any changes to session attributes continue to be in effect for the next unsuspecting user of that connection.
Session attributes that MUST NOT BE CHANGED include:
Note: The SET QUERY_BAND ... FOR TRANSACTION command is recommended as an alternative to SET QUERY_BAND ... FOR SESSION because SET QUERY_BAND ... FOR TRANSACTION is limited in scope to the current transaction.
The Teradata JDBC Driver provides Communications Processor (COP) discovery behavior when the COP=ON connection parameter is specified, or when the COP connection parameter is omitted.
A database system can be composed of multiple database nodes. One or more of the database nodes can be configured to run the database Gateway process. Each database node that runs the database Gateway process is termed a Communications Processor, or COP. COP Discovery refers to the procedure of identifying all the available COP hostnames and their IP addresses. COP hostnames can be defined in DNS, or can be defined in the client system's hosts file. Teradata strongly recommends that COP hostnames be defined in DNS, rather than the client system's hosts file. Defining COP hostnames in DNS provides centralized administration, and enables centralized changes to COP hostnames if and when the database is reconfigured.
Beginning with Teradata JDBC Driver 16.00.00.28, the COPLAST connection parameter specifies how COP Discovery determines the last cop hostname. When the COPLAST=OFF connection parameter is specified, or the COPLAST connection parameter is omitted, or COP Discovery is disabled via the COP=OFF connection parameter, then the Teradata JDBC Driver will not perform a DNS lookup for the coplast hostname.
Beginning with Teradata JDBC Driver 16.00.00.28, when the COPLAST=ON connection parameter is specified, and COP Discovery is enabled, then the Teradata JDBC Driver will first perform a DNS lookup for a coplast hostname to obtain the IP address of the last COP hostname before performing COP Discovery. Subsequently, during COP Discovery, the Teradata JDBC Driver will stop searching for COP hostnames when either an unknown COP hostname is encountered, or a COP hostname is encountered whose IP address matches the IP address of the coplast hostname.
When performing COP Discovery, the Teradata JDBC Driver starts with cop1, which is appended to the database hostname, and then proceeds with cop2, cop3, ..., copN. The Teradata JDBC Driver supports domain-name qualification for COP Discovery and the coplast hostname. Domain-name qualification is recommended, because it can improve performance by avoiding unnecessary DNS lookups for DNS search suffixes.
The following table illustrates the DNS lookups performed for a hypothetical three-node database system named "whomooz".
|
No domain name qualification |
With domain name qualification (Recommended) |
---|---|---|
Application-specified database hostname |
|
|
DNS lookups with COP Discovery turned on, and COPLAST=OFF or omitted
(Default behavior) |
DNS lookups are performed until an unknown COP hostname is encountered. |
|
DNS lookups with COP Discovery turned on, and COPLAST=ON |
DNS lookups are performed until a COP hostname is found whose IP address matches the coplast hostname, or an unknown COP hostname is encountered. |
|
DNS lookup with COP Discovery turned off (COP=OFF) |
Round-robin is only possible with DNS, not with the client system hosts file. |
|
The Teradata JDBC Driver supports the definition of multiple IP addresses for COP hostnames and non-COP hostnames.
The Teradata JDBC Driver calls the Java API method InetAddress.getAllByName
to obtain all the IP addresses defined for each hostname.
For the first connection to a particular database system, the Teradata JDBC Driver generates a random number to index into the list of COPs. For each subsequent connection, the Teradata JDBC Driver increments the saved index until it wraps around to the first position. This behavior provides load distribution across all discovered COPs.
The Teradata JDBC Driver masks connection failures to down COPs, thereby hiding most connection failures from the client application. An exception is thrown to the application only when all the COPs are down for that database. If a COP is down, the next COP in the sequence (including a wrap-around to the first COP) receives extra connections that were originally destined for the down COP. When multiple IP addresses are defined in DNS for a COP, the Teradata JDBC Driver will attempt to connect to each of the COP's IP addresses, and the COP is considered down only when connection attempts fail to all of the COP's IP addresses.
If COP Discovery is turned off, or no COP hostnames are defined in DNS, the Teradata JDBC Driver connects directly to the database hostname provided in the connection URL. This permits load distribution schemes other than the COP Discovery approach. For example, round-robin DNS or a TCP/IP load distribution product can be used. COP Discovery takes precedence over simple database hostname lookup. To use an alternative load distribution scheme, either ensure that no COP hostnames are defined in DNS, or specify the COP=OFF connection parameter.
The JVM caches DNS lookups, and the Teradata JDBC Driver does not maintain its own cache of DNS name resolutions. The administrator can use the standard JVM system properties for cache control as defined in the javadoc for the InetAddress class.
The InetAddress class caches both successful and unsuccessful host name resolutions. The positive caching guards against DNS spoofing attacks, and the negative caching improves performance.
By default, the result of positive host name resolutions are cached forever, because there is no general rule to decide when it is safe to remove cache entries. The result of an unsuccessful host name resolution is cached for a very short period of time (10 seconds) to improve performance.
Under certain circumstances where it can be determined that DNS spoofing attacks are not possible, a Java security property can be set to a different Time-to-live (TTL) value for positive caching. Likewise, a system administrator can configure a different negative caching TTL value when needed. Two Java security properties control the TTL values used for positive and negative host name resolution caching:
networkaddress.cache.ttl (default: -1)
indicates the caching policy for successful name lookups from the name service. The value is specified as an integer to indicate the number of seconds to cache the successful lookup.
A value of -1 indicates cache forever.
networkaddress.cache.negative.ttl (default: 10)
indicates the caching policy for unsuccessful name lookups from the name service. The value is specified as an integer to indicate the number of seconds to cache the failure for unsuccessful lookups.
A value of 0 indicates never cache.
A value of -1 indicates cache forever.
Proxy server support is available beginning with Teradata JDBC Driver 20.00.00.12.
The following table lists each kind of network connection made by the driver, and indicates the available options for specifying a proxy server.
Connection type | Connection parameter proxy server settings | Java System proxy server settings |
---|---|---|
HTTPS connections to database | Available | Available |
Non-HTTPS connections to database |
| Available |
HTTPS connections to Identity Provider endpoints | Available | Available |
HTTP connections to Identity Provider endpoints |
| Available |
HTTP connections for CRL and OCSP certificate revocation checking |
| Available |
Connection parameters are available to specify the proxy server for HTTPS connections. These connection parameters have the highest priority in determining proxy server settings for HTTPS connections.
In particular, connection parameters to specify the proxy server have higher priority than Java System proxy server settings.
If connection parameters for proxy server are specified and are applicable to the kind of connection, then the JDBC Driver will use the specified connection parameter proxy server settings for the connection.
Otherwise, if connection parameters for proxy server are not specified or are not applicable to the kind of connection, then the JDBC Driver will use lower-priority proxy server settings for the connection.
Connection parameter | Description |
---|---|
HTTPS_PROXY=proxy URL | Specify the proxy server with a URL that must begin with |
HTTPS_PROXY_USER=proxy username | Optionally specifies the proxy server username for the proxy server identified by the HTTPS_PROXY parameter. This parameter may only be specified in conjunction with the HTTPS_PROXY parameter. |
HTTPS_PROXY_PASSWORD=proxy password | Optionally specifies the proxy server password for the proxy server identified by the HTTPS_PROXY parameter. This parameter may only be specified in conjunction with the HTTPS_PROXY parameter. |
PROXY_BYPASS_HOSTS=pattern | Optionally specifies a matching pattern for hostnames and addresses to bypass the proxy server identified by the HTTPS_PROXY parameter. This parameter may only be specified in conjunction with the HTTPS_PROXY parameter. Separate multiple hostnames and addresses with a vertical bar When this parameter is omitted, the default pattern is When a bypass occurs due to this parameter, then the JDBC Driver will use lower-priority proxy server settings for the connection. |
For example, PROXY_BYPASS_HOSTS can specify a database COP hostname wildcard to bypass the proxy server for HTTPS connections to the database, while using the proxy server for other HTTPS connections.
Example JDBC connection URL |
---|
|
Java System proxy server settings are second priority, and are lower priority than connection parameter proxy server settings.
For HTTPS connections to database and for HTTPS connections to Identity Provider endpoints:
https.proxyHost
specifies the proxy server hostname.https.proxyPort
specifies the proxy server port number.Example Java command line |
---|
|
For HTTP connections to Identity Provider endpoints and for HTTP connections for CRL and OCSP certificate revocation checking:
http.proxyHost
specifies the proxy server hostname.http.proxyPort
specifies the proxy server port number.Example Java command line |
---|
|
The HTTPS and HTTP Java System proxy server settings can be specified together.
Example Java command line |
---|
|
Java System property http.nonProxyHosts
optionally specifies a matching pattern for hostnames and addresses to bypass the proxy server identified by the Java System properties https.proxyHost
and http.proxyHost
. The same property http.nonProxyHosts
is used for both HTTPS and HTTP.
|
character. Specify an asterisk *
as a wildcard character.localhost|127.*|[::1]
which bypasses the proxy server identified by https.proxyHost
and/or http.proxyHost
for common variations of the loopback address.
For example, http.nonProxyHosts
can specify a database COP hostname wildcard to bypass the proxy server for HTTPS connections to the database, while using the proxy server for other kinds of connections.
Example Java command line |
---|
|
If the Java System property java.net.useSystemProxies=true
is specified, without specifying other Java System proxy server settings, then the operating system proxy server settings are used.
The Java System property java.net.useSystemProxies
is only available for Windows and macOS.
Java proxy precedence behavior is documented in OpenJDK file src/java.base/share/conf/net.properties
:
"Note that the system properties that do explicitly set proxies (like http.proxyHost) do take precedence over the system settings even if java.net.useSystemProxies is set to true."
Example Java command line |
---|
|
If Java System proxy server settings are not specified or are not applicable to the kind of connection, then a direct connection is made and no proxy server is used.
Teradata JDBC Driver Stored Password Protection enables an application to provide a JDBC connection password in encrypted form to the Teradata JDBC Driver, and also enables an application to provide the NEW_PASSWORD connection parameter's value in encrypted form.
Stored Password Protection is available beginning with Teradata JDBC Driver 16.00.00.24.
There are several different ways that an application may specify a password to the Teradata JDBC Driver, all of which may use an encrypted password:
DriverManager.getConnection(String,String,String)
method.DriverManager.getConnection(String,Properties)
method.DriverManager.getConnection(String)
method.DriverManager.getConnection
method.DriverManager.getConnection
method.DriverManager.getConnection
method.If the password, however specified, begins with the prefix "ENCRYPTED_PASSWORD("
then the specified password must follow this format:
ENCRYPTED_PASSWORD(PasswordEncryptionKeyResourceName,EncryptedPasswordResourceName)
The PasswordEncryptionKeyResourceName must be separated from the EncryptedPasswordResourceName by a single comma.
The PasswordEncryptionKeyResourceName specifies the name of a resource that contains the password encryption key and associated information. The EncryptedPasswordResourceName specifies the name of a resource that contains the encrypted password and associated information. The two resources are described below.
When an encrypted password is specified for the PASSWORD, NEW_PASSWORD, SSLTRUSTSTORE_PASSWORD, and/or LOGDATA connection URL parameters,
the value must be enclosed in single quotes,
to enclose the "ENCRYPTED_PASSWORD("
syntax's comma separator for the resource names,
otherwise that comma would be interpreted as a separator for the next connection URL parameter.
TJEncryptPassword.java is a sample program to create encrypted password files for use with Teradata JDBC Driver Stored Password Protection.
This program works in conjunction with Teradata JDBC Driver Stored Password Protection.
This program creates the files containing the password encryption key and encrypted password, which can be subsequently specified to
the Teradata JDBC Driver via the "ENCRYPTED_PASSWORD("
syntax.
You are not required to use this program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files. The only requirement is that the files must match the format expected by the Teradata JDBC Driver, which is documented below.
This program encrypts the password and then immediately decrypts the password, in order to verify that the password can be successfully decrypted. This program mimics the implementation of the Teradata JDBC Driver's password decryption, and is intended to openly illustrate its operation and enable scrutiny by the community.
The encrypted password is only as safe as the two files. You are responsible for restricting access to the files containing the password encryption key and encrypted password. If an attacker obtains both files, the password can be decrypted. The operating system file permissions for the two files should be as limited and restrictive as possible, to ensure that only the intended operating system userid has access to the files.
The two files can be kept on separate physical volumes, to reduce the risk that both files might be lost at the same time. If either or both of the files are located on a network volume, then an encrypted wire protocol can be used to access the network volume, such as sshfs, encrypted NFSv4, or encrypted SMB 3.0.
This program accepts eight command-line arguments:
Argument |
Description |
---|---|
1. Transformation |
Specifies the transformation argument for the Example: |
2. KeySizeInBits |
Specifies the keysize argument for the Specify Example:
To use AES with a 192-bit or 256-bit key, the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files must be downloaded from Oracle and installed in your JRE. Example: |
3. MAC |
Specifies the algorithm argument for the MAC algorithm HmacSHA256 is available with JDK 5 and later. MAC algorithm HmacSHA1 is available with JDK 1.4.2. Example: |
4. PasswordEncryptionKeyFileName |
Specifies a filename in the current directory, a relative pathname, or an absolute pathname. The file is created by this program. If the file already exists, it will be overwritten by the new file. Example: |
5. EncryptedPasswordFileName |
Specifies a filename in the current directory, a relative pathname, or an absolute pathname. The filename or pathname that must differ from the PasswordEncryptionKeyFileName. The file is created by this program. If the file already exists, it will be overwritten by the new file. Example: |
6. Hostname |
Specifies the database hostname. Example: |
7. Username |
Specifies the database username. Example: |
8. Password |
Specifies the database password to be encrypted. Unicode characters in the password can be specified with the Example: |
Complete instructions for how to download, compile, and run the Teradata JDBC Driver sample programs are available here.
The following list is a brief summary of the necessary steps to prepare for using the TJEncryptPassword program:
jar xvf samples.jar
javac TJEncryptPassword.java
The following commands assume that the files extracted from samples.jar, the compiled class files, and the Teradata JDBC Driver jar files are all located in the current directory. The current directory must be specified on the classpath.
The TJEncryptPassword program uses the Teradata JDBC Driver to log on to the specified database using the encrypted password, so the TJEncryptPassword program must have access to the Teradata JDBC Driver jar files on the classpath.
Beginning with Teradata JDBC Driver 16.20.00.11, the classpath must list the current directory and terajdbc4.jar
java -cp .;terajdbc4.jar
java -cp .:terajdbc4.jar
With older versions of the Teradata JDBC Driver, the classpath must list the current directory, terajdbc4.jar
, and tdgssconfig.jar
java -cp .;terajdbc4.jar;tdgssconfig.jar
java -cp .:terajdbc4.jar:tdgssconfig.jar
The following example command illustrates running the TJEncryptPassword program on Windows, using a 256-bit AES key available with the JCE Unlimited Strength Jurisdiction Policy Files, and using the HmacSHA256 algorithm available beginning with JDK 5.
java -cp .;terajdbc4.jar TJEncryptPassword AES/CBC/NoPadding 256 HmacSHA256 PassKey.properties EncPass.properties whomooz guest please
The following example command illustrates running the TJEncryptPassword program on Windows, using the default AES key size due to lacking the JCE Unlimited Strength Jurisdiction Policy Files, and using the HmacSHA256 algorithm available beginning with JDK 5.
java -cp .;terajdbc4.jar TJEncryptPassword AES/CBC/NoPadding -default HmacSHA256 PassKey.properties EncPass.properties whomooz guest please
The following example command illustrates running the TJEncryptPassword program on Windows, using the HmacSHA1 algorithm available with JDK 1.4.2.
java -cp .;terajdbc4.jar TJEncryptPassword AES/CBC/NoPadding -default HmacSHA1 PassKey.properties EncPass.properties whomooz guest please
You are not required to use the TJEncryptPassword program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files, but the files must match the format expected by the Teradata JDBC Driver.
The password encryption key file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.
The file must contain the following string properties:
Property |
Description |
---|---|
version=1 |
The version number must be 1. This property is required. |
transformation=TransformationName |
This value must be a valid transformation argument for the This property is required. |
algorithm=AlgorithmName |
This value must correspond to the algorithm portion of the transformation. This value must be a valid algorithm argument for the This property is required. |
match=MatchValue |
The password encryption key and encrypted password files must contain the same match value. The match values are compared to ensure that the two specified files are related to each other, serving as a "sanity check" to help avoid configuration errors. This property is required.
Note: The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password. |
key=HexDigits |
This value is the password encryption key, encoded as hex digits. This property is required. |
mac=AlgorithmName |
This value must be a valid algorithm argument for the Teradata JDBC Driver Stored Password Protection performs Encrypt-then-MAC for protection from a padding oracle attack. This property is required. |
mackey=HexDigits |
This value is the MAC key, encoded as hex digits. This property is required. |
The encrypted password file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.
The file must contain the following string properties:
Property |
Description |
---|---|
version=1 |
The version number must be 1. This property is required. |
match=MatchValue |
The password encryption key and encrypted password files must contain the same match value. The match values are compared to ensure that the two specified files are related to each other, serving as a "sanity check" to help avoid configuration errors. This property is required.
Note: The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password. |
password=HexDigits |
This value is the encrypted password, encoded as hex digits. This property is required. |
params=HexDigits |
This value contains the cipher algorithm parameters, if any, encoded as hex digits. Some ciphers need algorithm parameters that cannot be derived from the key, such as an initialization vector. This property is optional, depending on whether the cipher algorithm has associated parameters. |
hash=HexDigits |
This value is the expected message authentication code (MAC), encoded as hex digits. After encryption, the expected MAC is calculated using the ciphertext, transformation name, and algorithm parameters if any. Before decryption, the Teradata JDBC Driver calculates the MAC using the ciphertext, transformation name, and algorithm parameters if any, and verifies that the calculated MAC matches the expected MAC. If the calculated MAC differs from the expected MAC, then either or both of the files may have been tampered with. This property is required. |
A transformation is a string that describes the set of operations to be performed on the given input, to produce transformed output.
A transformation always includes the name of a cryptographic algorithm such as DES or AES, and may optionally be followed by a feedback mode and padding scheme.
The JDK 7 javadoc for javax.crypto.Cipher indicates that every Java implementation must support the following transformations:
AES/CBC/NoPadding
AES/CBC/PKCS5Padding
AES/ECB/NoPadding
AES/ECB/PKCS5Padding
DES/CBC/NoPadding
DES/CBC/PKCS5Padding
DES/ECB/NoPadding
DES/ECB/PKCS5Padding
DESede/CBC/NoPadding
DESede/CBC/PKCS5Padding
DESede/ECB/NoPadding
DESede/ECB/PKCS5Padding
RSA/ECB/PKCS1Padding
RSA/ECB/OAEPWithSHA-1AndMGF1Padding
RSA/ECB/OAEPWithSHA-256AndMGF1Padding
Teradata JDBC Driver Stored Password Protection uses a symmetric encryption algorithm such as DES or AES, in which the same secret key is used for encryption and decryption of the password. Teradata JDBC Driver Stored Password Protection does not use an asymmetric encryption algorithm such as RSA, with separate public and private keys.
Teradata JDBC Driver Stored Password Protection hides the password length in the encrypted password file by extending the length of the UTF8-encoded password with trailing null bytes. The length is extended to the next 512-byte boundary.
AES/CBC/NoPadding
, may only be used to encrypt data whose byte count after extension is a multiple
of the algorithm's block size.
The 512-byte boundary is compatible with many block ciphers. AES, for example, has a block size of 128 bits (16 bytes), and is therefore
compatible with the 512-byte boundary.AES/CBC/PKCS5Padding
, can be used to encrypt data of any length.
However, CBC with padding is vulnerable to a "padding oracle attack", so Teradata JDBC Driver Stored Password Protection performs Encrypt-then-MAC
for protection from a padding oracle attack.
MAC algorithm HmacSHA256
is available with JDK 5 and later. MAC algorithm HmacSHA1
is available with JDK 1.4.2.AES/CFB8/NoPadding
can be used to encrypt data of any length.The strength of the encryption depends on your choice of cipher algorithm and key size.
KeyGenerator.init
method.
To use AES with a 192-bit or 256-bit key, the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files must be downloaded
from Oracle and installed in your JRE.KeyGenerator.init
method.The TJEncryptPassword program has command-line arguments PasswordEncryptionKeyFileName and EncryptedPasswordFileName to specify filenames.
In contrast, the Teradata JDBC Driver's "ENCRYPTED_PASSWORD("
syntax uses resource names, rather than filenames, in order to offer
more flexibility for file storage location and access.
ENCRYPTED_PASSWORD(PasswordEncryptionKeyResourceName,EncryptedPasswordResourceName)
Files created by the TJEncryptPassword program are subsequently accessed as resources by the Teradata JDBC Driver.
The resource names include a prefix to indicate how the resource must be accessed.
If the resource name begins with the "classpath:"
prefix, then the Teradata JDBC Driver loads the resource from the classpath.
If you specify a resource name with the "classpath:"
prefix, then you must ensure the resource is available on the classpath for the Teradata JDBC Driver.
For security, classpath resources are required to have specific resource name prefixes.
The PasswordEncryptionKeyResourceName must begin with "PassKey"
and the EncryptedPasswordResourceName must begin with "EncPass"
.
Example:
ENCRYPTED_PASSWORD(classpath:PassKeyJohnDoe.properties,classpath:EncPassJohnDoe.properties)
If the resource name begins with a prefix other than "classpath:"
, then the Teradata JDBC Driver loads the resource via the
new URL(resourcename).openStream()
method. Non-classpath resources are not required to have specific resource name prefixes.
You must ensure that non-classpath resources are accessible by the Teradata JDBC Driver.
The resource name can begin with the "file:"
prefix and specify a relative pathname for the Teradata JDBC Driver to load the resource
from a relative-pathname file.
Example with files in current directory:
ENCRYPTED_PASSWORD(file:JohnDoeKey.properties,file:JohnDoePass.properties)
Example with relative paths:
ENCRYPTED_PASSWORD(file:../dir1/JohnDoeKey.properties,file:../dir2/JohnDoePass.properties)
The resource name can begin with the "file:"
prefix and specify an absolute pathname for the Teradata JDBC Driver to load the resource
from an absolute-pathname file.
Example with absolute paths on Windows:
ENCRYPTED_PASSWORD(file:c:/dir1/JohnDoeKey.properties,file:c:/dir2/JohnDoePass.properties)
Example with absolute paths on Linux:
ENCRYPTED_PASSWORD(file:/dir1/JohnDoeKey.properties,file:/dir2/JohnDoePass.properties)
The two resource names specified for an encrypted password must be accessible to the Teradata JDBC Driver and must conform to the properties file formats described above.
The Teradata JDBC Driver throws SQLException if the resource name begins with the "classpath:"
prefix, but the resource is not available on the classpath.
The Teradata JDBC Driver will also throw SQLException if a non-classpath resource is not accessible.
The Teradata JDBC Driver throws SQLException if the resources do not conform to the required properties file formats.
The Teradata JDBC Driver verifies that the match values in the two resources are present, and match each other. The Teradata JDBC Driver throws SQLException if the match values differ from each other. The match values are compared to ensure that the two specified resources are related to each other, serving as a "sanity check" to help avoid configuration errors. The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password.
Before decryption, the Teradata JDBC Driver calculates the MAC using the ciphertext, transformation name, and algorithm parameters if any, and verifies that the calculated MAC matches the expected MAC. The Teradata JDBC Driver throws SQLException if the calculated MAC differs from the expected MAC, to indicate that either or both of the resources may have been tampered with.
For a logon password, the Teradata JDBC Driver uses the decrypted password string to log on to the database.
For a new password, the Teradata JDBC Driver uses the decrypted password string with the MODIFY USER
command to update an expired password.
Table 14 describes URL and data source parameters for authentication and encryption. Descriptions of methods that allow the getting and setting of the parameters are listed in TeraDataSource Class.
Please refer to the Teradata Vantage™ Security Administration reference, chapter "Network Security Policy", section "Configuring a Confidentiality QOP Policy" for how to configure an LDAP directory to require message traffic encryption for particular users, for non-HTTPS connections.
In that same chapter, please refer to section "Requiring Confidentiality" for how to use the gtwcontrol command to require message traffic encryption for all database users, for non-HTTPS connections.
The following table describes the logon mechanisms selected by the LOGMECH connection parameter.
LOGMECH |
Description |
Usage and Requirements |
---|---|---|
BEARER |
OIDC Client Credentials Grant with JWT Bearer Token for client authentication |
User, Password, and LOGDATA must all be omitted when using this method. JWS_PRIVATE_KEY is required when using this method. JWS_CERT is also needed for Identity Providers that require an "x5t" header thumbprint. OIDC_CLIENTID is commonly used to override the default Client ID when using this method. OIDC_SCOPE, OIDC_TOKEN, and JWS_ALGORITHM are optional parameters when using this method.
The database user must have the "logon with null password" permission. The database must be configured with Identity Provider information for Federated Authentication. These tasks are covered in the reference Teradata Vantage™ Security Administration.
Available beginning with Teradata Advanced SQL Engine 17.10 and Teradata JDBC Driver 20.00.00.17. |
BROWSER |
Browser Authentication, also known as OIDC Authorization Code Flow with Proof Key for Code Exchange (PKCE) |
User, Password, and LOGDATA must all be omitted when using Browser Authentication. BROWSER, BROWSER_TAB_TIMEOUT, BROWSER_TIMEOUT, OIDC_CLIENTID, OIDC_SCOPE, and OIDC_TOKEN are optional parameters when using this method.
Browser Authentication is supported for Windows and macOS. Browser Authentication is not supported for other operating systems.
The database user must have the "logon with null password" permission. The database must be configured with Identity Provider information for Federated Authentication. These tasks are covered in the reference Teradata Vantage™ Security Administration.
Available beginning with Teradata Advanced SQL Engine 17.10 and Teradata JDBC Driver 17.10.00.01. |
CODE |
OIDC Device Code Flow, also known as OIDC Device Authorization Grant |
User, Password, and LOGDATA must all be omitted when using this method. CODE_APPEND_FILE, OIDC_CLIENTID, OIDC_SCOPE, and OIDC_TOKEN are optional parameters when using this method.
The database user must have the "logon with null password" permission. The database must be configured with Identity Provider information for Federated Authentication. These tasks are covered in the reference Teradata Vantage™ Security Administration.
Available beginning with Teradata Advanced SQL Engine 17.10 and Teradata JDBC Driver 20.00.00.17. |
CRED |
OIDC Client Credentials Grant with client_secret_post for client authentication |
User, Password, OIDC_CLIENTID, and OIDC_SCOPE must all be omitted when using this method. LOGDATA must contain the Client Credentials Grant request HTTP POST Form Data encoded as Content-Type application/x-www-form-urlencoded. OIDC_TOKEN is an optional parameter when using this method.
The database user must have the "logon with null password" permission. The database must be configured with Identity Provider information for Federated Authentication. These tasks are covered in the reference Teradata Vantage™ Security Administration.
Available beginning with Teradata Advanced SQL Engine 17.10 and Teradata JDBC Driver 20.00.00.17. |
JWT |
JSON Web Token (JWT) |
LOGDATA must contain
The database user must have the "logon with null password" permission. Your application must obtain a valid JWT from a User Service that is accessible to your application. The database must be configured to trust JWTs issued by your User Service. These tasks are covered in the reference Teradata Vantage™ Security Administration.
Available beginning with Teradata Advanced SQL Engine 16.20 and Teradata JDBC Driver 16.20.00.07. |
KRB5 |
Kerberos V5 |
Requires a significant number of administration tasks on the machine that is running the Teradata JDBC Driver. See Meeting Kerberos Prerequisites for more detail.
For Kerberos Single Sign On (SSO), the database user must have the "logon with null password" permission. |
LDAP |
Lightweight Directory Access Protocol (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. |
ROPC |
OIDC Resource Owner Password Credentials (ROPC) |
LOGDATA must be omitted when using this method. User and Password are required when using this method. OIDC_CLIENTID, OIDC_SCOPE, and OIDC_TOKEN are optional parameters when using this method.
The database user must have the "logon with null password" permission. The database must be configured with Identity Provider information for Federated Authentication. These tasks are covered in the reference Teradata Vantage™ Security Administration.
Available beginning with Teradata Advanced SQL Engine 17.10 and Teradata JDBC Driver 20.00.00.17. |
SECRET |
OIDC Client Credentials Grant with client_secret_basic for client authentication |
User and password must be omitted when using this method. LOGDATA must contain the client secret. OIDC_CLIENTID is commonly used to override the default Client ID when using this method. OIDC_SCOPE and OIDC_TOKEN are optional parameters when using this method.
The database user must have the "logon with null password" permission. The database must be configured with Identity Provider information for Federated Authentication. These tasks are covered in the reference Teradata Vantage™ Security Administration.
Available beginning with Teradata Advanced SQL Engine 17.10 and Teradata JDBC Driver 20.00.00.17. |
TD2 |
Teradata Method 2 |
Does not require any special setup, and can be used immediately. |
TDNEGO |
Teradata Negotiating Mechanism |
Automatically selects the appropriate logon mechanism.
Available beginning with Teradata Database 15.10 and Teradata JDBC Driver 15.10.00.31. |
Prior to operating with Kerberos, the system administrator must perform the following prerequisites:
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.
All Kerberos users must be defined within the Active Directory in the Windows domain. Kerberos usernames are case-sensitive. It is important that users log on to the their machines exactly as they are defined within Active Directory. Although a user defined in an Active Directory as DR818999 could successfully log onto the domain as dr818999 (notice the case change), for Kerberos to work, the user must log in to the domain as DR818999.
Note: Ensure that the definition of the user within the Active Directory has the "Do not require Kerberos preauthentication" checked.
Kerberos authentication is supported on the following client systems.
Refer to the Teradata Vantage™ Security Administration reference for information about configuring supported client systems to use an Active Directory or Linux Kerberos Key Distribution Center (KDC).
The essential Kerberos configuration information is specified in the krb5.ini file on Windows or the krb5.conf file on other platforms. Java searches for krb5.ini or krb5.conf in the following order.
java.security.krb5.conf
is set, that property value specifies the path and file name.java.security.krb5.conf
is not set, then Java looks for krb5.ini or krb5.conf in the JRE's lib/security
directory.c:\winnt\krb5.ini
on Windows, /etc/krb5/krb5.conf
on Solaris, or /etc/krb5.conf
on other platforms.The krb5.ini file on Windows is the equivalent of the krb5.conf file that is the standard for both MIT and Heimdal Kerberos. The details of the various settings can be found in the MIT Kerberos documentation.
The following file is an example and must be modified to reflect your actual domain, realm, and Key Distribution Center (KDC).
[libdefaults]
ticket_lifetime = 6000
default_realm = ESROOTDOM.ESDEV.TDAT
clockskew = 13000
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
}
For a Linux client, add the following lines to the krb5.conf file under the [libdefaults] entry:
default_tkt_enctypes = arcfour-hmac rc4-hmac
default_tgs_enctypes = arcfour-hmac rc4-hmac
DNS domain names and hostnames are case-insensitive and, by convention, are lowercase.
In contrast, Kerberos realm names are uppercase and are case-sensitive. The Kerberos realm name is the uppercase version of the domain name.
Kerberos authentication will not work unless the Kerberos realm name is specified in uppercase in the krb5.ini or krb5.conf file.
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
When using Kerberos and Teradata Unity Director, Credential Delegation must be enabled. To use Credential Delegation, obtain a Ticket-Granting-Ticket that is forwardable. This is done using the forwardable option of kinit, for example:
kinit -f
This option must be used in order for Teradata Unity Director to route authentication requests to the database.
Set the forwardable=true option in the krb5.ini (Windows) or krb5.conf (Linux) file under the [libdefaults] section. The previous krb5.ini or krb5.conf file modified to use credential delegation is similar to:
[libdefaults]
forwardable = true
ticket_lifetime = 6000
default_realm = ESROOTDOM.ESDEV.TDAT
clockskew = 13000
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
}
Kerberos authentication requires the following Login Configuration file, which can be stored in a directory that you choose.
com.sun.security.jgss.initiate
{
com.sun.security.auth.module.Krb5LoginModule sufficient useTicketCache=true;
};
other
{
com.sun.security.auth.module.Krb5LoginModule required;
};
You can specify the Login Configuration filename with the java.security.auth.login.config
Java system property.
For example, if the Login Configuration file is named TeraJDBC.config and is located in the current directory, specify the following JVM command-line option.
-Djava.security.auth.login.config=TeraJDBC.config
Alternatively, the Login Configuration file can be specified as a system-wide setting.
Edit the java.security
file located in the JRE's lib/security
directory, and add a login.config.url.
N property.
Examine the java.security
file to determine whether you have any existing login.config.url.
N properties.
You must choose a value for N that does not conflict with any of your existing properties, and the values of N must be consecutively numbered.
The following example shows a single login.config.url.
N value named login.config.url.1
.
In this example, the configuration file is located at C:\dmr\TeraJDBC.config
.
login.config.url.1=file:C:/dmr/TeraJDBC.config
Note that the property value URL must be specified with forward slashes. On Windows, substitute backslashes with forward slashes.
The normal configuration for Microsoft Windows does not permit the export of a session key for a Kerberos Ticket-Granting Ticket (TGT). In order to use Kerberos SSO, you must change a Windows registry setting to enable the export of a session key for a Kerberos TGT.
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters
Value Name: AllowTgtSessionKey
Value Type: REG_DWORD
Value: 0x01 (default is 0)
When an Active Directory account belongs to the local Administrators group on the client PC, Windows will not export a session key for a Kerberos TGT,
even when the AllowTgtSessionKey
registry value is set to 0x1
.
Therefore, it is not possible to use Kerberos SSO for an account that belongs to the local Administrators group on the client PC.
To enable SSO, the JVM option must be supplied:
-Djavax.security.auth.useSubjectCredsOnly=false
To enable the Kerberos SSO logon, the user defined in the 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 Vantage™ SQL Data Definition Language reference.
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.
When the Teradata JDBC Driver establishes a connection to the database, the Teradata JDBC Driver transmits information about the client system and client software to the database. The database records this information in Data Dictionary system tables, to enable analysis of client system demographics by database administrators. The following sections describe the Client Attributes feature and the LogonSource column.
Beginning with Teradata Database 14.0 and Teradata JDBC Driver 13.10.00.21, the Client Attributes feature records a variety of information about the client system and client software in the system tables DBC.SessionTbl and DBC.EventLog. The Client Attributes feature is intended to be a replacement for the information recorded in the LogonSource column of the system tables DBC.SessionTbl and DBC.EventLog.
The Client Attributes are recorded at session logon time. Subsequently, the system views DBC.SessionInfoV and DBC.LogOnOffV can be queried to obtain information about the client system and client software on a per-session basis. Client Attribute values may be recorded in the database in either mixed-case or in uppercase, depending on the session character set and other factors. Analysis of recorded Client Attributes must flexibly accommodate either mixed-case or uppercase values.
Warning: |
The information in this section is subject to change in future releases of the Teradata JDBC Driver. Client Attributes can be "mined" for information about client system demographics; however, any applications that parse Client Attribute values will have to be changed if Client Attribute formats are changed in the future. Client Attributes are not intended to be used for workload management. Instead, query bands are intended for workload management. Any use of Client Attributes for workload management may break if Client Attributes are changed, or augmented, in the future. |
Client Attribute |
Source |
Description |
---|---|---|
MechanismName |
Database |
The connection's logon mechanism; for example, TD2, LDAP, etc. |
ClientIpAddress |
Database |
The client IP address, as determined by the database |
ClientTcpPortNumber |
Database |
The connection's client TCP port number, as determined by the database |
ClientIPAddrByClient |
Teradata JDBC Driver |
The client IP address, as determined by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33. |
ClientPortByClient |
Teradata JDBC Driver |
The connection's client TCP port number, as determined by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33. |
ClientJDBCDriverVersion |
Teradata JDBC Driver |
The Teradata JDBC Driver version number. This Client Attribute is available in the DBC.LogOnOffV view on database systems with the fix for DBS DR 174576. |
ClientProgramName |
Teradata JDBC Driver |
Beginning with Teradata JDBC Driver 17.20.00.03 and JDK 9 or later, this Client Attribute records the Java executable pathname followed by a streamlined stack trace of package names and class names at the time the connection is established.
|
ClientSystemUserId |
Teradata JDBC Driver |
The Java system property user.name |
ClientOsName |
Teradata JDBC Driver |
The Java system properties os.name, os.version, and os.arch, concatenated together and separated by spaces |
ClientProcThreadId |
Teradata JDBC Driver |
Beginning with JDK 9 and Teradata JDBC Driver 16.20.00.03, the JVM process ID as reported by the ProcessHandle.pid method. Otherwise, the JVM process ID as reported by the java.lang.management.RuntimeMXBean.getName method. This Client Attribute is available on JDK 5 and later. |
ClientJavaVersion |
Teradata JDBC Driver |
The Java system property java.version |
ClientVmName |
Teradata JDBC Driver |
The Java system properties java.vm.vendor, java.vm.name, and java.vm.version, concatenated together and separated by spaces |
ClientTdHostName |
Teradata JDBC Driver |
Beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33,
this Client Attribute records the database hostname as specified by the application, without any COP suffix.
|
ClientCOPSuffixedHostName |
Teradata JDBC Driver |
The COP-suffixed database hostname chosen by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33. Beginning with Teradata JDBC Driver 15.10.00.19, this Client Attribute is not available when a literal IP address is specified for the database, instead of a hostname. |
ServerIPAddrByClient |
Teradata JDBC Driver |
The database node's IP address, as determined by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33. |
ServerPortByClient |
Teradata JDBC Driver |
The destination port number of the TCP connection to the database node, as determined by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.33. |
ClientConfType |
Teradata JDBC Driver |
The Confidentiality Type as determined by the Teradata JDBC Driver. This Client Attribute is available beginning with Teradata JDBC Driver 17.10.00.08 and Teradata Advanced SQL Engine 16.20.53.30.
|
ServerConfType |
Database |
The Confidentiality Type as determined by the database. This Client Attribute is available beginning with Teradata Advanced SQL Engine 16.20.53.30. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30.
|
ClientConfVersion |
Database |
The TLS version as determined by the database, if this is an HTTPS/TLS connection. This Client Attribute is available beginning with Teradata Advanced SQL Engine 16.20.53.30. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30. |
ClientConfCipherSuite |
Database |
The TLS cipher as determined by the database, if this is an HTTPS/TLS connection. This Client Attribute is available beginning with Teradata Advanced SQL Engine 16.20.53.30. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30. |
ClientAttributesEx |
Teradata JDBC Driver |
Additional Client Attributes are available in this column as a list of name=value pairs, each terminated by a semicolon. Individual values can be accessed using the NVP system function.
The CERT attribute indicates the TLS certificate status, if this is an HTTPS/TLS connection. HTTPS/TLS connections are available beginning with Teradata JDBC Driver 17.10.00.07 and Teradata Advanced SQL Engine 16.20.53.30. When the CERT attribute indicates the TLS certificate is valid (V) or invalid (I), then additional TLS certificate status details are provided as a series of comma-separated two-letter codes.
|
Beginning with Teradata Database 14.0, the LogonSource column is considered obsolete and has been superseded by the Client Attributes feature. The LogonSource column may be deprecated and subsequently removed in future releases of the database.
When the Teradata JDBC Driver establishes a connection to the database, the Teradata JDBC Driver composes a string value that is stored in the LogonSource column of the system tables DBC.SessionTbl and DBC.EventLog. The LogonSource column is included in system views such as DBC.SessionInfoV and DBC.LogOnOffV. All LogonSource values provided by Teradata JDBC Driver and other clients are recorded in the database in uppercase.
The Teradata JDBC Driver follows the format documented in the Teradata Data Dictionary, section "System Views Columns Reference", for network-attached LogonSource values. Network-attached LogonSource values have eight fields, separated by whitespace. The database composes fields 1 through 3; the Teradata JDBC Driver composes fields 4 through 8.
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.
1 1 1
1 2 3 4 5 6 7 8 9 0 1 2
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678
<--set by Teradata Database--><-------------------------------------------set by client---------------------------------------->
(TCP/IP) 11AB 153.64.135.140 CHARON;CHARONCOP1/192.168.16.144:1025 CID=C2A132 ROOTUSER JDBC03.02.00.00;1.4.2_01 01 LSS
(TCP/IP) 11AB 153.64.135.140 CHARON.SD.TERADATA.COM;CHARONCOP1.SD.TERA CID=C2A132 ROOTUSER JDBC03.02.00.00;1.4.2_01 01 LSS
----------------------------- ----------------------------------------- ------------ -------- -------------------------- ------
1 2 3 4 1 1 2
12345678901234567890123456789012345678901 123456789012 12345678 12345678901234567890123456 123456
Truncated to the space Will be Trunc'ed Trunc'ed to 26 chars Always
remaining in the 97 chars, after 12 chars to 20 chars (may be less) 6 chars
the subsequent fields are composed or less (may be less)
Fields are separated from each other by exactly one space character.
The TDPID field is composed of:
COPnnn
suffix). For example, CHARON
. ;
"
CHARONCOP1/192.168.16.144
:
"
1025.
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 database hostname "CHARON"
is: CHARON;CHARONCOP1/192.168.16.144:1025
An example truncated
value for this field when an application specifies a fully-qualified database hostname of
"CHARON.SD.TERADATA.COM"
is:
CHARON.SD.TERADATA.COM;CHARONCOP1.SD.TERA
Note: The Teradata JDBC Driver does not provide the Java Thread ID for this field.
In an application server environment, threads are not tied to particular database connections, so any particular thread can execute requests on a connection originally created by a different thread.
To avoid potential confusion, the Teradata JDBC Driver provides a connection ID for field 5 containing the LogonSource value. The connection ID is also provided in exception and log messages, which enable connection ID values to be correlated between LogonSource values and exception and log messages. The Teradata JDBC Driver always prefixes connection ID values with the prefix "cid=", to make it easy to distinguish connection ID values from other values.
The connection ID is the hash code of a private object used by the connection. It provides a simple unique identifier for a particular connection to the database.
The Client Process/Thread ID field is composed of:
CID=
An example value for this field is: CID=1E51060
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
The Client Program Name field is composed of:
JDBC
;
"
System.getProperty("java.version")
,
for example, 1.4.2_04 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
This field is composed of 01 LSS.
CREATE USER and MODIFY USER commands provide STARTUP clauses for specifying certain initial session settings. In addition, the Teradata JDBC Driver provides connection parameters and corresponding DataSource properties for specifying certain initial session settings.
Some session settings are specified only by executing an SQL DDL session command. The following table lists many of the session settings, and indicates which initial settings are specified with a CREATE/MODIFY USER clause or a JDBC Connection parameter. The complete list of SET SESSION commands is available in the reference Teradata Vantage™ SQL Data Definition Language Syntax and Examples.
Session Setting |
SQL DDL Session Command |
CREATE/MODIFY USER Clause |
JDBC Connection Parameter |
Only Available as Session Command |
---|---|---|---|---|
Volatile tables |
CREATE VOLATILE TABLE |
- |
- |
Yes |
Current database |
DATABASE |
DEFAULT DATABASE |
DATABASE |
No |
Diagnostic settings |
DIAGNOSTIC ... FOR SESSION |
- |
- |
Yes |
Session query band |
SET QUERY_BAND ... FOR SESSION |
- |
- |
Yes |
Current role |
SET ROLE |
DEFAULT ROLE |
- |
No |
Current account |
SET SESSION ACCOUNT ... FOR SESSION |
ACCOUNT |
ACCOUNT |
No |
Unicode Pass Through |
SET SESSION CHARACTER SET UNICODE PASS THROUGH ON |
- |
- |
Yes |
Current transaction isolation |
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL |
- |
- |
Yes |
Collation sequence |
SET SESSION COLLATION |
COLLATION |
- |
No |
Temporal qualifier |
SET SESSION CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME |
- |
- |
Yes |
Current database |
SET SESSION DATABASE |
DEFAULT DATABASE |
DATABASE |
No |
Date format |
SET SESSION DATEFORM |
DATEFORM |
- |
No |
Function tracing |
SET SESSION FUNCTION TRACE |
- |
- |
Yes |
Replication services control |
SET SESSION OVERRIDE REPLICATION |
- |
- |
Yes |
Current time zone |
SET TIME ZONE |
TIME ZONE |
- |
No |
Unicode Pass Through support is available beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.08.
The user's STARTUP SQL request can be used to establish initial session settings that cannot be set with a CREATE/MODIFY USER clause or a JDBC Connection parameter. For example, the following command sets a STARTUP SQL request for user "susan" to change the transaction isolation to read-uncommitted.
MODIFY USER susan AS STARTUP='SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL RU'
The Teradata JDBC Driver RUNSTARTUP=ON connection parameter must be specified to execute the user's STARTUP SQL request after logon. The default behavior is RUNSTARTUP=OFF. If the RUNSTARTUP connection parameter is omitted, then the user's STARTUP SQL request will not be executed.
A Java application uses a JDBC java.sql.Connection object to interact with a database session. The JDBC connection can be disconnected from the database session in various ways outside the control of the Teradata JDBC Driver, such as:
Teradata Session Reconnect is available beginning with Teradata JDBC Driver 13.10.00.24. When Teradata Session Reconnect is enabled, the Teradata JDBC Driver will attempt to reconnect the JDBC connection to the 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 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 database and Teradata JDBC Driver configuration parameters;
specifically, the 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 database enforces a limited time period for reconnecting to a session after a database restart. The amount of time is set using the database utility program "gtwcontrol". The standard value is 20 minutes. The 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 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.
The TMODE connection parameter enables an application to specify the transaction mode for the connection.
While ANSI mode is generally recommended, please note that every application is different, and some applications may need to use TERA mode. The following differences between ANSI and TERA mode might affect a typical user or application:
The last two behavior differences, taken together, may cause character data comparisons (such as in WHERE clause conditions) to be case-insensitive in TERA mode, but case-sensitive in ANSI mode. This, in turn, can produce different query results in ANSI mode versus TERA mode. Comparing two NOT CASESPECIFIC expressions is case-insensitive regardless of mode, and comparing a CASESPECIFIC expression to another expression of any kind is case-sensitive regardless of mode. You may explicitly CAST an expression to be CASESPECIFIC or NOT CASESPECIFIC to obtain the character data comparison required by your application.
The reference Teradata Vantage™ SQL Request and Transaction Processing recommends that ANSI mode be used for all new applications. The primary benefit of using ANSI mode is that inadvertent data truncation is avoided. In contrast, when using TERA mode, silent data truncation can occur when data is inserted, because silent data truncation is a feature of TERA mode.
A drawback of using ANSI mode is that you can only call stored procedures that were created using ANSI mode, and you cannot call stored procedures that were created using TERA mode. It may not be possible to switch over to ANSI mode exclusively, because you may have some legacy applications that require TERA mode to work properly. You can work around this drawback by creating your stored procedures twice, in two different users/databases, once using ANSI mode, and once using TERA mode.
Refer to Teradata Vantage™ SQL Request and Transaction Processing for complete information regarding the differences between ANSI and TERA transaction modes.
Auto-commit is a basic feature of the JDBC API Specification, and the Teradata JDBC Driver appropriately implements auto-commit on and off functionality for both ANSI and TERA mode.
When a connection is first established, it begins with the default JDBC auto-commit setting, which is on (true). When auto-commit is on, the JDBC Driver is solely responsible for managing transactions, and the JDBC Driver commits each SQL request that is successfully executed. An application should not execute any transaction management SQL commands when auto-commit is on. An application should not call the Connection.commit method or the Connection.rollback method when auto-commit is on.
An application can manage transactions itself by calling the Connection.setAutoCommit method with a false argument to turn off auto-commit. When auto-commit is off, the JDBC Driver leaves the current transaction open after each SQL request is executed, and the application is responsible for committing or rolling back the transaction by calling the Connection.commit or the Connection.rollback method, respectively.
Best practices recommend that an application avoid executing database-vendor-specific transaction management commands such as BT, ET, ABORT, COMMIT, or ROLLBACK, because such commands differ from one vendor to another. (They even differ between Teradata's two modes ANSI and TERA.) Instead, best practices recommend that an application only call the standard JDBC API methods Connection.setAutoCommit, Connection.commit and Connection.rollback for transaction management.
As part of the wire protocol between the database and Teradata client interface software (such as the Teradata JDBC Driver), each message transmitted from the database to the client has a bit designated to indicate whether the session has a transaction in progress or not. Thus, the client interface software is kept informed as to whether the session has a transaction in progress or not.
In TERA mode with auto-commit off, when the application uses the Teradata JDBC Driver to execute a SQL request, if the session does not have a transaction in progress, then the Teradata JDBC Driver automatically executes BT before executing the application's SQL request. Subsequently, in TERA mode with auto-commit off, when the application uses the Teradata JDBC Driver to execute another SQL request, and the session already has a transaction in progress, then the Teradata JDBC Driver has no need to execute BT before executing the application's SQL request.
In TERA mode, BT and ET pairs can be nested, and the database keeps track of the nesting level. The outermost BT/ET pair defines the transaction scope; inner BT/ET pairs have no effect on the transaction because the database does not provide actual transaction nesting. To commit the transaction, ET commands must be repeatedly executed until the nesting is unwound. The Teradata wire protocol bit (mentioned earlier) indicates when the nesting is unwound and the transaction is complete. When the application calls the Connection.commit method in TERA mode, the Teradata JDBC Driver repeatedly executes ET commands until the nesting is unwound and the transaction is complete.
In rare cases, an application may not follow best practices and may explicitly execute transaction management commands. Such an application must turn off auto-commit before executing transaction management commands such as BT, ET, ABORT, COMMIT, or ROLLBACK. The application is responsible for executing the appropriate commands for the transaction mode in effect. TERA mode commands are BT, ET, and ABORT. ANSI mode commands are COMMIT and ROLLBACK. An application must take special care when opening a transaction in TERA mode with auto-commit off. In TERA mode with auto-commit off, when the application executes a SQL request, if the session does not have a transaction in progress, then the Teradata JDBC Driver automatically executes BT before executing the application's SQL request. Therefore, the application should not begin a transaction by executing BT.
// TERA mode example showing undesirable BT/ET nesting
con.setAutoCommit(false);
stmt.execute("BT"); // BT automatically executed by the JDBC Driver before this, and produces a nested BT
stmt.execute("insert into mytable1 values(1, 2)");
stmt.execute("insert into mytable2 values(3, 4)");
stmt.execute("ET"); // unwind nesting
stmt.execute("ET"); // complete transaction
// TERA mode example showing how to avoid BT/ET nesting
con.setAutoCommit(false);
stmt.execute("insert into mytable1 values(1, 2)"); // BT automatically executed by the JDBC Driver before this
stmt.execute("insert into mytable2 values(3, 4)");
stmt.execute("ET"); // complete transaction
Please note that neither previous example shows best practices. Best practices recommend that an application only call the standard JDBC API methods Connection.setAutoCommit, Connection.commit and Connection.rollback for transaction management.
// Example showing best practice
con.setAutoCommit(false);
stmt.execute("insert into mytable1 values(1, 2)");
stmt.execute("insert into mytable2 values(3, 4)");
con.commit();
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:
When JDBC escape clause processing is enabled, the Teradata JDBC Driver looks for any escape syntax and translates it into native SQL syntax for the database. This makes escape syntax independent of any database.
The default for JDBC escape clause processing is ENABLED. Escape clause processing can be disabled for the Classes Statement and RowSet by calling the methods:
The following is the generic syntax for escape clauses:
{keyword parameters...}
Teradata JDBC Driver supports the following types of escape clauses:
Literal Type |
Format |
Notes |
---|---|---|
Date |
{d 'yyyy-mm-dd'} |
|
Time |
{t 'hh:mm:ss'} |
|
Timestamp |
{ts 'yyyy-mm-dd hh:mm:ss'} |
The decimal point and fractional digits may be omitted, or 1 to 6 fractional digits f may be specified after a decimal point |
The following tables list the JDBC Escape Syntax scalar functions that are supported by the Teradata JDBC Driver.
Numeric Function |
Returns |
Notes |
---|---|---|
{fn ABS(number)} |
Absolute value of number |
|
{fn ACOS(float)} |
Arccosine, in radians, of float |
|
{fn ASIN(float)} |
Arcsine, in radians, of float |
|
{fn ATAN(float)} |
Arctangent, in radians, of float |
|
{fn ATAN2(y, x)} |
Arctangent, in radians, of y / x |
The parameter order differs from the JDBC API Specification |
{fn CEILING(number)} |
Smallest integer greater than or equal to number |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn COS(float)} |
Cosine of float radians |
|
{fn COT(float)} |
Cotangent of float radians |
Available beginning with Teradata JDBC Driver 15.00.00.22 |
{fn DEGREES(number)} |
Degrees in number radians |
Available beginning with Teradata JDBC Driver 15.00.00.22 |
{fn EXP(float)} |
e raised to the power of float |
|
{fn FLOOR(number)} |
Largest integer less than or equal to number |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn LOG(float)} |
Natural (base e) logarithm of float |
|
{fn LOG10(float)} |
Base 10 logarithm of float |
Available beginning with Teradata JDBC Driver 15.00.00.22 |
{fn MOD(integer1, integer2)} |
Remainder for integer1 / integer2 |
|
{fn PI()} |
The constant pi, approximately equal to 3.14159... |
|
{fn POWER(number, integer)} |
number raised to integer power |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn RADIANS(number)} |
Radians in number degrees |
Available beginning with Teradata JDBC Driver 15.00.00.22 |
{fn RAND(seed)} |
A random float value such that 0 ≤ value < 1 |
The seed is ignored. Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn ROUND(number, places)} |
number rounded to places |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn SIGN(number)} |
-1 if number is negative; 0 if number is 0; 1 if number is positive |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn SIN(float)} |
Sine of float radians |
|
{fn SQRT(float)} |
Square root of float |
|
{fn TAN(float)} |
Tangent of float radians |
|
{fn TRUNCATE(number, places)} |
number truncated to places |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
String Function |
Returns |
Notes |
---|---|---|
{fn ASCII(string)} |
ASCII code of the first character in string |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn CHAR(code)} |
Character with ASCII code |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn CHAR_LENGTH(string)} |
Length in characters of string |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn CHARACTER_LENGTH(string)} |
Length in characters of string |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn CONCAT(string1, string2)} |
String formed by concatenating string1 and string2 |
|
{fn DIFFERENCE(string1, string2)} |
A number from 0 to 4 that indicates the phonetic similarity of string1 and string2 based on their Soundex codes |
A larger return value indicates greater phonetic similarity; 0 indicates no similarity, 4 indicates strong similarity. Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn INSERT(string1, position, length, string2)} |
String formed by replacing the length-character segment of string1 at position with string2 |
Available beginning with Teradata Database 15.0 and Teradata JDBC Driver 15.00.00.22 |
{fn LCASE(string)} |
String formed by replacing all uppercase characters in string with their lowercase equivalents |
|
{fn LEFT(string, count)} |
Leftmost count characters of string |
Available beginning with Teradata JDBC Driver 15.00.00.22 |
{fn LENGTH(string)} |
Length in characters of string |
|
{fn LOCATE(string1, string2)} |
Position in string2 of the first occurrence of string1 |
Returns 0 if string2 does not contain string1 |
{fn LTRIM(string)} |
String formed by removing leading spaces from string |
|
{fn OCTET_LENGTH(string)} |
Length in octets of string |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn POSITION(string1 IN string2)} |
Position in string2 of the first occurrence of string1 |
Returns 0 if string2 does not contain string1. Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn REPEAT(string, count)} |
String formed by repeating string count times |
Available beginning with Teradata Database 15.0 and Teradata JDBC Driver 15.00.00.22 |
{fn REPLACE(string1, string2, string3)} |
String formed by replacing all occurrences of string2 in string1 with string3 |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn RIGHT(string, count)} |
Rightmost count characters of string |
Available beginning with Teradata Database 15.0 and Teradata JDBC Driver 15.00.00.22 |
{fn RTRIM(string)} |
String formed by removing trailing spaces from string |
|
{fn SOUNDEX(string)} |
Soundex code for string |
|
{fn SPACE(count)} |
String consisting of count spaces |
Available beginning with Teradata Database 14.0 and Teradata JDBC Driver 15.00.00.22 |
{fn SUBSTRING(string, position, length)} |
The length-character segment of string at position |
|
{fn UCASE(string)} |
String formed by replacing all lowercase characters in string with their uppercase equivalents |
|
System Function |
Returns |
Notes |
---|---|---|
{fn DATABASE()} |
Current default database name |
|
{fn IFNULL(expression, value)} |
expression if expression is not NULL, or value if expression is NULL |
|
{fn USER()} |
Logon user name |
The current authorized user name may differ from the logon user name after SET QUERY_BAND sets a proxy user |
Time/Date Function |
Returns |
Notes |
---|---|---|
{fn CURDATE()} |
Current date |
|
{fn CURRENT_DATE()} |
Current date |
Available beginning with Teradata JDBC Driver 15.00.00.22 |
{fn CURRENT_TIME()} |
Current time |
Available beginning with Teradata JDBC Driver 15.00.00.22 |
{fn CURRENT_TIMESTAMP()} |
Current date and time |
Available beginning with Teradata JDBC Driver 15.00.00.22 |
{fn CURTIME()} |
Current time |
|
{fn DAYOFMONTH(date)} |
An integer from 1 to 31 indicating the day of month in date |
|
{fn EXTRACT(field FROM value)} |
The field component of the date and/or time value |
field may be one of the following:
Available beginning with Teradata JDBC Driver 15.00.00.22 |
{fn HOUR(time)} |
An integer from 0 to 23 indicating the hour of time |
|
{fn MINUTE(time)} |
An integer from 0 to 59 indicating the minute of time |
|
{fn MONTH(date)} |
An integer from 1 to 12 indicating the month of date |
|
{fn NOW()} |
Current date and time |
|
{fn SECOND(time)} |
An integer from 0 to 59 indicating the second of time |
|
{fn TIMESTAMPADD(interval, count, timestamp)} |
Timestamp formed by adding count interval(s) to timestamp |
interval must be one of the following:
|
{fn TIMESTAMPDIFF(interval, timestamp1, timestamp2)} |
The number of interval(s) by which timestamp2 exceeds timestamp1 |
interval must be one of the following:
|
{fn WEEK(date)} |
An integer from 1 to 53 indicating the week of the year of date |
Available beginning with Teradata Advanced SQL Engine 16.20 and Teradata JDBC Driver 16.20.00.05 |
{fn YEAR(date)} |
The year of date |
|
Escape clauses for data type conversion use the following syntax:
{fn CONVERT(value, SQLtype)}
where SQLtype is one of the data types listed in the following table.
Conversion Functions |
---|
DATE |
DECIMAL |
DOUBLE |
INTEGER |
FLOAT |
LONGVARCHAR |
SMALLINT |
TIME |
TIMESTAMP |
TINYINT |
VARBINARY |
VARCHAR |
CHAR |
BINARY |
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.
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
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 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 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:
The following table lists the JDBC Escape Syntax functions that are intended for use with the Connection.nativeSQL
method.
These functions provide information about the connection, or control the behavior of the connection.
Functions that provide information return locally-cached information and avoid a round-trip to the database.
Connection Function |
Returns |
Notes |
---|---|---|
{fn teradata_amp_count} |
The number of AMPs of the database system |
|
{fn teradata_getloglevel} |
Current log level |
Returns one of the values for the LOG connection parameter. |
{fn teradata_logon_sequence_number} |
Session's Logon Sequence Number, if available |
|
{fn teradata_provide(config_response)} |
Config Response parcel contents in JSON format |
Available beginning with Teradata JDBC Driver 17.10.00.09. |
{fn teradata_provide(connection_id)} |
Connection ID (the hash code of a private object used by the connection) |
Available beginning with Teradata JDBC Driver 16.00.00.30. |
{fn teradata_provide(default_connection)} |
true or false indicating whether this is a JDBC default connection for use by a Java Stored Procedure |
Available beginning with Teradata JDBC Driver 16.00.00.30. |
{fn teradata_provide(gateway_config)} |
Gateway Config parcel contents in JSON format |
Available beginning with Teradata JDBC Driver 17.10.00.09. |
{fn teradata_provide(governed)} |
true or false indicating the GOVERN connection parameter setting |
Available beginning with Teradata JDBC Driver 20.00.00.07. |
{fn teradata_provide(host_id)} |
Session's host ID |
Returns the same value as Available beginning with Teradata JDBC Driver 16.00.00.03. |
{fn teradata_provide(java_charset_name)} |
Java charset name specified by the CLIENT_CHARSET connection parameter, or the Java charset name that corresponds to the session character set if the CLIENT_CHARSET connection parameter is omitted. |
Available beginning with Teradata JDBC Driver 16.00.00.03. |
{fn teradata_provide(local_address)} |
Local address of the connection's TCP socket |
Returns empty string for a JDBC default connection. Available beginning with Teradata JDBC Driver 16.00.00.30. |
{fn teradata_provide(local_port)} |
Local port of the connection's TCP socket |
Returns empty string for a JDBC default connection. Available beginning with Teradata JDBC Driver 16.00.00.30. |
{fn teradata_provide(original_hostname)} |
Original specified database hostname |
Returns empty string for a JDBC default connection. Available beginning with Teradata JDBC Driver 16.00.00.30. |
{fn teradata_provide(redrive_active)} |
true or false indicating whether this connection has Redrive active |
Available beginning with Teradata JDBC Driver 16.00.00.30. |
{fn teradata_provide(remote_address)} |
Hostname (if available) and IP address of the connected database node |
Returns empty string for a JDBC default connection. Available beginning with Teradata JDBC Driver 16.00.00.30. |
{fn teradata_provide(remote_port)} |
TCP port number of the database |
Returns empty string for a JDBC default connection. Available beginning with Teradata JDBC Driver 16.00.00.30. |
{fn teradata_provide(rnp_active)} |
true or false indicating whether this connection has Recoverable Network Protocol active |
Available beginning with Teradata JDBC Driver 16.00.00.30. |
{fn teradata_provide(session_charset_code)} |
Session character set code |
Available beginning with Teradata JDBC Driver 16.00.00.03. |
{fn teradata_provide(session_charset_name)} |
Session character set name |
Returns the same value as Available beginning with Teradata JDBC Driver 16.00.00.03. |
{fn teradata_provide(transaction_mode)} |
Session's transaction mode, ANSI or TERA |
Available beginning with Teradata JDBC Driver 16.00.00.03. |
{fn teradata_provide(uses_check_workload)} |
true or false indicating whether this connection uses CHECK WORKLOAD |
Available beginning with Teradata JDBC Driver 20.00.00.07. |
{fn teradata_session_number} |
Session number |
Returns the same value as |
{fn teradata_socket_info} |
A string of information about the TCP socket connection to the database |
The format of the returned information is subject to change. An application should not rely on the specific format of the infomation. |
{fn teradata_useansidate} |
Empty string |
Switches to the default Y2K-compliant behavior for java.sql.Date values passed to the PreparedStatement/CallableStatement setDate or setObject methods, and transmitted to destination CHAR/VARCHAR columns and parameters. |
{fn teradata_useintegerdate} |
Empty string |
Switches to non-Y2K-compliant behavior for java.sql.Date values passed to the PreparedStatement/CallableStatement setDate or setObject methods, and transmitted to destination CHAR/VARCHAR columns and parameters. |
The following table lists the JDBC Escape Syntax functions that are intended for use with the Connection.createStatement
,
Connection.prepareStatement
, or Connection.prepareCall
method.
These functions control the behavior of the corresponding Statement, PreparedStatement, or CallableStatement,
and are limited in scope to the particular SQL request in which they are specified.
Request-Scope Function |
Returns |
Notes |
---|---|---|
{fn teradata_auto_out_param} |
Empty string |
Specifies that Statement and PreparedStatement will skip the validation of unset parameter markers, and automatically treat any unset parameter marker as an OUT parameter. This enables the use of Statement and PreparedStatement to CALL to a stored procedure with OUT parameters. An exception is thrown if this function is used with a CallableStatement. Available beginning with Teradata JDBC Driver 15.10.00.09. |
{fn teradata_call_param_rs} |
Empty string |
Specifies that when Statement or PreparedStatement is used to execute a CALL to a stored procedure, the stored procedure's INOUT and OUT parameter output values will be returned as a result set. An exception is thrown if this function is used with a CallableStatement. Available beginning with Teradata JDBC Driver 15.10.00.09. |
{fn teradata_failfast} |
Empty string |
Specifies that this SQL request should be rejected ("fail fast") instead of delayed by a workload management rule or throttle. Available beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.17. |
{fn teradata_provide(request_scope_catalog_json)} |
Empty string |
Specifies that the Available beginning with Teradata JDBC Driver 20.00.00.06 |
{fn teradata_provide(request_scope_column_name_off)} |
Empty string |
Specifies that the Takes priority over the COLUMN_NAME connection parameter for this SQL request. Available beginning with Teradata JDBC Driver 16.00.00.28 |
{fn teradata_provide(request_scope_column_name_on)} |
Empty string |
Specifies that, when StatementInfo parcel support is available,
the This function has no effect when StatementInfo parcel support is unavailable. Takes priority over the COLUMN_NAME connection parameter for this SQL request. Available beginning with Teradata JDBC Driver 16.00.00.28 |
{fn teradata_provide(request_scope_maybenull_on)} |
Empty string |
Specifies that, when StatementInfo parcel support is available, the return value of the
This function has no effect when StatementInfo parcel support is unavailable. Takes priority over the MAYBENULL connection parameter for this SQL request. Available beginning with Teradata JDBC Driver 16.00.00.28 |
{fn teradata_provide(request_scope_refresh_rsmd)} |
Empty string |
Specifies that when a PreparedStatement or CallableStatement is executed, the ResultSetMetaData will be refreshed. Specify this function for a PreparedStatement or CallableStatement that returns the data types ST_GEOMETRY, DATASET STORAGE FORMAT AVRO, and/or DATASET STORAGE FORMAT CSV if you also execute the SET TRANSFORM GROUP command on the connection. Available beginning with Teradata JDBC Driver 16.20.00.06 |
{fn teradata_untrusted} |
Empty string |
Marks the SQL request as untrusted. See TRUSTED SQL and QUERY BAND Impersonation for more information. |
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.
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.
The query band is passed to the database as a list of name=value pairs in a string. The application defines both the names and the values. A query band can be set for the transaction and/or for the session. Beginning with Teradata Database 15.10, a default query band can be set for a profile.
For more information on these SQL commands, refer to the Teradata Vantage™ SQL Data Definition Language reference.
The query band syntax rules are as follows:
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");
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 ();
To clear a query band value in a transaction:
stmt = conn.CreateStatement();
stmt.executeUpdate("SET QUERY_BAND = NONE for TRANSACTION");
Beginning with Teradata JDBC Driver 14.00.00.33, applications running in a JDK 6.0 or later environment can retrieve query band values with the following methods:
The first method returns a Properties object containing all active query band name/value pairs. The second method returns the query band value for the specified name.
If the same query band name is active for multiple contexts, only one of the corresponding query band values will be returned by these methods. Transaction query band values take precedence over session query band values, which take precedence over profile query band values. Profile query band values are supported beginning with Teradata Database 15.10 and Teradata JDBC Driver 15.00.00.23.
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 database.
Beginning with Teradata Database 13.10, Trusted Sessions Enhanced Security prevents the use of the SET QUERY_BAND SQL to set or remove the current proxy user. This is accomplished by categorizing all SQL requests as trusted or untrusted.
Applications that compose all their own SQL requests and trust all their own SQL requests, do not use this feature. This feature is used by applications working with both trusted and untrusted SQL requests. An example of an untrusted SQL request is a SQL request obtained from a user. An untrusted SQL request might contain an SQL Injection attempt by a malicious user.
Applications working with both trusted and untrusted SQL requests use this feature by following this process:
This escape function downgrades an SQL request from trusted to untrusted. No mechanism is provided to upgrade an SQL request from untrusted to trusted because it might be exploited by an SQL Injection attack.
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.
Method |
Without Calendar |
With Calendar |
---|---|---|
setDate |
Sends a SQL data type DATE value to the 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 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 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.
Method |
Without Calendar |
With Calendar |
---|---|---|
setTime |
Sends a SQL data type TIME value to the 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.
Intended for use with TIME destination columns, and not intended for use with TIME WITH TIME ZONE destination columns. |
Sends a SQL data type TIME WITH TIME ZONE to the 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.
Intended for use with TIME WITH TIME ZONE destination columns, and not intended for use with TIME destination columns. |
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.
Method |
Without Calendar |
With Calendar |
---|---|---|
setTimestamp |
Sends a SQL data type TIMESTAMP value to the 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.
Intended for use with TIMESTAMP destination columns, and not intended for use with TIMESTAMP WITH TIME ZONE destination columns. |
Sends a SQL data type TIMESTAMP WITH TIME ZONE to the 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.
Intended for use with TIMESTAMP WITH TIME ZONE destination columns, and not intended for use with TIMESTAMP destination columns. |
Daylight Savings Time is not used for Timestamp values. The database stores TIMESTAMP WITH TIME ZONE values as a raw offset from UTC. For consistency with the 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")));
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})) ;
Beginning with Teradata JDBC Driver 13.00.00.09, the ResultSet and CallableStatement interfaces' getDate, getTime, and getTimestamp methods with Calendar argument will modify the Calendar argument's TimeZone attribute to provide the time zone portion of TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE data values received from the database.
Also beginning with Teradata JDBC Driver 13.00.00.09, the ResultSet and CallableStatement interfaces' getDate, getTime, and getTimestamp methods provide implicit data type conversions for DATE, TIME, and TIMESTAMP data values received from the database.
While 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 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. Avoid combinations designated as (Not intended use) in order to ensure proper round-trip exchange of data values with the database.
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.
(Not intended use) |
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 intended use) |
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.
(Not intended use) |
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.
(Not intended use) |
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.
(Not intended use) |
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.
(Not intended use) |
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.
(Not intended use) |
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.
(Not intended use) |
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.
(Not intended use) |
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.
(Not intended use) |
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.
(Not intended use) |
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.
(Not intended use) |
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. |
The session's current DateForm primarily dictates how DATE values are transmitted from the database to the Teradata JDBC Driver. The session's current DateForm also dictates how the 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 database transmits DATE values to the Teradata JDBC Driver as 10-character values in the form "YYYY-MM-DD", and the 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 database transmits DATE values to the Teradata JDBC Driver as 4-byte binary values, and the database accepts non-Y2K-compliant implicit conversions from a PreparedStatement setString value to a destination DATE column or parameter. (The 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.
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 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 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.
Beginning with Teradata JDBC Driver 12.0 and Teradata Database 12.0, the Teradata JDBC Driver sends java.sql.Date values to the 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 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.
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 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 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.
The 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 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 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 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 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 database is enhanced, applications that call stored procedures with TIME and TIMESTAMP INOUT parameters must follow these guidelines:
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.
A 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.
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.
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.
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.
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 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 database.
For more information on these SQL commands, refer to the Teradata Vantage™ SQL Data Definition Language reference.
Teradata JDBC Driver supports the following functionality for UDTs.
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. |
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.
Custom type mapping is not supported for stored procedure INOUT parameters that are DISTINCT UDTs.
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 Vantage™ Data Types and Literals reference.
The application must provide a class that implements the java.sql.Struct interface.
public class MyStruct implements java.sql.Struct
{
private String m_typeName ;
private Object [] m_attributes ;
public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }
public String getSQLTypeName() { return m_typeName ; }
public Object [] getAttributes() { return m_attributes ; }
public Object [] getAttributes(java.util.Map map) { return m_attributes ; }
}
The application uses instances of that class to compose PERIOD(TIME WITH TIME ZONE) values.
Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT+08:00")) ;
Time time1 = Time.valueOf( ...
Time time2 = Time.valueOf( ...
// Available beginning with Teradata JDBC Driver 14.10.00.26
Struct timetz1 = new MyStruct("TIME WITH TIME ZONE", new Object [] {time1, cal}) ;
Struct timetz2 = new MyStruct("TIME WITH TIME ZONE", new Object [] {time2, cal}) ;
// Assuming a table with two columns: INTEGER and PERIOD(TIME WITH TIME ZONE)
PreparedStatement ps = con.prepareStatement ("INSERT INTO MyTable VALUES(?,?)") ;
ps.setInt(1, id) ;
ps.setObject(2, new MyStruct("PERIOD(TIME WITH TIME ZONE)", new Object [] {timetz1, timetz2})) ;
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.
An application can use the Connection createArrayOf method to compose a java.sql.Array value to send to the 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 Vantage™ Data Types and Literals reference.
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 database and retrieve XML data from the database. The java.sql.SQLXML interface is available in JDK 6.0 and later. SQLXML is not available in JDK 5.0 and earlier.
Java applications using XML libraries are vulnerable to XXE attacks because the default settings for most Java XML parsers is process XML external entities. The Teradata JDBC Driver uses DocumentBuilderFactory to parse XML values returned from the database. Beginning with Teradata JDBC Driver 16.20.00.10, Document Type Definition (DTD) processing is disabled for the DocumentBuilderFactory, to prevent most XML entity attacks. If your application requires this functionality, specify the XXE_PROCESSING=ON connection parameter.
Early builds of JDK 6 and JDK 7 are affected by Java bug JDK-7157610 which causes a NullPointerException when DTD processing is disabled. The best solution is to upgrade to a JDK version with the fix for Java bug JDK-7157610. As a workaround to avoid Java bug JDK-7157610, specify the XXE_PROCESSING=ON connection parameter.
For more information regarding the SQL XML data type, refer to XML Data Type in the Teradata Vantage™ Data Types and Literals reference.
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 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 database as the SQL DECIMAL data type. Correspondingly, a SQL NUMBER value received from the 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 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 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 Vantage™ Data Types and Literals reference.
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.
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() ;
This example inserts an INTERVAL YEAR value into an INTERVAL YEAR TO MONTH destination column, relying on the 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() ;
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() ;
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 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.
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() ;
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() ;
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 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
The DATASET data type is a complex data type in which each data value corresponds to an entire file or document. The kind of files or documents that can be stored is dictated by the DATASET data type's associated "Storage Format".
Beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.23, the DATASET data type offers the Avro storage format. The JDBC API does not yet define a standard DATASET data type, so the Teradata JDBC Driver offers Teradata-specific functionality for an application to use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as an Avro format DATASET data type. Applications can also insert VARBYTE and BLOB values into Avro format DATASET destination columns. In order for the application to fully take advantage of the DATASET data type's built-in functions, the DATASET question-mark parameter marker should be set using a Struct value. This can be seen in the example shown below.
The database returns an Avro-formatted DATASET value as a BLOB value. An application can use the following metadata methods to differentiate between an Avro-formatted DATASET value and an actual BLOB value. These methods return "DATASET STORAGE FORMAT AVRO" to indicate an Avro-formatted DATASET value, and return "BLOB" to indicate a BLOB value.
When an application uses the Teradata-specific functionality of specifying an Avro-formatted value as a Struct value, the Struct value must contain one of the following attributes.
Furthermore, when the Struct contains an InputStream attribute, the Struct must also contain a second attribute that is an Integer type specifying the number of bytes in the stream.
To use the Teradata-specific functionality of specifying an Avro-formatted DATASET value as a Struct value, the application must provide a class that implements the java.sql.Struct interface.
public class MyStruct implements java.sql.Struct
{
private String m_typeName ;
private Object [] m_attributes ;
public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }
public String getSQLTypeName() { return m_typeName ; }
public Object [] getAttributes() { return m_attributes ; }
public Object [] getAttributes(java.util.Map map) { return m_attributes ; }
}
The application then uses instances of that class to compose Avro-formatted DATASET values.
This example validates the Avro-formatted DATASET value. In this case, a single-row ResultSet will be returned containing the value "OK".
PreparedStatement ps = con.prepareStatement("SELECT AVRO_CHECK(?)") ;
ps.setObject(1, new MyStruct("DATASET STORAGE FORMAT AVRO", new Object [] {avroInputStream, nLength})) ;
ResultSet rs = ps.executeQuery() ;
This example shows a PreparedStatement batch insert of NULL and non-NULL Avro-formatted DATASET values into an Avro format DATASET destination column.
// Assuming a table with an INTEGER column and a DATASET STORAGE FROMAT AVRO column
PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?, ?)") ;
ps.setInt(1, 123) ;
ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT AVRO", new Object [] {avroInputStream, nAvroLength})) ;
ps.addBatch() ;
ps.setInt(1, 456) ;
ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT AVRO", new Object [] {null})) ;
ps.addBatch() ;
ps.executeBatch() ;
Beginning with Teradata Advanced SQL Engine 16.20 and Teradata JDBC Driver 16.20.00.01, the DATASET data type offers the Comma Separated Value (CSV) storage format. The JDBC API does not yet define a standard DATASET data type, so the Teradata JDBC Driver offers Teradata-specific functionality for an application to use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as a CSV format DATASET data type. Applications can also insert VARCHAR and CLOB values into CSV format DATASET destination columns. In order for the application to fully take advantage of the DATASET data type's built-in functions, the DATASET question-mark parameter marker should be set using a Struct value. This can be seen in the example shown below.
The database returns a CSV-formatted DATASET value as a CLOB value. An application can use the following metadata methods to differentiate between a CSV-formatted DATASET value and an actual CLOB value. These methods return "DATASET STORAGE FORMAT CSV" to indicate a CSV-formatted DATASET value, and return "CLOB" to indicate a CLOB value.
When an application uses the Teradata-specific functionality of specifying a CSV-formatted 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 reader.
To use the Teradata-specific functionality of specifying a CSV-formatted DATASET value as a Struct value, the application must provide a class that implements the java.sql.Struct interface.
public class MyStruct implements java.sql.Struct
{
private String m_typeName ;
private Object [] m_attributes ;
public MyStruct(String typeName, Object [] attributes) { m_typeName = typeName ; m_attributes = attributes ; }
public String getSQLTypeName() { return m_typeName ; }
public Object [] getAttributes() { return m_attributes ; }
public Object [] getAttributes(java.util.Map map) { return m_attributes ; }
}
The application then uses instances of that class to compose CSV-formatted DATASET values.
This example validates the CSV-formatted DATASET value. In this case, a single-row ResultSet will be returned containing the integer value 1 if it valid or 0 if it is invalid.
PreparedStatement ps = con.prepareStatement("SELECT CAST (? as DATASET STORAGE FORMAT CSV).validate ()") ;
ps.setObject(1, new MyStruct("DATASET STORAGE FORMAT CSV", new Object [] {csvReader, nLength})) ;
ResultSet rs = ps.executeQuery() ;
This example shows a PreparedStatement batch insert of NULL and non-NULL CSV-formatted DATASET values into a CSV format DATASET destination column.
// Assuming a table with an INTEGER column and a DATASET STORAGE FROMAT CSV column
PreparedStatement ps = con.prepareStatement("INSERT INTO MyTable VALUES(?, ?)") ;
ps.setInt(1, 123) ;
ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT CSV", new Object [] {csvReader, nCSVLength})) ;
ps.addBatch() ;
ps.setInt(1, 456) ;
ps.setObject(2, new MyStruct("DATASET STORAGE FORMAT CSV", new Object [] {null})) ;
ps.addBatch() ;
ps.executeBatch() ;
If an application executes a query that returns an Avro-formatted DATASET data value from the database while using an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the data type will be returned as a BLOB.
If an application attempts to use the Teradata-specific functionality of binding an Avro-formatted DATASET value as a Struct value with Teradata Database 16.0 or later, in conjunction with an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the following exception may be thrown. The solution is to upgrade to a newer version of the Teradata JDBC Driver that supports the DATASET data type.
[Error 3922] [SQLState HY000] Invalid Repr in DataInfo Parcel.
If an application attempts to use the Teradata-specific functionality of binding an Avro-formatted DATASET value as a Struct value with Teradata Database 15.10 or earlier, in conjunction with a version of the Teradata JDBC Driver that supports the Avro format DATASET data type, then the following exception may be thrown. The solution is to upgrade to Teradata Database 16.0 or later.
[Error 1509] [SQLState HY000] Teradata Database DATASET STORAGE FORMAT AVRO data type support is required, and StatementInfo parcel support must be enabled
If an application executes a query that returns a CSV-formatted DATASET data value from the database while using an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the data type will be returned as a CLOB.
If an application attempts to use the Teradata-specific functionality of binding a CSV-formatted DATASET value as a Struct value with Teradata Advanced SQL Engine 16.20 or later, in conjunction with an old version of the Teradata JDBC Driver that does not support the DATASET data type, then the following exception may be thrown. The solution is to upgrade to a newer version of the Teradata JDBC Driver that supports the DATASET data type.
[Error 3922] [SQLState HY000] Invalid Repr in DataInfo Parcel.
If an application attempts to use the Teradata-specific functionality of binding a CSV-formatted DATASET value as a Struct value with Teradata Database 16.10 or earlier, in conjunction with a version of the Teradata JDBC Driver that supports the CSV format DATASET data type, then the following exception may be thrown. The solution is to upgrade to Teradata Advanced SQL Engine 16.20 or later.
[Error 1509] [SQLState HY000] Teradata Database DATASET STORAGE FORMAT CSV data type support is required, and StatementInfo parcel support must be enabled
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 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
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
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();
}
}
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, 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.
While the 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.
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.
Beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.25, for row count values received from the 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 () |
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 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.
Several of the Teradata JDBC Driver DatabaseMetaData methods submit queries to the database on behalf of the application that calls the DatabaseMetaData methods. The application designer must ensure that the application has sufficient privileges to access the necessary Data Dictionary tables and views.
The following table lists each of the DatabaseMetaData methods that query Data Dictionary tables and/or views, and lists the necessary access for each method.
Beginning with Teradata JDBC Driver 13.00.00.25, Data Dictionary V views are used when connected to Teradata Database 12.0 or later. In the table below, the [V] suffix indicates whether the V view will be conditionally used depending on the database version.
The USEXVIEWS connection parameter controls whether the normal views or the X views are used. In the table below, the [X] suffix indicates whether the X view will be conditionally used depending on the setting of the USEXVIEWS connection parameter.
DatabaseMetaData Method |
Privileges Needed |
---|---|
getAttributes |
Select access on DBC.Tables[V][X] Select access on DBC.Columns[V][X] Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33 Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33 |
getBestRowIdentifier |
Select access on DBC.Tables[V][X] Select access on DBC.Columns[V][X] Select access on DBC.Indices[V][X] Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33 Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33 |
getColumnPrivileges |
Select access on DBC.Tables[V][X] Select access on DBC.Columns[V][X] Select access on DBC.AllRights[V][X] |
getColumns |
Select access on DBC.Tables[V][X] Select access on DBC.Columns[V][X], prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.20 Select access on DBC.ColumnsJQV[X], beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.20 Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33 Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33 Uses HELP COLUMN and HELP TYPE to obtain information about view columns prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.20 |
getCrossReference |
Select access on DBC.All_RI_Parents[V][X] |
getExportedKeys |
Select access on DBC.All_RI_Parents[V][X] |
getFunctions |
Select access on DBC.Tables[V][X] Select access on DBC.Functions[V][X] |
getFunctionColumns |
Select access on DBC.Tables[V][X] Select access on DBC.Functions[V][X] Select access on DBC.Columns[V][X] Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33 Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33 |
getImportedKeys |
Select access on DBC.All_RI_Parents[V][X] |
getIndexInfo |
Select access on DBC.Indices[V][X], beginning with Teradata JDBC Driver 14.00.00.24 Select access on DBC.IndexStats[V], beginning with Teradata JDBC Driver 14.00.00.24 Select access on DBC.Tables[V][X], beginning with Teradata JDBC Driver 14.00.00.24 Uses HELP STATISTICS and HELP INDEX prior to Teradata JDBC Driver 14.00.00.24 |
getPrimaryKeys |
Select access on DBC.Indices[V][X] |
getProcedureColumns |
Select access on DBC.Tables[V][X] Select access on DBC.Columns[V][X] Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33 Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33 |
getProcedures |
Select access on DBC.Tables[V][X] |
getSchemas |
Select access on DBC.Databases[V][X] |
getTablePrivileges |
Select access on DBC.Tables[V][X] Select access on DBC.AllRights[V][X] |
getTables |
Select access on DBC.Tables[V][X] |
getUDTs |
Select access on DBC.Tables[V][X] Select access on DBC.Columns[V][X] Select access on DBC.UDFInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33 Select access on DBC.UDTInfo, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33 Select access on DBC.UDTInfoV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33 Select access on DBC.UDTTransform, prior to Teradata Database 16.0 or Teradata JDBC Driver 15.10.00.33 Select access on DBC.UDTTransformV, beginning with Teradata Database 16.0 and Teradata JDBC Driver 15.10.00.33 |
The Teradata JDBC Driver works with the Sun JDK 5.0 implementation of JDBC RowSet Interface.
When using com.sun.rowset.JdbcRowSetImpl(java.sql.Connection connection) and com.sun.rowset.JdbcRowSetImpl(String url, String user, String password), it calls Connection.prepareStatement(String sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE). However, for database versions prior to 12.0, CONCUR_UPDATABLE is not supported.
The Teradata JDBC Driver downgrades the unsupported ResultSet concurrency (CONCUR_UPDATABLE) to the supported concurrency (CONCUR_READ_ONLY) for database versions prior to 12.0 or when the fetched result set is not updatable for Teradata Database 12.0 and newer versions. Refer to Making a Result Set Updatable for more details about how to use updatable result set.
In version 3.4 of the Teradata JDBC Driver, the following methods were added or enabled to support generated keys:
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.
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.
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.
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:
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:
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.
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.
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 with getGenerated Keys. If generatedKeys are requested after an UPSERT statement, an empty result set is returned.
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.
When a ? parameter is specified in the select list of a SELECT statement, the ResultSetMetaData may be ambiguous, in addition to the ParameterMetaData.
SELECT ?
Note: Question-mark parameter markers may be used in a select-list within a SELECT statement beginning with Teradata Database V2R6.2.0.19.
Table 17 outlines what is returned in cases where the data type of a parameter is ambiguous, and is considered an unknown data type.
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:
Note: 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.
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.
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:
If using JDBC, use jar files for XSPs that are stored on the server or the client. A jar file for a Java XSP that is on the client must be transferred from the client node to the server node. For security purposes, the Teradata JDBC Driver uses the classpath to load all resources. The jar file that contains Java XSPs must not be on the classpath itself. Instead, the container of the jar file must be on the classpath. For example, if the jar file is located in a directory on the file system, then the directory name must be present in the classpath. As another example, the jar file may be located inside a war file or an ear file, because the application server will automatically make the contents of the war file or ear file available on the classpath. Once the class path is set, the Teradata JDBC Driver can transfer the source file to the server node.
The following is code from a JDBC sample class using SQLJ to install and transfer a jar file from the client to the DBS server:
stmt.executeUpdate("{call sqlj.install_jar('cj!SampleXJSP.jar', 'SampleXJSP',0)}");
This example gives the location of the jar file using the locspec parameter. The <locspec> specifies where the originating jar file is located. If the <location designator> specifies 'CJ!' then the jar is located on the client in the client-interpreted location specified by the class path for the Teradata JDBC Driver. If the <location designator> specifies 'SJ!' then the jar is located on the database server using the <server jar path>.
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';
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.
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.
|
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 (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.
The following SQL statements are not supported in a Java stored procedure when being used to generate a dynamic result set. This also means that they cannot be mixed with other SQL statements in a multi-statement request when some of these other SQL statements are used to generate a dynamic result set:
The following restrictions apply when returning auto-generated keys result sets from a Java stored procedure:
A Java stored procedure can never directly or indirectly call another Java stored procedure.
An IN or INOUT LOB parameter cannot be updated in a Java Stored Procedure.
The LOB_TEMP_TABLE used in a Java Stored Procedure must be separate from the one used by the JDBC connection calling the Java Stored Procedure.
Teradata Database 12.0 and 13.0 support Java Stored Procedures compiled with JDK 1.4.2 or JDK 5.0 only.
Java Stored Procedures compiled with JDK 6.0 are supported beginning with Teradata Database 13.10.
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:
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 database is not updatable, including:
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 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.
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 the 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.
The following are some common scenarios for using an updatable result set with the Teradata JDBC Driver:
The following code fragment updates the STATE column in the third row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.
rs.absolute(3); // moves the cursor to the third row of rs
rs.updateString("STATE", "CALIFORNIA"); // updates the
// STATE column of row 3 to be CALIFORNIA
rs.updateRow(); // updates the row in the data source
An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table, using the method insertRow.
rs.moveToInsertRow(); // moves cursor to the insert row
rs.updateString(1, "Michael"); // updates the
// first column of the insert row to be Michael
rs.updateInt(2, 35); // updates the second column to be 35
rs.updateBoolean(3, true); // updates the third column to true
rs.insertRow(); // inserts the row in the data source
rs.moveToCurrentRow(); // moves back to current row
rs.absolute(3); // moves the cursor to the third row of rs
rs.deleteRow(); // deletes the current row 3 in the data source
The Teradata JDBC Driver implements the method refreshRow to clear up the column values updated by a set of updater methods for the current row in the result set. If refreshRow is called after calling the updater methods, but before calling the method updateRow, then the updates made to the row are lost. However, the Teradata JDBC Driver doesn't refetch the latest value of the current row from the database to refresh the current row.
The following code fragment updates the STATE and AMOUNT columns in the third row of the ResultSet object rs and then uses the method refreshRow to clear up the column values updated by the updater methods for the current row in the result set. The method updateRow does not update the data source table from which rs was derived since the update column values are lost.
rs.absolute(3); // moves the cursor to the third row of rs
rs.updateString("STATE", "CALIFORNIA"); // updates the
// STATE column of row 3 to be CALIFORNIA
rs.updateInt("AMOUNT", 58); // updates the second column to be 35
rs.refreshRow(); // clears up the update column
// values for the current row
rs.updateRow(); // no UPDATE in the data source since
// update values are lost
There are some scenarios where the result set type and concurrency need to be upgraded or downgraded.
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.
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.
The following are Teradata JDBC Driver exception scenarios when using updatable result set:
Error 1197: No unique primary index (UPI) or key columns are fetched in this result set from the table
Error 1198: Not all of non-nullable columns in the insert row have been given a value for the table
Error 1199: Not all of non-nullable columns in the insert row have been given a non-null value for the table
Error 1195: Unique Primary Index (UPI) columns are NULL for the table
Error 1196: Primary key columns are NULL for the table
Error 1191: Function should not be called since the cursor is on the insert row
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.
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 database. However, because special floating point values are not fully supported by the database, incorrect results and database errors might occur for SELECT statements with WHERE clause conditions that reference special floating point values.
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.
The JDBC PreparedStatement and CallableStatement batch feature is implemented by the Teradata JDBC Driver using the Teradata Database's "iterated request" feature. Improved performance can be expected when using a PreparedStatement batch with a SQL request that is compatible with an iterated request, such as INSERT, UPDATE, or DELETE.
Multi-statement requests, and CALLs to stored procedures, are not compatible with iterated requests, due to a Teradata Database limitation. A multi-statement request cannot be used with a PreparedStatement batch.
The Teradata JDBC Driver offers special-case support for a CALL to a stored procedure with a PreparedStatement (or CallableStatement) batch. Because the Teradata Database does not support iterated requests for a CALL to a stored procedure, the Teradata JDBC Driver cannot transmit a batch as an iterated request when the SQL request is a CALL to a stored procedure. Instead, the Teradata JDBC Driver executes the CALL to the stored procedure repeatedly, once per each set of bound parameter values. No performance improvement is expected when a batch is used for a CALL to a stored procedure.
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.
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 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.
|
Each failed parameter set must be resubmitted individually using the PreparedStatement executeUpdate method.
|
JDBC FastLoad provides a method for quickly loading large amounts of data into an empty destination table in a 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.
JDBC FastLoad is enabled with TYPE=FASTLOAD in the URL connection string. When enabled, the FastLoad protocol is used with the 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 database.
In order to qualify for JDBC FastLoad, the SQL INSERT statement must meet the following criteria:
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.
DatabaseName.TableName_ERR_1
and DatabaseName.TableName_ERR_2
.
For example, if the destination DatabaseName is guest
and the destination TableName is FastLoadExample
, then
the two error tables will be named guest.FastLoadExample_ERR_1
and
guest.FastLoadExample_ERR_2
.
The default behavior is to create the error tables in the same database as the destination table being loaded.
Beginning with Teradata JDBC Driver 16.00.00.31, the location and suffixes of the error tables can be specified with the connection parameters
ERROR_TABLE_1_SUFFIX, ERROR_TABLE_2_SUFFIX
and ERROR_TABLE_DATABASE.
The error tables are automatically queried and dropped by JDBC FastLoad when the loading operation is complete.
The error tables must not be accessed by the application because any access may cause a deadlock while JDBC FastLoad is active.
For more details on the purpose of the error tables, see the section on Error Recording in the Teradata FastLoad Reference.
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.
Connection.nativeSQL("{fn teradata_amp_count()}")
Returns the number of AMPs configured for a 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 provides a method for quickly loading large amounts of data into an empty destination table in a database. The application must provide the data as an InputStream containing variable-length text in Comma Separated Values (CSV) format. The default separator is ',' (comma). Other separators are supported, as indicated in Field Separators Supported by JDBC FastLoad CSV. This feature is available beginning with Teradata JDBC Driver 13.00.00.26.
JDBC FastLoad CSV is enabled with TYPE=FASTLOADCSV in the URL connection string. When enabled, the FastLoad protocol is used with the 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:
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.
InputStream inStream = ...
PreparedStatement pstmt = con.prepareStatement("INSERT INTO ...");
try {
if(con.getWarnings() != null)
...
pstmt.setAsciiStream(1, inStream, -1);
pstmt.executeUpdate(); // assuming auto-commit is turned on
if(con.getWarnings() != null || pstmt.getWarnings() != null)
...
} finally {
pstmt.close();
}
DatabaseName.TableName_ERR_1
and DatabaseName.TableName_ERR_2
.
For example, if the destination DatabaseName is guest
and the destination TableName is FastLoadExample
, then
the two error tables will be named guest.FastLoadExample_ERR_1
and
guest.FastLoadExample_ERR_2
.
The default behavior is to create the error tables in the same database as the destination table being loaded.
Beginning with Teradata JDBC Driver 16.00.00.31, the location and suffixes of the error tables can be specified with the connection parameters
ERROR_TABLE_1_SUFFIX, ERROR_TABLE_2_SUFFIX
and ERROR_TABLE_DATABASE.
The error tables must not exist before starting JDBC FastLoad CSV.
Beginning with Teradata JDBC Driver 14.00.00.31, after the application calls the Connection commit or rollback method,
the application must query and drop the error tables.
Prior to Teradata JDBC Driver 14.00.00.31, after the PreparedStatement is closed,
the application must query and drop the error tables.
For more details on the purpose of the two error tables, see the section on Error Recording in the Teradata FastLoad Reference.
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.
JDBC FastLoad CSV uses field separators in the InputStream data set of variable-length text to separate columns of data. The default separator is ',' (comma), but it can be changed to any other character from '\u0000' to '\u007f' of the Unicode Basic Multilingual Plane character set, except for the following characters:
Commonly-used separators, other than comma, are '\u003b' (';' semicolon), '\u007c' ('|' vertical line), and '\u0009' ('\t' tab).
Most printable separator characters, such as semicolon or vertical line, can be specified in the Teradata JDBC Driver Connection URL with no escaping or quoting needed. For example, a vertical line field separator is specified as follows within the Teradata JDBC Driver Connection URL.
Connection con = DriverManager.getConnection(
"jdbc:teradata://MySystem/FIELD_SEP=|,TYPE=FASTLOADCSV", user, password);
A Unicode escape sequence must be used when a non-printable or special character is used as the field separator. For example, a tab field separator is specified as follows within the Teradata JDBC Driver Connection URL. Note that when a backslash ('\') is included in a Java string literal, it must be escaped with a preceding backslash character.
Connection con = DriverManager.getConnection(
"jdbc:teradata://MySystem/FIELD_SEP=\\u0009,TYPE=FASTLOADCSV", user, password);
Some printable characters are significant for the Teradata JDBC Driver Connection URL itself. Either a Unicode escape sequence may be used, or the field separator may be enclosed within single quotes. For example, when a comma is explicitly specified as the field separator, it must be enclosed by single quotes within the Teradata JDBC Driver Connection URL.
Connection con = DriverManager.getConnection(
"jdbc:teradata://MySystem/FIELD_SEP=',',TYPE=FASTLOADCSV", user, password);
If the data values in the data set might contain all the common field separator and quote characters, then the ASCII Unit Separator (US) control character ('\u001f') can be used as the field separator, since it is the control character designated for this purpose.
JDBC FastExport provides a method for quickly retrieving large amounts of data from a 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.
JDBC FastExport is enabled with TYPE=FASTEXPORT in the URL connection string. When enabled, the FastExport protocol is used with the 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 database.
To qualify for JDBC FastExport, the SQL SELECT statement must meet the following criteria:
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.
Returns the number of AMPs configured for a database. The information helps in determining the maximum number of JDBC FastExport connections that can be created.
Returns comma-separated pairs of a JDBC FastExport-capable PreparedStatement.hashCode() and the associated Logon Sequence Number (LSN) of any JDBC FastExport PreparedStatement created by this Connection.
For example, a string of "6166383,1850,22323092,1851" indicates that two JDBC FastExport PreparedStatements are active. The first JDBC FastExport PreparedStatement has hash code 61663483 and uses LSN 1850. The second has hash code 22323092 and uses LSN 1851.
The information helps in finding the DBC.SessionInfo.SessionNo of JDBC FastExport connections, as shown in Program Examples. The LSN is not allocated until JDBC FastExport becomes active. JDBC FastExport becomes active when ? parameter markers are used in a WHERE clause condition and at least one column value is bound using the JDBC FastExport PreparedStatement. Otherwise, JDBC FastExport becomes active when the JDBC FastExport PreparedStatement is executed.
JDBC Monitor provides a method for accessing and using standard performance monitoring and production control functions contained within the database.
JDBC Monitor is enabled with the PARTITION=MONITOR connection parameter. When enabled, the Monitor protocol is used exclusively to communicate with the database. Only database PM/API Monitor commands can be executed with the Monitor protocol. SQL DML and DDL statements cannot be executed using the Monitor protocol.
The 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 21 describes the MONITOR PHYSICAL CONFIG statement.
Parameter |
PreparedStatement |
Description |
---|---|---|
1 (mon_ver_id) |
setShort |
Monitor software version id |
Table 22 describes the MONITOR PHYSICAL RESOURCE statement.
Parameter |
PreparedStatement |
Description |
---|---|---|
1 (mon_ver_id) |
setShort |
Monitor software version id |
Table 23 describes the 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 25 describes the MONITOR SQL statement.
Table 26 describes the MONITOR VERSION statement.
Parameter |
PreparedStatement |
Description |
---|---|---|
1 (mon_ver_id) |
setShort |
Monitor software version id |
Table 27 describes the MONITOR VIRTUAL CONFIG statement.
Parameter |
PreparedStatement |
Description |
---|---|---|
1 (mon_ver_id) |
setShort |
Monitor software version id |
Table 28 describes the MONITOR VIRTUAL RESOURCE statement.
Parameter |
PreparedStatement |
Description |
---|---|---|
1 (mon_ver_id) |
setShort |
Monitor software version id |
Table 29 describes the 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 31 describes the 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.
Parameter |
PreparedStatement |
Description |
---|---|---|
1 (mon_ver_id) |
setShort |
Monitor software version id |
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 database to the application as another Java byte array.
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") ;
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 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 database, and does not wait for the 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 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 database. The application is wholly responsible for interpreting the response message, including any Error or Failure parcels.