sp_who - selecting equivalent info from system tables?
Saul Margolis
reverted to sp_who to find out who has sessions on the server. The
annoying thing about sp_who is that I am not able to sort by username
of hostname or select for only a particular username.
I would imagine that all this data comes from a Sybase system table.
Does anybody know which table I should select from if I wish to write
a SELECT statement to do the equivalent of sp_who, except perhaps
sorting by a column?
We are using ASE12.5 and I am a DBO for my database, but not an sa on
the server.
Thanks in advance,
Saul
Anthony Mandic
>
> Lately I've been having a few problems with sessions, and have
> reverted to sp_who to find out who has sessions on the server. The
> annoying thing about sp_who is that I am not able to sort by username
> of hostname or select for only a particular username.
>
> I would imagine that all this data comes from a Sybase system table.
> Does anybody know which table I should select from if I wish to write
> a SELECT statement to do the equivalent of sp_who, except perhaps
> sorting by a column?
master..sysprocesses
-am © 2003
Luc Van der Veurst
master..sysprocesses. Go to the sybsystemprocs database and
execute sp_helptext sp_who. You will then see how sysprocesses
is used in sp_who.
Luc.
Saul Margolis
that I need to link to other system tables to get loginname, blk_spid,
dbname, cmd etc. Any idea which tables?
Regards,
Saul
Anthony Mandic <a...@hotmail.com> wrote in message news:<3EE0627C...@hotmail.com>...
Anthony Mandic
>
> Thanks Anthony. I selected from sysprocesses table, and it appears
> that I need to link to other system tables to get loginname, blk_spid,
> dbname, cmd etc. Any idea which tables?
Everything you need is in the master database. An sp_help might
be useful there. But basically you would want syslogins, sysdatabases
(although you could call db_name()) and perhaps syslocks (although
a blocking spid would really be a self-join to sysprocesses).
As Luc suggested, look at the source to sp_who too. There are a
few freeware alternatives that people have developed over the
years. Check the Sybase FAQ at http://www.isug.com/Sybase_FAQ/
for the links.
-am © 2003
Saul Margolis
Thanks, I got a freeware procedure off the isug site. The procedure
returns statistics on io and cpu - do you know how to interpret this?
Regards,
Saul
Anthony Mandic <b...@hotmail.com> wrote in message news:<3EE442F5...@hotmail.com>...
Larry B
some sp_who alternatives for ASE 12.5
select 'spid'= convert(char(4), spid), 'login' = convert(char(12),
suser_name(suid)), 'ENG' = convert(char(4), enginenum),
convert(char(20),loggedindatetime),ipaddr,
program_name, hostname,'BLK' = convert(char(4),blocked), cmd,
'database' = convert(char(8),db_name(dbid)), cpu,physical_io,
status, 'user' =convert(char(8), user_name(uid)), 'proc name ' =
convert(char(20),object_name(id,dbid))
from master..sysprocesses
where suid > 0
order by physical_io
select 'spid'= convert(char(4), spid), 'login' = convert(char(12),
suser_name(suid)),affinity, enginenum,
program_name, hostname,'blocking spid' = blocked, cmd,
'database' = db_name(dbid), cpu,physical_io, tran_name
status, 'proc name = ' = convert(char(20),object_name(id)),
'user' = user_name(uid), tran_name
from master..sysprocesses
where suid > 0
order by spid
FOR 11.9.X
> Luc.
Larry B
SP_WHO OPTIONAL sql 11.9.X
select 'spid'= convert(char(4), spid), 'login' = convert(char(12),
suser_name(suid)),
LB
Anthony Mandic
> Thanks, I got a freeware procedure off the isug site. The procedure
> returns statistics on io and cpu - do you know how to interpret this?
It would depend on where its getting them from. It would most
likely either be sysprocesses or syslogins. For both, you can
find the write up on their respective fields in the Commands
Reference manual supplement. This contains write ups on all
the system tables. Try the online manuals.
-am © 2003