Compare each table and index partition against the _maxnpused threshold

database la_que_estas_investigando;
SELECT tabname,partnum, npused,
FROM systables WHERE npused > $_maxnpused
AND partnum != 0

database la_que_estas_investigando;
SELECT f.indexname, h.npused
FROM systables t, sysfragments f, sysmaster:sysptnhdr h
WHERE t.tabid = f.tabid AND
f.partn = h.partnum AND f.npused > $_maxnpused ;

seqscans, br, bw, pr, pw y mucho mas por tabla y dbspace

select first +10
tabname,seqscans,bufreads,bufwrites,pagreads,pagwrites
from sysptprof
order by 3 desc

dbsname char(128) yes
tabname char(128) yes
partnum integer yes
lockreqs integer yes
lockwts integer yes
deadlks integer yes
lktouts integer yes
isreads integer yes
iswrites integer yes
isrewrites integer yes
isdeletes integer yes
bufreads integer yes
bufwrites integer yes
seqscans integer yes
pagreads integer yes
pagwrites integer yes

Uso numero extents por tabla (fragmento) y por dbspace

select dbsname DatabaseName,
DBINFO('DBSPACE', systabnames.partnum) ,
tabname TableName,
ti_pagesize Pagesize,
round((ti_nptotal * ti_pagesize ) / 1024/ 1024) || " Mb" Total_pages,
round((ti_nextsiz * ti_pagesize ) / 1024/ 1024) || " Mb" Next_extent,
round((ti_npused * ti_pagesize ) / 1024/ 1024) || " Mb" Pages_Used_Mb,
-- version >=10 (new field)
round((ti_npdata * ti_pagesize ) / 1024/ 1024) || " Mb" Pages_Data_Mb,
ti_nrows Nrows
from sysmaster:systabinfo, sysmaster:systabnames
where partnum = ti_partnum
and tabname not matches "TBL*" -- util para tablespace tablespace
and dbsname not in ('sysmaster', 'sysutils')
and tabname = 'tjrnl'
order by 1,2,4,3;

how each session is interacting with the database

select sid, (isreads+bufreads+bufwrites+pagreads+pagwrites) access, locksheld,  
seqscans, total_sorts, dsksorts 
from syssesprof 

 Sid    access       locksheld     seqscans   tot_sorts disksorts
233982      246              0          2            0           0
230421     7789             12        456         1000           0
225679     9981            213        669          876           2
247869    10098            440        578           98           2
   78       70             45          6            0           0
  447       46             89          6            0           0

Sequential Scans by database/table

select dbsname, tabname, sum(seqscans) tot_scans

from sysptprof

where seqscans > 0 
and dbsname not like "sys%" 
group by 1,2 
order by 3 desc 

The output is as follows:

dbsname              tabname                     tot_scans 

airgen_10_0          systemrelease                    2352
airgen_10_5_old      systemrelease                    1596
airgen_10_5          systemrelease                    1596
airgen_10_0          fanout_comp                      1587
airgen_10_5_old      sysusers                         1248
airgen_10_0          sysusers                         1241
airgen_10_5          sysusers                         1231
airgen_10_0          join_map                         1036
airgen_10_0          fanout_param                      958
airgen_10_0          func_call                         770
airgen_10_5          nenmoclassmap                     586
airgen_10_5_old      nenmoclassmap                     586

Dbspace I/O

select d.name, fname[15,25] path_name, sum(pagesread) diskreads, 
sum(pageswritten) diskwrites 
from syschkio c, syschunks k, sysdbspaces d 
where d.dbsnum = k.dbsnum 
and k.chknum = c.chknum 
group by 1, 2 
order by 1 

The output is as follows:

name              path_name          diskreads       diskwrites

airgen_idx_dbs   uild95/ltmp             3672             7964
airgen_main_dbs  uild95/ltmp            13545            32903
llog             uild95/ltmp               19            51633
rootdbs          uild95/ltmp              211            43117
temp1            uild95/ltmp             3015             3122
temp2            uild95/ltmp             3218             3317

We can get similar information using onstat -D, which displays disk reads and writes
for each chunk, and onstat -g ioq which displays disk I/O waiting queue information.

Dbspace usage

select name dbspace, sum(chksize)*2/1024/1024 allocated,
sum(nfree)*2/1024/1024 free,
100 - (round   (sum(chksize*2) - sum(nfree*2)) /sum(chksize*2) *100)    pcfree
from sysdbspaces d, syschunks c
where d.dbsnum = c.dbsnum
group by name
order by name

the following query is complementary to the prevoius one

select
DBINFO('DBSPACE', systabnames.partnum) ,
sum(round((ti_nptotal * ti_pagesize )) / 1024/ 1024) ,
sum(round((ti_npused * ti_pagesize )) / 1024/ 1024)
-- version >=10 (new field)
from sysmaster:systabinfo, sysmaster:systabnames
where partnum = ti_partnum
and tabname not matches "TBL*" -- util para tablespace tablespace
and DBINFO('DBSPACE', systabnames.partnum) in ('dbspace')
group by 1