Considering the Upper Limit on Extents

Hi, There! I have been having such fun with extents as only the truly and completely evil should have. We have a number of customers who have reached or are just about to reach the maximum number of extents for certain tables. This is due to lack of effective database administration, I know, but it has happened and I am dealing with it. However, we are also in the process of moving these same customers to IDS 9.4 and have discovered that 9.4 handles extents differently. In IDS 7.31, the upper limit on extents depends on the page size and the table definition. To learn the upper limit on extents for a particular table, use the following set of easy to remember formulas: vcspace = 8 *vcolumns + 136 tcspace = 4 *tcolumns ixspace = 12 *indexes ixparts = 4 *icolumns extspace = pagesize -(vcspace + tcspace + ixspace + ixparts + 84) maxextents = extspace/8 Where: vcolumns is the number of columns that contain BYTE or TEXT data and VARCHAR data. tcolumns is the number of columns in the table. indexes is the number of indexes on the table. icolumns is the number of columns named in those indexes. pagesize is the size of a page reported by oncheck -pr. This is documented on page 4-32 of the Performance Guide for Informix Dynamic Server which can be found at http://publibfp.boulder.ibm.com/epubs/pdf/4357.pdf However, in IDS 9.4 and 10.0, this formula seems invalid. In the "Considering the Upper Limit on Extents" section of the IBM Informix Dynamic Server Performance Guide for these versions, we are instructed to add the frcnt value from oncheck -pP of the physical address of the table or index fragment to the number of extents reported in the oncheck -pt output of the table. This section of the Performance Guides is identical for both versions (right down to the doc bug in the -pt output) and can be found at: http://publib.boulder.ibm.com/epubs/html/25122960/perf189.htm (I like the html format of the 10.0 doc. I wish IBM would do this for the previous versions, too. It makes pointing people to the manuals far easier.) My problem is that output now varies significantly from database to database. I have tested on tables with identical schemas on Solaris 8, IDS 9.40.UC3 (space converted to big chunks after table created) and 9.40.FC4 (space converted to big chunks before table created), and on AIX 5, IDS 9.40.FC4 (no big chunks). The tables showed 219, 225, and 127, respectively. With the 7.3 formulas, I calculated 199 on Solaris and 455 on AIX. One customer has 250 extents already. I need to know what factors effect the upper limit on extents. This is not in the latest performance guides. I have 300 customers to advise. Sincerely, Christopher Coleman Database Analyst Medication Management Mediware Information Systems, Inc.