Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Unanswered: reorg for LOBs to recover space

    Is a reorg with LONGLOBDATA the only way I can recover space for deleted LOBs? I have a table that is rather dynamic with many deletes of LOBs. I would rather not have to take the table offline to recover the space from these deletes. I am running 8.2 on Linux.

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Free space is reused. So if you have new LOBs that are added, then you won't have a problem, generally.

    Or do you notice a steadily growing long tablespace that would justify your concerns?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Posts
    4
    I have two tables actually. One has the long data with the main tablespace and another table that has long data in a separate long tablespace. They appear to be growing and not reusing space. I may have to reorg and keep closer track to see if this is actually true.

    So for normal table data a reorg is required to reclaim that space, but that can be done while online. For long data it will be reused as soon as it is deleted?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by jkemp101
    So for normal table data a reorg is required to reclaim that space, but that can be done while online. For long data it will be reused as soon as it is deleted?
    No, normal tables also don't need a REORG to reclaim free space (unless those tables are LOADed into or are append-only). The same applies to long tablespaces. The table spaces may be growing slightly because the new data to be inserted may not fit exactly in the space freed by the deleted data. DB2 will search for free space for each new row and take the first match - not necessarily the best match. So you could run into some fragmantation, leading to more space being needed.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Posts
    4
    So if I do a get snapshot for a table and look at the LOB Object pages counter, will it go down if I delete a bunch of LOBs or null them out? I thought they were not "freed" from the table until a reorg. Will the space of the deleted LOBs only be reused by the table they were deleted from or any other that is in that same tablespace?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I guess the real question is what you mean with "freed". If you mean that DB2 frees the disc space and makes it available for other files in the file system, the answer is: no, DB2 doesn't do that automatically. You need a reorg for that. But if you mean that pages are free to be reused by other, new LOBs, then the answer is: yes.

    Whether the LOB object page counter is a high water mark or counts the amount of allocated and occupied pages, I do not know - did you have a look at the manual for this counter?

    Also, a table is just a logical construct and a reorg cannot free anything from a table. It can free pages in a tablespace.

    I'm just giving an educated guess now: free/unused LOB pages in a table space can be reused by any new/updated LOB going in this (long) tablespace. So the pages are not "bound" to some table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Nov 2009
    Posts
    2

    Classic Reorg with LONGLOBDATA option

    We have recently deleted 100M rows of data from a table with 150M rows. This table has a CLOB column with length 10485760. db2pd still shows the lobsize of the table is 428GB (unchanged from prior to the purge). I understand I need to perform a classic reorg with the LONGLOBDATA option in order to reclaim the space.
    My questions are....
    1) Will I need +400 gigs of tempspace to reorg this table? I know in reality that after the deletes, this table is now much smaller than DB2 is reporting.
    2) Is there any way to estimate the additional time it will take to reorg with the LONGLOBDATA option? A typical reorg takes 5 hours. It's a production table, so I need to approximate the downtime.

    Does anyone have experience with this LONGLOBDATA option?

    Thanks
    Valerie

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Did you REORG online or offline? I remember that an online REORG does not release LOB space

  9. #9
    Join Date
    Nov 2009
    Posts
    2

    reorg for LOBs to recover space

    LOBs are by default not included in either an online or offline reorg, and can only be included if you use the Classic (offline) reorg with LONGLOBDATA parameter.

Posting Permissions

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