All Forums Connectivity
emilwu 72 posts Joined 12/07
14 Feb 2011
ExecuteUpdate -- -row count return type is int

I noticed that the statement.executeUpdate() method returns int data type , which conforms to JDBC standard. However, here is the question: it is Teradata!!! the affected row count can easily blow away the integer range. How to deal with such issue?
Noticeably the BTEQ affected row count is also relies on a smaller 32 bit data type range limit. Any tricks that can be played to get around the issue?

tomnolan 594 posts Joined 01/08
14 Feb 2011

The Teradata Database handles this issue.

Here is the relevant excerpt from the Teradata Database Messages manual, for code 6813:

6813 Numeric overflow in internal counters. The number of rows returned is actual number of rows returned, modulo 2^32.

Explanation: DR64568-VP185005-01 -> This warning message is sent within SUCCESS/OK parcel when the activitycount (i.e., the number of rows) returned, overflows the current max limit of (2^32 - 1). The request is successful. The value returned to the user is : (actual number of rows returned) modulo 2^32. NOTE: If the user knows that the actual number of rows returned cannot exceed 2^33, then the actual number of rows returned can be derived as follows: actual number of rows returned = 2^32 + the value returned to the user. DR64568-VP185005-01 <-

Generated By: DIS modules.

For Whom: End User.

Remedy: This message is for informational purposes only.

Chusa01 9 posts Joined 05/09
17 May 2011

Is there a way to get the "activityCount" (or updated rows/selected rows) after using the execute() method? (outside of looping thru & retrieving every row)? I've been trying to use the last() method then & using the getrow(), but i can't seem to get that to work -- it always gives me an exception saying that my result set is FORWARD ONLY... I even tried creating a statement with a TYPE_SCROLL_INSENSITIVE resultsetType, but it doesn't work. Any ideas (or suggestions)? It seems like this should be super simple, but i can't seem to figure it out.

thanks!

tomnolan 594 posts Joined 01/08
18 May 2011

For a DML statement that does not return rows, such as an INSERT, UPDATE, or DELETE statement, the Teradata "activity count" is the same concept as the JDBC API "update count". You can retrieve the update count from the Statement.getUpdateCount method.

However, it sounds as though you want to obtain the Teradata activity count for a DML statement that returns rows, such as a SELECT statement. The JDBC API does not offer a way for an application to obtain the row count of a result set.

The Teradata JDBC Driver and the Teradata Database have supported TYPE_SCROLL_INSENSITIVE result sets for several years now, beginning with Teradata JDBC Driver 03.02.00.00 (released in 2004) and Teradata Database V2R5.1 (released in 2003).

As you noted, there is an inefficient workaround for obtaining the row count -- you can use a TYPE_SCROLL_INSENSITIVE result set, you can position to the last row of the result set with the ResultSet.last method, and then obtain the row count from the ResultSet.getRow method.

Chusa01 9 posts Joined 05/09
23 May 2011

Apparently, the TYPE_SCROLL_INSENSITIVE only works IF... the LOB_SUPPORT is ON. For whatever reason, i had that set to "OFF" when i connected to the database; once i turned that ON... the resultSet.last(); & then resultSet.getRow() worked perfectly. The timing wasn't bad with a small dataset -- am wondering how bad it might be if it's millions of records.

But, I would have thought that I should have gotten an exception when i tried to createStatement() & specifically requested the SCROLL_INSENSITIVE... shouldn't have said -- "Lob_support not on, scrollable cursor not permitted?" -- or something?

stmt=tdConn.createStatement(ResultSet.TYPE_SCROLL_INSE NSITIVE, ResultSet.CONCUR_READ_ONLY);

Also -- when debugging, there's a m_nActivityCount inside of the resultSet structure... which already has the result count in it - after i execute my query. It just seems odd that i can't figure out a way to get to that value and/or that they haven't provided the means to share that critical piece of information.

PS -- thanks for the reply. Its much appreciated.

tomnolan 594 posts Joined 01/08
23 May 2011

>>> I would have thought that I should have gotten an exception when i tried to createStatement() & specifically requested the SCROLL_INSENSITIVE... shouldn't have said -- "Lob_support not on, scrollable cursor not permitted?" -- or something?

In that situation, a JDBC Driver is supposed to provide a SQLWarning and "downgrade" the Statement, rather than throw an exception.

In particular, the Teradata JDBC Driver adds a SQLWarning to the Connection's warning chain -- with Error Code 1241, and Message "Downgraded to ResultSet.TYPE_FORWARD_ONLY because TYPE_SCROLL_INSENSITIVE is not supported when LOB_SUPPORT is OFF"

The application can obtain the Connection's warning chain by calling the Connection.getWarnings method.

tomnolan 594 posts Joined 01/08
03 Oct 2012

The activity count 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. We haven't scheduled work on JDBC RFC 121720 yet.
If and when we implement JDBC RFC 121720, it would be a bad idea for an application to expect the activity count to exactly match the actual row count in all cases. Instead, the application should treat the activity count as an approximate row count.
 

You must sign in to leave a comment.