Monday, August 24, 2009

Monitoring temporary dbspaces

This script was created by my colleague. The objective is to dump out onstat -d outputs whenever the free spaces in the temporary dbspaces is running low. The script can be modified to change the duration and the amount of free space left before it starts dumping outputs of onstat -d.


chk_space.sh
=================================================
#!/bin/sh
i=0
while [ $i -lt 3600 ]
do
onstat -d | awk -f t_flex.awk |tee ./tt$$.out
fsum=`cut -f 5 -d " " ./tt$$.out`
if [ $fsum -lt 1000000 ]
then
echo "Space is being monitored" $fsum
date >> chk_tmp_$$.out
onstat -d >> chk_tmp_$$.out
fi
i=`expr $i + 1`
echo "Finish Loop: " $i
sleep 1
done
rm ./tt$$.out

=================================================

t_flex.awk
=================================================
/dev\/rawlinks\/temp/ { ssum+=$5;fsum+=$6 }
END { print "Total:",ssum," Free:",fsum }
=================================================

/dev\/rawlinks\/temp/ is the chunk path of temporary dbspaces.

Monday, August 3, 2009

Detecting long transactions

SQL to detect how long a transaction has been open and displays all transactions that have less than a percentage amount left of logs before going into long transaction.

database sysmaster;
set isolation to dirty read;
select a.sid, b.longtx, b.logbeg, c.cf_effective ltxhwm,
d.cf_effective logfiles, e.uniqid currlog,
round(((e.uniqid - b.logbeg)/d.cf_effective)*100) pctused
from sysrstcb a, systxptab b, syscfgtab c, syscfgtab d, syslogs e
where a.address>0
and a.sid>0
and b.logbeg>0
and c.cf_name="LTXHWM"
and d.cf_name="LOGFILES"
and e.is_current=1
and c.cf_effective - round(((e.uniqid - b.logbeg)/d.cf_effective)*100) <= 10
and a.address=b.owner
order by pctused desc

The query will display the following columns:

sid - session id of the user thread
longtx - 1 if in long transaction already
logbeg - begin log of this transaction
ltxhwm - long transaction high water mark set in onconfig
logfiles - number of logical logs
currlog - current log used by the instance
pctused - percentage of the total number of logs (i.e. logfiles) from logbeg to currlog of this transaction

The output will be displayed in the order of pctused. Only those transactions that have 10% or less amount of logs remaining before reaching the LTXHWM will be displayed. You can change this condition by changing the following line:

and c.cf_effective - round(((e.uniqid - b.logbeg)/d.cf_effective)*100) <= 10 (change to 20, 30, etc, this is the percentage left before reaching LTXHWM)

Locked databases

SQL to get the session/thread holding a lock on a database.

dbaccess sysmaster;
set isolation to dirty read;

select b.name database_name, "HDR+"||a.type lock_type,
c.us_sid session_id, c.us_name owner,
d.th_id tid, d.th_name thread
from syslocks a, sysdatabases b, sysuserthreads c, systhreads d
where a.rowidlk=b.rowid
and a.owner=c.us_sid
and c.us_mttcb=d.th_addr
and b.name="<database name>";

For example:

select b.name database_name, "HDR+"||a.type lock_type,
c.us_sid session_id, c.us_name owner,
d.th_id tid, d.th_name thread
from syslocks a, sysdatabases b, sysuserthreads c, systhreads d
where a.rowidlk=b.rowid
and a.owner=c.us_sid
and c.us_mttcb=d.th_addr
and b.name="stores1"

-----

database_name stores1
lock_type HDR+S
session_id 97
owner informix
tid 140
thread sqlexec