Re: Concurrent Database Maintenance?

On 02-09-2010 23:28, shorti wrote: > No REORG..I'm not sure I know how to function.......does not > compute...does not compute.....does not :) Informix automatically manages your indexes. You can configure how it does it, or even prevent it from doing it, but that part is managed by the database (as it should). Regarding the data itself, it can reuse space left free by DELETEs. Update stats are needed if/when your data profile changes. This doesn't mean you shouldn't run them periodically... Update stats can run simultaneously with the normal database activity. LOCK MODE WAIT <N> as Art wrote is a good practice.... One thing I'm not sure if it was referenced in this thread is the number of extents in the tables. An extent is a sequential set of pages that belong to a table, index or partition. The maximum number of extents in Informix is very low (specially in 2K pages). This is NOT a problem if you plan well and monitor it regularly. Again, Informix does a pretty nice job to avoid that the tables reach an high number of extents. An online REORG is a request from customer that ever met a situation where a table reaches the maximum number of tables. That is a nasty situation for a 24x7 system, but we have to say it will only happen if you neglect your duties as a DBA. Nevertheless let's hope for good news in the upcoming releases related to these points. Currently you have REPACK and SHRINK. These can work online, but it may affect queries that do full scans (usually we don't like those in OLTP systems), so anyone using them online must be aware of this. We can create indexes online, but the problem with that related to your concerns, is that the index may not exist. So if you think about this to "reorg" and index, you'd have to drop it first. And this would of course impact the query plans... Again, you don't need to REORG them... Well, in fact, we saw a situation a few months ago here, where the current "state" of the index made the running of update stats very slow. Note that it did not affect the queries... Only a full index scan like the one done by the LOW option... And there was a workaround for it... So, in short, don't worry too much about not being able to do something you're used to do, because that may not be necessary in Informix. Again, years of experience and I never do "reorgs". I recall having a performance issue some years ago and I did test it in a "lab"... I recreated all the indexes did several measures and it did not help... What would have help appeared years later in V11 :) Regards.