Пример с использованием - 'GET SNAPSHOT FOR … APPLICATIONS'

Sample Script:
--------------------------------------------------------------------------
#!/bin/ksh
# (c) 2004 Copyright IBM corp.
# Sample code, no support available.
# Purpose: Get DB2 Application Snapshot & format output at session level.
#-----------------------------------------------------------------------

SCRIPTNAME=$0
DBNAME=
SHOWMEM=0
PROCID=$$
##SNAPFILE=db2_snapshot.$PROCID
SNAPFILE=db2_snapshot.txt

rm -f $SNAPFILE

#-----------------------------------------------------------------------
function USAGE
#=============
{
print "Usage: $SCRIPTNAME [-d <database>] [-m]\n"
print "Where: <database> is optional"
print " -m is optional to show memory usage"
exit 1
}
#-----------------------------------------------------------------------

while getopts "d:m" opt;
do
case $opt in
d) typeset -u DBNAME=$OPTARG ;;
m) SHOWMEM=1 ;;
?) USAGE ;;
*) USAGE ;;
esac
done

#if ([[ -z $DBNAME ]]); then
# print "\nERROR: Required parameter not supplied"
# USAGE
#fi

#-----------------------------------------------------------------------
# SQL1042C An unexpected system error occurred. SQLSTATE=58004
#-----------------------------------------------------------------------
echo ""
echo "-Instance: $DB2INSTANCE"

if [[ ! -z $DBNAME ]]; then
echo ">> Database: "${DBNAME}
CMD="GET SNAPSHOT FOR APPLICATIONS ON $DBNAME GLOBAL"
else
CMD="GET SNAPSHOT FOR ALL APPLICATIONS GLOBAL"
fi

echo "-DB Co DB |---------------- Application ----------------| Age |------------ Rows ------------| |--- CPU ---|"
echo "-Name ord AuthId ClientId PID Name Handle Seq. nts Most_Recent_Oper Selected Read Written User System"
echo "-------- --- -------- ------------ ---------- -------------- -------- ---- --- ------------------ ---------- ---------- ---------- ------ ------"

db2 "$CMD" \
| egrep -i 'database|CPU|application|authorization|communication|rows|statement|sequence|agents|operation|Database partition number|Memory|Current size|High water mark|Most|sorts|fetched|login|cursor|operation' \
| awk -v ShowMem=$SHOWMEM 'BEGIN {FS="="; nHndle=0; nSeqNo=0; cStatus=""; cAppName=""; cConnAuth=""; cDb=""; \
cCommAddr=""; cPID=""; cClientAuth=""; nuCPU=0; nsCPU=0; nSelected=0; nRead=0; nWritten=0; nMaxAgents=0; \
nApps=0; nSorts=0; nFetched=0; \
nCurrBytes=0; nHWMBytes=0; }; \
{ \
if ($1 ~ /SQL1042C/) { print "System Error SQL1042C"; }; \
if ($1 ~ /Application Snapshot/) \
{ nApps+=1; \
if ( (ShowMem==1) && (nCurrBytes>0) ) \
{ \
PrintLine0(); \
print "Memory (MB):" nCurrBytes/1024/1024 ", HWM:" nHWMBytes/1024/1024; \
nCurrBytes=0; nHWMBytes=0; \
}; \
cConnAuth=""; cAppName=""; nHndle=0; \
}; \

if ($1 ~ /Application handle/) { nHndle=$2; }; \
if ($1 ~ /Application name/) { cAppName=substr($2,2,14); }; \
if ($1 ~ /Sequence number/) { nSeqNo=substr($2,2,4); }; \
if ($1 ~ /CONNECT Authorization ID/) { cConnAuth=substr($2,2,8); }; \
if ($1 ~ /Database name/) { cDb=substr($2,2,8); }; \
if ($1 ~ /Inbound communication address/) { cCommAddr=substr($2,2,20); gsub(/ /,"_",cCommAddr); }; \
if ($1 ~ /Coordinating database partition number/) { nCoord=substr($2,2); }; \
if ($1 ~ /Process ID of client application/) { cPID=substr($2,2,10); }; \
if ($1 ~ /Client login ID/) { cClientAuth=substr($2,2,12); gsub(/ /,"_",cClientAuth); }; \
if ($1 ~ /Agents associated with the application/) { nMaxAgents=$2; }; \
if ($1 ~ /Application status/) { cStatus=substr($2,2,18); }; \
if (($1 ~ /Most recent operation /) && (cStatus="")) { cStatus=substr($2,2,18); }; \
if ($1 ~ /Rows selected/) { nSelected=$2; }; \
if ($1 ~ /Rows read/) { nRead=+$2; }; \
if ($1 ~ /Rows written/) { nWritten=+$2; }; \
if ($1 ~ /Rows fetched/) { nFetched=$2; }; \
if ( (nFetched>0) ) \
{ PrintLine0(); \
printf ("xFetched:%12d (Addr:%s)\n", nFetched, cCommAddr); \
nFetched=0; \
}; \

if ($1 ~ /Total sorts/) { nSorts=$2; }; \

##if ($1 ~ /Number of agents working on statement/) { nMaxAgents=$2; }; \

if (ShowMem==1) \
{ if ($1 ~ /Memory usage for application/) { nCurrBytes=0; nHWMBytes=0; }; \
if ($1 ~ /Current size \(bytes\)/) { nCurrBytes=nCurrBytes+$2; }; \
if ($1 ~ /High water mark/) { nHWMBytes=nHWMBytes+$2; }; \
} \

if ($1 ~ /Total User CPU Time used by agent/) { nuCPU=$2; }; \

##if (($1 ~ /Application handle/) && (nHndle != 0) && (cStatus!=""))
##if ($1 ~ /Blocking cursor/) \
##if ($1 ~ /Rows fetched/) \
if ($1~/Total System CPU Time used by agent/) \
{ nsCPU=$2; \
gsub(/ /,"_",cStatus); \
PrintLine0(); \
PrintLine1(); \

if ( (ShowMem==1) && (nCurrBytes>0) ) \
{ PrintLine0(); \
print "Memory (MB):" nCurrBytes/1024/1024 ", HWM:" nHWMBytes/1024/1024; \
nCurrBytes=0; nHWMBytes=0; \
}; \
nSeqNo=0; cStatus=""; cDb=""; \
cCommAddr=""; nuCPU=0; nsCPU=0; nSelected=0; nRead=0; nWritten=0; nMaxAgents=0; \
#cPID=""; \
nSorts=0; nFetched=0; \
}; \
} \
END \
{ if ((ShowMem==1) && (nCurrBytes>0)) \
{ PrintLine0(); \
print "Memory (MB):" nCurrBytes/1024/1024 ", HWM:" nHWMBytes/1024/1024; \
}; \
}; \
\
function PrintLine0()
{
printf ("%-8s %-3d %-8s %-12s %10d %-14s %8d", cDb, nCoord, cConnAuth, cClientAuth, cPID,cAppName, nHndle );
};
\
function PrintLine1()
{
printf (" %0.4d %-3d %-18s %10d %10d %10d %6.2f %6.2f\n", nSeqNo, nMaxAgents, cStatus, nSelected, nRead, nWritten, nuCPU, nsCPU);
}
' | sort
-------------------------------------------------------
С наступающим праздником !!!

С уважением,
Вадим.