Fragmentation: Detach Attach partition
User attempted to detach one partition from a two-partition fragmented table in Informix 11.50, but the table became non-fragmented afterward. The issue was resolved: fragmented tables must have at least two fragments. Detaching one of two fragments leaves only one, converting it to non-fragmented. Testing with three or more fragments confirmed detach works correctly when maintaining minimum fragment requirement.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello All,
Informix 11.50.FC6 on HP-Unix
create table myfragment
(date_stamp date
) with vercols
fragment by expression
partition myfrag_1 ((date_stamp <= DATE ('12/31/2011' )
) AND (date_stamp >= DATE ('01/01/2011' ) ) )
in els_legal_dat,
partition myfrag_2 ((date_stamp <= DATE ('12/31/2012' )
) AND (date_stamp >= DATE ('01/01/2012') ) )
in els_legal_dat;
I want to detach only partion myfrag_1 for which I tried the following:
> alter fragment on table myfragment detach myfrag_1 newtable;
Alter fragment completed.
$> dbschema -d trigger -t myfragment -ss
{ TABLE "vikasha".myfragment row size = 12 number of columns = 1 index size =
0 }
create table "vikasha".myfragment
(
date_stamp date
) with vercols extent size 16 next size 16 lock mode page;
revoke all on "vikasha".myfragment from "public" as "vikasha";
I have also tried with key word partition
> alter fragment on table myfragment detach partition myfrag_1 newtable;
Alter fragment completed.
Still the same result, Why is the second partition 'myfrag_2' getting detached?
I am missing something very obvious, please help
Regards,
Vikas
Hi Vikas,
It is because a fragmented table must always have at least two fragments.
After detaching your first fragment there would only be one fragment left, so
it becomes a non-fragmented table.
Hope that helps,
Stuart
---
Ardenta Ltd is a company registered in England and Wales. Registered number:
4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
Middlesex, TW16 6RT.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of VIKAS
HIVARKAR
Sent: 15 May 2012 07:17
To: ids@iiug.org
Subject: Fragmentation: Detach Attach partition [27136]
Hello All,
Informix 11.50.FC6 on HP-Unix
create table myfragment
(date_stamp date
) with vercols
fragment by expression
partition myfrag_1 ((date_stamp <= DATE ('12/31/2011' )
) AND (date_stamp >= DATE ('01/01/2011' ) ) )
in els_legal_dat,
partition myfrag_2 ((date_stamp <= DATE ('12/31/2012' )
) AND (date_stamp >= DATE ('01/01/2012') ) )
in els_legal_dat;
I want to detach only partion myfrag_1 for which I tried the following:
> alter fragment on table myfragment detach myfrag_1 newtable;
Alter fragment completed.
$> dbschema -d trigger -t myfragment -ss
{ TABLE "vikasha".myfragment row size = 12 number of columns = 1 index size =
0 }
create table "vikasha".myfragment
(
date_stamp date
) with vercols extent size 16 next size 16 lock mode page;
revoke all on "vikasha".myfragment from "public" as "vikasha";
I have also tried with key word partition
> alter fragment on table myfragment detach partition myfrag_1 newtable;
Alter fragment completed.
Still the same result, Why is the second partition 'myfrag_2' getting
detached?
I am missing something very obvious, please help
Regards,
Vikas
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Stuart,
Yes you are absolutely correct on this, I tried with 3 fragments and dropped
one with 2 remaining and it works as designed.
Thanks for lot for your help! really appreciate.
*****************************************************************************
Hi Vikas,
It is because a fragmented table must always have at least two fragments.
After detaching your first fragment there would only be one fragment left, so
it becomes a non-fragmented table.
Hope that helps,
Stuart
---
Ardenta Ltd is a company registered in England and Wales. Registered number:
4181041. Registered office: Saxon House, Downside, Sunbury on Thames,
Middlesex, TW16 6RT.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of VIKAS
HIVARKAR
Sent: 15 May 2012 07:17
To: ids@iiug.org
Subject: Fragmentation: Detach Attach partition [27136]
Hello All,
Informix 11.50.FC6 on HP-Unix
create table myfragment
(date_stamp date
) with vercols
fragment by expression
partition myfrag_1 ((date_stamp <= DATE ('12/31/2011' )
) AND (date_stamp >= DATE ('01/01/2011' ) ) )
in els_legal_dat,
partition myfrag_2 ((date_stamp <= DATE ('12/31/2012' )
) AND (date_stamp >= DATE ('01/01/2012') ) )
in els_legal_dat;
I want to detach only partion myfrag_1 for which I tried the following:
> alter fragment on table myfragment detach myfrag_1 newtable;
Alter fragment completed.
$> dbschema -d trigger -t myfragment -ss
{ TABLE "vikasha".myfragment row size = 12 number of columns = 1 index size =
0 }
create table "vikasha".myfragment
(
date_stamp date
) with vercols extent size 16 next size 16 lock mode page;
revoke all on "vikasha".myfragment from "public" as "vikasha";
I have also tried with key word partition
> alter fragment on table myfragment detach partition myfrag_1 newtable;
Alter fragment completed.
Still the same result, Why is the second partition 'myfrag_2' getting
detached?
I am missing something very obvious, please help
Regards,
Vikas
The second fragment is NOT getting detached, but since the table has only a
single fragment, it is essentially no longer a fragmented table, so
dbschema is showing it as a non-fragmented table. Try adding a REMAINDER
fragment before you test the DETACH alter and see what happens. You cannot
create a fragmented table with a single fragment. Try it.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
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 Tue, May 15, 2012 at 2:17 AM, VIKAS HIVARKAR
<vikas.hivarkar@gmail.com>wrote:
> Hello All,
>
> Informix 11.50.FC6 on HP-Unix
>
> create table myfragment
> (> date_stamp date
> ) with vercols
> fragment by expression
> partition myfrag_1 ((date_stamp <= DATE ('12/31/2011' )
>
> ) AND (date_stamp >= DATE ('01/01/2011' ) ) )
> in els_legal_dat,
> partition myfrag_2 ((date_stamp <= DATE ('12/31/2012' )
>
> ) AND (date_stamp >= DATE ('01/01/2012') ) )
> in els_legal_dat;
>
> I want to detach only partion myfrag_1 for which I tried the following:
>
> > alter fragment on table myfragment detach myfrag_1 newtable;>
> Alter fragment completed.>
> $> dbschema -d trigger -t myfragment -ss
> { TABLE "vikasha".myfragment row size = 12 number of columns = 1 index
> size =
> 0 }
> create table "vikasha".myfragment
> (
>
> date_stamp date
> ) with vercols extent size 16 next size 16 lock mode page;
>
> revoke all on "vikasha".myfragment from "public" as "vikasha";>
> I have also tried with key word partition
> > alter fragment on table myfragment detach partition myfrag_1 newtable;>
> Alter fragment completed.>
> Still the same result, Why is the second partition 'myfrag_2' getting
> detached?
>
> I am missing something very obvious, please help
>
> Regards,
> Vikas
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3ba959e86def04c010ce91
Hello Art,
Thanks for your response, I have tested with more than 2 fragments and it
works just as you have explained.
Is it possible to insert the data from the detached partition into another
fragmented table (same partitioning as the original table)?
I fear that the detached data will be large enough to hit the 16 million page
limit per dbspace hence the archive table should again be a fragmented table.
Is there a better way of doing the above.
----------------------------------------------------------------------------
The second fragment is NOT getting detached, but since the table has only a
single fragment, it is essentially no longer a fragmented table, so
dbschema is showing it as a non-fragmented table. Try adding a REMAINDER
fragment before you test the DETACH alter and see what happens. You cannot
create a fragmented table with a single fragment. Try it.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
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 Tue, May 15, 2012 at 2:17 AM, VIKAS HIVARKAR
<vikas.hivarkar@gmail.com>wrote:
> Hello All,
>
> Informix 11.50.FC6 on HP-Unix
>
> create table myfragment
> (> date_stamp date
> ) with vercols
> fragment by expression
> partition myfrag_1 ((date_stamp <= DATE ('12/31/2011' )
>
> ) AND (date_stamp >= DATE ('01/01/2011' ) ) )
> in els_legal_dat,
> partition myfrag_2 ((date_stamp <= DATE ('12/31/2012' )
>
> ) AND (date_stamp >= DATE ('01/01/2012') ) )
> in els_legal_dat;
>
> I want to detach only partion myfrag_1 for which I tried the following:
>
> > alter fragment on table myfragment detach myfrag_1 newtable;>
> Alter fragment completed.>
> $> dbschema -d trigger -t myfragment -ss
> { TABLE "vikasha".myfragment row size = 12 number of columns = 1 index
> size =
> 0 }
> create table "vikasha".myfragment
> (
>
> date_stamp date
> ) with vercols extent size 16 next size 16 lock mode page;
>
> revoke all on "vikasha".myfragment from "public" as "vikasha";>
> I have also tried with key word partition
> > alter fragment on table myfragment detach partition myfrag_1 newtable;>
> Alter fragment completed.>
> Still the same result, Why is the second partition 'myfrag_2' getting
> detached?
>
> I am missing something very obvious, please help
>
> Regards,
> Vikas
You can ATTACH the detached fragment/table to another table. This is
common to move oldest data from a production table on fast storage to a
history table stored in dbspaces on less expensive storage.
If the table you are attaching it to is already fragmented and the
attachment is compatible with the existing fragmentation scheme, then the
attach will not require the table to be rebuilt. However any indexes on
the table which are not fragmented, are fragmented on a different scheme
than the table, or which are not present in the newly attached fragment
will be rebuilt as part of the attach.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
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 Tue, May 15, 2012 at 8:50 AM, VIKAS HIVARKAR
<vikas.hivarkar@gmail.com>wrote:
> Hello Art,
>
> Thanks for your response, I have tested with more than 2 fragments and it
> works just as you have explained.
>
> Is it possible to insert the data from the detached partition into another
> fragmented table (same partitioning as the original table)?
>
> I fear that the detached data will be large enough to hit the 16 million
> page
> limit per dbspace hence the archive table should again be a fragmented
> table.
>
> Is there a better way of doing the above.
>
>
> ----------------------------------------------------------------------------
> The second fragment is NOT getting detached, but since the table has only a
> single fragment, it is essentially no longer a fragmented table, so
> dbschema is showing it as a non-fragmented table. Try adding a REMAINDER
> fragment before you test the DETACH alter and see what happens. You cannot
> create a fragmented table with a single fragment. Try it.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> 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 Tue, May 15, 2012 at 2:17 AM, VIKAS HIVARKAR
> <vikas.hivarkar@gmail.com>wrote:
>
> > Hello All,
> >
> > Informix 11.50.FC6 on HP-Unix
> >
> > create table myfragment
> > (> > date_stamp date
> > ) with vercols
> > fragment by expression
> > partition myfrag_1 ((date_stamp <= DATE ('12/31/2011' )
> >
> > ) AND (date_stamp >= DATE ('01/01/2011' ) ) )
> > in els_legal_dat,
> > partition myfrag_2 ((date_stamp <= DATE ('12/31/2012' )
> >
> > ) AND (date_stamp >= DATE ('01/01/2012') ) )
> > in els_legal_dat;
> >
> > I want to detach only partion myfrag_1 for which I tried the following:
> >
> > > alter fragment on table myfragment detach myfrag_1 newtable;> >
> > Alter fragment completed.> >
> > $> dbschema -d trigger -t myfragment -ss
> > { TABLE "vikasha".myfragment row size = 12 number of columns = 1 index
> > size =
> > 0 }
> > create table "vikasha".myfragment
> > (
> >
> > date_stamp date
> > ) with vercols extent size 16 next size 16 lock mode page;
> >
> > revoke all on "vikasha".myfragment from "public" as "vikasha";> >
> > I have also tried with key word partition
> > > alter fragment on table myfragment detach partition myfrag_1 newtable;> >
> > Alter fragment completed.> >
> > Still the same result, Why is the second partition 'myfrag_2' getting
> > detached?
> >
> > I am missing something very obvious, please help
> >
> > Regards,
> > Vikas
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8eb84a7dfe04c0130d80