#---------------------------------- cut here ----------------------------------
# This is a shell archive.  Remove anything before this line,
# then unpack it by saving it in a file and typing "sh file".
#
# Wrapped by Jacob L. Salomon <dajls@clancy> on Thu Jan 10 11:45:12 2002
#
# This archive contains:
#	who-lock.txt	who-lock.sh	who-locks.sh	who-access.sh	
#
# Modification/access file times will be preserved.
# Error checking via wc(1) will be performed.
# Error checking via sum(1) will be performed.

LANG=""; export LANG
PATH=/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:$PATH; export PATH

if sum -r </dev/null >/dev/null 2>&1
then
	sumopt='-r'
else
	sumopt=''
fi

echo x - who-lock.txt
cat >who-lock.txt <<'@EOF'
Program(s):             who-lock.sh
                        who-locks.sh
                        who-access.sh
Purpose:                Determine who is accessing a table
Calls scripts:          beautify-unl.sh
Author:                 Jacob Salomon
                        JakeSalomon@netscape.net
Initial Release Date:   1999-09-02
Latest  Release Date:   2001-01-10
Acknowledgement:        Rick Bernstein, who posted lock-test.sh, the
                        script on which I based who-access.sh .
========================================================================

The Problem:
-----------

When in the course of admin events it becomes necessary to gain
exclusive access to a table (eg. for an ALTER TABLE), the DBA sometimes
finds it impossible to obtain this kind of access to said table;
someone is already accessing the table. Thus the engine rejects the
ALTER TABLE command with a stubborn message stating "Non-exclusive
access" or "The table has been locked by another user".  It would be
nice if you could easily determine the culprit (who is, most likely,
innocently going about his business).

In a Standard-Engine environment, the user find the session locking the
needed table by the following steps:

 - Using dbaccess, get the path name to the table
 - Call up the system administrator (or a pal with root privilege) and
   ask for a run of "fuser -u" utility on that path name. Get the PID.
 - Run ps -ef, piping the output to a grep to scan for that pid.

This is a tedious task, difficult to automate due to privileged nature
of the fuser command. Short of subverting Unix security, this can be
done manually only.

For the Dynamic Server environment, Informix provides some tools to
help determine who this is. The normal procedure is:

 - Keep a list of all database:tables in the system, with their (hex)
   partition numbers. For fragmented tables, keep a list of the
   partition numbers of all the fragments.  Oh yes, in order to be able
   to cut/paste from this list to the steps below, be sure to maintain
   the partition numbers with the letters a-f in lower case.  Note that
   the SQL function hex() produces hex values with these letters in
   upper case.
 - <onstat -k>  Display the lock table from shared memory, piping the
   output to a grep command to scan for the hex partition number of the
   table you want to access.  For a fragmented table, the grep command
   should scan for all partition numbers associated with the table,
   including those of detached indices. Note the user number in column
   3 of the output.
 - <onstat -u>  Display the user list from shared memory, piping the
   output to a grep command that scans for the user numbers derived
   from the above step.
 - <onstat -g ses nnnn> Display information about the session,
   including the tty, to find out what the user is doing and, from the
   tty, perhaps where the person is located.

WOW! That sure sounded easy, especially maintaining that list!

Sarcasm aside, there is a query and shell-script that can generate this
list of partitions. This is partitions.sh, which repeats the above
algorithm for each user database in the system.  It is available in the
IIUG library at URL:
    http://www.iiug.org/ver1/software/archive/partitions

Note also that the difficulty presented by the case of the hex numbers
was slightly exaggerated; grep -i will perform case-insensitive
comparisons.

This package has 3 alternatives to the above. They are shell-scripts:
 - who-lock.sh    Display a list of lock information, including the
                  database, table, owners and waiters on each lock.
 - who-locks.sh   Display same data as who-lock.sh, but with far more
                  flexible parameter conventions.
 - who-access.sh  Display user sessions of users who are accessing a
                  table.

Both who-lock.sh and who-access.sh have the same simple command line
parameters: A list of tables in the form database:table. There are some
functional differences, however, so each merits its own discussion. The
who-locks.sh (with the plural locks) program, by contrast, accepts a
list of options and parameters.

Both who-lock.sh and who-locks.sh pipe their output to beautify-unl.sh
to force the output into evenly spaced columns.  This is available from
the IIUG software archives under "Miscellaneous Utilities".
who-access.sh translates it parameters into a grep filter and produces
a subset of the output of the command onstat -g ses.  This has no
guarantee of column allignment.
========================================================================

who-lock.sh
-----------
The utility who-lock.sh can be invoked three ways:
 - On its own, with no parameters.  This is essentially a dump of the
   entire table sysmaster:syslocks, along with some data from some other
   sysmaster tables.
 - With the name of a database.  In this case, it displays any locks
   that any user has on the specified database.
 - Specifying a database:table.  Now it displays all locks on that
   table.

More than one parameter may be specified. I might specify two or more
databases, two or more qualified tables (database:table) or mix parame-
ters at different levels of qualification:
    who-lock.sh book imm:rtrn_cat
This example will display all locks on database book, as well as all
locks on table imm:rtrn_cat.

What kind of information does who-lock display?  This doc is being
edited on a 72-character line. The output of who-lock.sh is usually
wider, ranging to about 140 characters, though averaging under 100 on
the machines I have tested it on.  In any case, I cannot show an actual
line of output; it needs to be split up in this doc.

