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.

query timeout?

5 posts in General Discussion Last posting was on 2009-09-09 14:27:37.0Z
George Brink Posted on 2009-09-08 19:59:57.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: query timeout?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa6b7bd$1@forums-1-dub>
Date: 8 Sep 2009 12:59:57 -0700
X-Trace: forums-1-dub 1252439997 10.22.241.152 (8 Sep 2009 12:59:57 -0700)
X-Original-Trace: 8 Sep 2009 12:59:57 -0700, vip152.sybase.com
Lines: 5
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28278
Article PK: 77523

Is it possible to specify the timeout for select statement on the server
level? I know I can set up the query timeout on the client side, but
right now we are looking for a way to kill long-running select queries
from an existing applications (we can not rewrite them).
Server is ASE 15.0.3


Jason L. Froebe Posted on 2009-09-08 21:59:50.0Z
From: "Jason L. Froebe" <jason.froebe@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: query timeout?
References: <4aa6b7bd$1@forums-1-dub>
In-Reply-To: <4aa6b7bd$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa6d3d6$1@forums-1-dub>
Date: 8 Sep 2009 14:59:50 -0700
X-Trace: forums-1-dub 1252447190 10.22.241.152 (8 Sep 2009 14:59:50 -0700)
X-Original-Trace: 8 Sep 2009 14:59:50 -0700, vip152.sybase.com
Lines: 14
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28281
Article PK: 77524


George Brink wrote:
> Is it possible to specify the timeout for select statement on the server
> level? I know I can set up the query timeout on the client side, but
> right now we are looking for a way to kill long-running select queries
> from an existing applications (we can not rewrite them).
> Server is ASE 15.0.3

Not directly. You could use David Wein's idle reaper
http://www.sypron.nl/sp__idleReaper.sql (uses the MDA tables)

Normally this would be handled in the connection settings of the
application (JDBC, CTLIB, ODBC, etc)

jason


Michael Peppler [Team Sybase] Posted on 2009-09-09 04:48:21.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: query timeout?
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2009.09.09.04.48.20.205968@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4aa6b7bd$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 8 Sep 2009 21:48:21 -0700
X-Trace: forums-1-dub 1252471701 10.22.241.152 (8 Sep 2009 21:48:21 -0700)
X-Original-Trace: 8 Sep 2009 21:48:21 -0700, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28282
Article PK: 77526


On Tue, 08 Sep 2009 12:59:57 -0700, George Brink wrote:

> Is it possible to specify the timeout for select statement on the server
> level? I know I can set up the query timeout on the client side, but
> right now we are looking for a way to kill long-running select queries
> from an existing applications (we can not rewrite them).
> Server is ASE 15.0.3

You could use the resource governor for this. You can use this to define a
maximum query elapsed time, or row count, or IO cost, or tempdb space
usage.

You need to enable the resource governor via sp_configure 'allow resource
limits' (a static setting - you'll have to restart ASE), and then use
sp_add_resource_limit to add the type of limits that you wish to set (on a
per login or per program_name basis).

Michael


Jason L. Froebe Posted on 2009-09-09 13:24:32.0Z
From: "Jason L. Froebe" <jason.froebe@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: query timeout?
References: <4aa6b7bd$1@forums-1-dub> <pan.2009.09.09.04.48.20.205968@peppler.org>
In-Reply-To: <pan.2009.09.09.04.48.20.205968@peppler.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa7ac90$1@forums-1-dub>
Date: 9 Sep 2009 06:24:32 -0700
X-Trace: forums-1-dub 1252502672 10.22.241.152 (9 Sep 2009 06:24:32 -0700)
X-Original-Trace: 9 Sep 2009 06:24:32 -0700, vip152.sybase.com
Lines: 25
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28283
Article PK: 77527


Michael Peppler [Team Sybase] wrote:
> On Tue, 08 Sep 2009 12:59:57 -0700, George Brink wrote:
>
>> Is it possible to specify the timeout for select statement on the server
>> level? I know I can set up the query timeout on the client side, but
>> right now we are looking for a way to kill long-running select queries
>> from an existing applications (we can not rewrite them).
>> Server is ASE 15.0.3
>
> You could use the resource governor for this. You can use this to define a
> maximum query elapsed time, or row count, or IO cost, or tempdb space
> usage.
>
> You need to enable the resource governor via sp_configure 'allow resource
> limits' (a static setting - you'll have to restart ASE), and then use
> sp_add_resource_limit to add the type of limits that you wish to set (on a
> per login or per program_name basis).
>
> Michael
>
>

I forgot about the resource governor... good catch! :)

jason


George Brink Posted on 2009-09-09 14:27:37.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: query timeout?
References: <4aa6b7bd$1@forums-1-dub> <pan.2009.09.09.04.48.20.205968@peppler.org>
In-Reply-To: <pan.2009.09.09.04.48.20.205968@peppler.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa7bb59$1@forums-1-dub>
Date: 9 Sep 2009 07:27:37 -0700
X-Trace: forums-1-dub 1252506457 10.22.241.152 (9 Sep 2009 07:27:37 -0700)
X-Original-Trace: 9 Sep 2009 07:27:37 -0700, vip152.sybase.com
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28284
Article PK: 77528

Thank you.
That is really helpful.

Michael Peppler [Team Sybase] wrote:
> On Tue, 08 Sep 2009 12:59:57 -0700, George Brink wrote:
>
>> Is it possible to specify the timeout for select statement on the server
>> level? I know I can set up the query timeout on the client side, but
>> right now we are looking for a way to kill long-running select queries
>> from an existing applications (we can not rewrite them).
>> Server is ASE 15.0.3
>
> You could use the resource governor for this. You can use this to define a
> maximum query elapsed time, or row count, or IO cost, or tempdb space
> usage.
>
> You need to enable the resource governor via sp_configure 'allow resource
> limits' (a static setting - you'll have to restart ASE), and then use
> sp_add_resource_limit to add the type of limits that you wish to set (on a
> per login or per program_name basis).
>
> Michael
>
>