Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Resource Limit Not Being Applied to JDBC connection

7 posts in General Discussion Last posting was on 2010-08-03 18:07:14.0Z
Mark Maslow Posted on 2010-07-28 20:57:07.0Z
From: Mark Maslow <mark.maslow@sierraclub.org>
Newsgroups: sybase.public.ase.general
Subject: Resource Limit Not Being Applied to JDBC connection
Message-ID: <MPG.26ba3976e21f448b98971b@forums.sybase.com>
Organization: Sierra Club
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.70.2067
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 28 Jul 2010 13:57:07 -0700
X-Trace: forums-1-dub 1280350627 10.22.241.152 (28 Jul 2010 13:57:07 -0700)
X-Original-Trace: 28 Jul 2010 13:57:07 -0700, vip152.sybase.com
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29425
Article PK: 78657

I am attempting to apply a resource limit to a particular user id in
order to limit the time that a query can run. I used
sp_add_resource_limit to set a maximum elapsed of 60 seconds for this
user for any application.

When I log in as that user via RapidSQL and execute a query, the query
stops after the allotted time with the message "query batch running time
exceeded limit of 60".

I have a web application which logs into the database via a JDBC
connection pool with the same user id. The resource limit doesn't seem
to apply to queries submitted via this web application. I am able to
monitor the process using DBArtisan. I can see that the connection has
the same user id, and is executing the same SQL as I did in RapidSQL,
but it is not timing out.

Does anyone have any idea why the resource limit is not being applied in
to queries submitted by the web application?


Luc Van der Veurst Posted on 2010-07-29 11:39:18.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <MPG.26ba3976e21f448b98971b@forums.sybase.com>
Subject: Re: Resource Limit Not Being Applied to JDBC connection
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
X-Forwarded: by - (DeleGate/5.8.7)
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c516866$1@forums-1-dub>
Date: 29 Jul 2010 04:39:18 -0700
X-Trace: forums-1-dub 1280403558 10.22.241.152 (29 Jul 2010 04:39:18 -0700)
X-Original-Trace: 29 Jul 2010 04:39:18 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29427
Article PK: 78659

Are you sure that your application doesn't resubmit the query when
it fails ? Check the errorlog of the server for resource limit messages.

We also have a jdbc connection pool (established by an application server)
and resource limits and this works as expected. So what you are
experiencing doesn't seem to be general behaviour.

Luc.

"Mark Maslow" <mark.maslow@sierraclub.org> wrote in message
news:MPG.26ba3976e21f448b98971b@forums.sybase.com...
>I am attempting to apply a resource limit to a particular user id in
> order to limit the time that a query can run. I used
> sp_add_resource_limit to set a maximum elapsed of 60 seconds for this
> user for any application.
>
> When I log in as that user via RapidSQL and execute a query, the query
> stops after the allotted time with the message "query batch running time
> exceeded limit of 60".
>
> I have a web application which logs into the database via a JDBC
> connection pool with the same user id. The resource limit doesn't seem
> to apply to queries submitted via this web application. I am able to
> monitor the process using DBArtisan. I can see that the connection has
> the same user id, and is executing the same SQL as I did in RapidSQL,
> but it is not timing out.
>
> Does anyone have any idea why the resource limit is not being applied in
> to queries submitted by the web application?


Mark Maslow Posted on 2010-07-29 21:23:37.0Z
From: Mark Maslow <mark.maslow@sierraclub.org>
Newsgroups: sybase.public.ase.general
Subject: Re: Resource Limit Not Being Applied to JDBC connection
Message-ID: <MPG.26bb912fbed57a4698971c@forums.sybase.com>
References: <MPG.26ba3976e21f448b98971b@forums.sybase.com> <4c516866$1@forums-1-dub>
Organization: Sierra Club
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.70.2067
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 29 Jul 2010 14:23:37 -0700
X-Trace: forums-1-dub 1280438617 10.22.241.152 (29 Jul 2010 14:23:37 -0700)
X-Original-Trace: 29 Jul 2010 14:23:37 -0700, vip152.sybase.com
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29429
Article PK: 78660

In article <4c516866$1@forums-1-dub>, dba_azvub@hotmail.com says...

> Are you sure that your application doesn't resubmit the query when
> it fails ? Check the errorlog of the server for resource limit messages.
>
> We also have a jdbc connection pool (established by an application server)
> and resource limits and this works as expected. So what you are
> experiencing doesn't seem to be general behaviour.
>

It turns out that #1 below works as expected, but #2 does not - it just
seems to hang. I guess this is a bug - Sybase TS has reproduced, and is
looking into it.

#1:
Statement stmt2 = con.createStatement("exec sp_test");
stmt2.execute(query);

#2:
CallableStatement cstmt = con.prepareCall("sp_test");
ResultSet rs = cstmt.executeQuery();


Mark Maslow Posted on 2010-07-30 00:01:41.0Z
From: Mark Maslow <mark.maslow@sierraclub.org>
Newsgroups: sybase.public.ase.general
Subject: Re: Resource Limit Not Being Applied to JDBC connection
Message-ID: <MPG.26bbb62d674e0cd598971d@forums.sybase.com>
References: <MPG.26ba3976e21f448b98971b@forums.sybase.com> <4c516866$1@forums-1-dub>
Organization: Sierra Club
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.70.2067
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 29 Jul 2010 17:01:41 -0700
X-Trace: forums-1-dub 1280448101 10.22.241.152 (29 Jul 2010 17:01:41 -0700)
X-Original-Trace: 29 Jul 2010 17:01:41 -0700, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29430
Article PK: 78664

In article <4c516866$1@forums-1-dub>, dba_azvub@hotmail.com says...

> Are you sure that your application doesn't resubmit the query when
> it fails ? Check the errorlog of the server for resource limit messages.
>
> We also have a jdbc connection pool (established by an application server)
> and resource limits and this works as expected. So what you are
> experiencing doesn't seem to be general behaviour.

In case anyone else ever has this problem -

Method1 does not respect resource limits, but method2 does. Presumably,
this will get fixed some day. But the workaround, once discovered, is
simple and perfectly acceptable.


METHOD1:

String callString = "{call dbo.ExecSelection(?)}";
CallableStatement cstmt = con.prepareCall(callString);
cstmt.setLong(1, 28299);
ResultSet rs = cstmt.executeQuery();


METHOD2:

String query = "exec dbo.ExecSelection ?";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setLong(1, 28299);
ResultSet rs = pstmt.executeQuery();


J Posted on 2010-08-03 17:14:47.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Resource Limit Not Being Applied to JDBC connection
Reply-To: J@bogusemailAddress.com
Message-ID: <4c584645.1132450125@forums.sybase.com>
References: <MPG.26ba3976e21f448b98971b@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 3 Aug 2010 10:14:47 -0700
X-Trace: forums-1-dub 1280855687 10.22.241.152 (3 Aug 2010 10:14:47 -0700)
X-Original-Trace: 3 Aug 2010 10:14:47 -0700, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29439
Article PK: 78674

On 28 Jul 2010 13:57:07 -0700, Mark Maslow
<mark.maslow@sierraclub.org> wrote:

Mark,

The dataserver understands two kinds of commands from the clients: (1)
language commands and (2) remote procedure calls. When you are using
CallableStatement in jConnect (jdbc driver) then you are using an RPC.
From a little test I conducted the problems is not in the jConnect
driver, it appears to be in the ASE handling of the stored procedure
when submitted as an RPC.

My research finds that "elapsed time" is not enforced for "stored
procedure execution". I am guessing that that thinking there is that
someone has built a procedure and given permissions for a user to
execute it so it is a bit different from an "ad hoc query".

Jay

>I am attempting to apply a resource limit to a particular user id in
>order to limit the time that a query can run. I used
>sp_add_resource_limit to set a maximum elapsed of 60 seconds for this
>user for any application.
>
>When I log in as that user via RapidSQL and execute a query, the query
>stops after the allotted time with the message "query batch running time
>exceeded limit of 60".
>
>I have a web application which logs into the database via a JDBC
>connection pool with the same user id. The resource limit doesn't seem
>to apply to queries submitted via this web application. I am able to
>monitor the process using DBArtisan. I can see that the connection has
>the same user id, and is executing the same SQL as I did in RapidSQL,
>but it is not timing out.
>
>Does anyone have any idea why the resource limit is not being applied in
>to queries submitted by the web application?


Mark Maslow Posted on 2010-08-03 17:31:55.0Z
From: Mark Maslow <mark.maslow@sierraclub.org>
Newsgroups: sybase.public.ase.general
Subject: Re: Resource Limit Not Being Applied to JDBC connection
Message-ID: <MPG.26c1f2514b027a3c989720@forums.sybase.com>
References: <MPG.26ba3976e21f448b98971b@forums.sybase.com> <4c584645.1132450125@forums.sybase.com>
Organization: Sierra Club
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.70.2067
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 3 Aug 2010 10:31:55 -0700
X-Trace: forums-1-dub 1280856715 10.22.241.152 (3 Aug 2010 10:31:55 -0700)
X-Original-Trace: 3 Aug 2010 10:31:55 -0700, vip152.sybase.com
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29440
Article PK: 78671

In article <4c584645.1132450125@forums.sybase.com>,
jtotally_bogus@sbcglobal.net says...

> On 28 Jul 2010 13:57:07 -0700, Mark Maslow
> <mark.maslow@sierraclub.org> wrote:
>
> Mark,
>
> The dataserver understands two kinds of commands from the clients: (1)
> language commands and (2) remote procedure calls. When you are using
> CallableStatement in jConnect (jdbc driver) then you are using an RPC.
> From a little test I conducted the problems is not in the jConnect
> driver, it appears to be in the ASE handling of the stored procedure
> when submitted as an RPC.
>
> My research finds that "elapsed time" is not enforced for "stored
> procedure execution". I am guessing that that thinking there is that
> someone has built a procedure and given permissions for a user to
> execute it so it is a bit different from an "ad hoc query".
>

The Sybase TS Engineer that I worked with considers this an ASE
"defect" and has reported it as such (case # 11620645). If it turns out
that it is actually a "feature", and works the way it was intended to
work, then it must at least be clearly documented.


J Posted on 2010-08-03 18:07:14.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Resource Limit Not Being Applied to JDBC connection
Reply-To: J@bogusemailAddress.com
Message-ID: <4c585a8c.1137640906@forums.sybase.com>
References: <MPG.26ba3976e21f448b98971b@forums.sybase.com> <4c584645.1132450125@forums.sybase.com> <MPG.26c1f2514b027a3c989720@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 3 Aug 2010 11:07:14 -0700
X-Trace: forums-1-dub 1280858834 10.22.241.152 (3 Aug 2010 11:07:14 -0700)
X-Original-Trace: 3 Aug 2010 11:07:14 -0700, vip152.sybase.com
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29441
Article PK: 78672

On 3 Aug 2010 10:31:55 -0700, Mark Maslow <mark.maslow@sierraclub.org>
wrote:

I agree. I would have recommended that you handle it this way and am
glad that you did. I am in Sybase support and we should at least
document the restriction if there is one.

Jay

>In article <4c584645.1132450125@forums.sybase.com>,
>jtotally_bogus@sbcglobal.net says...
>> On 28 Jul 2010 13:57:07 -0700, Mark Maslow
>> <mark.maslow@sierraclub.org> wrote:
>>
>> Mark,
>>
>> The dataserver understands two kinds of commands from the clients: (1)
>> language commands and (2) remote procedure calls. When you are using
>> CallableStatement in jConnect (jdbc driver) then you are using an RPC.
>> From a little test I conducted the problems is not in the jConnect
>> driver, it appears to be in the ASE handling of the stored procedure
>> when submitted as an RPC.
>>
>> My research finds that "elapsed time" is not enforced for "stored
>> procedure execution". I am guessing that that thinking there is that
>> someone has built a procedure and given permissions for a user to
>> execute it so it is a bit different from an "ad hoc query".
>>
>
>The Sybase TS Engineer that I worked with considers this an ASE
>"defect" and has reported it as such (case # 11620645). If it turns out
>that it is actually a "feature", and works the way it was intended to
>work, then it must at least be clearly documented.
>