KEY

Describe a key definition

KEY name type [ORDER order] [DUPS dups] [INSERT location] [MODIFIABLE modifiable]  
[NONULL|NULL REPLICATING|NULL NONREPLICATING|NULL SHORT [VALUE value]] 
[DENSITY percentage] [NODENSITY] [COMPRESS [INDEX] [RECORD] [KEY]] [NOCOMPRESS] 
[ODBC VIEW] [ODBC NOVIEW] [KRF krf][DESCRIPTION "description"] 
SEGMENT segtype data [SEGMENT segtype data] [...]

Arguments

name

The name of a new or existing key. This name can have a maximum of 30 characters.

type

The key type. Valid values are ACCESS and FOREIGN. Access keys represent true keys in the data file and are used to specify relationships between files. Foreign keys are also used to specify relationships between files, but they don’t have to be true keys in the data file.

ORDER order

(optional) Indicates the data order, which specifies how the key data for an access key is stored. Valid values are ASCENDING (default) and DESCENDING. This value is ignored if it is specified for a foreign key. If the current structure’s file type is relative, the order must be ASCENDING.

DUPS dups

(optional) Specifies whether an access key allows duplicates. Valid values are YES and NO. The default is YES for all keys except the primary key (the first key defined). This value is ignored if it is specified for a foreign key. If the current structure’s file type is relative, the DUPS value must be NO.

INSERT location

(optional) Indicates where records with duplicate keys are inserted relative to other records containing the same key value. Used for access keys only. Valid values are FRONT (default) and END. This value is ignored if specified for a foreign key.

Note

The following six keywords are used only for access keys other than the primary key (the first one defined). They are ignored if specified for a foreign key.

MODIFIABLE modifiable

(optional) Specifies whether an access key is modifiable. Valid values for the modifiable argument are YES and NO (default).

NONULL

(optional) Specifies that the key is not a null key. The default null key value is NONULL. This keyword is used to modify an existing null key.

NULL REPLICATING

(optional) Specifies that the key is a replicating null key. The null key value is a single character and must match each byte of the specified key.

NULL NONREPLICATING

(optional) Specifies that the key is a non‑replicating null key. The null key value is a string that must match the key, from the beginning of the key and for the length of the key.

NULL SHORT

(optional) Specifies that the key is a short null key.

VALUE value

(optional) Specifies the replicating or non‑replicating null key value. For a replicating null key, value is a single character. For a non‑replicating null key, value is a string. The default null value is a space.

DENSITY percentage

(optional) Indicates the key density value. Used for access keys only and specifies a number between 50 and 100 representing the density percentage for this key. If specified, percentage overrides the density of the file to which this key belongs. The default key density is unspecified.

NODENSITY

(optional) Indicates that no file density override is specified, and that the key density used will be that of the file to which the key belongs. This keyword is used to modify an existing key.

COMPRESS

(optional) Indicates that up to three key compression options follow. These options are used only by RMS indexed files. The compression options are treated as a set. Therefore, to overlay one or more of them, you must respecify all of them. All compression options must be specified on the same line together with COMPRESS. No compression options are set by default.

INDEX

(optional) Specifies that the key’s index is compressed. Used for access keys only.

RECORD

(optional) Specifies that the record within the data is compressed. Used for access keys only.

KEY

(optional) Specifies that the key within the data is compressed. Used for access keys only.

NOCOMPRESS

(optional) Indicates that no index, record, or key compression is specified. This keyword is used to modify an existing key.

ODBC VIEW

(optional) Indicates that the key will be available to xfODBC as a described index in the system catalog. ODBC VIEW is the default.

ODBC NOVIEW

(optional) Indicates that the key will not be available to xfODBC as a described index in the system catalog.

KRF krf

(optional) Indicates the key of reference, which is used by ReportWriter and xfODBC to access the file. The key of reference is 0‑based. For example, by default, the third key defined in your file is key of reference 2. The KRF keyword is used to explicitly specify a key of reference that differs from the default. If you specify a key of reference for one key in the structure, you should do so for all keys in the structure so as to avoid duplicates.

You will need to specify a key of reference if you use RMS indexed files (which allow more than 99 keys) and want to access your file with a key other than one of the first 99.

DESCRIPTION “description”

(optional) A description of the key definition. It can have a maximum of 40 characters and must be enclosed in double or single quotation marks ( “ ” or ‘ ’ ). This description is available when Repository displays a list of keys.

SEGMENT segtype data

Indicates the segment type and data for one segment of the key. Segtype and data must be specified on the same line as SEGMENT. A key must contain at least one segment definition and may contain a maximum of eight. The data value helps define each segment of the key. If a specified field or structure has not yet been defined, the segment is ignored. Valid values for segtype are FIELD, LITERAL, EXTERNAL, and RECORD NUMBER. See below for details.

field_name [SEGTYPE segdtype] [SEGORDER segorder] [NOSEGORDER]

Where field_name is the name of a field in the current structure. The maximum size is 30 characters.

SEGTYPE segdtype (optional) indicates the key segment data type. It is used for access keys only and specifies the data type for this specific segment, which overrides the default data type of the key. The default segment data type override is unspecified.

Valid values for segdtype are

ALPHA
NOCASE
DECIMAL
INTEGER
UNSIGNED
SEQUENCE
TIMESTAMP
CTIMESTAMP

SEGORDER segorder (optional) specifies an override to the key’s sort order. It is used for access keys only. The default key segment order override is unspecified. Valid values for segorder are ASCENDING and DESCENDING.

NOSEGORDER (optional) is used for access keys only and specifies that the key sort order (rather than key segment sort order) is used for the key. This keyword is used to modify an existing key.

Discussion

The KEY statement is used to describe a key definition. This key is associated with the most recently defined structure. If no structure has been defined yet, the key is ignored.

Adding new definitions

The first key defined for a structure is assumed to be the primary key. Access keys must be defined first, followed by any foreign keys. The order of your access keys determines the key of reference used by ReportWriter and xfODBC. You can define a maximum of 99 keys in one structure.

Replacing existing definitions

All required keywords and data must be specified. The existing key is deleted and replaced by the specified one. (Note that when you replace a structure, any keys that are not explicitly specified in the schema will be deleted.)

Overlaying existing definitions

Name and type must be respecified because type is position‑dependent. The current key attributes are overwritten with any new attributes that are specified. Keep in mind that because the key segments are not numbered, to overlay one segment, you must respecify all of them.

Note

For structures whose file type is relative, only one access key can be defined, and it must be ascending, allow no duplicates, and have one segment of type record number. Any additional access keys are ignored.

Examples

KEY cckey access ORDER ascending DUPS no
SEGMENT field cccomp SEGMENT field ccclnt
KEY prim_cont foreign SEGMENT field cccomp 
DESCRIPTION "Primary contact key"
SEGMENT literal "01" SEGMENT external cccont cnname