LISTLOCKS - for OnLine 4.0
Here's an idea I've been toying with that finally came together during a
presentation at the recent Informix Worldwide User Conference. It takes
the tbstat -k/tbstat -u/systables information and displays it in a
"human readable" format. It should work on any System V machine running
OnLine 4.0. It may also work with other UNIX platforms and OnLine 5.0
but I have no way to test this.
This comes with no guarantees or support [insert standard disclaimer here].
If anyone comes up with any improvements/fixes, I'd love to hear about
them (so I can add them to my system :-).
CAVEATS:
There is no way I know in 4.0 to look up a list of databases. You
have to hard code them into the script.
While I can identify database shared locks by tblsnum (0x01000002),
I can't tell which database they are for, so I just use the
generic N/A.
The distinction between Row and Key locks is blurry. The reference
only says that Key locks have "long" rowids. So some "Key" locks
may be Row locks in disguise.
You need the "join" program. I don't think this is standard on
all BSD machines.
You need isql/isqlrf. It may be possible to replace this with
dbaccess for OnLine 5.0, but I can't test this.
Since this is a snapshot, lock information is already out of date
by the time you see it. This is especially true since the tbstat
commands are run separately in this script. However, most real
lock problems stick around for a while.
Yes, this could have been done better in PERL. But our management
hasn't signed on with PERL, and I wanted to make this work for all
our machines. The port to PERL is left as an exercise for the
reader :-).
Sorry, no man page is available. See the comments in the code.
Talk to your Informix Rep. and get them to provide an option to
tbstat that does this for you.
David Schmitt, Systems Administrator Voice: (708)632-5562
Cellular Subscriber Group MIS FAX: (708)632-4421
Motorola, Inc., Room S356 Email: daves@csg.gss.mot.com
1475 W. Shure Dr., Arlington Hts, IL 60004
#! /bin/sh
# This is a shell archive. Remove anything before this line, then unpack
# it by saving it into a file and typing "sh file". To overwrite existing
# files, type "sh file -c". You can also feed this as standard input via
# unshar, or by typing "sh <file", e.g.. If this archive is complete, you
# will see the following message at the end:
# "End of shell archive."
# Contents: listlocks
# Wrapped by daves@techmpc on Tue Jul 14 12:55:04 1992
PATH=/bin:/usr/bin:/usr/ucb ; export PATHif test -f 'listlocks' -a "${1}" != "-c" ; then
echo shar: Will not clobber existing file \\"'listlocks'\\"
else
echo shar: Extracting \\"'listlocks'\\" \\(5726 characters\\)
sed "s/^X//" >'listlocks' <<'END_OF_FILE'
X#!/bin/sh
X#
X# @(#) listlocks.sh 1.3@(#)
X# Author: Dave Schmitt (daves@csg.gss.mot.com)
X#
X# This script collects information about locks from an OnLine 4.10
X# database, and prints it in a human readable format. Why Informix
X# doesn't already do this, I have no idea.
X#
X# We require:
X# tbstat (comes with OnLine)
X# isql (development or runtime)
X# awk (new, old, or clone)
X# sort (standard System V - don't know about BSD)
X# join (standard System V - don't know about BSD)
X# tr (standard System V - don't know about BSD)
X#
X######################################################################
X# START OF CONFIGURATION SECTION
X######################################################################
X#
X# Since I can't figure out a way from the shell to get a list of all
X# databases, you'll have to list them yourself, here. Sorry.
X#
XDBS="stores syspgm4gl"
X#
X# Directory to put temporary files.
X#
XTMPDIR=${TMPDIR:-/usr/tmp}
X#
X# The AWK program (both new and old awk should work)
X#
XAWK=awk
X#
X# Set up the Informix environment if it isn't already
X#
XINFORMIXDIR=${INFORMIXDIR:-/usr/informix}
XSQLEXEC=${SQLEXEC:-${INFORMIXDIR}/lib/sqlturbo}
XTBCONFIG=${TBCONFIG:-tbconfig}
XPATH=${PATH}:${INFORMIXDIR}/bin
Xexport INFORMIXDIR SQLEXEC TBCONFIG PATH
X#
X# Use Run-Time if available, otherwise Development isql.
X#
XISQL=`type isqlrf >/dev/null 2>&1 && echo isqlrf || echo isql`
X#
X######################################################################
X# END OF CONFIGURATION SECTION
X######################################################################
X
XTBSTAT_U1=${TMPDIR}/tbu1.$$
XTBSTAT_U2=${TMPDIR}/tbu2.$$
XTBSTAT_K=${TMPDIR}/tbk.$$
XTBLSPACE=${TMPDIR}/tbl.$$
XJOIN1=${TMPDIR}/join1.$$
XJOIN2=${TMPDIR}/join2.$$
XJOIN3=${TMPDIR}/join3.$$
XJOIN4=${TMPDIR}/join4.$$
X
X#
X# Collect user information and sort by 1) address; 2) lock waited for.
X#
X# Each line looks like:
X#
X# address flags pid user tty wait tout locks nreads nwrites
X#
Xecho "Gathering user information..." >&2
Xtbstat -u | ${AWK} '
X$2 == "flags" { PRINT = "true"; next }
X$2 == "active," { PRINT = "false"; }
XPRINT == "true" { if (NF == 9) {
X print $1, $2, $3, $4, "-", $5, $6, $7, $8, $9
X } else {
X print $1, $2, $3, $4, $5, $6, $7, $8, $9, $10
X }
X }
X' | sort > ${TBSTAT_U1}
Xsort +5.0 < ${TBSTAT_U1} > ${TBSTAT_U2}
X
X#
X# Collect lock information and sort by tblsnum. Each line looks like:
X#
X# address wtlist owner lklist type tblsnum rowid size
X#
Xecho "Gathering lock information..." >&2
Xtbstat -k | ${AWK} '
X$1 == "address" { PRINT = "true"; next }
X$2 == "active," { PRINT = "false"; }
XPRINT == "true" { print $1, $2, $3, $4, $5, "0x0" $6, $7, $8 }
X' | sort +5.0 > ${TBSTAT_K}
X
X#
X# Collect tblspace information and sort by tblsnum. Each line looks like:
X#
X# database owner table tblsnum
X#
Xecho "Gathering table information..." >&2
X(
X for DBNAME in ${DBS}
X do
X echo "select owner, tabname, hex(partnum) from systables" |
X ${ISQL} ${DBNAME} 2>/dev/null |
X ${AWK} '$3 ~ /^0x/ { print "'${DBNAME}'", $1, $2, $3 }' |
X tr '[A-Z]' '[a-z]'
X done
X #
X # The special case of the database tblspace, a tblspace used to
X # keep track of databases, and thus not a part of any one of them
X #
X # Whenever you connect to a database, you place (at least) a shared
X # lock on the corresponding row in this tblspace for that database.
X #
X echo "N/A informix - 0x01000002"
X) | sort +3.0 > ${TBLSPACE}
X
X#
X# Join the lock and table information and sort by 1) user; 2) lock.
X#
X# We have to cut out certain fields, because "join" doesn't handle more
X# than 11 for some reason. It doesn't really matter since we're limited
X# to 80 columns if we want the screen to look clean :-).
X#
Xecho "Joining lock/table information..." >&2
X#
X# owner type rowid size database owner ta