#! /bin/sh

# This is a shell archive.  Type 'sh <file>' to unpack.

echo x - README.1st
cat >README.1st <<'MKSHAR_EOF'
SP_SHOW_LOCKS, by Thomas J. Girsch
----------------------------------

The enclosed procedure, sp_show_locks(), gives a detailed list of which user
sessions currently hold locks against a specified database table, and what
type of locks they hold.  It has proven to be (for me, at least) an
invaluable tool in solving concurrency issues.

To create the sp_show_locks() procedure on a database <db>, simply type:

        dbaccess <db> spsl.sql

The procedure is only intelligent enough to find locks held against the current
database, so if you have many databases you'll have to modify the proc or else
create it in each database.

Usage:

        EXECUTE PROCEDURE sp_show_locks(<table-pattern>);

where <table-pattern> is the name of a table (as a quoted string) or a wildcard
The procedure is only intelligent enough to find locks held against the current
database, so if you have many databases you'll have to modify the proc or else
create it in each database.

Usage:

        EXECUTE PROCEDURE sp_show_locks(<table-pattern>);

where <table-pattern> is the name of a table (as a quoted string) or a wildcard
pattern that will match one or more tables.

Examples:

        EXECUTE PROCEDURE sp_show_locks("orders");
                -- shows current locks held against the 'orders' table

        EXECUTE PROCEDURE sp_show_locks("sys*");
                -- shows current locks held against system catalogs

        EXECUTE PROCEDURE sp_show_locks("*");
                -- shows all locks held


Example Output:

	(expression)  User informix (606) is 'X   ' locking table orders

In the above output, 'informix' is the user locking the table.  The number in
parens is the session ID of the session that owns the lock.  'X' is the lock
mode (exclusive), and 'orders' is the table name.


CAVEATS:

- Because the SP uses the SMI interface to get its information, it can
  often be somewhat slow.  I've seen it take as long as 30-45 seconds to
  return, although this isn't typical
- The procedure doesn't tell you whether a row/page in the table is being
  exclusively locked, or if the whole table is being locked.
- I thought about modifying the procedure to expand the lock mode from the
  letter code to a descriptive name, but this would have required hard-
  coding a mapping, and I felt that for forward-compatibility's sake this
  would be unwise.

If you have questions, comments, or just want to talk, I can be reached via
my e-mail, <tgirsch@iname.com>

Enjoy!

- Tom Girsch
MKSHAR_EOF


echo x - spsl.sql
cat >spsl.sql <<'MKSHAR_EOF'
CREATE PROCEDURE "informix".sp_show_locks( sp_locked_table CHAR(80) )
	RETURNING VARCHAR(80);

DEFINE sp_owner INTEGER;
DEFINE sp_table VARCHAR(18);
DEFINE sp_type VARCHAR(4);
DEFINE sp_user_msg VARCHAR(80);

FOREACH SELECT DISTINCT owner, tabname, type
	  INTO sp_owner, sp_table, sp_type
	  FROM sysmaster:syslocks
	 WHERE tabname MATCHES sp_locked_table
	   AND tabname IN ( SELECT tabname
			      FROM systables )

	FOREACH SELECT DISTINCT "User " || username || " (" || TRUNC(sid) || ") is '" || sp_type ||
				"' locking table " || sp_table
		  INTO sp_user_msg
		  FROM sysmaster:syssessions
		 WHERE sid = sp_owner
	 RETURN sp_user_msg WITH RESUME;

	END FOREACH;

END FOREACH;

END PROCEDURE

DOCUMENT "This procedure shows who owns locks on the specified table.";


 

MKSHAR_EOF