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

On 01-Jul-2010 05:27, Charles Wilt wrote:

My understanding is that simply removing the logical file member
is enough. With the member removed the access path is gone and
thus not maintained during the load/purge. You don't have to
actually delete the logical.

With RMVM the LFM will be gone, but the AccPth may remain. The ownership [by a file, not by a user] of a shared access path is transient, such that the ownership may simply be transferred to another LF with a compatible definition. With no remaining LFM, only the key\index definitions remain for how to create the keyed AccPth [stored as part of the logical file; e.g. shown in DSPFD], until a logical file member is added. So removing all of the LF members will indeed remove maintenance requirements, for lack of any logical access paths; although physical access paths, to include constraints, may remain.

On the other hand, recreating the logicals in an optimized order
does have benefits....

As well as having potential side effects. Sharing must be understood for its consequences on the applications; i.e. sharing prevented, where sharing is incompatible with how the index is used.

I wonder if it's possible to automate the
process of rebuilding the logicals in an optimized order...

Consider that the restore of logical files in a single request will order the create of those LFs for optimal sharing. With SAV\DLT\RST using the option to save private authorities, the problem for DLTF making that series of actions an effective DR scenario [i.e. such that authority recovery is required] can be resolved by restoring those private authorities. The RSTOBJ MBROPT(*NEW) OPTION(*OLD) allows restoring removed members, with no changes to the sharing. Restoring only the LF or LFM [i.e. with the data member(s) being restored in the same request] effects access path rebuilds, but ALCOBJ ((TheRstLib *LIB *EXCLRD)) prevents the EDTRBDAP from building the list of required rebuilds, so the process that holds that lock can perform the rebuilds under whatever is the desired work management setup versus deferring the rebuild activity to the RunPty-52 QDBSRV## jobs.

More comments inline...

On Wed, Jun 30, 2010 at 10:27 PM, Evan Harris wrote:

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.

Not sure what "complex" means here. Duplicate key ordering [e.g. FCFO, FIFO, LIFO] must be the same, and the key fields must be the same from left-to-right [first to Nth], with the index having fewer than the N keys created after the index having the N keys.

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.

The OS database server jobs may allow more concurrency for optimal builds, but those jobs run at RunPty(52) which may not be ideal.

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.

Removing only the members prevents loss of private authority and ownership, and I believe effect of STRJRNAP.

On Wed, Jun 30, 2010 at 3:30 AM, Charles Wilt 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?

SQL database file objects do not [properly, at least] enable removing the members. The effect is possible, but not supported; a nuance not prevented, solely for benefit of development in data collection for problem investigations.

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

Consider data copy versus reorganize when a significant percentage of data will be deleted. Also consider active reorg versus exclusive reorganize which is probably the best possible choice regardless of index maintenance concerns. I would actually just leave all the objects alone, and take the hit in maintenance for both the delete activity and while reclaiming the deleted rows.

I'm thinking that at least the logical file member portion
may also be useful in a bulk data load process..

Yes. However IMO leaving the unique indexes is better than finding out hours into a rebuild that there were duplicates added to the physical dataspace when they should have been prevented. I also would use SAV\RMVM\RST to get the original attributes back rather than mucking with trying to create a utility to add the members back.

Regards, Chuck