Decrease Size of Root Dbspace

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

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

If there are multiple chunks you can drop any uninhabited chunks. Other than that, you are stuck. Art

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: https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/informix-1410xc2-features?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=librarydocuments All presentations and the agenda used by the Roadshow can be found there. The current internal ZACS Informix Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/

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: https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/informix-1410xc2-features?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=librarydocuments All presentations and the agenda used by the Roadshow can be found there. The current internal ZACS Informix Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/

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

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 www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ 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.

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

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

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