Using FastExport with Teradata JDBC Driver

Tools
N/A

Using FastExport with Teradata JDBC Driver

Hi,

We need to be able to invoke FastExport from Java, using the Teradata JDBC Driver. I have successfully gotten this to work, thanks to the examples here: http://www.teradata.com/DownloadCenter/Topic9361-99-1.aspx

However, we want to be able to invoke FastExport in this way so as to generate an output file. The examples only show how to obtain a ResultSet object. Is this possible? I can't seem to find any documentation of the actual Teradata JDBC API in a javadoc or similar format anywhere.

TIA
8 REPLIES
Teradata Employee

Re: Using FastExport with Teradata JDBC Driver

To help avoid security problems, we have a design policy that the Teradata JDBC Driver should not use the Java APIs for file system access. The Teradata JDBC Driver only accesses files/resources on the classpath via Class.getResourceAsStream, which is read-only.

The Teradata JDBC Driver does not write to the file system; and in particular, the JDBC FastExport feature does not output to a file. You are correct that the JDBC FastExport feature only provides data to your application via a ResultSet.

Writing output to a file is the responsibility of your application.

Enthusiast

Re: Using FastExport with Teradata JDBC Driver

I'm having an issue using the JDBC driver with the FastExport option & I'm hoping that you [teradata form folks] can help...

WITHOUT fastexport, i can execute the query, snag the resultset -- skip to the last() record & grab my record count using getRow().  This works.

Last week -- i switched up to using FastExport in JDBC.  I converted my code to use Prepared statements.  I can pull data no problem.  BUT -- when i skip to the last record using last() & use getRow() like before, i get the way wrong number.  I'm only using 1 session (passing "Sessions=1") -- & the difference in the numbers isn't an even multiple of anything.  AND i'm using the exact same query...

Without fastexport -- i get:  21,175,639. 

When i use fastexport, i get:   2,147,483,646.

any ideas?

Thanks!!!

-Chuck

Supporter

Re: Using FastExport with Teradata JDBC Driver

And which number is correct?

I don't fully get what you wanne do. Do you export the data or do you only wanne know the row count?

Enthusiast

Re: Using FastExport with Teradata JDBC Driver

i want both.

ultimately, i want to know how many records i'm going to extract (ie: how many rows the query is returning);  I'm pulling large amounts of data out & I need updates to see if/how it's running so that i can determine if it's going faster than other methods -- or if it's working at all.  So i'm using the count as a means to display a visual update of % complete.  In addition, i'm using it as a checks & balances to say that the query was expecting to return x rows & that i actually got x rows.

the non-fast-export # is the correct value.

I'm just puzzled why the fast-export one doesn't return the correct # & then how to get the correct # using the fast export connection.

I keep thinking that there should be something in the resultset which would just tell you how many rows are coming back - but i haven't found it yet. 

any help you can offer would really be appreciated.

Thanks!

Teradata Employee

Re: Using FastExport with Teradata JDBC Driver

JDBC FastExport does not support scrollable result sets. That limitation comes from the Teradata Database, because the FastExport wire protocol does not support scrollable result sets.

You did find a problem with the Teradata JDBC Driver, however...

When the application calls Connection.prepareStatement on a TYPE=FASTEXPORT connection, and specifies ResultSet.TYPE_SCROLL_INSENSITIVE, the Teradata JDBC Driver should bypass JDBC FastExport for that particular PreparedStatement and add a SQLWarning to the Connection to indicate that JDBC FastExport cannot be used with a scrollable ResultSet.

However, there is a bug (JDBC DR 161123) in the Teradata JDBC Driver, and it doesn't provide that behavior.

Instead, the application receives the behavior that you described -- the ResultSet appears to function somewhat like a scrollable ResultSet, but it is not correct.

To avoid the JDBC DR 161123 bug, your application shouldn't specify ResultSet.TYPE_SCROLL_INSENSITIVE with a TYPE=FASTEXPORT connection. JDBC FastExport only supports TYPE_FORWARD_ONLY ResultSets.

Regarding your desire to know how many rows are in a ResultSet before fetching the rows...

The FastExport wire protocol doesn't provide a row count to the Teradata JDBC Driver, so the Teradata JDBC Driver doesn't know how many rows to expect. (In fact, that's where the 2,147,483,646 number comes from -- the Teradata JDBC Driver initially guesses the row count will be Integer.MAX_VALUE.)

In contrast, the Teradata SQL wire protocol does provide a row count to the Teradata JDBC Driver after the query is executed, and before the rows are fetched. It's called the "activity count", and it is an accurate row count most of the time; however, under certain conditions the actual row count can differ from the activity count.

There is feature request (JDBC RFC 121720) for the Teradata JDBC Driver to make the activity count available to an application. However, as I noted, it would be a bad idea for an application to expect that the acitivity count will exactly match the actual row count in all cases. Instead, the application should treat the activity count as an approximate row count. The bad news is that we haven't scheduled work on JDBC RFC 121720 yet.

As I discuss in my blog post http://developer.teradata.com/blog/tomnolan/2012/03/jdbc-resultset-pagination, there are only a couple of ways to obtain an accurate ResultSet row count prior to fetching the rows:

1. Execute select count(*) from ... prior to the actual query. This technique works with a regular SQL connection, and also works with JDBC FastExport.

2. Use a scrollable ResultSet, call ResultSet.last and ResultSet.getRow. This technique is not usable with JDBC FastExport.

Enthusiast

Re: Using FastExport with Teradata JDBC Driver

Tomnolan, 

about the fast export jdbc connectivity. is there any detailed instructions how to get the row count before fetch by not using select count(*)? When using native Fastexpoprt utility , this tool  usually reports number of blocks being generated before the rows fetched from database . 

When using ODBC and .NET connections under SQLA, the resultset rowsize is also reported and visible before the rows were fetched. 

why not JDBC? it will be a good idea to fetch such info since it appears to me that it is available anyway. 

Teradata Employee

Re: Using FastExport with Teradata JDBC Driver

>>> about the fast export jdbc connectivity. is there any detailed instructions how to get the row count before fetch by not using select count(*)?

Select count(*) is the only way to obtain the row count prior to FastExport.

>>> When using native Fastexpoprt utility , this tool usually reports number of blocks being generated before the rows fetched from database .

There are three issues with the block count: (1) there is a variable number of rows in each block, so there is no way to estimate the row count from the block count; (2) the block count is only available with spooled FastExport, it's not available with a direct FastExport from a table that bypasses spool; and (3) the Teradata JDBC Driver does not provide the block count to a Java application.

>>> When using ODBC and .NET connections under SQLA, the resultset rowsize is also reported and visible before the rows were fetched. why not JDBC? it will be a good idea to fetch such info since it appears to me that it is available anyway.

SQLA displays the activity count, which is an approximate row count. There is feature request (JDBC RFC 121720) for the Teradata JDBC Driver to make the activity count available to an application. We haven't scheduled work on JDBC RFC 121720 yet.

Teradata Employee

Re: Using FastExport with Teradata JDBC Driver

Teradata JDBC Driver 14.00.00.33 is now available for download here on Developer Exchange. It contains the fix for DR 161123 ("Bypass JDBC FastExport for unsupported ResultSet scrollability, concurrency, or holdability").

This was the problem reported by Chusa01 when the application calls Connection.prepareStatement on a TYPE=FASTEXPORT connection, and specifies ResultSet.TYPE_SCROLL_INSENSITIVE.