Unanswered: Informix 9 and the 16.7M page limit in infx7
Well, I've been having a lot of trouble lately. We're running 7.30UC3 (old and junky it is!) and we started getting the dreaded "no more extents" error - which according to the informix documentation means you need to unload and load. So we did. As it turns out, thanks to some posts here, the real problem was the 16.7M page limit. (What a waste of a week!)
So we're evaluating several options - but I'm wondering does Informix 9 still have that limit? Or are my only options to either institute a delete-old-data policy or fragment the table? (Fragmenting will be hard)
These limits are normal. If you have a correct database design, with correct First page sizes and NEXT page sizes, you should not have any problems with that limit. That's why they say you have to unload/load.
So you can change the extent sizes.
Informix is the only database on the market that fills the holes, caused by delete statements. So once you have the correct extent sizes, no problems occur.
otherwise, that sounds great.. and that is what me and my coworkers thought. So our plan was to copy the table (since load/unload isn't too feasible because we cannot have too much downtime) into a new table (with same schema) in a new dbspace that would live in cooked files on a new couple disks. Our old table lives in a dbspace on a bunch of raw devices and is nice and speedy. So we'd copy to the new table, then play the audit trail of what went on during that time into the new table, take the site down and rename the newtable to the old table. We could then sit around and think of a plan for moving the data back onto the fast disks. (We planned on fragmenting it on the way back to the fast disks).
After a week the copy/index build/stats update finished. We were ready to flip over - I played the audit trail and voila - got the error again. The new table had huge extent sizes and when I checked we had 16 extents - but 16.7M pages used up. The magic number. After checking here and usenet I learned the only solution is to delete data or to fragment.
I'd love to fragment but the issue becomes the fact that the guys who designed the db put everything in the same dbspace.. so I'd have to move all that data over as well and re-chunk everything into a series of dbspaces so I could fragment the data. Since we don't have resources or time to do this, we're going with a data expiration policy until perhaps sometime in the future we'll get to redo it.
It would be great if informix mentioned the 16.7M page limit in more places.. or gave separate errors for the situation ie "No more exents" means no more extents.. and "No more data pages" for that situation. Oh well.
Thanks for replying though!
PS: Are we in agreement informix's handling of textblobs is AWFUL? oh well..
Hi, I 'm sorry I misunderstood you.
This limit will be gone in the next server release 9.40, currently on beta. Coming out probably end of march.
I hope this could help you.
Lots of limits are gone in that release. It may be worth waiting for?
Did you check how Informix handles textblobs in version 9? CLOBS?
well, we're sort of hesitant about using brand new stuff. Although I will bring it up. Is there any ETA? I'd love to not have to unload data. Perhaps we'll use the data unload (we'll be keeping it, just offline) and then evaluate 9.4 when it comes out.
also depends on the migration.. Will you have to load/unload all your data or will it magically work? Load/unload kills us.. Not to mention I gotta get the powers that be to cough up money for it
In informix you only have to use load/unload to get your number of extents down. Not even that. you can still use 'alter table to fragment init in <new-dbspace>'
What do you mean by ETA?
migration works very smooth. You just have to install the new version of informix into a new directory. bring your old engien down. set the new INFORMIXDIR to point to the new software. Informix will migrate automatically.
there are some proceduers to speed things up but that's a different discussion.
9.40 is based on a very solid 9.30. Our beta customers have been testing the engine for 3 months now.
I don't think money will be a problem. If you have support on your current informix version, it is very cheap to convert the licences.
Did you try HPL to unload/load data? That is our fastest tool to do these things.
turns out we may be able to get 9.4 in here. We'll see.
as for HPL you lose most of the benefits of HPL when you have textblobs. Informix's massive weakness is how much it sucks with text blobs. (that and the 255 limit on varchars) but that is a different discussion.
had a talk with informix. It appears 9.4 fixes all those silly problems, and also does away with the 2GB/chunk limit.. hurray! (We always got a kick our array had like 400GB of space, but we only could use 40GB. A not so encouraging thing was they couldn't tell me if it was out of beta yet. So we'll see. It always requires a 64 bit OS - ala solaris 8. so. we'll see how this goes.
anybody ever try to do a rollback of an informix upgrade?