sessions to database question
John Moore asked how to find and kill sessions connected to a specific database. Ashutosh Khunte gave a shell loop using onstat -g sql with onmode -z. Art Kagel pointed to sysopendb by odb_sessionid, joined to syssessions or sysscblst. Lester Knutsen posted a sysmaster query joining syslocks, sysdatabases and syssessions. Answered.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I have the need to run through the list of sessions and kill any that are connected to a specific database. I tried the IIUG site and couldn't find a script. It doesn't look like it comes from syssessions & sysdatabases. Can someone tell me what table & columns I need to join together to dump this information out? Thanks in advance, John Moore
The easiest way to do this is from command line:
for i in `onstat -g sql | grep <db-name> | awk '{print $1}'`^J do^J onmode -z
"$i"^J done^J`^J'
--- On Tue, 1/20/09, John Moore <johnmoore@pdsi-software.com> wrote:
From: John Moore <johnmoore@pdsi-software.com>
Subject: sessions to database question [14589]
To: ids@iiug.org
Date: Tuesday, January 20, 2009, 3:48 PM
I have the need to run through the list of sessions and kill any that
are connected to a specific database. I tried the IIUG site and
couldn't find a script. It doesn't look like it comes from syssessions
& sysdatabases. Can someone tell me what table & columns I need to join
together to dump this information out?
Thanks in advance,
John Moore
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
select * from sysopendb where odb_sessionid = <session id>;
You can map the session id to pid by joining to syssessions or the table
that view is built on: sysscblst which also has the application name and
other information.
Art
On Tue, Jan 20, 2009 at 6:48 PM, John Moore <johnmoore@pdsi-software.com>wrote:
> I have the need to run through the list of sessions and kill any that
> are connected to a specific database. I tried the IIUG site and
> couldn't find a script. It doesn't look like it comes from syssessions
> & sysdatabases. Can someone tell me what table & columns I need to join
> together to dump this information out?
>
> Thanks in advance,
> John Moore
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--005045017bc62d87600460f65a6f
John,
Many years ago I had the same problem and the first sysmaster script I wrote
was to do this - Regards - Lester
-----------------------------------------------------------------------------
-- Module: @(#)dbwho.sql 1.5 Date: 2002/05/01
-- Author: Lester B. Knutsen Email: lester@advancedatatools.com
-- Advanced DataTools Corporation
-- Discription: Displays who is using what database
-----------------------------------------------------------------------------
database sysmaster;
select
sysdatabases.name database,
syssessions.username,
syssessions.hostname,
syslocks.owner sid
from syslocks, sysdatabases , outer syssessions
where syslocks.rowidlk = sysdatabases.rowid
and syslocks.tabname = "sysdatabases"
and syslocks.owner = syssessions.sid
order by 1;
John Moore wrote:
> I have the need to run through the list of sessions and kill any that
> are connected to a specific database. I tried the IIUG site and
> couldn't find a script. It doesn't look like it comes from syssessions
> & sysdatabases. Can someone tell me what table & columns I need to join
> together to dump this information out?
>
> Thanks in advance,
> John Moore
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
--
______________________________________________________________________
Lester Knutsen lester@advancedatatools.com
Advanced DataTools Corporation Voice: 703-256-0267 x102
Visit our Web page: http://www.advancedatatools.com
______________________________________________________________________