Database   |Table        |Row-ID    |LK-type|Locked-by|Session|
AtHost|TTY       |Waiter|Wait-Name|

The database and table names are obvious.
 - The Row-ID columns displays the hex rowid with the 0x prefix and
   the hex letters A-F in upper case. (Since we are not piping through
   onstat commands, there was no need to filter these down.)  Note that
   for a table lock, the rowid will be 0 (0x00000000).
 - LK-Type refers to the S (shared), X (exclusive) and I (Intent)
   designations.
 - Locked-by is the name of the user (syssessions.username)
 - Session is the session ID of the locker.
 - AtHost is the name of the host server that sought this lock. It is
   often not the current database server.  In client-server applications
   (like PeopleSoft apps) it is often the same as the userid.
 - TTY is almost obvious, if the user is in a local terminal session.
   In client-server applications, this is usually the IP name of the the
   client PC.
 - Waiter is the session ID of the first session in the queue waiting
   for the resource to be unlocked by the current locker.
 - Wait-Name is the username of the waiting session.

Here is a sample of some of the output. I have started a transaction and
set isolation to cursor stability. I am running s select * on table
alpha_s in database imm. This table uses page level locking.  After a
few rows, here are the output lines relevant to the table:

Database   |Table        |Row-ID    |LK-type|Locked-by|Session|AtHost|
TTY       |Waiter|Wait-Name|

imm        |(database)   |0x0000020D|S      |jake     |   3354|wolfe |
|/dev/ttyp3|      |         |
imm        |alpha_s      |0x00000000|IS     |jake     |   3354|wolfe |
|/dev/ttyp3|      |         |
imm        |alpha_s      |0x00000900|S      |jake     |   3354|wolfe |
|/dev/ttyp3|      |         |

Note first the last two "lines". I am holding an intent/shared lock on
the entire table, as indicated by rowid 0x00000000. I am also holding a
shared page lock on page 9 of the table, as indicated by the 00 in the
the two low-order digits of the rowid.

Now let's examine the first line of output. This is the shared database
lock I obtain when I connect to the database.  In the output of
onstat -k, this lock appears as:

  Locks
  address  wtlist   owner    lklist   type     tblsnum  rowid key#/bsiz
  c16128a4 0        cfa86e98 0            S    100002   20d         0

referring to tblspace 10002, rowid 20d (hex). 

