Collect database server's basic information

Question

Before IDS 11, to collect database server's information, all operations or actions will be done one by one and manually. To improve it, utilize some scripts to do it automatically and conveniently.

Answer

Steps to use these scripts:

1. At first, run health_check.sh on database server site to collect data

2. Then, run health_operation.sh with the package generated by health_check.sh and do some simple analysis based on these data

#!/bin/sh
###############################################################################
#
# Module: health_check.sh
# Description: Collect informix information for health check
#
# Change Log
#
# Date Name Description.................
###############################################################################
#Bundled file's name form: INSTANCENAME_YYYYMMDD_POSITION_MACHINE.tar.[bz2|gz|Z]
#Notice: Can not run this script on IDS 7.14 or before version as not include evidence.sh

usage()
{
printf "Usage: health_check.sh position\n"
exit 1

} 

# check usage; exit if incorrect. valid argument counts is 1.
[ $# -ne 1 ] && usage

#Initialize environment
AWK=""
if [ -z "$AWK" ]
then for awker in gawk nawk awk
do
for dir in `echo $PATH | sed 's/:/ /g'`
do
if [ -x "$dir/$awker" ]
then AWK="$dir/$awker"
break 2
fi
done
done
fi

DATE=`date '+%Y%m%d'`
DATAPATH=./${INFORMIXSERVER}/${DATE}
POSITION=$1
MACHINE=`uname -n`
ONLINE_LOG=`onstat -c |${AWK} -v dir=${INFORMIXDIR} '/^MSGPATH/ {sub(/\\$INFORMIXDIR/,dir,$2);print $2}'`
BAR_ACT_LOG=`onstat -c |${AWK} -v dir=${INFORMIXDIR} '/^BAR_ACT_LOG/ {sub(/\\$INFORMIXDIR/,dir,$2);print $2}'`

[ ! -d ${DATAPATH} ] && mkdir -p ${DATAPATH}

#Check for message log
printf "Operating message log...\n"
egrep -i "assert|err|fail" "${ONLINE_LOG}">${DATAPATH}/online.err
egrep -i "warn" "${ONLINE_LOG}">${DATAPATH}/online.warn
egrep -i "modified" "${ONLINE_LOG}">${DATAPATH}/online.modified
egrep -i "duration" "${ONLINE_LOG}">${DATAPATH}/online.duration

egrep -i "assert|err|fail" "${BAR_ACT_LOG}">${DATAPATH}/bar_act.err

cp ${ONLINE_LOG} ${DATAPATH}/online.log
cp ${BAR_ACT_LOG} ${DATAPATH}/bar_act.log

#Check for SMI tables & collect database's schema
printf "Operating SMI tables & collect database's schema...\n"
for database in `dbaccess sysmaster 2>${DATAPATH}/log <<! | ${AWK} '
BEGIN {flag = 0;}
!/^$/{
if ( flag == 1 )
print $1
if ( $1 == "name" )
if ( NF == 2 )
print $2
else
flag = 1;
}'
select name from sysdatabases
where name not in ("sysmaster", "sysutils", "sysuser", "syscdr", onpload", "sysadmin")
!
`
do
dbschema -d ${database} -ss ${DATAPATH}/${database}.sql
dbaccess ${database} >${DATAPATH}/log 2>&1 <<!

set isolation dirty read;

--find sequential scan
unload to ${DATAPATH}/seq_scans_$database.txt
select p.dbsname, p.tabname, t.nrows, sum(p.seqscans) tot_seqscans
from sysmaster:sysptprof p, systables t
where p.dbsname = "${database}"
and p.tabname = t.tabname
and t.tabid > 99
and t.nrows > 20000
group by 1,2,3
having sum(p.seqscans) > 3
order by 3 desc,4 desc;

--find lock wait
unload to ${DATAPATH}/lockwait_$database.txt 
select p.dbsname, p.tabname, t.nrows, t.locklevel,sum(p.lockwts) lockwts
from sysmaster:sysptprof p, systables t
where p.dbsname = "${database}"
and p.tabname = t.tabname
and t.tabid > 99
group by 1,2,3,4
having sum(p.lockwts) > 0
order by 5 desc;

--监控index层数意义不大，因为客户对于index的层数是不可控的
unload to ${DATAPATH}/idx_lvl_$database.txt 
select t.tabname, i.idxname, i.levels
from systables t, sysindexes i
where t.tabid = i.tabid
and t.tabid > 99
and i.levels > 4
order by 3 desc;

--evaluate index's unique keys in the first column, find high duplicated index
unload to ${DATAPATH}/idx_unique_$database.txt
select t.tabname, i.idxname, t.nrows, i.nunique
from systables t, sysindexes i
where t.tabid =i.tabid
and t.tabid > 99
and t.nrows > 10000
and i.nunique < 0.01*t.nrows 
order by 3 desc,4 asc;

--evaluate table's io ratio
unload to ${DATAPATH}/table_io_$database.txt 
select p.dbsname, p.tabname, (p.isreads + p.pagreads) diskreads, (p.iswrites + p.pagwrites) diskwrites,(p.isreads + p.pagreads + p.iswrites + p.pagwrites) disk_io
from sysmaster:sysptprof p, systables t
where p.dbsname = "${database}"
and p.tabname = t.tabname
and t.tabid > 99
order by 5 desc;
!

done

dbaccess sysmaster >${DATAPATH}/log 2>&1 <<!

--get dbspace's usage percent
unload to ${DATAPATH}/dbs_spaces.txt 
select name dbspace, sum(chksize) allocated, sum(nfree) free,
round(((sum(chksize) - sum(nfree))/sum(chksize))*100) pcused
from sysmaster:sysdbspaces d, sysmaster:syschunks c
where d.dbsnum = c.dbsnum
group by 1
order by 4 desc;

--get chunk's io ratio
unload to ${DATAPATH}/chunk_io.txt
select d.name, fname path_name, sum(pagesread) diskreads, sum(pageswritten) diskwrites
from sysmaster:syschkio c, sysmaster:syschunks k, sysmaster:sysdbspaces d
where d.dbsnum = k.dbsnum
and k.chknum = c.chunknum
group by 1, 2
order by 3 desc;

!

#Get integrated and evident information of the instance
printf "Collect integrated and evident information of the instance ...\n"
#Tip:There is a little problem at here when we use $INFORMIXDIR to indicate the path in IDS 11.10 or above
sed -e "s/tail -100/tail -5000/" -e "s/DEBUG=off/DEBUG=on/" -e "s/onSTAT -g ses$/onSTAT -g ses 0/" ${INFORMIXDIR}/etc/evidence.sh > ${DATAPATH}/evidence.sh
sh ${DATAPATH}/evidence.sh 2 0 ${DATAPATH}/evi.txt 0 0 0 0 1

#Estimate the maximum rows can be hold in a single table fragment
printf "Estimating the maximum rows in a single table fragment...\n"
cal_maxrows.sh > ${DATAPATH}/maxrows.txt

#Estimate the maximum extents can be hold in a single table fragment
printf "Estimating the maximum extents in a single table fragment...\n"
cal_restextents.sh > ${DATAPATH}/maxextents.txt

mv ./oncheck.pt.* ${DATAPATH}

#Sanity check
printf "Operating sanity check...\n"
oncheck -pr > ${DATAPATH}/oncheck.pr
oncheck -pc > ${DATAPATH}/oncheck.pc
oncheck -pe > ${DATAPATH}/oncheck.pe
egrep -i err ${DATAPATH}/oncheck* | grep -v syserrors > ${DATAPATH}/oncheck.err

#Compress the output of check
printf "Operating compress action...\n"
for packer in bzip2.bz2 gzip.gz compress.Z
do
compress=`echo $packer | sed 's/\..*//'`
suffix=`echo $packer | sed 's/.*\.//'`
for dir in `echo $PATH | sed 's/:/ /g'`
do
if [ -x "$dir/$compress" ]
then COMPRESS="$dir/$compress"
COMPRESS_SUFFIX=$suffix
break 2
fi
done
done
tar cvf - ${DATAPATH} | ${COMPRESS} > ./${INFORMIXSERVER}_${DATE}_${POSITION}_${MACHINE}.tar.${COMPRESS_SUFFIX}

printf "Do you want to backup all database server's log and clean them? [Y/n]"
ANS=''
read ANS
if [ "${ANS}" = "Y" -o "${ANS}" = "y" ]
then
cat ${ONLINE_LOG} | ${COMPRESS} > ${ONLINE_LOG}_${DATE}.${COMPRESS_SUFFIX}
cat ${BAR_ACT_LOG} | ${COMPRESS} > ${BAR_ACT_LOG}_${DATE}.${COMPRESS_SUFFIX}
> ${ONLINE_LOG}
> ${BAR_ACT_LOG}
fi

#Clear the instance's counter number
printf "Do you want to reset all count of database server? [Y/n]"
ANS=''
read ANS
if [ "${ANS}" = "Y" -o "${ANS}" = "y" ]
then
onstat -z
fi

#Clean interim files



#!/bin/sh
###############################################################################
#
# Module: cal_maxrows.sh
# Description: Estimate the maximum rows can be inserted into a single tablespace
#
# Change Log
#
# Date Name Description.................
#
###############################################################################

case `uname -s` in Linux) ECHO="echo -e" ;;
*) ECHO="echo" ;;
esac
ev_echo_n()
{
$ECHO $*\\c
}

