Results 1 to 7 of 7

Thread: row overflows

  1. #1
    Join Date
    Dec 2008
    Posts
    44

    Unanswered: row overflows

    DB2 9.5FP4-AIX
    Table snapshot shows a table with lot of Overflows. Increased pctfree to 20% for the table & it didn't help with overflows; table is in 32K tablespace and compressed.

    Anything I can do to stop overflows?

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Ask the application folks to change the design so the application stops updating VARCHAR columns after they have been previously inserted (or stop it from happening so often). Consider using CHAR instead of VARCHAR columns depending on the size of the column that is being updated.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    did you execute reorg after changing pctfree ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Dec 2008
    Posts
    44
    Thanks for replies.

    Application change or data type change are not possible, as it is vendor database.

    Yes, offline reorg was done after increasing pctfree.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Percent Free is mainly used to maintain clustering order (correct page only) during inserts if you have a clustering index. It doesn't usually help for overflow rows, because if the VARCHAR column content is now made larger due to an update, the row will not fit in the same space anymore. But if you have some free space on the page, at least the overflow row "may" be put on same page, but it will still be an overflow since the indexes still point back to original location.

    An offline reorg will eliminate any existing overflow rows, regardless of whether you have any percent free. Reorg will also recreate the percent free on each page to the original amount requested.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Dec 2008
    Posts
    44
    Marcus,
    My understanding of Overflow is that the row does not fit in the same page after DML operation.

    "the overflow row "may" be put on same page, but it will still be an overflow since the indexes still point back to original location" - this doesn't seem right, if this is true, I would be seeing overflow for many tables. I think.

    Offline reorg eliminates the overflow, but, subsequent DML operations cause the overflow again.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by rajaraja_cholan View Post
    Marcus,
    My understanding of Overflow is that the row does not fit in the same page after DML operation.

    "the overflow row "may" be put on same page, but it will still be an overflow since the indexes still point back to original location" - this doesn't seem right, if this is true, I would be seeing overflow for many tables. I think.
    I don't see how that can be. When a row won't fit in the same space due to VARCHAR column UPDATE (and new value is larger than before), then the row is moved to a new location with a pointer left in the old location. The indexes are not updated, so even if the page is still correct, the row number in the page is wrong, so it is still considered an overflow row. Obviously, if the overflow row is on the same page as the original, that is a better overflow than if the page is different, since an additional page fetch is not required.

    Quote Originally Posted by rajaraja_cholan View Post
    Offline reorg eliminates the overflow, but, subsequent DML operations cause the overflow again.
    Yes, if an update occurs to a VARCHAR that makes the data contents larger, it will not fit in the same place. This is the root cause, and the only solution is to have the application not do this.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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