Type IV JDBC Driver for Teradata

ANSI vs. TERADATA MODE

• A session will be set to ANSI mode under the following conditions:
  1. The connection is for a DBC/SQL session that does NOT allocate or associate with an LSN AND
  2. The DBMS defines ANSI as the default mode and the connection URL omits the sessmode option OR
  3. The connection URL includes the ";sessmode=ANSI" option
  The ";sessmode=ANSI" URL option will be ignored for non-SQL sessions, or sessions which use an LSN.
• Remember that a statement failure does NOT terminate a transaction in ANSI mode. TdRedux will raise SQLExceptions whenever it receives a failure or error indication from the DBMS; the application may choose to rollback() immediately, or may continue to process ResultSets (possibly receiving further SQLExceptions).

STORED PROCEDURES

• CallableStatements are now supported. My previous assertion that CALL statements could be safely executed via Statement.executeQuery() or PreparedStatement.execute() was incorrect due to changes in the PREPARE phase to support stored procedures.
• Stored procedures should ONLY be executed via the CallableStatement class, due to the PREPARE changes.
• Be aware that CallableStatement must refer to database metadata in order to completely differentiate between IN and INOUT arguments. Therefore, users of applications which invoke stored procedures must have access to the metadata views (specifically, the DBC.TablesX and ColumnsX views), and the PREPARE phase may be more time-consuming than for other SQL statements.
• Refer to the Getting Started With the JDBC API document for details of argument handling. In brief, arguments are specified as either literals or placeholders, and the columnIndex passed to the getXXX() methods map to the associated placeholder position. E.g., assume the following procedure definition:

  CREATE PROCEDURE proc1(OUT arg1 INT, IN arg2 FLOAT, INOUT arg3 CHAR(20)) ...
  

  and the following CALL of that procdure:

  CALL proc1(?, ?, ?)
  

  The OUT parameter arg1 is registered and retrieved with a parameter index of 1, the IN parameter arg2 is set with a parameter index of 2, and the INOUT parameter arg3 is set, registered, and retrieved with a parameter index of 3. An attempt to execute a "getXXX()" against columnIndex 2 will raise an exception.
• USING clauses are not supported with CALL statements.
• The executeQuery() method should not be used with CallableStatement objects, since Teradata stored procedures do not return result sets, just OUT and INOUT argument values. The executeUpdate() or execute() method should be used instead.
• I have not yet tested explicit CAST syntax, though I'm not certain that syntax is very useful in a JDBC context anyway.
• At present, CREATE/REPLACE PROCEDURE statements are limited to about 64,000 bytes. A future release will add support for the Multi-TSR request to allow larger procedure definition.
• When CREATE'ing/REPLACE'ing procedures, the console PRINT capability can be enabled or disabled by first calling the Statement.tdrdxSetSPLPrint(boolean) method with a true argument to enable, or false argument to disable, console printing of included PRINT statements. The current state of the PRINT flag can be checked via theStatement.tdrdxGetSPLPrint() method. The default value is false (i.e., PRINT'ing is disabled).
• Likewise, when CREATE'ing/REPLACE'ing procedures, the saving of procedure source can be enabled or disabled by first calling the Statement.tdrdxSetSPLSave(boolean) method with a true argument to enable, or false argument to disable, the saving of the procedure source. The current state of the SAVE flag can be checked via theStatement.tdrdxGetSPLSave() method. The default value is true (i.e., source is saved).
• The executeQuery() method should be used for CREATE/REPLACE PROCEDURE statements. In the event of compilation errors, a SQLWarning will be returned, and the errors can be fetched from the associated ResultSet.

UPDATABLE CURSORS

• Note that updatable cursors are only supported in ANSI mode, and are subject to the same restrictions listed in the Teradata SQL Reference Manuals.
• Positioned operations require that AUTOCOMMIT be disabled.
• Explicit cursor SQL syntax (e.g., "SELECT...FROM... FOR CURSOR", "UPDATE ... SET... WHERE CURRENT OF...") is not supported; instead, applications should use the relevant JDBC 2.0 cursor functions:
  1. To create an updatable cursor, the create/prepareStatement() method should be called with the resultSetConcurrency argument set to ResultSet.CONCUR_UPDATABLE.
  2. Calling the executeQuery() method will open the cursor.
  3. To apply a positioned update, use the ResultSet.updateXXX() methods to set the various fields of the current row, then call ResultSet.updateRow() to apply the updates (Note that the application does not supply any SQL statement for the update).
  4. To apply a positioned delete, call ResultSet.deleteRow(). After deleteRow() is called, the current cursor row will be invalid until ResultSet.next() called.
  5. Rows may be inserted by moving to the "insert" row (ResultSet.moveToInsertRow()), calling the various ResultSet.updateXXX() methods to populate the row, and then calling ResultSet.insertRow() to apply the insert. The application can then return to the current cursor row by calling ResultSet.moveToCurrentRow().
• Updates, deletes, and inserts applied in this fashion are only visible after the current transaction is committed.

    try {
        java.sql.Statement cursor =
            conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                java.sql.ResultSet.CONCUR_UPDATABLE);
        java.sql.ResultSet cursorrs = cursor.executeQuery("SELECT * FROM alltypetest");
        while (cursorrs.next()) {
            int curcol1 = cursorrs.getInt(1);
            if (curcol1%3 == 0) {
                cursorrs.updateShort(2, (short)0);
                cursorrs.updateString("col5",
                    "dsgsfglkjty kjhtyejhyrjhtylkjrhtykerjhtykrjhtyklerjmiyt");
                cursorrs.updateRow();
                continue;
            }
            if (curcol1%4 == 0) {
                cursorrs.deleteRow();
                continue;
            }
            if (curcol1%5 == 0) {
                cursorrs.moveToInsertRow();
                cursorrs.updateInt("col1", curcol1 + 124000);
                cursorrs.updateShort("col2", (short)23);
                cursorrs.updateByte("col3", (byte)1);
                cursorrs.updateString(4, "wqeryyy");
                cursorrs.updateFloat(6, (float)1.2345);
        // leave everything else NULL
                cursorrs.insertRow();
                cursorrs.moveToCurrentRow();
            }
        }
        conn.commit();
    }
    catch (java.sql.SQLException SqlEx) {
        System.out.println("SQLException: " + SqlEx.getMessage());
    }