usage()
{
ev_echo_n "usage: cal_maxrows.sh [database [tablename]]\n"
exit 1

} 

# check usage; exit if incorrect. valid argument counts are 0, 1 and 2.
[ $# -ne 0 -a $# -ne 1 -a $# -ne 2 ] && usage

AWK=""
if [ -z "$AWK" ]
then for awker in gawk nawk awk
do
for dir in `echo $PATH | sed 's/:/ /g'`
do
if [ -x "$dir/$awker" ]
then AWK="$dir/$awker"
break 2
fi
done
done
fi

################################################################################
# #
# MAIN BODY OF SCRIPT #
# #
################################################################################

#initialize environment
database=$1
table=$2
db_filename=db_$$.txt
tab_filename=tab_$$.txt
trap 'rm -f $db_filename $tab_filename;exit 1' 1 2 15

buffersize=`oncheck -pr | ${AWK} '/Page Size/ {print $3}'`

#get database's and table's information
if [ "x${database}" = "x" ]
then
dbaccess sysmaster >/dev/null 2>&1 <<!
unload to ${db_filename} select name from sysdatabases 
where name not in ("sysmaster", "sysutils", "sysuser", "syscdr", onpload", "sysadmin")
!
else
ev_echo_n "${database}|" > ${db_filename}
fi

for database in `${AWK} -F '|' '{print $1}' ${db_filename}`
do
if [ "x${table}" = "x" ]
then
dbaccess ${database} >/dev/null 2>&1 <<!
unload to ${tab_filename} select tabname from systables where tabid >= 100 and tabtype='T'
!
else
ev_echo_n "${table}|" > ${tab_filename}
fi

#calculate the accurate value we want to get
(cat ${tab_filename};onstat -d;oncheck -pt ${database}) |tee ./oncheck.pt.${database}| ${AWK} -v bz=${buffersize} '
function cal(rowsize, bz, rows, PN_4BITS)
{
printf "%d\t%d\t%d\t", bz, rowsize, rows;
pageuse = bz - 28;
maxpages = (PN_4BITS == 1 ? int((int(pageuse/16))*32*16777215/((int(pageuse/16))*32+1)) : int((int(pageuse/8))*32*16777215/((int(pageuse/8))*32+1)));
ratio = 0.80;

if ( rowsize + 4 <= pageuse ) {
j = int(pageuse / (rowsize + 4));
j > 255 ? j = 255 : j;
maxrows=maxpages * j * ratio;
} else {
partremsize = (rowsize % (pageuse - 8)) + 4
partratio = partremsize / pageuse;
if ( partratio <= 0.1 )
partratio = 0.1;
else if ( partratio <= 0.3 )
partratio = 0.3;
else if ( partratio <= 0.5 )
partratio = 0.5;
else 
partratio = 1;
maxpages = (PN_4BITS == 1 ? int((int(pageuse/16))*32*maxpages/((int(pageuse/16))*32+1)) : int((int(pageuse/8))*32*maxpages/((int(pageuse/8))*32+1)));
maxrows=(maxpages / (int(rowsize / (pageuse - 8)) + partratio)) * ratio;
}
printf ("%15d\t%f\n", maxrows, 100 * (rows / maxrows));
}

BEGIN {flag=0;tab_flag=0;Dbspaces_flag=0;}

#get relevant tables
/.*\|$/ && NF == 1 {split($0, a, "|"); table[a[1]]=NR;}

#get fragment information
/^Dbspaces/ {Dbspaces_flag=1;}
Dbspaces_flag == 1 {
if ( $2 ~ /[0-9]+/ ) {
Dbspaces[$2] = $NF;
} else if ( $0 ~ /maximum$/ ) {
Dbspaces_flag = 0;
}
}

/^TBLspace Report for/ || /Table fragment.*in/ {
if ( $0 ~ /^TBLspace Report for/ ) {
fragment=0;
split($4,r,"[.:]"); 
databasename=r[1];
tablename=r[3];
if ( tablename in table ) {
flag=1; 
tab_flag=1;
} else {
tab_flag=0;
next;
}
}
if ( $0 ~ /Table fragment.*in/ && tab_flag == 1) {
flag=1; 
}
if ( fragment == 0 && $0 ~ /Table fragment.*in/ ) {
fragment = 1; next;
}
printf "%-20.20s %-30.30s ", databasename, tablename;
}
/TBLspace use.*bit bit-maps/ && flag == 1 {PN_4BITS = ($3 == 4 ? 1 : 0); }
/Maximum row size/ && flag == 1 {rowsize=$4;}
/Number of rows/ && flag == 1 {rows=$4;}
/Partition partnum/ && flag == 1 {printf "%-20.20s ", Dbspaces[int($3/1048576)]}
/Extents/ && flag == 1 {flag=0; cal(rowsize, bz, rows, PN_4BITS);}
' | sort -n -r -k 8
rm ${tab_filename}
printf "%-20.20s %-30.30s %-20.20s %s\t%s\t%s\t%15.15s\t%s\n" db table fragment pgsz rz rows maxrows ratio
done
rm ${db_filename}



#!/bin/sh
###############################################################################
#
# Module: cal_restextents.sh
# Description: Estimate the maximum extents can be allocated for a single tablespace
#
# Change Log
#
# Date Name Description.................
#
###############################################################################

AWK=""
if [ -z "$AWK" ]
then for awker in gawk nawk awk
do
for dir in `echo $PATH | sed 's/:/ /g'`
do
if [ -x "$dir/$awker" ]
then AWK="$dir/$awker"
break 2
fi
done
done
fi

SRC_VER=`onstat - | ${AWK} '/Version/ {
keep_next = -1
for (i = 1; i < NF; i++) {
if ($i == "Version")
keep_next = i + 1
else if (i == keep_next)
print $i
}
}'`
if [ `expr "$SRC_VER" : "\(.*\..*\)\..*"|sed 's/\.//'` -lt 940 ]
then
pre94=1
else
pre94=0
fi

case `uname -s` in Linux) ECHO="echo -e" ;;
*) ECHO="echo" ;;
esac
ev_echo_n()
{
$ECHO $*\\c
}

