Extent size of system catalogs
Andy was migrating an IDS instance to a new box and asked whether it's safe/advisable to use ALTER TABLE ... NEXT SIZE on system catalog tables that had badly fragmented extents. Consensus: yes — changing the NEXT SIZE on catalog tables is one of the few DDL operations IBM sanctions on the system catalogs, and it's best done right after CREATE DATABASE, before other tables exist. It only affects future extents; catalog tables can't be reorganised with ALTER FRAGMENT, so an already-fragmented catalog requires dbexport/dbimport (editing the schema file to set NEXT SIZE first).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am about to migrate an IDS instance which has severe extent fragmentation on some of the system catalogs. Is it good practice to run ALTER TABLE ... NEXT SIZE on these or is there another preferred way? We'll be going to IDS 10 (because the application hasn't been certified on 11 yet). Thanks Andy
Yes, it is. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of ANDY KENT Sent: Friday, December 07, 2007 6:12 AM To: ids@iiug.org Subject: Extent size of system catalogs [10642] I am about to migrate an IDS instance which has severe extent fragmentation on some of the system catalogs. Is it good practice to run ALTER TABLE ... NEXT SIZE on these or is there another preferred way? We'll be going to IDS 10 (because the application hasn't been certified on 11 yet). Thanks Andy **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
ANDY KENT wrote:
> I am about to migrate an IDS instance which has severe extent fragmentation
on
> some of the system catalogs.
>
> Is it good practice to run ALTER TABLE ... NEXT SIZE on these or is there
> another preferred way? We'll be going to IDS 10 (because the application
> hasn't been certified on 11 yet).
>
Altering the NEXT SIZE will only set the next extent size for the table
it will not reorganize it. You also have to:
ALTER FRAGMENT ON TABLE <mytable> INIT IN <dbspace or fragmentationexpression>;
The dbspace you reorg into can be the same dbspace the table already
exists in if there's space or a different one.
The alternative is to export the data or rename the table, create a new
table with the desired extent sizing and reload/copy the data to the new
table.
Art S. Kagel
> Thanks
> Andy
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Art,
He's talking about system catalogues. You have fragmented these in the
past?
j.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Art
S. Kagel (Oninit LLC)
Sent: Friday, December 07, 2007 8:17 AM
To: ids@iiug.org
Subject: Re: Extent size of system catalogs [10644]
ANDY KENT wrote:
> I am about to migrate an IDS instance which has severe extent
fragmentation
on
> some of the system catalogs.
>
> Is it good practice to run ALTER TABLE ... NEXT SIZE on these or is there
> another preferred way? We'll be going to IDS 10 (because the application
> hasn't been certified on 11 yet).
>
Altering the NEXT SIZE will only set the next extent size for the table
it will not reorganize it. You also have to:
ALTER FRAGMENT ON TABLE <mytable> INIT IN <dbspace or fragmentationexpression>;
The dbspace you reorg into can be the same dbspace the table already
exists in if there's space or a different one.
The alternative is to export the data or rename the table, create a new
table with the desired extent sizing and reload/copy the data to the new
table.
Art S. Kagel
> Thanks
> Andy
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
It's a migration onto a brand new box so we have the chance for a clean start. The ALTER TABLEs would be the next thing after CREATE DATABASE. My concern is whether it's advisable to run DDL on system catalogs. Andy
An alter next size is ok to do on system catalogues - especially in an environment where you know what that size is going to be from a previous box. The best you can hope for there is 2 extents, but that's a lot better than 30-odd or whatever. Mind you, this is the only DDL I consider safe to do to system catalogues. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of ANDY KENT Sent: Friday, December 07, 2007 8:44 AM To: ids@iiug.org Subject: Re: Extent size of system catalogs [10646] It's a migration onto a brand new box so we have the chance for a clean start. The ALTER TABLEs would be the next thing after CREATE DATABASE. My concern is whether it's advisable to run DDL on system catalogs. Andy **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Jack is absolutely right. And you are now doing what I have been doing for
years. I always perform change the next extent size of system tables to
512kb. You cannot reorg these tables; you can only dbexport and dbimport
your database if one of your system tables reaches maximum extents.
Take care.
Clifton M. Bean
Informix DBA / AIX System Admin
Currency Technics & Metrics
Phone: (972) 812-1411 x244
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack
Parker
Sent: Friday, December 07, 2007 8:09 AM
To: ids@iiug.org
Subject: RE: Extent size of system catalogs [10648]
An alter next size is ok to do on system catalogues - especially in an
environment where you know what that size is going to be from a previous
box. The best you can hope for there is 2 extents, but that's a lot better
than 30-odd or whatever.
Mind you, this is the only DDL I consider safe to do to system catalogues.
j.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
ANDY KENT
Sent: Friday, December 07, 2007 8:44 AM
To: ids@iiug.org
Subject: Re: Extent size of system catalogs [10646]
It's a migration onto a brand new box so we have the chance for a clean
start.
The ALTER TABLEs would be the next thing after CREATE DATABASE. My concern
is
whether it's advisable to run DDL on system catalogs.
Andy
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Jack Parker wrote:
> Art,
>
> He's talking about system catalogues. You have fragmented these in the
> past?
>
AARRGGHH!! Shouldn't answer posts before I have my coffee!
Non-sequitor, clearly. Sorry folk.
Yeah, can't defrag system catalog tables. If they are in danger of
running out of extents you'll have to dbexport/dbimport the database(s)
to defrag the catalog, just edit the schema file to set the NEXT SIZE of
the catalog tables BEFORE any other tables are created.
Yes, one can reset the NEXT SIZE for the catalog tables using the
command listed in the original post. Nothing else is needed. It just
only effects tables created after that.
Art S. Kagel
> j.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Art
> S. Kagel (Oninit LLC)
> Sent: Friday, December 07, 2007 8:17 AM
> To: ids@iiug.org
> Subject: Re: Extent size of system catalogs [10644]
>
> ANDY KENT wrote:
>
>> I am about to migrate an IDS instance which has severe extent
>>
> fragmentation
> on
>
>> some of the system catalogs.
>>
>> Is it good practice to run ALTER TABLE ... NEXT SIZE on these or is there
>> another preferred way? We'll be going to IDS 10 (because the application
>> hasn't been certified on 11 yet).
>>
>>
>
> Altering the NEXT SIZE will only set the next extent size for the table
> it will not reorganize it. You also have to:
> ALTER FRAGMENT ON TABLE <mytable> INIT IN <dbspace or fragmentation> expression>;
>
> The dbspace you reorg into can be the same dbspace the table already
> exists in if there's space or a different one.
>
> The alternative is to export the data or rename the table, create a new
> table with the desired extent sizing and reload/copy the data to the new
> table.
>
> Art S. Kagel
>
>
>> Thanks
>> Andy
>>
>>
>>
>>
> ****************************************************************************
> ***
>
>> 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.
>
>
ANDY KENT wrote: > It's a migration onto a brand new box so we have the chance for a clean start. > The ALTER TABLEs would be the next thing after CREATE DATABASE. My concern is > whether it's advisable to run DDL on system catalogs. > This one DDL has the stamp of approval from the IBM Informix team. There are very few of those, but this isw one of the ones that are OK. Art S. Kagel > Andy > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
You be fine doing this Paul Watson Tel: +1 913-400-2620 Mob: +1 913-387-7529 Web: www.oninit.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > ANDY KENT > Sent: 07 December 2007 07:44 > To: ids@iiug.org > Subject: Re: Extent size of system catalogs [10646] > > It's a migration onto a brand new box so we have the chance for a clean > start. > The ALTER TABLEs would be the next thing after CREATE DATABASE. My > concern is > whether it's advisable to run DDL on system catalogs. > > Andy > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.