Spaceusage of LOBs
in [DB2]
|
From: Helmut Tessarek on 7 Jun 2010 15:27 Hi Michael, Sorry that it took so long, but I had to talk to one of the developers, since I'm not too familiar with that part of the code. You probably won't like the answer, but here it is anyway: LOBs have to be aligned internally. Depending on the index you are using for the reorg and the variation of LOB sizes, it might be that no space is reclaimed (on disk) during a reorg. But if you are deleting e.g. 10 GB of LOBs and the .LB files don't decrease in size after a reorg, you can insert 10 GB of LOBs again and the .LB files won't increase in size. Hope this helps. -- Helmut K. C. Tessarek DB2 Performance and Development /* Thou shalt not follow the NULL pointer for chaos and madness await thee at its end. */
From: Michael on 8 Jun 2010 07:24 On 7 Jun., 21:27, Helmut Tessarek <tessa...(a)evermeet.cx> wrote: > Hi Michael, > > Sorry that it took so long, but I had to talk to one of the developers, since > I'm not too familiar with that part of the code. > > You probably won't like the answer, but here it is anyway: > > LOBs have to be aligned internally. Depending on the index you are using for > the reorg and the variation of LOB sizes, it might be that no space is > reclaimed (on disk) during a reorg. > But if you are deleting e.g. 10 GB of LOBs and the .LB files don't decrease in > size after a reorg, you can insert 10 GB of LOBs again and the .LB files won't > increase in size. > > Hope this helps. > > -- > Helmut K. C. Tessarek > DB2 Performance and Development > > /* > Thou shalt not follow the NULL pointer for chaos and madness > await thee at its end. > */ Thanks Helmut, is there a different behaviour with DMS instead of SMS tablespaces and/ or in DB2 V9 or V9.5. Would it help to create a temporary index? Regards Michael
From: Helmut Tessarek on 8 Jun 2010 13:50
> is there a different behaviour with DMS instead of SMS tablespaces and/ > or in DB2 V9 or V9.5. Would it help to create a temporary index? There is not really a difference between DMS and SMS (regarding the reorg LOBs), since LOBs have to be aligned in DMS tablespaces as well. With DMS there is also the HWM barrier. The containers for a DMS tablespace can only be decreased to the point of the HWM. Pre 9.7 you had to use db2dart and reorg to lower the HWM, but this did not work if the high water mark was held by object table data extents. However in 9.7 there are commands to reduce the HWM and it will even work, if it is held by object table data extents. I'm not sure, if creating a temp index would help. You will have to try. But to be clear, I think that aligning the data to an index, which is dropped after the reorg, does not really make any sense. It would also be bad for performance. What you could do is an export of the table and importing it to a new table. This should reclaim the space, but I doubt that this is a valid solution for you. -- Helmut K. C. Tessarek DB2 Performance and Development /* Thou shalt not follow the NULL pointer for chaos and madness await thee at its end. */ |