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.

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?

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?

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.

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?

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.

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

Did you REORG online or offline? I remember that an online REORG does not release LOB 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.