Attach Detach fragment
Hello ALL,
IDS 11.50.FC8W2 on HP-Unix
create table t1 ( c1 int not null , c2 int default 10000 not null) with
vercols fragment by expression partition tabchec02 ((c1 <= 10 ) AND (c1 > 0))
in tab_tabchec_dat, partition tabchec03 ((c1 <= 20) AND (c1 > 10)) in
tab_tabchec_dat, partition tabchec04 ((c1 <= 30) AND (c1 > 20)) in
tab_tabchec_dat, partition tabchec05 ((c1 <= 40) AND (c1 > 30)) intab_tabchec_dat extent size 16 next size 16 lock mode row;
insert into t1 values ( 5,55);
insert into t1 values ( 15,555);
insert into t1 values ( 25,555);
insert into t1 values ( 35,555);
alter fragment on table t1 detach partition tabchec04 t1_4;
alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <= 30) AND
(c1 > 20)) after tabchec03;
783: Cannot attach because of incompatible schema.
Error in line 1Near character position 103
-----------------------------------------------------------------------
alter table t1_4 modify (c1 integer not null);
alter table t1_4 modify (c2 integer not null);
alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <= 30) AND
(c1 > 20)) after tabchec03;
Things work fine!
-----------------------------------------------------------------------
So If I have detached a fragment from original table, the detached
table(fragment) will not have the contraints inherited from the original table
and hence I cannot attach(throws 783) this fragment asIS to the original table
or my archive table (which is having exactly the same schema as that of
original table).
Now I have to alter the detached table to add constraints, what If I have
millions of records...probably better to do conventional unload load stuff
with HPL. then how does attach help me?
Regards,
Vikas
I agree, it's an issue. Attach is not that useful because of it. =
Detach is very useful for getting rid of old data if you have fragmented =
based on a date.
j.
On Jul 17, 2012, at 8:08 AM, VIKAS HIVARKAR wrote:
> Hello ALL,=20
>=20
> IDS 11.50.FC8W2 on HP-Unix=20
>=20
> create table t1 ( c1 int not null , c2 int default 10000 not null) =
with=20> vercols fragment by expression partition tabchec02 ((c1 <=3D 10 ) AND =
(c1 > 0))=20
> in tab_tabchec_dat, partition tabchec03 ((c1 <=3D 20) AND (c1 > 10)) =
in=20
> tab_tabchec_dat, partition tabchec04 ((c1 <=3D 30) AND (c1 > 20)) in=20=
> tab_tabchec_dat, partition tabchec05 ((c1 <=3D 40) AND (c1 > 30)) in=20=
> tab_tabchec_dat extent size 16 next size 16 lock mode row;=20
>=20
> insert into t1 values ( 5,55);=20
> insert into t1 values ( 15,555);=20
> insert into t1 values ( 25,555);=20
> insert into t1 values ( 35,555);=20>=20
> alter fragment on table t1 detach partition tabchec04 t1_4;=20
> alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <=3D =30) AND=20
> (c1 > 20)) after tabchec03;=20
>=20
> 783: Cannot attach because of incompatible schema.=20
> Error in line 1=20> Near character position 103=20
> =
-----------------------------------------------------------------------=20=
> alter table t1_4 modify (c1 integer not null);=20
> alter table t1_4 modify (c2 integer not null);=20
> alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <=3D =30) AND=20
> (c1 > 20)) after tabchec03;=20
>=20
> Things work fine!=20
> =
-----------------------------------------------------------------------=20=
>=20
> So If I have detached a fragment from original table, the detached=20
> table(fragment) will not have the contraints inherited from the =
original table=20
> and hence I cannot attach(throws 783) this fragment asIS to the =
original table=20
> or my archive table (which is having exactly the same schema as that =
of=20
> original table).=20
>=20
> Now I have to alter the detached table to add constraints, what If I =
have=20
> millions of records...probably better to do conventional unload load =
stuff=20
> with HPL. then how does attach help me?=20
>=20
> Regards,=20
> Vikas=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
The reason why attach fails is understandabke... If you coul attach without
constraints there would be margin fir user error and in generak Informix
never allows that (even if there is a performance impact).
Now, having said the above, lets take a look at your real problems:
1- attaching a fragment to a table where you detached it from makes no
sense to me.... What's the point?
2- attaching it to an archive table makes sense... But why do you need the
constraints on that table? If it receives data detached from the main table
you know that data is "good". In realiry is like what you'd like it to do
in the first place: trust the data source.
To end, I'd vote for a feature that would allow the constraints to be kept
during a detach....
Regards
On Jul 17, 2012 1:08 PM, "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com> wrote:
> Hello ALL,
>
> IDS 11.50.FC8W2 on HP-Unix
>
> create table t1 ( c1 int not null , c2 int default 10000 not null) with> vercols fragment by expression partition tabchec02 ((c1 <= 10 ) AND (c1 >
> 0))
> in tab_tabchec_dat, partition tabchec03 ((c1 <= 20) AND (c1 > 10)) in
> tab_tabchec_dat, partition tabchec04 ((c1 <= 30) AND (c1 > 20)) in
> tab_tabchec_dat, partition tabchec05 ((c1 <= 40) AND (c1 > 30)) in
> tab_tabchec_dat extent size 16 next size 16 lock mode row;
>
> insert into t1 values ( 5,55);
> insert into t1 values ( 15,555);
> insert into t1 values ( 25,555);
> insert into t1 values ( 35,555);>
> alter fragment on table t1 detach partition tabchec04 t1_4;
> alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <= 30)
> AND
> (c1 > 20)) after tabchec03;>
> 783: Cannot attach because of incompatible schema.
> Error in line 1> Near character position 103
> -----------------------------------------------------------------------
> alter table t1_4 modify (c1 integer not null);
> alter table t1_4 modify (c2 integer not null);
> alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <= 30)
> AND
> (c1 > 20)) after tabchec03;>
> Things work fine!
> -----------------------------------------------------------------------
>
> So If I have detached a fragment from original table, the detached
> table(fragment) will not have the contraints inherited from the original
> table
> and hence I cannot attach(throws 783) this fragment asIS to the original
> table
> or my archive table (which is having exactly the same schema as that of
> original table).
>
> Now I have to alter the detached table to add constraints, what If I have
> millions of records...probably better to do conventional unload load stuff
> with HPL. then how does attach help me?
>
> Regards,
> Vikas
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--485b393aab73e195d604c507f697
Yes,
the real issue is that all of the constraints are lost when you detach.
j.
On Jul 17, 2012, at 11:11 AM, Fernando Nunes wrote:
> The reason why attach fails is understandabke... If you coul attach =
without=20
> constraints there would be margin fir user error and in generak =
Informix=20
> never allows that (even if there is a performance impact).=20
>=20
> Now, having said the above, lets take a look at your real problems:=20
>=20
> 1- attaching a fragment to a table where you detached it from makes no=20=
> sense to me.... What's the point?=20
>=20
> 2- attaching it to an archive table makes sense... But why do you need =
the=20
> constraints on that table? If it receives data detached from the main =
table=20
> you know that data is "good". In realiry is like what you'd like it to =
do=20
> in the first place: trust the data source.=20
>=20
> To end, I'd vote for a feature that would allow the constraints to be =
kept=20
> during a detach....=20
>=20
> Regards=20
> On Jul 17, 2012 1:08 PM, "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com> =
wrote:=20
>=20
>> Hello ALL,=20
>>=20
>> IDS 11.50.FC8W2 on HP-Unix=20
>>=20
>> create table t1 ( c1 int not null , c2 int default 10000 not null) =
with=20>> vercols fragment by expression partition tabchec02 ((c1 <=3D 10 ) AND =
(c1 >=20
>> 0))=20
>> in tab_tabchec_dat, partition tabchec03 ((c1 <=3D 20) AND (c1 > 10)) =
in=20
>> tab_tabchec_dat, partition tabchec04 ((c1 <=3D 30) AND (c1 > 20)) in=20=
>> tab_tabchec_dat, partition tabchec05 ((c1 <=3D 40) AND (c1 > 30)) in=20=
>> tab_tabchec_dat extent size 16 next size 16 lock mode row;=20
>>=20
>> insert into t1 values ( 5,55);=20
>> insert into t1 values ( 15,555);=20
>> insert into t1 values ( 25,555);=20
>> insert into t1 values ( 35,555);=20>>=20
>> alter fragment on table t1 detach partition tabchec04 t1_4;=20
>> alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <=3D=30)=20
>> AND=20
>> (c1 > 20)) after tabchec03;=20
>>=20
>> 783: Cannot attach because of incompatible schema.=20
>> Error in line 1=20>> Near character position 103=20
>> =
-----------------------------------------------------------------------=20=
>> alter table t1_4 modify (c1 integer not null);=20
>> alter table t1_4 modify (c2 integer not null);=20
>> alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <=3D=30)=20
>> AND=20
>> (c1 > 20)) after tabchec03;=20
>>=20
>> Things work fine!=20
>> =
-----------------------------------------------------------------------=20=
>>=20
>> So If I have detached a fragment from original table, the detached=20
>> table(fragment) will not have the contraints inherited from the =
original=20
>> table=20
>> and hence I cannot attach(throws 783) this fragment asIS to the =
original=20
>> table=20
>> or my archive table (which is having exactly the same schema as that =
of=20
>> original table).=20
>>=20
>> Now I have to alter the detached table to add constraints, what If I =
have=20
>> millions of records...probably better to do conventional unload load =
stuff=20
>> with HPL. then how does attach help me?=20
>>=20
>> Regards,=20
>> Vikas=20
>>=20
>>=20
>>=20
>>=20
> =
**************************************************************************=
*****=20
>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>>=20
>>=20
>=20
> --485b393aab73e195d604c507f697=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Hi,
I DONOT intend to attach the fragment to same table from where it was
detached, I tried that just for testing when I was facing "incompatible schema
error all the time"
The Fragment need to be attached to an archived table however this table is
still used by some historic checks and the application which uses this table
need the constraints on and hence not sure if I can remove the constraints and
at what cost.
Regards,
Vikas
===============================================================================
The reason why attach fails is understandabke... If you coul attach without
constraints there would be margin fir user error and in generak Informix
never allows that (even if there is a performance impact).
Now, having said the above, lets take a look at your real problems:
1- attaching a fragment to a table where you detached it from makes no
sense to me.... What's the point?
2- attaching it to an archive table makes sense... But why do you need the
constraints on that table? If it receives data detached from the main table
you know that data is "good". In realiry is like what you'd like it to do
in the first place: trust the data source.
To end, I'd vote for a feature that would allow the constraints to be kept
during a detach....
Regards
On Jul 17, 2012 1:08 PM, "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com> wrote:
> Hello ALL,
>
> IDS 11.50.FC8W2 on HP-Unix
>
> create table t1 ( c1 int not null , c2 int default 10000 not null) with> vercols fragment by expression partition tabchec02 ((c1 <= 10 ) AND (c1 >
> 0))
> in tab_tabchec_dat, partition tabchec03 ((c1 <= 20) AND (c1 > 10)) in
> tab_tabchec_dat, partition tabchec04 ((c1 <= 30) AND (c1 > 20)) in
> tab_tabchec_dat, partition tabchec05 ((c1 <= 40) AND (c1 > 30)) in
> tab_tabchec_dat extent size 16 next size 16 lock mode row;
>
> insert into t1 values ( 5,55);
> insert into t1 values ( 15,555);
> insert into t1 values ( 25,555);
> insert into t1 values ( 35,555);>
> alter fragment on table t1 detach partition tabchec04 t1_4;
> alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <= 30)
> AND
> (c1 > 20)) after tabchec03;>
> 783: Cannot attach because of incompatible schema.
> Error in line 1> Near character position 103
> -----------------------------------------------------------------------
> alter table t1_4 modify (c1 integer not null);
> alter table t1_4 modify (c2 integer not null);
> alter fragment on table t1 attach t1_4 as partition tabchec04 ((c1 <= 30)
> AND
> (c1 > 20)) after tabchec03;>
> Things work fine!
> -----------------------------------------------------------------------
>
> So If I have detached a fragment from original table, the detached
> table(fragment) will not have the contraints inherited from the original
> table
> and hence I cannot attach(throws 783) this fragment asIS to the original
> table
> or my archive table (which is having exactly the same schema as that of
> original table).
>
> Now I have to alter the detached table to add constraints, what If I have
> millions of records...probably better to do conventional unload load stuff
> with HPL. then how does attach help me?
>
> Regards,
> Vikas