Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2003
    Posts
    23

    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)


    thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    23

    Re: Informix 9 and the 16.7M page limit in infx7

    As it turns out, I found the limits in the release notes to a 9.3 install we have - the limit is still there. Oh well.

  3. #3
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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.
    rws

  4. #4
    Join Date
    Jan 2003
    Posts
    23
    page sizes? Do you mean extent sizes?

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

  5. #5
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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?
    rws

  6. #6
    Join Date
    Jan 2003
    Posts
    23
    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

  7. #7
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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.
    rws

  8. #8
    Join Date
    Jan 2003
    Posts
    23
    ETA = estimated time to arrival

    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.

    thanks for your help.

  9. #9
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    The limit on varchar is also gone in 9.40.
    If you want an overview of new features in 9.40, just mail me.
    I have a default presentation.
    At the moment we expect it to come end of march/begin of April.
    rws

  10. #10
    Join Date
    Jan 2003
    Posts
    23
    march/april? Odd the head dba guy here says it is already out. (But I don't always believe him). I do have a conf call with informix sales today where we'll find out all the nitty gritty.

    9.4 sounds a LOT nicer..

    do you know offhand if they finally made it so you can update a text blob? It is such a pain to have to delete/insert to update a text blob.

    I'll let you know what I find out after that call.. just have to wait for the west coast to wake up now

  11. #11
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    I know that they increased the varchar limit. Most of the TEXt datatypes can now be stored in varchars.
    rws

  12. #12
    Join Date
    Jan 2003
    Posts
    23
    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?

  13. #13
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    End of Beta is January 10th.
    End of January, they will have a Gamma drop.

    The Gamma drop will be the latest 'Beta' drop. It will be compared with 9.40.UC1

    End of March is planned GA date.

    I never did a rollback of an upgrade. I'm not sure what might happen. It is possible though to do a reversion towards a lower software level.
    rws

  14. #14
    Join Date
    Jan 2003
    Posts
    23
    hey, thanks a ton!

  15. #15
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    No problem, hope you got the presentation.
    rws

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •