Re: Help: Reducing Extents

Hi, Chad asked: > I am running a electronic document management system (EDMS) that uses > Informix 7.22 as a back end to manage the documents. The EDMS system > warns me when there are database problems that slow performance. In > this case it warned me that there were too many extends for the tables > listed below: > > DBMS Tables With More Than 5 Extents > # of Extents Type Name < details deleted > > How do I reduce the number of extents to improve performance?? I just went through this execurise for a client with several older Informix databases that was very fragemented with extents. It will require unloading and rebuilding each table. And if there is not enough free space in one big chunck, when you reload the table will be fragmented again. The steps we used are: 1. Analysis each tables space usage and calculate how much space each table will need. Also need to calculate growth. Since you have 7.X you can use the following query from the sysmaster database to get started: database sysmaster; select dbsname, tabname, count(*) num_of_extents, sum (pe_size ) pages_used, round (sum (pe_size ) * 2 { Your systems page size in KB } * 1.2 { Add 20% Growth factor }) ext_size, { First Extent Size in KB } round (sum (pe_size ) * 2 { Your systems page size in KB } * .2 { Estimated 20% Yearly Growth }) next_size { Next Extent Size in KB } from systabnames, sysptnext where partnum = pe_partnum group by 1, 2 order by 3 desc, 4 desc; Change the page size in the SQL to match your system and change the growth factor to match your system. My goal was to make the next extent size big enough so they should not have more then one extent per year, and could go eight years before have problems. 2. Unload all the data 3. Create a schema adding in the corrected extent sizes from step 1 above. 4. Drop the database and re-create it using the schema above. (Make sure you have good backups. I really like to build the new database on new disk drives and instead of droping the old database just plug in new disk drives with the new schema and keep the old ones as a backup. A month later after we are sure everything is working we reuse the disks) 4. Reload the data. 5 Test and go live. This is a major operation, make sure you test everything, and make sure you have GOOD backups, and make sure you plan well. Regards - Lester ############################################################################# # Lester Knutsen lester@advancedatatools.com # # Advanced DataTools Corporation Voice: 703-256-0267 # # Grant group privileges for Informix databases with DB Privileges # # Visit our Web page: http://www.advancedatatools.com # # Washington Area Informix User Group: http://www.access.digex.net/~waiug # #############################################################################