I have tried this, and I observed the same thing as Wayne. DELETE
DBVOLUME will not force any reorganization. It runs too fast to be
attempting any beneficial reorganization. It just moves whole pages,
without regard to what parts of the B-tree shrubbery are in them. But
also I have not seen it make fragmentation worse either. (Fragmentation
is something I'm studying; it's more complicated an issue than I first
realized, but more about that at some other time.)

Once your DB gets to a certain size, it is no longer reasonable to
perform an UNLOAD/LOAD operation, because it would require more downtime
that we can afford. (i.e. something approximating the time it takes milk
to go bad in a properly adjusted refrigerator) So we just have to accept
a certain steady state of fragmentation as the working reality. (There
is a rumor of something in the pipeline by developers that might help
this - comments from anyone that went to SHARE in San Francisco?)

(Thank goodness those minor database bumbs and bruises are pretty well
self-healed by continuous migration, expiration, and reclamation!)

But, what you CAN achieve via DELETE DBVOLUME, and this is the ONLY way,
is I/O load balancing between physical volumes. Monitor this with Q
DBVOL F=D. If you lay out your new DBVOLs so that they will all be
pretty full, when you delete the old ones, the database will be balanced
among the new ones. Then, how do you grow? Allocate new DBVOLS on the
SAME physical vols as the ones you just spread your database out among,
but make sure the new ones are physically adjacent to the existing ones.
This works best with JBOD disks; those Gigantic Black Boxes 'O Raid are
too unimaginably complex to contemplate load balancing.

   Stage 1          Stage 2            Stage 3
   10gb DB          6gb DB             10gb DB
   55% full         92% full           55% full
   unbalanced       balanced           balanced

    PV1   PV2        PV3   PV4          PV3   PV4
  +-----+-----+                       +-----+-----+
  |5gb  |     |                       | 2gb | 2gb |
  |100% |     |                       |empty|empty|
  |full |     |    +-----+-----+      +-----+-----+
  |     |     |    | 3gb | 3gb |      | 3gb | 3gb |
  |     |5gb  |    |not  |not  |      |not  |not  |
  |     |10%  |    |quite|quite|      |quite|quite|
  |     |full |    |full |full |      |full |full |
  +-----+-----+    +-----+-----+      +-----+-----+

Why do PV1 and PV2 change to PV3 and PV4? If they are not separate, the
DELETE DBVOL that takes place between Stage 1 and Stage 2 will take
forever, making your disk arms thrash so hard the drives could walk
across the floor.

Another strategy I have (inadvertently!) used is to "fatten up" the
database by disabling expiration for a while, move/balance it with
DELETE DBVOL, and then trim it back down by restarting expiration. This
second strategy, though, will certainly increase fragmentation. Nobody
in their right mind would do this deliberately, but I did do it and it
worked extremely well for load balancing. (i.e. I'm at Stage 2,
balanced, and now only 60% full.)

Just remember that this is not an exact science, but with load balancing
precision is not necessary. Approximate will do.

DANGER! BEWARE! If you are using TSM Mirroring, your database will NOT
be mirrored during the DELETE DBVOL operation. Make a full DB backup
before you start, and contemplate OS or hardware mirroring or RAID as a
temporary measure while it is underway. TIVOLI: It would sure be nice to
DELETE DBVOL all mirror copies at once, in a mirrored fashion!

Roger Deschner      University of Illinois at Chicago     rogerd AT uic DOT edu
============ "In theory, theory and practice are the same, =============
========= but in practice, theory and practice are different." =========


On Fri, 20 Sep 2002, Wayne T. Smith wrote:

>That would be a nice feature, but, at least on my Tivoli ADSM V3 server,
>  doing a couple of volumes (about 5 gig out of 15 in use) had no effect
>on reported % utilization whatsoever.    cheers, wayne
>
>Seay, Paul wrote, in part:
>> One of our TSM Support Staff members went to the advanced class and was led
>> to believe you could get some reorg benefits doing the following
>>
>>         Define New DB Volumes whereever you want them.
>>         Perform DELETE DBVOLUME commands on each of the existing DBVOLUMEs
>> one at a time which causes the data to be moved.
>>
>> Our DBVOLUMES are on ESS disk so they are not mirrored.  Thus, the delete
>> causes a move of all the current data to other volumes.
>>
>> Has anyone ever heard of doing this to get some reorganization benefits?
>> If so, were the benefits, mild or significant?
>>
>> I would not be surprised if massive filespace deletes could be recaptured by
>> doing this.
>