IBM SC41-5210-04 manual Collating sequence and CCSIDs in Query for iSeries

To collate means to place items in proper sequence or to check that items are in proper sequence. For Query for iSeries purposes, collating sequences apply to SBCS character data in SBCS, DBCS-open, or DBCS-either fields, not numeric, date, time, timestamp, DBCS-only, or DBCS-graphic fields. The collating sequences you can use are:

vThe collating sequence provided by Query for iSeries for the language of your country.

vA collating sequence that you define.

vA translation table, created by the CRTTBL (Create Table) command, that exists in one of your libraries.

vOne of the sort sequences supplied with the system. For each supported language, the system supplies one table with unique weights for all characters and a second table with shared weights for some characters.

Unless you select a collating sequence, the standard sequence used for collating is the same as the numeric sequence of the hexadecimal values used to represent the characters.

Collating sequence and CCSIDs in Query for iSeries

You can define a collating sequence by assigning a sequence number to each character in a displayed list. After renumbering in increments of 10 starting from 64 (’40’X), the assigned number for each character is saved in the one-byte table entry at the offset that corresponds to the numeric value of the hexadecimal representation of the character. The hexadecimal representation can be used later to retrieve the collating weight for character data comparisons.

A CCSID is saved with a collating sequence so that the collating sequence can be converted for use with data in a different code page. Conversion of a collating sequence is a matter of rearranging the numbers in the table so that the appropriate collating weight is found for each character.

How a collating sequence affects Query for iSeries

Several definition steps in Query for iSeries use the selected collating sequence to determine the final results when your query is run. A collating sequence is used:

vWhen you join files together by comparing an SBCS, DBCS-open, or DBCS-either character field in one file to a character field in the other file by using the following tests:

–EQ (equal)

–NE (not equal)

–GT (greater than)

–LT (less than)

–GE (greater than or equal)

–LE (less than or equal)

vWhen you use comparison tests like EQ, NE, GT, LT, GE, LE, LIKE, NLIKE, LIST, NLIST, and RANGE to select records based on SBCS, DBCS-open, or DBCS-either character field values

Note: Query for iSeries does not use the collating sequence for EQ, NE, LIST, NLIST, LIKE, and NLIKE comparisons, or apply it to SBCS characters in DBCS fields and constants, when the Use collating sequence for all character comparisons option is set to NO (the default for a query from a release earlier than Version 2 Release 3) on the Specify Processing Options display.

vWhen you select an SBCS, DBCS-open, or DBCS-either character field for sorting records

vWhen you define minimum and maximum summary functions for an SBCS DBCS-open, or DBCS-either character field

vWhen you define report breaks on an SBCS, DBCS-open, or DBCS-either character field

112Query for iSeries Use V5R2