But be careful of detaching indices for the TS control tables, if the
index is not attached things will break
Cheers
Paul
> C??sar,
>
> Some short notes:
>
>
> 1- The documentation is misleading. Never use ATTACHED indexes. Whoever
> wrote this was thinking on having one index partition per index per table
> fragment
> 2- Your CREATE INDEX is wrong because it explicit a fragmentation
> strategy. Although you use the same, that's not what you should do because
> the engine doesn't validate/check. Just use "CREATE INDEX..." as in the
> manual, without specifying storage clauses
>
>
> 3- The detached tables had always loose their indexes and constraints. An
> RFE for this could make sense ("KEEP INDEXES", "KEEP CONSTRAINTS" or
> whatever). Maybe there's a reason for the current behavior, but I don't
> know why
>
>
> 4- Your use case seems a bit odd... why are you detaching a partition from
> a table and attaching it again?! I could understand if you were moving a
> partition between tables, and your complains (point 3) would still be
> valid
>
> Regards.
>
>
> ------------------------------
> FERNANDO NUNES
> ------------------------------
> -------------------------------------------
> Original Message:
> Sent: Wed December 04, 2019 01:29 PM
> From: Cesar Martins
> Subject: Table partition, ONLINE detach/attach issues.
>
> Fernando,
> About this :
> What the feature requires is indexes that follow the same strategy as the
> table, and are detached. Basically this means you will have index
> partitions associated with each table partition. This allows the server to
> drop/remove those index partitions if the partition is removed from the
> table (or adding them to the table if their associated table is being
> attached to the main table).
>
>
>
> I also always have that in mind, at least when used with partition by
> expression.
>
> At first, that is what I expected for the range interval too, when I get
> to start with all this trouble because that never happens.
>
> Then I check the manual and start to execute a lot of tests.
>
> I notice that isn't working even for partition by expression, at least in
> my tests.
>
>
>
> Check the output below.
>
> I will upload the test script , if you want to try to reproduce there.
>
> This test here is no relation with ONLINE feature, only the fact of the
> index is lost to remain table, even when they have the same partition
> configuration.
>
> $ dbaccess -e -a - frag1.sqldatabase sysmaster ;Database selected.drop
> database if exists test_frag ;create database test_frag in dbs1 with
> buffered log ;Database closed.Database created.--EXECUTE FUNCTION
> sysbldprepare('excompat.*','create');(expression) 01 row(s)
> retrieved.--drop table if exists employee ;Table dropped.drop table if
> exists employee3 ;Table dropped.CREATE TABLE employee (emp_id INTEGER,
> name CHAR(32), dept_id CHAR(2), mgr_id INTEGER, ssn CHAR(12)) partition
> BY expression partition p0 (emp_id < 100) in dbs1, PARTITION p1 (emp_id
> < 200) IN dbs1, PARTITION p2 (emp_id < 400) IN dbs1, remainder in
> dbs1;Table created.CREATE INDEX employee_id_idx ON employee(emp_id)
> partition BY expression partition p0 (emp_id < 100) in dbs1, PARTITION
> p1 (emp_id < 200) IN dbs1, PARTITION p2 (emp_id < 400) IN dbs1,
> remainder in dbs1;Index created.INSERT INTO employee VALUES (401, "Susan",
> "DV", 101, "123-45-6789");1 row(s) inserted.INSERT INTO employee VALUES
> (601,
> "David", "QA", 104, "987-65-4321");1 row(s) inserted.select f.fragtype as
> t, f.strategy as s ,f.evalpos::char(2) as evalpos ,f.partition[1,10]
> ,dbms_lob_substr(f.exprtext,30)::char(30) as exprtextfrom sysfragments f,
> systables twhere t.tabname = 'employee' and t.tabid = f.tabidorder by
> 1,evalpos;t s evalpos partition exprtextI E 0 p0 (emp_id <
> 100 )I E 1 p1 (emp_id < 200 )I E 2 p2 (emp_id
> < 400 )I E 3 dbs1 remainderT E 0 p0 (emp_id <
> 100 )T E 1 p1 (emp_id < 200 )T E 2 p2 (emp_id
> < 400 )T E 3 dbs1 remainder8 row(s) retrieved.ALTER FRAGMENT
> ON TABLE employee DETACH PARTITION p2 employee3;Alter fragment
> completed.--DBSCHEMA Schema Utility INFORMIX-SQL Version
> 12.10.FC13X4{ TABLE "informix".employee row size = 54 number of columns =
> 5 index size = 13 }create table "informix".employee ( emp_id integer,
> name char(32), dept_id char(2), mgr_id integer, ssn
> char(12) ) fragment by expression partition p0 (emp_id < 100 ) in
> dbs1, partition p1 (emp_id < 200 ) in dbs1, remainder in dbs1
> extent size 16 next size 16 lock mode row;revoke all on
> "informix".employee from "public" as "informix";create index
> "informix".employee_id_idx on "informix".employee (emp_id) using btree
> fragment by expression partition p0 (emp_id < 100 ) in dbs1,
> partition p1 (emp_id < 200 ) in dbs1, remainder in dbs1;--DBSCHEMA
> Schema Utility INFORMIX-SQL Version 12.10.FC13X4{ TABLE
> "informix".employee3 row size = 54 number of columns = 5 index size = 0
> }create table "informix".employee3 ( emp_id integer, name
> char(32), dept_id char(2), mgr_id integer, ssn char(12) )
> extent size 16 next size 16 lock mode row;revoke all on
> "informix".employee3 from "public" as "informix";--drop table if exists
> employee ;Table dropped.drop table if exists employee3 ;Table
> dropped.CREATE TABLE employee (emp_id INTEGER, name CHAR(32), dept_id
> CHAR(2), mgr_id INTEGER, ssn CHAR(12)) FRAGMENT BY RANGE (emp_id)
> INTERVAL (100) STORE IN (dbs1) PARTITION p1 VALUES < 100 IN dbs1;Table
> created.CREATE INDEX employee_id_idx ON employee(emp_id) FRAGMENT BY
> RANGE (emp_id) INTERVAL (100) STORE IN (dbs1) PARTITION p1 VALUES < 100
> IN dbs1;Index created.INSERT INTO employee VALUES (401, "Susan", "DV",
> 101, "123-45-6789");1 row(s) inserted.INSERT INTO employee VALUES (601,
> "David", "QA", 104, "987-65-4321");1 row(s) inserted.select f.fragtype as
> t, f.strategy as s ,f.evalpos::char(2) as evalpos ,f.partition[1,10]
> ,dbms_lob_substr(f.exprtext,30)::char(30) as exprtextfrom sysfragments f,
> systables twhere t.tabname = 'employee' and t.tabid = f.tabidorder by
> 1,evalpos;t s evalpos partition exprtextI N -1 dbs1I N
> -2 100I N -3 emp_idI N 0 p1
> VALUES < 100I N 4 sys_p4 VALUES >= 400. AND VALUES < 50I N 6
> sys_p6 VALUES >= 600. AND VALUES < 70T N -1
> dbs1T N -2 100T N -3 emp_idT N 0
> p1 VALUES < 100T N 4 sys_p4 VALUES >= 400. AND VALUES
> < 50T N 6 sys_p6 VALUES >= 600. AND VALUES < 7012 row(s)
> retrieved.ALTER FRAGMENT ON TABLE employee DETACH PARTITION sys_p6
> employee3;Alter fragment completed.select * from employee3 ; emp_id
> name dept_id mgr_id ssn 601
> David QA 104 987-65-43211
> row(s) retrieved.--DBSCHEMA Schema Utility INFORMIX-SQL Version
> 12.10.FC13X4{ TABLE "informix".employee row size = 54 number of columns
> = 5 index size = 13 }create table "informix".employee ( emp_id
> integer, name char(32), dept_id char(2), mgr_id integer,
> ssn char(12) ) fragment by range(emp_id) interval(100) store in(dbs1)
> partition p1 VALUES < 100 in dbs1 extent size 16 next size 16 lock
> mode row;revoke all on "informix".employee from "public" as
> "informix";create index
> "informix".employee_id_idx on "informix".employee (emp_id) using btree
> fragment by range(emp_id) interval(100) store in(dbs1) partition p1
> VALUES < 100 in dbs1;--DBSCHEMA Schema Utility INFORMIX-SQL Version
> 12.10.FC13X4{ TABLE "informix".employee3 row size = 54 number of columns
> = 5 index size = 0 }create table "informix".employee3 ( emp_id
> integer, name char(32), dept_id char(2), mgr_id integer, ssn
> char(12) ) extent size 16 next size 16 lock mode row;revoke all on
> "informix".employee3 from "public" as "informix";database sysmaster
> ;Database closed.Database selected.drop database if exists test_frag
> ;Database dropped.Database closed.
>
> ------------------------------
> Cesar Martins
> ------------------------------
>
> Original Message:
> Sent: Wed December 04, 2019 08:21 AM
> From: FERNANDO NUNES
> Subject: Table partition, ONLINE detach/attach issues.
>
>
> Hello C??sar,
>
> You're making a confusion between attached indexes ("in table") and
> indexes that follow the same strategy as the table.
>
> The attached indexes are, as you correctly state, deprecated and should
> not be used.
>
> What the feature requires is indexes that follow the same strategy as the
> table, and are detached. Basically this means you will have index
> partitions associated with each table partition. This allows the server to
> drop/remove those index partitions if the partition is removed from the
> table (or adding them to the table if their associated table is being
> attached to the main table).
>
> Without this, a removal or addition of a partition would require a
> complete index rebuild, and naturally the "online" would have gone.
>
>
>
> I need more time to check your other concerns. But some attaching issues
> can be eased by creating check constraints on the partitions that are
> being attached to map the fragmenting expression. Otherwise the system
> will need to validate all records in the attaching table to make sure they
> fit the fragmentation expression. If you already have a constraint on the
> table, Informix is smart enough to notice it.
>
> Latest versions may not need these verifications, if you so decide, but I
> would need to verify if it applies to attach/detach.
> Regards
>
>
> ------------------------------
> FERNANDO NUNES
>
> Original Message:
> Sent: Wed December 04, 2019 07:59 AM
> From: Cesar Martins
> Subject: Table partition, ONLINE detach/attach issues.
>
> Hi!
>
> IFX 12.10.FC13
>
> We trying to implement the use of ATTACH/DETACH partitions in our
> environment
> using the supposed advantages of RANGE INTERVAL rules.
> Our goal is using the ONLINE feature to get minimal ou zero impact for the
> users. With that, we will able to reduce some processes from 4~8 hours
> running to a few seconds of execution.
>
> We are getting frustrated because all restrictions/limitations begin
> to multiple paradox rules and what appears to be an incomplete
> implementation of
> this feature.
> I would like to know if someone in the world uses this feature, because of
> the way how it was implemented appear to be useless and I don't see how
> any
> system will able to use and get any advantage of using this.
>
> My point of view, I see as a defect since the main objective to use
> the ONLINE feature is almost impossible to implement in a production
> environment.
>
> I will explain all situation below and appreciate any tips or explanations
> if we
> misunderstand this feature.
>
> Our main objective is :
> * Be capable to detach a fragment online
> * Be capable to attach a derived table over they origin table again or
> other
> table with the same structure.
> All this using the ONLINE option, of course.
>
> We use few resources what is difficulting this implementation.
> 1) our tables have detached indexes.
> 2) our tables have check constrains (not PK, not FK).
> 3) our tables have serial fields.
>
> Some explanation about our difficulties to use this feature :
>
> Manual : Using the ONLINE keyword in DETACH operations
>
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_1941.htm>
#Informix