Oninit cdi / Archive
Talk to an expert →

figuring out extents

Posted in 2005

Topics: Storage & Space Management

Floyd Wellershaus — — source: IIUG Forums & Mailing Lists

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

↪ replying to Floyd Wellershaus
Bono Vox — — source: IIUG Forums & Mailing Lists

http://www.iiug.org/software/archive/extentsize ============================================================ 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 - - - - - - - ============================================================== >From: "Floyd Welle...." <fwellers@yahoo.com> >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 > >

↪ replying to Floyd Wellershaus
Floyd Wellershaus — — source: IIUG Forums & Mailing Lists

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

↪ replying to Bono Vox
Thirugnanam, Kannan — — source: IIUG Forums & Mailing Lists

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

Related threads