Re: Compression Tools

David M. Cole wrote: > > Does anyone know of compression tools for Informix tables? I submitted defragtable.ec in my original submission to the IIUG Software Repository, file utils_ak, and there are others there as well. In addition, if you are running 7.2x you can most quickly defragment a table using "ALTER FRAGMENT ON TABLE mytable INIT IN mydbspace". Where mydbspace can be any dbspace even the one the table already resides in if there is room for the second copy of the table. To insure best compression increase the tables NEXT SIZE with ALTER TABLE before doing the ALTER FRAGMENT so that if there are small extents free in the dbspace into which you are defragging the table you don't get a fragmented result table (been there, done that, AARRGGHH). How to choose NEXT SIZE? Several options: 1) Decide the maximum number of extents you will be happy with and make NEXT SIZE nptotal/nexts (nptotal from sysmaster:sysptnhdr, nexts is the number you determined NOT the nextns column from sysptnhdr). 2) Run oncheck -pe and look at the sizes of the free extents on the chunk(s) making up the dbspace into which you are defragging the table. Choose one large enough to eliminate all of the tiny extents that you determine are TOO SMALL. Use that value (minus a few for bitmap overhead) for NEXT SIZE. 3) Just set NEXT SIZE to a reasonable value that will limit future fragmentation to acceptable levels. If the table grows 10000 pages a month you might choose 60000 pages so you will only have two extents per year. Afterward, alter the table's NEXT SIZE again to a value like that suggested in 3) above. Art S. Kagel