RE: Informix Dynamic Server w/ AD Version 8.21.UD4
Larry asked why tables on his 8.21 XPS/AIX system have 300-446 extents when docs cite a ~200 limit. Art Kagel and John Miller explained the extent list must fit in the table's partition-header page: 2K pages allow ~200 extents, but AIX/Windows (and XPS) use 4K pages, allowing roughly 400-450, less if there are many indexes or special columns. Fix for user tables: unload/truncate or copy to a new table and rename. The internal TBLSpace tablespace isn't visible or tunable and can only be reorganized by unloading everything and reinitializing the instance.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have an instance of IDS 8.21. When looking up information on maximum extents, it lists something low in the 200's maximum for the number of extents that a table can have. I have a couple of tables with over 400 extents and one over 300. I have not seen any problems yet, but according to the documentation that I have read, the database should have complained a long time ago. Does anyone of any current information on this as to why the database has not complained and on what I can expect for max extents? Larry
OK, here's the scoop. The entire list of a table (or a table's partition/fragment) must fit on the table's partition header page along with the table's basic statistics, index key lists, and information about special columns. The first items are fixed for all tables, but the keys and special columns will vary per table and affect the number of extent records that will fit in the remaining space on the page. So, on a 2K page (all Informix versions prior to 10.00 on all platforms used a 2K page size except AIX and Windows which use 4K pages) has room for a bit over 200 extents if there are only a few indexes and no special columns. The max adjusts down from there. If you are running on AIX or Windows (see why it's important to always post your platform information?) you have a 4K partition header page so you can probably fit around 400->450 extents max on a simple table. If you have tables with over 100 extents and are frequently accessing rows that reside in multiple extents, I would strongly recommend reorganizing those tables. Art On Mon, Feb 9, 2009 at 5:22 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > I have an instance of IDS 8.21. When looking up information on maximum > extents, it lists something low in the 200's maximum for the number of > extents > that a table can have. I have a couple of tables with over 400 extents and > one > over 300. I have not seen any problems yet, but according to the > documentation > that I have read, the database should have complained a long time ago. Does > anyone of any current information on this as to why the database has not > complained and on what I can expect for max extents? > > Larry > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. --0016369891bde784970462842cb2
Larry: Your XPS system is probably using a pagesize of 4KB (or higher). Most XPS system have a base page size of 4KB. The base page size of an XPS system maybe specified when you init the system the first time. Most of the manual calculation assume a 2KB pages size.. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/09/2009 02:22:21 PM: > I have an instance of IDS 8.21. When looking up information on maximum > extents, it lists something low in the 200's maximum for the number > of extents > that a table can have. I have a couple of tables with over 400 > extents and one > over 300. I have not seen any problems yet, but according to the > documentation > that I have read, the database should have complained a long time ago. Does > anyone of any current information on this as to why the database has not > complained and on what I can expect for max extents? > > Larry > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Here are the extents on the tables in question: Extents Table ------- -------- 446.0 TBLSpace 412.0 agg_grp 305.0 sales_wkly We are running AIX 4.3 with a 4k page size. I am not quite as familiar with XPS as I am with IDS. I will assume that I am getting close to the extent limit on those 3 tables. What are my options. As you can see, one of the tables is a system table and the other 2 are user tables. With the 2 user tables, can you just do like in IDS and unload, drop/recreate, reload the tables? What are my options instead. On the system table, I am not sure what to do. I can use as much information as possible. Larry > To: ids@iiug.org > From: miller3@us.ibm.com > Subject: RE: Informix Dynamic Server w/ AD Version 8.21.... [14811] > Date: Mon, 9 Feb 2009 18:19:49 -0500 > > Larry: > > Your XPS system is probably using a pagesize of 4KB (or higher). Most XPS > system have a base page size of 4KB. The base page size of an XPS system > maybe specified when you init the system the first time. > > Most of the manual calculation assume a 2KB pages size.. > > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 02/09/2009 02:22:21 PM: > > > I have an instance of IDS 8.21. When looking up information on maximum > > extents, it lists something low in the 200's maximum for the number > > of extents > > that a table can have. I have a couple of tables with over 400 > > extents and one > > over 300. I have not seen any problems yet, but according to the > > documentation > > that I have read, the database should have complained a long time ago. > Does > > anyone of any current information on this as to why the database has not > > complained and on what I can expect for max extents? > > > > Larry > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
The user tables you can reorg by exporting their data, truncating the table, and reloading the data - or create a new table, copy the data from the old table to the new one, rename both tables, drop the old one. The 'system' table is actually the set of tablespace pages or each table's header page for the entire server. The only way to reorg the tablespace tablespace is to unload all databases, reinitialize the engine, reinitialize all dbspaces, recreate and reload all databases. Not pretty. Art On Wed, Feb 11, 2009 at 4:58 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > Here are the extents on the tables in question: > > Extents Table > > ------- -------- > > 446.0 TBLSpace > > 412.0 agg_grp > > 305.0 sales_wkly > We are running AIX 4.3 with a 4k page size. I am not quite as familiar with > XPS as I am with IDS. I will assume that I am getting close to the extent > limit on those 3 tables. What are my options. As you can see, one of the > tables is a system table and the other 2 are user tables. With the 2 user > tables, can you just do like in IDS and unload, drop/recreate, reload the > tables? What are my options instead. On the system table, I am not sure > what > to do. I can use as much information as possible. > Larry > > > To: ids@iiug.org > > From: miller3@us.ibm.com > > Subject: RE: Informix Dynamic Server w/ AD Version 8.21.... [14811] > > Date: Mon, 9 Feb 2009 18:19:49 -0500 > > > > Larry: > > > > Your XPS system is probably using a pagesize of 4KB (or higher). Most XPS > > system have a base page size of 4KB. The base page size of an XPS system > > maybe specified when you init the system the first time. > > > > Most of the manual calculation assume a 2KB pages size.. > > > > John F. Miller III > > STSM, Support Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > > > ids-bounces@iiug.org wrote on 02/09/2009 02:22:21 PM: > > > > > I have an instance of IDS 8.21. When looking up information on maximum > > > extents, it lists something low in the 200's maximum for the number > > > of extents > > > that a table can have. I have a couple of tables with over 400 > > > extents and one > > > over 300. I have not seen any problems yet, but according to the > > > documentation > > > that I have read, the database should have complained a long time ago. > > Does > > > anyone of any current information on this as to why the database has > not > > > complained and on what I can expect for max extents? > > > > > > Larry > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. --0016e64e9c0eebe04c0462aeabe2
For the reorg of the TBLSpace tablespace, Is there a way, or a need to set the size of the initial extent and next extents? > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: Informix Dynamic Server w/ AD Version 8.21.... [14852] > Date: Wed, 11 Feb 2009 20:30:58 -0500 > > The user tables you can reorg by exporting their data, truncating the table, > and reloading the data - or create a new table, copy the data from the old > table to the new one, rename both tables, drop the old one. The 'system' > table is actually the set of tablespace pages or each table's header page > for the entire server. The only way to reorg the tablespace tablespace is > to unload all databases, reinitialize the engine, reinitialize all dbspaces, > recreate and reload all databases. Not pretty. > > Art > > On Wed, Feb 11, 2009 at 4:58 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > > Here are the extents on the tables in question: > > > > Extents Table > > > > ------- -------- > > > > 446.0 TBLSpace > > > > 412.0 agg_grp > > > > 305.0 sales_wkly > > We are running AIX 4.3 with a 4k page size. I am not quite as familiar with > > XPS as I am with IDS. I will assume that I am getting close to the extent > > limit on those 3 tables. What are my options. As you can see, one of the > > tables is a system table and the other 2 are user tables. With the 2 user > > tables, can you just do like in IDS and unload, drop/recreate, reload the > > tables? What are my options instead. On the system table, I am not sure > > what > > to do. I can use as much information as possible. > > Larry > > > > > To: ids@iiug.org > > > From: miller3@us.ibm.com > > > Subject: RE: Informix Dynamic Server w/ AD Version 8.21.... [14811] > > > Date: Mon, 9 Feb 2009 18:19:49 -0500 > > > > > > Larry: > > > > > > Your XPS system is probably using a pagesize of 4KB (or higher). Most XPS > > > system have a base page size of 4KB. The base page size of an XPS system > > > maybe specified when you init the system the first time. > > > > > > Most of the manual calculation assume a 2KB pages size.. > > > > > > John F. Miller III > > > STSM, Support Architect > > > miller3@us.ibm.com > > > 503-578-5645 > > > IBM Informix Dynamic Server (IDS) > > > > > > ids-bounces@iiug.org wrote on 02/09/2009 02:22:21 PM: > > > > > > > I have an instance of IDS 8.21. When looking up information on maximum > > > > extents, it lists something low in the 200's maximum for the number > > > > of extents > > > > that a table can have. I have a couple of tables with over 400 > > > > extents and one > > > > over 300. I have not seen any problems yet, but according to the > > > > documentation > > > > that I have read, the database should have complained a long time ago. > > > Does > > > > anyone of any current information on this as to why the database has > > not > > > > complained and on what I can expect for max extents? > > > > > > > > Larry > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any entity > with which I am affiliated nor those of the entities themselves. > > --0016e64e9c0eebe04c0462aeabe2 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Art, Also, I can't find the TBLSpace table in either the sysmaster or the user database. Where is that located and can I set the next extent for now? > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: Informix Dynamic Server w/ AD Version 8.21.... [14852] > Date: Wed, 11 Feb 2009 20:30:58 -0500 > > The user tables you can reorg by exporting their data, truncating the table, > and reloading the data - or create a new table, copy the data from the old > table to the new one, rename both tables, drop the old one. The 'system' > table is actually the set of tablespace pages or each table's header page > for the entire server. The only way to reorg the tablespace tablespace is > to unload all databases, reinitialize the engine, reinitialize all dbspaces, > recreate and reload all databases. Not pretty. > > Art > > On Wed, Feb 11, 2009 at 4:58 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > > Here are the extents on the tables in question: > > > > Extents Table > > > > ------- -------- > > > > 446.0 TBLSpace > > > > 412.0 agg_grp > > > > 305.0 sales_wkly > > We are running AIX 4.3 with a 4k page size. I am not quite as familiar with > > XPS as I am with IDS. I will assume that I am getting close to the extent > > limit on those 3 tables. What are my options. As you can see, one of the > > tables is a system table and the other 2 are user tables. With the 2 user > > tables, can you just do like in IDS and unload, drop/recreate, reload the > > tables? What are my options instead. On the system table, I am not sure > > what > > to do. I can use as much information as possible. > > Larry > > > > > To: ids@iiug.org > > > From: miller3@us.ibm.com > > > Subject: RE: Informix Dynamic Server w/ AD Version 8.21.... [14811] > > > Date: Mon, 9 Feb 2009 18:19:49 -0500 > > > > > > Larry: > > > > > > Your XPS system is probably using a pagesize of 4KB (or higher). Most XPS > > > system have a base page size of 4KB. The base page size of an XPS system > > > maybe specified when you init the system the first time. > > > > > > Most of the manual calculation assume a 2KB pages size.. > > > > > > John F. Miller III > > > STSM, Support Architect > > > miller3@us.ibm.com > > > 503-578-5645 > > > IBM Informix Dynamic Server (IDS) > > > > > > ids-bounces@iiug.org wrote on 02/09/2009 02:22:21 PM: > > > > > > > I have an instance of IDS 8.21. When looking up information on maximum > > > > extents, it lists something low in the 200's maximum for the number > > > > of extents > > > > that a table can have. I have a couple of tables with over 400 > > > > extents and one > > > > over 300. I have not seen any problems yet, but according to the > > > > documentation > > > > that I have read, the database should have complained a long time ago. > > > Does > > > > anyone of any current information on this as to why the database has > > not > > > > complained and on what I can expect for max extents? > > > > > > > > Larry > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any entity > with which I am affiliated nor those of the entities themselves. > > --0016e64e9c0eebe04c0462aeabe2 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
No until IDS 11.50xC3 or later. Art On Thu, Feb 12, 2009 at 11:07 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote: > For the reorg of the TBLSpace tablespace, Is there a way, or a need to set > the > size of the initial extent and next extents? > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: Informix Dynamic Server w/ AD Version 8.21.... [14852] > > Date: Wed, 11 Feb 2009 20:30:58 -0500 > > > > The user tables you can reorg by exporting their data, truncating the > table, > > and reloading the data - or create a new table, copy the data from the > old > > table to the new one, rename both tables, drop the old one. The 'system' > > table is actually the set of tablespace pages or each table's header page > > for the entire server. The only way to reorg the tablespace tablespace is > > to unload all databases, reinitialize the engine, reinitialize all > dbspaces, > > recreate and reload all databases. Not pretty. > > > > Art > > > > On Wed, Feb 11, 2009 at 4:58 PM, LARRY SORENSEN <lsorensen25@msn.com> > wrote: > > > > > Here are the extents on the tables in question: > > > > > > Extents Table > > > > > > ------- -------- > > > > > > 446.0 TBLSpace > > > > > > 412.0 agg_grp > > > > > > 305.0 sales_wkly > > > We are running AIX 4.3 with a 4k page size. I am not quite as familiar > with > > > XPS as I am with IDS. I will assume that I am getting close to the > extent > > > limit on those 3 tables. What are my options. As you can see, one of > the > > > tables is a system table and the other 2 are user tables. With the 2 > user > > > tables, can you just do like in IDS and unload, drop/recreate, reload > the > > > tables? What are my options instead. On the system table, I am not sure > > > what > > > to do. I can use as much information as possible. > > > Larry > > > > > > > To: ids@iiug.org > > > > From: miller3@us.ibm.com > > > > Subject: RE: Informix Dynamic Server w/ AD Version 8.21.... [14811] > > > > Date: Mon, 9 Feb 2009 18:19:49 -0500 > > > > > > > > Larry: > > > > > > > > Your XPS system is probably using a pagesize of 4KB (or higher). Most > XPS > > > > system have a base page size of 4KB. The base page size of an XPS > system > > > > maybe specified when you init the system the first time. > > > > > > > > Most of the manual calculation assume a 2KB pages size.. > > > > > > > > John F. Miller III > > > > STSM, Support Architect > > > > miller3@us.ibm.com > > > > 503-578-5645 > > > > IBM Informix Dynamic Server (IDS) > > > > > > > > ids-bounces@iiug.org wrote on 02/09/2009 02:22:21 PM: > > > > > > > > > I have an instance of IDS 8.21. When looking up information on > maximum > > > > > extents, it lists something low in the 200's maximum for the number > > > > > of extents > > > > > that a table can have. I have a couple of tables with over 400 > > > > > extents and one > > > > > over 300. I have not seen any problems yet, but according to the > > > > > documentation > > > > > that I have read, the database should have complained a long time > ago. > > > > Does > > > > > anyone of any current information on this as to why the database > has > > > not > > > > > complained and on what I can expect for max extents? > > > > > > > > > > Larry > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (art@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > > with which I am associated either explicitly or implicitly. Neither do > > those opinions reflect those of other individuals affiliated with any > entity > > with which I am affiliated nor those of the entities themselves. > > > > --0016e64e9c0eebe04c0462aeabe2 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. --0016e640cee851c2880462bbd3d7
It doesn't appear anywhere. It is a total internal table and you cannot set the next size for it (nor for normal system tables either). Art On Thu, Feb 12, 2009 at 11:13 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote: > Art, > > Also, I can't find the TBLSpace table in either the sysmaster or the user > database. Where is that located and can I set the next extent for now? > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: Informix Dynamic Server w/ AD Version 8.21.... [14852] > > Date: Wed, 11 Feb 2009 20:30:58 -0500 > > > > The user tables you can reorg by exporting their data, truncating the > table, > > and reloading the data - or create a new table, copy the data from the > old > > table to the new one, rename both tables, drop the old one. The 'system' > > table is actually the set of tablespace pages or each table's header page > > for the entire server. The only way to reorg the tablespace tablespace is > > to unload all databases, reinitialize the engine, reinitialize all > dbspaces, > > recreate and reload all databases. Not pretty. > > > > Art > > > > On Wed, Feb 11, 2009 at 4:58 PM, LARRY SORENSEN <lsorensen25@msn.com> > wrote: > > > > > Here are the extents on the tables in question: > > > > > > Extents Table > > > > > > ------- -------- > > > > > > 446.0 TBLSpace > > > > > > 412.0 agg_grp > > > > > > 305.0 sales_wkly > > > We are running AIX 4.3 with a 4k page size. I am not quite as familiar > with > > > XPS as I am with IDS. I will assume that I am getting close to the > extent > > > limit on those 3 tables. What are my options. As you can see, one of > the > > > tables is a system table and the other 2 are user tables. With the 2 > user > > > tables, can you just do like in IDS and unload, drop/recreate, reload > the > > > tables? What are my options instead. On the system table, I am not sure > > > what > > > to do. I can use as much information as possible. > > > Larry > > > > > > > To: ids@iiug.org > > > > From: miller3@us.ibm.com > > > > Subject: RE: Informix Dynamic Server w/ AD Version 8.21.... [14811] > > > > Date: Mon, 9 Feb 2009 18:19:49 -0500 > > > > > > > > Larry: > > > > > > > > Your XPS system is probably using a pagesize of 4KB (or higher). Most > XPS > > > > system have a base page size of 4KB. The base page size of an XPS > system > > > > maybe specified when you init the system the first time. > > > > > > > > Most of the manual calculation assume a 2KB pages size.. > > > > > > > > John F. Miller III > > > > STSM, Support Architect > > > > miller3@us.ibm.com > > > > 503-578-5645 > > > > IBM Informix Dynamic Server (IDS) > > > > > > > > ids-bounces@iiug.org wrote on 02/09/2009 02:22:21 PM: > > > > > > > > > I have an instance of IDS 8.21. When looking up information on > maximum > > > > > extents, it lists something low in the 200's maximum for the number > > > > > of extents > > > > > that a table can have. I have a couple of tables with over 400 > > > > > extents and one > > > > > over 300. I have not seen any problems yet, but according to the > > > > > documentation > > > > > that I have read, the database should have complained a long time > ago. > > > > Does > > > > > anyone of any current information on this as to why the database > has > > > not > > > > > complained and on what I can expect for max extents? > > > > > > > > > > Larry > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (art@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > > with which I am associated either explicitly or implicitly. Neither do > > those opinions reflect those of other individuals affiliated with any > entity > > with which I am affiliated nor those of the entities themselves. > > > > --0016e64e9c0eebe04c0462aeabe2 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. --001485f91dd267cc4d0462bbd844
Art, I appreciate your responses. One last thing, hopefully. There is a formula that the reference manual lists to calculate the maximum number of extents for a table. I did not understand it very well. Is there anyway that you could explain it to me? Since I don't have access to the TBLSpace table, I am not sure of columns, indexes, etc. When running 'onutil CHECK RESERVED' it does not show the result other than to say it was checked. The filesystem page size is 4k. To calculate the upper limit on extents for a particular table, use the following set of formulas: vcspace = 8 *vcolumns + 136 tcspace = 4 *tcolumns ixspace = 12 *indexes ixparts = 4 *icolumns extspace = pagesize (vcspace + tcspace + ixspace + ixparts + 84) maxextents = extspace/8 The table can have no more than maxextents extents. vcolumns is the number of columns that contain blob and VARCHAR data. tcolumns is the number of columns in the table. indexes is the number of indexes on the table. icolumns is the number of columns named in those indexes. pagesize is the size of a page reported by onutil CHECK RESERVED. > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: Informix Dynamic Server w/ AD Version 8.21.... [14871] > Date: Thu, 12 Feb 2009 12:14:03 -0500 > > It doesn't appear anywhere. It is a total internal table and you cannot set > the next size for it (nor for normal system tables either). > > Art > > On Thu, Feb 12, 2009 at 11:13 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote: > > > Art, > > > > Also, I can't find the TBLSpace table in either the sysmaster or the user > > database. Where is that located and can I set the next extent for now? > > > > > To: ids@iiug.org > > > From: art.kagel@gmail.com > > > Subject: Re: Informix Dynamic Server w/ AD Version 8.21.... [14852] > > > Date: Wed, 11 Feb 2009 20:30:58 -0500 > > > > > > The user tables you can reorg by exporting their data, truncating the > > table, > > > and reloading the data - or create a new table, copy the data from the > > old > > > table to the new one, rename both tables, drop the old one. The 'system' > > > table is actually the set of tablespace pages or each table's header page > > > for the entire server. The only way to reorg the tablespace tablespace is > > > to unload all databases, reinitialize the engine, reinitialize all > > dbspaces, > > > recreate and reload all databases. Not pretty. > > > > > > Art > > > > > > On Wed, Feb 11, 2009 at 4:58 PM, LARRY SORENSEN <lsorensen25@msn.com> > > wrote: > > > > > > > Here are the extents on the tables in question: > > > > > > > > Extents Table > > > > > > > > ------- -------- > > > > > > > > 446.0 TBLSpace > > > > > > > > 412.0 agg_grp > > > > > > > > 305.0 sales_wkly > > > > We are running AIX 4.3 with a 4k page size. I am not quite as familiar > > with > > > > XPS as I am with IDS. I will assume that I am getting close to the > > extent > > > > limit on those 3 tables. What are my options. As you can see, one of > > the > > > > tables is a system table and the other 2 are user tables. With the 2 > > user > > > > tables, can you just do like in IDS and unload, drop/recreate, reload > > the > > > > tables? What are my options instead. On the system table, I am not sure > > > > what > > > > to do. I can use as much information as possible. > > > > Larry > > > > > > > > > To: ids@iiug.org > > > > > From: miller3@us.ibm.com > > > > > Subject: RE: Informix Dynamic Server w/ AD Version 8.21.... [14811] > > > > > Date: Mon, 9 Feb 2009 18:19:49 -0500 > > > > > > > > > > Larry: > > > > > > > > > > Your XPS system is probably using a pagesize of 4KB (or higher). Most > > XPS > > > > > system have a base page size of 4KB. The base page size of an XPS > > system > > > > > maybe specified when you init the system the first time. > > > > > > > > > > Most of the manual calculation assume a 2KB pages size.. > > > > > > > > > > John F. Miller III > > > > > STSM, Support Architect > > > > > miller3@us.ibm.com > > > > > 503-578-5645 > > > > > IBM Informix Dynamic Server (IDS) > > > > > > > > > > ids-bounces@iiug.org wrote on 02/09/2009 02:22:21 PM: > > > > > > > > > > > I have an instance of IDS 8.21. When looking up information on > > maximum > > > > > > extents, it lists something low in the 200's maximum for the number > > > > > > of extents > > > > > > that a table can have. I have a couple of tables with over 400 > > > > > > extents and one > > > > > > over 300. I have not seen any problems yet, but according to the > > > > > > documentation > > > > > > that I have read, the database should have complained a long time > > ago. > > > > > Does > > > > > > anyone of any current information on this as to why the database > > has > > > > not > > > > > > complained and on what I can expect for max extents? > > > > > > > > > > > > Larry > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > -- > > > Art S. Kagel > > > Oninit (www.oninit.com) > > > IIUG Board of Directors (art@iiug.org) > > > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and > > > do not reflect on my employer, Oninit, the IIUG, nor any other > > organization > > > with which I am associated either explicitly or implicitly. Neither do > > > those opinions reflect those of other individuals affiliated with any > > entity > > > with which I am affiliated nor those of the entities themselves. > > > > > > --0016e64e9c0eebe04c0462aeabe2 > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do@@NL@
As for the tablespace tablespace itself, assume the absolute maximum since there are no indexes and no real columns. The data stored there IS accessible. It's represented by several SMI tables in sysmaster: systabnames, sysptnhdr, sysextents, syskeycols(? not sure of that one, no server handy right now) which display various parts of the partition's basic information. So, on a 4K page the max would be something like: extspace = 4096 - (136 + 4 + 0 + 0 + 84) = 3872 max extents = 3872/8 = 484 FYI, the partition page for the tablespace tablespace is there in those SMI tables. It's the second partition (so the second smallest partnum) in the rootdb space (the first is the database tablespace (or partition) that holds the list of databases on the server - shown in sysdatabases). The partnum is something like 0x00100002 in hex. Art On Thu, Feb 12, 2009 at 1:36 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > Art, > > I appreciate your responses. One last thing, hopefully. There is a formula > that the reference manual lists to calculate the maximum number of extents > for > a table. I did not understand it very well. Is there anyway that you could > explain it to me? Since I don't have access to the TBLSpace table, I am not > sure of columns, indexes, etc. When running 'onutil CHECK RESERVED' it does > not show the result other than to say it was checked. The filesystem page > size > is 4k. > > To calculate the upper limit on extents for a particular table, use the > following > set of formulas: > vcspace = 8 *vcolumns + 136 > tcspace = 4 *tcolumns > ixspace = 12 *indexes > ixparts = 4 *icolumns > extspace = pagesize (vcspace + tcspace + ixspace + ixparts + 84) > maxextents = extspace/8 > The table can have no more than maxextents extents. > > vcolumns is the number of columns that contain blob and VARCHAR > data. > tcolumns is the number of columns in the table. > indexes is the number of indexes on the table. > icolumns is the number of columns named in those indexes. > pagesize is the size of a page reported by onutil CHECK RESERVED. > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: Informix Dynamic Server w/ AD Version 8.21.... [14871] > > Date: Thu, 12 Feb 2009 12:14:03 -0500 > > > > It doesn't appear anywhere. It is a total internal table and you cannot > set > > the next size for it (nor for normal system tables either). > > > > Art > > > > On Thu, Feb 12, 2009 at 11:13 AM, LARRY SORENSEN <lsorensen25@msn.com > >wrote: > > > > > Art, > > > > > > Also, I can't find the TBLSpace table in either the sysmaster or the > user > > > database. Where is that located and can I set the next extent for now? > > > > > > > To: ids@iiug.org > > > > From: art.kagel@gmail.com > > > > Subject: Re: Informix Dynamic Server w/ AD Version 8.21.... [14852] > > > > Date: Wed, 11 Feb 2009 20:30:58 -0500 > > > > > > > > The user tables you can reorg by exporting their data, truncating the > > > table, > > > > and reloading the data - or create a new table, copy the data from > the > > > old > > > > table to the new one, rename both tables, drop the old one. The > 'system' > > > > table is actually the set of tablespace pages or each table's header > page > > > > for the entire server. The only way to reorg the tablespace > tablespace > is > > > > to unload all databases, reinitialize the engine, reinitialize all > > > dbspaces, > > > > recreate and reload all databases. Not pretty. > > > > > > > > Art > > > > > > > > On Wed, Feb 11, 2009 at 4:58 PM, LARRY SORENSEN <lsorensen25@msn.com > > > > > wrote: > > > > > > > > > Here are the extents on the tables in question: > > > > > > > > > > Extents Table > > > > > > > > > > ------- -------- > > > > > > > > > > 446.0 TBLSpace > > > > > > > > > > 412.0 agg_grp > > > > > > > > > > 305.0 sales_wkly > > > > > We are running AIX 4.3 with a 4k page size. I am not quite as > familiar > > > with > > > > > XPS as I am with IDS. I will assume that I am getting close to the > > > extent > > > > > limit on those 3 tables. What are my options. As you can see, one > of > > > the > > > > > tables is a system table and the other 2 are user tables. With the > 2 > > > user > > > > > tables, can you just do like in IDS and unload, drop/recreate, > reload > > > the > > > > > tables? What are my options instead. On the system table, I am not > sure > > > > > what > > > > > to do. I can use as much information as possible. > > > > > Larry > > > > > > > > > > > To: ids@iiug.org > > > > > > From: miller3@us.ibm.com > > > > > > Subject: RE: Informix Dynamic Server w/ AD Version 8.21.... > [14811] > > > > > > Date: Mon, 9 Feb 2009 18:19:49 -0500 > > > > > > > > > > > > Larry: > > > > > > > > > > > > Your XPS system is probably using a pagesize of 4KB (or higher). > Most > > > XPS > > > > > > system have a base page size of 4KB. The base page size of an XPS > > > system > > > > > > maybe specified when you init the system the first time. > > > > > > > > > > > > Most of the manual calculation assume a 2KB pages size.. > > > > > > > > > > > > John F. Miller III > > > > > > STSM, Support Architect > > > > > > miller3@us.ibm.com > > > > > > 503-578-5645 > > > > > > IBM Informix Dynamic Server (IDS) > > > > > > > > > > > > ids-bounces@iiug.org wrote on 02/09/2009 02:22:21 PM: > > > > > > > > > > > > > I have an instance of IDS 8.21. When looking up information on > > > maximum > > > > > > > extents, it lists something low in the 200's maximum for the > number > > > > > > > of extents > > > > > > > that a table can have. I have a couple of tables with over 400 > > > > > > > extents and one > > > > > > > over 300. I have not seen any problems yet, but according to > the > > > > > > > documentation > > > > > > > that I have read, the database should have complained a long > time > > > ago. > > > > > > Does > > > > > > > anyone of any current information on this as to why the > database > > > has > > > > > not > > > > > > > complained and on what I can expect for max extents? > > > > > > > > > > > > > > Larry > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > > > > > > > > Forum Note: Use "Reply" to post a response in the discussion > forum. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > Forum Note: Use "Reply" to post a response in the discussion > forum. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Rep