On 29-Jun-2016 15:27 -0500, Paul Nelson wrote:
On Wednesday, June 29, 2016 3:15 PM CRPence wrote:
On 29-Jun-2016 14:55 -0500, Paul Nelson wrote:
On 29-Jun-2016 14:40 -0500, Paul Nelson wrote:
Does anybody know the equivalent command in the SQL world?
That's probably it. If I add the index back after a file reorg,
will that set me up for level checks?
Ah. So the inquiry from the OP is regarding an intention to disable
the keyed Access Path (ACCPTH) [maintenance] during a Reorganize
Physical File Member (RGZPFM) against the underlying data?
So perhaps the AccPth of the INDEX is defined UNIQUE, thus
preventing changing the Maintenance (MAINT) attribute? In that
case, the DROP INDEX [or Delete File (DLTF)] can be used after a
Save Object (SAVOBJ), and the Restore Object (RSTOBJ) done
afterward -- there could be two restores, one first without the
member, and then another to restore the member, but there would be
no point in so doing. <<SNIP>>
We’re working on a huge file reorg project. We have a 17TB system
that’s over 75% full. We have determined we can get down around 50%
by getting rid of deleted records. Once we accomplish that, we'll
change over to reusing deleted records.
RMVM beforehand and ADDLFM afterward has always been the way to go
to avoid thrashing. But that was before the proliferation of SQL
indexes.
:-)
I may be forgetting something, but as I recall, for a RGZPFM, use of
RMVM and ADDLFM were not for performance reasons; not directly, except
for uniquely keyed LFs [because those are maintained rather than rebuilt
after-the-fact like the other LFM]. And doing that would reduce storage
to allow the effective duplication of the PFM to occur without pushing
limits so as to leaving /breathing room/ and thus more efficient
allocations during the run-time. The reason RMVM is not generally
helpful in that scenario, is that the database starts the request by
invalidating all of the non-unique access paths, and does not rebuild
them until after the compression of deleted records is completed; and I
believe those are run in the background. I suppose if they are built
synchronous to the requester, then removal of the LFMs would be good.
Ah... I see; I think I recall now. The use of RBDACCPTH(*OPTIMIZE)
has the LF access paths built in the background concurrently, whereas
the RBDACCPTH(*YES) has them built sequentially in the process that
requests the RGZPFM. So I expect if there is sufficient storage, then
you might just want to use that /optimize/ option and forget about the
hassle of trying to deal with the access paths separately; let the DB
figure out what to do with them..
Without having just seen\tested that, I was going to suggest that the
RMVM was very helpful for performance of reorganize requests that are
implemented [not with RGZPFM, but instead] as a Copy File (CPYF) request
that Compress-Out Delete Records (COMPRESS), for which deleted rows are
omitted [or as a copy by SQL INSERT for which only active rows are
selected], after which the separate ADDLFM requests can be scheduled and
run concurrently to whatever [tempered] activity level and run
priorities are deemed acceptable.
But if the considerations are for storage reasons:
As I recall, merely changing the maintenance does not reset the
DataSpace Index (DSI), so the storage for the access path remains taken.
And for lack of the ability to RMVM, the best option for use of the
old-style\dedicated reorg is to make a backup of the INDEX first [use
PVTAUT(*YES) to include authorities], effect the DROP INDEX, and then
after the reorg perform a restore of the INDEX -- the restore activity,
of course, effects the background rebuild. Drop [aka delete] and
recreate is often undesirable [and why sav\dlt\rst is "best"], so as to
avoid losing customizations from attribute defaults and to avoid loss of
the authorities [but unordered sav\dlt\rst for non-SQL LFs, care must be
taken to plan for shared vs non-shared access paths if not assured from
their original creation attributes].
However, please note that the newer\online reorg [specifying *YES for
the Allow Cancel (ALWCANCEL) on the RGZPFM] enables reorganizing the
data [e.g. for reclaiming deleted records] without either having to take
the file offline or having to rebuild any keyed Access Paths. The
physical file must be journaled however; system-managed or user-managed
deletion of the receivers keeps the storage in-check... as does the lack
of the requirement to make copies of [¿some of the?] journaled access
paths and the physical dataspace, as is required with the offline-reorg.
The more recent the release, the better are the capabilities of the
/online/ capabilities of the RGZPFM feature. Despite whatever reason
for considering RMVM, do review the possibility of using the online
reorg instead of RGZPFM or copying the data.
midrange.com