This brings up to a very nice (IMHO) feature of who-lock.sh: Although a
database lock shows up as an ordinary row lock on a specific table,
who-lock.sh recognizes a database lock when it sees one and provides
more useful information than the fact that this is some row someplace
in the database tblspace.  It indicates which database that rowid
refers to.  And since no particular table is involved with this lock
(it's on the whole database, after all), it displays a parenthesized
"(database)" in the table column.

The above described the invocation of who-lock.sh with no parameters.
As described, the script can take parameters. This is very straight-
forward. Simply specify the name of a database or a qualified table
name in the form database:table. In either case, who-lock.sh displays
only locks that have been placed on the named database or table.

Note also that you can specify any number of tables and databases
on the command line.

$ who-lock.sh stores7 book:main_table inv:order_history

This will display all locks on the stores7 database (including database
locks), all locks on table main_table in database book (excluding
database locks, of course) and all locks on table order_history in
database inv.

As a debugging feature, who-lock.sh displays the WHERE clause of the
SQL query that it runs against the syslocks table.  While I have not
provided a command line option to turn this off, the downloader is free
to comment out the ECHO command that displays it.
========================================================================

who-locks.sh
------------

The utility who-locks.sh performs the same function as who-lock.sh
(with the singular "lock"). The similarity is mainly functional;
who-locks.sh uses some undocumented SMI tables in order to obtain
information omitted by the the sanctioned SMI views.  who-locks.sh also
uses a more Unix-like parameter convention (it calls getopts) and has
additional options to filter by lock attributes other than the table.
For example, I can track the locking behavior of a specific user by
entering the command:  who-locks.sh -u jake.

                        *IMPORTANT NOTE*
                        *--------------*
This script requires [the presence in the $PATH list] two other scripts
in order to run:

  - partition-list.sh   This utility comes bundled with partitions.sh
                        and can be optained at URL:
            http://www.iiug.org/ver1/software/archive/partitions

  - beautify-unl.sh     This oldie maintains straight columns in your
                        output. Fund at URL:
            http://www.iiug.org/cgi-bin/software/archive/beautify-unl

A complete help page for who-locks can be obtained by typing:
    $ who-locks.sh -H   # Help command"

who-locks.sh queries SMI tables 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.
--------------
Notes:
1.  There is greater flexibility with filters.  With these options I can
easily monitor the status of the locks with minimal output eg. only
locks held by a user grumpy that someone is waiting for. Or only
those locks being waited for by user sneezy. (Are these names under
copyright? ;-)

2.  If you specify a table name - skipping the database name parameter -
and the table name appears in multiple databases (e.g.  systables)
all tables of this name will have their locks displayed with their
database qualifiers.

3.  Regarding the -p option: Most of the time, there is one session id
per PID. After all, one client process can open only one session, right?
So what's the point of filtering by PID?  Truth be known, not much.
However some folks have actually written multi-threaded application
servers that create multiple sessions.  This option allows me to track
this client's locks.

4.  The -w is of dubious usefulness.  The waiter ID in onstat -k and in
the SMI column syslocks.waiter both show only the first waiter in a
queue to the lock.  If your locked-out session is third on the list, it
will not show up in the -w listing.  (This brings to mind another
possible feature to the program but that's not even on my back burner
right now.)  However, if the system is not outrageously busy, the
waiter queue on a lock is very short, like one user.  In this case your
locked out session has a good chance of showing up.
========================================================================

who-access.sh
-------------

The following has surely happened to other DBAs as has happened to me:
I wish to performs a task that requires exclusive access to a table eg.
an ALTER TABLE command.  I get the error message that I no exclusive
access. So I look for who is locking that table (using who-lock.sh, of
course!) and it turns up no users.  Yet, I still unable to perform that
blessed task.  What gives?

When it last happened to me, I posted the question on internet newsgroup
comp.databases.informix. The most interesting answer I received was
from Rick Bernstein, who posted his script, lock_test.sh, which looks
at the output of "onstat -g opn" to determine who has a table open. This
works even if no locks have been imposed, as when the the user is
operating under "dirty read" mode.  BTW, the exchange can be found in 
deja.com, searching for the heading:
    Bogus non-exclusive access creating a foreign key

Taking Rick up on his invitation to modify the script, I came up with
who-access.sh, which is a broad variation of Rick's original idea. The
who-access.sh script produces a subset of "onstat -g ses", listing only
those sessions that have the named table open, regardless of the lock
mode.

Here is a sample run of the command. In this example, I specify two
tables, qualified with the database name. Note also that we have
redirected stderr to /dev/null in order to suppress the "database
selected" and other messages from dbaccess.

-----------------------------------------------------------------------
$ who-access.sh imm:t_callout imm:t_changes 2>/dev/null
Sessions accessing table: imm:t_callout
session                                   #RSAM    total      used      
id       user     tty      pid   hostname threads  memory     memory    
2415     mbcam    MBJXB    -366003 mbjxb    1        2883584    1494784
2432     mbcxf    MBCXF    -325001 mbcxf    1        3530752    1514528

Sessions accessing table: imm:t_changes
session                                   #RSAM    total      used
id       user     tty      pid   hostname threads  memory     memory
3742     autoret  -        12790 wolfe    1        278528     273704
-----------------------------------------------------------------------

Note also that the slight misalignment of some columns with the column
headings is a manifestation of onstat formatting.  Once we have the
session ID's that is the only processing of outout from the command:
    onstat -g ses
and no further attempt is made to format this output.
================================= EOT =================================
@EOF
set `sum $sumopt <who-lock.txt`; if test $1 -ne 52220
then
	echo ERROR: who-lock.txt checksum is $1 should be 52220
fi
set `wc -lwc <who-lock.txt`
if test $1$2$3 != 359265317843
then
	echo ERROR: wc results of who-lock.txt are $* should be 359 2653 17843
fi

touch -m 0110114502 who-lock.txt
touch -a 0110114402 who-lock.txt
chmod 640 who-lock.txt

echo x - who-lock.sh
cat >who-lock.sh <<'@EOF'
#!/usr/bin/ksh
# who-lock.sh - Determine who has locked what rows in which tables
#
# Author: Jacob Salomon
#         JakeSalomon@netscape.net
# Version: 2.0
# Change History:
# o Release 2.0: Added parameter handling capability
# --------------------------------------------------------------------
# Parameter:
# - Names of tables in form:
#   o database:table
#   o database
#
# Outputs to stdout.
#
UNLFILE=/tmp/lock-list-$$.unl
OUTFILE=/tmp/lock-list-$$.out
parse_params()  # Function to handle the parameters.
                # Output: A WHERE clause
{
  LTOP=$#       # Parameter count
  LC=1          # Initialize array & loop counter
  while [ $LC -le $LTOP ]
  do
    if (echo $1 | grep -q :)    # If it in the form of dbs:table
    then
      echo $1 | tr : " "| read DN TN    # Separate dbs from table
      WCL[$LC]='(dbsname = "'${DN}'" and tabname = "'${TN}'")'
    else
      WCL[$LC]='(dbsname = "'$1'")'
    fi
    shift
    LC=$(( $LC + 1 ))
  done
  WH="where "
  LC=1          # Restart the loop counter for new loop
  while [ $LC -le $LTOP ]
  do
    if [ $LC -gt 1 ]
    then        # If more than 1 part to the WHERE clause
      WH=${WH}" or"
    fi
    WH=${WH}" "${WCL[$LC]}
    LC=$(( $LC + 1 ))
  done
  echo $WH      # Spout to the caller
}

if [ $# -eq 0 ]
then
  WHERE_CLAUSE=""
else
  # Find out the nature of the parameter
  WHERE_CLAUSE=`parse_params $*`
  echo WHERE_CLAUSE: 1>&2
  echo $WHERE_CLAUSE 1>&2
fi
dbaccess sysmaster - <<%%
set isolation to dirty read;
select dbsname, tabname,
       hex(rowidlk) lock_row, type,
       s.username locked_by,
       l.owner, s.hostname, s.tty,
       l.waiter,
       w.username waiter_name
  from sysmaster:syslocks l,
       sysmaster:syssessions s,
       outer sysmaster:syssessions w
 where l.owner = s.sid
   and l.waiter = w.sid
 and not (    (l.dbsname = "sysmaster")
          and (l.tabname = "sysdatabases"))
union
select d.name database_name, "(database)",
       hex(rowidlk) lock_row, type,
       s.username locked_by,
       l.owner, s.hostname, s.tty,
       l.waiter,
       w.username waiter_name
  from sysmaster:syslocks l,
       sysmaster:sysdatabases d,
       sysmaster:syssessions s,
       outer sysmaster:syssessions w
 where l.owner = s.sid
   and l.waiter = w.sid
   and (    (l.dbsname = "sysmaster")
        and (l.tabname = "sysdatabases"))
   and l.rowidlk = d.rowid
 into temp t_locked_rows
;
unload to $UNLFILE
select * from t_locked_rows
 $WHERE_CLAUSE
 order by locked_by, owner, dbsname, tabname, lock_row
; 
%%
cat >$OUTFILE <<%%
Database|Table|Row-ID|LK-type|Locked-by|Session|AtHost|TTY|Waiter|Wait-Name|
--------|-----|------|-------|---------|-------|------|---|------|---------|
%%
cat $UNLFILE >>$OUTFILE
beautify-unl.sh $OUTFILE
rm $UNLFILE $OUTFILE
@EOF
set `sum $sumopt <who-lock.sh`; if test $1 -ne 48417
then
	echo ERROR: who-lock.sh checksum is $1 should be 48417
fi
set `wc -lwc <who-lock.sh`
if test $1$2$3 != 1043512778
then
	echo ERROR: wc results of who-lock.sh are $* should be 104 351 2778
fi

touch -m 1211161400 who-lock.sh
touch -a 0110114102 who-lock.sh
chmod 755 who-lock.sh

echo x - who-locks.sh
cat >who-locks.sh <<'@EOF'
#!/usr/bin/ksh
# who-locks.sh - Determine who has locked what rows in which tables
#
# Author: Jacob Salomon
#         JakeSalomon@netscape.net
# Version: 2.0
# Change History:
# o Release 2.0: Added parameter handling capability
# --------------------------------------------------------------------
# Parameter:
# - See the usage() section
#
# Outputs to stdout.
#

# Some quickie settings
YES=0
NO=1
program=$(basename $0)

#====================================================================
#DB_FILE=/tmp/$0.$$.err
#DB_FILE=$(basename $0 .sh).err
DB_FILE=/dev/null
errmsg()            # For debugging - set DB_FILE if needed
{
  echo $(date)::$*  >>$DB_FILE
}
#====================================================================
# split_string() - Split a string by a delimiter and return the string
#                  with the components quoted and still separated by
#                  the same delimiter.
# Parameter:
#  o The delimiter
#  o The string itself.
#
split_string()
{
  in_delim=$1
  in_string=$2
  #sed_cmd="'s/${in_delim}/\"${in_delim} \"/g"
  echo $in_string |
  awk -vdlim=$in_delim '
  BEGIN {FS=dlim}
  {
    for (lc = 1; lc < NF; lc++)     # All but last component
      printf("\"%s\"%c ", $lc, FS)  # Output one component
    printf("\"%s\"\n", $lc)         # Output last component, terminate
  }
  '
}
#====================================================================
#
usage()         # For -H option: Provide soem help text
{
cat <<%%
$program 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:
$program [-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 $program 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.
%%
}
#
# Parse_params() - Analyze the options and parameters list
#
parse_params()  # Function to handle the parameters.
                # Output: A WHERE clause
{
  EINVAL=22     # Errno for invalid argument 
  # Set flags for the parameter settings
  #
  help_flag=$NO
  dblock_flag=$NO
  wait_only_flag=$NO
  DBS_LIST=""   # No database parameters yet
  UDB_LIST=""   # Compile a blank-delimited list of databases
  dbs_list_count=0
  TBL_LIST=""   # No tables specified yet
  tbl_list_count=0
  USR_LIST=""   # No users yet
  usr_list_count=0
  PID_LIST=""   # Nor pids
  pid_list_count=0
  HOS_LIST=""   # Nor hosts
  hos_list_count=0
  WTR_LIST=""   # Nor waiters
  wtr_list_count=0

  # Note: the count variable above are not to count the actual number
  # of items in each list. They are simply constructs to count how
  # often an option of each type is used.  Their purpose is to decide
  # on placement of commas in each list.
  #
  opt_string="h:d:t:u:p:w:DHW"
  while getopts $opt_string cparm
  do
    errmsg Option $cparm with param: "$OPTARG" and OPTIND: $OPTIND
    case "$cparm" in
    H)
      help_flag=$YES
      break             # Stop now and handle this outside the loop
      ;;
    d)
      if [ "$DBS_LIST" = "" ]
      then
        DBS_LIST="("    # Initialize parenthesized list if necessary
      fi
      if [ $dbs_list_count -gt 0 ]
      then                          # If this is not first item on list
        DBS_LIST=${DBS_LIST}", "    # append comma to previous item
      fi
                                    # Now append argument[s] to list
      DBS_LIST=${DBS_LIST}$(split_string , ${OPTARG})
      UDB_LIST=${UDB_LIST}" $(echo $OPTARG | tr , ' ')"
      dbs_list_count=$(($dbs_list_count + 1))   # Bump count to avoid
                                                # unnecessary comma
      ;;
#
# Parse_params() - Continued
#
    t)
      if [ "$TBL_LIST" = "" ]
      then
        TBL_LIST="("    # Initialize parenthesized list if necessary
      fi
      if [ $tbl_list_count -gt 0 ]
      then                          # If this is not first item on list
        TBL_LIST=${TBL_LIST}", "    # append comma to previous item
      fi
                                    # Now append argument[s] to list
      TBL_LIST=${TBL_LIST}$(split_string , ${OPTARG})
      tbl_list_count=$(($tbl_list_count + 1))   # Bump count to avoid
                                                # unnecessary comma
      ;;
    u)
      if [ "$USR_LIST" = "" ]
      then
        USR_LIST="("    # Initialize parenthesized list if necessary
      fi
      if [ $usr_list_count -gt 0 ]
      then                          # If this is not first item on list
        USR_LIST=${USR_LIST}", "    # append comma to previous item
      fi
                                    # Now append argument[s] to list
      USR_LIST=${USR_LIST}$(split_string , ${OPTARG})
      usr_list_count=$(($usr_list_count + 1))   # Bump count to avoid
                                                # unnecessary comma
      ;;
    h)
      if [ "$HOS_LIST" = "" ]
      then
        HOS_LIST="("    # Initialize parenthesized list if necessary
      fi
      if [ $hos_list_count -gt 0 ]
      then                          # If this is not first item on list
        HOS_LIST=${HOS_LIST}", "    # append comma to previous item
      fi
                                    # Now append argument[s] to list
      HOS_LIST=${HOS_LIST}$(split_string , ${OPTARG})
      hos_list_count=$(($hos_list_count + 1))   # Bump count to avoid
                                                # unnecessary comma
      ;;
    p)
      if [ "$PID_LIST" = "" ]
      then
        PID_LIST="("    # Initialize parenthesized list if necessary
      fi
      if [ $pid_list_count -gt 0 ]
      then                          # If this is not first item on list
        PID_LIST=${PID_LIST}", "    # append comma to previous item
      fi
                                    # Now append argument[s] to list
      PID_LIST=${PID_LIST}$(split_string , ${OPTARG})
      pid_list_count=$(($pid_list_count + 1))   # Bump count to avoid
                                                # unnecessary comma
      ;;
