Re: DB Size
Jim Gordon (jgordon@ssf-sys.DHL.COM) wrote:
: A post was made a few weeks ago of a shellscript which calculated
: extent sizes for each table. I can't give an tribute because I don't
: remember whose work it was but the shell worked fine for me. It
: worked by using tbcheck -pt and awk to get the number of pages used
: for each table.
I wrote it and here it is. I wanted to post this again anyway because
the architecture it was originally written for (Sequent Intel 486MP) had
4k per page, while other architectures (HP 9000) have 2k per page. The
new version of dbsize has this as a required value to be added to the
script. It represents the number of 1k bytes per Informix On-line page.
If your OS has the 'pagesize' command, this will probably equal the
proper Informix value.
---------------------------------------------------------------------------
Here are some scripts I worked up to create the EXTENT lines in the
database create scripts.
The first one, dbsize, reads the output of 'tbcheck -pT' and generates a
file containing a line with the table name, and another line with the
computed sizes and extents (i.e. + 20%). You will need to remove the
sys* tables at the top of the file, as they do not appear in the
database creation script. You run it as
dbsize tbcheck.out
The script dbadd reads the output of the first script and the database
create script and inserts the extents in the proper places. You run it
as:
dbadd sizefile son_db.sql
You could integrate the two scripts into one, but I think this is easier
to follow.
It saved me alot of time, and possible typing errors. It gives extents
to all files over 8 pages.
Also, I have gotten in the habit of testing the script before I import
by creating the database by hand and running dbaccess and inserting the
intire SQL script into the query workspace and running it quickly to
check for syntax errors. Saves alot of worry if I have made edits by
hand.
---------------------------------------------------------------------------
:
#
# Define this before using.
# This is the number of 1k bytes per page, usually 2 or 4
#
KPERPAGE=
#
#
[ "$KPERPAGE" = "" ] && echo "Define 1k byes per page" 2>&1 && exit 1
awk ' $1 == "TBLSpace" && $2 == "Report" {printf "%s ",$4}
$3 == "pages" && $4 == "used" {printf "%s\\n", $5} ' $* |\\
cut -d'.' -f2 |\\
awk ' $2 > 8 {printf "^create table.*\\\\\\\\.%s \\n", $1;
sz=$2 * '"$KPERPAGE"' * 1.2;
new = sz * 0.2;
sz = int((sz + 7)/8)*8
new = int((new + 7)/8)*8
if (new <32)
new = 32;
printf "EXTENT SIZE %d NEXT SIZE %d\\n", sz, new}'
---------------------------------------------------------------------------
:
#dbadd
#set -x
trap "rm -f /tmp/$$" 0 1 2 3 15
while :
do
read TABLE || break
read EXTENT || break
awk ' BEGIN {found="N"; done="N"}
{ if (done != "Y" && $0 ~ /'"$TABLE "'/)
found="Y";
if (done != "Y" && found == "Y" && $1 == ");")
{
printf ")\\n%s;\\n","'"$EXTENT"'";
done = "Y";
}
else printf "%s\\n", $0;
}' $2 >/tmp/$$
mv /tmp/$$ $2
done <$1
---------------------------------------------------------------------------
--
Bruce Momjian | 830 Blythe Avenue
root@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
--
Bruce Momjian | 830 Blythe Avenue
root@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)