The Performance and Tuning Doctor by Sturnus

It displays in a similar format to the "top" UNIX utility, and will refresh periodically. Processes are sorted by priority:

• Those being blocked
• Those blocking other processes
• User processes
• Internal threads

It has been tested on a 15.0 server, but will probably work OK on earlier versions - you may need to change or remove some of the columns from the query if it doesn't. Some columns have been truncated as they are quite long, and you may have to do some more truncation or remove columns if it doesn't all display nicely on your screen.

#!/bin/sh
#
# sybtop
#
# This script displays the state of processes on a Sybase ASE server, using a
# "top"-like format.
#
# This script is originally copyright (c) 2008, JL. It may be copied and used freely, and
# modified anyway you wish, as long as this original copyright notice remains intact.
#
# www.sturnus.co.uk
#
#
# Modification History
# Date        Who  What
# ----------  ---  ---
# 2008-10-16  JL   First written.
#
#

# Uncomment for debugging purposes
#set -x


##### Set these according to your environment #####

export SYBSERVER="TEST"    # The name of the Sybase server to which you are connecting
                           # (in your interfaces file)
export SYBLOGIN="sa"       # The server login name
export SYBPWFILE=~/.sapw   # The path of a file containing the login password
export DISPLAYLINES=15     # The number of lines that you wish to be displayed on the
                           # screen at any time
export REFRESHINTERVAL=5   # How long to wait between refreshes, in seconds


##### Functions #####

signal_handler()
  {
  # Just clean up the temporary file, but don't bother generating
  # an error if it doesn't exist.
  rm -f $TMPFILEPATH

  echo "$0 caught signal $1 - exiting"

  exit 0
  }


##### Start #####

# Generate a temporary filename. This is used by this script to store
# the results of the SQL query.
export TMPFILEPATH="/tmp/sybtop$$"

# Trap various signals, so that if the script exits before we have had a chance
# to remove the temporary file, it can be cleaned up.
trap "signal_handler SIGHUP"  SIGHUP
trap "signal_handler SIGINT"  SIGINT
trap "signal_handler SIGKILL" SIGKILL
trap "signal_handler SIGTERM" SIGTERM


# Loop forever, or until the script is killed (e.g. with Control-C)
while [ 1 ]
do

  #
  # Run the query, sending the result to the temporary file. We do this to
  # double-buffer the output, so the screen updates quickly even if the query
  # takes a while to run or it is blocked.
  #
  # The strange construct with the sub-shell is so that we can:
  #   - Pipe the password through to "isql" without it appearing on the
  #     screen, even if debugging is enabled (with "set -x").
  #   - Use the "<< END_OF_SQL" construct (and thus show the query nicely
  #     formatted over several lines).
  #

  ( cat $SYBPWFILE; cat << END_OF_SQL

SELECT "SPID"        = spid,
       "Login"       = convert( char(10), isnull( suser_name( suid ), "" ) ),
       "Database"    = convert( char(10), db_name( dbid ) ),
       "Command"     = convert( char(20), cmd ),
       "Status"      = convert( char(10), status ),
       "CPU"         = cpu,
       "I/O"         = physical_io,
       "Blocked"     = CASE
                         WHEN blocked > 0
                           THEN convert( char(5), blocked ) + " : " +
                                convert( varchar(5), time_blocked ) + " s"
                         ELSE ""
                       END,
       "Blocking"    = isnull( convert( char(5),
                                        nullif( ( SELECT count(*)
                                                    FROM master..sysprocesses p2
                                                   WHERE p2.blocked = p1.spid ), 0 ) ), "" ),
       "Program"     = convert( char(10), isnull( program_name, "" ) ),
       "Client App"  = convert( char(10), clientapplname ),
       "IP"          = convert( char(15), isnull( ipaddr, "" ) )
  FROM master..sysprocesses p1
 ORDER BY CASE
            WHEN blocked > 0
              THEN 0
            ELSE 1
          END,
          time_blocked DESC,
          9            DESC,
          CASE
            WHEN suid > 0
              THEN 0
            ELSE 1
          END,
          spid
go

END_OF_SQL
) | isql -S $SYBSERVER -U $SYBLOGIN -w 1000 \
  | grep -v "^Password:" \
  | grep -v "rows affected)$" > $TMPFILEPATH


  # Determine how many lines are in the output file
  TOTALLINES=`cat $TMPFILEPATH | wc -l`

  # Clear the screen ready to display the update
  clear


  # We only display the top DISPLAYLINES processes, but if there are more than
  # this in total then we want to know how many so we can show it on screen.

  echo "----- $0: `date +%Y-%m-%d:%H:%M:%S` -----"
  echo ""
  head -$DISPLAYLINES $TMPFILEPATH

  if [ $TOTALLINES -gt $DISPLAYLINES ]
  then

    NONDISPLAYLINES=`expr $TOTALLINES - $DISPLAYLINES`

    echo "... $NONDISPLAYLINES more lines(s) ..."

  fi


  # Remove the temporary file
  rm $TMPFILEPATH


  # Sleep for the refresh interval before re-running the query
  sleep $REFRESHINTERVAL

done


echo "Loop exited prematurely"

exit 2

Back to top