Reorganization options

You can specify whether the reorganize operation on a physical file member can or can not be canceled. Consider the features of each option when deciding which option to use.

• ALWCANCEL(*NO): This is the traditional type of reorganize operation. A full copy of the data might be made, so you need up to two times the amount of space. This option cannot be canceled (suspended) and cannot fully run in parallel. It requires exclusive use of the physical file member.
• ALWCANCEL(*YES): The data rows are moved within the physical file member so that a full copy of the data is not required. The physical file must be journaled, however, so storage is necessary for the journal entries. You can use the journal receiver threshold to minimize the amount of storage used in a specific journal receiver.

  This option can be canceled (suspended) and restarted. It can run in parallel if the DB2® Symmetric Multiprocessing option is installed. To control the amount of resources used by the reorganize operation, you might want to change the query attributes by using the Change Query Attributes (CHGQRYA) CL command or by using the Change Query Attributes function from System i® Navigator.

  If LOCK(*EXCLRD) or LOCK(*SHRUPD) is specified, the result of the reorganize operation is not guaranteed to be exact, because concurrent users might be locking rows or changing rows in the file member.

  If *SHRUPD is chosen, the resulting order of the records may not exactly match what was requested on the KEYFILE keyword. The records will be reorganized to closely match the specified order, but concurrent update, delete, and insert operations will cause some records to not be reorganized. If the records must exactly match the arrival sequence (*NONE) or a keyed file sequence, do not use *SHRUPD. At the end of the reorganize, an attempt is made to remove deleted records at the end of the file and return storage to the system. If *SHRUPD is specified, it is possible that a concurrent insert that occurs just prior to removing the deleted records may prevent the removal of some or all of the deleted records at the end of the file. In this case, the FROMRCD keyword can be used on a subsequent reorganize to attempt the removal of the deleted records without reorganizing the entire file again.

  The FROMRCD specifies which records in the file will be reorganized. Only records from the specified record to the end of the file will be reorganized.

The type of reorganize operation that you decide to use depends on several factors. For example, is your goal to recover space, or is the sequence of the rows important? Is it important that the reorganize operation can be canceled (suspended)? Is it important to allow concurrent access to the physical file member? Use the following table to determine which option is most appropriate based on these factors. The shaded entries (which are also identified by an asterisk) are the characteristics of a key file option that make its choice particularly desirable.