#
# Parse_params() - Continued
#
    w)
      if [ "$WTR_LIST" = "" ]
      then
        WTR_LIST="("    # Initialize parenthesized list if necessary
      fi
      if [ $wtr_list_count -gt 0 ]
      then                          # If this is not first item on list
        WTR_LIST=${WTR_LIST}", "    # append comma to previous item
      fi
                                    # Now append argument[s] to list
      WTR_LIST=${WTR_LIST}$(split_string , ${OPTARG})
      wtr_list_count=$(($wtr_list_count + 1))   # Bump count to avoid
                                                # unnecessary comma
      ;;
    D)
      dblock_flag=$YES      # Include database locks in the output
      ;;
    W)
      wait_only_flag=$YES   # Display only locks that have waiters
      ;;
    *)
      echo Unrecognized option: $cparm
      usage
      exit $EINVAL
      ;;
    esac
  done

  # Finished parsing parameters and creating list, except one detail:
  # Now, about that possibly unfinished help_flag business..
  #
  if [ $help_flag -eq $YES ]
  then
    usage
    exit 0
  fi

  # About closing those lists.. Close only a list that has items.
  #
  if [ $dbs_list_count -gt 0 ]
  then
    DBS_LIST=${DBS_LIST}")"
  fi

  if [ $tbl_list_count -gt 0 ]
  then
    TBL_LIST=${TBL_LIST}")"
  fi

  if [ $usr_list_count -gt 0 ]
  then
    USR_LIST=${USR_LIST}")"
  fi
