Default language.

Space announcement This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

ORDER

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

Related topic

Basic REORG PLUS processing options

 

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 a partitioning index.

Restrictions

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

  • 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 the 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 the table.

Restrictions

Following are the restrictions for ASSOCIATE BYTABLE:

  • For a two-phase reorganization, this option is meaningless for 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 the reorganization. REORG PLUS processes clustering indexes together with non clustering 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.

 

 

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