sysdistrib extents reach max
Poster found that the sysdistrib system catalog table had hit the maximum number of extents and asked how to reduce them. Replies: catalog tables can't be reorganised in place, so the practical fix is to unload/drop/recreate and reload the database (dbexport/dbimport), optionally editing the dbimport SQL to ALTER TABLE ... NEXT SIZE on the catalog tables (e.g. sysdistrib NEXT SIZE 9000) so fewer extents are allocated; moving the catalog to a dbspace with a larger (4K+) page size also raises the extent limit. Dropping distributions first was also suggested. Art Kagel conceded his initial claim that NEXT SIZE can't be changed on catalog tables was wrong. No confirmation from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Found that database.sysdistrib table extent numbers reach max.How its possible to reduce the extent numbers? Thanks in advance.please advise. rgds Raja
You cannot reorganize system catalog tables and you cannot change their first and next extent sizes. The only solution is to export the database, drop it, and recreate and reload it. That may not do the trick, however, If you have a newer version of IDS that supports multiple page sizes, you can move the system catalog into a dbspace with 4K or larger pages which will effectively more than double the number of extents that catalog tables (and other tables in the database's default dbspace) can have. 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. On Tue, Apr 7, 2009 at 9:16 PM, SCHIL ACHE <penfriend5@yahoo.co.uk> wrote: > Hi, > > Found that database.sysdistrib table extent numbers reach max.How its > possible > to reduce the extent numbers? > > Thanks in advance.please advise. > > rgds > Raja > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e645a3d8efe4490467013483
I apologize for correcting... You can change next extent sizes. In fact for systems like Baan you may have to do it. The problem here is that once you've reached the limit I don't see a way to "eliminate" some extents... This is something that needs to be avoided... I'm curious for xC4... Regards. On Wed, Apr 8, 2009 at 2:39 AM, Art Kagel <art.kagel@gmail.com> wrote: > You cannot reorganize system catalog tables and you cannot change their > first and next extent sizes. The only solution is to export the database, > drop it, and recreate and reload it. That may not do the trick, however, > If you have a newer version of IDS that supports multiple page sizes, you > can move the system catalog into a dbspace with 4K or larger pages which > will effectively more than double the number of extents that catalog tables > (and other tables in the database's default dbspace) can have. > > 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. > > On Tue, Apr 7, 2009 at 9:16 PM, SCHIL ACHE <penfriend5@yahoo.co.uk> wrote: > > > Hi, > > > > Found that database.sysdistrib table extent numbers reach max.How its > > possible > > to reduce the extent numbers? > > > > Thanks in advance.please advise. > > > > rgds > > Raja > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0016e645a3d8efe4490467013483 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001636c5ab5ba922a7046713c77d
Hi,
I'm not sure can you drop statistics by
update statistics ... drop distribution .. ( please check syntax again)
and then "alter table sysdistrib modify next size XXX" to change the next sizeto bigger size.
BRGs,
Jakkrit A.
________________________________
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Wednesday, April 8, 2009 8:39:49 AM
Subject: Re: sysdistrib extents reach max [15469]
You cannot reorganize system catalog tables and you cannot change their
first and next extent sizes. The only solution is to export the database,
drop it, and recreate and reload it. That may not do the trick, however,
If you have a newer version of IDS that supports multiple page sizes, you
can move the system catalog into a dbspace with 4K or larger pages which
will effectively more than double the number of extents that catalog tables
(and other tables in the database's default dbspace) can have.
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.
On Tue, Apr 7, 2009 at 9:16 PM, SCHIL ACHE <penfriend5@yahoo.co.uk> wrote:
> Hi,
>
> Found that database.sysdistrib table extent numbers reach max.How its
> possible
> to reduce the extent numbers?
>
> Thanks in advance.please advise.
>
> rgds
> Raja
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e645a3d8efe4490467013483
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
On Wed, Apr 8, 2009 at 2:39 AM, Art Kagel <art.kagel@gmail.com> wrote:
> You cannot reorganize system catalog tables and you cannot change
> their
> first and next extent sizes. The only solution is to export the database,
> drop it, and recreate and reload it. That may not do the trick, however,
> If you have a newer version of IDS that supports multiple page sizes, you
> can move the system catalog into a dbspace with 4K or larger pages which
> will effectively more than double the number of extents that catalog tables
> (and other tables in the database's default dbspace) can have.
>
> Art S. Kagel
I like Art's idea of using 4k page size. If your thinking about a reorg., you
can modify the dbimport sql but remember to make a copy of it before making
any changes. I find the following to be helpful in reducing the initial number
of extents:
{ DATABASE system delimiter | }
grant dba to "informix";
grant dba to "root";
grant dba to "public";
ALTER TABLE sysattrtypes NEXT SIZE 64;
ALTER TABLE syscoldepend NEXT SIZE 256;
ALTER TABLE syscolumns NEXT SIZE 900;
ALTER TABLE sysconstraints NEXT SIZE 1200;
ALTER TABLE sysdefaults NEXT SIZE 64;
ALTER TABLE sysdistrib NEXT SIZE 9000 ;
ALTER TABLE sysfragments NEXT SIZE 800;
ALTER TABLE sysindices NEXT SIZE 800;
ALTER TABLE sysobjstate NEXT SIZE 1200;
ALTER TABLE sysprocauth NEXT SIZE 64;
ALTER TABLE sysprocbody NEXT SIZE 1600;
ALTER TABLE sysprocedures NEXT SIZE 256;
ALTER TABLE sysprocplan NEXT SIZE 1400;
ALTER TABLE systabauth NEXT SIZE 256;
ALTER TABLE systables NEXT SIZE 300;
At the end of the sql I resize again to something less aggressive. Depending
on the size of the database, your "next size" could be different. Your version
of Informix might not have all these tables.
Ya, CLEARLY (as my teenagers say) I was mistaken, you CAN change the next
size of the system catalog tables.
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.
On Thu, Apr 9, 2009 at 7:37 AM, RALPH GENTRY <gentrym@staples.com> wrote:
> On Wed, Apr 8, 2009 at 2:39 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > You cannot reorganize system catalog tables and you cannot change
> > their
> > first and next extent sizes. The only solution is to export the database,
> > drop it, and recreate and reload it. That may not do the trick, however,
> > If you have a newer version of IDS that supports multiple page sizes, you
> > can move the system catalog into a dbspace with 4K or larger pages which
> > will effectively more than double the number of extents that catalog
> tables
> > (and other tables in the database's default dbspace) can have.
> >
> > Art S. Kagel
>
> I like Art's idea of using 4k page size. If your thinking about a reorg.,
> you
> can modify the dbimport sql but remember to make a copy of it before making
> any changes. I find the following to be helpful in reducing the initial
> number
> of extents:
>
> { DATABASE system delimiter | }
>
> grant dba to "informix";
> grant dba to "root";
> grant dba to "public";>
> ALTER TABLE sysattrtypes NEXT SIZE 64;
> ALTER TABLE syscoldepend NEXT SIZE 256;
> ALTER TABLE syscolumns NEXT SIZE 900;
> ALTER TABLE sysconstraints NEXT SIZE 1200;
> ALTER TABLE sysdefaults NEXT SIZE 64;
> ALTER TABLE sysdistrib NEXT SIZE 9000 ;
> ALTER TABLE sysfragments NEXT SIZE 800;
> ALTER TABLE sysindices NEXT SIZE 800;
> ALTER TABLE sysobjstate NEXT SIZE 1200;
> ALTER TABLE sysprocauth NEXT SIZE 64;
> ALTER TABLE sysprocbody NEXT SIZE 1600;
> ALTER TABLE sysprocedures NEXT SIZE 256;
> ALTER TABLE sysprocplan NEXT SIZE 1400;
> ALTER TABLE systabauth NEXT SIZE 256;
> ALTER TABLE systables NEXT SIZE 300;>
> At the end of the sql I resize again to something less aggressive.
> Depending
> on the size of the database, your "next size" could be different. Your
> version
> of Informix might not have all these tables.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e646161a29ba75046751b0fc