RE: fragmented system tables

I can't say there is no better way, but I just been there / done that as part of a migration. First the whole database has to be exported/imported. I have ODS 7.22UC2 now. The systables do not come out with DB export thus I could find no way to alter initial extent sizes. I issued alter table statements to modify the next extent size prior to the loading of the database tables, indexes, etc. That means that for lots of data for a table like "sysprocplan" (because of 1200 stored procedures), the fewest extents I can have is 2. My predecessor left me with about 96 extents on sysprocplan prior to our migration! Mr. Doe's book on ODS and the SQL tutorial cover more on extent calcs. > ---------- > From: John Carlson[SMTP:carlson1@bellsouth.net] > Reply To: John Carlson > Sent: Wednesday, May 13, 1998 10:00 AM > To: informix-list@iiug.org > Subject: Re: fragmented system tables > > Volker Fraenkle wrote: > > > > Sven Tolkemit schrieb: > > > > > > Hi people, > > > > > > we discovered, that the system tables (systables,sysindexes, etc.) on > > > our Online 7.23.UC1 are very fragmented. We think, this leads into > > > performance problems. Is there a way to defragment the tables? > > > > > > Thanks for answer! > > > > > > Sven > > > > Yes, there is. You have to unload and drop the tables. After, you can > > create it with "first size" and "next size" (to prevent your tables from > > high fragmentation) and load the tables. > > > > Note: At creating a table, the default lock level is "page". > > > > If there is enough space on the disk you can unload and load a complete > > database with "dbexport"/"dbimport". Maybe, it works with tapes, too (I > > know, it "works" with tapes, but depending on the size of db, it is > > verry slow). > > Unless something has changed, should you do this with system tables? > I've never tried to drop and recreate a system table before. > > Last I heard, this is a big no-no. > > > John Carlson > Informix DBA > WH Smith, Inc. >