USING getBoolean()

com.presicient.tdredux.ResultSet rs = (com.presicient.tdredux)pstmt.executeQuery();
rs.tdrdxSetBooleanMap("TRUE");
rs.tdrdxSetBooleanMap("T");
rs.tdrdxSetBooleanMap("YES");

Notes on Date and Timestamp Types

• Using PreparedStatement.setDate() for a placeholder (as opposed to a USING clause parameter) will result in the provided value being converted to a VARCHAR string of the form 'YYYY-MM-DD' for transfer to the DBMS.
• Teradata currently has an issue with using placeholders that are compared with TIME or TIMESTAMP types within predicate clauses which causes an error to be generated when the statement is prepared. As of release 1.20, TdRedux attempts to suppress this error by
  1. silently discarding the ANSI Date/time error during prepareStatement()
  2. when the statement is executed, constructing a modified version of the statement to explicitly cast placeholders bound to TIME or TIMESTAMP values to their respective type (unless the placeholder is already explicitly cast), and re-preparing it.
  3. assuming the modified version is successfully prepared, the modified statement is then executed.
  Note that this process only works in ANSI mode, or in Teradata mode with autocommit set to true. In Teradata mode with autocommit turned off, the initial failure of the prepareStatement will cause transaction rollback, which must be reported to the application. To minimize any impacts to your applications, you should explicitly cast any TIME or TIMESTAMP placeholders, e.g.,WHERE timestamp_column < ?(timestamp(0)).... Also note that write-only placeholders (i.e., placeholders in the VALUES clause of an INSERT or the SET clause of an UPDATE) do not appear to have this issue, and so do not require the explicit cast.
• As of release 1.20, TdRedux now supports the escaped version of date, time, and timestamp literals ( {d 'YYYY-MM-DD'}, {t 'HH:MM:SS'}, {ts 'YYYY-MM-SS HH:MM:SS.SSS'}).
• As of release 1.20, TdRedux now provides support for the Calendarized version of get/setDate, get/setTime, and get/setTimestamp; however, these implementations should be considered experimental, and users are advised to fully analyze their use.
• As of release 1.24, TdRedux provides array binding interfaces for the TIME and TIMESTAMP type (tdrdxBindTimeArray(),tdrdxBindTimestampArray())
• TdRedux assumes INTEGERDATE format if a DATE parameter is specified via a USING clause. Be sure to force your session to INTEGERDATE via "SET SESSION DATEFORM=INTEGERDATE" if you intend to specify DATE parameters via USING clauses. Note that fastload sessions will inherit their DATEFORM from the associated control SQL session, so set the DATEFORM on the control session before attempting to fastload with a DATE parameter in the USING clause.

Using PooledConnections

• TdRedux will attempt to "clean up" Connections returned to a ConnectionPool by
  • closing any open statements
  • rolling back any open transaction
  • restoring AutoCommit to true

  However, applications should explicitly close their Statement objects, and either commit or rollback any open transactions prior to closing a pooled connection.

• Various Teradata session commands (SET SESSION DATEFORM, SET SESSION ACCOUNT, SET TIME ZONE, DATABASE, etc.) can leave a pooled connection in an unknown operational mode. Applications should either
  • never use those commands
  • always restore the session state to a known, predefined state

  TdRedux may provide the ability to block such commands in a future release.

• While TdRedux can detect database restarts, and other "soft" network failures, the ability to detect "hard" network failures (e.g., network switch or router failures, or a hard restart of the database platform) is only available on platforms which are running JVM 1.3 or greater, as Java did not provide the ability to enable TCP/IP's KEEPALIVE capability until JVM 1.3. If your network is subject to frequent hard failures, and you are still running on JVM 1.2, you may want to consider upgrading to JVM 1.3.

  In addition, since Java's setKeepAlive() interface uses the simple BSD form (i.e., either enable or disable KEEPALIVE, rather than specify the actual KEEPALIVE intervals), the length of time required to detect a network failure is dependent on the hosting platform's default KEEPALIVE settings. You may need to tune those settings in order for network failures to be detected in an acceptable amount of time.

• releases of TdRedux prior to 1.10 allowed applications to reuse Statement (and PreparedStatement and CallableStatement) objects after they had been explcitly close()'d. In support of PooledConnections, this JDBC non-conformance has been corrected. Unfortunately, if your application had inadvertantly been reusing Statements after they had been closed, your application may break when using this release.

MISCELLANEOUS

• As of release 1.20, TdRedux has been formally tested as a Java application on Windows 2000, Mac OS X, Red Hat Linux 7.3, and Solaris 8.0. Other users have reported success on RS6000/AIX, and OS/390 platforms.
• TdRedux uses only Java-independent data types; this appears to Teradata as a Sun/HP/IBM/iMac type workstation.
• When exporting data from a TdRedux application, be aware that the data is also in Java platform-independent format.
• The array binding interfaces should be considered deprecated as of release 1.20. and may be removed in a future release, to be superceded by the standard Batch execution interface.
• Be aware that the Statement.cancel() method may have no impact if the DBMS has completed processing the statement, and that result sets may continue to return some rows after the cancel() is issued. In addition, the cancel() may take a significant time to complete, in the event the DBMS must rollback a significant amount of work for the overall transaction.
• FASTEXPORT support has only been tested on simple queries (e.g., SELECT * FROM table).
• When a query times out (i.e., the period defined by Statement.setQueryTimeout() is exceeded), it is automatically cancel()'ed, which may take a significant time to complete depending on query complexity. In addition, in Teradata session mode, the timeout will result in implicit transaction rollback.
• Applications load the driver by either:
  • explicitly calling Class.forname("com.presicient.tdredux.Driver") within their application before calling DriverManager.getConnection(my_url)
  • editting their .hotjava/properties file and adding "com.presicient.tdredux.Driver" to the jdbc.drivers property list, e.g.
    

    jdbc.drivers=com.oracle.jdbc.OracleDriver:com.presicient.tdredux.Driver;
    

  • configuring a DataSource or ConnectionPoolDataSource in their environment.
• "Batches" have not been thoroughly tested, and are only supported for non-parameterized statements (i.e., only Statement objects).
• Remember to explicitly close() ResultSet, Statement, PreparedStatement, and CallableStatement objects when finished with them. Failure to do so may lead to apparent memory leaks as internal buffers and contexts accumulate. TdRedux attempts to clean up internal buffers in certain circumstances to avoid such memory leaks, but the application must ultimately determine when resources are no longer needed.
• If a response message from the DBMS exceeds the connection URL buffersize, TdRedux will expand the buffersize as needed. Note that the process of buffer expansion can severely impact performance due to the additional message interchanges required between client and server. Be sure to tune your buffersize appropriately, or use the default (maximum) size.
• As of release 1.20, TdRedux has been tested against both the Sun JDBC Conformance Test Suite 1.2.1, and the IBM WebSphere Self Test Conformance test suite.

CHANGE HISTORY

• Release 8.01
  • added support for V2R5.1.1,5.1.2,6.0 logon encryption
  • improved logonsource information
• Release 1.42
  • fix for CallableStatement activity peek
  • Release 1.41
  • added V2R5.1 encrypted logon support
