Path: utzoo!attcan!uunet!lll-winken!brutus.cs.uiuc.edu!zaphod.mps.ohio-state.edu!think!husc6!bochner From: bochner@speed.harvard.edu (Harry Bochner) Newsgroups: comp.databases Subject: Re: Emptying data files in Informix Message-ID: Date: 21 Mar 90 23:07:28 GMT References: <11507@sequoia.execu.com> Sender: news@husc6.harvard.edu Organization: Aiken Computation Lab, Harvard University Lines: 45 In-reply-to: painter@sequoia.execu.com's message of 21 Mar 90 15:52:53 GMT In article <11507@sequoia.execu.com> painter@sequoia.execu.com (Tom Painter) writes: >Every 3 months, we load a database from tapes. There are over 60,000 >records that get split across eight tables. As a result the .dat and .idx >files are very large. When the new tapes arrive, I need to clear out all >of the records and load the empty files. > >The problem, of course, is that the files are not reduced in size when >rows are deleted (I assume that they are merely marked as deleted). So >after a few rounds of this, the files are 3-4 times as large as they >should be. You mean the deleted rows aren't being reused at all? I'm pretty sure INFORMIX 2.10.03 does reuse them: I have lots of tables that are hold temporary data for applications, and get emptied at the end of a run. They don't keep growing indefinitely, they just hold a 'high water mark' of the maximum data they ever had at one time. That's what I'd expect in your case, too. Is your version of INFORMIX up to date? >2. Use dbschema to create the appropriate sql statements. Drop the > tables, and rebuild. > Problem: The dbschema output would have to be editted each time > and, again the end users don't have the training for > that. Assuming the table structure doesn't change, there's no reason to edit the sql scripts. Just use dbschema once to get the current definitions, and edit the script (once) to include a drop statement before each create statement. Then run this sql script each time you need to flush the database. The advantage of this approach is the it's much faster to drop a large table and redefine it than to delete all its rows. >My Solution: >Any thoughts on problems caused if I built the identical tables in another >directory, left them empty, and moved them on top of the existing live >data files. Sounds to me like asking for trouble. I prefer not to use backdoor methods on anything I don't have source code for ... -- Harry Bochner bochner@endor.harvard.edu