Re: Best way of tidying up the database

Ian Clark wrote: > > Hi All, > > I was considering a tidy up operation on our database. In the years > that it has been up and running it has never been properly maintained > in order for it to be as efficient as possible. > In fact, I modified the backup script the other night to include an > 'UPDATE STATISTICS' afterwards and that is probably the only > administration that it has seen in 5 years!! :-) > > My plan for giving the database an overhaul was as follows: > > 1. Backup the database. > 2. Backup the database. Never can be too carful :-) > 3. Produce a schema from which to re-create the database. > 4. Unload the data for each table to an ascii file. > 5. Delete the database. > 6. Recreate the database from the schema. > 7. Load all the data back. > > Sounds straightforward enough. Am I missing anything? > > Any views/comments would be most welcome. > Any plans to check the extent sizes? Planning for future growth would require changing the "extent / next extent" sizes in the schema. Just be sure to run "dbschema -ss" in order to save the 'server-specific' information, such as current extent sizes and table location. We do this about once every four or five months (wish it were sooner). It also gives me a chance to determine physical table layout on the system as a whole, thus, reducing disk I\\O bottlenecks. John Carlson Informix DBA WH Smith, Inc.