Re: Best way of tidying up the database

Platinum makes a tool called TS/REORG which can help automate this process. You might want to check it out. -Chuck David Williams wrote: > > 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