有用的sql

检查表的顺序扫描

select first 50 a.tabname, b.nrows, sum(a.seqscans) tot_scans
from sysmaster:sysptprof a, systables b
where seqscans> 0
and dbsname= 'crmdb'
and a.tabname[1,3]<>'sys'
and a.tabname= b.tabname
group by 1,2 order by 2 desc;

 

查询所有被打开的数据库的工作站，用户以及sessionid
select sysdatabases.name database, --database
syssessions.username, --username
syssessions.hostname, --workstation
syslocks.owner sid    --informix session id
from syslocks,sysdatabases,outer syssessions
where syslocks.tabname = "sysdatabases" --find locks on sysdatabases
and syslocks.rowidlk = sysdatabases.rowid --join rowid to database
and syslocks.owner = syssessions.sid --session id to get user info
order by 1;

 

查看表的extent数量：
#!/bin/sh
dbaccess sysmaster - >/dev/null 2>&1 <set isolation to dirty read;
unload to chunkfile DELIMITER "|"
select t.tabname, count(*) num_ext,t.rowsize,t.nrows
from sysmaster:sysextents e, test:systables t
where e.tabname=t.tabname
and dbsname = "test"
and t.tabid > 99
and t.tabtype = 'T'
group by 1,3,4
order by 2 desc
sql
more chunkfile
rm chunkfile
将test数据库换成需要的数据库即可

 

检查每个dbspace的利用率：

select    name[1,8] dbspace,       -- name truncated to fit on one line
          sum(chksize) Pages_size, -- sum of all chuncks size pages
          sum(chksize) - sum(nfree) Pages_used,
          sum(nfree) Pages_free,   -- sum of all chunks free pages
          round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free
from      sysdbspaces d, syschunks c
where     d.dbsnum = c.dbsnum
group by 1
order by 1;