A classic version of shell scripts for solving deadlocks in DB2 databases!

The following articles mainly describe the shell script for solving deadlocks in the DB2 database in linux/unix, A detailed description of the shell script used to unlock a deadlock in the DB2 database. I hope it will be helpful in your future studies.

Clear;

 
 

  
  
DB2 "connect to db user dbuser using dbpass"  
  
  
DB2 "update monitor switches using lock on table on"  
  
  
tmpf=`echo "$HOME/tmp/""$RANDOM"_"$SECONDS"".lock"`  
  
  
DB2 get snapshot for locks on abbs>$tmpf  
  
  
handles=`cat $tmpf|awk '{if($2=="handle")print $4}'`  
  
  
x=0;  
  
  
y=0;  
  
  
deadhandles=" " 
  
  
for i in $handles  
  
  
do  
  
  
 
 
 

Obtains the row where the current id is located.

 
 

  
  
ibeg=`cat $tmpf|awk '{if($2=="handle" && $4=="'$i'")print NR}'`;  
  
  
#echo "Line[15]:handle:$i,beg:$ibeg" 
 
 

Retrieve content after the current row

 
 

  
  
cat $tmpf|awk '{if(NR-1>'$ibeg')printf("%s\n", $0);}'>$tmpf.$i; 
 
 

Obtains the row where the next handle is located.

 
 

  
  
hangdleaf=`cat $tmpf.$i|awk '{if($2=="handle")print NR}'`  
  
  
#echo Line[20]:$hangdleaf  
  
  
if [ -z "$hangdleaf" ];then  
  
  
cat $tmpf.$i>$tmpf.$i.aft;  
  
  
else 
 
 

Take the next handle

 
 

  
  
itmp="";  
  
  
for j in $hangdleaf  
  
  
do  
  
  
if [ -z "$itmp" ];then  
  
  
handlenext=$j;  
  
  
itmp="get done!" 
  
  
fi  
  
  
done  
  
  
#echo Line[33]:$handlenext;  
  
  
#cat $tmpf.$i|awk '{if(NR<'$handlenext')print $0;}'  
  
  
cat $tmpf.$i|awk '{if(NR<'$handlenext')print $0;}'>$tmpf.$i.aft;  
  
  
fi 
 
 

Determine whether the handle lock exists.

 
 

  
  
Ifx = 'cat $ tmpf. $ I. aft | awk '{if ($1 = "Mode" & ($3 = "X" | $3 = "IX ")) | ($2 = "mode" & substr ($6, 2, 1) = "X") print $0 ;}''
  
  
# Echo Line [39]: handle [$ I], $ ifx
  
  
If [-z "$ ifx"]; then
  
  
A = 1
  
  
Else
  
  
Deadhandles = "$ deadhandles" "$ I"
  
  
X = 'expr $ x + 1 ';
  
  
Echo $ I
  
  
Fi
  
  
# Cat $ tmpf. $ I. aft | grep X | more
  
  
# Echo Line [48]: $ deadhandles
  
  
# Read yn
  
  
Rm-f $ tmpf. $ I
  
  
Rm-f $ tmpf. $ I. aft
  
  
Done
  
  
# Echo "deadlock $ x: $ deadhandles ";
  
  
# Read yn
  
  
Clear
  
  
If [-z "$ deadhandles"] | ["$ deadhandles" = ""]; then
  
  
Echo "no deadlock, quit ......";
  
  
Read yn;
  
  
Exit 0;
  
  
Fi
  
  
Echo "Lock handle: $ deadhandles, unlock? Y/N \ c"
  
  
Read yn;
  
  
If [-z "$ yn"]; then
  
  
Echo "Give up unlocking! "
  
  
Exit 0;
  
  
Fi
  
  
If ["$ yn" = "Y"] | ["$ yn" = "y"]; then
  
  
X = 0;
  
  
For I in $ deadhandles
  
  
Do
  
  
Echo deadlock solution: $ I
  
  
DB2 "force applications ($ I )"
  
  
X = 'expr $ x + 1'
  
  
Done
  
  
Echo "unlock [$ x] locks ...."
  
  
Else
  
  
Echo "Give up unlocking! "
  
  
Exit 0;
  
  
Fi
  
  
DB2 connect reset;
  
  
DB2 terminate;
  
  
Rm-f $ tmpf
  
  
Echo "unlock terminal:">/tmp/tty. tmp
  
  
Tty>/tmp/tty. tmp
  
  
Wall </tmp/tty. tmp
  
  
Rm-f/tmp/tty. tmp
 
 

The above content is an introduction to the shell script for resolving deadlocks in the DB2 database in linux/unix. I hope you will get some benefits.