Determining the Maximum Number of Extents

Posted in 2008

Topics: Storage & Space Management, Transactions, Locking & Isolation, Platform-Specific Issues, Versions, Editions & End-of-Life

Clifton Bean — Sun, 13 Jan 2008 14:20:21 — source: IIUG Forums & Mailing Lists

IDS 10.0.UC6 AIX 5.2 I have a table that currently has 180 extents and I just want to make sure I understand how to calculate the table's maximum number of extents. I am using the calculation from the following IDS Webpage: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.pe rf.doc/perf189.htm First, I did an oncheck -pt on the tablename and got the following: Physical Address 25:485560 Creation date 06/10/2007 01:58:16 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 527 Number of special columns 0 Number of keys 0 Number of extents 180 Current serial value 2216656 Pagesize (k) 4 First extent size 8 Next extent size 6400 Number of pages allocated 187792 Number of pages used 187792 Number of data pages 187244 Number of rows 1310669 I then took the physical address (how nice of IFMX to now separate out the chunk number and page address above) and performed an oncheck -pP 25 485560 and got the following: addr stamp chksum nslots flag type frptr frcnt next prev 25:485560 675410294 b191 5 802 PARTN 1608 2468 0 0 According to that page, I should then take the frcnt (2468) and divide it by 8 to get the number of ADDITIONAL extents ... which is 308. 308+180 = 488 Is this truly the number of extents this table can have? I just want to make sure I am performing this calculation correctly since I have not used v10 that long and want to make sure I am doing this correctly. Thanks in advance. Clifton PS. Does anyone have a spreadsheet methodology that one can use to autocalculate this for all of the tables within a database / instance? _________________________________________________________________ Put your friends on the big screen with Windows Vista® + Windows Live. http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=TXT_TAGLM_CPC_Medi aCtr_bigscreen_012008

↪ replying to Clifton Bean

Jack Parker — Sun, 13 Jan 2008 15:06:44 — source: IIUG Forums & Mailing Lists

There is an easier way. Check this out: http://groups.google.com/group/comp.databases.informix/browse_frm/thread/4c8 cb5ef1a864887/e8d37ae732708294?hl=en&lnk=gst&q=max+extents#e8d37ae732708294 j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Clifton Bean Sent: Sunday, January 13, 2008 9:20 AM To: ids@iiug.org Subject: Determining the Maximum Number of Extents [10895] IDS 10.0.UC6 AIX 5.2 I have a table that currently has 180 extents and I just want to make sure I understand how to calculate the table's maximum number of extents. I am using the calculation from the following IDS Webpage: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ib m.perf.doc/perf189.htm First, I did an oncheck -pt on the tablename and got the following: Physical Address 25:485560 Creation date 06/10/2007 01:58:16 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 527 Number of special columns 0 Number of keys 0 Number of extents 180 Current serial value 2216656 Pagesize (k) 4 First extent size 8 Next extent size 6400 Number of pages allocated 187792 Number of pages used 187792 Number of data pages 187244 Number of rows 1310669 I then took the physical address (how nice of IFMX to now separate out the chunk number and page address above) and performed an oncheck -pP 25 485560 and got the following: addr stamp chksum nslots flag type frptr frcnt next prev 25:485560 675410294 b191 5 802 PARTN 1608 2468 0 0 According to that page, I should then take the frcnt (2468) and divide it by 8 to get the number of ADDITIONAL extents ... which is 308. 308+180 = 488 Is this truly the number of extents this table can have? I just want to make sure I am performing this calculation correctly since I have not used v10 that long and want to make sure I am doing this correctly. Thanks in advance. Clifton PS. Does anyone have a spreadsheet methodology that one can use to autocalculate this for all of the tables within a database / instance? _________________________________________________________________ Put your friends on the big screen with Windows Vista. + Windows Live . http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=TXT_TAGLM_CPC_ MediaCtr_bigscreen_012008 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Clifton Bean

Art S. Kagel — Tue, 15 Jan 2008 02:42:22 — source: IIUG Forums & Mailing Lists

Clifton Bean wrote: > IDS 10.0.UC6 AIX 5.2 > > I have a table that currently has 180 extents and I just want to make sure I > understand how to calculate the table's maximum number of extents. > > I am using the calculation from the following IDS Webpage: > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.pe rf.doc/perf189.htm > > First, I did an oncheck -pt on the tablename and got the following: > > Physical Address 25:485560 > > Creation date 06/10/2007 01:58:16 > > TBLspace Flags 802 Row Locking > > TBLspace use 4 bit bit-maps > > Maximum row size 527 > > Number of special columns 0 > > Number of keys 0 > > Number of extents 180 > > Current serial value 2216656 > > Pagesize (k) 4 > > First extent size 8 > > Next extent size 6400 > > Number of pages allocated 187792 > > Number of pages used 187792 > > Number of data pages 187244 > > Number of rows 1310669 > > I then took the physical address (how nice of IFMX to now separate out the > chunk number and page address above) and performed an oncheck -pP 25 485560 > and got the following: > > addr stamp chksum nslots flag type frptr frcnt next prev > 25:485560 675410294 b191 5 802 PARTN 1608 2468 0 0 > > According to that page, I should then take the frcnt (2468) and divide it by 8 > to get the number of ADDITIONAL extents ... which is 308. > > 308+180 = 488 > > Is this truly the number of extents this table can have? I just want to make > sure I am performing this calculation correctly since I have not used v10 that > long and want to make sure I am doing this correctly. > Yes, for a 4K pagesize database (the default on AIX and Windows) the maximum extents for a table with no indexes and no special column is about that, for a 2K pagesize database it's about 1/2 that or around 240. Art S. Kagel Oninit > Thanks in advance. > Clifton > > PS. > Does anyone have a spreadsheet methodology that one can use to autocalculate > this for all of the tables within a database / instance? > >

Glossary

IDS (Informix Dynamic Server)