Informix

 View Only
  • 1.  Table partition, ONLINE detach/attach issues.

    Posted Wed December 04, 2019 08:00 AM
    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
    ====
    All references in the documentation to allow ONLINE detach, the index must have
    the "same fragmentation of the table", where they want to mean ATTACHED INDEX.
    Here is the first paradox, in two kinds: technically and concept.
    The technical paradox :
    When you try to create an ATTACHED index using the IN TABLE statement over a
    fragmented table, you will get the error :
    # 9665: Cannot create attached indexes on boolean, lvarchar, user defined data
    # types and fragmented tables.
    If you use a detached index, then you will get the error below when trying to
    detach a partition using the ONLINE option:
    # 25834: The specified alter fragment operation cannot be performed online.
    Here is the concept paradox :
    * As far I know ATTACHED indexes aren't recommended to be used so long, why
    a quite "new" resource requires a not recommended resource:
    IBM® does not recommend use in new applications of the IN TABLE storage
    option, nor of the DEFAULT_ATTACH environment variable. Such indexes are a
    deprecated feature that might not be supported in some future release of
    Informix.

    Now we get into the second PARADOX over this area.
    The code example in the "Using the ONLINE keyword in DETACH operations" manuals
    magically works!
    However, they work with a detached index, not attached. (they don't inform the
    IN TABLE and neither any storage option, but that doesn't mean the index is
    attached, that means the index will be detached and allocated in the default
    dbspace of the database)
    Then you think, "ok, they work with a detached index", NO!!!!
    If you run the example and add the "IN <DBSPACE>" at the CREATE INDEX statement,
    even if the same default dbspace, that's enough to the DETACH not work anymore
    returning the error -25834 .
    If you use "IN TABLE" option, then you stuck with -9665 .

    With all this limitation, we already stop any benefit of the ONLINE feature.


    We also stuck in a few resource limitations what gives the impression of all
    this become an incomplete feature implementation.

    1) Our tables have serial fields, they aren't accepted to attach
    The reason for that maybe makes sense. But is that hard to implement?
    I can workaround changing my columns to integer and then back them to
    serial, but this "disable" all ONLINE feature in two ways:
    first, the need for exclusive access to alter the table data type and second,
    the gap while the table is without they serial and users working over it
    create a huge risk to have new data with null values into that column.

    2) To attach a partition the engine requires a CHECK CONSTRAINT with the same
    rule of the interval, that's ok and makes all sense.
    The problem is in the detach process where they don't add this CHECK
    CONSTRAINT automatically.
    So, if you detach a partition and try to attach it again to the same table,
    you can't. Then you need to manually add a check constraint, where you need
    to know how the data is partitioned and what is the interval inside of this
    table to create the constraint, why that when the engine has all this at the
    moment of the DETACH.

    3) Our tables have our CHECK CONSTRAINT, but these constraints aren't
    created to the derived table when the partition is detached.
    Then we get into the same problem of item 2 above. If you try to attach
    this table back, you can't.

    Both items, 2 and 3 force us to after the detach process, manually create
    constraints when it's not always so easy to identify which rule need to be
    recreated.

    4) Our tables have detached and fragmented indexes.
    We also set theses indexes to use the same fragmentation RANGE INTERVAL rule
    over the same field of the table, so, they match perfectly each partition.
    Theses indexes partition isn't reused after the detach.
    I don't know if there is any technical restriction, but theses partition
    could be detached together with the table, this way avoid to recreate any
    index.

    5) We work with multiple partitions, one partition by day .
    If we try to attach multiple partitions in a sequence we stuck in a lock
    exclusive after the first attach finished, even in a test environment were
    haven't any other user connected.
    Get worst, the 'onstat -g opn' doesn't return anybody accessing the
    table, the 'onstat -K' doesn't show any lock over it too.
    After digging why that, we discover the DbScheduler is that user.
    I didn't check what is doing, but since our script have the FORCE_DDL_EXEC
    set, we expected the ATTACH will force other sessions out and get the
    exclusive lock over the table, but that appears not to be true over the
    DbScheduler.



    ------------------------------
    Cesar Martins
    ------------------------------

    #Informix


  • 2.  RE: Table partition, ONLINE detach/attach issues.

    Posted Wed December 04, 2019 08:21 AM

    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
    ------------------------------



  • 3.  RE: Table partition, ONLINE detach/attach issues.

    Posted Wed December 04, 2019 08:53 AM
    Edited by System Fri January 20, 2023 04:11 PM
    Hi Fernando, 

    I don't believe I get confusion with that (attached indexes). 
    My reasoning the same as you. 

    However, that isn't what the documentation says and how the engine behaves .

    In the manual pages about this subject you will find a few of references like : 
    All indexes on the surviving table must have the same fragmentation scheme as the table. (That is, any indexes must be attached.)
    I quote this from the manual. 

    Also, the engine behave didn't match with the manual in a lot of points, that's why I say this looks like a paradox.

    If you want, just run the example below, what is the example from the manual with some modifications... 
    Just adjust your dbspace name... 

    database sysmaster ;
    
    drop database if exists test_frag ;
    create database test_frag in dbs1 with buffered log ;
    
    drop table if exists employee ;
    drop table if exists employee3 ;
    
    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 p0 VALUES < 200 IN dbs1,
    PARTITION p1 VALUES < 400 IN dbs1;
    
    --CREATE INDEX employee_id_idx ON employee(emp_id) ; -- WITH THIS, THEY WORK.
    --CREATE INDEX employee_id_idx ON employee(emp_id) in dbs1; -- WITH THIS , THE ALTER FRAGMENT DETACH FAIL WITH -25834.
    CREATE INDEX employee_id_idx ON employee(emp_id) in table; -- THIS NEVER WILL WORK, FAIL WITH -9665
    
    
    CREATE INDEX employee_dept_idx ON employee(dept_id);
    
    INSERT INTO employee VALUES (401, "Susan", "DV", 101, "123-45-6789");
    INSERT INTO employee VALUES (601, "David", "QA", 104, "987-65-4321");
    
    
    ALTER FRAGMENT ONLINE ON TABLE employee DETACH PARTITION sys_p2 employee3;





     



    ------------------------------
    Cesar Martins
    ------------------------------



  • 4.  RE: Table partition, ONLINE detach/attach issues.

    Posted Wed December 04, 2019 01:30 PM
    Edited by System Fri January 20, 2023 04:49 PM
      |   view attached
    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.sql
    database 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)
    
               0
    
    1 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 exprtext
    from sysfragments f, systables t
    where t.tabname = 'employee' and t.tabid = f.tabid
    order by 1,evalpos
    ;
    
    t s evalpos partition  exprtext
    
    I E 0       p0         (emp_id < 100 )
    I E 1       p1         (emp_id < 200 )
    I E 2       p2         (emp_id < 400 )
    I E 3       dbs1       remainder
    T E 0       p0         (emp_id < 100 )
    T E 1       p1         (emp_id < 200 )
    T E 2       p2         (emp_id < 400 )
    T E 3       dbs1       remainder
    
    8 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 exprtext
    from sysfragments f, systables t
    where t.tabname = 'employee' and t.tabid = f.tabid
    order by 1,evalpos
    ;
    
    t s evalpos partition  exprtext
    
    I N -1                 dbs1
    I N -2                 100
    I N -3                 emp_id
    I N 0       p1         VALUES < 100
    I N 4       sys_p4     VALUES >= 400. AND VALUES < 50
    I N 6       sys_p6     VALUES >= 600. AND VALUES < 70
    T N -1                 dbs1
    T N -2                 100
    T N -3                 emp_id
    T N 0       p1         VALUES < 100
    T N 4       sys_p4     VALUES >= 400. AND VALUES < 50
    T N 6       sys_p6     VALUES >= 600. AND VALUES < 70
    
    12 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-4321
    
    1 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
    ------------------------------

    Attachment(s)

    txt
    frag1.sql.txt   2 KB 1 version


  • 5.  RE: Table partition, ONLINE detach/attach issues.

    Posted Thu December 05, 2019 03:23 AM
    Hi Cesar,

    Off the topic of your first article, I also tested the Interval Fragmentation feature in Informix 11.70.FC9 and 12.10.FC13.
    According to the onlog command I ran (onlog -n), I noticed that the index of the interval fragment that detached was dropped.

    This seems to be the original behavior in interval partitions.
    However, the explanation that the index is dropped after detech does not appear to be in the IBM Knowledge Center.



    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 6.  RE: Table partition, ONLINE detach/attach issues.

    Posted Fri December 06, 2019 09:25 AM
    Hello SangGyu!!
    Yes, that could be a great feature to be added to the engine.
    However, I really want to know if someone uses all the features available today to confirm if all these limitations are correctly and then submit an RFE to improve this feature, which today is very, very raw...

    ------------------------------
    Cesar Martins
    ------------------------------



  • 7.  RE: Table partition, ONLINE detach/attach issues.

    Posted Fri December 06, 2019 01:11 PM

    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
    ------------------------------



  • 8.  RE: Table partition, ONLINE detach/attach issues.

    IBM Champion
    Posted Fri December 06, 2019 01:13 PM
    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


  • 9.  RE: Table partition, ONLINE detach/attach issues.

    Posted Fri December 06, 2019 03:27 PM
    Hi Fernando, 
    Thanks for your comments, that's really helped now.
    Thank you!

    Few comments:

    1. I agree and I think this documentation wasn't clear and there is a huge lack of information there.
    That induce me to misunderstood how this works.

    2. Ok, I've followed your instruction and ran again as the manual says, using the create index without any storage option.
    However, I don't use the manual example because of the lack of data. I ran in my environment where a table has 600k records and +60 partitions.
    After creating few indexes, I ran oncheck -pt / -pe,  with them able to see the index following the same rules of the table partition and the detach online works properly.
    So, until now in my knowledge, we have attached and detached indexes, now appear to have a new 'gender', a special one, something between both, I will call it as detached auto partitioned.
    Definitively it isn't an attached index neither a regular detached index since it should be created without storage option. (this is the kind of lack of information in the manual)

    3. Weird, I have a brief memory from years ago were tables detached from a fragment by expression keep the indexes which have exactly the same fragmentation expression... well probably messing my knowledge with another product.
    Anyway, I will create a RFE with this request.

    4. Theses tables are our production/stock movements, they have a lot of information including the cost valuation too. Montly each table can reach 6~10millions of records, according to company production. To maintain the performance we keep the last couple of months only and move out to historical table older data (insert/delete). This data are also used for stock accounting and from times in time, we need to back older data to main tables for the users able to run again the accounting and do adjustments (that's mean, detach from the historical table and attach in the main table)
    Currently, all this process ran thru application and we trying to make it more efficient, one of the premises is run thru the application without interference or dependency of the system staff (analyst/dbas).


    So, if I understood right now, to use the range interval attach/detach online feature, you must achieve these requirements:
    • It is mandatory the create index should have no storage option set.
    • The index will be created in the same dbspace of the table. 
      • Table and index will work in the same page-size.
        If your environment used to keep them separated page-size, beware.
        (my situation here, data=4k, indexes=8k)
    • To attach a table you need to
      • Manually create all constraints already pre-existing in the main table
      • Manually create the constraint used to check the range interval
      • Manually create all indexes in the same dbspace of the table to be attached
    • A partition detached will not have any of the constraints and indexes pre-existing in the main table.
      (the same mentioned above)
    • Not accept attach tables with serial (-866).

    Problems still get :
    a) create unique index (detached auto partitioned) in a partitioned table, fail with error :  872: Invalid fragment strategy or expression for the unique index..
    I need to check with the support if this is a defect or real limitation.

    b) Using pdqpriority 100 , get almost instantly 212: Cannot add index. 12: Not enough core
    Also, I need to check with the support.

    About the documentation, not only the manual induce to misunderstand all this:

    $ finderr 25834
    -25834 The specified alter fragment operation cannot be performed online.
    . . .
    The following conditions apply for attaching an interval fragment online:
    . . .
    - All indexes on the surviving table has to be fragmented same as table
    i.e. all indexes must be attached.

    ------------------------------
    Cesar Martins
    ------------------------------