RGZPFM Frequently Asked Questions

Question 1: Do I need to think about which parameters to use on RGZPFM?

Answer 1: Yes.  See TechNote RGZPFM options and comparison.

Question 2: Is there a preferred method to reorganize a file if it is keyed or non keyed? Is there a preferred method if the physical file is non keyed however a logical file has a key (unique or non-unique)?

Answer 2: This depends on the desired outcome, applications, activity of the file and maintenance window available. Following is a link to a chart that lists the types of reorganize operations and their features. Consider which features are most important when deciding to use Allow Cancel ALWCANCEL(*YES or *NO) , Rebuild Access Path RBDACCPTH (*YES or *NO) and the LOCK state used.

http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/dbp/rbaforeorgtypes.htm

Also consider if the order of the file needs to be maintained as arrival (first in first out), if the file is set to reuse deleted records, and jobs running while the reorg is running.
Note that running RGZPFM with ALWCANCEL(*YES) will delete/reinsert rows to move the row to a new location. This can cause incorrect output (INCORROUT) in other applications that are running at the same time. If we remove a row and then immediately add it back it is possible that a query might not find the row.

Consider using OVRDBF in the RGZPFM job and set record wait time to less than the normal record wait value. This will cause the RGZPFM job to not wait for records and locking rows for longer period of times (affecting application jobs).

Another possible solution if the file is read only is to:
duplicate the file with data,
reorganize the file copy while the queries still access the original file and,
when the reorganize is complete, save and restore the reorganized copy over the original file.

This would require an exclusive lock during the restore.


Question 3: Should I use *RPLDLTRCD for the KEYFILE parameter all of the time?

Answer 3: If you do NOT need to maintain the order of the physical file (First In First Out or Last in First Out) using RPLDLTRCD can be much faster. This option is suggested if the file is already set up to reuse deleted records. Check via DSPFD's Reuse deleted records (REUSEDLT) setting.


Question 4: Is there a specific process for files that utilize triggers or constraints? What effect will it have on triggers/referential integrity (RI) when data is being deleted and reinserted in a different position in the table as part of the normal reorganize physical file process?

Answer 4: ALWCANCEL(*NO) must be used if the physical file being reorganized is a parent in referential constraint with a delete rule of CASCADE, SET NULL, SET DEFAULT, or RESTRICT. Triggers will not be fired as rows are being moved; therefore files with triggers can be reorganized. Refer to message msgCPF3181.


Question 5: When using ALWCANCEL(*YES), under what circumstances should various LOCK options be used? Are their any risks with shared read or shared update?

Answer 5: That is something the database owner must decide based on the applications designed around it and desired outcome. Application jobs running during the reorg can affect how much space is given back and require the RGZPFM to run multiple times before space is given back. If this is sufficient then use the *SHRUPD option to allow normal processes to continue. If you need to gain as much space as possible consider using *EXCLRD and allow applications to read the file but not change the data.

The point of when space is returned depends on the options used. If the file is RPLDLTRCD(*YES) and KEYFILE(*RPLDLTRCD) is specified, you can get some storage back on a cancel. If KEYFILE is set to a file name or *NONE, space is not given back until RGZPFM is able to fully complete.


Question 6: Is there any risk to cancelling a reorganization?

Answer 6: If you might cancel the RGZPFM, then ALWCANCEL(*YES) should be used.
If RBDACCPTH(*YES) or RBDACCPTH(*OPTIMIZE) is specified, any access paths that were marked invalid at the start of the RGZPFM will be rebuilt from scratch.


Question 7: If a RGZPFM with KEYFILE(*RPLDLTRCD) and LOCK(*EXCLRD) is run, how does this impact someone reading the file using an ODBC SQL select statement?
Could the same record potentially be read twice? Or, not at all if an application is reading the file sequentially and the RGZPFM process moves the last row to a position previous to the application that is reading sequentially how will the application get that row or will it not get it at all?

