The window into the entire system, an area of shared memory with an SQL like interface
dbaccess sysmaster - delete from systables where tabid > 99 and partnum < '0x100000'; delete from systables where tabname = 'sysdbspartn'; close database; drop database sysmaster;
select sid, username,
dbinfo('UTC_TO_DATETIME',connected) conection_time,
current - dbinfo('UTC_TO_DATETIME',connected)
connected_since
from syssessions
order by 2,3
SELECT s.sid, s.username, q.odb_dbname database,
dbinfo('UTC_TO_DATETIME',s.connected) conection_time,
dbinfo('UTC_TO_DATETIME',t.run_time) last_run_time,
current - dbinfo('UTC_TO_DATETIME',t.run_time) idle_time
FROM syssessions s, systcblst t, sysrstcb r, sysopendb q
WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.odb_sessionid
ORDER BY 6 DESC
select tx_id
from systrans
where tx_addr = select us_txp
from sysuserthreads
where us_sid = SESSIONID)
Historically, this table could not tell you if you are in a real transaction ie a BEGIN WORK unless that transaction had done real work as the flags where not changed when the transaction is started.
select systabnames.tabname,
sysptntab.pf_isread,
sysptntab.pf_iswrite,
sysptntab.pf_isrwrite,
sysptntab.pf_isdelete,
sysptntab.pf_seqscans
from systabnames, sysptntab
where systabnames.partnum = sysptntab.partnum
select dbinfo('UTC_TO_DATETIME', sh_curtime) - dbinfo('UTC_TO_DATETIME', sh_boottime)
from sysshmvals
select sqs_statement
from syssqlstat
where sqs_statement;
or
select sqx_sessionid,
sqx_conbno,
sqx_sqlstatement
from syssqexplain,
sysscblst
where sqx_sessionid = sid
order by 1,2
select number,
name
from syslogfil,
syschunks,
sysdbspaces
where syslogfil.chunk = syschunks.chknum
and syschunks.dbsnum = sysdbspaces.dbsnum
select owner
from sysmaster:systables
where tabid = 99
Works fine on the earlier engines, not so good on the later ones as the Official Release doesn't tally back i.e. 10.00.FC6 reports 9.50C1, and 11.50.FC6 reports 9.53C1 etc. However, DBINFO('version','server-type')
DBINFO('version','major')
DBINFO('version','minor')
DBINFO('version','os')
DBINFO('version','full')
All should work
select unique username
from syssessions
select syssesprof.sid,
isreads,
iswrites,
isrewrites,
isdeletes
from syssesprof, syssessions
where syssesprof.sid = syssessions.sid
select sid
fromsyssessions
where pid = 'pid'
select dbsname, tabname, seqscans
from sysptprof;
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
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
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;
select number,
uniqid,
size,
used,
(used/size*100)
from syslogs
where uniqid >=
(select min(tx_loguniq)
from systrans
where tx_loguniq > 0)
union
select number,
uniqid,
size,
0,
0.00
from syslogs
where uniqid <
(select min(tx_loguniq)
from systrans
where tx_loguniq > 0)
select sh_pagesize
from sysshmvals
The nrows column on systables is only maintained as a result of update statistics but the current number of rows is always maintained accurately within sysmaster.
select ti_nrows
from systabnames,
systabinfo
where systabn.tabname = TABLENAME
and dbsname = DATABASE
and systabinfo.ti_partnum = systabnmes.partnum
select username, uid
from sysmaster:syssessions
where sid = 'session id'
select name
from sysdatabases
select name
from sysdbspaces
where dbsnum in
(select trunc(partnum / 1048576)
from sysdatabases
where name = "DATABASE")
In the later engines
select sysdatabases.name,
sysdatabases.owner,
sysdbspaces.name
from sysdbspaces,sysdatabases
where partdbsnum(sysdatabases.partnum) =
sysdbspaces.dbsnum
or
select DBINFO("DBSPACE",partnum) dbspace,
name,
owner
from sysdatabases
select prtpage/PAGESIZE
from sysdbstab</br>
where bitval(flags,'0x10') = 1
select tabname,
trunc(systabnames.partnum/1048576) dbspace,
sysdbspaces.name
from systabnames,
sysdbspaces
where sysdbspaces.dbsnum = trunc(systabnames.partnum/1048576)
select name,
count(*) chunks,
sum(chksize),
sum(nfree)
from syschunks,
sysdbspaces
where syschunks.dbsnum = sysdbspaces.dbsnum
group by 1
The following sql can identify sql using poor indexes
select sqx_sessionid,
sqx_executions,
round(sqx_bufreads/sqx_executions),
round(sqx_pagereads/sqx_executions),
sqx_estcost,
sqx_estrows,
sqx_index,
sqx_sqlstatement
from syssqexplain
where sqx_executions > 3000
and (sqx_bufreads / sqx_executions > 50
or sqx_executions > 100)
and (sqx_bufreads / sqx_executions > 1000
or sqx_executions > 1)
and sqx_bufreads / sqx_executions > 50000
The following SQL identifies the unused indexes
SELECT x1.dbsname, x3.tabname, x1.tabname AS idxname, (x4.npused * x4.pagesize / 1024) :: INT AS size_kb FROM systabnames AS x1, sysptntab AS x2, systabnames AS x3, sysptnhdr AS x4 WHERE x2.partnum = x1.partnum AND x2.partnum != x2.tablock AND x3.partnum = x2.tablock AND x4.partnum = x1.partnum AND x1.dbsname MATCHES '[a-z]*' AND x3.tabname MATCHES '[a-z]*' AND x2.pf_isread = 0 AND x2.pf_iswrite > 0 ORDER BY 4 DESC
In the later engines, or databases with detached indices this information is also available via Table IO stats but the indexname as the table filter
select dbname,
tabname,
sum(pagreads),
sum(pagwrites)
from sysptprof
group by dbsname, tabname
select dbsname,tabname
from sysptprof, sysdbspaces
where dbsnum = trunc(partnum/1048576)
and name = TEMPDBS
order by 1
SELECT q1.*, (100 * size_kb / dbs_size) :: DECIMAL(5,2) AS percent
FROM
(
SELECT
t2.owner [1,8],
t2.dbsname [1,18] AS database,
t2.tabname [1,22] AS table,
t3.name [1,10] AS dbspace,
(CURRENT - DBINFO('utc_to_datetime', ti_created)) :: INTERVAL DAY(4) TO SECOND AS life_time,
(ti_nptotal * ti_pagesize/1024) :: INT AS size_kb
FROM
systabinfo AS t1,
systabnames AS t2,
sysdbspaces AS t3
WHERE t2.partnum = ti_partnum
AND t3.dbsnum = TRUNC(t2.partnum/1024/1024)
AND TRUNC(MOD(ti_flags,256)/16) > 0
) AS q1,
(
SELECT name AS dbspace,
SUM(chksize * d1.pagesize/1024) AS dbs_size
FROM
syschunks AS d1,
sysdbspaces AS d2
WHERE d1.dbsnum = d2.dbsnum
GROUP BY 1
) AS q2
WHERE q1.dbspace = q2.dbspace
ORDER BY 6 DESC, 5 DESC;
This check does not need any locks unlike oncheck, therefore on a live system might not be accurate.
select dbsname,
tabname,
ti_nextns,
ti_nptotal,
ti_npused,
ti_npdata,
ti_nrows,
ti_rowsize
from systabnames, systabinfo
where partnum = ti_partnum
select tabname,
count(*)
from sysextents
where dbsname = 'DATABASE'
group by 1
Or summarised by dbspaces
select substr(DBINFO("DBSPACE",partnum),1,10) DBSpace,
dbsname[1,10] Database,
tabname[1,15] Table,
sum(pe_size) tot_space,
count(*) no_of_exts
from sysptnext, systabnames
where pe_partnum = partnum
and tabname != "TBLSpace"
group by 1,2,3
order by 1,4 desc
select *
from sysconfig
For example, to extract the current server name
select *
from sysconfig
where cf_name = "DBSERVERNAME"
select *
from sysprofile
Alternatively, this information can be found using
<a href="/onstat/onstat_p.html">onstat -p</a>
select *
from syslogs
As this is just a view onto syslogfil but with the flags interpreted the same data can be seen just by selecting from the syslogfil table, therefore avoiding the bitval operations. So the syslogfil select would
select number ,uniqid ,size ,used ,
bitval(syslogfil.flags ,'0x1' ) is_used,
bitval(syslogfil.flags ,'0x2' ) is_current,
bitval(syslogfil.flags ,'0x4' ) is_backed_up,
bitval(syslogfil.flags ,'0x8' ) is_new,
bitval(syslogfil.flags ,'0x10' ) is_archived,
bitval(syslogfil.flags ,'0x20' ) is_temp,
syslogfil.flags,name
from syslogfil,syschunks,sysdbstab where (syslogfil.number > 0)
and trunc((hex(physloc)/1048576))=chknum
and syschunks.dbsnum = sysdbstab.dbsnum
select vpid,
txt,
usecs_user,
usecs_sys,
num_ready
from sysvplst,
flags_text
where sysvplst.flags != 6
and sysvplst.class = flags_text.flags
and flags_text.tabname = "sysvplst"
select name,
sum(chksize) pages,
sum(nfree) free
from sysdbspaces, syschunks
where sysdbspaces.dbsnum = syschunks.dbsnum
group by 1
select name,
is_mirrored,
is_blobspace,
is_temp,
chknum,
fname,
offset,
is_offline,
is_recovering,
is_blobchunk,
is_inconsistent,
chksize,
nfree,
mfname,
moffset,
mis_offline,
mis_recovering
from sysdbspaces, syschunks
where sysdbspaces.dbsnum = syschunks.dbsnum
order by 1,5
select name,
syschfree.chknum,
syschfree.extnum,
syschfree.start,
syschfree.leng
from sysdbspaces, syschunks, syschfree
where sysdbspaces.dbsnum = syschunks.dbsnum
and syschunks.chknum = syschfree.chknum
order by 1,2
Alternatively, this information is contained in onstat d
select name,
chknum,
"Primary",
reads,
writes,
pagesread,
pageswritten
from syschktab, sysdbstab
where syschktab.dbsnum = sysdbstab.dbsnum
union all
select name,
chknum,
"Mirror",
reads,
writes,
pagesread,
pageswritten
from sysmchktab, sysdbstab
where sysmchktab.dbsnum = sysdbstab.dbsnum
order by 1,2
Alternatively, this information can be seen from
<a href="/onstat/onstatu_d.html">onstat -D</a>
select name,
chktype,
"Primary" chktype,
reads,
writes,
pagesread,
pageswritten
from syschktab, sysdbstab
where syschktab.dbsnum = sysdbstab.dbsnum
union all
select name,
chktype,
"Mirror" chktype,
reads,
writes,
pagesread,
pageswritten
from sysmchktab, sysdbstab
where sysmchktab.dbsnum = sysdbstab.dbsnum
into temp t_io with no log;
select sum(reads) t_reads,
sum(writes) t_writes,
sum(pagesread) t_pagesread,
sum(pageswritten) t_pageswritten
from t_io
into temp t_sum with no log;
select name,
chknum,
chktype,
reads,
writes,
pagesread,
pageswritten,
round((reads/t_reads)*100,2) p_reads,
round((writes/t_writes)*100,2) p_writes,
round((pagesread/t_pagesread)*100,2) p_pagesread,
round((pageswritten/t_pageswritten)*100,2) p_pageswritten
from t_io, t_sum
select DBINFO("DBSPACE",partnum),
dbsname,
tabname,
pe_phys,
pe_size
from sysptnext, systabnames
where pe_partnum = partnum
order by 1
select dbsname,
tabname,
isreads,
bufreads,
pagreads,
iswrites,
bufwrites,
pagwrites,
lockreqs,
lockwts,
deadlks
from sysptprof
order by 3
If the columns read 0 then you may need to set TBLSPACE_STATS in the your ONCONFIG file
select dbsname,
tabname,
count(*) num_of_extents,
sum( pe_size ) total_size
from systabnames, sysptnext
where partnum = pe_partnum
and tabname not matches "sys*"
group by 1, 2
order by 3 desc, 4 desc;
The following SQL will identify the most used tables
select fname, name, dbsname, tabname,
pagreads+pagwrites pagtots
from sysptprof, syschunks, sysdbspaces, sysptnhdr
where trunc(hex(sysptprof.partnum)/1048576) = chknum
and syschunks.dbsnum = sysdbspaces.dbsnum
and sysptprof.partnum = sysptnhdr.partnum
and (pagreads+pagwrites) != 0
order by 5 desc
For Online versions 4 and 5 is not as straightforward. tbstat -t will show all the active tables. This will give the tblnum in hex which can then be used in the following sql
select tabname
from systables
where hex(partnum) = tblnum
select sum(ti_nptotal),
sum(ti_npused)
from systabnames, systabinfo
where partnum = ti_partnum
order by 1
select systables.tabname,
syscolumns.colname,
sysdistrib.constructed,mode
from sysdistrib,systables,syscolumns
where systables.tabid > 99
and systables.tabid = syscolumns.tabid
and sysdistrib.tabid=systables.tabid
and sysdistrib.colno = syscolumns.colno
group by 1,2,3,4
order by tabname,colname;
This can be determined via the following SQL:
SELECT sh_pfclrtime
FROM sysshmvals;
select distinct tabname, b.constructed, b.mode
from systables a, sysdistrib b
where a.tabid=b.tabid
order by 1
DBINFO('dbspace', partn)
DBINFO('sqlca.sqlerrd1')
DBINFO('sqlca.sqlerrd2')
DBINFO('utc_to_datetime', <column>)
DBINFO('utc_current')
DBINFO('get_tz')
DBINFO('serial8')
DBINFO('sessionid')
DBINFO('dbhostname')
DBINFO('version','server-type')
DBINFO('version','major')
DBINFO('version','minor')
DBINFO('version','os')
DBINFO('version','full')
Which are available very much depends on the IDS version
select pl_physize, pl_phyused, round ((pl_phyused * 100.0)/pl_physize,2)
from sysplog
select sqc_currdb
from syssqlcurall
where sqc_sessionid = dbinfo('sessionid');
Find detached indexes
select
sysmaster:sysdbspaces.name dbs_name,
sysmaster:systabnames.tabname,
app_db:sysindexes.idxname
from sysmaster:systabnames, sysmaster:sysdbspaces,
app_db:sysindexes
where sysmaster:sysdbspaces.dbsnum =
trunc(sysmaster:systabnames.partnum/1048576)
and sysmaster:sysdbspaces.name != "rootdbs"
and sysmaster:sysdbspaces.name != "database_dbs"
and app_db:sysindexes.idxname = sysmaster:systabnames.tabname
union
-- Find other indexes
select
sysmaster:sysdbspaces.name dbs_name,
sysmaster:systabnames.tabname,
app_db:sysindexes.idxname
from sysmaster:systabnames, sysmaster:sysdbspaces,
app_db:systables, app_db:sysindexes
where sysmaster:sysdbspaces.dbsnum =
trunc(sysmaster:systabnames.partnum/1048576)
and sysmaster:sysdbspaces.name != "rootdbs"
and sysmaster:sysdbspaces.name != "database_dbs"
and app_db:systables.tabname = sysmaster:systabnames.tabname
and app_db:sysindexes.tabid = app_db:systables.tabid
-- Exclude detached indexes
and app_db:sysindexes.idxname not in
(select tabname from sysmaster:systabnames )
order by 1, 2, 3
SELECT UNIQUE e.dbsname, e.tabname
FROM sysdbspaces d, syschunks c, sysextents e
WHERE d.name = 'rootdbs'
AND c.dbsnum = d.dbsnum
AND e.chunk = c.chknum
AND e.dbsname NOT IN ('rootdbs', 'sysmaster', 'sysadmin', 'sysuser', 'sysutils')
ORDER BY 1, 2
select sh_mode from sysmaster:sysshmvals;
IDS 7,9,10,11
0 Initialisation
1 Quiescent 2 Recovery 3 Backup 4 Shutdown 5 Online 6 Abort
IDS 8
0 Initialisation 1 Quiescent 2 Micro kernel 3 Recovery 4 Backup 5 Shutdown 6 Online 7 Abort
select ha_type from sysmaster:sysha_type
0 Not part of a high-availability environment 1 Primary server 2 HDR secondary server 3 SD secondary server 4 RS secondary server
select distinct dbinfo("dbspace",b.partnum)
from <database>:sysfragments a, sysmaster:systabnames b
where a.partn = b.partnum
and tabid > 99
union
select distinct dbinfo("dbspace",b.partnum)
from <database>:systables a, sysmaster:systabnames b
where a.partnum = b.partnum
and a.partnum != 0
and tabid > 99
union
select b.name dbs_name
from sysmaster:systabnames a, sysmaster:sysdbspaces b, <database>:sysindexes c
where b.dbsnum = trunc(a.partnum/1048576)
and c.idxname = a.tabname
and a.dbsname = '<database>'
and tabid > 99
order by 1
select sysindexes.idxname index, sysindexes.idxtype as type, sysptprof.isreads reads
from sysindexes, sysmaster:sysptprof sysptprof
where sysindexes.idxname = sysptprof.tabname
and sysptprof.isreads = 0
order by reads desc, idxname ;