usage()
{
ev_echo_n "usage: cal_restextents.sh [database [tablename]]\n"
exit 1

} # end of usage().
# check usage; exit if incorrect. valid argument counts are 0, 1 and 2.
[ $# -ne 0 -a $# -ne 1 -a $# -ne 2 ] && usage

################################################################################
# #
# MAIN BODY OF SCRIPT #
# #
################################################################################

#initialize environment
database=$1
table=$2
db_filename=db_$$.txt
tab_filename=tab_$$.txt
trap 'rm -f $db_filename $tab_filename;exit 1' 1 2 15

#get database's and table's information
if [ "x${database}" = "x" ]
then
dbaccess sysmaster >/dev/null 2>&1 <<!
unload to ${db_filename} select name from sysdatabases
where name not in ("sysmaster", "sysutils", "sysuser", "syscdr", onpload", "sysadmin")
!
else
ev_echo_n "${database}|" > ${db_filename}
fi

for database in `${AWK} -F '|' '{print $1}' ${db_filename}`
do
if [ "x${table}" = "x" ]
then
dbaccess ${database} >/dev/null 2>&1 <<!
unload to ${tab_filename} select tabname from systables where tabid >= 100 and tabtype='T'
!
else
ev_echo_n "${table}|" > ${tab_filename}
fi

#calculate the accurate value we want to get
(cat ${tab_filename};onstat -d;oncheck -pt ${database}) |tee ./oncheck.pt.${database}| ${AWK} -v pre94=${pre94} '
function todec(str) {
hstr="0123456789ABCDEF";
res=0;
n=length(str);
for(i=1;i<=n;i++) {
digit[i]=substr(toupper(str),i,1);
num=index(hstr,digit[i])-1;
res=res+(num*16^(n-i));
}
return res;
}

BEGIN {flag=0;}

#get relevant tables
/.*\|$/ && NF == 1 {split($0, a, "|"); table[a[1]]=NR;}

#get fragment information
/^Dbspaces/ {Dbspaces_flag=1;}
Dbspaces_flag == 1 {
if ( $2 ~ /[0-9]+/ ) {
Dbspaces[$2] = $NF;
} else if ( $0 ~ /maximum$/ ) {
Dbspaces_flag = 0;
}
}

/^TBLspace Report for/ || /Table fragment.*in/ {
if ( $0 ~ /^TBLspace Report for/ ) {
fragment=0;
split($4,r,"[.:]"); 
databasename=r[1];
tablename=r[3];
if ( tablename in table ) {
flag=1; 
tab_flag=1;
} else {
tab_flag=0;
next;
}
}
if ( $0 ~ /Table fragment.*in/ && tab_flag == 1) {
flag=1; 
}
if ( fragment == 0 && $0 ~ /Table fragment.*in/ ) {
fragment = 1; next;
}
printf "%-20.20s %-30.30s ", databasename, tablename;
}
/Physical Address/ && flag == 1 {
if ( pre94 == 1 ) {
printf "%d\t%d\t", todec($3)/1048576, todec($3)%1048576;
} else {
split($3,r,":")
printf "%d\t%d\t", r[1], r[2];
}
}
/Number of extents/ && flag == 1 {printf "%d\t", $4;}
/Partition partnum/ && flag == 1 {printf "%s\t\t", Dbspaces[int($3/1048576)];}
/Extents/ && flag == 1 {flag = 0; printf "\n";}
' | while read database table chk pg extent fragment 
do
printf "%-20.20s %-30.30s %-20.20s %d\t%d\t%d" ${database} ${table} ${fragment} ${chk} ${pg} ${extent}
oncheck -pP ${chk} ${pg} | ${AWK} -v ext=${extent} -v pre94=${pre94} '
flag == 1 { 
if ( pre94 == 1 ) {
printf "\t%d\t%d\t%f", $7, int($7/8), 100*(ext/(int($7/8)+ext));
} else {
printf "\t%d\t%d\t%f", $8, int($8/8), 100*(ext/(int($8/8)+ext));
}
flag=0;
} 
/frcnt/ {flag=1}'
printf "\n"
done | sort -r -n -k 9
rm ${tab_filename}
printf "%-20.20s %-30.30s %-20.20s %s\t%s\t%s\t%s\t%s\t%s\n" db tab fragment chk pg ext frcnt rest ratio
done
rm ${db_filename}



#!/bin/sh
###############################################################################
#
# Module: health_operation.sh
# Description: Operate informix information collected from health check
#
# Change Log
#
# Date Name Description.................
# Archive file format INSTANCENAME_YYYYMMDD_POSITION_MACHINE.tar.[bz2|gz|Z]
###############################################################################
#Bundled file's name form: INSTANCENAME_YYYYMMDD_POSITION_MACHINE.tar.[bz2|gz|Z]

usage()
{
printf "Usage: health_operation.sh archived_files\n"
exit 1

} 

# check usage; exit if incorrect. valid argument at least great than 0.
[ $# -eq 0 ] && usage

#Operate all bundled files entered one by one
for files in $*
do

#Decompress the health check information
for packer in bzip2.bz2 gzip.gz gzip.Z
do
compress=`echo $packer | sed 's/\..*//'`
suffix=`echo $packer | sed 's/.*\.//'`
if [ "${suffix}" = "`echo "${files}" | sed 's/.*\.//'`" ] 
then
base=`basename ${files} .tar.${suffix}`
INS=`echo $base | sed -e 's/_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_.*$//'`
DATE=`echo $base | sed -e 's/.*_\([0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]\)_.*$/\1/'`
POSITION=`echo $base | sed -e 's/.*_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_//' -e 's/_.*//'`
MACHINE=`echo $base | sed -e 's/.*_//'`
printf ${INS},${DATE},${POSITION},${MACHINE},
SERVER=${INS}
[ ! -d ${POSITION}/${MACHINE} ] && mkdir -p ${POSITION}/${MACHINE}
$compress -c -d ${files} | tar xf - -C ./${POSITION}/${MACHINE} 2>/dev/null
break;
fi
done

#Prepare pretreatment environment
DATADIR=${POSITION}/${MACHINE}/${SERVER}/${DATE}
PREDIR=${POSITION}/${MACHINE}/${SERVER}/pre${DATE}
DATEBASE_DATE=`echo ${DATE} | awk '{print substr($0,5,2)"/"substr($0,7,2)"/"substr($0,0,4)}'`
[ ! -d ${PREDIR} ] && mkdir -p ${PREDIR}

#Operate the evidence information
awk -v server=${SERVER} -v position=${POSITION} -v machine=${MACHINE} -v date=${DATEBASE_DATE} -v predir=${PREDIR} '

BEGIN { num = 0; }

function initflag()
{
pflag=0;
}

function on_p() 
{
onstat_p_output=predir"/onstat_p.txt";
printf "%s|%s|%s|%s|read|%d|%d|%d|%f|\n", server, position, machine, date, onstat_p["dskreads"], onstat_p["pagreads"], onstat_p["bufreads"], onstat_p["read_cache"] >onstat_p_output
printf "%s|%s|%s|%s|write|%d|%d|%d|%f|\n", server, position, machine, date, onstat_p["dskwrits"], onstat_p["pagwrits"], onstat_p["bufwrits"], onstat_p["write_cache"] >>onstat_p_output
}

/onstat -p/ {initflag(); pflag=1;}

pflag == 1 {
if ( $0 ~ /^[0-9][0-9]*/ ) {
num ++;
if (num == 1) {
onstat_p["dskreads"] = $1;
onstat_p["pagreads"] = $2;
onstat_p["bufreads"] = $3;
onstat_p["read_cache"] = $4;
onstat_p["dskwrits"] = $5;
onstat_p["pagwrits"] = $6;
onstat_p["bufwrits"] = $7;
onstat_p["write_cache"] = $8;
} else if (num == 2) {
onstat_p["write"] = $5;
onstat_p["rewrite"] = $6;
onstat_p["delete"] = $7;
} else if (num == 3) {
} else if (num == 4) {
onstat_p["ovlock"] = $1;
onstat_p["ovbuff"] = $3;
onstat_p["usercpu"] = $4;
onstat_p["syscpu"] = $5;
onstat_p["numckpts"] = $7;
} else if (num == 5) {
onstat_p["bufwaits"] = $1;
onstat_p["lokwaits"] = $2;
onstat_p["lockreqs"] = $3;
onstat_p["deadlks"] = $4;
onstat_p["ckpwaits"] = $6;
onstat_p["compress"] = $7;
onstat_p["seqscans"] = $8;
} else if (num == 6) {
onstat_p["ixda-RA"] = $1;
onstat_p["idx-RA"] = $2;
onstat_p["da-RA"] = $3;
onstat_p["RA-pgsused"] = $4;
}
}
}

END {
on_p();
}

' ${DATADIR}/evi.txt

#Operate the data by SQL
DATABASE="database_name" #To specify the database's name at here

#Operate for onstat_p

PREDATE=`dbaccess ${DATABASE} 2>${PREDIR}/log <<! | awk -v date=${DATEBASE_DATE} '
BEGIN{a=date}
/^[0-9]+/ {a=$1}
END {print a}'
select first 1 date from onstat_p where date < "${DATEBASE_DATE}" and server="${SERVER}" 
and position="${POSITION}" and machine="${MACHINE}" order by date desc
!
`

printf ${DATEBASE_DATE},${PREDATE}"\n"

dbaccess ${DATABASE} >${PREDIR}/log 2>&1 <<!

delete from onstat_p where server="${SERVER}" and position="${POSITION}" and machine="${MACHINE}" and date="${DATEBASE_DATE}";
load from ${PREDIR}/onstat_p.txt insert into onstat_p;

unload to ${PREDIR}/onstat_p.txt select case a.type
when "read" then "read"
when "write" then "write"
end,
a.disk, a.page, a.buffer, a.cache::decimal(4,2), b.cache::decimal(4,2),
case when a.cache > nvl(b.cache,0) then "↑"
when a.cache < nvl(b.cache,0) then "↓"
else "→"
end
from onstat_p a, outer onstat_p b
where a.date="${DATEBASE_DATE}" and b.date="${PREDATE}"
and a.server="${SERVER}" and a.position="${POSITION}" and a.machine="${MACHINE}"
and a.server=b.server and a.position=b.position and a.machine=b.machine and a.type=b.type;

!

#Operate for dbspace information
#If can not find previous information, I think the PREDATE is current date, so the trent of value is →
PREDATE=`dbaccess ${DATABASE} 2>${PREDIR}/log <<! | awk -v date=${DATEBASE_DATE} '
BEGIN{a=date}
/^[0-9]+/ {a=$1}
END {print a}'
select first 1 date from dbs_spaces where date < "${DATEBASE_DATE}" and server="${SERVER}"
and position="${POSITION}" and machine="${MACHINE}" order by date desc
!
`
sed "s@^@${SERVER}|${POSITION}|${MACHINE}|${DATEBASE_DATE}|@" ${DATADIR}/dbs_spaces.txt > ${PREDIR}/dbs_spaces.txt

dbaccess ${DATABASE} >${PREDIR}/log 2>&1 <<!
delete from dbs_spaces where server="${SERVER}" and position="${POSITION}" and machine="${MACHINE}" and date="${DATEBASE_DATE}";
load from ${PREDIR}/dbs_spaces.txt insert into dbs_spaces;

unload to ${PREDIR}/dbs_spaces.txt select a.dbspace,a.total,a.free,a.usage,b.usage,
case when a.usage > nvl(b.usage,0) then "↑"
when a.usage < nvl(b.usage,0) then "↓"
else "→"
end
from dbs_spaces a, outer dbs_spaces b
where a.date="${DATEBASE_DATE}" and b.date="${PREDATE}"
and a.server="${SERVER}" and a.position="${POSITION}" and a.machine="${MACHINE}"
and a.server=b.server and a.position=b.position and a.machine=b.machine and a.dbspace=b.dbspace
order by a.usage desc
!

#Operate tabspace usage

PREDATE=`dbaccess ${DATABASE} 2>${PREDIR}/log <<! | awk -v date=${DATEBASE_DATE} '
BEGIN{a=date}
/^[0-9]+/ {a=$1}
END {print a}'
select first 1 date from maxrows where date < "${DATEBASE_DATE}" and server="${SERVER}"
and position="${POSITION}" and machine="${MACHINE}" order by date desc
!
`
awk -v server=${SERVER} -v position=${POSITION} -v machine=${MACHINE} -v date=${DATEBASE_DATE} '
!/^$/ && !/^db.*ratio$/ {printf "%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|\n", server, position, machine, date, $1, $2, $3, $4, $5, $6, $7, $8}
' ${DATADIR}/maxrows.txt > ${PREDIR}/maxrows.txt

dbaccess ${DATABASE} >${PREDIR}/log 2>&1 <<!
delete from maxrows where server="${SERVER}" and position="${POSITION}" and machine="${MACHINE}" and date="${DATEBASE_DATE}";
load from ${PREDIR}/maxrows.txt insert into maxrows;

unload to ${PREDIR}/maxrows.txt select a.database, a.table, a.fragment, a.rowsize, a.rows, a.maxrows, a.ratio::decimal(4,2), b.ratio::decimal(4,2),
case when a.ratio > nvl(b.ratio,0) then "↑"
when a.ratio < nvl(b.ratio,0) then "↓"
else "→"
end
from maxrows a, outer maxrows b
where a.date="${DATEBASE_DATE}" and b.date="${PREDATE}"
and a.server="${SERVER}" and a.position="${POSITION}" and a.machine="${MACHINE}"
and a.server=b.server and a.position=b.position and a.machine=b.machine and a.database=b.database and a.table=b.table and a.fragment=b.fragment
order by a.ratio desc;
!

#Operate extent usage

PREDATE=`dbaccess ${DATABASE} 2>${PREDIR}/log <<! | awk -v date=${DATEBASE_DATE} '
BEGIN{a=date}
/^[0-9]+/ {a=$1}
END {print a}'
select first 1 date from maxextents where date < "${DATEBASE_DATE}" and server="${SERVER}"
and position="${POSITION}" and machine="${MACHINE}" order by date desc
!
`
awk -v server=${SERVER} -v position=${POSITION} -v machine=${MACHINE} -v date=${DATEBASE_DATE} '
!/^$/ && !/^db.*ratio$/ {printf "%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|\n", server, position, machine, date, $1, $2, $3, $4, $5, $6, $7, $8, $9}
' ${DATADIR}/maxextents.txt > ${PREDIR}/maxextents.txt

dbaccess ${DATABASE} >${PREDIR}/log 2>&1 <<!
delete from maxextents where server="${SERVER}" and position="${POSITION}" and machine="${MACHINE}" and date="${DATEBASE_DATE}";
load from ${PREDIR}/maxextents.txt insert into maxextents;

unload to ${PREDIR}/maxextents.txt select a.database, a.table, a.fragment, a.extent, a.restextent, a.ratio::decimal(4,2), b.ratio::decimal(4,2),
case when a.ratio > nvl(b.ratio,0) then "↑"
when a.ratio < nvl(b.ratio,0) then "↓"
else "→"
end
from maxextents a, outer maxextents b
where a.date="${DATEBASE_DATE}" and b.date="${PREDATE}"
and a.server="${SERVER}" and a.position="${POSITION}" and a.machine="${MACHINE}"
and a.server=b.server and a.position=b.position and a.machine=b.machine and a.database=b.database and a.table=b.table and a.fragment=b.fragment
order by a.ratio desc;
!

#Operate sequence scan

PREDATE=`dbaccess ${DATABASE} 2>${PREDIR}/log <<! | awk -v date=${DATEBASE_DATE} '
BEGIN{a=date}
/^[0-9]+/ {a=$1}
END {print a}'
select first 1 date from seq_scans where date < "${DATEBASE_DATE}" and server="${SERVER}"
and position="${POSITION}" and machine="${MACHINE}" order by date desc
!
`
sed "s@^@${SERVER}|${POSITION}|${MACHINE}|${DATEBASE_DATE}|@" ${DATADIR}/seq_scans*.txt > ${PREDIR}/seq_scans.txt

dbaccess ${DATABASE} >${PREDIR}/log 2>&1 <<!
delete from seq_scans where server="${SERVER}" and position="${POSITION}" and machine="${MACHINE}" and date="${DATEBASE_DATE}";
load from ${PREDIR}/seq_scans.txt insert into seq_scans;

unload to ${PREDIR}/seq_scans.txt select a.database,a.table,a.rows,a.seqscan,b.seqscan,
case when a.seqscan > nvl(b.seqscan,0) then "↑"
when a.seqscan < nvl(b.seqscan,0) then "↓"
else "→"
end
from seq_scans a, outer seq_scans b
where a.date="${DATEBASE_DATE}" and b.date="${PREDATE}"
and a.server="${SERVER}" and a.position="${POSITION}" and a.machine="${MACHINE}"
and a.server=b.server and a.position=b.position and a.machine=b.machine and a.database=b.database and a.table=b.table 
order by a.rows desc, a.seqscan desc
!

#Operate serial number issue
awk '
$1 ~ /TBLspace/ && $2 ~ /.+:.+\..+/ {table=$2}
/Current serial value/ && $4 >= 1000000000 {print table, $4} ' ${DATADIR}/oncheck.pc > ${PREDIR}/oncheck.serial

done

https://www-304.ibm.com/support/docview.wss?uid=swg21330733