Tablespace Allocation versus Storage Needs (More Scripting
IDS 7.31FD4X2
SunOS 5.7
Okay team, here's one for you:
This copy of the e-mail actually tells you what the problem is...apolog=
ies
for the previous omission.
I have written a script that is supposed to report space allocation for=
table and table-fragment tablespaces. I am receiving some erroneous res=
ults
and I have not been able to pinpoint the error in my logic. Please take=
a
look and see what you can see?I know I am receiving flawed results for =
52
of 2071 tables, but we all know what that means?
The problem: Some tables report that the amount of space I calculate is=
needed to store the actual number of rows in the table is greater than =
the
amount of space I calculate to be allocated to that table's tablespace.=
Update stats is not an issue.
The eventual goal is to report tables that are allocated beyond their
needs. Such tables would be marked for analysis as candidates for rebui=
ld
to free space within a dbspace. If you know a better way, I am open to
suggestion (but likely to continue trudging the path to my destiny with=
in
the SMI tables...).
Note that this is a first run through and I am aware that there are oth=
er
considerations when it comes to storage so feel free to mention what yo=
u
know. You can leave out the varchar part though; rowsize is the max
possible size for a row (as far as I can determine) and that will err i=
n my
favor...
The logic I am using to calculate the space needed to hold the rows tha=
t
exist within a table is:
the number of rows that fit on a page is calculated with this formula:=
('pagesize less overhead' / rowsize)
The pages required to store those rows is calculated with this formula=
:
('number of rows in the table' / 'number of rows that fit on a page')
The KB required to store those rows is calculated with this formula:
('number of pages to store rows * 'O/S page size')
(See attached file: 4iiug.tar)
Regards,
Bill Roberts
AAIS Core Production Support, Informix DBA
Verizon Data Services
Office : (813) 978-2340
Pager: (888) 423-6604
e-mail:bill.roberts@verizon.com
http://dbaman.tmtrfl.tel.gte.com/browser.cgi
#!/bin/ksh
#set -x
#######################################################################=
#########
# Place your very own environmental script here...
#######################################################################=
#########
. /opt/informix/dba.env.sh
#######################################################################=
#########
# Calculate the pagesize less overhead for my O/S.
#######################################################################=
#########
PAGE_SIZE=3D$(onstat -b | grep buffer | awk {'print $10'})
PAGE_SIZE_LO=3D$(expr $(onstat -b | grep buffer | awk {'print $10'}) - =
24)
#######################################################################=
#########
# Create a list of databases in an instance.
#######################################################################=
#########
for DB in $(printf "output to pipe cat without headings select name fro=
m \\\\
sysdatabases where name not in
('onpload','sysmaster','sysutils');" \\\\
| dbaccess 2>/dev/null sysmaster);do
#######################################################################=
#########
# Check for connect permission.
#######################################################################=
#########
echo | dbaccess > /dev/null 2>&1 ${DB}
RC=3D$?
if [ ${RC} -ne 0 ];then
echo "No connect permission to ${DB}"
else
#######################################################################=
#########
# Check for logging and set isolation level if it is on.
#######################################################################=
#########
IS_LOGGED=3D$(printf "output to pipe cat without headings \\\\
select is_logging from \\\\sysdatabases where name =3D \\\\"${DB}\\\\";" \\\\
| dbaccess 2>/dev/null sysmaster | tr -d [:space:])
if [ ${IS_LOGGED} -eq 0 ];then
SET_ISOLATION_LEVEL=3D
else
SET_ISOLATION_LEVEL=3D'set isolation dirty read;'
fi
#######################################################################=
#########
# Create a cross-reference for tables/dbspaces. Remove a lot of junk fr=
om
the
# select once the bugs are fixed...
# Oh, and by the way:
#
# the number of rows that fit on a page is calculated with this formula=
:
# ('pagesize less overhead' / rowsize)
#
# The pages required to store those rows is calculated with this formul=
a:
# ('number of rows in the table' / 'number of rows that fit on a page')=
#
# The KB required to store those rows is calculated with this formula:
# ('number of pages to store rows * 'O/S page size'
#
# Note that this is a first run through and I am aware that there are o=
ther
# considerations when it comes to storage (varchar overhead comes to mi=
nd),
but
# I'll work these issues out later (feel free to mention it though...)
#######################################################################=
#########
UNL_FILE=3D${DB}_t2d.unl
TXT_FILE=3D${DB}_t2d.txt
printf "${SET_ISOLATION_LEVEL}
UNLOAD TO ${UNL_FILE} delimiter \\\\" \\\\"
SELECT UNIQUE partnum,
name,
tabname,
tabtype,
rowsize,
nrows,
(ROUND((nrows*rowsize)/1024)) ROWS_KB,
(ROUND(nrows/(${PAGE_SIZE_LO}/rowsize))
*${PAGE_SIZE}) KB_4_ROWS,
(SUM(te_size)*${PAGE_SIZE}) EXTENTS_KB,
(ROUND((nrows*rowsize)/1024)/(SUM(te_size)*${PAGE_SIZE})*100) PERCENT_ROWS
FROM systables t, sysmaster:sysdbspaces s,
sysmaster:systabextents e
WHERE t.tabid > 99
and t.tabtype =3D \\\\"T\\\\"
and t.partnum !=3D 0
and s.dbsnum =3D trunc(t.partnum / 1048576)
and t.partnum =3D e.te_partnum
GROUP BY 1,2,3,4,5,6
UNION
SELECT UNIQUE partn,
name,
tabname,
fragtype,
t.rowsize,
f.nrows,
(ROUND(f.nrows*(t.rowsize+4)/1024)) USED_KB,
(ROUND((f.nrows/${PAGE_SIZE_LO}/(t.rowsize+4))=
*${PAGE_SIZE})) KB_4_ROWS,
(SUM(te_size)*${PAGE_SIZE}) ALLOCATED_KB,
(ROUND((f.nrows*(t.rowsize+4)/1024)/(SUM(te_si=
ze)
*${PAGE_SIZE}))*100) PERCENT_ROWS
FROM systables t, sysfragments f, sysmaster:sysdbspaces s,
sysmaster:systabextents e
WHERE t.tabid =3D f.tabid
and f.fragtype =3D \\\\"T\\\\"
and s.dbsnum =3D trunc(f.partn / 1048576)
and f.partn =3D e.te_partnum
GROUP BY 1,2,3,4,5,6
ORDER BY 3,2;" | dbaccess 2>/dev/null ${DB}
#######################################################################=
#########
# Create a few integer variables...
#######################################################################=
#########
typeset -i PARTN=3D0
typeset -i NROWS=3D0
typeset -i ROWSIZE=3D0
typeset -i USED_KB=3D0
typeset -i KB_4_ROWS=3D0
typeset -i ALLOC_KB=3D0
typeset -i PERCENT_ROWS=3D0
#######################################################################=
#########
# Print a heading.
#######################################################################=
#########
printf "%-9s %-18s %-18s %4s %9s %9s