IDS Forum

Fwd: RE: Removing excessive extents

Posted By: ART KAGEL, BLOOMBERG/ 65E 55TH
Date: Wednesday, 4 June 2003, at 3:50 p.m.

In Response To: Fwd: RE: Removing excessive extents (John Bejarano )

There is no way to reorg a table with zero downtime. If you need to reorg a
table that is needed live or with minimal downtime the only option is:

1. Create a new table with the same structure and desired extent sizing &
location
2. Copy all rows from the original table to the new one, this is the time
consumer and the last thing you can do without locking out users
3. Lock the original table in exclusive mode
4. Drop all constraints and indexes on the original table (will also destroy any
referencing foreign keys on other tables)
5. Rename the original table to a new name
6. Unlock the original table, the lock's not needed anymore
7. Rename the new table to the original name
8. Lock the table
9. Recreate the indexes on the table
10. Recreate constraints on the table
11. Recreate referencing foreign keys pointing to the table
12. If the original table was active during the copy you may have to recover
missing or updated data at this point. (If this step is difficult given your
table structure and/or the nature of the transactions you may need to add audit
triggers to the table before starting so you can track which rows to delete,
copy, and/or update afterward.)

Art S. Kagel

----- Original Message -----
From: John Bejarano <jbejaran@yahoo.com>
At: 6/ 4 15:35

Yes, but doesn't the alter index...to cluster
statement put an exclusive lock on the table? Seems
that you still need downtime to do this (unless you
can operate with this particular table unavailable for
a while which doesn't apply at my company.)

--John Bejarano
Shutterfly.

--- RPhillips@ce-a.com wrote:
> Date: Wed, 4 Jun 2003 13:31:19 -0400 (EDT)
> To: ids@iiug.org
> From: RPhillips@ce-a.com
> Subject: RE: Removing excessive extents [1277]
>
> You mean like this
> (http://www.iiug.org/forums/ids/index.cgi?read=377)
> Which
> I'm not sure why it is supposed to work.
>
> "I have used the following technique on some of my
> tables when I could not
> get enough down time to do a "proper" resizing
> (unload, change schema, drop
> table, rebuild, load).
>
>
> alter the tables next extent size so that it is
> large enough to hold the
> whole table. Then alter one of the indexes to
> cluster and then drop the
> cluster.
>
>
> it would look like this
>
> alter table table_x modify next size 123456;
> alter index table_x_index to cluster;
> alter index table_x_index to not cluster;"
>
> -----Original Message-----
> From: rkusenet [mailto:rkusenet@sympatico.ca]
> Sent: Wednesday, June 04, 2003 1:12 PM
> To: RPhillips@ce-a.com
> Cc: ids@iiug.org
> Subject: Re: Removing excessive extents [1275]
>
>
> Another easier way can be to create a cluster index
> on hospital table
> and then drop that cluster index.
> when u do that, it will relocate data from its
> present position,
> which would automatically free extents.
>
> ----- Original Message -----
> From: <RPhillips@ce-a.com>
> To: <ids@iiug.org>
> Sent: Wednesday, June 04, 2003 12:41
> Subject: RE: Removing excessive extents [1275]
>
>
> > Could you give me an overview of how this works.
> The only documentation I
> > have on fragments is regarding creating fragments
> in different dbspaces,
> and
> > deleteing them from different dbspaces.
> >
> > Also I did a dbschema on one of the tables that
> have too many extents and
> it
> > didn't give me the size of the extent...
> >
> > { TABLE "informix".hospitals row size = 854 number
> of columns = 34 index
> > size = 12
> > }
> > create table "informix".hospitals
> > (
> > hospital_id serial not null ,
> > hospital_name char(20),
> > hospital_full_name char(50),
> > street char(62),
> > street2 char(62),
> > city char(30),
> > state char(2),
> > zip char(10),
> > phone char(14),
> > fax char(14),
> > mstreet char(62),
> > mstreet2 char(62),
> > mcity char(30),
> > mstate char(2),
> > mzip char(10),
> > use_mail_address char(1),
> > region_id integer,
> > district_id integer,
> > provider_number char(50),
> > contract_start date,
> > contract_end date,
> > ob char(1),
> > comments char(255),
> > in_percent decimal(7,4),
> > out_percent decimal(7,4),
> > profee_in_percent decimal(7,4),
> > profee_out_percent decimal(7,4),
> > last_modified date,
> > iscurrent char(1),
> > modified_by char(3),
> > flat_rate money(16,2),
> > ptreecode char(5),
> > terms integer,
> > email varchar(50),
> > unique (hospital_id)
> > );
> > revoke all on "informix".hospitals from "public";
> >
> > -----Original Message-----
> > From: rkusenet [mailto:rkusenet@sympatico.ca]
> > Sent: Wednesday, June 04, 2003 12:25 PM
> > To: ids@iiug.org; RPhillips@ce-a.com
> > Subject: Re: Removing excessive extents [1273]
> >
> >
> >
> > ----- Original Message -----
> > From: <RPhillips@ce-a.com>
> > To: <ids@iiug.org>
> > Sent: Wednesday, June 04, 2003 11:48
> > Subject: Removing excessive extents [1273]
> >
> >
> > > I have two tables that I want to reorganize to
> reduce the number of
> > extents
> > > that they have. I am clear on how to do this by
> copying the data in the
> > > table, dropping the table and then recreating it
> with a larger extent
> size
> > > and then putting the data back.
> > >
> > > My question is what happens to tables that have
> a foreign key based on
> > this
> > > table when I drop it?
> >
> > Informix will prevent you from dropping.
> >
> > If reducing the number of extent is your goal,
> then use alter fragment
> >
> >
>

Messages In This Thread

• Removing excessive extents
  RPhillips@ce-a.com -- Wednesday, 4 June 2003, at 11:48 a.m.
  • RE: Removing excessive extents
    RPhillips@ce-a.com -- Wednesday, 4 June 2003, at 12:41 p.m.
    • Re: Removing excessive extents
      rkusenet -- Wednesday, 4 June 2003, at 1:16 p.m.
    • RE: Removing excessive extents
      RPhillips@ce-a.com -- Wednesday, 4 June 2003, at 1:30 p.m.
      • Fwd: RE: Removing excessive extents
        John Bejarano -- Wednesday, 4 June 2003, at 2:40 p.m.
        • Fwd: RE: Removing excessive extents
          ART KAGEL, BLOOMBERG/ 65E 55TH -- Wednesday, 4 June 2003, at 3:50 p.m.
      • RE: Removing excessive extents
        Mark Scranton -- Thursday, 5 June 2003, at 11:19 a.m.
        • RE: Removing excessive extents
          ART KAGEL, BLOOMBERG/ 65E 55TH -- Thursday, 5 June 2003, at 12:45 p.m.
    • RE: Removing excessive extents
      ART KAGEL, BLOOMBERG/ 65E 55TH -- Wednesday, 4 June 2003, at 2:19 p.m.
      • RE: Removing excessive extents
        Johann Eggers -- Thursday, 5 June 2003, at 3:40 a.m.
    • RE: Removing excessive extents
      John Carlson -- Wednesday, 4 June 2003, at 3:16 p.m.
  • Re: Removing excessive extents
    ART KAGEL, BLOOMBERG/ 65E 55TH -- Wednesday, 4 June 2003, at 6:49 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.