Online table reorg
Hi, is there a way to do an online table reorg in IDS 11.5 We currently have a huge table that we would like to reorg while users are still connected to the database. Is there a way to do it in IDS 1.5? Thanks _________________________________________________________________ More storage. Better anti-spam and antivirus protection. Hotmail makes it simple. http://go.microsoft.com/?linkid=9671357
If you have 11.50.xC4 or later you can use the Admin API to pack the table's
rows and aggregate all of the free space at the end of the table's extents:
EXECUTE FUNCTION task(table repack, table_name, database_name,owner_name);
After packing the table if you want to release the unused space for other
tables to use you can optionally shrink the table's extents:
EXECUTE FUNCTION admin(table shrink, table_name, database_name,owner_name);
The two operations can be combined into a single command:
EXECUTE FUNCTION admin(table repack shrink, table_name, database_name,owner_name);
These operations can optionally be done with the table locked by using
'repack_offline' intead of 'repack' otherwise the table remains online and
available to users during the packing operation. When you pack online there
is a possibility that new rows inserted during the packing run will not be
packed.
See the Administrator's Guide for details.
Art
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 Fri, Aug 28, 2009 at 9:28 AM, Georges Martin <
georges_martin_1@hotmail.com> wrote:
> Hi,
>
> is there a way to do an online table reorg in IDS 11.5
>
> We currently have a huge table that we would like to reorg while users are
> still connected to the database.
>
> Is there a way to do it in IDS 1.5?
>
> Thanks
>
> _________________________________________________________________
> More storage. Better anti-spam and antivirus protection. Hotmail makes it
> simple.
> http://go.microsoft.com/?linkid=9671357
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174760aa88e5190472342224
Hi Art,
would that procedure also reduce the number of extents?
Regards,
Reinhard.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On
> Behalf Of Art
> Kagel
> Sent: Friday, August 28, 2009 4:03 PM
> To: ids@iiug.org
> Subject: Re: Online table reorg [16806]
>
>
> If you have 11.50.xC4 or later you can use the Admin API to
> pack the table's
> rows and aggregate all of the free space at the end of the
> table's extents:
>
> EXECUTE FUNCTION task("table repack", "table_name", "database_name",> "owner_name");
>
> After packing the table if you want to release the unused
> space for other
> tables to use you can optionally shrink the table's extents:
>
> EXECUTE FUNCTION admin("table shrink", "table_name", "database_name",> "owner_name");
>
> The two operations can be combined into a single command:
>
> EXECUTE FUNCTION admin("table repack shrink", "table_name",
> "database_name",> "owner_name");
>
> These operations can optionally be done with the table locked
> by using
> 'repack_offline' intead of 'repack' otherwise the table
> remains online and
> available to users during the packing operation. When you
> pack online there
> is a possibility that new rows inserted during the packing
> run will not be
> packed.
>
> See the Administrator's Guide for details.
>
> Art
>
> 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 Fri, Aug 28, 2009 at 9:28 AM, Georges Martin <
> georges_martin_1@hotmail.com> wrote:
>
> > Hi,
> >
> > is there a way to do an online table reorg in IDS 11.5
> >
> > We currently have a huge table that we would like to reorg
> while users are
> > still connected to the database.
> >
> > Is there a way to do it in IDS 1.5?
> >
> > Thanks
> >
> > _________________________________________________________________
> > More storage. Better anti-spam and antivirus protection.
> Hotmail makes it
> > simple.
> > http://go.microsoft.com/?linkid=9671357
> >
> >
> >
> >
> **************************************************************
> *****************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0015174760aa88e5190472342224
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The shrink command can removed unused extents.
Example:
A table has two extents (first extent has 500 pages, second exten=
t
has 100 pages)
let say you have 4 rows per page, so our table currently has 2,40=
0
rows
If you delete every other row in your table (i.e. removing 1,200
rows)
The problem is the free space is in spread across all pages
If you repack, move all rows from the end of the table to the
beginning
With the above given information the first 300 page would b=
e
fill with
- the last extent is complete free
- the first extent would have the last 200 page free
Then the shrink command will remove extent #2 and can remove the =
200
free pages in extent 1 (if the first extent size is less th=
an
300 pages)
NOTE: The shrink command will not shrink a table smaller than t=
he
first extent)
size of a table.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
=
From: "Habichtsberg, Reinhard" <RHabichtsberg@arz-emmendingen.d=
e>
=
To: ids@iiug.org =
=
Date: 08/28/2009 09:30 AM =
=
Subject: RE: Online table reorg [16808] =
=
Sent by: ids-bounces@iiug.org =
=
Hi Art,
would that procedure also reduce the number of extents?
Regards,
Reinhard.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On
> Behalf Of Art
> Kagel
> Sent: Friday, August 28, 2009 4:03 PM
> To: ids@iiug.org
> Subject: Re: Online table reorg [16806]
>
>
> If you have 11.50.xC4 or later you can use the Admin API to
> pack the table's
> rows and aggregate all of the free space at the end of the
> table's extents:
>
> EXECUTE FUNCTION task("table repack", "table_name", "database_name",> "owner_name");
>
> After packing the table if you want to release the unused
> space for other
> tables to use you can optionally shrink the table's extents:
>
> EXECUTE FUNCTION admin("table shrink", "table_name", "database_name",=
> "owner_name");
>
> The two operations can be combined into a single command:
>
> EXECUTE FUNCTION admin("table repack shrink", "table_name",
> "database_name",> "owner_name");
>
> These operations can optionally be done with the table locked
> by using
> 'repack_offline' intead of 'repack' otherwise the table
> remains online and
> available to users during the packing operation. When you
> pack online there
> is a possibility that new rows inserted during the packing
> run will not be
> packed.
>
> See the Administrator's Guide for details.
>
> Art
>
> 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 Fri, Aug 28, 2009 at 9:28 AM, Georges Martin <
> georges_martin_1@hotmail.com> wrote:
>
> > Hi,
> >
> > is there a way to do an online table reorg in IDS 11.5
> >
> > We currently have a huge table that we would like to reorg
> while users are
> > still connected to the database.
> >
> > Is there a way to do it in IDS 1.5?
> >
> > Thanks
> >
> > _________________________________________________________________
> > More storage. Better anti-spam and antivirus protection.
> Hotmail makes it
> > simple.
> > http://go.microsoft.com/?linkid=3D9671357
> >
> >
> >
> >
> **************************************************************
> *****************
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
>
> --0015174760aa88e5190472342224
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
Yes. As long as there is sufficient contiguous space in the dbspace.
Art
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 Fri, Aug 28, 2009 at 12:29 PM, Habichtsberg, Reinhard <
RHabichtsberg@arz-emmendingen.de> wrote:
> Hi Art,
>
> would that procedure also reduce the number of extents?
>
> Regards,
> Reinhard.
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On
> > Behalf Of Art
> > Kagel
> > Sent: Friday, August 28, 2009 4:03 PM
> > To: ids@iiug.org
> > Subject: Re: Online table reorg [16806]
> >
> >
> > If you have 11.50.xC4 or later you can use the Admin API to
> > pack the table's
> > rows and aggregate all of the free space at the end of the
> > table's extents:
> >
> > EXECUTE FUNCTION task("table repack", "table_name", "database_name",> > "owner_name");
> >
> > After packing the table if you want to release the unused
> > space for other
> > tables to use you can optionally shrink the table's extents:
> >
> > EXECUTE FUNCTION admin("table shrink", "table_name", "database_name",> > "owner_name");
> >
> > The two operations can be combined into a single command:
> >
> > EXECUTE FUNCTION admin("table repack shrink", "table_name",
> > "database_name",> > "owner_name");
> >
> > These operations can optionally be done with the table locked
> > by using
> > 'repack_offline' intead of 'repack' otherwise the table
> > remains online and
> > available to users during the packing operation. When you
> > pack online there
> > is a possibility that new rows inserted during the packing
> > run will not be
> > packed.
> >
> > See the Administrator's Guide for details.
> >
> > Art
> >
> > 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 Fri, Aug 28, 2009 at 9:28 AM, Georges Martin <
> > georges_martin_1@hotmail.com> wrote:
> >
> > > Hi,
> > >
> > > is there a way to do an online table reorg in IDS 11.5
> > >
> > > We currently have a huge table that we would like to reorg
> > while users are
> > > still connected to the database.
> > >
> > > Is there a way to do it in IDS 1.5?
> > >
> > > Thanks
> > >
> > > _________________________________________________________________
> > > More storage. Better anti-spam and antivirus protection.
> > Hotmail makes it
> > > simple.
> > > http://go.microsoft.com/?linkid=9671357
> > >
> > >
> > >
> > >
> > **************************************************************
> > *****************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --0015174760aa88e5190472342224
> >
> >
> > **************************************************************
> > *****************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174760aa0294bc047237fd07