IDS Forum

RE: figuring out extents

Posted By: Thirugnanam, Kannan
Date: Tuesday, 10 May 2005, at 12:11 p.m.

In Response To: RE: figuring out extents (Bono Vox)

I have used the attached excel file to estimate the DB space. Over the
years, it has worked quite well for me.

Caveats:
1. If the row size is greater than 2000 bytes, it might extent to more
than one page. The new feature in Version 10 (page size) would help in
that scenario.
2. If there are a lot of deletes, then the index tree gets larger. The
index overhead ratio should be increased from 1.25. I have used 1.4.
3. Projected % depends on your companies business growth.

I got this calculations from a training material, long back. There might
be an Informix internal calculation available at IBM. If the internal
folks (Soft. Eng.) could get us something, it would be great.


Thank you,
Kannan Thirugnanam

-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Bono Vox
Sent: Tuesday, May 10, 2005 8:18 AM
To: ids@iiug.org
Subject: RE: figuring out extents [4894]=20

http://www.iiug.org/software/archive/extentsize

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

From: Clem Akins <cwakins@leia.alloys.rmc.com>
Subject: Extent Sizes

In keeping with the current threads on extents and row sizing, here's
a little script that I copied from the Informix SQL Tutorial.

It was written on a DEC Ultrix box, and runs without modification on a
new Digital Unix machine. Maybe it will run on yours. Let me know if
you find fault with it.

Enjoy,
__________________________________________________________________
| Clem Akins Standard Disclaimers Apply |
|Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" |
| Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com |
|________________________________________________________________|

BEGIN SCRIPT - - - - -
#!/bin/sh

#set -x

# calculate extent sizing for a table
# and upper limit on extents for a table
# these formulas taken from
# Informix Guide to SQL Tutorial v4.10 July 1991 pg 10-12
#
# cwa 5-18-93

pgsize=3D2048 #in tbconfig
file
pageuse=3D`expr $pgsize - 28`
rowsize=3D0

echo "Enter the estimated number of rows: "
read estrows

echo "How many columns in the table? "
read numcols

colspace=3D`expr $numcols \* 4`

echo "Enter the maximum row size (from tbcheck)"
echo "or zero to describe each column one-by-one: "
read rowsize

if [ $rowsize =3D 0 ]
then
i=3D0
while [ $i -lt $numcols ]
do
i=3D`expr $i + 1`
clear
echo ""
echo ""
echo " TEXT and BYTE: ................. 56 bytes"
echo " SMALLINT: ....................... 2 bytes"
echo " INTEGER: ........................ 4 bytes"
echo " SMALLFLOAT: ..................... 4 bytes"
echo " FLOAT: .......................... 8 bytes long"
echo " SERIAL: ......................... 4 bytes long"
echo " DATE: ........................... 4 bytes long"
echo ""
echo " MONEY & DECIMAL: 1/2 total digits + 1 (rounded
up)"
echo ""
echo " DATETIME & INTERVAL:"
echo " length is (((sum of digits)/2) +1) (rounded
up)"
echo " digits are:"
echo " YEAR: .......4 digits (unless you
specified more)"
echo " FRACTION: ...3 digits (unless you
specified more)"
echo " all others: .2 digits (unless you
specified more)"
echo ""
echo " CHAR columns as defined"
echo ""
echo "Enter the size in bytes of column $i: "
read sizetemp
rowsize=3D`expr $rowsize + $sizetemp`
done

rowsize=3D`expr $rowsize + 4`
fi

echo "How many indexes in the table? "
read numindexes

ixspace=3D`expr $numindexes \* 12`

ixparts=3D0
ixtemp=3D0
i=3D0
while [ $i -lt $numindexes ]
do
i=3D`expr $i + 1`
echo "Enter the number of columns in index $i: "
read numixcols
ixtemp=3D`expr $numixcols \* 4`
ixparts=3D`expr $ixparts + $ixtemp`
done

echo "The size of the row is: $rowsize"

if [ $rowsize -le $pageuse ]
then
homerow=3D$rowsize
overpage=3D0
else
homerow=3D`expr 4 + rowsize % pageuse`
overpage=3D`expr $rowsize / $pageuse`
fi

datrows=3D`expr $pageuse / $homerow`

if [ $datrows -gt 255 ]
then
datrows=3D255
fi

dattemp=3D`expr $estrows % $datrows` #modulo
(remainder)
if [ $dattemp !=3D 0 ]
then
datpages=3D`expr $estrows / $datrows`
datpages=3D`expr 1 + $datpages`
else
datpages=3D`expr $estrows / $datrows`
fi

expages=3D`expr $overpage \* $estrows`
totpages=3D`expr $datpages + $expages`
echo "Total pages required for this table: $totpages"

exttemp=3D`expr $colspace + $ixspace + $ixparts + 84`
extspace=3D`expr $pgsize - $exttemp`
limit=3D`expr $extspace / 8`
echo "Maximum number of extents allowed for this table: $limit"
END SCRIPT - - - - - - -

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D











>From: "Floyd Welle...." <fwellers@yahoo.com>
>To: ids@iiug.org
>Subject: figuring out extents [4889] Date: Mon, 9 May 2005 11:07:12
-0400=20
>(EDT)
>
>Hello,
>Does anyone have a script to be able to figure out the storage
parameters (=20
>first extent and next extent sizes ), based on a table schema, number
of=20
>expected rows, and growth ?
>
>Thanks,
>floyd
>
>

Messages In This Thread

• figuring out extents
  Floyd Wellershaus -- Monday, 9 May 2005, at 11:07 a.m.
  • RE: figuring out extents
    Bono Vox -- Tuesday, 10 May 2005, at 8:17 a.m.
    • RE: figuring out extents
      Thirugnanam, Kannan -- Tuesday, 10 May 2005, at 12:11 p.m.
  • RE: figuring out extents
    Floyd Wellershaus -- Tuesday, 10 May 2005, at 8:27 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.