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 impacting 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/
midrange.com
