IDS Forum

RE: figuring out extents

Posted By: Floyd Wellershaus
Date: Tuesday, 10 May 2005, at 8:27 a.m.

In Response To: figuring out extents (Floyd Wellershaus )

Thanks. It's cool, but what I really want is one that will tell me how to size the extents. what the first and next extent sizes should be.



Bono Vox <in4mixperu@hotmail.com> wrote:
http://www.iiug.org/software/archive/extentsize

============================================================

From: Clem Akins
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=2048 #in tbconfig file
pageuse=`expr $pgsize - 28`
rowsize=0

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

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

colspace=`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 = 0 ]
then
i=0
while [ $i -lt $numcols ]
do
i=`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=`expr $rowsize + $sizetemp`
done

rowsize=`expr $rowsize + 4`
fi

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

ixspace=`expr $numindexes \* 12`

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

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

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

datrows=`expr $pageuse / $homerow`

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

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

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

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

==============================================================











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




========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator

email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================

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.