server1 - Informix Database Space has exc
User received "excessive table extents" warning in Informix 9.40.UC6. The issue occurs when a table space accumulates too many extents, potentially preventing new table creation. Solutions provided: (1) unload/drop/recreate table with larger EXTENT SIZE and NEXT SIZE, (2) use ALTER TABLE NEXT SIZE with ALTER FRAGMENT, or (3) manually create new table with wider extents and copy data.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, I can't find a document concerning this type of message from my DB... "servername - Informix Database Space <dbname> has excessive table extents." Version: IDS 9.4 SunOS 5.7 Anyone who can explain this message? Is it critical?
Original post: Hi All, I can't find a document concerning this type of message from my DB... "servername - Informix Database Space <dbname> has excessive table extents." Version: IDS 9.4 SunOS 5.7 Anyone who can explain this message? Is it critical? Response: That's the exact message going into the MSGPATH file, or where is that message going? I tried to search our source for that string, but I didn't find it, so that's why I'm asking if it's exact. If you want to take the message literally, I'd gues that <dbname> wasn't a database, but rather a dbspace name, and if that's true it sounds like the tblspace tblspace for that dbspace has gotten really big (so you have 1 big dbspace with a lot of tables in it). That's a lot of guessing on my part, but that could be what it means. If that table has a lot of extents, it could become an issue if the partition page gets full such that it can't grow anymore...if that were to happen, then you would no longer be able to create any new tables in the dbspace without 1st dropping some so their partition pages could be reused. Again, this is just a guess based on what I think the message means since I didn't actually find it in our source code. Also knowing the exact version, not just 9.4 might help on that as well as I didn't go check every version 9.4 that we have so it might be a new message that wasn't in the version I was looking in. Jacques Renaut IBM Informix Advanced Support APD Team
my issue is "excessive table extents". IBM Informix Dynamic Server Version 9.40.UC6 SunOS 5.7 Generic_106541-42 sun4u sparc SUNW,Ultra-80 What is the best option i can do in order for me to reorg the data? is there any command to reorg data?
In 9.40 you have two options: 1. Unload the data, drop the table, recreate it with bigger EXTENT SIZE and NEXT SIZE settings, reload the data, or 2. ALTER TABLE NEXT SIZE <nkb>; ALTER FRAGMENT...INIT IN ... #2 will be faster but you need enough logical log space to be able to process the rebuild without triggering a long transaction rollback and enough space in the target dbspace(s) to hold the new copy of the table (the original will not drop until the entire copy is completed). 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 Wed, Jun 5, 2013 at 6:11 AM, JACK PAPA <informix2009@gmail.com> wrote: > my issue is "excessive table extents". > > IBM Informix Dynamic Server Version 9.40.UC6 > SunOS 5.7 Generic_106541-42 sun4u sparc SUNW,Ultra-80 > > What is the best option i can do in order for me to reorg the data? > is there any command to reorg data? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c2e93c7d44be04de65b0ef
OK, there's a third option: Manually create a new table with wider extents, copy the data from the existing tablet o the new one using whatever method you like (I like my dbcopy utility - very fast and no long transaction worries), drop the original table, rename the new one, and fix up any foreign keys, views, and procedures referencing the table. 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 Wed, Jun 5, 2013 at 6:31 AM, Art Kagel <art.kagel@gmail.com> wrote: > In 9.40 you have two options: > > 1. Unload the data, drop the table, recreate it with bigger EXTENT SIZE > > and NEXT SIZE settings, reload the data, or > > 2. ALTER TABLE NEXT SIZE <nkb>; ALTER FRAGMENT...INIT IN ... > > #2 will be faster but you need enough logical log space to be able to > process the rebuild without triggering a long transaction rollback and > enough space in the target dbspace(s) to hold the new copy of the table > (the original will not drop until the entire copy is completed). > > 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 Wed, Jun 5, 2013 at 6:11 AM, JACK PAPA <informix2009@gmail.com> wrote: > > > my issue is "excessive table extents". > > > > IBM Informix Dynamic Server Version 9.40.UC6 > > SunOS 5.7 Generic_106541-42 sun4u sparc SUNW,Ultra-80 > > > > What is the best option i can do in order for me to reorg the data? > > is there any command to reorg data? > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c2e93c7d44be04de65b0ef > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c2fdecc85b6304de65c11f
Hi,
In case you run into a TBLSPACE out of extents, there is no way to reorganize
this
but to put databases into a different tablespace using dbexport/drop/dbimport.
You cannot create new tables/databases in this dbspace when the limit is
reached.
Check your extents using the folling query:
database sysmaster;
set isolation to dirty read;select
n.tabname[1,18] as _table,
n.dbsname[1,18] as _db,
h.nextns as _extents,
round(npused*sh_pagesize/1024/1024 , 2) as MB, nrows from sysptnhdr
h, systabnames n, sysshmvals s where h.partnum = n.partnum and
h.nextns > 80 order by 3 desc
If you get output for _table TBLSpace, _db yourdbs with a number of > 200,
then this is your problem.
You cannot make the TBLSpace bigger, only while setting up the dbspace.
For rootdbs, there is an option on onconfig (TBLTBLFIRST, TBLTBLNEXT), which
you can modify only on initialisation of the instance. this implies a complete
unload/load.
Marcus Haarmann
----- Ursprüngliche Mail -----
Von: "JACK PAPA" <informix2009@gmail.com>
An: ids@iiug.org
Gesendet: Mittwoch, 5. Juni 2013 12:11:31
Betreff: Re: server1 - Informix Database Space has exc [30426]
my issue is "excessive table extents".
IBM Informix Dynamic Server Version 9.40.UC6
SunOS 5.7 Generic_106541-42 sun4u sparc SUNW,Ultra-80
What is the best option i can do in order for me to reorg the data?
is there any command to reorg data?
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.