Informix

 View Only
Expand all | Collapse all

Table and index defragment

  • 1.  Table and index defragment

    Posted Tue May 05, 2020 05:45 AM
    Hi,

    I am having some problems with fragmented tables over several customers, as I have several indexes and constraints is there any way to achieve this problem without having to reconstruct everything?
    Is there a procedure in sysadmin for this?
    Has anyone had similar problems that can help me?

    Thanks for any help,

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    #Informix


  • 2.  RE: Table and index defragment

    IBM Champion
    Posted Tue May 05, 2020 06:34 AM
    So you want to turn a set of fragmented tables into non-fragmented tables, without a lot of interruption and without having to (spend the time to) rebuild everything (indices, constraints)?

    How about
    • creating the new tables side by side with the old ones, with slightly different names,
    • then moving the data and keeping it synchronized using ''loopback replication" (local ER, within same database)
    • until everything's fine and you can switch new for old tables by means of renaming? (I'd hope RI constraints will survive this.)
    HTH,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 3.  RE: Table and index defragment

    Posted Tue May 05, 2020 06:56 AM
    Edited by System Fri January 20, 2023 04:12 PM
    This is an interesting solution, regarding the index propagation features that exist since version 12.10.xC12W1 ...
    I would like to have some details of the configuration for local ER, because all the attempts I made to configure the local ER so far, none worked .. !!

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    Original Message


  • 4.  RE: Table and index defragment

    IBM Champion
    Posted Tue May 05, 2020 08:25 AM
    Let us know about those 'loopback replication' problems.

    There was a thread recently on "Asynchronous post commit triggers & loopback replication ERROR" that started out with a loopback replication definition problem, maybe that can help too.

    Andreas

    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 5.  RE: Table and index defragment

    Posted Tue May 05, 2020 08:54 AM
    First of all a quick setup guide, considering the various information I found I was never able to establish a connection to the same server ...

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    Original Message


  • 6.  RE: Table and index defragment

    IBM Champion
    Posted Tue May 05, 2020 09:37 AM
    onconfig:
    ---------
    DBSERVERNAME serv_a1
    DBSERVERALIASES serv_a2

    CDR_QDATA_SBSPACE some_sbspace(s)


    sqlhosts:
    ---------
    er_serv group - - i=1
    serv_a1 onsoctcp myhost service_a1 g=er_serv

    er_loop group - - i=2
    serv_a2 onsoctcp myhost service_a2 g=er_loop


    server definitions:
    --------------------
     cdr define server er_serv -I
    cdr define server er_loop -I -S er_serv -N


    Important points:
    • the port used for the 'loopback' pseudo ER server (serv_a2) can't be the same as needs to occur after the one used for the 'real' ER server (serv_a1) in DBSERVERNAME/DBSERVERALIASES list
    • the pseudo server has to be defined second, i.e. into an existing ER domain (of at least one server, the local 'real' one), 'syncing' itself from the real local server (same instance): -S er_serv
    • the pseudo server has to be defined as 'non-root' server under that real server (-N)
    From there on you can use the two ER nodes (er_serv and er_loop), e.g. in replicate definitions, (almost) the same way as any other ER nodes.

    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 7.  RE: Table and index defragment

    Posted Tue May 05, 2020 10:20 AM
    Thanks Andreas,

    I will test it, thats the information I was looking for :)

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    Original Message


  • 8.  RE: Table and index defragment

    IBM Champion
    Posted Tue May 05, 2020 08:02 AM

    What are the problems ?

     




    Original Message


  • 9.  RE: Table and index defragment

    Posted Tue May 05, 2020 08:54 AM
    First of all a quick setup guide, considering the various information I found I was never able to establish a connection to the same server ...

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    Original Message


  • 10.  RE: Table and index defragment

    Posted Tue May 05, 2020 09:05 AM
    Edited by System Fri January 20, 2023 04:14 PM
    @Sergio Peres

    I haven't read the parallel ​thread, but doesn't "ALTER FRAGMENT <table name> INIT IN < dbspace name>;" not do it for you?​​​

    ------------------------------
    Kirit Rana
    ------------------------------

    Original Message


  • 11.  RE: Table and index defragment

    Posted Tue May 05, 2020 09:26 AM
    yes it works, but as in most instances I only have 1 dbspace, that limits the option.
    although I can always use external tables, my concern is the indexes and constraints.

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    Original Message


  • 12.  RE: Table and index defragment

    Posted Tue May 05, 2020 10:07 AM
    Edited by System Fri January 20, 2023 04:25 PM
    So look here...

    • Attached indexes become non-fragmented indexes in the same dbspace as the new non-fragmented table.
    • Constraints that do not use Detached indexes become non-fragmented indexes in the same dbspace as the new non-fragmented table.
    • Fragmentation strategy of Detached indexes and Constraints that use Detached indexes remain unchanged.

    Don't worry about having just the one dbspace as you can re-organise into the same dbspace. Just ensure your temporary dbspace(s) are large enough to hold the entire table at least twice over.

    Hope it helps.


    ------------------------------
    Kirit Rana
    ------------------------------

    Original Message


  • 13.  RE: Table and index defragment

    IBM Champion
    Posted Tue May 05, 2020 09:23 AM
    Sergio:

    Are you asking about changing a partitioned table or index (ie created with FRAGMENT BY ...) to a non-partitioned table/index or are you asking about tables with too many non-contiguous extents that need to be reorganized ie defragmented?

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.







    Original Message


  • 14.  RE: Table and index defragment

    Posted Tue May 05, 2020 09:29 AM
    Hi Art,

    My problem are tables with too many non-contiguous extents and their reorganization.

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    Original Message


  • 15.  RE: Table and index defragment

    IBM Champion
    Posted Tue May 05, 2020 10:02 AM
    OK, so you have two good options. You can use the ALTER FRAGMENT ... INIT IN ... option which can be used to relocate a table of index EVEN into the same dbspace in which it already resides. But that requires some downtime to copy the table. 

    The other is to use the DEFRAGMENT API function:

    execute function task( 'defragment', 'database:"owner".table' );

    or

    execute function task( 'defragment partnum', <partnum> );

    These are restartable and online tasks. It is safe even if the server crashes (and you can manually restart the defragment after the server returns) and does not lock anything for more than a fraction of a second. It does not always succeed in reducing a partition to a single extent, but it will usually reduce the number of extents if there is sufficient free space in the dbspace to do that. You can combine this with using REPACK SHRINK on other tables to coalesce fragmented free space between extents in other tables into a smaller number of larger free blocks and then defragment again. The onstat -g defragment command reports the status of all active defragment runs.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.






    Original Message


  • 16.  RE: Table and index defragment

    Posted Tue May 05, 2020 10:17 AM
    I have used this procedure, but only defragments tables and indexes with a name, what I cannot defragment constraints created automatically by the engine, as they have space at the beginning.

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    Original Message


  • 17.  RE: Table and index defragment

    IBM Champion
    Posted Tue May 05, 2020 11:34 AM
    Sergio:

    You can use the DEFRAGMENT PARTNUM option for auto-named indexes! Also, I don't remember what release you are using, but you can rename auto-named indexes in releases 12.10.xC8 and later:

    RENAME INDEX 100_1 TO idx1;

    That will work to rename the index named " 100_1" to "idx1". 

    You can also get the partnum(s) for an index, even if it is an auto-generated index, from sysmaster:systabnames:
    > select * from systabnames where tabname = ' 111_23';

    partnum  5243076
    dbsname  timeseries
    owner    informix
    tabname   111_23
    collate  en_US.819
    dbsnum   5

    partnum  5244283
    dbsname  art
    owner    informix
    tabname   111_23
    collate  en_US.819
    dbsnum   5

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








    Original Message


  • 18.  RE: Table and index defragment

    Posted Tue May 05, 2020 11:49 AM
    My problem is that we have more than 3000 tables on each database and fragmentation levels are different over customers and tables. 
    I am trying to find any way to do it over several on the same way. We have more than 150 instances and don't have direct access.

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    Original Message