Attaching a detached partition
Amit Patel got a syntax error attaching a detached partition back. Andreas Legner noted the IN dbspace clause is invalid with ALTER FRAGMENT ATTACH; Art Kagel gave a corrected statement using ATTACH ... AS PARTITION, and explained the partition stays in its original dbspace. For querying one partition, Art suggested filtering or the ifx_row_id string. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Dear All, I'm trying to attach a detached partition back to the table , for which I took backup of detached partition. But I'm getting syntax error. I'm testing on my personal laptop. OS : Redhat 7.9 , IDS 14.10 but when I change the process as below, it works. could some please help me with first solution? Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------
Hi Amit, I think the "IN <dbspace>" clause makes no sense with ALTER FRAGMENT ... ATTACH. ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------
Amit:
Besides the "IN dbspace" clause issue that Andreas noted, you have another syntax issue, try this:
alter fragment on table emp attache z_emp_tabl_prtn_202511 as partition tabl_prtn_202511;Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Dear Art/Andreas, Thanks for your help. it is working as "alter fragment on table emp attach z_emp_tabl_prtn_202511 as partition tabl_prtn_202511 (tabl_prtn = 202511) ; " but if we go without dbspace clause, then by default will data go to ROOT DBPACE? Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------
Amit:
No, not in the root dbspace. When you detached the partition it remained in whatever dbspace it was created in. The same thing happens when you attach a table to another table, the added table becomes a partition, yes, but it remains in whatever dbspace it currently resides in, it is no moved. That is why the "IN dbspace" clause is not allowed. You can verify this with the "oncheck -pT <database>:<table>" command or even with dbschema -ss ...
If you need to move the partition to a different dbspace, you can run an ALTER FRAGMENT INIT IN on the table before re-attaching it.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Dear Art, Can we query to select data from a particular Partition on table in infornix? ------------------------------ AMIT PATEL ------------------------------
Amit:
If the table was not partitioned as ROUND ROBIN, you can always apply filters that limit the search to one or more specific partitions based on the criteria used to create those partitions eliminating the others.
Also, you can also use the under-documented physical address of the row (ifx_row_id), here is an example:
select first 3 tabid from systables where ifx_row_id between '1049283:001' and '1049283:999';tabid
1
2
3
3 row(s) retrieved.
The ifx_row_id is a string made up of the partition and rowid separated by a colon. This works even for tables that are partitioned without the "WITH ROWID" clause. You can get the partnum for each partition from its row in the sysfragments table.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------