utility Script: who-locks.sh ----------------============ Synopsys: Displays information on active locks, more comprehensible than the output of onstat -k Dependencies: - fragment-list.sh: A component of fragments.shar. Obtain at: ftp://ftp.iiug.org/pub/informix/pub/fragments.shar.gz - beautify-unl: Perl utility and module to straighten out columns of delimited data. Obtain at: ftp://ftp.iiug.org/pub/informix/pub/beautify-unl.shar.gz Author: Jacob Salomon jakesalomon@yahoo.com Release: 2.1 Date: 2014-05-06 (I was surprised to see I had not submitted it to IIUG before. My apologies.) Motivation and Purpose of this Utility: --------------------------------------- When a table lock, be it shared or exclusive, gets in the way of an operation you need to run e.g. HPL (which demands exclusive access to the table) it might be a good idea to find out who has a lock on that table. Your first instinct, ifyou're not too jaded yet, mightbe to run: $ onstat -k # (Output edited to fit in 72 columns) Locks address wtlist owner lklist type tblsnum rowid 10ae9ca58 0 13dbb7130 0 HDR+S 100002 2102 10ae9cad8 0 13dbb9a98 0 S 100002 2102 10ae9cb58 0 13dbbed68 0 S 100002 2101 ... Good luck figuring that out. Most of the locks displayed are database locks and thus probably irrelevant to your quest. If you happen to know the tblspace number of your target table perhaps you can grep the above listing for it. (Plug alert: YOu can get the tblspace number[s] of a table using my long-posted utility fragments.sh.) Still, there really should be an easier way. Well, seek no more, my friends! Here it is: Everything you want to know about the locks in your server! Example: You wish to run HPL on table yutz in database abcd i.e. abcd:yutz. But you get the message that it is lock by another user. You simply run who-locks.sh, specifying the database and name of your target table: $ who-locks.sh -d abcd -t yutz. The output would look something like he lines below, except it would be much wider than my [self-imposed] 80-column editing window, so I will show the heading and data lines split up: |Table |Index|Row-ID |LKTY|Sess|Locked-by|AtHost |abcd:jake.yutz|(NA) |0x00000000|X |2399|joe |robin.sherwood.com |TTY|PID |Waiter|WName |WHost |Lock-Life | | |23198| 2228|jsalomon|localhost| 0 00:01:03.000| So I see that my target table, abcd:yutz (owned by user jake) is exclusively locked (row ID 0 is the giveaway) by user joe, who is accessing it from his host machine, robin.sherwood.com. Of course, there is no TTY data because that is available only for a local session. If user joe were on a local session (indicated by host name localhost) the TTY woule read something like pts/29 and the PID would be that of the front-end program with the database connection. Actually, even on the remote host as in the above example, you might be able to log in on that host and use options to the "ps" command to determine what application program is accessing the table. The remainder of this README file is merely the output of who-locks.sh -H That is: The supplied help text, which give a pretty good (if I may say so myself) explanation of all the options. $ who-locks.sh -H who-locks.sh queries SMI tables tnd the catalogs of individual databases in order to get expanded information on locks, including owner identification, database and table names, waiter info, and lock duration. All options that have parameters follow the same convention: The option with a comma-separated list or repeating the option with one or more parameters to follow. Examples below. Usage: who-locks.sh [-H] [-d database] [-t table] [-u User-ID of lock owner] [-h host machine] [-p PID] [-w waiter id] [-W] [-D] -H : Display this help text and exit. Ignores other parameters -d database : Display locks only on tables in the specified databases. If multiple database are specified, the parameter consists of a list of databases sepearted by commas. Eg: -d store,utils Also, the parameter me be repeated: Eg: -d store -d utils,inventory Default: All database. -t table : Display locks only on tables in the list. Eg: -t employee,task,project Eg: -t employee -t task,project Default: All tables. -u owner : Specify a login ID. Display only those locks that are held by the specified user[s]. Default: All owners -h host : Specify a host machine - Show only those locks that are owned by a user running on the named [client] machine. (This is of arguable use for PC-based clients.) Default: All locks, regardless of host. -p pid : Process ID. Once you have identified some suspicious usage, you can track those locks that were created courtesy of only that client program by specifying its process ID (PID). -w waiter : Specify login ID of waiter. Suppose a user with the login ID of yutz calls you (the DBA) and says "My app is hanging; please see who is holding locks on the rows I need." You can specify the "-w yutz" option and view only those locks that are waited for by yutz. -W : Show only those locks with someone waiting on them. This is useful if there is a general applications slowdown and you suspect users are waiting too long for locked rows. If you specify both the -W option and a -w list, the list will be ignored and who-locks.sh will use this -W option only. -D : Show database locks. Since most database locks are innocuous - nearly always shared - it is a waste of screen (and eyeball) space to display the database locks. Hence, the default is to omit them. Should it be necessary to display database locks eg. someone needs to run dbexport, use this option to see who's in the way. ==========================================================================