Re: Informix database reorganization

You may want to check out the alter fragment statement. Art Kagel have posted several times on this theme so you should look for that at http://www.dejanews.com What I write here is based on what Art has posted and he may correct errors in this: It sounds like you have all your data tables in one dbspace. I assume it consists of many chunks. You might want to set up a number of dbspaces to put the data in. May be you would also fragment some tables over these dbspaces. Then each dbspace could consist of fewer chunks. When this is done you could use the alter fragment ... init in ... statement to move a table from one dbspace to another. This is the fastest way there is to do that, and you need not to bother whether about dropping and recreating constraints as you would with other ways of doing this. If a table is fragmented over several dbspaces you would probably move a fragment at a time in a similar way. If you use detached indexes you will have to look into any handling of those that might be necessary. Now if you set up a number of equal sized dbspaces but keep at least one free at all times you can keep using this strategy to move all tables in one dbspace to a free one in a round robin manner. If you move a single table at a time it will become unfragmented (reside in one extent) in the new dbspace. If you have multiple free dbspaces to move into you can of course move one table to each dbspace simultaneously. If you want to move more tables at a time you have to set the extent size appropriately before you start moving each table. You can only set the next extent size on an existing table so the first extent will be whatever it was when you created the table. This may cause the table to reside in two extents but that should be a minor problem. Moving tables in this way also makes the table unavailable while it's beeing moved, but this may be fast enough that you can find more available time to do it. On 28 Sep 1998 14:53:20 GMT, -=Eclypse=- <eclypse@cdc.net> wrote: > >We're looking for a way to dump and reload our Informix database (right >now 7.22UC3) that will cause the minimal amount of downtime to our users. >Our machines are RS/6000 F50s, AIX4.2.1, and we use SSA disk drives. Our >database server is a three-way box with 2GB of RAM. We have 6 dbspaces, >one for root, baan tables, temp, log, index, and data. As far as data >goes, we've got about 24 million rows of data and 71000 tables. We >use BaaN IV as our ERP package and the way our BaaN install has been >done has put plenty of tables in our Informix database. Right now, it would >take us about 5 days to do a full dump and reload. We have about 400 >tables with the number of extents over 4 (some as high as 100 extents) and >I know this is not helping our performance. We can dump and reload >individual tables, alter the next extent size, and this will help some. I've >also heard of people taking another drawer of disks and dumping those tables >out to it as well so that you put off the total re-org for a lot longer. Is >it possible to use the replication feature of Informix to have a separate, >identical server set up as your secondary, break the link so it becomes a >primary, point your clients to that server when the link is dead, dump >and reload the primary, then bring it back up, replicate the changes, and >then do the same to the secondary? > >We're not sure how you can maintain 100% uptime and still keep your database >in top shape. We're trying to find out how other people are doing this. It's >getting ever more difficult to get days of downtime (our next window is the >Thanksgiving holiday) because our company is becoming more dependant on the >BaaN system we have in place. > >Most of the reason that we're having these problems is due to the fact that >our implementation is still in progress and the tables are fragmenting more >because of this and as data is added, cleaned up, removed, etc. Can anyone >give me a few pointers on what we should expect, some "best practices," or >tell us how you maintain your database and your uptimes for your users? >Informix has been minimally helpful on this issue, even at their classes >the instructors give only vague answers because they want you to use their >consulting services. I guess what we're mainly trying to find out is "how >the big boys do it." We've had IBM run some BaaN and Informix sizing for >us, but they don't address the issues that we're concerned with. They >can say "you need these big boxes" but they don't address redundancy or >maintenance. > >I would certainly appreciate any help. We're wanting to purchase equipment >to do the job, but we have a one time shot to get it right the first time >and we want to make the right choices. Thanks in advance. > >Jon Freeland<*> >Database Administrator >Miller Industries, Inc. >eclypse@millerind.com Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)