#
# Parse_params() - Continued
#
  if [ $hos_list_count -gt 0 ]
  then
    HOS_LIST=${HOS_LIST}")"
  fi

  if [ $pid_list_count -gt 0 ]
  then
    PID_LIST=${PID_LIST}")"
  fi

  if [ $wtr_list_count -gt 0 ]
  then
    WTR_LIST=${WTR_LIST}")"
  fi
}   # End function parse_params()
#
# Process_params() - Build SQL statement fragments based on the
                   # parameters analyzed in parse_params()
# Start building parts of the WHERE clause based on the flags and
# lists already set in parse_params()
#
# Help keep count of clauses in the final SQL statement that extracts
# the data the user wants.
#
process_params()
{
  clause_count=0    # This will help with placement of WHERE and AND
                    # parts of the WHERE clause.

  #* Does the user want to see database locks also? Normally, this data
  #  constitutes a waste of screen and eyeball space.
  #
  if [ $dblock_flag -eq $YES ]
  then
    DBLOCK_CLAUSE=""        # If user wants it, don't skip it
  else
    DBLOCK_CLAUSE="(tabname != \"(database)\")" # Default - omit these
    clause_count=$(($clause_count + 1))         # Bump up clause count
    clause_list[$clause_count]=$DBLOCK_CLAUSE   # Save clause in list
  fi

  #* Does the user want to view locks regardeless of waiting status of
  #  only those locks with someone waiting on them?
  #
  WAIT_ONLY_CLAUSE=""       # Default - whether/not there is a waiter
  if [ $wait_only_flag -eq $YES ]
  then                      # User wants to see only waited-on locks
    #WAIT_ONLY_CLAUSE="(trim(waiter_name) is not null)"
    WAIT_ONLY_CLAUSE="(waiter_name is not null)"
    clause_count=$(($clause_count + 1))         # Bump up clause count
    clause_list[$clause_count]=$WAIT_ONLY_CLAUSE # Save clause in list
  fi

  #* Did user specify databases(s) to scan for locks?
  #
  if [ $dbs_list_count -eq 0 ]
  then                      # (Empty database list)
    DBS_CLAUSE=""           # No: Default: all databases
  else
    DBS_CLAUSE="(database in "${DBS_LIST}")"
    clause_count=$(($clause_count + 1))         # Bump up clause count
    clause_list[$clause_count]=$DBS_CLAUSE      # Save clause in list
  fi

  #* Did user specify table(s) to scan for locks?
  #
  if [ $tbl_list_count -eq 0 ]
  then                      # (Empty table list)
    TBL_CLAUSE=""           # No: Default: all tables
  else
    TBL_CLAUSE="(tabname in "${TBL_LIST}")"
    clause_count=$(($clause_count + 1))         # Bump up clause count
    clause_list[$clause_count]=$TBL_CLAUSE      # Save clause in list
  fi
#
# Process_params (Continued)

  #* Did user specify user(s) to scan for locks?
  #
  if [ $usr_list_count -eq 0 ]
  then
    USR_CLAUSE=""           # No: Default: all users
  else
    USR_CLAUSE="(locker_name in "${USR_LIST}")"
    clause_count=$(($clause_count + 1))         # Bump up clause count
    clause_list[$clause_count]=$USR_CLAUSE      # Save clause in list
  fi

  #* Did user specify PID(s) to scan for locks?
  #
  if [ $pid_list_count -eq 0 ]
  then
    PID_CLAUSE=""           # No: Default: all front-end process-id's
  else
    PID_CLAUSE="(locker_pid in "${PID_LIST}")"
    clause_count=$(($clause_count + 1))         # Bump up clause count
    clause_list[$clause_count]=$PID_CLAUSE      # Save clause in list
  fi

  #* Did user specify host(s) to scan for locks?
  #
  if [ $hos_list_count -eq 0 ]
  then
    HOS_CLAUSE=""           # No: Default: all hosts
  else
    HOS_CLAUSE="(locker_host in "${HOS_LIST}")"
    clause_count=$(($clause_count + 1))         # Bump up clause count
    clause_list[$clause_count]=$HOS_CLAUSE      # Save clause in list
  fi

  #* Did user specify waiter name(s) to scan for locks?
  #
  if [ $wtr_list_count -eq 0 ]
  then
    WTR_CLAUSE=""           # No: Default: all waiters
  else
    WTR_CLAUSE="(waiter_name in "${WTR_LIST}")"
    clause_count=$(($clause_count + 1))         # Bump up clause count
    clause_list[$clause_count]=$WTR_CLAUSE      # Save clause in list
  fi

  # (Whew!) I now have all possible clauses to pull into the SQL
  # command (with the UNLOAD prefix). They need WHERE and AND prefixes,
  # depending on their position in the above array.  This will be done
  # by shell function where_clause(), when required.
  #
}   # End Function: process_params()
#
# where_clause() - Emit all the entries in the array where_clauses[]
#                  but prepend:
#                  WHERE to the first clause, 
#                  AND to all other clauses
# Parameters: Implicit, using the following variables:
#  o clause_list[]  Tha array holding the clauses
#  o clause_count   The number of clauses that were generated
#
where_clause()
{
  LC=1                      # Loop counter
  while [ $LC -le $clause_count ]
  do
    if [ $LC -eq 1 ]
    then
      wh_prefix="WHERE "    # WHERE for first subclause in WHERE clause
    else
      wh_prefix="  AND "    # AND for all subsequent subclauses
    fi
    echo $wh_prefix ${clause_list[$LC]}
    LC=$(( $LC + 1 ))
  done
}
#
# Main execution starts here
#
#echo "=================================================" >>${DB_FILE}
UNLFILE=/tmp/lock-list-$$.unl
OUTFILE=/tmp/lock-list-$$.out
PTNFILE=/tmp/partition-list-$$.unl
PTNERR=/tmp/partition-list-$$.err

parse_params $*                 # Check parameters for validity
process_params                  # Set up canonical parameter lists

# Get basic partition info into a file
#
partition-list.sh $UDB_LIST >$PTNFILE 2>$PTNERR
# Note above: If no database parameters were supplied, then $UDB_LIST
# will be a null string and is ignored in the above command.

# Begin an in-line SQL script as manipulated by environment variables
# that were set according to command-line parameters.
#
dbaccess sysmaster - <<%% 2>>${PTNERR}
set isolation to dirty read;

create temp table partition_list
(
  dbsname       char(18),
  tabname       char(18),
  tabid         integer,
  partnum       integer,
  partn_type    char(1),
  index_name    char(18)
) with no log ;

load from $PTNFILE
  insert into partition_list ;

-- The following SQL is a slightly expanded version of the SQL that
-- forms the basis of the view sysmaster:syslocks, combined with the
-- basic parition information derived from partition-list.sh . The data
-- is selected into a temp table rather than derived as a view.
--
select  pl.dbsname      database,
        pl.tabname      tabname,
        pl.tabid        tabid,
        pl.partnum      partnum,
        pl.partn_type   fragtype,
        pl.index_name   indexname,
        lk.rowidr       rowidlk,
        ft.txt[1,4]     type,
        tc.sid          owner,
        wtc.sid         waiter,
        lk.grtime
  from  sysmaster:syslcktab      lk,
        partition_list           pl,
        sysmaster:systxptab      tx,
        sysmaster:sysrstcb       tc,
        sysmaster:flags_text     ft,
        outer sysmaster:sysrstcb wtc
 where lk.partnum = pl.partnum
   and lk.owner   = tx.address
   and tx.owner   = tc.address
   and lk.wtlist  = wtc.address
   and ft.tabname = 'syslcktab'
   and ft.flags   = lk.type
  into temp schlocks with no log
