Move table from rootdbs to a different dbspace
Someone asked how to move a table out of rootdbs into another dbspace without unloading/reloading the data on a production database. Art Kagel answered: use ALTER FRAGMENT ON TABLE <table> INIT IN <dbspace>, noting you need enough space in the target dbspace and enough logical log space (or change the table to RAW first to reduce logging). He also confirmed the operation requires exclusive access to the table, so it can't be done while in use, and that system catalog tables can't be moved this way — those require unload, drop, recreate and reload of the database.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, How do i move a table created from rootdbs (creation) to another dbspace. I do not want to unload/load as this is a prod db. is there anyway?
ALTER FRAGMENT ON TABLE <tablename> INIT IN<dbspace-or-fragmentation-expression>;
That will move the table. You'll need enough room in the target dbspace
(which can be the same one the table resides in already for a simple reorg)
of course and you'll need enough logical log space to hold the logging of
the move unless you ALTER the type of the table to RAW before the move.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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 Thu, Jan 28, 2010 at 11:15 PM, JACK PAPA <informix2009@gmail.com> wrote:
> Hi All,
>
> How do i move a table created from rootdbs (creation) to another dbspace. I
> do
> not want to unload/load as this is a prod db. is there anyway?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174c14fc3e2cc0047e45fa25
Thank you very much Art. I will check this command..
ALTER FRAGMENT ON TABLE tablename INIT IN dbspace1;
Only to be sure I got it: With ALTER FRAGMENT you need exclusive access and
cannot use the table in production anyway?
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
> Sent: Friday, January 29, 2010 5:22 AM
> To: ids@iiug.org
> Subject: Re: Move table from rootdbs to a different dbspace [18834]
>
> ALTER FRAGMENT ON TABLE <tablename> INIT IN> <dbspace-or-fragmentation-expression>;
>
> That will move the table. You'll need enough room in the target dbspace
> (which can be the same one the table resides in already for a simple
reorg)
> of course and you'll need enough logical log space to hold the logging of
> the move unless you ALTER the type of the table to RAW before the move.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
by
> inference. 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 Thu, Jan 28, 2010 at 11:15 PM, JACK PAPA <informix2009@gmail.com>
wrote:
>
> > Hi All,
> >
> > How do i move a table created from rootdbs (creation) to another
dbspace. I
> > do
> > not want to unload/load as this is a prod db. is there anyway?
> >
> >
> >
> >
>
****************************************************************************
***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0015174c14fc3e2cc0047e45fa25
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
Correct.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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, Jan 29, 2010 at 2:17 AM, Habichtsberg, Reinhard <
RHabichtsberg@arz-emmendingen.de> wrote:
> Only to be sure I got it: With ALTER FRAGMENT you need exclusive access and
> cannot use the table in production anyway?
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
> Kagel
> > Sent: Friday, January 29, 2010 5:22 AM
> > To: ids@iiug.org
> > Subject: Re: Move table from rootdbs to a different dbspace [18834]
> >
> > ALTER FRAGMENT ON TABLE <tablename> INIT IN> > <dbspace-or-fragmentation-expression>;
> >
> > That will move the table. You'll need enough room in the target dbspace
> > (which can be the same one the table resides in already for a simple
> reorg)
> > of course and you'll need enough logical log space to hold the logging of
> > the move unless you ALTER the type of the table to RAW before the move.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > See you at the 2010 IIUG Informix Conference
> > April 25-28, 2010
> > Overland Park (Kansas City), KS
> > www.iiug.org/conf
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly, implicitly, or
> by
> > inference. 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 Thu, Jan 28, 2010 at 11:15 PM, JACK PAPA <informix2009@gmail.com>
> wrote:
> >
> > > Hi All,
> > >
> > > How do i move a table created from rootdbs (creation) to another
> dbspace. I
> > > do
> > > not want to unload/load as this is a prod db. is there anyway?
> > >
> > >
> > >
> > >
> >
>
> ****************************************************************************
> ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --0015174c14fc3e2cc0047e45fa25
> >
> >
> >
>
> ****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001517479462a59a49047e4a53c6
Hi Kagel, Do this applicable for system(catalog tables also)? rgds schillache
No, the only way to move the system catalog tables is to unload, drop, recreate, reload the database into a different dbspace(s). PLEASE quote at least the relevant parts of the posts that you are responding to. Most of us on this list use the email gateway and delete read messages/postings, so we do not have a threaded history to look at. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Mon, Feb 8, 2010 at 12:15 AM, SCHIL ACHE <penfriend5@yahoo.co.uk> wrote: > Hi Kagel, > > Do this applicable for system(catalog tables also)? > > rgds > schillache > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174befc4ff8083047f14d07d