Hi all I got it by this way,
I could not do the db2dart since I read, ensure no connection to DB, PROD, I could not disconnect any, in this PROD
Then I run the
reorg table tablename
--> and it did eliminate fragmented data for me
Total pages = 180000
Useable pages = 179968
Used pages = 77984
Free pages = 101984
High water mark (pages) = 78016
I got it by reading your explanation, Geez, how could I just imagine that HWM is the max number of pages
used at history's, and stick with that wrong idea until Daniel said, The high-water mark is the page number of the highest allocated page in the table space!
and for sure John, I will do the test with DB2DART to see how useful this tool
Now, the thing is, I run 2 things
REORG TABLE xxxxx INDEX xxxxx allow no access --> to cluster the index I wanted
reorg table tablename --> for fragmented data
I wish I could see the way to run 1 command to doing that 2 things in one :-))
But I will stop here to let you work and my appreciation for talking to me today,
Anh
-----Original Message-----
From: DANIEL CALLAHAN
[mailto:db2udbdba-ezmlmshield-x94785674.[Email address protected]
Sent: March 30, 2006 1:38 PM
To: LazyDBA Discussion
Subject: RE: the HIGH WATER MARK value
"Schleicher,
Jeremy D."
<[Email Address Removed] To
n.fsu.edu> DANIEL CALLAHAN/Bedford/[Email Address Removed]
cc
03/30/2006 01:28
PM Subject
RE: the HIGH WATER MARK value
MANAGED BY DATABASE
USING
(File '/data/SPRDFI/table/ARAPP.DBF' 475000,
File '/data/SPRDFI/table/ARAPP1.DBF' 287500
Sent you the wrong one
Your problem is that the containers are not the same size. take a look in
the admin
manual on high water mark with different container sizes....
Its working as advertised.
Tablespace ID = 9
Name = ARAPP
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 762500
Useable pages = 762448
Used pages = 466064
Free pages = 296384
High water mark (pages) = 751760
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 48
Number of containers = 2
Minimum recovery time = 2006-03-29-13.09.06.000000
High water mark is 75K yet used is 46k Shouldn't I be able to lower the
size of the buffer pool to 50k if I wanted to?
Jeremy Schleicher
ERP Database Administrator
Florida State University
-----Original Message-----
From: DANIEL CALLAHAN [mailto:[Email address protected]
Sent: Thursday, March 30, 2006 1:25 PM
To: Schleicher, Jeremy D.
Subject: RE: the HIGH WATER MARK value
Total pages = 2268000
Useable pages = 2267952
Used pages = 1509136
Free pages = 758816
High water mark (pages) = 1509136
2267952 - 1509136 = 758816 free pages.
high water mark = 1509136 ... same as used pages.
????
"Schleicher,
Jeremy D."
<[Email Address Removed] n.fsu.edu> DANIEL CALLAHAN/Bedford/[Email Address Removed] 03/30/2006 01:19
PM
Subject
RE: the HIGH WATER MARK value
Here is one
Tablespace ID = 7
Name = APLARGE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 2268000
Useable pages = 2267952
Used pages = 1509136
Free pages = 758816
High water mark (pages) = 1509136
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 48
Number of containers = 3
Minimum recovery time = 2005-12-22-16.16.41.000000
Jeremy Schleicher
ERP Database Administrator
Florida State University
-----Original Message-----
From: DANIEL CALLAHAN [mailto:[Email address protected]
Sent: Thursday, March 30, 2006 1:17 PM
To: Schleicher, Jeremy D.
Subject: RE: the HIGH WATER MARK value
send it to me along with your dft_extent_sz and the freespace in the
tablespace.
(the list tablespaces show detail)
"Schleicher,
Jeremy D."
<[Email Address Removed] n.fsu.edu> DANIEL CALLAHAN/Bedford/[Email Address Removed] 03/30/2006 01:07
PM
Subject
RE: the HIGH WATER MARK value
When I do a list tablespaces show detail I see space allocated and space
in use, in some cases the numbers are rather far apart.
Jeremy Schleicher
ERP Database Administrator
Florida State University
-----Original Message-----
From: DANIEL CALLAHAN [mailto:[Email address protected]
Sent: Thursday, March 30, 2006 1:05 PM
To: Schleicher, Jeremy D.
Subject: RE: the HIGH WATER MARK value
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
How do you know that you have lots of empty space? Whats your free
space
set to?
and remember that it takes a minimum of 5 extents to start with.
"Schleicher
Jeremy D. "
<db2udbdba-ezmlms
To
hield-x29449487.x "LazyDBA Discussion"
[Email address protected] [Email address protected]
a.com>
cc
03/30/2006 12:56
Subject
PM RE: the HIGH WATER MARK value
Explain this to me then. I have a very high watermark but lots of empty
wasted space but can't reduce the size of the tablespace because of the
watermark. How can I reduce the size of the tablespace?
Jeremy Schleicher
ERP Database Administrator
Florida State University
-----Original Message-----
From: DANIEL CALLAHAN
[mailto:db2udbdba-ezmlmshield-x65117914.[Email address protected]
Sent: Thursday, March 30, 2006 12:54 PM
To: LazyDBA Discussion
Subject: RE: the HIGH WATER MARK value
The high-water mark is the page number of the highest allocated page in
the
table space. For example, a table space has 1000 pages and an extent
size
of 10, resulting in 100 extents. If the 42nd extent is the highest
allocated extent in the table space that means that the high-water mark
is
42 * 10 = 420 pages. This is not the same as used pages because some of
the
extents below the high-water mark may have been freed up such that they
are
available for reuse.
This means that you have data in that tablespace, or free space up to
that
high water mark to the end of the extent.
"Pham Anh "
<db2udbdba-ezmlms
hield-x67200436.x
To
[Email address protected] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
03/30/2006 12:44
PM
Subject
RE: the HIGH WATER MARK value
...This the PROD and the task reorg is done I would not be able to do
INPLACE as it is slower than normal reorg
My wonder is except, may be a unload, reload or INPLACE reorg, the
db2dart
would do an analyze..., I would not want to see
it in backup mode or whatever could be affected to users
Would any other way I would try ?
How is the UDB deal with that ? is it a bug here?
the HWM is staying there until when ?? and this is just statistic, how
it
could prevent us to reduce the unused space,
I become nasty to see that way of UDB I guess! oh what a design !
Thanks all
-----Original Message-----
From: Pham Anh
[mailto:db2udbdba-ezmlmshield-x83071587.[Email address protected]
Sent: March 30, 2006 12:04 PM
To: LazyDBA Discussion
Subject: RE: the HIGH WATER MARK value
Could your help me, I so need this inf and I am searching ...
.. also I have went throught the archived emails to see if the topic is
there, yes, it was, but could not find a
'firm' solution
I got a reply by John to "truncate" option on reorg, but I am doing the
reorg normal, not INPLACE
my rerog is
REORG TABLE xxxxx INDEX xxxxx allow no access
Thanks all
-----Original Message-----
From: Pham Anh
[mailto:db2udbdba-ezmlmshield-x2405649.[Email address protected]
Sent: March 30, 2006 10:57 AM
To: LazyDBA Discussion
Subject: the HIGH WATER MARK value
Good morning all,
I ran a reorg and no option USE, e.g it will reorg with temspace using
the
same Tablespace
and here is the after result,
Tablespace ID = 7
Name = S1WAAI012
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 180000
Useable pages = 179968
Used pages = 81248
Free pages = 98720
High water mark (pages) = 154048 -----> increased during
reorg
----------------
now when I want to reduce the size to 100 000, I got this
There is not enough space in the table space "S1WAAI012
" for the
specified action.
Explanation:
Space is being removed from a tablespace using one or more of
the DROP, REDUCE, and RESIZE container actions. However, the
amount of space being removed is greater than the amount of space
above the high-water mark.
----------------
How the High water mark (pages) would be updated to release that
one-time
statisctic and let we able to reduce space
at once when we want ---
Thanks for you reply
Anh
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page