Re: Utility to remove and re-add logical file members

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

I believe you will have to pay the piper sometime, no matter what. If 
you leave the access path around, it's being updated for every record 
copied. If you take it off and add it back, it'll be built in one 
operation. I think the latter will be faster. But YMMV.

Vern, who is facing similar issues with product update processing.

On 6/30/2010 9:27 PM, Evan Harris wrote:
> Hi Charles
>
> In at least one case dealing with a table of 150+ million rows I gained
> significant advantage by deleting the logicals and recreating them after my
> processing was done. (At least I think I gained an advantage - I may have a
> different view after posting this)
>
> In this case the logicals had been created as determined by development
> requirements (i.e. relatively randomly :) ) which provided an opportunity to
> re-create the logicals in a sequence that took advantage of access path
> sharing to save space, but more importantly to improve the speed of
> recreating the subsequent access paths (and probably maintenance as well).
>
> I tried to order the creation of the logicals in such a way as to allow
> creation of a new logical to leverage already existing access paths by doing
> some analysis on the keys of the logicals - essentially I created from most
> complex to least complex; there may have been better ways to do it. I also
> dual streamed the creation process so that I had two logicals being created
> concurrently and had similar key structures in the same job path.
>
> Maybe I needn't have actually done re-created the logicals - I don't know if
> just removing and adding the member would have the same effect, but some
> testing might shed some light on it.
>
> Regards
> Evan Harris
>
>
> On Wed, Jun 30, 2010 at 3:30 AM, Charles Wilt<charles.wilt@xxxxxxxxx>wrote:
>
>    
> > Before I start rolling my own, is anybody aware of a utility to remove
> > all members from dependent logical files (and SQL views/indexes) for a
> > given physical that will allow for the members to be automatically
> > re-added later?
> >
> > My intent it to purge data from files on our development machine.
> >
> > remove logical file members
> > (optionally) stop journalling
> > delete records from physical
> > reorg physical
> > (optionally) restart journalling
> > re-add logical file members
> >
> >
> > I'm thinking that at least the logical file member portion may also be
> > useful in a bulk data load process..
> >
> >      
>
>