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.

how to determine if stored procedure is currently being executed

7 posts in Unix Last posting was on 2001-05-03 09:32:52.0Z
Yuriy Posted on 2001-04-27 16:53:31.0Z
From: Dock@dialupnetcom.no.spam (Yuriy)
Subject: how to determine if stored procedure is currently being executed
Date: Fri, 27 Apr 2001 16:53:31 GMT
Message-ID: <3aeba39f.9558314@forums.sybase.com>
X-Newsreader: Forte Agent 1.5/32.452
X-No-Archive: yes
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.unix
Lines: 5
NNTP-Posting-Host: user24.216.19.141.dsli.com 216.19.141.24
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.unix:542
Article PK: 1092630

Hi,

Is there any way to determine if stored procedure sp_xyz is currently
being executed by selecting some column from some sys table?

Thanks,


Yuriy


Yuriy Posted on 2001-04-27 19:25:37.0Z
From: Dock@dialupnetcom.no.spam (Yuriy)
Subject: Re: how to determine if stored procedure is currently being executed - another question
Date: Fri, 27 Apr 2001 19:25:37 GMT
Message-ID: <3aebc376.17709895@forums.sybase.com>
References: <3aeba39f.9558314@forums.sybase.com>
X-Newsreader: Forte Agent 1.5/32.452
X-No-Archive: yes
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.unix
Lines: 39
NNTP-Posting-Host: user24.216.19.141.dsli.com 216.19.141.24
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.unix:537
Article PK: 1092627

The actual reason for my question was because we are having some
problems with our users - they sometimes kill PB application (task
manager -> end task), but we want to make it idiot proof.

We have a stored procedure that regens reports. Within that stored
proc we simply call about 20 procedures that do the real regeneration.
The process takes 10 to 15 minutes to complete. First statement in the
main proc is update check_table set regen_in_progress = 'Y', last
statement update check_table set regen_in_progress = 'N',
regen_required_flag = 'N', so another user won't start the regen
process, while someone is already doing it. The problem is - when user
kills app - process on Sybase SQL Server dies as well and the
regen_in_progress field is never reset to 'N', so they constantly call
us and ask to reset. We have about 40 users and already tracked down 3
users who were doing it, talked to them and situation is more or less
all right now. Begin tran/commit tran cannot be used because we can
simply fill up the transaction log - that DB is used for other apps as
well. If the client app is killed, we would still have the
regen_required_flag set to 'Y' so, anyone who wants to see the reports
would still have to regen them. By having the ability to find out if
any of regen sps are running, we can elliminate the need for
regen_in_progress flag, so that will solve our problem, but it is not
a clean way of doing it. I am wondering if there's a way to ensure
stor proc completes?


Thanks,


Yuriy


Johan Posted on 2001-05-03 09:32:52.0Z
From: Johan
Date: Thu, 3 May 2001 05:32:52 -0400
Newsgroups: sybase.public.sqlserver.unix
Subject: Re: how to determine if stored procedure is currently being executed
Message-ID: <DBE16B641CAA04220034729B85256A41.00701CF885256A3B@webforums>
References: <3aeba39f.9558314@forums.sybase.com> <3aebc376.17709895@forums.sybase.com>
Lines: 46
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.unix:508
Article PK: 1092599

I would alter the check_table to include the spid of the user who last
modified it.
Any new user wanting to execute the proc will then have to check if it is
possibly in use (regen_in_progress = 'Y'), and if that spid is still
running (by selecting the spid's details into local variables from
master..sysprocesses), and confirming that the spid is running with the
correct proc_id (if Killed another user may be using the spid).
This query will be able to confirm beyond doubt whether anyone is REALLY
still running.

Beware that when you update the table "check_table", to add a where clause
specifying the local variables storing the spid, and statusses during your
initial select from the table "check_table", and to check that something
was actually updates by using @@rowcount.

This will ensure that only one task can start at a time, and will prevent a
scenario where more than one process starts off at the same time, after a
thread was killed.

enjoy

johan