;
--
-- The following unioned query combines the "syslocks" information
-- from the above temp table with session information.
--
select  l.database,
        l.tabname,
        l.tabid,
        l.partnum,
        l.fragtype,
        l.indexname,
        l.rowidlk,
        l.type,
        s.username      locker_name,
        l.owner         locker_session,
        s.hostname      locker_host,
        s.tty           locker_tty,
        s.pid           locker_pid,
        l.waiter        waiter_session,
        w.username      waiter_name,
        w.hostname      waiter_host,
        w.tty           waiter_tty,
        w.pid           waiter_pid,
        l.grtime
  from  schlocks                    l,
        sysmaster:syssessions       s,
        outer sysmaster:syssessions w
 where  l.owner = s.sid         -- Match up session id of lock/session
   and  l.waiter = w.sid        -- Match up waiter/other session
   and not (    (l.database = "sysmaster")
            and (l.tabname  = "sysdatabases"))  -- Not database locks
union
select  d.name database,
        "(database)",   -- Distinguishing data for database locks
        l.tabid,
        l.partnum,
        l.fragtype,
        l.indexname,
        l.rowidlk,
        l.type,
        s.username      locker_name,
        l.owner         locker_session,
        s.hostname      locker_host,
        s.tty           locker_tty,
        s.pid           locker_pid,
        l.waiter        waiter_session,
        w.username      waiter_name,
        w.hostname      waiter_host,
        w.tty           waiter_tty,
        w.pid           waiter_pid,
        l.grtime
  from schlocks                     l,
       sysmaster:sysdatabases       d,
       sysmaster:syssessions        s,
       outer sysmaster:syssessions  w
 where l.owner = s.sid
   and l.waiter = w.sid
   and (    (l.database = "sysmaster")
        and (l.tabname = "sysdatabases"))
   and l.rowidlk = d.rowid
  into temp t_locked_rows with no log
;

-- This final phase of the query retrieves the useful data in a
-- suitable form - hex where appropriate, the lock duration rather than
-- the lock-grant time.
--
unload to $UNLFILE
select  trim(database) || ":" || trim(tabname) tablname,
        indexname,
--      hex(partnum)  partition,
        hex(rowidlk)  lock_row,
        type,
        locker_session,
        locker_name,
        locker_host,
        locker_tty,
        locker_pid,
        waiter_session,
        waiter_name,
        waiter_host,
--      waiter_tty,
       (current day to fraction
        - extend(dbinfo('UTC_TO_DATETIME',grtime), day to fraction))
                      lock_life
  from  t_locked_rows
  $(where_clause)
  order by --database,
           --tabname,
           tablname,
           locker_name,
           locker_session,
           lock_life desc
;
%%
#
#
# Set up column headings for all that data:
#
HEAD1="|"                   # Start headings with a vertical bar
HEAD2="|"

# Note for hacks: If you want the partition number in here, 
#  1. Use the first "for" starter below and comment out the second.
#  2. Uncomment the hex(partnum) in above final query.
#
#for COL_HEAD in Table Index Partition Row-ID \
for COL_HEAD in Table Index Row-ID \
                LKTY Sess Locked-by AtHost TTY PID \
                Waiter WName WHost \
                Lock-Life
do
  HEAD1=${HEAD1}${COL_HEAD}"|"
  HEAD2=${HEAD2}$(echo $COL_HEAD | sed 's/[a-zA-Z]/-/g')"|"
done
cat >$OUTFILE <<%%
${HEAD1}
%%
# Now, output all our retrieved data to the output file
#
sed 's/^/|/' $UNLFILE >>$OUTFILE

# Finally, present it to user and delete the files.
#
beautify-unl.sh $OUTFILE
rm $UNLFILE $OUTFILE $PTNFILE

# Now, were there any errors in all the above processing?
#
grep -q [0-9]: $PTNERR      # Just find IF there was an error message
found_err=$?                # Grab copy of the exit code
if [ $found_err -eq $YES ]  # If an error message appears in the file
then                        # refer the user to the error file
  echo 1>&2 Warning: Database access errors detected
  echo 1>&2 See file $PTNERR
else
  rm $PTNERR
fi
#--------------------------------------------------------------------
@EOF
set `sum $sumopt <who-locks.sh`; if test $1 -ne 13175
then
	echo ERROR: who-locks.sh checksum is $1 should be 13175
fi
set `wc -lwc <who-locks.sh`
if test $1$2$3 != 638261521195
then
	echo ERROR: wc results of who-locks.sh are $* should be 638 2615 21195
fi

touch -m 0109124202 who-locks.sh
touch -a 0110113902 who-locks.sh
chmod 755 who-locks.sh

