Path: utzoo!attcan!uunet!cs.utexas.edu!execu!sequoia!painter
From: painter@sequoia.execu.com (Tom Painter)
Newsgroups: comp.databases
Subject: Emptying data files in Informix
Message-ID: <11507@sequoia.execu.com>
Date: 21 Mar 90 15:52:53 GMT
Organization: Execucom Systems Corp
Lines: 50

Is there any quick easy way to reduce the size of the .dat and .idx
files in an Informix databases when all of the records are deleted?

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.

I know of two ways that this could be done, neither is particularly 
pleasant: 

1.  Delete all records.  Alter the table and rebuild.  Then repair the
    alteration and rebuild.

    Problem:  The delete takes hours.  And the end users are not 
              sophisticated enough to do the alterations on the
              eight tables without error.  And I hope that they
              don't need to be.

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.

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.  My thought is that the empty data files would have the same 
structure as the live ones and that the database wouldn't be able to 
detect any difference, since (If I'm correct in this) the database updates
statistics each time isql in invoked.

Thanks for any and all help.  I can summarize to anyone that wants it.

Tom
-- 
-----------------------------------------------------------------------------
Tom Painter                             UUCP: ...!cs.utexas.edu!execu!painter
Execucom Systems Corp., Austin, Texas   Internet: painter@execu.com
(512) 327-7070                                    execu!painter@cs.utexas.edu
Disclaimer: My Company?  They'll claim my all waking hours, not my opinions.
-----------------------------------------------------------------------------