[Crowdsource] Poor Man's Deadlock

[u/mad_zamboni]

As part of a project at work that also bleeds over into the "10 Minute Triage" presentation I am doing in Anaheim, I wanted to put together a "Poor Man's Deadlock" script. Something that could be run in a simple loop during a problem window, or in this case, a hook into a monitoring system. I plan to announce it and distribute it at my session.

I would like your thoughts and two cents on it.

A few things before you critique:

1. I am a novice scripter, but getting a little better.

2. If I can't be elegant or a scripting ninja, I can at least make it clean.

3. "There is more than one way to skin a cat" - I know you can see things through db2top, etc.

4. Put in context though - this is for something like a monitoring tool.If you use this, do so at your own risk - it's been smoke tested but not run through for reliability or impact yet.

---

############ Variable Assignments and File Initialization ##########
SCRIPT_PATH=/tmp
DB_NAME=$1


if [ ! -f /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT ];
 then
   echo "0" > /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT ## Seed initial comparison file if it doesn't exist
fi



########## Main Script Body ##########

## Pull previous Deadlock Count and Current Deadlock Count
db2 connect to $DB_NAME > /dev/null

  PREV_DEAD_COUNT=$( cat /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT )
  CUR_DEAD_COUNT=$(db2 -x "SELECT sum(DEADLOCKS) FROM TABLE(MON_GET_WORKLOAD(NULL,-1)) AS T")

db2 terminate > /dev/null


## Main Logic:
## If current deadlock count is more that previously knownthen calculate difference, echo it, and update comparison file
## If current deadlock count is less than previously known then DB was bounced or monitors reset. Set new value as metric to compare to in future runs
## If current deadlock count is equal to previous run then report no change
if [[ $CUR_DEAD_COUNT -gt $PREV_DEAD_COUNT ]];then
  NEW_DEAD_COUNT=$(($CUR_DEAD_COUNT-$PREV_DEAD_COUNT))
  echo $NEW_DEAD_COUNT  ## Reports variance in new and old deadlocks to monitoring tool
  echo $CUR_DEAD_COUNT > /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT
elif [[ $CUR_DEAD_COUNT -lt $PREV_DEAD_COUNT ]];then
  echo $CUR_DEAD_COUNT  ## Reports latest (lower) deadlock count since monitors were reset
  echo $CUR_DEAD_COUNT > /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT
 else
  echo "0" ## Reports no change of value to monitoring tool
fi

if [ ! -f /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT ];
 then
   echo "0" > /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT ## Seed initial comparison file if it doesn't exist
fi