====== Sysmaster ======
===== What is Sysmaster =====
The window into the entire system, an area of shared memory with an SQL like interface
===== How to drop sysmaster =====
dbaccess sysmaster -
delete from systables where tabid > 99 and partnum < '0x100000';
delete from systables where tabname = 'sysdbspartn';
close database;
drop database sysmaster;
===== How long has the user been connected =====
select sid, username,
dbinfo('UTC_TO_DATETIME',connected) conection_time,
current - dbinfo('UTC_TO_DATETIME',connected)
connected_since
from syssessions
order by 2,3
===== How long has the user been idle =====
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
===== How to link a session to a transaction =====
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.
===== How to find the busy tables =====
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
===== How long has the database engine been running =====
select dbinfo('UTC_TO_DATETIME', sh_curtime) - dbinfo('UTC_TO_DATETIME', sh_boottime)
from sysshmvals
===== How to find the current SQL statement =====
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
===== Where are the logs =====
select number,
name
from syslogfil,
syschunks,
sysdbspaces
where syslogfil.chunk = syschunks.chknum
and syschunks.dbsnum = sysdbspaces.dbsnum
===== What is the current version of the engine =====
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
===== 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;
* Replace database_name with the database you are querying
* Must be run as user informix
===== How to display free log space =====
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)
===== What is the page size =====
select sh_pagesize
from sysshmvals
===== How many rows are there in the table =====
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
===== How to get the username =====
select username, uid
from sysmaster:syssessions
where sid = 'session id'
===== How to list all the databases =====
select name
from sysdatabases
===== What is the database to dbspace mapping =====
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
===== What is the blobspace page size =====
select prtpage/PAGESIZE
from sysdbstab
where bitval(flags,'0x10') = 1
===== What is the table to dbspace mapping =====
select tabname,
trunc(systabnames.partnum/1048576) dbspace,
sysdbspaces.name
from systabnames,
sysdbspaces
where sysdbspaces.dbsnum = trunc(systabnames.partnum/1048576)
===== What is the total size of the dbspaces =====
select name,
count(*) chunks,
sum(chksize),
sum(nfree)
from syschunks,
sysdbspaces
where syschunks.dbsnum = sysdbspaces.dbsnum
group by 1
===== What are the poor indexes =====
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
===== What is the disk IO for the table =====
select dbname,
tabname,
sum(pagreads),
sum(pagwrites)
from sysptprof
group by dbsname, tabname
===== List all the tables in the temporary dbspaces =====
select dbsname,tabname
from sysptprof, sysdbspaces
where dbsnum = trunc(partnum/1048576)
and name = TEMPDBS
order by 1
===== List all the tables in the temporary dbspaces by session =====
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;
===== How big is a table =====
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
===== List the table extent usage =====
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
===== What is the current engine configuration =====
select *
from sysconfig
For example, to extract the current server name
select *
from sysconfig
where cf_name = "DBSERVERNAME"
===== What is the current profile information =====
select *
from sysprofile
Alternatively, this information can be found using
onstat -p
===== What can I find out about the logs =====
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
===== How to summarise the VP statistics =====
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"
===== How much space is left in the DBSpace =====
select name,
sum(chksize) pages,
sum(nfree) free
from sysdbspaces, syschunks
where sysdbspaces.dbsnum = syschunks.dbsnum
group by 1
===== What is the current chunk status =====
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
===== How much space is left in a chunk =====
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 [[http://www.oninit.com/onstat/onstat_d.html|onstat d]]
===== What is the disk IO for the chunk =====
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
onstat -D
===== What is the percentage IO for the chunks =====
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
===== Where are the tables in the dbspace =====
select DBINFO("DBSPACE",partnum),
dbsname,
tabname,
pe_phys,
pe_size
from sysptnext, systabnames
where pe_partnum = partnum
order by 1
===== What is the IO for the table =====
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
===== How much space is occupied by a table. =====
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;
===== What are the busiest tables =====
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
===== What is the total size of the database =====
select sum(ti_nptotal),
sum(ti_npused)
from systabnames, systabinfo
where partnum = ti_partnum
order by 1
===== How were the last UPDATE STATISTICS run =====
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;
===== When was the last onstat -z run =====
This can be determined via the following SQL:
SELECT sh_pfclrtime
FROM sysshmvals;
===== When was Update Stats last run =====
select distinct tabname, b.constructed, b.mode
from systables a, sysdistrib b
where a.tabid=b.tabid
order by 1
===== What are all the DBINFO options =====
DBINFO('dbspace', partn)
DBINFO('sqlca.sqlerrd1')
DBINFO('sqlca.sqlerrd2')
DBINFO('utc_to_datetime', )
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
===== How full is the physical log =====
select pl_physize, pl_phyused, round ((pl_phyused * 100.0)/pl_physize,2)
from sysplog
===== Which database am I connected to =====
select sqc_currdb
from syssqlcurall
where sqc_sessionid = dbinfo('sessionid');
===== Where are the indexes =====
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
===== What tables are in rootdbs =====
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
===== What mode is the engine running in =====
select sh_mode
from sysmaster:sysshmvals;
IDS 7,9,10,11
-1 Offline (-1 = 255)
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
===== What type of instance is running =====
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
===== What dbspaces are being used by a database =====
select distinct dbinfo("dbspace",b.partnum)
from :sysfragments a, sysmaster:systabnames b
where a.partn = b.partnum
and tabid > 99
union
select distinct dbinfo("dbspace",b.partnum)
from :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, :sysindexes c
where b.dbsnum = trunc(a.partnum/1048576)
and c.idxname = a.tabname
and a.dbsname = ''
and tabid > 99
order by 1
===== What are the unused indexes =====
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 ;