Moving Tables/Indexes

Hi, We are running an OLTP system on Informix 11.50.FC6 and have over 2,500 tables and 6,800 indexes spread across 350 2GB raw chunks. What is the best and fastest way to move those tables to 2 large chunks? Essentially our sys-admins want to move us from using 350 chunks to a few huge chunks (underneath there are really a multitude of disks/spindles). Thank You, Dave in4mixdba@gmail.com --bcaec552428a39a01a04c8559eeb

Hi, My idea would be to add a new big DBSpace and move the tables with a script using alter fragment .. init in new DBS This locks the table for some time, but you can do it table by table and minimize the downtime. Indexes should be re-created in a new DBS (separate, on different hardware). It should clear most of the 2G chunks, which are then detachable. You cannot drop the whole DBSpace in this case, cause it will have a reference from the database, which cannot be moved. An alter database sql command does not exist. If you want to get rid of the whole fragmented DBS, the only way I know of is to move the database with dbexport/dbimport -d. Going that way, I would set up a separate instance, containing only the new (large) chunks. Of course, the downtime is much bigger in this case, depending on your hardware. Marcus ----- Ursprüngliche Mail ----- Von: "Informix DBA" <in4mixdba@gmail.com> An: ids@iiug.org Gesendet: Dienstag, 28. August 2012 18:06:04 Betreff: Moving Tables/Indexes [28176] Hi, We are running an OLTP system on Informix 11.50.FC6 and have over 2,500 tables and 6,800 indexes spread across 350 2GB raw chunks. What is the best and fastest way to move those tables to 2 large chunks? Essentially our sys-admins want to move us from using 350 chunks to a few huge chunks (underneath there are really a multitude of disks/spindles). Thank You, Dave in4mixdba@gmail.com --bcaec552428a39a01a04c8559eeb ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

You can use ALTER FRAGMENT ... INIT IN <dbspace or fragmentation expression> However, note that two chunks is way too few. Informix flushes dirty pages at checkpoint time by assigning one cleaner thread per chunk. If you only have two massive chunks there will be only two threads flushing data. If your OLTP system is a busy one that may leave lots of data at risk which may cause very long delays recovering from a soft crash before the server will be back online. Yes, 350 chunks is way too many, however, two is far too few. Figure out the available throughput of concurrent IOs that can be processed by your array and make sure that your busy table extents are spread over enough chunks to keep the IO system humming without overloading it. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. On Tue, Aug 28, 2012 at 12:06 PM, Informix DBA <in4mixdba@gmail.com> wrote: > Hi, > > We are running an OLTP system on Informix 11.50.FC6 and have over 2,500 > tables and 6,800 indexes spread across 350 2GB raw chunks. What is the > best and fastest way to move those tables to 2 large chunks? Essentially > our sys-admins want to move us from using 350 chunks to a few huge chunks > (underneath there are really a multitude of disks/spindles). > > Thank You, > > Dave > in4mixdba@gmail.com > > --bcaec552428a39a01a04c8559eeb > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340af36202c004c8563495

You should also consider the idea of moving your biggest tables (according to its row size), to a dbspace with a bigger page size). That should enhance, memory consumption will allow you to fit more data into it. Not to mention index fragmentation, also better on bigger page sizes, and also table fragmentation would be reduced. But remember, as bigger is your page size, bigger is your IO page sizes transfer. Don´t push it too much!!! Hope it helps. Best regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator - Cleartech Ltda BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: Moving Tables/Indexes [28178] > Date: Tue, 28 Aug 2012 12:48:03 -0400 > > You can use ALTER FRAGMENT ... INIT IN <dbspace or fragmentation expression> > > However, note that two chunks is way too few. Informix flushes dirty pages > at checkpoint time by assigning one cleaner thread per chunk. If you only > have two massive chunks there will be only two threads flushing data. If > your OLTP system is a busy one that may leave lots of data at risk which > may cause very long delays recovering from a soft crash before the server > will be back online. > > Yes, 350 chunks is way too many, however, two is far too few. Figure out > the available throughput of concurrent IOs that can be processed by your > array and make sure that your busy table extents are spread over enough > chunks to keep the IO system humming without overloading it. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. > > On Tue, Aug 28, 2012 at 12:06 PM, Informix DBA <in4mixdba@gmail.com> wrote: > > > Hi, > > > > We are running an OLTP system on Informix 11.50.FC6 and have over 2,500 > > tables and 6,800 indexes spread across 350 2GB raw chunks. What is the > > best and fastest way to move those tables to 2 large chunks? Essentially > > our sys-admins want to move us from using 350 chunks to a few huge chunks > > (underneath there are really a multitude of disks/spindles). > > > > Thank You, > > > > Dave > > in4mixdba@gmail.com > > > > --bcaec552428a39a01a04c8559eeb > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae9340af36202c004c8563495 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >