Table partition, ONLINE detach/attach issues.
Cesar Martins on 12.10.FC13 found ONLINE detach/attach of interval partitions failing (errors 9665, 25834) with attached or detached indexes. Fernando Nunes explained the docs are misleading: use plain CREATE INDEX with no storage clause so indexes follow the table fragmentation. That made online detach work for Cesar, who planned an RFE to keep indexes and constraints on detach.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
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 ------------------------------
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
------------------------------
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
------------------------------
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
------------------------------
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 ------------------------------
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 ------------------------------
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
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 coreAlso, 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
------------------------------
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g