IDS Forum

Re: Re.systables.

Posted By:
Date:

Hi,

hmm. It depends on what you are doing with your database.

systables contains one row for each table in your database (+ some static
overhead for the system catalog itself), and syscolumns holds, you guessed
it,
a row for each column (of each table) in your database.

If your database schema is going to be rather static, i.e. you have a
relatively
fixed number of tables and you are not adding lots of columns to these
existing tables, then there's no real need to change extent sizes for
systables and/or syscolumns.

If your database schema turns out to be rather dynamic, especially if you
(plan to) add lots of tables and columns over time, then changing the
extent
sizes for those two tables might help. But even then, with some
calculations
you can make an educated guess at what would be sensible ...

If you have 90 GB of data (which I assume is concentrated in rather few
tables),
then it is much more important to increase the extent size for those
tables
(rather than for systables). Since you know already, how big the database
is
going to be, then I would change the extent sizes upon creation of the
database.

[ BTW:
a) If extents for a single table are allocated sequentially (happens
especially
during data loading), then these consecutive extents will be treated
as one big
extent (not many small ones).
b) There is a feature "automatic increase of extent size". IDS detects
that for
a table there've been that many extent allocations (don't remember the
absolut
number) and then doubles the next extent size automatically for future
allocations.
This happens repeatedly.
c) If you do concurrent data load into different tables, then you are
most likely
to get chunks fragmented by small extents. This is the scenario to
avoid by
defining a big enough first extent size - in fact at best so big that
all the existing
data to be loaded fits into it. ]

Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich
Data Management Solutions





"Clifton M. Bean" <cmbean@sbcglobal.net>
Sent by: forum.subscriber@iiug.org
24.02.2003 04:32


To: ids@iiug.org
cc:
Subject: Re.systables. [473]



There are quite a few tables whose next extent size you should alter after
the "create database" statement; amongst them are systables and syscolumns
(others depend upon your environment). I normally change the next extent
size from its default of 16 to 1024 give them plenty of room to grow into.
Since each table can only have a certain number of extents, the larger the
next extent the better. Having the larger size will also keep the
fragmentation of these tables down.

Take care.
Clifton

----- Original Message -----
From: "Sushil Shir...." <sushilps@hotmail.com>
To: <ids@iiug.org>
Sent: Sunday, February 23, 2003 5:42 PM
Subject: Re.systables. [472]


> Hi Everybody,
>
> Do we have to alter the extent size for systables, database with
> probably 90 GB size.
> If yes when is the right time, when we create database or when the
> size of the database increases.
>
> Thanks in advance.
> Sushil...

Messages In This Thread

• Re.systables.
  Sushil Shirodkar -- Sunday, 23 February 2003, at 6:42 p.m.
  • Re.systables.
    Clifton M. Bean -- Sunday, 23 February 2003, at 10:32 p.m.
    • Re: Re.systables.
      Martin Fuerderer -- Monday, 24 February 2003, at 4:23 a.m.
      • Re: Re.systables.
        Andy Lennard -- Monday, 24 February 2003, at 7:43 a.m.
        • Re: Re.systables.
          Sushil Shirodkar -- Monday, 24 February 2003, at 4:54 p.m.
    • Re: Re.systables.
      Sushil Shirodkar -- Monday, 24 February 2003, at 7:33 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.