• Release 1.40
  • added ResultSetMetaData.tdrdxGetRowCount() to return the number of rows returned by a ResultSet
  • added ResultSetMetaData.tdrdxGetActivity() to return the name of the activity associated with a ResultSet
  • added support for Statement properties, via Statement.tdrdxSetProperty(), Statement.tdrdxGetProperty(), Statement.tdrdxRemoveProperty(), Statement.tdrdxTestProperty()
  • added support for formatted ResultSets
• Release 1.39
  • Jakarta ORO is now included in TdRedux package, with modified package name, in order to avoid classpath conflicts with various application servers (most notably, IBM WebSphere). Please review APACHE-ORO-LICENSE file for Apache license conditions.
• Release 1.38
  • fixed IO.closeResultSet() when closing due to exceeding maxRows (caused bad query timeout value)
• Release 1.37
  • fixed Statement.setMaxFieldSize() to support a param value of zero (which translates to "max" size)
• Release 1.36
  • added metaData connection property
• Release 1.35
  • fix for ResultSet.getDate() when returned integer date value is negative
• Release 1.34
  • fixed Account string append in DataSource.getUrl()
  • fix for TIME/TIMESTAMP placeholder bindings that don't match default format
• Release 1.33
  • fixed for multiplexing requests on single Connection
• Release 1.32
  • fixed DataSource to properly set account string property
  • modified Connection.getTypeMap() to return null rather than throw exception
• Release 1.31
  • fixed MONITOR SESSION input parameter order (due to bug in Teradata PM API docs)
• Release 1.30
  • added support for COPn hostname resolution
  • corrected the removal of strings and comments
  • corrected USING clause parse for names that include NOT keyword
  • converted string/comment removal from ORO to native java
  • V2R5 metadata updates
  • V2R5 PM API updates
  • V2R5 large SQL support
  • optimize I/O object partition testing
  • added com.presicient.tdredux.Connection.tdrdxGetHostID() function
  • fixed potential deadlock in diagnostic dump processing
• Release 1.29
  • added support for ERRORLIMIT for FASTLOAD,MLOAD
• Release 1.28
  • added support for MLOAD
  • V2R5.0 compatibility testing
  • 
    
• Release 1.27
  • fix for CallableStatement with only OUT parameters
• Release 1.26
  • fixed USING w/ TIMESTAMP w/ subsec precision < 3
  • fixed PREPARE optimization for CREATE/REPLACE (SET | MULTISET | VOLATILE | GLOBAL TEMPORARY) TABLE
  • removed logtable updates for fastexport test
  • forced session dateform to integerdate for fastload test
• Release 1.25
  • fixed USING w/ TIME/TIMESTAMP values
• Release 1.24
  • improved error reporting when exception thrown during logon
  • fixed PreparedStatement.tdrdxBindDateArray()
  • added PreparedStatement.tdrdxBindTimeArray(), PreparedStatement.tdrdxBindTimestampArray()
  • added Remote Console support, inlc. ResultSetMetaData.tdrdxConsolePrompt()
• Release 1.23
  • added global query timeout parameter to Connection URL
• Release 1.22
  • closed timing window which caused ineffective cancel() against Statement objects from another thread
  • improved exception info thrown from query timeout
• Release 1.21
  • added TRIM() to all CHAR columns returned from data dictionary queries in DatabaseMetaData class to improve interoperability w/ some 3rd party tools (e.g., Database Pilot in JBuilder 8)
  • fixed ANSI mode issue w/ reusing PreparedStatement objects which previously raised non-fatal errors (e.g., truncation) and left the assoc. ResultSet object(s) in an indeterminate state, cause subsequent uses of the PreparedStatement to return wo/ fetching results
  • fixed network socket issue to capture all exceptions caused by connection failure
  • fixed occasional spurious IndexOutOfBounds exception due to invalid request activity code returned from DBMS
