几个DB2数据库常用的脚本

本人经常使用的DB2的脚本

1.Forces all applications connected to the database,
#!/bin/ksh
#
#
# Usage :
# Forces all applications connected to the database,

set -x
dbname=$1
if [ "$dbname" = "" ]
then
echo "Usage : " $0 ""
exit
fi

listcmd="db2 list applications for database $dbname"

while true
do
$listcmd
if [ $? -eq 2 ]
then
db2 deactivate database $dbname
exit
fi
$listcmd | tail +5 | nawk '{print $3}' | while read applid
do
if [ "$applid" != "" ]
then
db2 "force application($applid)"
fi
done
sleep 5
done

2.list tablespaces useage

#!/bin/ksh
#
#
# Usage : db2_tspace.ksh -d -t -h -u -p
dbname=""
thres=90
noheaders=0
userid=""
password=""

syntax()
{
echo " "
echo " db2_tspace.ksh -d -t -h -u -p "
echo " "
echo " -d Database Name. Mandatory"
echo " -t Threshold Percentage "
echo " -h Do not print headers and footers . Optional. Default - Prints headers and footer"
echo " -u Userid used to connect. Optional."
echo " -p Password for the userid. Mandatory if -u is specified."
echo " "
}

while getopts "d:t:hu:p:" inopt
do
case $inopt in
d) dbname=$OPTARG ;;
t) thres=$OPTARG ;;
h) noheaders=1 ;;
u) userid=$OPTARG ;;
p) password=$OPTARG ;;
esac
done

if [ -z "$dbname" ] # Return error if dbname is null
then
syntax
return 12
exit
fi

if [ -z "$userid" ] && [ -z "$password" ] # Choosing the connection statement
then
db2 connect to $dbname > /dev/null
else
db2 connect to $dbname user $userid using $password > /dev/null
fi

#db2 connect to $dbname > /dev/null


if [ $? -ne 0 ]
then
echo "Error when connecting to database $dbname"
return 12
exit
fi

db2 list tablespaces > /dev/null # Check if list tablespaces command is successful

if [ $? -ne 0 ]
then
echo "Error Occured when listing tablespaces"
return 12
exit
fi

db2 list tablespaces show detail | awk -v tpers=$thres -v dbn=$dbname -v nohead=$noheaders '

BEGIN {

i=0 # Reset Counter
StateWarning=0 # Reset tablespace warning flag
warnts=0 # Reset Number of non-Normal Tablespaces
# Print Headers
if (nohead==0)
{
print
print " Database : " dbn
print
print "Threshold % : " tpers "n"

printf("%3s %-20s %-4s %6s %12s %12s %7sn","ID","Tablespace","Type","PgSize","Pages Alloc","Pages Used","%Used")
printf("%3s %-20s %-4s %6s %12s %12s %7sn","--","----------","----","------","-----------","----------","-----")
}
}