echo x - who-access.sh
cat >who-access.sh <<'@EOF'
#!/usr/bin/ksh
# who-access.sh - Find out who is accessing a specified table.
#
# Author: Jacob Salomon
# Date:   07/15/1999
# Credits: Original idea from a script by:
#          Rick Bernstein, <rbernste@alarismed.com>
#
# This script fills in an inadequecy in who-lock.sh: who-lock.sh can
# only detect a user's presence on a table by detecting a lock. A user
# operating in dirty read does not always leave locks; yet there is
# enough access to prevent operations like "alter table" that require
# exclusiove access.  This script corrects the oversight by using the
# output of onstat -g opn to search for threads that have the table
# open.
#
# Parameters:
#  - database:table [database:table ...]
# Displays:
#  - List of users accessing each table.
#--------------------------------------------------------------------
# getpartnum()
# Shell function to translate a datbase/table name to a partition
# number. Bear in mind that fragmented tables will yield multiple
# partition numbers - one for each fragment in a separate dbspace
# Parameters:
# - Qualified table name in form: database:table
#
getpartnum()
{
  #echo getpartnum $1
  echo $1 | tr : " " | read DBN TBN
  #echo $DBN : $TBN
  if [ "$DBN" = "" ] || [ "$TBN" = "" ]
  then
    echo Usage: $0 database:table
    exit 3
  fi
  SQL="select hex(partnum) from systabnames"
  SQL=${SQL}" where dbsname = '$DBN' and tabname = '$TBN' ;"
  #echo $SQL 1>&2
  PARTN=$(
    dbaccess sysmaster - <<%%
    output to pipe "cat " without headings
    $SQL
%%
  )
  # Partnum comes out with upper-case letters in hex. Lower the case:
  #
  if [ "$PARTN" ]           # If I got a value back from dbaccess
  then
    LCPARTN=$(echo $PARTN | tr A-F a-f)
  else
    LCPARTN="zilch"     # Make sure there is a value
  fi
  echo $LCPARTN
}
#
# show_tab_users()
#   Shell function to display all sessions that have the given table
#   open, even if no locks have been imposed.
# Parameter: 1 qualified table name
#
show_tab_users()
{
  dbstabname=$1
  set $(getpartnum $dbstabname)      # Set $* to list of partnums
  #echo $*
  if [ $1 = "zilch" ]       # If no partitions were returned
  then
    echo No partitions correspond to qualified name: $dbstabname 1>&2
    exit 2                  # File not found
  fi
  
  # Now use these partition numbers to filter the output of
  #   onstat -g opn
  # but before I can do that I need to insert -e before each one.
  #
  grepcmd="grep"
  while [ $# -gt 0 ]
  do
    grepcmd=${grepcmd}" -e $1"
    shift
  done
  #echo $grepcmd
  
  # Now here's the plan: I am doing all this with the intention of
  # getting at the thread ID's that are accessing the partition[s]
  # I will use each TID to get at its corresponding session id.
  # But more on that later.
  #
  n_threads=0               # Initialize these values for later testing.
  n_sessions=0
  tidlist=$(onstat -g opn | $grepcmd | cut -d' ' -f1|sort|uniq)
  if [ "$tidlist" != "" ]
  then                      # If I got something from that pipeline
    #echo tidlist: $tidlist
    set $(echo $tidlist)    # set up to make a grep command of results
    n_threads=$#            # Count number of threads we just got above
  fi                        # (If no result, $n_threads stays 0)
#
  if [ $n_threads -gt 0 ]
  then
    inlist="("              # Start piecing together WHERE clause
    while [ $# -gt 1 ]      # Down to but not yet including last entry
    do
      inlist=${inlist}$1,   # Append comma separator
      shift
    done
    inlist=${inlist}$1")"   # After last entry: no comma; close paren
    
    SQL2="select us_sid from sysuserthreads"
    SQL2=${SQL2}" where us_tid in $inlist ;"
    #echo $SQL2
    session_list=$(
      dbaccess sysmaster - <<%%
      output to pipe "cat " without headings
      $SQL2
%%
    )
    #echo $session_list
    
    if [ "$session_list" != "" ]    # Got something from that pipeline?
    then                            # set up for grep command of results
      set $(echo $session_list)     # Count sessions we just got above
      n_sessions=$#                 # (No result => $n_sessions stays 0)
    fi
  fi
  
  # Now form a new grep command to scan for the session-id's
  #
  if [ $n_threads -gt 0 ] && [ $n_sessions -gt 0 ]
  then
    grep_cmd="grep"
    while [ $# -gt 0 ]
    do
      grep_cmd=${grep_cmd}" -e $1"
      shift
    done
    echo Sessions accessing table: $dbstabname
    onstat -g ses|head -5|tail -2   # First output heading lines
    onstat -g ses|$grep_cmd|/usr/bin/sort +1 -2 +0 -1 -n
    # That is: Sort first on user name, then on the session-id
  else
    echo No users on database:table: $dbstabname
  fi
  echo ""               # Output a blank line
}
#
# Actual work of the script:
# Translate the database:table name to something fit for SQL
while [ $# -gt 0 ]
do
  qualtab=$1
  shift
  show_tab_users $qualtab       # Show user sessions on that table
done
@EOF
set `sum $sumopt <who-access.sh`; if test $1 -ne 55973
then
	echo ERROR: who-access.sh checksum is $1 should be 55973
fi
set `wc -lwc <who-access.sh`
if test $1$2$3 != 1577714903
then
	echo ERROR: wc results of who-access.sh are $* should be 157 771 4903
fi

touch -m 1211161400 who-access.sh
touch -a 0110114102 who-access.sh
chmod 755 who-access.sh

exit 0