ISAM error -106 non-exclusive access

Good morning, I'm sure this question has come up dozens of times. I couldn't find what I was looking for in the list archives. I have a table that we need to modify schema "Informix".profile_rec, but I can rename or drop this table due to non-exclusive access (isam error -106). I cannot find any users locking the table when querying the syslocks table in the sysmaster database. Also, onstat -k is too complicated for me to trace back to tables/users. Does someone have a handy process or script that can find a lock that isn't in syslock and trace it back to a user's id/session? Short of bouncing the IDS engine, I know of no other way to release locks. Thank-you in advance for your assistance. Jonathan Smaby Pomona College ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

This has been helpful to me in the past: #!/usr/bin/ksh function usage { print " " print "Usage: hidden_locks.sh -d DATABASE -t TABLE " print " " print " -d DATABASE NAME" print " -f TABLE NAME " print " " } function validate { dum="${OPTARG}" if [[ ${dum} = '-' ]] then print "${opt} requires an argument" usage exit fi } typeset -L1 dum='' dopt=0 topt=0 while getopts ":t:d:" opt do case ${opt} in t) validate topt=1 TABLE=${OPTARG};; d) validate dopt=1 DATABASE=${OPTARG};; :) print "You must supply an argument with an option " usage exit;; \\\\?) print "${OPTARG} is an invalid argument" exit;; esac done shift `expr $OPTIND - 1` if [ $topt -ne 1 ] then print "No table name specified" usage exit fi if [ $dopt -ne 1 ] then print "No Database specified" usage exit fi onstat -g opn > /tmp/onstat_g_opn onstat -u > /tmp/onstat_u >/tmp/sessions_unsorted for PARTNUM in `echo "select hex(partnum) from systables where tabname = \\\\"$TABLE\\\\" UNION select hex(partn) from systables t,sysfragments f where tabname = \\\\"$TABLE\\\\" and t.tabid=f.tabid;"|sqlcmd -d $DATABASE|cut -d 'x' -f 2` do print -u2 "PARTNUM:$PARTNUM" for RSTCB in `grep $PARTNUM /tmp/onstat_g_opn|cut -d ' ' -f 2|cut -d 'x' -f 2|sort -u` do print -u2 "\\\\tRSTCB:$RSTCB" print -u2 "\\\\t\\\\t`grep $RSTCB /tmp/onstat_u`" SESS=`grep $RSTCB /tmp/onstat_u|cut -d ' ' -f 3` echo "$SESS" done done|sort -u>/tmp/hidden_locks_sessions print -u2 "\\ Sessions holding hidden Locks are in /tmp/hidden_locks_sessions" -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan Smaby Sent: Thursday, October 09, 2008 2:05 PM To: ids@iiug.org Subject: ISAM error -106 non-exclusive access [13652] Good morning, I'm sure this question has come up dozens of times. I couldn't find what I was looking for in the list archives. I have a table that we need to modify schema "Informix".profile_rec, but I can rename or drop this table due to non-exclusive access (isam error -106). I cannot find any users locking the table when querying the syslocks table in the sysmaster database. Also, onstat -k is too complicated for me to trace back to tables/users. Does someone have a handy process or script that can find a lock that isn't in syslock and trace it back to a user's id/session? Short of bouncing the IDS engine, I know of no other way to release locks. Thank-you in advance for your assistance. Jonathan Smaby Pomona College ------------------------------------------------------------- This message has been scanned by Postini anti-virus software. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

2008/10/9 Jonathan Smaby <Jonathan.Smaby@pomona.edu>: > Good morning, > > I'm sure this question has come up dozens of times. I couldn't find > what I was looking for in the list archives. I have a table that we > need to modify schema "Informix".profile_rec, but I can rename or drop > this table due to non-exclusive access (isam error -106). I cannot find > any users locking the table when querying the syslocks table in the > sysmaster database. Also, onstat -k is too complicated for me to trace > back to tables/users. Does someone have a handy process or script that > can find a lock that isn't in syslock and trace it back to a user's > id/session? Short of bouncing the IDS engine, I know of no other way to > release locks. > > Thank-you in advance for your assistance. > > Jonathan Smaby > > Pomona College > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Jonathan Also if you have ER running this will stop an alter/drop table, you will need a 'cdr stop' on the instance with the table. Keith