bring ONLINE , chunks marked as down

select "onspaces -s "||trim(a.name)||" -p "|| trim(b.fname)|| " -o " || b.offset * BLOODY_PAGE_SIZE_IN_K || " -O -y "
from sysdbspaces a,syschunks b
where a.dbsnum=b.dbsnum and is_offline = 1 and is_temp=0
union
select "onspaces -s "||trim(a.name)||" -p "|| trim(b.mfname)|| " -o " || b.moffset * BLOODY_PAGE_SIZE_IN_K || " -O -y "
from sysdbspaces a,syschunks b
where a.dbsnum=b.dbsnum and mis_offline = 1 and is_temp=0

worst 15 queries

select first 15 sqx_estcost cost,
syssessions.username[1,10] user,
syssessions.tty[1,10],
sqx_iscurrent crnt,
sqx_sessionid sid,
trim(sqx_sqlstatement[1,180]) sql
from syssqexplain, syssessions
where sqx_iscurrent = 'Y'
and sqx_sessionid = syssessions.sid
and sqx_sessionid <> DBINFO('sessionid')
order by sqx_iscurrent desc, sqx_estcost desc ;

Query to show number of extents table has left rather than using oncheck

SELECT --+ORDERED,INDEX(a,systabs_pnix),INDEX(b,sysptnhdridx),INDEX(c,syspaghd)

a.dbsname AS database_name,

a.tabname,

d.name AS dbspace_name,

b.nextns AS extents_act,

TRUNC(c.pg_frcnt / 8) + b.nextns AS max_extents

FROM sysmaster:sysdbstab d,

sysmaster:syspaghdr c,

sysmaster:systabnames a,

sysmaster:sysptnhdr b

WHERE c.pg_partnum = sysmaster:partaddr(d.dbsnum, 1)

AND sysmaster:bitval(c.pg_flags, 2) = 1

AND c.pg_nslots = 5

AND a.partnum = sysmaster:partaddr(d.dbsnum, c.pg_pagenum)

AND a.partnum = b.partnum

How to trace a session holding a mutex lock

Example to trace a session holding a mutex lock and preventing another one to run:

Tracing this mutex lock through...

onstat -g lmx -r 1

Locked mutexes:

mid addr name holder lkcnt waiter waittime

181210 5d7cf3e30 rrlist 127856 1

onstat -g ath | grep 127856

127856 57e2381c0 5ca641b68 1 running 9cpu sqlexec

onstat -u | grep 5ca641b68

5ca641b68 ---P--- 113927 openbet - 0 0 1 0 0

onstat -g sql 113927

Sess SQL Current Iso Lock SQL ISAM F.E.

Id Stmt type Database Lvl Mode ERR ERR Vers Explain

113927 SELECT perfmon NL Wait 10 0 0 9.29 Off

Current SQL statement :

select distinct t.qry_id, t.text from tQryText t,

tQrySum s where t.qry_id = s.qry_id and s.app_id in

