Re: Best way of tidying up the database

In article <34D8A349.453F@bellsouth.net>, W. H. SMITH <carlson1@bellsouth.net> writes >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 :-) Yep. >> 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. >> Steps 2-7 can be simpilfied. dbexport to tape drop database dbimport from tape. >> 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 That is an Online thing. If this is SE I would backupx2 dbexport drop database backup any files on that filesystem (Step A) mkfs the filesystem again restore the files from step A dbimport the database. This will ensure that - the datbase uses contiguous space - the filesystem is consistent >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. > Remember when you dbimport that - extent doubling occurs every 16 extents - adjacent extents get joined together - dbimport will import such that eacj table is contiguous i.e. one extent. Of course if you have >1 database in Online you will need to dbexport tem ALL, drop them ALL, then then dbimport each one in turn. >We do this about once every four or five months (wish it were sooner). My current company does it...NEVER... just saw a system which had a table with 100 extents!! Several had 93/94 extents and altogther there were ~100 with >50 extents and >30 with >8 extents. I didn't have time for a dbexport since it would have needed a tape and hence could not be automated overnight as a level 0 archive runs overnight. So I did alot of alter table.. next size 16386; alter index.. to cluster... Now we only have one table with >8 extents and it only has ~16 [well it's better than 100!]. >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. -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care