I got ISAM error -142: overflow of tblspace page. What does this mean ?

finderr -142

This is a pretty intimidating message, but all it really means is that you exceeded the maximum number of allowable extents. Use my reorg1.sh script to reduce the number of extents and you should be fine.

Actually, until someone called me about this error, I didn't know what would happen if you exceeded the maximum number of extents. I knew the limit was around 200 extents, (for systems with a 2K page size), but I had never seen anyone actually reach that number of extents.

When I saw this error message, I didn't know right away what it was, But thanks to Mark Scranton's "Informix Internals" session at the Orlando User Conference, I was able to guess it was an extents problem. I used my script extent1.sh to check the number of extents - and sure enough, it had over 200 extents (227 to be exact). Thanks Mark!

A followup from Mark Scranton

Date: Tue, 05 Dec 2000 08:00:43 -0700
To: peterschmidt@erols.com
From: Mark Scranton
Subject: Re: Isam error -142

Hey Peter -

I believe that it means exactly what you have said...we've tried to add another extent to the extent list on the partition page. The extent list is slot 5 on that page, and he gets whatever room is left over after the filling of the first 4 slots:

Slot 1 - the 56 byte partition structure
Slot 2 - general table info (small)
Slot 3 - varchars/blobs (special columns as we call it)
Slot 4 - indexes.
Slot 5 - the extent list

Slots 3 and 4 may in fact be empty. Slot 5 gets the rest. Each extent entry in slot 5 is 8 bytes, plus another 8 bytes for the on-deck or cap entry. When this page - either 2K or 4K is full, that's it! No more entries for any of these slots. Thus, the overflow of the tblspace or partition page.

Make sense?

Thanks,
Mark.