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
checkipc.txt