Reorganizing a table space

Authorities and privileges

To reorganize a table space:

1. Open the Table Space Reorganize window.
   
   
2. From the Options page, specify how you want the reorganization utility to handle data before during and after the REORG.
   
   
3. From the Data Sets page, identify specific data set information, data set names and sizes, that is used with the reorganization utility.
   
   
4. From the External Spec page, you can define parameters so that the data is unloaded in a format that is acceptable to the LOAD utility of any DB2 subsystem.
   
   
5. From the Discard page, you can define parameters to delete rows during the reorganization.
   
   
6. Click the OK push button to reorganize the object.
   
   

Related information

• Table spaces

Options Page

1. From the Options page on the Log records during reload check box, specify whether records are logged during the reload phase of the REORG utility. If the records are not logged, the table space is recoverable only after an image copy has been taken. Logging records during the reload phase is not allowed for any table space in DSNDB01 or DSNDB06 or if you have selected SHRLEVEL REFERENCE or CHANGE.
   
   
2. If your table space is partitioned, select the Specify partition check box and then click the up or down arrows to define a partition number or range. You can reorganize a single partition of a partitioned table space, or a range of partitions within a partitioned table space. The integer selected must be in the range from 1 to the number of partitions that are defined for the partitioning index. The maximum is 254.
   
   
3. Specify options for improving performance after unloading the data:
   
   
   1. Specify the SORTDATA option to unload the data using a sequential scan. If you do not specify the SORTDATA option, the REORG utility uses the clustering index to unload the data.
      
      
   2. Specify the NOSYSREC option to omit the unload data set. The output of sorting (if there is a clustering index and SORTDATA is specified) is the input to reloading, without using an unload data set. You can use this option only if you specify SORTDATA, SHRLEVEL REFERENCE, or SHRLEVEL NONE, and only if you do not specify UNLOAD PAUSE or UNLOAD ONLY.
      
      
   3. Specify SORTKEYS to pass index keys to sort in memory rather than to work files. Avoiding this I/O to the work files improves LOAD performance.
      
      
4. From Type of access allowed select:
   
   
   1. The Read-only application access radio button to unload data from the area being reorganized (while applications can read but cannot write to the area). DB2 reloads into a shadow copy of that area (while applications can read but cannot write to the original copy). DB2 switches future access from the original copy to the shadow copy by exchanging names of the data sets. DB2 then allows read/write access again. Click the Options push button to open the Reference Option window.
      
      
   2. The Read-write application access radio button to unload data from the area being reorganized (while applications can read and write to the area). DB2 reloads data into a shadow copy of that area (while applications have read/write access to the orginal copy of the area). DB2 applies the log of the original copy to the shadow copy (while applications can read and usually write to the original copy). Then DB2 switches future access from the original copy to the shadow copy by exchanging data set names. DB2 then allows read/write access again. Click the Options push button to open the Change Option window.
      
      
   3. The No application access radio button to unload data from the area being reorganized (while applications can read but cannot write to the area). DB2 reloads data into that area (while applications have no access). Then DB2 allows read/write access again.
      
      
5. From After unloading the data select:
   
   
   1. Select Continue to include the reload phase of the reorganize utility. Select only if RECOVER TABLESPACE has terminated during reconstruction of a page, because of an error. In this case, the page is marked as broken. After you have repaired the page, you can use Continue option to recover the page, starting from the point of failure in the recovery log.
      
      Select the Keepdictionary check box to compress data using an existing dictionary. If you are satisfied with the compression you are getting with an existing dictionary, you can keep that dictionary. This method saves you the processing overhead of building the dictionary.
      
      
   2. Select Pause to include the reload phase of the reorganize utility. The reorganization pauses after unloading the table space into the unload data set. You cannot use NOSYSREC and PAUSE. You can restart the utility REORG using the phase restart or current restart.
      
      Select the Keepdictionary check box to compress data using an existing dictionary. If you are satisfied with the compression you are getting with an existing dictionary, you can keep that dictionary. This method saves you the processing overhead of building the dictionary.
      
      
   3. Select Only to do the reoganize only without including a reload phase.
      
      

[Return to main task ]

Data Sets Page

1. Specify the unload data set name. This data set contains the data to be unloaded. The default name is SYSREC. The data set must be a sequential data set that is readable by the MVS BSAM access method.
2. Optional: You can specify 2 copy data sets, primary and backup data sets. These data sets contain image copy data sets. The default is SYSCOPY for the primary copy. A full image copy data set is created for the table specified when REORG executes. The table space for which an image copy is produced is not placed in COPY pending status.
3. Optional: You can specify 2 copy data sets at the recovery site, primary and backup data sets. These data sets contain image copy data sets.
4. The temporary work files for sort input and output (SYSUT1 and SORTOUT) data sets are required when:
   • the tables have indexes
   • SORTKEYS on the options page of this notebook is specified with no estimate or an estimate of 0
5. You can specify the device type for temporary data sets to be dynamically allocated by DFSORT. This can be any device type that is acceptable to the DYNALLOC parameter of the SORT or OPTION options for DFSORT.
6. If you select to use DFSORT temporary data sets, then you must type a number of data sets to be dynamically allocated by the sort application program. This is used only if a device type is selected in the previous step.
7. Specify to preformat the remaining pages up to the high allocated RBA in the table space and indexspaces. The preformatting occurs after the data has been loaded and the indexes are built. PREFORMAT can operate on an entire table space and its index spaces, or on a partition of a partitioned table space and its corresponding partitioning index space. This is not valid if you selected the ONLY radio button on the Options page for doing a REORG only.

[Return to main task ]

External Spec Page

1. Click the Add push button to open the window to define a table to be qualified by certain predicates for unloading.
2. Optional: Specify the Do not pad check box to prevent the padding of variable-length columns.
3. Optional: Click the Change push button to modify any previously defined tables.
4. Optional: Click the Remove push button to delete tables from this page.

[Return to main task ]

Discard Spec Page

1. Select the Discard records meeting the specified WHEN condition check box.
2. Click the Add push button to open the Add window to define a table to be qualified by certain predicates for unloading.
3. Optional: Specify the Do not pad check box to prevent the padding of variable-length columns.
4. Optional: Click the Change push button to modify any previously defined tables.
5. Optional: Click the Remove push button to delete tables from this page.

[Return to main task ]