IDS Forum

Re: Attach Detach fragment

Posted By: VIKAS HIVARKAR
Date: Wednesday, 18 July 2012, at 12:54 a.m.

In Response To: Re: Attach Detach fragment (Fernando Nunes)

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

Messages In This Thread

• Attach Detach fragment
  VIKAS HIVARKAR -- Tuesday, 17 July 2012, at 8:08 a.m.
  • Re: Attach Detach fragment
    Jack Parker -- Tuesday, 17 July 2012, at 8:19 a.m.
  • Re: Attach Detach fragment
    Fernando Nunes -- Tuesday, 17 July 2012, at 11:11 a.m.
    • Re: Attach Detach fragment
      Jack Parker -- Tuesday, 17 July 2012, at 11:13 a.m.
    • Re: Attach Detach fragment
      VIKAS HIVARKAR -- Wednesday, 18 July 2012, at 12:54 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.