Reducing Pages HIGHWATER mark
in [DB2]
|
From: p175 on 15 Aug 2006 03:18 Express C v9.1 I'm having a hell of a job trying to reduce the highwater mark for various tablespaces in Express C. DB2MART advises to reorg several tables with LONGLOBDATA and to disconnect, reconnect between each step, but the tablespace snapshot is still showing a highwater mark as follows. Total number of pages = 2560000 Number of usable pages = 2559960 Number of used pages = 166368 Number of pending free pages = 75744 Number of free pages = 2317848 High water mark (pages) = 2559960 Current tablespace size (bytes) = 10485760000 Maximum tablespace size (bytes) = NONE Increase size (bytes) = AUTOMATIC Time of last successful resize = Last resize attempt failed = No Rebalancer Mode = No Rebalancing Minimum Recovery Time = Number of quiescers = 0 Number of containers = 1 I've followed the DB2MART instructions to the letter, reorgs seem to work fine, where am I going wrong ya think ? Thanks for the help
From: Phil Sherman on 15 Aug 2006 10:08 Did you include the LONGLOBDATA keyword in your reorg command? It should significantly increase the reorg time. See details in the Command Reference manual under REORG INDEXES/TABLE. It would have been helpful if you'd included the reorg command with your original post. Phil Sherman p175 wrote: > Express C v9.1 > > I'm having a hell of a job trying to reduce the highwater mark for > various tablespaces in Express C. DB2MART advises to reorg several > tables with LONGLOBDATA and to disconnect, reconnect between each step, > but the tablespace snapshot is still showing a highwater mark as > follows. > > Total number of pages = 2560000 > Number of usable pages = 2559960 > Number of used pages = 166368 > Number of pending free pages = 75744 > Number of free pages = 2317848 > High water mark (pages) = 2559960 > Current tablespace size (bytes) = 10485760000 > Maximum tablespace size (bytes) = NONE > Increase size (bytes) = AUTOMATIC > Time of last successful resize = > Last resize attempt failed = No > Rebalancer Mode = No Rebalancing > Minimum Recovery Time = > Number of quiescers = 0 > Number of containers = 1 > > I've followed the DB2MART instructions to the letter, reorgs seem to > work fine, where am I going wrong ya think ? > > Thanks for the help >
From: p175 on 15 Aug 2006 14:04 Yes I did Phil and the following the command I entered in all cases: DB2 REORG TABLE DB2ADMIN.ABC LONGLOBDATA RESETDICTIONARY
From: Josh Tiefenbach on 15 Aug 2006 23:50 > Total number of pages = 2560000 > Number of usable pages = 2559960 > Number of used pages = 166368 > Number of pending free pages = 75744 > Number of free pages = 2317848 > High water mark (pages) = 2559960 The fact that you have a non-zero number of pending-free pages suggests that you havent completely deactivated/reactivated the database. It wouldnt suprise me if the HWM is in one of those pending free extents. When you disconnected/reconnected to the db, was the another active session? The suggestion to bounce your connection is intended to ensure that there arent any active transactions in the db. jsoh
From: p175 on 15 Aug 2006 23:59
I know thats weird, as I am the only user and I was watching the deallocation - reallocation of bp memory etc as the connections bounced just to make sure. I just can't figure why it won't work, it's bizzarre. If I re-run the DB2DART program afterwards, it is no longer listing the tables that have been reorg'd so that seems to be working. I'm getting the highwater mark from a snapshot I run after the DB2DART process, so is there anything I should be doing differently there perhaps ? |