{
if ($1=="Tablespace" && $2=="ID")
{
tsid[i] = $NF # Tablespace ID
}


if ($1=="Name" && $2=="=")
{
tsname[i] = $NF # Tablespace Name
}


if ($1=="Type" && $2=="=")
{
if ($3=="Database")
{
tstype[i] = "DMS" # Database Type
}
else tstype[i]="SMS"
}


if ($1=="State" && $2=="=")
{
if ($NF!="0x0000") # Database State
{
StateWarning=1
warnts=warnts+1
}
}


if ($1=="Total" && $2=="pages")
{
tsalloc_pages[i] = $NF # Allocated Pages
}


if ($1=="Used" && $2=="pages")
{
tsused_pages[i] = $NF # Used pages
}


if ($1=="Page" && $2=="size")
{
tspage_size[i] = $NF # Get pagesize and calculate values

# Allocation - MB
tsalloc_mb[i] = tsalloc_pages[i] * (tspage_size[i]/1024/1024)

# Usage - MB
tsused_mb[i] = tsused_pages[i] * (tspage_size[i]/1024/1024)

# Free - MB
tsfree_mb[i] = tsalloc_kb[i] - tsused_kb[i]

# Used Percentage
tsused_pc[i] = tsused_mb[i]*100/tsalloc_mb[i]
if (tsused_pc[i] > tpers && tstype[i]=="DMS" )
{
tsthres[i] = "<}
else tsthres[i] = " "

# Used - MB
tsused=tsused+tsused_mb[i]

# Allocated - MB
tsalloc=tsalloc+tsalloc_mb[i]

i++ # Increment Counter
}

}

END {
for (j=0;j{
printf("%3d %-20s %4s %6d %12d %12d %7.2f %2sn",tsid[j],tsname[j],tstype[j],tspage_size[j],tsalloc_pages[j],tsused_pages[j],tsused_pc[j],tsthres[j])
}
if (nohead==0)
{
print
print
print "Total Allocated (MB) : " tsalloc
print "Total Used (MB) : " tsused
print
printf("Percentage Used : %5.2f %1sn ",(tsused/tsalloc)*100,"%")
print
if (StateWarning==1)
{
print "WARNING : " warnts " TABLESPACE(S) NOT IN NORMAL STATE"
}
print "PS:"
print "1. SMS Tablespace Usage is always 100%. For this reason, the threshold percentage is not applied for SMS."
}
}'

3.Lists the tablespaces and their information

#!/bin/ksh
#
#
# Usage : db2_tspace_cont.ksh -d -h -u -p

dbname=""
noheaders=0
userid=""
password=""

syntax()
{
echo " "
echo " db2_tspace_cont.ksh -d -h -u -p "
echo " "
echo " -d Database Name. Mandatory"
echo " -h Do not print headers and footers . Optional. Default - Prints headers and footer"
echo " -u Userid used to connect. Optional."
echo " -p Password for the userid. Mandatory if -u is specified."
echo " "
}

while getopts "d:hu:p:" inopt
do
case $inopt in
d) dbname=$OPTARG ;;
h) noheaders=1 ;;
u) userid=$OPTARG ;;
p) password=$OPTARG ;;
esac
done

if [ -z "$dbname" ] # Return error if dbname is null
then
# echo "Enter a valid database name using the -d option"
syntax
return 12
exit
fi

if [ -z "$userid" ] && [ -z "$password" ] # Choosing the connection statement
then
db2 connect to $dbname > /dev/null
else
db2 connect to $dbname user $userid using $password > /dev/null
fi

if [ $? -ne 0 ]
then
echo "Error when connecting to database $dbname"
return 12
exit
fi

db2 list tablespace containers for 0 > /dev/null # Check if list tablespace containers command is successful

if [ $? -ne 0 ]
then
echo "Error Occured when listing tablespace containers"
return 12
exit
fi
if [ -z "$userid" ] && [ -z "$password" ] # Choosing the connection statement
then
listtsp_cmd="db2_tspace.ksh -d $dbname -h"
else
listtsp_cmd="db2_tspace.ksh -d $dbname -h -u $userid -p $password"
fi

if [ $noheaders -eq 0 ]
then
echo 1 | awk -v dbn=$dbname ' {
print ""
print ""
printf("%10s %s","","Tablespace Containers for Database :" dbn) ;
print " "
print " "
printf("%4s %-20s %-6s %6s %8s %8s %sn","TsID","Tablespace","TSType","ContId","ContType","PagesAlloc","Container Name") ;
printf("%4s %-20s %-6s %6s %8s %8s %sn","----","----------","------","------","--------","----------","-------------------------------------------")
}
'
fi

$listtsp_cmd | awk '{print $1 " " $2 " " $3}' | while read tsid tsname tstype
do
db2 list tablespace containers for $tsid show detail | awk -v tspid=$tsid -v tsp=$tsname -v tsptype=$tstype '
BEGIN {
firstcont="yes"
nullstr=""
}
{
if ($1=="Container" && $2=="ID")
{
contid=$NF
}

if ($1=="Name" && $2=="=")
{
contname=$NF
}

if ($1=="Type" && $2=="=")
{
conttype=$NF
}

if ($1=="Total" && $2=="pages")
{
contalloc=$NF
if (firstcont=="yes")
{
print ""
printf("%-4s %-20s %-6s %6d %8s %10d %sn",tspid,tsp,tsptype,contid,conttype,contalloc,contname)
firstcont="no"
}
else
{
printf("%-4s %-20s %-6s %6d %8s %10d %sn",nullstr,nullstr,nullstr,contid,conttype,contalloc,contname)
}
}
}
'
done
echo
echo
 
4.批量建nickname

db2 connect to brio
cat nick_name.lst|while read tname
do
db2 "drop nickname "${tname}
db2 "create nickname "DB2V8I1"."${tname}" for oraserver2.newboss.${tname} "

done

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4096/viewspace-52437/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4096/viewspace-52437/