ORDER

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

Related topic

Basic LOADPLUS processing options

ORDER.png

ORDER affects only new rows that you are loading. For a LOAD RESUME YES job, LOADPLUS does not mix new rows with existing rows.

You can always omit the ORDER option from your LOAD command without error. If you do not specify ORDER, the default is ORDER NO except when you are loading a multi-table, segmented table space. In this case, ORDER YES is the default.

Restrictions

The following restrictions apply to the ORDER option:

  • When invoking DSNUTILB, LOADPLUS ignores this option.
  • You cannot change the value of the ORDER option when you restart your load job.

NO

If you specify ORDER NO, LOADPLUS performs no ordering at all, and the rows retain the order of the input data set from which they were read. The table space might require reorganization for adequate Db2 performance. LOADPLUS sorts all indexes independently of the data.

ORDER NO is the default except when you are loading a multi-table, segmented table space. (In this case, ORDER YES is the default.)

Restrictions

In addition to the restrictions for the ORDER option, the following restrictions apply specifically to ORDER NO:

  • Do not specify this option if you are loading multiple tables in a segmented table space unless you are performing an SQLAPPLY load.

    You can specify this option if you are loading one table of a multi-table, segmented table space.

  • For optimal performance, we recommend that you do not specify this option if a large number of partitions are participating in the load job.

PRESORTED

If you specify ORDER PRESORTED, LOADPLUS verifies that the rows are in the correct clustering index order. If the data is partitioned and the clustering index is either not partitioned or is a data-partitioned secondary index (DPSI), the data must be sorted first by partition, then by clustering key.

LOADPLUS performs no sorts on either the data or the index and the job terminates if the data is not in the correct order.

The encoding scheme of the table determines the collating sequence for character data. For example, if the table is defined as EBCDIC, the collating sequence will be EBCDIC.

ORDER PRESORTED is valid when loading a single table with one of the following specifications:

  • LOAD REPLACE
  • LOAD RESUME NO
  • LOAD RESUME YES INTO TABLE tableName REPLACE
  • Single-phase LOAD RESUME YES PART n REPLACE if you are replacing all partitions that are participating in the load and no nonpartitioned indexes are participating in the load

Restrictions

In addition to the restrictions for the ORDER option, the following restrictions apply specifically to ORDER PRESORTED:

  • If you use this option with types of load jobs other than the ones described as valid for ORDER PRESORTED, LOADPLUS issues a message BMC50115E or BMC51430E and terminates.
  • You must specify only one SYSREC data set when using ORDER PRESORTED. Otherwise, the job terminates.

YES

If you specify ORDER YES, LOADPLUS orders the rows in the tables of the table space by their clustering key or, if there is no clustering key, by partitioning key.

Important

To simplify terminology, this space 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.

The encoding scheme of the table determines the collating sequence for character data. For example, if the table is defined as EBCDIC, the collating sequence will be EBCDIC.

Restriction

In addition to the restrictions for the ORDER option, if you specify ORDER YES and also specify SKIPIX to skip a clustering index, LOADPLUS terminates with the message BMC51527E.

ASSOCIATE

Use the ASSOCIATE option to specify how you want LOADPLUS to sort the rows for multi-table table spaces. The following table describes the values that you can specify for the ASSOCIATE option:

Value

Description

Restrictions

BYTABLE

BYTABLE, which is the default if you specify ORDER YES, tells LOADPLUS to sort the rows by the table as well as by each table’s clustering key. If no clustering key exists, LOADPLUS uses X'00's. LOADPLUS always sorts segmented table spaces by the table.

This option is meaningless for single-table table space with no clustering index. In this case, LOADPLUS processes this option as if you specified ORDER NO.

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.

  • Do not specify this option if there is no clustering index.
  • LOADPLUS ignores this option if you are loading a segmented or partitioned table space.
  • LOADPLUS changes this option to ASSOCIATE BYTABLE for an SQLAPPLY load when referential integrity exists between the objects that you are loading.

 

 

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