backing up Logical Logs to file (compressed)

LTAPEDEV /opt/informix/backup/logs

# BACKUP_FILTER       - Specifies the pathname of a filter program
# RESTORE_FILTER      - Specifies the pathname of a filter program
BACKUP_FILTER 'pigz -p 8'
RESTORE_FILTER gunzip

Restart listener dynamically

onmode -P start

memory usage per session

sysscblst
- sid
- username
- hostname
- memtotal
- memused

Installer issues v11.5

I've seen error during an installation of informix v11.5 specially during the phase Branding Installed Files: "java.text.ParseException: Unparseable date"

if you see that, try to install it with the following options:

./installserver -javahome none –console

./ids_install -javahome -console

Also I've seen the following error during an 11.5.FC8W2 installation on solaris 10 in a local zone:

Creating uninstaller...

id: illegal option -- u

Usage: id [-ap] [user]

/opt/informix/kk/gskit/installgskit: test: argument expected

apparently the script is using a wrong "id" command and IBM considers it as a cosmetic error

Spot mutex locks

#!/bin/ksh
#set -x
## param 1: min number of times a waiter has to appear on the list to be considered as a mutex lock

## param 2: consecutive number of times to sample "output onstat -g lmx"
## param 3: wait time between samplings
## it only shows the session holding a mutex in a partition

## recomendation: waiters.sh 2 5 5


_npollings=1
_samplings=$2
_interval=$3

while true
do
while [ _npollings -lt $_samplings ]
do
onstat -g lmx
((_npollings+=1))
done | awk -v max_waiters=$1 '
BEGIN { _alertit = 0 }
/^[0-9]/ {
if ($3 in holdinglock)
holdinglock[$3]++;
else
holdinglock[$3]=1;

}

/^[ \t]+/ {
if ( $1 in waiters)
waiters[$1]++;
else
waiters[$1]=1;
}
END {
for (_waiter in waiters)
{
if (waiters[_waiter] >= max_waiters)
_alertit=1;
}
if (_alertit == 1)
for (_holder in holdinglock) {
if ( substr(_holder,1,3) == "pt_" )
printf "%s\n",substr(_holder,4);
}
}
' | while read _holder
do
date
echo "SELECT FIRST 1 t.dbsname, t.tabname FROM systabnames t where lower(hex(t.partnum)) like \"%"$_holder"%\";" | dbaccess sysmaster
done| tee -a new_mutex_counts.out
sleep $_interval
done

Sessions doing sorts to disk

select username,

syssesprof.sid,

total_sorts,

dsksorts,

max_sortdiskspace

from syssesprof, syssessions

where syssesprof.sid = syssessions.sid

order by max_sortdiskspace desc

You can use a SQL statement to obtain information regarding each of the temporary tables that are currently created

SELECT t.tabname ,t.dbsname,t.owner,
DECODE(d.is_logging,1,"Y","N") AS db_with_log,
s.name AS dbspace,
DBINFO("UTC_TO_DATETIME",ti_created) AS created,
DECODE(hex(mod(ti_flags,256)/16),6,"Y","N") AS
table_using_log,ti_npused AS num_usedpages,
ti_nptotal AS num_pages
FROM sysmaster:systabnames t, sysmaster:systabinfo i,
sysmaster:sysdbspaces s, sysmaster:sysdatabases d
WHERE t.partnum=ti_partnum AND
d.name=t.dbsname AND
s.dbsnum=TRUNC(t.partnum/1048576) AND
hex(mod(ti_flags,256)/16) IN ( 6,2 )

BTREE Cleaner, something to start with

onstat -C

onstat -C hot

onstat -C clean

onmode -C start X

onmode -C stop

onmode -C rangesize 100

If you are version 10.00.xC8 or higher I would suggest turning on ALICE=

scanning to value 6 or higher.

onmode -C alice 6

This will greatly increase the performance of the index cleaning.

Just to add that ALICE will only work if the indexes are detached which they

should if they were created under v10, but if you migrated in place, this

may not be the case...

Everything related to Locks

select

l.indx,

l.partnum,

p.dbsname,

p.tabname,

l.rowidr,

l.keynum,

l.grtime,

DECODE(l.type,

0, 'NONE',

1, 'BYTE',

2, 'IS',

3, 'S',

4, 'SR',

5, 'U',

6, 'UR',

7, 'IX',

8, 'SIX',

9, 'X',

10, 'XR') type,

r.sid,

s.pid,

s.hostname

from

syslcktab l, -- raw lock table

systabnames p, -- partnum -> dbs/tab map

systxptab x, -- transactions

sysrstcb r, -- rsam thread control blocks

sysscblst s -- session control blocks

where

l.partnum <> 1048578 and -- skip 'sysmaster.sysdatabases'...

l.partnum = p.partnum and

l.owner = x.address and

x.owner = r.address and

r.sid = s.sid

order by

l.grtime desc

BTR Buffer turnover ratio

BTR = (((bufwrits + pagreads) / BUFFERS) / elapsed)

elapse in hours (since you zero-ed the stats)

This might find the sql causing a locked mutex....

#!/bin/ksh

my_FILE="/tmp/glmx.out"

onstat -g lmx | awk '{printf $4 "\n"}' | egrep -v "Server|holder|on" > $my_FILE

if [ -s $my_FILE ]

then

while read line

do

if [ ! -z "$line" ] # string is not empty

then

ath_val=`onstat -g ath | grep "$line" | awk '{printf $3}'`

sid=`onstat -u | grep $ath_val | awk '{printf $3}'`

echo "SQL output for session: $sid"

onstat -g sql $sid > /tmp/$sid-lmx.out

else

echo "No session found for locked mutex."

fi

done < $my_FILE

else

echo "No locked mutexes found"

fi

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