• Release 1.20 Numerous changes to improve JDBC 2.0 conformance via the JDBC CTS:
  • fixed set/getBigDecimal() to use proper scale factor
  • fixed Statement and PreparedStatement handling of execute()/getUpdateCount()/getMoreResults() to conform to std.
  • fixed numerous Statement.setXXX capability methods to throw exceptions whne invalid values were supplied (setFetchSize, setFetchDirection, setMaxFieldSize, setMaxRows, setQueryTimeout)
  • fixed PreparedStatement constructor to throw exception on ANSI Date/Time error when inside transaction in Teradata mode (In Teradata mode, failure on PREPARE causes transaction rollback; hence, the fix to ignore the ANSI date/time error and re-prepare after parameters are bound will not work in that specific case)
  • fix DatabaseMetaData.getCatalogs to throw NotImplemented
  • fixed the SQL used for DatabaseMetaData.getColumns() to avoid Database numeric overflow error.
  • fixed getResultSetType() to return correct value
  • fixed PreparedStatement.clearParameters() to function properly; After first created, or after clearParameters() is called, any attempt to execute a statement with placeholders will throw an exception if all placeholders have not been explicitly set.
  • fixed DatabaseMetaData.supportsBatchUpdates() to return false
  • added support for the date, time and timestamp literals escape syntax ({d 'YYYY-MM-DD'}, {t 'HH:MM:SS'}, {ts 'YYYY-MM-DD HH:MM:SS.SSSS'})
  • fixed getTime() to properly trim subseconds before conversion
  • fixed getDriverVersion(), getURL(), nullsAreSupported(), and supportsStoredProcedures() in DatabaseMetaData to return correct values
  • improved error reporting when error is returned during attempt to connect (esp. "All virtual circuits are in use")
  • fixed CHAR/VARCHAR to number conversions (eg, getInt() on a CHAR column) to trim whitespace before conversion
  • fixed character set problem with diagnostic parcel stream logging
  • suppress exception when commit() is called while autoCommit=true (WebSphere self-test requirement)
  • suppress exception to Failure 3514 when rollback in Teradata mode
• Changes in TdRedux 1.19 in support of WebSphere 4.0:
  • add support for " FOR UPDATE" cursor syntax
  • suppress exception for setTransactionIsolation() because WebSphere doesn't check DatabaseMetaData for supported isolations
  • add setSessmode() methods because WebSphere cheats when using the DataSourceFactory; instead of using an empty Reference to determine which properties are supported, is requires each method to have a matching setXxxxx() method
  • modified close/cancel of updatable cursors to issue CANCEL when assoc. resultset is closed.
  • added description, networkProtocol, roleName DataSource properties
  • fix for setNull() for Types.DATE types
  • fix misparse of DATE from string when fetching or inserting data in ANSI session mode
  • Calendarized set/getTimestamp/Time/Date() (experimental)
  • get/setBinary/Ascii/CharacterStream() (experimental)
• Changes in TdRedux 1.18:
  • updated PreparedStatement.setTimestamp(), PreparedStatement.setTime() to support placeholders which have not been explicitly cast as TIMESTAMP or TIME type. Note that this capability does incur some add'l overhead during statement execution to properly update the SQL sent to the DBMS based on the type and precision of the value bound to the placeholder, so explicitly casting the placeholders in your SQL for such fields is more optimal.
  • fixed event notification to properly generate SQLException error msg and SQLSTATE (previously were swapped)
  • improved/clarified error msg returned by Connection.setTransactionIsolation() when other than TRANSACTION_SERIALIZABLE was specified; msg is now.
  • modified TdRdxConnectionPoolWrapper to properly report instances when the underlying physical connection was closed (eg, by a loss of network connection)
  • fixed "database=" URL property in ANSI mode to properly commit() upon completion of DATABASE stmt
  • changed source of database version and release information
• Release 1.14
  • fix protocol error on commit() before ResultSet.close()
• Release 1.13
  • fix PreparedStatement.clearParameters() when no params defined
  • fix PreparedStatement.setXXX() parameter binding when no params defined
• Release 1.12
  • fix diagnostic logging
  • fix early ResultSet.close()
• Release 1.10
  • Support for javax.sql extensions:
    • DataSource
    • ConnectionPoolDataSource
    • PooledConnection
    • ConnectionEvent, ConnectionEventListener
  • fix PreparedStatement.setDate()
  • fix setTransactionIsolation()
  • fix BUFFERSIZE=64 overflow in connection URL
  • fix explicit port specification in connection URL
• Release 1.05
  • fix DatabaseMetadata.storesUpperCaseIdentifiers(), storesMixedCaseIdentifiers()
  • fix null pattern arguments in various DatabaseMetadata methods
• Release 1.03
  • corrected Statement regression introduced in 1.02
• Release 1.02
  • fix for PreparedStatement reuse
  • optimize Statement.executeUpdate() to skip prepare step
  • PM API updates for V2R4.1
  • metadata updates for V2R4.1
• Release 1.01
  • fix for DATE type conversion
• Release 1.00
  • First general availability release.
  • removed explicit DriverManager logWriter dependency
  • fixed large response buffer expansion

TO DO

• performance tuning
• javadocs
• UNICODE
• Reconnection
• quoted identifiers
• JDBC 3.0 updates
• JDK 1.4 updates
• full escape clause syntax support
• Teradata V2R5.1 LOBs, UDF creation

COPYRIGHT

Copyright© 2001-2009, Presicient Corp., USA. All rights reserved.