The key clause identifies the key or keys of the set.
All key items must be data items or group items in the data set referenced by the set. When multiple key items appear, the key items are listed from left to right in order of significance. The leftmost key item is the major key.
When group key items contain signed numeric or real items, the set cannot necessarily be ordered ascending by key. Normalization or transformation is not performed on signed numeric and real items that are within group items because group items are aligned on an 8-bit byte boundary. To ensure correct ordering for sets, change the group key to a multiple key item.
When group keys contain alpha items or numeric items, the set can contain some extra padded digits if the numeric items are not byte-aligned. To ensure no extra padded digits are in the set, use multiple key items rather than a group item key, or make all numeric items within the group byte-aligned.
The following restrictions apply to key items:
-
Signed numeric items that are used as keys must have a precision of 22 or less; unsigned numeric items must have a precision of 23 or less. The scale factor must not exceed the precision or 11.
-
Items that use the SIZE or STORED options cannot be used as keys.
-
Key items within variable-format records must be contained in the fixed part of the record.
The ASCENDING and DESCENDING options can be specified only for index sequential and ordered list sets. When the ASCENDING option is used, records with low key values precede records with high key values. When the DESCENDING option is used, records with high key values precede records with low key values. If neither the ASCENDING nor DESCENDING option is specified, an ascending order is assumed.
| Note: | For descending keys, comparison operations are achieved by using the complement of the value to be compared. Internally, the process of complementing requires that a field length be specified, and the length used is that of the data item being compared. Therefore, if the length of the comparison value is greater than the declared data item size, high-order truncation occurs. For example, if DATE is declared to be NUMBER (6) and the application performs a statement similar to FIND X WHERE DATE = 20021127, then truncation occurs and the statement does not function as expected. |
When a key item has the DATAENCRYPT option set, that key will be hashed in a 32 byte hash value. As the result, the size of the table (TABLESIZE or BLOCKSIZE) need to be extended. The same key value will have the same hashed value as long as they are in the same set or subset. Keys in different sets or subsets will have different hashed values even if they contain the same data values.
The following restrictions apply to key items specified with the DATAENCRYPT option:
-
Only FIND equal is supported.
Note: Using an LSS, LEQ, or NEQ operator on a FIND operation returns a syntax error. Using a GEQ and GTR operator on a FIND operation returns unpredictable results. -
Linear search is not supported if
-
a user selection expression contains an encrypted key item and a non-encrypted signed numeric or real key item.
-
a user selection expression contains an encrypted REAL type key item.
On an encrypted database, applications using an LSS, LEQ, NEQ, GEQ, or GTR operator on a FIND operation return a syntax error at compile time. On a database that is not encrypted, using an LSS, LEQ, NEQ, GEQ, or GTR operator on a FIND operation returns the warning message, “THIS SELECTION EXPRESSION CAUSES A LINEAR SEARCH.”
-
-
An encrypted group cannot be used as a key of a set or subset. When you use a group item as one of the keys in the set or subset, declare the individual items of the group as the keys rather than declaring the group itself.
-
The ASCENDING and DESCENDING options cannot be specified for an index sequential set or subset containing encrypted key items. This is because the ordering of the set or subset is based on the order of the hashed key values and not on the order of the unencrypted (clear text) key values.
-
An Access of a random, direct or ordered data set cannot contain encrypted key items.
-
An item specified with the DATAENCRYPT option cannot be used as key data.
-
An encrypted item cannot be used as a key item for embedded sets.
-
Online reorganization and online garbage collection are not allowed if a set or subset contains any encrypted keys.
-
A reorganization of a set or subset with encrypted keys must be generated from its data set. Reorganization cannot generate a manual subset with encrypted keys.
-
Index Random set cannot contain encrypted key items.
-
An item specified with the DATAENCRYPT option cannot be used as a part of a SELECT, VERIFY, or WHERE clause.
