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. From the Statistics Options page, you can select options for running statistics.
   
   
7. From the Table Statistics page, you can define rows to run the statistics.
   
   
8. From the Index Statistics page, you can define indees to run the statistics.
   
   
9. Click the OK push button to reorganize the object.

Related information

• Creating a table space.

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. This is required for LOB table spaces.
   
   
2. Specify the REUSE check box to load data into a non-empty data set.
   
   
3. Select the Update statistics check box to enable the statistics options pages for tables and indexes.
   
   
4. 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.
   
   
5. 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. Specify whether to type values for OFFPOSLIMIT and INDREFLIMIT and whether to generate a report only, without actually reorganizing.
      
      
6. In the OFFPOSLIMIT field, type a value that helps determine if you should REORG your table space. The default value is 10. This option is not valid for LOB table spaces. The value you type is compared against the result of the calculation (NEAROFFPOSF + FAROFFPOSF) × 100 / CARDF for the specified partition(s) in catalog table SYSIBM.SYSINDEXPART for the explicit clustering index(es) for every table in the table space specified. If the calculated value exceeds the OFFPOSLIMIT value that you have typed, REORG is performed or recommended.
   
   
7. In the INDREFLIMIT field, type a value that helps determine if you should REORG your table space. The default value is 10. This option is not valid for LOB table spaces. The specified value is compared against the result of the calculation (NEARINDREF + FARINDREF) × 100 / CARDF for the specified partition(s) in catalog table SYSIBM.SYSTABLEPART for the table space specified. If the calculated value exceeds the OFFPOSLIMIT value that you have typed, REORG is performed or recommended.
   
   
8. 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), reloading into a shadow copy of that area (while applications can read but cannot write to the original copy) switching applications' future access from the original copy to the shadow copy by exchanging the names of the data sets, and then allowing 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), reloading into a shadow copy of that area (while applications have read/write access to the orginal copy of the area), applying the log of the original copy to the shadow copy (while applications can read and usually write to the original copy), switching applications' future access from the original copy to the shadow copy by exchanging the names of the data sets, and then allowing 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), reloading into that area (while applications have no access), and then allowing read/write access again. This is required for LOB table spaces.
      
      
9. 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.
      
      
   4. Select the EXTERNAL radio button unload the data and then end the utility job and remove the status in catalog table SYSIBM.SYSUTIL corresponding to this utility ID. Data rows are decompressed, edit routines are decoded, field procedures are decoded, and SMALLINT, INTEGER, FLOAT, DECIMAL, DATE, TIME, and TIMESTAMP columns are converted to external format. Validation procedures are not invoked.

[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. Optional: You can specify a punch data set name to receive the LOAD utility control statements generated by REORG TABLESPACE UNLOAD EXTERNAL or REORG TABLESPACE DISCARD FROM TABLE. The default is SYSPUNCH. PUNCHDDN is required if the last partition of a partitioned table space has had its limit key reduced.
6. Optional: You can specify a discard data set name to hold copies of records that meet the specifications defined on the Discard page. If you omit this data set, the utility application program saves discarded records only if there is a SYSDISC DD statement in your JCL input. The default is SYSDISC.
7. 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.
8. 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.
9. 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 ]

Statistics Options Page

1. Specify the Report statistics radio button to determine if a set of messages is generated to report the collected statistics. The default, NO, means that output is not sent to SYSPRINT. Yes means that output is sent to SYSPRINT.
2. Specify whether the collected statistics are inserted into the catalog tables. Select the Access path and space statistics radio button update all collected statistics in the catalog. Select the Access path statistics only radio button to update only the catalog table columns that provide statistics used for access path selection. Select the Space statistics only radio button to update only the catalog table columns that provide statistics to help the database administrator assess the status of a particular table space or index. You can also choose to update no catalog table columns. This is only valid when you have selected YES on the Report statistics radio button.

[Return to main task ]

Table Statistics Page

1. Select the Collect statistics on tables check box to specify a table or tables for which column information is to be gathered. All tables must belong to the selected table space.
2. Specify the All radio button to gather statistics for all columns of all tables in the table space.
3. Specify the Individual radio button to gather statistics for all columns of specified tables in the table space.
4. Click the Add push button to open the Add table options window.
5. Click the Change push button to open the Change table options window.
6. Click the Remove push button to delete a selected table from the page.

[Return to main task ]

Index Statistics Page

1. Select the Collect statistics on indexes check box to specify an index or indexes for which column information is to be gathered. All the indexes must be associated with the selected table space.
2. Specify the All radio button to gather statistics for all indexes defined on tables contained in the table space.
3. Specify the Individual radio button to gather statistics for specified indexes in the table space.
4. Click the Add push button to open the Add index options window.
5. Click the Change push button to open the Change index options window.
6. Click the Remove push button to delete a selected index from the page.

[Return to main task ]