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'