Type IV JDBC Driver for Teradata
|
Please note: TdRedux has been end-of-lifed as of Jan. 1, 2007. The information on
this page is provided for existing customers' reference. If you have an interest in
using TdRedux, please contact Presicient sales.
IntroductionTdRedux is a Type IV JDBC driver for Teradata. Type IV drivers are pure Java, direct connection drivers. No "bridges", "gateways", or JNI adaptors are required. TdRedux includes support for fastload, fastexport, multiload, PM/API (MONITOR), and Remote Console. JDBC 2.0 Optional ClassesTdRedux supports the following javax.sql optional classes:
The DataSource and ConnectionPoolDataSource can be configured with the following properties:
|
Support for IBM WebSphere™ ServersAs of release 1.20, significant testing with WebSphere 4.0 and 5.1 has been performed. Refer to this page for information on setting up and using TdRedux with WebSphere. RestrictionsThe following major capabilities are currently not supported:
Multi-threaded applications
should avoid sharing Connection objects across multiple threads
(i.e., multiple threads shouldn't access the same ResultSet at the same time.), with the exception
of one thread issuing a Both USING clauses and placeholder ('?') parameters are supported; however, they cannot be mixed in the same statement. The exceptions are
Unimplemented MethodsThe following JDBC 2.0 standard methods will throw a NotImplemented exception; most of these methods are unimplmented due to:
Driver Specific ExtensionsThe following methods have been provided to
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Array BindingAt present, the JDBC 2.0 standard does not provide optimizations for bulk data movement. (Note: the Batch Execution interface provides some support, however, the code pathlength required to individually bind each parameter for each statement nullifies much of the benefit of such an interface). This ommission may create a serious performance impediment for purely standards-compliant JDBC applications in terabyte datawarehouse environments. To remedy the situation, I've added some array binding methods as outlined above.
For bulk export via either a regular SQL SELECT statement, or a
set of EXPORT sessions, the application can create arrays of any of the primitive
types (as specified above) and bind them to the ResultSet object returned by either
PreparedStatement.execute() or Statement.executeQuery(). Note the following rules:
//
// export a complete table in raw mode
//
try {
com.presicient.tdredux.PreparedStatement pstmt =
(com.presicient.tdredux.PreparedStatement)conn.prepareQuery("SELECT * FROM mytable");
com.presicient.tdredux.ResultSet rs =
(com.presicient.tdredux.ResultSet)pstmt.executeQuery();
byte rows[][] = new byte[100];
rs.tdrdxBindBinaryArray(0, rows);
int rows_rcvd = 0;
while (rs.next()) {
rows_rcvd = rs.tdrdxGetRowsFetched();
//
// proceed to write out the rows
//
for (int i = 0; i < rows_rcvd; i++)
exportfile.write(rows[i]);
}
}
catch (SQLException SQLE) {...}
For bulk import to the DBMS, arrays may be bound to parameters using the PreparedStatement methods described above. At present, only SQL and FASTLOAD sessions can exploit this interface, though a future release will provide support for repeated execution. Note the following:
An example:
//
// import a complete table in raw mode
//
try {
com.presicient.tdredux.PreparedStatement pstmt =
conn.prepareStatement("INSERT INTO mytable VALUES(?, ?, ?, ?, ?");
byte rows[][] = new byte[100][];
pstmt.tdrdxBindBinaryArray(0, rows);
while (// data remains to load ) {
for (int i = 0; i < 100; i++) {
int len = importfile.readunsignedShort();
rows[i] = importfile.read(len);
importfile.skipBytes(1); // skip newline
}
// ...load data into the rows array...
for (int i = 0; i < 100; i++)
pstmt.executeUpdate();
}
}
catch (SQLException SQLE) {...}
Summarized SELECT ResultSetsA set of methods has been provided to support the OLAP functions available via theSELECT...WITH... statement. When a ResultSet contains summary information, additional columns
are defined, along with a set of "summary row" indexes, corresponding to each WITH clause in the
SELECT statement; as a convenience, summary row 0 is used to indicate the non-summary row
to all the relevant ResultSet and ResultSetMetaData methods.
ResultSetMetaData.tdrdxHasSummaryRows() can be called to determine if a ResultSet
includes summary data.
An application determines that the current ResultSet row is a summary row by calling
ResultSet.tdrdxIsSummaryRow(). Since a SELECT can have multiple WITH clauses, the application calls
ResultSet.tdrdxGetSummaryRowNumber()
to determine which summary row is currently available; if not on
a summary row, zero is returned.
ResultSetMetaData.tdrdxIsSummaryColumn(int column) or
ResultSetMetaData.tdrdxIsSummaryColumn(String columnname)
can be called to determine if a specified column is part of a summarized row; this can be useful for determining
when to stop reading column values if the ResultSet currently holds a non-summary row. An application
manipulates the summary data by:
Formatted ResultsAs of release 1.40, TdRedux supports returning results in Teradata formatted form (ala BTEQ report), using the FMREQUEST parcel instead of the usual REQUEST/INDICREQUEST parcel, to send the SQL request to the database.To enable a formatted ResultSet:
Note that formatted ResultSets report all fields as TYPE_VARCHAR in the ResultSetMetaData; any attempt to retrieve a field using other than getString() will cause a SQLExceptionto be thrown. InstallationCurrently there is no formal install packaging. For Windows systems, simply unZIP the ZIP package to a directory of your choosing; for UNIX systems, gunzip/untar to a directory of your choosing. Then add the TdRedux.jar file - including the complete directory path - to your CLASSPATH environment variable or the "-classpath" argument on your "java" commandline. E.g.,CLASSPATH=$CLASSPATH:/usr/local/jars/TdRedux.jar;export CLASSPATH [ for UNIX ] set CLASSPATH=%CLASSPATH%;C:\TdRedux\TdRedux.jar [ for Windows ] Testing the InstallationThe release package includes a test application, TdRdxTest, which runs through a battery of tests, including
javac TdRdxTest.java FastloadThread.java MultiloadThread.java ExportThread.java To run TdRdxTest you'll need a Teradata user account capable of CREATE'ing and DROP'ing tables and stored procedures, executing INSERTs, DELETEs and SELECTs against them, referencing system data dictionary views, as well as running FASTLOADs, MULTILOADs, and FASTEXPORTs. Only a small amount of database space is required, a max of 10,000 rows (about 100 bytes each) is loaded by a max of 2 FASTLOAD sessions. An additional userid is used to test the MONITOR API; this user needs all the various privileges required to SET SESSION RATE, SET RESOURCE RATE, and MONITOR xxx. If this additional user is omitted, then the MONITOR tests will be bypassed. |
|
The actual commandline is:
java TdRdxTest <hostname> <general-user-id> <general-user-password> [<monitor-user-id> <monitor-user-password>]
E.g., java TdRdxTest dbc darnold sde356yu dbc dbc This test will spew a lot of information, you may want to redirect output to a file to capture it in case things don't behave as expected. Note that several of the tests will return errors (they're actually testing error handling). In addition, a simple remote console application "console.java" is included which can be used to test the remote console interface. Be advised that remote console requires the following to be setup on the DBMS:
To run the console.java:
java console <hostname> <user-id> <password> <console-app-name>
E.g., java console dbc darnold sde356yu DBSCONTROL |
Connection URLThe format of the URL used during connection is
jdbc:tdredux:host[:port][;option[;...]]where
|
jdbc:tdredux:dbc;user=dbitst;password=dbitst;partition=FASTLOAD;lsn=23564;sessmode=TERADATA;debug=fltest.log;buffersize=16;database=mydb |
Connection Properties
The various connection options listed above may be set using the alternate
The complete description can be queried at runtime using the Driver.getPropertyInfo() method. SQLSTATE ValuesSQLExceptions may be thrown due to any of
Prerequisite SoftwareTdRedux requires the following prerequisite software:
NOTE:As of release 1.39, TdRedux no longer requires the Jakarta ORO package to be separately downloaded and installed. A modified version of the package is now included in the TdRedux JAR, per the terms of the Apache License (a copy of the license is included in the TdRedux bundle). However, the package name has been modified in order to overcome issues with various application servers (most notably, IBM WebSphere) which use functionally-limited or antiquated versions of the ORO package, and thus cause irreparable classpath conflicts with any separately installed version. Also note that this change requires a change in the WebSphere JDBC Provider template (also included in this bundle). The following examples are taken from the TdRdxTest application which accompanies the release package.
ExamplesTypical Usage ExampleReview the various tests contained in TdRdxTest.java. Summary Rows Usage Example
try {
rs = (com.presicient.tdredux.ResultSet)stmt.executeQuery(
"select col11, col2, col9 from alltypetst with avg(col2), avg(col9) by col11 with sum(col2)");
rsmd = (com.presicient.tdredux.ResultSetMetaData)rs.getMetaData();
while (rs.next()) {
int sumrow = rs.tdrdxGetSummaryRowNumber();
if (sumrow == 0)
continue;
int i = rsmd.tdrdxGetFirstSummaryColumn(sumrow);
int colcnt = rsmd.tdrdxGetSummaryColumnCount(sumrow);
for (int j = 0; j < colcnt; i++, j++) {
System.out.println(rsmd.getColumnName(i) + "(" +
rsmd.getColumnLabel(i) + ") align to " +
rsmd.tdrdxGetAlignmentColumn(i) + ":");
switch (rsmd.getColumnType(i)) {
case Types.INTEGER:
System.out.println(rs.getInt(i));
break;
case Types.SMALLINT:
System.out.println(rs.getShort(i));
break;
case Types.TINYINT:
System.out.println(rs.getByte(i));
break;
case Types.DOUBLE:
System.out.println(rs.getDouble(i));
break;
case Types.CHAR:
System.out.println(rs.getString(i));
break;
case Types.VARCHAR:
System.out.println(rs.getString(i));
break;
case Types.DECIMAL:
System.out.println(rs.getBigDecimal(i));
break;
case Types.DATE:
System.out.println(rs.getDate(i));
break;
default:
System.out.println("Unknown type");
}
}
}
}
catch (java.sql.SQLException SqlEx) {
System.out.println("SQLException: " + SqlEx.getMessage());
}
FASTLOAD Usage ExampleDetailed instructions for using Fastload via TdRedux. MLOAD Usage ExampleDetailed instructions for using Multiload via TdRedux. FASTEXPORT Usage ExampleDetailed instructions for using Fastexport via TdRedux. MONITOR Usage Example
Review the Remote Console Usage Example
Review the Implementation NotesMETADATA
|
|
The remaining fields return the following values for the respective types:
|
|
Some of these values may seem odd, but e.g., PRECISION has different meanings for different types. I've tried
to base the values on the appropriate sections of the Teradata SQL Data Types Manual.
CALL statement is prepared. Therefore, the user calling
the stored procedure needs access to the associated metadata.
ANSI vs. TERADATA MODE
STORED PROCEDURES
UPDATABLE CURSORS
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()Teradata does not have a native boolean column type. As a convenience, a driver-specific ResultSet methodstdrdxSetBooleanMap(String trueValue)
and tdrdxSetBooleanMap(int trueValue) are provided to
allow an application to define any int or String value to be interpretted
as 'true' when the returned column value equals any of the registered trueValues.
NULL column values are always returned as 'false', and
string-typed columns must match the case of the specified trueValue object.
Note that the boolean map applies only to the ResultSet object
on which it is called.
E.g., to register the values 'TRUE', 'T', and 'YES' as true for a given ResultSet:
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 PooledConnections
MISCELLANEOUS
CHANGE HISTORY
TO DOThe following list includes features under consideration for future releases.
|
Teradata® is a registered trademark of NCR Corporation.
WebSphere® is a registered trademark of IBM Corporation.