Script to calc extent sizes

> From: ennis@rastov10.comm.mot.com (Bill Ennis) > Subject: Anyone have a function to compute the size of a column? [entire message snipped...] > From: Andy_DIBBINS_at_HML__STJAMES__B@orange.hutch.co.uk > Subject: Turbopak program ?? > > Hello, > > Some time ago a guy posted a program that he had written to calculate extent > sizes, I think he called it "turbopak" it was written in esql/c and was > originally written to run against Informix turbo ie pre - online. > [rest of message snipped...] > From: bb@aop.com (Berl Black) > Message-Id: <CHpy1F.HLt@aop.com> > Subject: Calculating On-Line Extents > Date: Wed, 8 Dec 1993 13:41:37 GMT > Reply-To: bb@aop.com (Berl Black) > Organization: Andrews Office Products, Capitol Heights, MD, (301)499-1515 > > Does anyone know of a utility program that will calculate extent sizes > based on actual table sizes. I am currently running 'tbcheck -pt' and > doing this manually. > Thanks This is a quickie script written for the Digital DECsystem (RISC) under Ultrix. It is a standard Bourne shell, but you may need to change something to use it. It shows: the size informix uses to store each row (sum(each col + 4)) the total number of pages used by the table (this is the size of the extent needed to hold that table) the maximum number of extents allowed for the table (for those DBA's who inherited a system with the extent/next size for each *large* table at the default size of 8) Hope this helps! (parenthetical note: I meant to post this yesterday but forgot to include it with my mail. When I went looking for it I found that I had mailed it way back in '93! Here's the whole enchilada...) __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________| ------------------------------ cut here ------------------------------------- #!/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" ------------------------------ cut here -------------------------------------