Answer 7: Whether an ODBC, JDBC, and so on application might read the same row twice or not depends on the isolation level specified for the application. If Read Stability is used, there is no chance of reading the same row two times. Read stability will cause quite a bit of additional locking on rows. Therefore, care should be taken if the application is performance sensitive. There is a very small chance that a row might not be found because, for a few milliseconds, a given row is removed and then added back to the file. The same thing would occur if a user application deleted a row then immediately added it back to the file.


Question 8: How does the RGZPFM ALWCANCEL(*YES) pick up from where it left off? Are there any parameters that must be in order for this to occur or is it automatic?

Answer 8: If a reorganization is stopped and no significant changes have occurred to the file since the reorganize (such as a restore, alter table, clear, and so on) and the reorganize options are the same as the original reorganize, the reorganize will pick up where it left off. If significant changes have been made or the options specified on the reorganize are different, the reorganize starts over from scratch.

Note: Consider the RGZPFM that is ended and restarted as one unit of work.  
For example the first RGZPFM may have worked through the first 1/3 of the SQL Table / physical file during one maintenance window.

When the exact same RGZPFM is run during the next maintenance window, the RGZPFM picks up after the work has already been done.

If you want the RGZPFM to fill in newly empty rows within that first 1/3 of the SQL Table / physical file you will want to clean up the corresponding RGZPFM status file.  

See Viewing Status of RGZPFM through IBM i Access Client Solutions for more information on RGZPFM status files.

The RGZPFM help text will be updated after IBM i 7.4.


Question 9: Does RGZPFM reset the the number of increments or record capacity of a file?

Answer 9:

RGZPFM with ALWCANCEL(*NO) does not reset increments or record capacity.
RGZPFM with ALWCANCEL(*YES) reset the increments and record capacity.


Question 10: What are the performance impacts of RGZPFM with RBDACCPTH(*YES) Versus RBDACCPTH(*NO) ?

Answer 10: The net result of using RBDACCPTH(*YES) or RBDACCPTH(*NO) will be the same - so as far as performance on the file after the RGZPFM has completed, there will be no query optimization performance difference using either one.

There can be a performance difference during the RGZPFM, depending on what you mean by performance.

There is really a clock time you need to be aware of and an impact time.
The clock time is actually how long the RGZPFM runs.
The impact time is how long your users are actually locked out of the file and prevented from using it.

Generally speaking, the RBDACCPTH(*YES) will be shorter clock time for the RGZPFM; however, your users will be locked out of using the underlying physical file for the duration of the RGZPFM as an exclusive lock is required on the file for each index rebuild that is necessary/done. Therefore, in this case, your clock time will be shorter; however, the impact time will be longer. All the access paths are rebuilt synchronously.

On the other hand, the RBDACCPTH(*NO) with ALWCANCEL(*YES) may be a longer clock time as each I/O operation causes all the indexes to be updated at that time. This provides the allowance for other jobs to access the file between operations of maintenance. So other users can also be using the file during that time because the indexes are updated rather than completely rebuilt. In this case, your clock time can be longer; however, the impact time will be shorter.

The RBDACCPTH(*OPTIMIZE) attempts to determine which method would result in the access paths being built faster (clock time) and does not factor in impact time. The other thing with *OPTIMIZE is that if the indexes are totally rebuilt, they are done so asynchronously.

Question 11: How much DASD space does RGZPFM use?

Answer 11: The answer to this question depends on the ALWCANCEL parameter.

Doing an ALWCANCEL(*YES) does not actually use any significant space.

Rows are moved from one RRN (relative row number) slot to another RRN slot - under commitment control. In the journal for the table being reorganized you will see R/DL and R/PX journal entries as the rows are moved.

On the other hand, doing an ALWCANCEL(*NO) does use significant DASD space.

We will create a temporary file in the recovery library (QRECOVERY) as one of the steps.
For tables in an iASP, the recovery library name will be QRCY000xx, where xx is the ASP number.

The temporary file for the reorganization will be QDBRGxxxxx.

So for a *SYSBAS situation, you should ensure you have enough DASD space in *SYSBAS for a duplicate copy of the table.
And in an iASP situation you need that amount of space in the iASP.