This script will list all the databases and their total size that exist in your installation.

It does this by extracting the database names from the master database (iidbdb) and then attaching to each database and summing up the total size from the iitables system catalog.

It then outputs this in formatted way to stdout.

It was written using Korn Shell and works on Solaris 9/10.

#!/bin/ksh
#
sql iidbdb << ! > /tmp/db.list
SELECT name
FROM iidatabase
ORDER BY name
\p\g
!
#
NODENAME=`uname -n|cut -f 1 -d “.”`
DBLIST=`cat /tmp/db.list | grep “|” | grep -v name | sed -e ‘s/|//g’`
#
echo “”
echo “Checking database sizes in MB for ” $NODENAME
echo “”
#
let TOT_DB_SIZE=0
for DB in $DBLIST
do
sql -f8n20.2 $DB << ! >| /tmp/$DB.size
DROP TABLE db_size_tmp
\g
CREATE TABLE db_size_tmp AS
SELECT distinct table_name, tbl_size=float8(number_pages) * float8(table_pagesize)
FROM iitables
\g
SELECT float8(sum(tbl_size)) / 1024
FROM db_size_tmp
\g
!
#
DB_SIZE=`cat /tmp/$DB.size | grep “|” | grep -v col1 | sed -e ‘s/|//g’ | cut -f 1 -d “.”`
printf “%-16s\t\t=\t%16s\n” $DB $DB_SIZE
rm /tmp/$DB.size
let TOT_DB_SIZE=TOT_DB_SIZE+DB_SIZE
#
done
echo “”
echo “TOTAL DB SIZE = ” $TOT_DB_SIZE ” MB”