Informix

 View Only
  • 1.  Separate DBspace for indexes

    Posted Wed October 05, 2022 03:09 AM
    Hello all.

    I have a task to migrate from 9 to 14 Informix. I saw Lester Knutsen's webcasts and he recommended separating indexes from data.
    At the moment the indexes are not separate. Can I, as a DBA, automatically separate all indexes from data after I import data from 9 to 14 Informix. Or will my programmer need to recreate all the indexes in the new dbspace if we decide to split them up?

    Another question is probably asked a lot but I can't find an answer. I have a database of about 150 GB and a VM with SAS storage and Windows Server 2019. My version of Informix does not support fragmentation. And while I have no idea if we will allocate some table in another dbspace. So maybe not. How many chunks would be best in my situation? One large or 8-9 with 20 GB of space. I tried to create 3 fragments and marked them as extendable. When Informix automatically extends, it always extends only the same fragment.

    ------------------------------
    Alexander Ivanov
    ------------------------------


  • 2.  RE: Separate DBspace for indexes

    Posted Wed October 05, 2022 03:13 AM
    Look at alter fragment statement, that will move the indices for you

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 3.  RE: Separate DBspace for indexes
    Best Answer

    InnerCircle
    Posted Wed October 05, 2022 10:54 AM
    I guess by 'separate index' you mean index in a different dbspace from data, right?
    So e.g. your tables in a dbspace called datadbs, and associated indices in dbspace indexdbs.

    Since we seem to be talking 'import', so unload from old and load into new database server, you'd have to newly define the database, its tables and indices in the new server anyway ... and could just augment the CREATE INDEX statements with an 'IN indexdbs' clause.  Also be sure to not have any constraints defined inline in the CREATE TABLE statements as those would create implicit indices without direction to the indexdbs, so they'd reside with the tables.

    In case you're planning to use dbexport/dbimport utilities instead, simply edit the <database>.sql in the <database>.exp directory adding the 'IN indexdbs' clause.

    As for the chunk question:  you'd either use one extendable chunk per dbspace, or multiple non-extendable ones.  If you had multiple extendable ones, I'm not sure they'd be used evenly, and your evidence seems to confirm only (the first) one would grow.  Technically, multiple chunks per dbspace would allow flushing new data to all of them in parallel, during checkpoint, but in reality this depends on a number of other factors, and if this isn't going to be a high throughput system, you won't sense any difference.

    HTH,
     Andreas

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