I'm trying to create a testdatabase from a productiondatabase. The steps
I've gone through so far are:

export data with certain criteria from roughly 150 tables
drop f.k pointing to these tables
load replace into these tables
set integrity on these tables
recreate f.k
reorging and runstating tables
full offline backup

For the tablespace holding tables this worked great. The high water mark
is greatly reduced, and I can resize the containers.

Now, the higw water mark for the index tablespace is not reduced by
much. Running reorg indexes all for ..., does not help.

db2dart suggests that all indexes should be dropped and recreated for a
huge amount of tables, but this means that primary keys, and hence
foreign keys must also be dropped. Is there any other way to lower hw?

The difference in "Current highwater mark: ..." for table x and table
x+1 in output from db2dart, is almost equally distributed. That is no
single table will alone reduce tha hw greatly (I guess). Any hints
appreciated.

db2 v8.2 linux


/Lennart

one thing you can try is this:

find the index holding the hwm. if that index will fit in the
freespace below the hwm, create an identical index on the same table
with a different name. see if it raised the hwm. if it didn't then
you can drop the original index and rename the new index. do this for
each index that hold the hwm until you get it to the size you want.
you may not see the hwm decrease right away. sometimes (maybe all the
time) db2 will not reclaim the pending free space until a new object
is created. for this we create a temp object and then drop it.



On Apr 22, 7:19 am, Lennart <erik.lennart.jons...@gmail.com> wrote:
> I'm trying to create a testdatabase from a productiondatabase. The steps
> I've gone through so far are:
>
> export data with certain criteria from roughly 150 tables
> drop f.k pointing to these tables
> load replace into these tables
> set integrity on these tables
> recreate f.k
> reorging and runstating tables
> full offline backup
>
> For the tablespace holding tables this worked great. The high water mark
> is greatly reduced, and I can resize the containers.
>
> Now, the higw water mark for the index tablespace is not reduced by
> much. Running reorg indexes all for ..., does not help.
>
> db2dart suggests that all indexes should be dropped and recreated for a
> huge amount of tables, but this means that primary keys, and hence
> foreign keys must also be dropped. Is there any other way to lower hw?
>
> The difference in "Current highwater mark: ..." for table x and table
> x+1 in output from db2dart, is almost equally distributed. That is no
> single table will alone reduce tha hw greatly (I guess). Any hints
> appreciated.
>
> db2 v8.2 linux
>
> /Lennart