Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Attaching a detached partition

    Posted Wed November 05, 2025 02:15 AM

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


  • 2.  RE: Attaching a detached partition

    Posted Wed November 05, 2025 04:59 AM

    Hi Amit,

    I think the "IN <dbspace>" clause makes no sense with ALTER FRAGMENT ... ATTACH.



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------

    Original Message


  • 3.  RE: Attaching a detached partition

    Posted Wed November 05, 2025 07:06 AM

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

    Original Message


  • 4.  RE: Attaching a detached partition

    Posted Wed November 05, 2025 12:13 PM

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

    Original Message


  • 5.  RE: Attaching a detached partition

    Posted Wed November 05, 2025 12:57 PM

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

    Original Message


  • 6.  RE: Attaching a detached partition

    Posted Thu November 06, 2025 11:37 PM

    Dear Art,

                   Can we query to select data from a particular Partition on table in infornix?



    ------------------------------
    AMIT PATEL
    ------------------------------

    Original Message


  • 7.  RE: Attaching a detached partition

    Posted Fri November 07, 2025 06:24 AM

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

    Original Message


Related Content