Results 1 to 5 of 5
  1. #1
    Join Date
    May 2013
    Posts
    8

    Unanswered: VARCHAR length changes

    Platform DB2 Z/OS-

    Lets say I have a table/tablespace(4k) with a VARCHAR(200) column. Hence, I have a 2 byte length field that represents the actual lengh of the data contained therein. Lets say I insert a row with the VARCHAR column being 50 bytes in length. Later I update that row/column and change the length from 50 to 100.

    How does the tablespace LDS store and manage the change in length of the the row/VARCHAR column? Does it use the freespace in the current page to store the subsequent 50 bytes? Does it use a pointer to address the location of the latter part of the VARCHAR?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This is how it works in DB2 LUW:

    DB2 will attempt to store the row in the same place (unlikely). If that fails, DB2 will create an overflow row, which stores the revised row in a new location (preferably on the same page, but if not then on another page) and indicates in the original location where the new location is. The indexes are not changed, as they still point to the original location (page number and row number within the page).

    Overflow rows are not good for performance since it takes two logical reads to retrieve them (from the original row location and the using the overflow information to the new location). You can see the number of overflow rows in the syscat.tables view (updated after a runstats).

    I think it works the same or similar in z/OS, but not sure where you find the number of overflow rows (maybe sysibm.systables).
    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
    May 2013
    Posts
    8
    Marcus, Thanks for directing me to sysibm.systables.

    I checked it. It has a column called SPLIT_ROWS CHAR(1). So, this looks like a switch rather than a count - probably indicates whether they exist (IBM site to reference this is down right now - so I cant look it up). Regardless, this seems to indicate that part of the row (not the whole row) is stored non-contiguously, elswhere.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by mikerexx View Post
    Marcus, Thanks for directing me to sysibm.systables.

    I checked it. It has a column called SPLIT_ROWS CHAR(1). So, this looks like a switch rather than a count - probably indicates whether they exist (IBM site to reference this is down right now - so I cant look it up). Regardless, this seems to indicate that part of the row (not the whole row) is stored non-contiguously, elswhere.
    In DB2 LUW, a row may be "split" if there are LOB columns, but that is different than an overflow row.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    mikerexx, look in table SYSIBM.SYSTABLEPART and columns NEARINDREF and FARINDREF. DB2 tries to put the row in the same location, the same page (if enough PCTFREE space is available), or on a page within (I think) 32 pages on either side (if enough FREEPAGE space is available). It has been awhile since I look into this and the number of pages may not be exactly right but it is in the ball-park. If it can't find space in those locations, it just adds the row to the last page (leaving a pointer in the original location no matter where it moved the row). Which is pretty much what Marcus indicated.

    NEARINDREF indicates the number of rows relocated near the original page and FARINDREF indicates the number of rows relocated far from the original page.

Posting Permissions

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