Re: RGZPFM while active issue - REORGANIZE RETURNED LESS STORAGE THAN ESTIMATED

I had a repeat of this issue.
From old PMR, here is the IBM doc summarizing the issue.
Previously, I had the issue at V6R1.
Now on V7R1.
Bottom line, even though the RGZPFM completed with no errors, space is not
returned for some files.
Anyone else seeing this issue?

*NONE Command 09/04/14 17:26:04.085501 QCADRV
QSYS 03BA RGZPFM QGPL 0062
Message . . . . : 5500 - RGZPFM
FILE(CURBSTONE/CCFP4000) MBR(CCFP4000)
RBDACCPTH(*NO) ALWCANCEL(*YES)
LOCK(*SHRUPD)
*NONE Diagnostic 09/04/14 17:38:26.730345
QDBFFCPY QSYS 0181 RGZPFM QGPL 0062
Message . . . . : REORGANIZE
RETURNED LESS STORAGE THAN ESTIMATED.

IBM Software
Technical Document

Document Number: 631414267
____________________________________________________________
Functional Area: DB2 for i5/OS
SubFunctional Area: DB2/400 Physical and Logical Files
SubSubFunctional Area: General
____________________________________________________________
Product: OS/400 DATABASE (5722SS1DB); I5/OS (5761SS1DB); BASE
(5770SS1DB)
Release: V5R4M0; V5R4M5; V6R1M0; V6R1M1; V7R1M0

Classification: Entitled/Advanced

Keywords:
____________________________________________________________
Document Title:Basics on Reorganize While Active
Document Description:
This document provides basic information on Reorganize While Active.

When running Reorganize While Active, it is important to understand how
this is being implemented.

First, the following parameters come into play:

Rebuild access paths . . . . . . > *NO (meaning they are maintained
while the RGZPFM is running)
Allow cancel . . . . . . . . . . > *YES

Note that the Lock state parameter will determine if users can read the
data or update the data.

The reorganize is moving rows (deleting and inserting). This is being
done to get all the deleted records at the bottom of the file. You will
see this in the journal by seeing a delete and insert for every row that
needs to be moved. This can cause a lot of journal activity and an
increase to journal receiver size or number of journal receivers generally
expected.

Commitment control is used when the job is deleting and reinserting the
rows. It also moves a set of rows before a commit is issued. The number
of rows moved per transaction varies between 1 and 4000. It is up to the
system to adjust how many rows it moves before a commit is issued.
Production jobs that are running with an isolation level of Cursor
Stability or above may wait for rows that have been moved until the commit
is issued. The default record wait time out is 60 seconds. If the record
wait time has been modified to be much smaller, lock time out errors are
possible.

A suggestion to reduce the possibility of row lock waits by production
jobs is to have the reorganize job issue OVRDBF FILE(FILE) WAITRCD(1) prior
to the reorganize. This will reduce the record wait time the reorganize
job is waiting to lock a row and limit the time the set of records are
locked. If the job is unable to get a record lock during the 1-second wait
time, it will not only skip that row and move on; it will also reduce the
number of rows per transaction.

When using *SHRUPD lock, it is important to understand how production jobs
inserting into the file will affect the RGZPFM job and its ability to
return space. After the reorganize job runs through the file one time, it
will look to see if any additional rows have been inserted at the end of
the file since the reorganize started. The job will note what rows need to
be moved and try to move them to get as much space returned as possible.
If a row gets inserted at the end of the file after the last rows that
needed to be moved were noted, the system will not be able to give back as
much space. More activity on the file (inserts) can prevent space being
returned. Files set up with Reuse Deleted Records *NO requires all inserts
to be placed at the end of the file. This increases the chance of limited
space being returned. Files that are set up to Reuse Deleted Records *Yes
will have a greater chance of production jobs inserting rows in the middle
of the file and not impacti
ng the RGZPFM job. There is still a chance that rows can get inserted at
the end and prevent space being returned even with Reuse Deleted Records
turned on.

For this reason, you may need to run the RGZPFM several times in order to
see space returned. In some cases, a maintenance window is needed to run
RGZPFM with no inserts happening. If this is the case, let the reorganize
run during the week and move as much as it can and do much of the work.
You should perform this work as close to the maintenance window as you can
and, if it ends earlier, start another one. Then, during the down time,
the reorganize will have less work to do, and down time should be
decreased. There is no way to estimate how long the RGZPFM will run.

Use the journal to determine activity that is occurring on the file while
the RGZPFM is running (DSPJRN).

At R710 with DB group 24, message (CPF9898) and a general status message
will be issued with the status of this last phase of giving back space.

Prior to R610, the reorganize job would have to get an exclusive lock on
the file at the end to give any space back. At R610 and later, this is no
longer needed.

Lastly, when Reorganize While Active is used, the system ends at a 16 MB
segment boundary. This means that for smaller files or files with little
spaces used for deleted records, you may see the number of records set to
0; however, no space is returned.

Tips to help improve performance:

1. Use RGZPFM KEYFILE(*RPLDLTRCD). This may reduce the amount of rows
that are needed to be moved. This option should not be used if arrival
sequence is needed for the file.
2. Use SETOBJACC to bring the file into the pool that the RGZPFM is
running in during the preparation phase.



Thank You
_____
Paul Steinmetz
IBM i Systems Administrator

Pencor Services, Inc.
462 Delaware Ave
Palmerton Pa 18071

610-826-9117 work
610-826-9188 fax
610-349-0913 cell
610-377-6012 home

psteinmetz@xxxxxxxxxx<mailto:psteinmetz@xxxxxxxxxx>
http://www.pencor.com/

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.