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=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 - - - - - - -