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.  Decrease Size of Root Dbspace

    Posted 05/22/20 03:41 PM
    IDS 12.10.FC12
    IDS 14.10.FC3 (upgrading very soon, so if it can be done in 14 that would be great)
    Solaris 10 1/13

    Consider the following scenario:

    Many years ago an experimental Informix instance is created.  The Oracle S.A.M.E. (Stripe and Mirror Everything) method is adopted, and everything (I mean everything) remains in the root dbspace (which is distributed over many spindles to spread all I/O evenly across all drives).

    Some years later, a DBA comes along and wants to enhance performance of some tables (at expense of others).  So, he creates new and separate dbspaces for tables, indexes, sblobs, logical logs, physical logs, and temp spaces.  He uses dbexport and dbimport to move all the databases out of the old root dbspace.  In fact, he moves everything (except stuff he can't, such as reserved pages) out of the old root dbspace.

    Now a root dbspace that once needed 100GB requires almost no space.

    IOW, now almost 100GB of space is completely wasted.

    So, the DBA thinks, "What a waste", and wants to shrink the root dbspace by about 99%.

    How can this be done?

    Thank you.

    DG

    ------------------------------
    David Grove
    ------------------------------

    #Informix


  • 2.  RE: Decrease Size of Root Dbspace

    Posted 05/22/20 03:45 PM
    Use ER to migrate and resize at that point ?

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





  • 3.  RE: Decrease Size of Root Dbspace

    Posted 05/22/20 04:42 PM
    If there are multiple chunks you can drop any uninhabited chunks.  Other than that, you are stuck.

    Art





  • 4.  RE: Decrease Size of Root Dbspace

    Posted 05/22/20 04:46 PM
    Via onstat -d output, how many chunks are there in the root space presently?
    If there is only 1, then you are screwed.
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The current Informix Roadshow presentations are here:
     
     
    The current internal ZACS Informix Page can be found here:
     
     
     



    Original Message


  • 5.  RE: Decrease Size of Root Dbspace

    Posted 05/22/20 04:59 PM
    Any chunk with 53 pages used only is a candidate to be dropped if there is more than one chunk.
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The current Informix Roadshow presentations are here:
     
     
    The current internal ZACS Informix Page can be found here:
     




    Original Message


  • 6.  RE: Decrease Size of Root Dbspace

    Posted 05/22/20 05:55 PM
    I thank you all, for your helpful replies.

    Not the situation I was hoping for-- but it is what I expected.  (Just was hoping that I was insufficiently knowledgeable in Informix admin, and there was some admin capability of which I was unaware, to manage the root dbspace.)

    We have two such instances that I want to re-engineer.  One has multiple chunks in the root dbspace-- so I can get rid of all but one.  The other has only a single chunk.  In both cases, I will be left with a >50GB chunk for the essential rootdbs objects.

    Doesn't seem "neat and tidy", but I guess we'll just carry around the excess baggage.

    Or, I had thought about creating a new instance (with a small root dbspace) from scratch, and using dbimport (actually Art's replacement because of the parallelization that speeds things up quite a bit) to re-establish all the databases.  But, then I thought that means losing the current sysadmin database.  SInce Scheduler database jobs don't belong to the database, but to sysadmin, that's just something else to worry about and give me grief.

    Is there any reason I can't use dbexport/dbimport to move the current sysadmin database to a newly minted instance, into which I import all the old databases (but with a smaller root dbspace?)  Exporting and importing all the databases seems an inelegant and time-consuming way to achieve the desired result, but I'm thinking it would probably work.  Would you concur?

    There sure are a lot of loose ends in Informix, with regard to admin capabilities.  I think it boils down to a philosophical thing.  My world is database-centric.  Informix's world is instance-centric.

    Anyway, I appreciate the comments.

    Onward and upward.

    Thank you.

    David Grove
    Alaska Dept. of Corrections

    ------------------------------
    David Grove
    ------------------------------



  • 7.  RE: Decrease Size of Root Dbspace

    Posted 05/22/20 06:02 PM
    David:

    You can use myexport/myimport for sysadmin. Just stop the scheduler, then delete all rows from all tables in the new instance, export from the old instance, then use myimport with the -e option which assumes that the database already exists and so just loads data.

    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


  • 8.  RE: Decrease Size of Root Dbspace

    Posted 05/22/20 06:32 PM
    Isn't there a task to move sysadmin ?

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



    Original Message


  • 9.  RE: Decrease Size of Root Dbspace

    Posted 05/22/20 06:46 PM

    Yes there is a task to move the sysadmin to another database, but the contents if there are any (such as SQLTRACE results, etc) are lost. After the move of the sysadmin database to another dbspace, the sysadmin will be as it were new.

    dbaccess sysadmin -
    execute function task("reset sysadmin", "<new dbspace>");

    Note: of course you have to exit from sysadmin if you were connected to it if you are using dbaccess. Also, stop your scheduler before moving the sysadmin.

    --     Khaled Bentebal   Email: khaled.bentebal@consult-ix.fr Site Web:  www.consult-ix.fr



    Original Message


  • 10.  RE: Decrease Size of Root Dbspace

    Posted 05/22/20 07:07 PM
    Yes, but David is planning to move it to a whole new instance to get a smaller rootdb now that z huge user database no longer lives there.

    Art



    Original Message


Related Content