RE: need dbspace/table report
Obie Wan has had problems running this. It may need tweaking for your
system. However the following script takes oncheck -pe output and formats
it into a report giving you table size per chunk. Along with other info.
Syntax is oncheck -pe | awk -f onchk.awk > whereever
If you are using Solaris then use nawk instead of awk. Since this is coming
regular mail, you should check word wrap on the long lines.
If you have issues with the script please tell me. THIS SHOULD NOT BE ADDED
TO IIUG until others have had a chance to tell me what's wrong with it.
cheers
j.
############################################################################
#
#
# nawk script to read the output of oncheck -pe and summarize it.
#
# IDS Version
#
# Copyright (c) 1999 Jack Parker.
# Permission is granted to copy, distribute and/or modify this document
# under the terms of the GNU Free Documentation License, Version 1.1
# or any later version published by the Free Software Foundation;
#
# syntax: oncheck -pe | nawk -f onchk.awk > wherever.
#
############################################################################
#
BEGIN {
prod_db["my_db1"]=1 # set up one array element for each production
db you wish to report on
prod_db["my_db2"]=1
skip["FREE"]=1 # Ignore these
skip["TBLSPACE"]=1
skip["CHUNK"]=1
skip["DATABASE"]=1
skip["OTHER"]=1
skip["EXT"]=1
skip["ROOT"]=1
page_sz=2048 # set according to your $ONCONFIG PAGESIZE
low=.2 # Less than this percent free generates a
flag
high=.8 # over this percent free generates a flag
ext=4 # More than this number of extents generates
a flag
}
{
if (csw==1) {
cksz=$1
ckusd=$2
ckfree=$3
csw=0
}
if (tsw==1) {
if ( $1 in skip ) {
# Ignore these
} else {
tbn=$1
n=split(tbn,x,":")
if (x[1] in prod_db) {
n=split(x[2],y,".")
tbnm=y[2]
tb[tbnm]+=substr($0,63)
tbct[tbnm]+=1
}
else {
tbnm=x[1]
tb[tbnm]+=substr($0,63)
tbct[tbnm]+=1
}
}
}
}
/Chunk/ {
n=split($3,ck,"/")
chknm=ck[3]
if (chknm != oldchk && oldchk != "") {
tsw=0
printf("\\nChunk: %-9s Size: %7.2f Used: %7.2f Ckfree:
%7.2f PctFree: %3.2f\\n", chknm, cksz*page_sz/(1024*1024),
ckusd*page_sz/(1024*1024),ckfree*page_sz/(1024*1024), (ckfree/cksz)*100)
if (ckfree/cksz<low) {printf("\\t\\t\\t\\t\\t\\t*** LESS THAN %2.0f%% FREE
***\\n",low*100)}
if (ckfree/cksz>high) {printf("\\t\\t\\t\\t\\t\\t*** OVER %2.0f%% FREE
SPACE ***\\n",high*100)}
for (i in tb) {
if (tb[i] > 0 && substr(tb[i],1,3) != " " && tb[i]!=3) {
printf("%-20s %7.2f Extents %3d", i,
tb[i]*page_sz/(1024*1024), tbct[i])
if (tbct[i] > ext) { printf(" *****\\n") }
else {printf("\\n") }
}
tot+=tb[i]
tb[i]=000
tbct[i]=0
}
gtot+=ckusd
tot=0
}
oldchk=chknm
csw=1
}
/----------------------/ {tsw=1}
END {
printf("Grand total disk use (MB) %9.2f\\n",
gtot*page_sz/(1024*1024))
}
> -----Original Message-----
> From: Doug McAllister [mailto:doug.mcallister@nospam.fmr.com]
> Sent: Monday, January 08, 2001 11:48 AM
> To: informix-list@iiug.org
> Subject: need dbspace/table report
>
>
> Hi all..
>
> I need to report on how much each table in a dbspace is using as a
> percentage of the total dbspace space.
> I have searched all of the usual spots and if there is such a thing,
> then the description is buried.
>
> Has anyone done such a thing or can point me to where I would find it?
> I do not care if the table is fragmented since I am interested in
> dpspace info, and I do not have 4gl so I would need either
> scripts or C.
>
> tnx.......
>
> Doug
>