Using reorganize in a utility procedure

In the Add Utility Procedure Element window select Reorganize to specify that reorganize utility should be run on the object list when the utility procedure is run.

An object list is a collection of predefined objects such as indexes or table spaces that can be referred to in order to run a utility on all of the objects in the object list using only a single command.

Authorities and privileges

To specify Reorganize to be run on an object list through a utility procedure :

  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 templates and sizes, that are used with the reorganization utility.

  4. From the Statistics Options page, you can select options for running statistics.

  5. Select the OK push button to add the element to the utility procedure.

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

    .
    1. 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.

    2. 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.

    3. 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

The Data Sets page contains the data set template information, or data definition statements, that will be used by the REORG utility. Some of these data sets templates are required depending on the options specified on options page of the Reorganize Table Space on Object List notebook.
  1. Specify the unload data set template. The created data set will contain 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:
  5. Optional: You can specify a punch data set template 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 template.

  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 ]

Statistics Options Page

The Statistics options page contains information on gathering statistics stored in the DB2 catalog.
  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 ]


Related information: