SQLCancel not working with UDB?

Peter Arrenbrecht

We have a setup with a v7 client accessing a v8 UDB server running on
AIX. When I call SQLCancel from a second thread on a long running query,
SQLCancel itself simply waits for statement completion too. That was not
the idea, no? Does anyone know how to fix this? Or is it not supported
(despite being described in the DB2 help)?

Regards
Peter Arrenbrecht
Opus Software AG

Nov 12 '05 #1

Knut Stolze

Peter Arrenbrecht wrote:

We have a setup with a v7 client accessing a v8 UDB server running on
AIX. When I call SQLCancel from a second thread on a long running query,
SQLCancel itself simply waits for statement completion too. That was not
the idea, no? Does anyone know how to fix this? Or is it not supported
(despite being described in the DB2 help)?

What's your long-running query doing on the server?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Nov 12 '05 #2

Peter Arrenbrecht

It's doing something like

SELECT COUNT(*)
FROM HugeTable A, HugeTable B

which is, obviously, designed to be running for quite a while so I can
test SQLCancel. It does not work with something like

SELECT *
FROM BigTable
WHERE SomeField LIKE '%m%'

either, though. During the fetch phase it works OK, but that is not
where I need it. It should abort the execution of the initial
SQLExec/SQLPutData.
Knut Stolze wrote:

Peter Arrenbrecht wrote:

We have a setup with a v7 client accessing a v8 UDB server running on
AIX. When I call SQLCancel from a second thread on a long running query,
SQLCancel itself simply waits for statement completion too. That was not
the idea, no? Does anyone know how to fix this? Or is it not supported
(despite being described in the DB2 help)?

What's your long-running query doing on the server?

Nov 12 '05 #3

Knut Stolze

Peter Arrenbrecht wrote:

It's doing something like

SELECT COUNT(*)
FROM HugeTable A, HugeTable B

which is, obviously, designed to be running for quite a while so I can
test SQLCancel. It does not work with something like

SELECT *
FROM BigTable
WHERE SomeField LIKE '%m%'

either, though. During the fetch phase it works OK, but that is not
where I need it. It should abort the execution of the initial
SQLExec/SQLPutData.

The way I read the CLI Reference is that SQLCancel does not guarantee that
the statement execution is indeed terminated because it says: "How the
function is canceled depends upon the operating system." and later on "If
the original function is canceled, ..."

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Nov 12 '05 #4

Richard D. Latham

Knut Stolze <st****@de.ibm.com> writes:

The way I read the CLI Reference is that SQLCancel does not guarantee that
the statement execution is indeed terminated because it says: "How the
function is canceled depends upon the operating system." and later on "If
the original function is canceled, ..."

Damn, I hate APIs where the name lies :-)

The Truth in Advertising act says that we should have called it
SQLAttemptCancel :-)

--
#include <disclaimer.std> /* I don't speak for IBM ... */
/* Heck, I don't even speak for myself */
/* Don't believe me ? Ask my wife :-) */
Richard D. Latham la*****@us.ibm.com

Nov 12 '05 #5

Peter Arrenbrecht

Well, that was Microsoft who named this one, I believe. Nevertheless, at
least your docs could make a little more specific claims as to where
it's supported and where not.
Richard D. Latham wrote:

Knut Stolze <st****@de.ibm.com> writes:

The way I read the CLI Reference is that SQLCancel does not guarantee that
the statement execution is indeed terminated because it says: "How the
function is canceled depends upon the operating system." and later on "If
the original function is canceled, ..."

Damn, I hate APIs where the name lies :-)

The Truth in Advertising act says that we should have called it
SQLAttemptCancel :-)

Nov 12 '05 #6

Serge Rielau

Peter,

When you run the same query from CLP, can you CTRL-C it?
I have never had a case where I couldn't CTRL-C, but it is thinkable and
would need to be looked at.

Cheers
Serge

Nov 12 '05 #7

robert

Peter Arrenbrecht <ar*********@NOXXX.opus.ch> wrote in message news:<41********@news.cybercity.ch>...

We have a setup with a v7 client accessing a v8 UDB server running on
AIX. When I call SQLCancel from a second thread on a long running query,
SQLCancel itself simply waits for statement completion too. That was not
the idea, no? Does anyone know how to fix this? Or is it not supported
(despite being described in the DB2 help)?

Regards
Peter Arrenbrecht
Opus Software AG

my preferred editor, Advanced Query Tool, stopped supporting asynchronous
queries in V8. they said it was because V8 stopped supporting it. so,
empirically, it worked. now it doesn't.

now, AQT is an ODBC based product; so shouldn't necessarily behave the
same on *nix. but if it is the same codebase.....

BobTheDataBaseBoy

Nov 12 '05 #8

Phil Castle

In reply to Robert's post, and speaking as the developer of AQT. In
DB2 v7 we used the "asynchronous queries" capability of the DB2 ODBC
Driver; we ran queries asynchronously and issued the SQLCancel in the
same thread. This async capability appears to be removed in the DB2 v8
ODBC Driver. To circumvent this, we are using multi-threading; the
queries run in one thread, a second thread runs the cancel if the user
selects this. The second thread does an SQLCancel; this seems to work
fine for cancelling the query (we have done much testing of this). The
SQLCancel returns immediately (I have *never* find it to hang), though
as Knut says, it can take a while for the query to actually terminate.

Phil Castle.
gn*****@rcn.com (robert) wrote in message news:<da**************************@posting.google. com>...

Peter Arrenbrecht <ar*********@NOXXX.opus.ch> wrote in message news:<41********@news.cybercity.ch>...

We have a setup with a v7 client accessing a v8 UDB server running on
AIX. When I call SQLCancel from a second thread on a long running query,
SQLCancel itself simply waits for statement completion too. That was not
the idea, no? Does anyone know how to fix this? Or is it not supported
(despite being described in the DB2 help)?

Regards
Peter Arrenbrecht
Opus Software AG

my preferred editor, Advanced Query Tool, stopped supporting asynchronous
queries in V8. they said it was because V8 stopped supporting it. so,
empirically, it worked. now it doesn't.

now, AQT is an ODBC based product; so shouldn't necessarily behave the
same on *nix. but if it is the same codebase.....

BobTheDataBaseBoy

Nov 12 '05 #9

Peter Arrenbrecht

Serge

No, I cannot. Same behavior: it waits for completion of the statement,
but then aborts the fetching of results:

db2 => select count(*) from bookop where bookop_text like '%m%'

1
-----------

0 record(s) selected.

I tested this repeatedly. Normal execution takes about 7 secs. Hitting
Ctrl+C right after starting it takes 7 secs as well.

The setup is as follows:

DB2 v7 client on Windows 2K accessing a DB2 v8 server on AIX.

I shall try to test it with a v8 client as well.

Regards,
peo
Serge Rielau wrote:

Peter,

When you run the same query from CLP, can you CTRL-C it?
I have never had a case where I couldn't CTRL-C, but it is thinkable and
would need to be looked at.

Cheers
Serge

Nov 12 '05 #10

Peter Arrenbrecht

Serge and all others,

It works fine with a v8 client. I shall suggest to my client they
upgrade the client to v8 soon.

Thanks for caring,
peo
Serge Rielau wrote:

Peter,

When you run the same query from CLP, can you CTRL-C it?
I have never had a case where I couldn't CTRL-C, but it is thinkable and
would need to be looked at.

Cheers
Serge

Nov 12 '05 #11

Knut Stolze

robert wrote:

Peter Arrenbrecht <ar*********@NOXXX.opus.ch> wrote in message
news:<41********@news.cybercity.ch>...

We have a setup with a v7 client accessing a v8 UDB server running on
AIX. When I call SQLCancel from a second thread on a long running query,
SQLCancel itself simply waits for statement completion too. That was not
the idea, no? Does anyone know how to fix this? Or is it not supported
(despite being described in the DB2 help)?

Regards
Peter Arrenbrecht
Opus Software AG

my preferred editor, Advanced Query Tool, stopped supporting asynchronous
queries in V8. they said it was because V8 stopped supporting it. so,
empirically, it worked. now it doesn't.

SQLCancel and asynchronous execution are two completely different beasts.

SQLCancel: One thread executes a long running SQL statement, and it is
blocked waiting for the database server to return the control to the
calling thread. SQLCancel can now be used from another thread to flow the
interrupt information to the database server. The server will then take
the necessary actions to terminate the query at a convenient time.

Async: Asynchronous execution differs because you don't need two threads.
An async statement execution causes the control to return immediately to
the caller. Of course, the statement might not have finished, so the
caller has to check once in a while to test if the execution is done now.
You don't need multiple threads here.

Asynchronous execution is indeed not documented any longer (but it should
still work for backward-compatibility only, I believe) because you can do
the same thing using multiple threads.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Nov 12 '05 #12