Limited supportBMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see REORG PLUS for DB2 13.1.

ORDER

The ORDER option tells REORG PLUS how and whether to order the table rows.

ORDER.png

For performance information about this option, see Memory requirements of the ORDER command option.

YES

When ORDER YES (the default) is in effect, REORG PLUS orders the rows in the tables of the table space by their clustering key or, if there is no clustering key, by partitioning key. REORG PLUS orders the data in segmented table spaces, even if no clustering index exists for the table space.

Important

To simplify terminology, this book refers to an index that is used to sort data as a data-sorting index. For traditional table spaces, this is a clustering index. For table-controlled partitioned table spaces, this is either a clustering index or partitioning index.

Restrictions

REORG PLUS ignores ORDER YES or changes it to ORDER NO when any of the following conditions exists:

  • You are reorganizing a LOB table space and SHRLEVEL REFERENCE is in effect.
  • All of the following conditions exist:
    • You are reorganizing a table space that has only one table.
    • The table space is not partitioned or is partition-by-growth.
    • No clustering index exists.
    • You are performing a two-phase reorganization (UNLOAD CONTINUE).

Option

Description

ASSOCIATE

This option does not apply to an index-only reorganization.

Use the ASSOCIATE option to specify how you want REORG PLUS to order rows for multi-table table spaces.

Value

Description

BYTABLE

BYTABLE, which is the default, tells REORG PLUS to sort the rows by table as well as by each table’s clustering key. If no clustering key exists, REORG PLUS uses X'00's. REORG PLUS always sorts segmented table spaces by table.

Restrictions

Following are the restrictions for ASSOCIATE BYTABLE:

  • For a two-phase reorganization, this option is meaningless for a single-table table space with no clustering index. In this case, REORG PLUS functions as if you specified ORDER NO.
  • When invoking DSNUTILB, REORG PLUS ignores this option.

BYCLUSTERKEY

BYCLUSTERKEY sorts the rows by each table’s clustering key only. This option is useful for multi-table, simple table spaces to group rows of different tables together by a common clustering key.

When invoking DSNUTILB, REORG PLUS passes this option as SORTDATA YES to the IBM DB2 REORG utility for processing.

Restriction

REORG PLUS fails if you specify BYCLUSTERKEY for a multi-table, segmented table space.

NO

For table space reorganizations, if you specify ORDER NO, REORG PLUS performs no ordering, and the data rows retain the order of the table before reorganization. REORG PLUS processes clustering indexes together with nonclustering indexes.

For index reorganizations, ORDER NO performs no ordering of the key/RID pairs.

When invoking DSNUTILB, REORG PLUS passes this option as SORTDATA NO to the IBM Db2 REORG utility for processing.

Restrictions

ORDER NO has the following restrictions:

  • REORG PLUS changes ORDER NO to ORDER YES when you are rebalancing partitions.
  • REORG PLUS terminates when you specify ORDER NO under any of the following conditions:
    • You are reorganizing a multi-table, segmented table space.
    • For a single-phase reorganization, you are performing a SHRLEVEL NONE reorganization.
    • For an index-only reorganization, you are performing a SHRLEVEL CHANGE reorganization.

Related topic

Basic-REORG-PLUS-processing-options

 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*