How to list the users
select unique username
from syssessions
What is the disk IO for a session
select syssesprof.sid,
isreads,
iswrites,
isrewrites,
isdeletes
from syssesprof, syssessions
where syssesprof.sid = syssessions.sid
How to convert a PID to a session ID
select sid
fromsyssessions
where pid = 'pid'
How to find the sequential scans for a table
select dbsname, tabname, seqscans
from sysptprof;
How to list the logging status of a database
select name,
is_logging,
is_buff_log,
is_ansi,
is_nls
from sysdatabases
The flags are:
0: Not logged
1: Buffered Logging
2: Unbuffered Logging
4: Ansi
8: NLS
How to list the locks
select dbsname,
tabname,
rowidlk,
keynum,
type
from syslocks,
syssessions
where owner = "sid"
B Byte lock
IS Intent shared lock
S Shared lock
XS Repeatable read shared lock
U Update lock
IX Intent exclusive lock
SIX Shared intent exclusive
X Exclusive lock
XR Repeatble read exclusive
How to list the database locks
SELECT "database lock" table_name, l.type lock_type, l.keynum index_num,
HEX(l.rowidlk) rowid, s.sid session_id, s.username, s.pid, s.hostname,
q.sqs_statement statement
FROM syslocks l, sysdatabases d, syssessions s, syssqlstat q
WHERE d.name = ' database_name '
AND l.rowidlk = d.rowid
AND l.owner = s.sid
AND dbsname = 'sysmaster'
AND tabname = 'sysdatabases'
AND s.sid = q.sqs_sessionid
UNION ALL
SELECT l.tabname, l.type, l.keynum, HEX(l.rowidlk), s.sid, s.username,
s.pid, s.hostname, q.sqs_statement
FROM syslocks l, syssessions s, syssqlstat q
WHERE l.dbsname = ' database_name '
AND l.owner = s.sid
AND s.sid = q.sqs_sessionid
AND dbsname = 'sysmaster'
AND tabname = 'sysdatabases'
ORDER BY 5;
