Hello..

I'm facing one problem in my database informix 11.50.

I don't know why, but sometimes some tables turn locked and I need restart database to my aplication work fine again.

I try to find the user or process that locked the table in syslocks, but not apear there.... I'm 100% my table's locked.. but I don't know who locked it and how I can free it...

Someone can help me ??

This should show you who's locking a table.

database sysmaster;
set lock mode to not wait;
set isolation to dirty read;

select dbsname[1,12], tabname[1,12],username[1,12], tty[1,12],
connected ,
type
from syssessions, syslocks
where sid = owner
and tabname = ?

-----
otherwise, you'll have to dig through onstat output to find it.
-----

If you're trying to alter that table, then any access to that table will prevent modification. You then need a rather complex script to find it (!) I have such a script, but let me know if the above doesn't work for you first.

Hi, if you're working with a Linux system: this script digs through the onstat output to find it.This doesn't show all the locks in all situations is my experience but I couldn't find the alternative way in the sysmaster DB. Maybe bigcalm's query fills the gap for me?

Good luck,
Hans

#/usr/bin/sh
#
# Author: Jonathan Daniel
#
# This script will tell you who is accessing a particular table.
# It can be useful when attempting to alter/drop/rename a table
# and you're experiencing the 106 ISAM error - eg
# 106: ISAM error: non-exclusive access.
#
#
# Derived mainly from information from here:
# Informix technology: When 'Exclusive' is not really exclusive...
# IBM Informix Dynamic Server v11.50 Information Center
#
# Warning: will only work for dba, probably only informix.
# Tested on informix 9.40.FC3, AIX 5.3
#
# Requires dbaccess and awk derivatives (awk,sed,grep)
if [ ! "$1" ]
then
echo "Usage: $0 <database> <table> [-V]"
exit 0;
fi

if [ ! "$2" ]
then
echo "Usage: $0 <database> <table> [-V]"
exit 1;
fi
VERBOSE=""
if [ "$3" == "-V" ]
then
VERBOSE="ON"
fi

dbaccess $1 - >/dev/null 2>/dev/null <<EOT1!
set lock mode to not wait;
set isolation to dirty read;
unload to /tmp/hex1.unl
select lower(hex(partnum)), tabname, dbsname
from sysmaster:systabnames
where dbsname = "${1}"
and tabname = "${2}"
;
EOT1!

if [ ! -s /tmp/hex1.unl ]
then
echo "Cannot find table $1/$2"
exit 0;
fi

hexno=`head -1 /tmp/hex1.unl | sed 's/|.*//g'`
if [ "${VERBOSE}" ]
then
echo "part number of table - ${hexno}"
fi

opensessions=`onstat -g opn | grep ${hexno} | awk '{print $2}' | sed 's/^0x//' | sed 's/^0//g' `
if [ $? -ne 0 ]
then
echo "No-one is currently accessing $1/$2";
exit 0;
fi
if [ ! "${opensessions}" ]
then
echo "No-one is currently accessing $1/$2";
exit 0;
fi
echo "User \tTerminal \tHost \tSessionID"
for sessionid in ${opensessions}
do
if [ "${VERBOSE}" ]
then
echo "Session Id: ${sessionid}"
fi

splitme=`onstat -u | grep ${sessionid} | awk '{printf("%s %s %s",$3,$4,$5);}' `
if [ ! "${splitme}" ]
then
# Has probably already disappeared
continue;
fi
sqlid=`echo ${splitme} | awk '{printf("%-10s",$1);}' `
userid=`echo ${splitme} | awk '{printf("%-10s",$2);}' `
terminal=`echo ${splitme} | awk '{printf("%-10s",$3);}' `

if [ "${VERBOSE}" ]
then
echo "SQL ID: ${sqlid}..."
onstat -g sql ${sqlid}
echo "User: ${userid}. Terminal ${terminal}"
fi
# Get hostname
orighost=`onstat -g ses | grep ${sqlid} | awk '{printf("%-10s",$5);}'`
# I can't get the host IP. For some reason, only 8 characters are
# available to me in onstat -g ses
echo "${userid}\t${terminal}\t${orighost}\t${sqlid} "
done

echo "\nYou may kill the sessions listed using: onmode -z <SessionID>"
echo "You can view the sql currently being run using: onstat -g sql <SessionID>"

Thanks for all.

My problem was in disk space in chunk... it was full.


Anyways thanks for scripts to check locks.


Yours
Fernando.