By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,236 IT Pros & Developers. It's quick & easy.

Reducing Pages HIGHWATER mark

P: n/a
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

Aug 15 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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
Aug 15 '06 #2

P: n/a
Yes I did Phil and the following the command I entered in all cases:

DB2 REORG TABLE DB2ADMIN.ABC LONGLOBDATA RESETDICTIONARY

Aug 15 '06 #3

P: n/a
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

Aug 16 '06 #4

P: n/a
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 ?

Aug 16 '06 #5

P: n/a
I've also checked the consistency flag after deactivating and it is set
to yes so it appears everything should be fine. I have no idea why
there would be pending pages.

Aug 16 '06 #6

P: n/a
Here's a listing from DB2DART *after* having run a job that included
all the recommendations. What I don't understand is the highwater mark
does't correspond to step 1 and subsequent tables. It starts with
listing 106665 extents, but in step 1 says that the current is 6161 and
reduces this by the dat object size in subsequent steps.

Obviously what I need is to reclaim the 101176 extents below HWM.
--------------------------------------------------------------------------------------------

Highwater Mark: 2559960 pages, 106665 extents (extents #0 - 106664)
Lower highwater mark processing - phase start.
Current highwater mark: 106664
Desired highwater mark: 0
Number of used extents in tablespace: 5489
Number of free extents below original HWM: 101176
Number of free extents below desired HWM: 0
Number of free extents below current HWM: 101176
Step #1: Object ID = 274

=Offline REORG of this table using the LONGLOBDATA option (do not
specify a temporary tablespace).

Table: DB2ADMIN.T1
DAT object size: 440
INX object size: 0
XDA object size: 0
LF object size: 0
LOB object size: 0
LOBA object size: 0
BMP object size: 0

Total size of object parts: 440
Minimum number of extents that will move by this operation: 440

Current highwater mark: 6161
Desired highwater mark: 0
Number of used extents in tablespace: 5489
Number of free extents below original HWM: 101176
Number of free extents below desired HWM: 0
Number of free extents below current HWM: 673
Step #2: Object ID = 526

=Offline REORG of this table using the LONGLOBDATA option (do not
specify a temporary tablespace).

Table: DB2ADMIN.T2
DAT object size: 430
INX object size: 0
XDA object size: 0
LF object size: 0
LOB object size: 0
LOBA object size: 0
BMP object size: 0

Total size of object parts: 430
Minimum number of extents that will move by this operation: 430

Current highwater mark: 5673
Desired highwater mark: 0
Number of used extents in tablespace: 5489
Number of free extents below original HWM: 101176
Number of free extents below desired HWM: 0
Number of free extents below current HWM: 185
Step #3: Object ID = 260

=EXPORT all data from table, drop the table, disconnect, reconnect,
recreate the table, and LOAD the data back into the table.

Table: DB2ADMIN.T3
DAT object size: 265
INX object size: 0
XDA object size: 0
LF object size: 0
LOB object size: 0
LOBA object size: 0
BMP object size: 0

Total size of object parts: 265
Minimum number of extents that will move by this operation: 265

Current highwater mark: 5488
Desired highwater mark: 0
Number of used extents in tablespace: 5489
Number of free extents below original HWM: 101176
Number of free extents below desired HWM: 0
Number of free extents below current HWM: 0
Final highwater mark: Extent #5488 (5489 extents, 131736 pages).

** This cannot be lowered further as there are not enough free extents
to move the object holding the highwater mark.
Lower highwater mark processing - phase end.

Highwater mark processing - phase end.

Aug 16 '06 #7

P: n/a
Anyone ?

Aug 17 '06 #8

P: n/a
Here's a listing from DB2DART *after* having run a job that included
all the recommendations. What I don't understand is the highwater mark
does't correspond to step 1 and subsequent tables. It starts with
listing 106665 extents, but in step 1 says that the current is 6161 and
reduces this by the dat object size in subsequent steps.

Obviously what I need is to reclaim the 101176 extents below HWM.
Frankly, I'm a little confused by all the data that your dumping out -
I really dont have a clear idea whats going on.

Have you tried running db2dart with the option which prints out each
extent in the tablespace, its free/used status, and which object owns
it, if its in use? I believe its /LHWM - dont have the docs in front of
me at the moment.

Its possible that the HWM is being held up by a system page that cant
be moved - but I would have thought that dart would have mentioned
that.

jsoh

Aug 17 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.