Teradata JDBC Driver Not Cancelling Statement

Tools & Utilities
Enthusiast

Teradata JDBC Driver Not Cancelling Statement


I'm using the Teradata JDBC Driver version 16.20.00.12. I'm unable to cancel long running queries that are returning a large amount of rows.

 

For example, I have a with 18 million rows. When I run any query, I am unable to cancel the query while the driver streams data back. Even after initiating the cancel on the statement, the driver continues to send response messages. Here are some logs:

 

// example query, keeps returning rows even after cancel
select
  *
from
  table
sample
500000

Logs:

// Logs

2019-07-10.23:15:16.515 TERAJDBC4 INFO [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb StatementReceiveState.action getState=2 nRemainingTime=252515 nTimeoutInMs=0 this=com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState@13420131(req#=6 stmt#=1 atype=1 acnt=500000 currs=com.teradata.jdbc.jdk6.JDK6_SQL_ResultSet@31869be1 ctlr=com.teradata.jdbc.jdbc_4.statemachine.StatementController@1d85fd29(sql=/*'QuerygeneratedbyP stmt=com.teradata.jdbc.jdk6.JDK6_SQL_Statement@19ba6a41(statecode=2 sess=com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb)))
2019-07-10.23:15:16.562 TERAJDBC4 INFO [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb Read message 1: resizing buffer from 65156 bytes to 487612 bytes
2019-07-10.23:15:16.631 TERAJDBC4 TIMING [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb Read Continue Response message 1, 487612 bytes, time: 116 ms
2019-07-10.23:15:16.644 TERAJDBC4 TIMING [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb Wrote Continue Request message, 69 bytes, time: 0 ms
2019-07-10.23:15:16.644 TERAJDBC4 INFO [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb StatementReceiveState.action getState=2 nRemainingTime=252386 nTimeoutInMs=0 this=com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState@13420131(req#=6 stmt#=1 atype=1 acnt=500000 currs=com.teradata.jdbc.jdk6.JDK6_SQL_ResultSet@31869be1 ctlr=com.teradata.jdbc.jdbc_4.statemachine.StatementController@1d85fd29(sql=/*'QuerygeneratedbyP stmt=com.teradata.jdbc.jdk6.JDK6_SQL_Statement@19ba6a41(statecode=2 sess=com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb)))
2019-07-10.23:15:16.698 TERAJDBC4 INFO [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb Read message 1: resizing buffer from 65156 bytes to 487634 bytes
2019-07-10.23:15:16.788 TERAJDBC4 TIMING [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb Read Continue Response message 1, 487634 bytes, time: 144 ms

// Cancel query

2019-07-10 23:15:16 T#0x1f 338/2500/2500MB: Canceling query with uuid app-1-e3478611be368a8852adcb8fa08f1c950110d01b46205ac28605d5af85305116-1562800513
2019-07-10 23:15:16 T#0x1f 338/2500/2500MB: Starting thread to cancel query
2019-07-10 23:15:16 T#0x21 338/2500/2500MB: Attempting to cancel JdbcQuery: app-1-e3478611be368a8852adcb8fa08f1c950110d01b46205ac28605d5af85305116-1562800513
2019-07-10 23:15:16 T#0x21 338/2500/2500MB: Now canceling statement: app-1-e3478611be368a8852adcb8fa08f1c950110d01b46205ac28605d5af85305116-1562800513
2019-07-10 23:15:16 T#0x21 338/2500/2500MB: Canceled: app-1-e3478611be368a8852adcb8fa08f1c950110d01b46205ac28605d5af85305116-1562800513

// Response continues to stream back

2019-07-10.23:15:16.806 TERAJDBC4 TIMING [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb Wrote Continue Request message, 69 bytes, time: 0 ms
2019-07-10.23:15:16.806 TERAJDBC4 INFO [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb StatementReceiveState.action getState=1 nRemainingTime=252224 nTimeoutInMs=0 this=com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState@13420131(req#=6 stmt#=1 atype=1 acnt=500000 currs=com.teradata.jdbc.jdk6.JDK6_SQL_ResultSet@31869be1 ctlr=com.teradata.jdbc.jdbc_4.statemachine.StatementController@1d85fd29(sql=/*'QuerygeneratedbyP stmt=com.teradata.jdbc.jdk6.JDK6_SQL_Statement@19ba6a41(statecode=1 sess=com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb)))
2019-07-10.23:15:16.843 TERAJDBC4 INFO [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb Read message 1: resizing buffer from 65156 bytes to 487580 bytes
2019-07-10.23:15:16.903 TERAJDBC4 TIMING [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb Read Continue Response message 1, 487580 bytes, time: 97 ms
2019-07-10.23:15:16.910 TERAJDBC4 TIMING [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb Wrote Continue Request message, 69 bytes, time: 0 ms
2019-07-10.23:15:16.911 TERAJDBC4 INFO [pool-3-thread-2] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb StatementReceiveState.action getState=1 nRemainingTime=252120 nTimeoutInMs=0 this=com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState@13420131(req#=6 stmt#=1 atype=1 acnt=500000 currs=com.teradata.jdbc.jdk6.JDK6_SQL_ResultSet@31869be1 ctlr=com.teradata.jdbc.jdbc_4.statemachine.StatementController@1d85fd29(sql=/*'QuerygeneratedbyP stmt=com.teradata.jdbc.jdk6.JDK6_SQL_Statement@19ba6a41(statecode=1 sess=com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5062caeb)))
....
11 REPLIES 11
Teradata Employee

Re: Teradata JDBC Driver Not Cancelling Statement

From the Teradata JDBC Driver Reference: "If the Teradata Database is still processing the statement when the cancel method is called, then the statement will be cancelled. If the statement had been completed upon receipt of the cancel request, the statement will not be cancelled."

 

If the driver is already "streaming back rows", then statement execution has been completed. You'll need a different approach to force the other thread to stop fetching rows (and then either explicitly close the result set, log off, or just issue a new request using the same statement object). Or you could perhaps issue an AbortSession database function from another session to stop the retrieval (see the Programming Reference manual for more information on use of PM API functions).

Teradata Employee

Re: Teradata JDBC Driver Not Cancelling Statement

@Fredis correct. Thanks for the response!

Enthusiast

Re: Teradata JDBC Driver Not Cancelling Statement

@Fred is there a native way to stop fetching rows?

 

"and then either explicitly close the result set, log off"

 

Can you provide more information about explicitly closing the result or logging of using the JDBC driver? I'm not familiar with this and am unable to find more information in the docs. 

 

"issue an AbortSession database function from another session to stop the retrieval"

 

For this portion, I remember reading in your docs somewhere that Teradata only runs one statement at a time (might be mistaken here)? It can accept concurrent requests but do they get queued up? Finally, does this queue apply to running a AbortSession database function from the JDBC driver itself? 

Teradata Employee

Re: Teradata JDBC Driver Not Cancelling Statement

Check the documentation here
https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html

One session can have only one request at a time. An admin abort would require logging on another session to issue the abort request.
Teradata Employee

Re: Teradata JDBC Driver Not Cancelling Statement


@jshah wrote:

When I run any query, I am unable to cancel the query while the driver streams data back.


What application are you using?

 

Keep in mind that the Teradata JDBC Driver is a passive library, meaning that the Teradata JDBC Driver does not create and run its own threads for processing result sets.

 

The Teradata JDBC Driver only uses the application's thread when the application calls a JDBC API method, and when the JDBC Driver returns control to the app, then the JDBC Driver is completely inactive until called again by the app.

 

So there is no scenario in which "the driver streams data back". Instead, the JDBC Driver only fetches rows from the Teradata Database because the app has requested rows from the JDBC Driver.

 

Given all this explanation, I hope that it is clear that if the application wants to stop fetching rows, then the app simply stops making JDBC API calls to the JDBC Driver. This behavior is entirely under the app's control, not the JDBC Driver's control.

Enthusiast

Re: Teradata JDBC Driver Not Cancelling Statement


@tomnolan wrote:


Given all this explanation, I hope that it is clear that if the application wants to stop fetching rows, then the app simply stops making JDBC API calls to the JDBC Driver. This behavior is entirely under the app's control, not the JDBC Driver's control.


The scenario I'm experiencing is not that we want to stop making JDBC API calls but rather when I have a query that returns large results, I want to cut off the response after a certain limit which gets programtically determined as I parse the response. If I can't get it to stop returning the rows, my service gets overloaded with those rows being returned, especially as there are other queries being run. Something to note is that, once the limit is reached, I stop calling resultSet.next() but the data is still being fetched and returned.

 

I need some functionality to stop rows being fetched from the query. I've tried executing a new query on the same statement which seems to do the trick (leading solution currently) but seems wacky as I have to run another query to cancel it. Looking for a more elegant way to get the fetch rows to close.

I've tried closing the result set but doesn't do the trick. I'm not sure what "logging off" for the jdbc driver means. Still looking into "AbortSession".

Teradata Employee

Re: Teradata JDBC Driver Not Cancelling Statement


@jshah wrote:

 

Something to note is that, once the limit is reached, I stop calling resultSet.next() but the data is still being fetched and returned.


That is simply not possible.

 

As I said earlier, the Teradata JDBC Driver does not create or run a thread to fetch data, and the Teradata JDBC Driver does not fetch data on its own.

Enthusiast

Re: Teradata JDBC Driver Not Cancelling Statement


@tomnolan wrote:


That is simply not possible.

 

As I said earlier, the Teradata JDBC Driver does not create or run a thread to fetch data, and the Teradata JDBC Driver does not fetch data on its own.


I just tested and I do agree that the rows stop being retrieved when I stop calling resultSet.next() however, after I "cancel" the statement and I stop calling resultSet.next(), I see write and response messages coming back from the driver when I have "LOG=INFO". Do you know what that is? Posted a snippet below. I believe that is what's causing the memory leak.

 

On the flip side, when I run a new query on the statement, these response messages do not keep being returned by the driver.

 

# Last row read
2019-07-17 18:38:17 T#0x18 1286/2500/2500MB: severity=error ********** Just called resultSet.next() 2019-07-17 18:38:17 T#0x18 1286/2500/2500MB: severity=error [1122256774, 2017-08-27, 125.44, 11862.16, 7757.14, 6903.89, 853.25, 0.00, organic, 2017, RichmondNo]

# Cancelled statement, closed resultSet 2019-07-17 18:38:17 T#0x18 1286/2500/2500MB: severity=error Aborting read, for app-1-b9c028daea60e447624b8663eff3b94d5289c3e8db4755db3b9a13965ca23d53-1563388668, too much data: 59474 rows, 5242953 chars. 2019-07-17 18:38:17 T#0x18 1286/2500/2500MB: Canceling query with uuid app-1-b9c028daea60e447624b8663eff3b94d5289c3e8db4755db3b9a13965ca23d53-1563388668 2019-07-17 18:38:17 T#0x1e 1286/2500/2500MB: Attempting to cancel JdbcQuery: app-1-b9c028daea60e447624b8663eff3b94d5289c3e8db4755db3b9a13965ca23d53-1563388668 2019-07-17 18:38:17 T#0x1e 1286/2500/2500MB: Now canceling statement: app-1-b9c028daea60e447624b8663eff3b94d5289c3e8db4755db3b9a13965ca23d53-1563388668 2019-07-17 18:38:17 T#0x1e 1286/2500/2500MB: Canceled: app-1-b9c028daea60e447624b8663eff3b94d5289c3e8db4755db3b9a13965ca23d53-1563388668

# What are these messages that continue coming back from the driver?
# I believe this is what is causing the memory leak as the response messages keep coming back.
2019-07-17.18:38:17.473 TERAJDBC4 TIMING [pool-3-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@529b7260 Wrote Continue Request message, 60 bytes, time: 0 ms 2019-07-17.18:38:17.473 TERAJDBC4 INFO [pool-3-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@529b7260 StatementReceiveState.action getState=1 nRemainingTime=227093 nTimeoutInMs=0 this=com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState@48aff58(req#=2 stmt#=1 atype=1 acnt=500000 currs=com.teradata.jdbc.jdk6.JDK6_SQL_ResultSet@2e34eb07 ctlr=com.teradata.jdbc.jdbc_4.statemachine.StatementController@a4d11b9(sql=/*'QuerygeneratedbyP stmt=com.teradata.jdbc.jdk6.JDK6_SQL_Statement@e729428(statecode=1 sess=com.teradata.jdbc.jdk6.JDK6_SQL_Connection@529b7260))) 2019-07-17.18:38:17.513 TERAJDBC4 INFO [pool-3-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@529b7260 Read message 1: resizing buffer from 65156 bytes to 2096896 bytes 2019-07-17.18:38:19.064 TERAJDBC4 TIMING [pool-3-thread-1] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@529b7260 Read Continue Response message 1, 2096896 bytes, time: 1591 ms

 

Teradata Employee

Re: Teradata JDBC Driver Not Cancelling Statement


@jshah wrote:

after I "cancel" the statement and I stop calling resultSet.next(), I see write and response messages coming back from the driver when I have "LOG=INFO". Do you know what that is?

Cancelling a Statement by calling the Statement.cancel method is only effective while the SQL request is executing on the database.

 

Calling the Statement.cancel method does nothing after the SQL request has finished executing. Calling the Statement.cancel method does nothing while your app is fetching rows by calling the ResultSet.next method.

 

Those particular Continue Request and Continue Response log messages you listed are due to your app calling the ResultSet.next method.

 

When your app calls the ResultSet.next method to fetch a row, and the JDBC Driver has no more rows in its buffer, then the JDBC Driver transmits a Continue Request message to fetch another set of rows from the database.