>We have a stored procedure that regens reports. Within that stored
>proc we simply call about 20 procedures that do the real regeneration.
>The process takes 10 to 15 minutes to complete. First statement in the
>main proc is update check_table set regen_in_progress = 'Y', last
>statement update check_table set regen_in_progress = 'N',
>regen_required_flag = 'N', so another user won't start the regen
>process, while someone is already doing it. The problem is - when user
>kills app - process on Sybase SQL Server dies as well and the
>regen_in_progress field is never reset to 'N', so they constantly call
>us and ask to reset. We have about 40 users and already tracked down 3
>users who were doing it, talked to them and situation is more or less
>all right now. Begin tran/commit tran cannot be used because we can
>simply fill up the transaction log - that DB is used for other apps as
>well. If the client app is killed, we would still have the
>regen_required_flag set to 'Y' so, anyone who wants to see the reports
>would still have to regen them. By having the ability to find out if
>any of regen sps are running, we can elliminate the need for
>regen_in_progress flag, so that will solve our problem, but it is not
>a clean way of doing it. I am wondering if there's a way to ensure
>stor proc completes?


Manish Patel Posted on 2001-04-27 17:43:39.0Z
From: "Manish Patel" <manishbpatel@hotmail.com>
References: <3aeba39f.9558314@forums.sybase.com>
Subject: Re: how to determine if stored procedure is currently being executed
Date: Fri, 27 Apr 2001 13:43:39 -0400
Lines: 27
X-Newsreader: Microsoft Outlook Express 4.72.3612.1700
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3612.1700
Message-ID: <6PCFfR0zAHA.178@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.unix
NNTP-Posting-Host: gfd.com 208.249.143.82
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.unix:541
Article PK: 1092632

Try this query :

SELECT sp.spid,
user_name = isnull(sl.name,"?"),
dbname = db_name(sp.dbid),
sp.hostname,
sp.program_name,
sp.cmd,
object = object_name(sp.id, sp.dbid),
sp.linenum,
sp.tran_name,
sp.status,
sp.cpu,
sp.physical_io,
sp.blocked,
sp.time_blocked
from master..sysprocesses sp, master..syslogins sl
WHERE sp.suid = sl.suid and sp.dbid > 1
AND sp.status != 'recv sleep' and object_name(sp.id, sp.dbid) = 'sp_xyz'
order by sp.spid


Hope it helps

Manish


Yuriy Posted on 2001-04-27 19:02:40.0Z
From: Dock@dialupnetcom.no.spam (Yuriy)
Subject: Re: how to determine if stored procedure is currently being executed
Date: Fri, 27 Apr 2001 19:02:40 GMT
Message-ID: <3ae9c201.17336979@forums.sybase.com>
References: <3aeba39f.9558314@forums.sybase.com> <6PCFfR0zAHA.178@forums.sybase.com>
X-Newsreader: Forte Agent 1.5/32.452
X-No-Archive: yes
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.unix
Lines: 20
NNTP-Posting-Host: user24.216.19.141.dsli.com 216.19.141.24
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.unix:539
Article PK: 1092629

Thanks a lot for your help, it surely helped me a lot.

Yuriy

On Fri, 27 Apr 2001 13:43:39 -0400, "Manish Patel"

<manishbpatel@hotmail.com> wrote:

>Try this query :
>
>SELECT sp.spid,
> user_name = isnull(sl.name,"?"),
> dbname = db_name(sp.dbid),
> sp.hostname,
> sp.program_name,
> sp.cmd,
> object = object_name(sp.id, sp.dbid),
> sp.linenum,
> sp.tran_name,
> sp.status,
> sp.cpu,
> sp.physical_io,
> sp.blocked,
> sp.time_blocked
>from master..sysprocesses sp, master..syslogins sl
>WHERE sp.suid = sl.suid and sp.dbid > 1
> AND sp.status != 'recv sleep' and object_name(sp.id, sp.dbid) = 'sp_xyz'
>order by sp.spid
>
>
>Hope it helps
>
>Manish
>


Carl Kayser (BLS) Posted on 2001-04-27 18:01:51.0Z
From: "Carl Kayser (BLS)" <Kayser_C@BLS.gov>
References: <3aeba39f.9558314@forums.sybase.com> <6PCFfR0zAHA.178@forums.sybase.com>
Subject: Re: how to determine if stored procedure is currently being executed
Date: Fri, 27 Apr 2001 14:01:51 -0400
Lines: 45
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <rBShyb0zAHA.178@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.unix
NNTP-Posting-Host: 146.142.34.96
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.unix:540
Article PK: 1092631

This answer is as good as you can get. However it will have a problem if
Manish is executing AA.dbo.sp_xyz from database BB. The query will look for
object_name (NNN, db_id (BB)) instead of object_name (NNN, db_id (AA)). So
you may get an incorrect answer because Sybase did not add an extra database
id column (for the executeable) in sysprocesses per ISUG request "e01 058".

Although the above situation would appear to be rare it happens far too
often for my taste. I coalesce the object ID accross several databases; in
particular sybsystemprocs, dbcc, and master, since I look for all SPs being
executed. Your situation should be simpler (but not necessarily accurate)
if you are only interested in a single database.

Manish Patel <manishbpatel@hotmail.com> wrote in message
news:6PCFfR0zAHA.178@forums.sybase.com...
> Try this query :
>
> SELECT sp.spid,
> user_name = isnull(sl.name,"?"),
> dbname = db_name(sp.dbid),
> sp.hostname,
> sp.program_name,
> sp.cmd,
> object = object_name(sp.id, sp.dbid),
> sp.linenum,
> sp.tran_name,
> sp.status,
> sp.cpu,
> sp.physical_io,
> sp.blocked,
> sp.time_blocked
> from master..sysprocesses sp, master..syslogins sl
> WHERE sp.suid = sl.suid and sp.dbid > 1
> AND sp.status != 'recv sleep' and object_name(sp.id, sp.dbid) = 'sp_xyz'
> order by sp.spid
>
>
> Hope it helps
>
> Manish
>
>


Yuriy Posted on 2001-04-27 19:04:47.0Z
From: Dock@dialupnetcom.no.spam (Yuriy)
Subject: Re: how to determine if stored procedure is currently being executed
Date: Fri, 27 Apr 2001 19:04:47 GMT
Message-ID: <3aeac260.17431585@forums.sybase.com>
References: <3aeba39f.9558314@forums.sybase.com> <6PCFfR0zAHA.178@forums.sybase.com> <rBShyb0zAHA.178@forums.sybase.com>
X-Newsreader: Forte Agent 1.5/32.452
X-No-Archive: yes
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.unix
Lines: 43
NNTP-Posting-Host: user24.216.19.141.dsli.com 216.19.141.24
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.unix:538
Article PK: 1092628

This proc would be executed only from within DB where it resides.

Thanks a lot,

Yuriy


On Fri, 27 Apr 2001 14:01:51 -0400, "Carl Kayser (BLS)"

<Kayser_C@BLS.gov> wrote:

>
>This answer is as good as you can get. However it will have a problem if
>Manish is executing AA.dbo.sp_xyz from database BB. The query will look for
>object_name (NNN, db_id (BB)) instead of object_name (NNN, db_id (AA)). So
>you may get an incorrect answer because Sybase did not add an extra database
>id column (for the executeable) in sysprocesses per ISUG request "e01 058".
>
>Although the above situation would appear to be rare it happens far too
>often for my taste. I coalesce the object ID accross several databases; in
>particular sybsystemprocs, dbcc, and master, since I look for all SPs being
>executed. Your situation should be simpler (but not necessarily accurate)
>if you are only interested in a single database.
>
>
>Manish Patel <manishbpatel@hotmail.com> wrote in message
>news:6PCFfR0zAHA.178@forums.sybase.com...
>> Try this query :
>>
>> SELECT sp.spid,
>> user_name = isnull(sl.name,"?"),
>> dbname = db_name(sp.dbid),
>> sp.hostname,
>> sp.program_name,
>> sp.cmd,
>> object = object_name(sp.id, sp.dbid),
>> sp.linenum,
>> sp.tran_name,
>> sp.status,
>> sp.cpu,
>> sp.physical_io,
>> sp.blocked,
>> sp.time_blocked
>> from master..sysprocesses sp, master..syslogins sl
>> WHERE sp.suid = sl.suid and sp.dbid > 1
>> AND sp.status != 'recv sleep' and object_name(sp.id, sp.dbid) = 'sp_xyz'
>> order by sp.spid
>>
>>
>> Hope it helps
>>
>> Manish
>>
>>
>