IDS Forum

Re: Re.systables.

Posted By:
Date:

Hi,

Thanks guys for all your valuable suggestions, I will keep in
mind the guidance/instruction related to extents of systables
its very rare were we change the structure of the database/tables.

Right now we don't have any issues with our normal/apps tables
because all of our tables are below 8 extents, in fact we
recently re-arranged all the big tables to extent 1 (I think the
extents double on every 17th extent automatically).

Thanks again.
Sushil...





>From: Martin Fuerderer <MARTINFU@de.ibm.com>
>To: "Clifton M. Bean" <cmbean@sbcglobal.net>, sushilps@hotmail.com
>CC: forum.subscriber@iiug.org, ids@iiug.org
>Subject: Re: Re.systables. [473]
>Date: Mon, 24 Feb 2003 02:20:59 -0700
>MIME-Version: 1.0
>Received: from e31.co.us.ibm.com ([32.97.110.129]) by
>mc8-f39.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Mon, 24 Feb
>2003 01:21:08 -0800
>Received: from westrelay02.boulder.ibm.com (westrelay02.boulder.ibm.com
>[9.17.195.11])by e31.co.us.ibm.com (8.12.7/8.12.2) with ESMTP id
>h1O9L67b058984;Mon, 24 Feb 2003 04:21:06 -0500
>Received: from d03nm028.boulder.ibm.com (d03av02.boulder.ibm.com
>[9.17.193.82])by westrelay02.boulder.ibm.com (8.12.3/NCO/VER6.5) with ESMTP
>id h1O9L57f085716;Mon, 24 Feb 2003 02:21:06 -0700
>X-Message-Info: dHZMQeBBv44lPE7o4B5bAg==
>X-Mailer: Lotus Notes Release 5.0.7 March 21, 2001
>Message-ID: <OFF3EA342F.51DDBF3E-ONC1256CD7.002D4C28@us.ibm.com>
>X-MIMETrack: Serialize by Router on D03NM028/03/M/IBM(Release 6.0
>[IBM]|December 16, 2002) at 02/24/2003 02:21:05,Serialize complete at
>02/24/2003 02:21:05
>Return-Path: MARTINFU@de.ibm.com
>X-OriginalArrivalTime: 24 Feb 2003 09:21:08.0771 (UTC)
>FILETIME=[10A07F30:01C2DBE6]
>
>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...
>


_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail

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.