Calculate Extent Size

Hi to ALL, I my company we are using Informix 11.50 FC3 on AIX 5.3 platform and oslevel : 5300-09 I have to create a new table and I would like to have the fist Extent calculated before I create the table. Is that possible ?? I s there a formula to calculate the first extent size ????? The table is : create table ms_file ( age char(5), code char(4), body char(80), tel_choice char(1), total_cust integer, total_del integer, total_sms integer, total_fail integer, sms_user char(4), modif_date date, modif_time char(15), file_name char(50) ) extent size 32 next size 32 lock mode row; And the number of records to be loaded are 500 per year. Thanks Description: Description: CoopLogo1Description: Description: CoopLogo1 Cooperative Computer Society (S.E.M) Ltd 1306 Nicosia P.O.B. 25037 CY Tel: +357 22 673 901 Fax: +357 22 672 774 Achilleas Achilleos Official A OS and Databases Management <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy --Boundary_(ID_WqAqbaoY9D5GKUjL6Y9/+A)

Regards. You could do the maths, but there are several utilities that could help you. One of them is AGS Server Studio, that could estimate tables extent sizes, change tables already created, among several other things. In case you want to read more about it, I suggest you to access IBM Information Center website, a very good source of knowledge, for almost everything about Informix products. I think this link should help you:http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.perf.doc /ids_prf_310.htm Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator > To: ids@iiug.org > From: AchilleasAchilleos@semltd.com.cy > Subject: Calculate Extent Size [26671] > Date: Fri, 6 Apr 2012 03:43:29 -0400 > > Hi to ALL, > > I my company we are using Informix 11.50 FC3 on AIX 5.3 platform and oslevel > : 5300-09 > > I have to create a new table and I would like to have the fist Extent > calculated before I create the table. > > Is that possible ?? > > I s there a formula to calculate the first extent size ????? > > The table is : > > create table ms_file> > ( > > age char(5), > > code char(4), > > body char(80), > > tel_choice char(1), > > total_cust integer, > > total_del integer, > > total_sms integer, > > total_fail integer, > > sms_user char(4), > > modif_date date, > > modif_time char(15), > > file_name char(50) > > ) extent size 32 next size 32 lock mode row; > > And the number of records to be loaded are 500 per year. > > Thanks > > Description: Description: CoopLogo1Description: Description: CoopLogo1 > > Cooperative Computer Society (S.E.M) Ltd > > 1306 Nicosia > > P.O.B. 25037 CY > > Tel: +357 22 673 901 > > Fax: +357 22 672 774 > > Achilleas Achilleos > > Official A > > OS and Databases Management > > <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy > > --Boundary_(ID_WqAqbaoY9D5GKUjL6Y9/+A) > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Take ((rowsize + 4) * # of rows) and that's the size of the table. If you want a closer approximation: rows_per_page = INT( (pagesize - 24) / (rowsize + 4)) # pages = (# rows) / rows_per_page size = (# pages) * pagesize Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 6, 2012 at 3:43 AM, Achilleas Achilleos < AchilleasAchilleos@semltd.com.cy> wrote: > Hi to ALL, > > I my company we are using Informix 11.50 FC3 on AIX 5.3 platform and > oslevel > : 5300-09 > > I have to create a new table and I would like to have the fist Extent > calculated before I create the table. > > Is that possible ?? > > I s there a formula to calculate the first extent size ????? > > The table is : > > create table ms_file> > ( > > age char(5), > > code char(4), > > body char(80), > > tel_choice char(1), > > total_cust integer, > > total_del integer, > > total_sms integer, > > total_fail integer, > > sms_user char(4), > > modif_date date, > > modif_time char(15), > > file_name char(50) > > ) extent size 32 next size 32 lock mode row; > > And the number of records to be loaded are 500 per year. > > Thanks > > Description: Description: CoopLogo1Description: Description: CoopLogo1 > > Cooperative Computer Society (S.E.M) Ltd > > 1306 Nicosia > > P.O.B. 25037 CY > > Tel: +357 22 673 901 > > Fax: +357 22 672 774 > > Achilleas Achilleos > > Official A > > OS and Databases Management > > <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy > > --Boundary_(ID_WqAqbaoY9D5GKUjL6Y9/+A) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8c0c5e1dcd04bd0258ec

Just a little correction that can have an impact if rows are big: > rows_per_page = INT( (pagesize - 28) / (rowsize + 4)) An empty page has a 24 byte header + a timestamp of 4 bytes , that makes it 28 bytes. So the usable space in a page in your case is 4096-28=4068 bytes. If your rows go beyond a page, the calculation is different since you will have to add 4 byte forward pointers. This formula is good if you do not have varchars or lvarchars. If you use variable length fields , you can use MAX_FILL_DATA_PAGES to better use the pages. Khaled Bentebal ConsultiX Le 6 avr. 2012 à 14:08, "Art Kagel" <art.kagel@gmail.com> a écrit : > Take ((rowsize + 4) * # of rows) and that's the size of the table. If you > want a closer approximation: > > rows_per_page = INT( (pagesize - 24) / (rowsize + 4)) > # pages = (# rows) / rows_per_page > size = (# pages) * pagesize > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Fri, Apr 6, 2012 at 3:43 AM, Achilleas Achilleos < > AchilleasAchilleos@semltd.com.cy> wrote: > >> Hi to ALL, >> >> I my company we are using Informix 11.50 FC3 on AIX 5.3 platform and >> oslevel >> : 5300-09 >> >> I have to create a new table and I would like to have the fist Extent >> calculated before I create the table. >> >> Is that possible ?? >> >> I s there a formula to calculate the first extent size ????? >> >> The table is : >> >> create table ms_file>> >> ( >> >> age char(5), >> >> code char(4), >> >> body char(80), >> >> tel_choice char(1), >> >> total_cust integer, >> >> total_del integer, >> >> total_sms integer, >> >> total_fail integer, >> >> sms_user char(4), >> >> modif_date date, >> >> modif_time char(15), >> >> file_name char(50) >> >> ) extent size 32 next size 32 lock mode row; >> >> And the number of records to be loaded are 500 per year. >> >> Thanks >> >> Description: Description: CoopLogo1Description: Description: CoopLogo1 >> >> Cooperative Computer Society (S.E.M) Ltd >> >> 1306 Nicosia >> >> P.O.B. 25037 CY >> >> Tel: +357 22 673 901 >> >> Fax: +357 22 672 774 >> >> Achilleas Achilleos >> >> Official A >> >> OS and Databases Management >> >> <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy >> >> --Boundary_(ID_WqAqbaoY9D5GKUjL6Y9/+A) >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --90e6ba6e8c0c5e1dcd04bd0258ec > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

You are absolutely correct Khaled. Thanks for shoring me up. I subtracted the 2020 bytes of usable space from a 2k pagesize of 2048 and got 24. DOH! And I was a math major! Mr. Bloomberg would have revoked my napping in class privileges! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 6, 2012 at 1:43 PM, Khaled BENTEBAL < khaled.bentebal@consult-ix.fr> wrote: > Just a little correction that can have an impact if rows are big: > > rows_per_page = INT( (pagesize - 28) / (rowsize + 4)) > > An empty page has a 24 byte header + a timestamp of 4 bytes , that makes > it 28 > bytes. So the usable space in a page in your case is 4096-28=4068 bytes. > > If your rows go beyond a page, the calculation is different since you will > have to add 4 byte forward pointers. > > This formula is good if you do not have varchars or lvarchars. If you use > variable length fields , you can use MAX_FILL_DATA_PAGES to better use the > pages. > > Khaled Bentebal > ConsultiX > > Le 6 avr. 2012 à 14:08, "Art Kagel" <art.kagel@gmail.com> a écrit : > > > Take ((rowsize + 4) * # of rows) and that's the size of the table. If you > > want a closer approximation: > > > > rows_per_page = INT( (pagesize - 24) / (rowsize + 4)) > > # pages = (# rows) / rows_per_page > > size = (# pages) * pagesize > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > other organization with which I am associated either explicitly, > > implicitly, or by inference. Neither do those opinions reflect those of > > other individuals affiliated with any entity with which I am affiliated > nor > > those of the entities themselves. > > > > On Fri, Apr 6, 2012 at 3:43 AM, Achilleas Achilleos < > > AchilleasAchilleos@semltd.com.cy> wrote: > > > >> Hi to ALL, > >> > >> I my company we are using Informix 11.50 FC3 on AIX 5.3 platform and > >> oslevel > >> : 5300-09 > >> > >> I have to create a new table and I would like to have the fist Extent > >> calculated before I create the table. > >> > >> Is that possible ?? > >> > >> I s there a formula to calculate the first extent size ????? > >> > >> The table is : > >> > >> create table ms_file> >> > >> ( > >> > >> age char(5), > >> > >> code char(4), > >> > >> body char(80), > >> > >> tel_choice char(1), > >> > >> total_cust integer, > >> > >> total_del integer, > >> > >> total_sms integer, > >> > >> total_fail integer, > >> > >> sms_user char(4), > >> > >> modif_date date, > >> > >> modif_time char(15), > >> > >> file_name char(50) > >> > >> ) extent size 32 next size 32 lock mode row; > >> > >> And the number of records to be loaded are 500 per year. > >> > >> Thanks > >> > >> Description: Description: CoopLogo1Description: Description: CoopLogo1 > >> > >> Cooperative Computer Society (S.E.M) Ltd > >> > >> 1306 Nicosia > >> > >> P.O.B. 25037 CY > >> > >> Tel: +357 22 673 901 > >> > >> Fax: +357 22 672 774 > >> > >> Achilleas Achilleos > >> > >> Official A > >> > >> OS and Databases Management > >> > >> <mailto:AchilleasAchilleos@semltd.com.cy> > AchilleasAchilleos@semltd.com.cy > >> > >> --Boundary_(ID_WqAqbaoY9D5GKUjL6Y9/+A) > >> > >> > >> > >> > > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > --90e6ba6e8c0c5e1dcd04bd0258ec > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93406612fd05f04bd06b328

No problem Art. A tiny thing forgotten. You give so many great answers and advices. You are excused Khaled Bentebal de mon portable Le 6 avr. 2012 à 19:19, "Art Kagel" <art.kagel@gmail.com> a écrit : > You are absolutely correct Khaled. Thanks for shoring me up. I subtracted > the 2020 bytes of usable space from a 2k pagesize of 2048 and got 24. > DOH! And I was a math major! Mr. Bloomberg would have revoked my napping > in class privileges! > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Fri, Apr 6, 2012 at 1:43 PM, Khaled BENTEBAL < > khaled.bentebal@consult-ix.fr> wrote: > >> Just a little correction that can have an impact if rows are big: >>> rows_per_page = INT( (pagesize - 28) / (rowsize + 4)) >> >> An empty page has a 24 byte header + a timestamp of 4 bytes , that makes >> it 28 >> bytes. So the usable space in a page in your case is 4096-28=4068 bytes. >> >> If your rows go beyond a page, the calculation is different since you will >> have to add 4 byte forward pointers. >> >> This formula is good if you do not have varchars or lvarchars. If you use >> variable length fields , you can use MAX_FILL_DATA_PAGES to better use the >> pages. >> >> Khaled Bentebal >> ConsultiX >> >> Le 6 avr. 2012 à 14:08, "Art Kagel" <art.kagel@gmail.com> a écrit : >> >>> Take ((rowsize + 4) * # of rows) and that's the size of the table. If you >>> want a closer approximation: >>> >>> rows_per_page = INT( (pagesize - 24) / (rowsize + 4)) >>> # pages = (# rows) / rows_per_page >>> size = (# pages) * pagesize >>> >>> Art >>> >>> Art S. Kagel >>> Advanced DataTools (www.advancedatatools.com) >>> Blog: http://informix-myview.blogspot.com/ >>> >>> Disclaimer: Please keep in mind that my own opinions are my own opinions >>> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any >>> other organization with which I am associated either explicitly, >>> implicitly, or by inference. Neither do those opinions reflect those of >>> other individuals affiliated with any entity with which I am affiliated >> nor >>> those of the entities themselves. >>> >>> On Fri, Apr 6, 2012 at 3:43 AM, Achilleas Achilleos < >>> AchilleasAchilleos@semltd.com.cy> wrote: >>> >>>> Hi to ALL, >>>> >>>> I my company we are using Informix 11.50 FC3 on AIX 5.3 platform and >>>> oslevel >>>> : 5300-09 >>>> >>>> I have to create a new table and I would like to have the fist Extent >>>> calculated before I create the table. >>>> >>>> Is that possible ?? >>>> >>>> I s there a formula to calculate the first extent size ????? >>>> >>>> The table is : >>>> >>>> create table ms_file>>>> >>>> ( >>>> >>>> age char(5), >>>> >>>> code char(4), >>>> >>>> body char(80), >>>> >>>> tel_choice char(1), >>>> >>>> total_cust integer, >>>> >>>> total_del integer, >>>> >>>> total_sms integer, >>>> >>>> total_fail integer, >>>> >>>> sms_user char(4), >>>> >>>> modif_date date, >>>> >>>> modif_time char(15), >>>> >>>> file_name char(50) >>>> >>>> ) extent size 32 next size 32 lock mode row; >>>> >>>> And the number of records to be loaded are 500 per year. >>>> >>>> Thanks >>>> >>>> Description: Description: CoopLogo1Description: Description: CoopLogo1 >>>> >>>> Cooperative Computer Society (S.E.M) Ltd >>>> >>>> 1306 Nicosia >>>> >>>> P.O.B. 25037 CY >>>> >>>> Tel: +357 22 673 901 >>>> >>>> Fax: +357 22 672 774 >>>> >>>> Achilleas Achilleos >>>> >>>> Official A >>>> >>>> OS and Databases Management >>>> >>>> <mailto:AchilleasAchilleos@semltd.com.cy> >> AchilleasAchilleos@semltd.com.cy >>>> >>>> --Boundary_(ID_WqAqbaoY9D5GKUjL6Y9/+A) >>>> >>>> >>>> >>>> >>> >> >> > ******************************************************************************* >>>> Forum Note: Use "Reply" to post a response in the discussion forum. >>>> >>>> >>> >>> --90e6ba6e8c0c5e1dcd04bd0258ec >>> >>> >>> >> >> > ******************************************************************************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --14dae93406612fd05f04bd06b328 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >