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/...60/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.