(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and s.date > ? order by 2;

onstat –g ath (perfom sql and update stats to trjnl)

110507 5df5e75a8 59ee052d0 1 cond wait netnorm 9cpu sqlexec

110508 6133c3028 596a5aef8 1 sleeping forever 9cpu sqlexec ß Update stats

127856 57e2381c0 5ca641b68 1 running 9cpu sqlexec ß SQL to perfmon

Database size

select dbsname DatabaseName,

trunc(sum((ti_nptotal * ti_pagesize ) / 1024/ 1024),2) || "Mb" sizeallocated,

trunc(sum((ti_npused * ti_pagesize ) / 1024/ 1024),2) || "Mb" usedsize

from sysmaster:systabinfo A, sysmaster:sysptprof B

where A.ti_partnum = B.partnum

and B.tabname not matches "TBL*" and B.dbsname not matches "*TEMP*"

and B.tabname not matches "sys*"

and B.dbsname not in ('sysmaster', 'sysutils')

group by 1

Table close to allocating next extent

##Table close to allocating next extent

TABLE_SPACE_FREE=10 ## as a percentage of free space

TABLE_SIZE=10 ## in mb

EXTENT_SIZE=10 ## im mb

select dbsname, DBINFO('DBSPACE', systabnames.partnum),

tabname,

( ti_nptotal * 2 ) / 1024 mb_allocated,

ti_nextns extents,

( ti_nextsiz * 2 ) / 1024 mb_nextextent,

( ti_npused * 2 ) / 1024 mb_used,

(ti_nptotal - ti_npused ) * 2 / 1024 mb_free,

((ti_nptotal - ti_npused ) * 2 / 1024 ) / (( ti_nptotal * 2 ) / 1024) * 100 pct_free

from sysmaster:systabinfo, sysmaster:systabnames

where partnum = ti_partnum

and tabname not matches 'TBL*'

and dbsname not in ('sysmaster', 'sysutils')

and ((ti_nptotal - ti_npused) * 2 / 1024 )/((ti_nptotal * 2 ) / 1024) * 100 < $TABLE_SPACE_FREE

and ( ti_nptotal * 2 ) / 1024 > $TABLE_SIZE

and ( ti_nextsiz * 2 ) / 1024 > $EXTENT_SIZE

order by 6 desc, 9 asc

Constraints de una tabla

select

c.constrname,

i.idxname,

trim(t1.tabname) || '.' || trim(col1.colname),

t2.tabname

from

systables t1,

sysconstraints c,

sysindexes i,

syscolumns col1,

sysreferences r,

systables t2

where

t1.tabid = c.tabid and

t1.tabid = i.tabid and

c.idxname = i.idxname and

t1.tabid = col1.tabid and

i.part1 = col1.colno and

c.constrid = r.constrid and

r.ptabid = t2.tabid and

t1.tabname = 'AQUI AQUI'

HDR y te cagas por la pta

sencillamente genial:

desde el primario:

ontape -s -L 0 -F -t STDIO | ssh secundario ". /informix/scripts/set_env.sh;ontape -p -t STDIO"

onmode -d sec primario

TABNAME from partnumber

#!/bin/ksh

# Pass in partnum to give table name

echo "Partnum is: $1"

a="0x00$1"

echo "select dbsname, DBINFO('DBSPACE', systabnames.partnum),

tabname,

( ti_nptotal * 2 ) / 1024 pages_in_Mb,

( ti_npused * 2 ) / 1024 pages_used_Mb,

HEX(partnum)

from sysmaster:systabinfo, sysmaster:systabnames

where partnum = ti_partnum

and hex(partnum) = '$a'" | dbaccess sysmaster

Sequential Scans (David)

DATABASE sysmaster;

SELECT

stn.dbsname[1,15],

stn.tabname[1,18],

sti.ti_nptotal *

(

select pagesize

from sysdbspaces

where name = dbinfo('dbspace', sti.ti_partnum)

)/1024/1024 mb_total,

sti.ti_nrows,

spp.seqscans

FROM systabnames stn, systabinfo sti, sysptprof spp

WHERE stn.partnum = sti.ti_partnum

AND stn.partnum = spp.partnum

AND spp.seqscans > 0

ORDER BY mb_total DESC, spp.seqscans DESC;

Users locking tables

database sysmaster;
select username name, pid, dbsname[1,10] database,
tabname[1,12] table, hostname, type
from syslocks, syssessions
where
syslocks.owner = syssessions.sid
order by 3,2

Extents: Tables witha big number of extents

select dbsname, tabname table_or_index, count(*) num_of_extents,
trunc(sum( pe_size * $PAGE_SIZE ) /1024/1024,0) total_size_MB
from systabnames, sysptnext
where systabnames.partnum = pe_partnum
and dbsname != 'HASHTEMP'
and systabnames.tabname not matches 'TBL*'
group by 1,2
having count(*) > $NUM_EXTENTS
order by 1,3 desc,2
-- PAGE_SIZE in bytes, please

Checkpoints and so on

onmode -B does not flush the pages from the buffer pool. It simply
writes dirty pages to disk. The page still remains in the buffer pool.
onmode -B is probably a much better solution to the long checkpoint
issue than using very low LRU values because it uses chunk writes
rather than LRU writes.
The engine uses two basic approches to writing dirty pages to disk.
The chunk writes that are used during the checkpoint (and onmode -B)
are sorted and organized arround the physical chunk. Because of this,
they are are rather efficient -- sort of like using scatter-gather IO.
The LRU writes are not organized and thus not nearly as efficient.

LA BESTIA PARDA DE LAS BESTIAS PARDAS

select first +10 dbsname DatabaseName,
B.tabname,B.seqscans,B.bufreads,B.bufwrites,B.pagreads,B.pagwrites,
A.ti_pagesize,
(ti_nptotal * ti_pagesize ) / 1024/ 1024 Mbsizeallocated,
(ti_npused * ti_pagesize ) / 1024/ 1024 Mbusedpages,
ti_nrows nrows
from sysmaster:systabinfo A, sysmaster:sysptprof B
where A.ti_partnum = B.partnum
and B.tabname not matches "TBL*" -- util para tablespace tablespace
and B.tabname not matches "sys*"
and B.dbsname not in ('sysmaster', 'sysutils')
order by MBusedpages desc

Trukillos varios

# Find how long informix has been online
SELECT sh_curtime-sh_boottime FROM sysshmvals

# Find when the stats were last cleared
output to pipe "cat" without headings
select sh_curtime - sh_pfclrtime from sysshmvals;

Query con stats de acceso a disco y sequential scan

select first +10 sqx_sessionid,sqx_executions,sqx_bufreads,sqx_pagereads,sqx_seqscan,
sqx_sqlstatement
from syssqexplain
order by 5 desc

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