Keys in ISAM files

When you create an ISAM file, you must define at least one key (the primary key) by which to access that file. You can define up to 255 keys: 1 primary key and 254 alternate keys.

A defined key can have the following attributes:

• Named key of reference
• Key type/segment types
• Duplicate
• Modifiable
• Segmented
• Null value
• Ascending or descending order per key or per segment
• Specified density

The maximum overall length of a key may not exceed 254 bytes on Windows and Unix (251 if the key allows duplicates or 250 if the key allows duplicates and this is a terabyte file), or 255 bytes on OpenVMS.

Named key of reference

When defining a key, you can specify an optional identifying string to be used in key-of-reference specifications for Synergy ISAM file access.

Key type

Each key in an ISAM file may be made up of one or more segments of the following key types:

• ALPHA (default)

Alphanumeric key. The standard ASCII character set is valid for each character of the key (although the entire binary 0 to 255 range is allowed).

• NOCASE

Case-insensitive alphanumeric key.

• DECIMAL

Zoned decimal key (Synergy DBL decimal data type). The valid range of values allowed for a decimal key is the maximum negative value to the maximum positive value for the size of the defined key. Implied-decimal values may also be used; however, the number of digits to the right of the decimal point must be maintained by the application.

d1 = -9 to 9
d2 = -99 to 99
etc.

• INTEGER

Native integer key (i1, i2, i4, or i8). The valid range of values allowed for an integer key is the maximum negative value to the maximum positive value for the size of the defined key.

i1 = -128 to 127
i2 = -32768 to 32767
etc.

• UNSIGNED

Native unsigned integer key (with the same restraints as integer). The valid range of values allowed for an unsigned integer key is 0 to the maximum positive value for the size of the defined key.

%unsigned(i1) = 0 to 255
%unsigned(i2) = 0 to 65535
etc.

• SEQUENCE

An i8 key that creates an automatically incrementing number between 1 and 9,223,372,036,854,775,807 that is guaranteed to generate a unique key within the file. The key’s value is generated on the initial STORE and will remain for the life of the record until deleted.

• TIMESTAMP

An i8 key that represents the current UTC time in microseconds. It maintains a timestamp of the last time modified. A timestamp key is set to the current time (or that of the server) on every STORE or WRITE (or DELETE when change tracking is enabled). To programmatically convert the i8 time to the local DATETIME, use the %DATETIME_FROM_I8 routine.

• CTIMESTAMP

An i8 key that represents the create timestamp time in microseconds. Unlike TIMESTAMP, which changes on every record update, the CTIMESTAMP value is only set on the initial STORE of a record.

Numeric keys or key segments may not overlap or be overlapped by any other key segment (alpha or numeric). However, you may specify the same numeric key segment in more than one key.

On Windows and Unix, using integer keys (INTEGER, UNSIGNED, SEQUENCE, TIMESTAMP, and CTIMESTAMP key types) in an ISAM file requires special handling when unloading and loading is performed. Due to the chance that one of these keys contains data that could be interpreted as a record terminator, unloading to a sequential (or text) file is not recommended. Instead, we recommend using the fconvert utility to generate a counted file (-oc). (See fconvert for more information and further restrictions on text files.)

Synergy DBMS automatically fills autokeys (SEQUENCE, TIMESTAMP, and CTIMESTAMP) with the appropriate values. A sequence key starts at 1 and is incremented in sequence for every record stored until the file is cleared with ISCLR. A sequence key cannot be descending. A record with autokeys passed to a STORE or WRITE statement will be updated to reflect the autokey values that were stored or written, unless a literal was passed. This allows you to retrieve the autokey values from the record without having to reread it. An autokey must be declared the appropriate size and cannot be modified, does not allow duplicates, cannot be a null key, and cannot be segmented or be a segment in a key with multiple segments. By default, autokey values are preserved (with the exception of a timestamp key where the incoming key data amounts to an empty key; in this case, all zeros, blanks, or nulls will be generated to the current timestamp).

A file that contains a timestamp key cannot be opened in update mode across a network share or NFS drive unless it’s opened exclusively (SHARE:Q_EXCL_RW or SHARE:Q_EXCL_RO). If you try to open a file with a timestamp key in update mode on a mapped drive, you’ll get a “Network share is not allowed with this file” error (NONETSHR). This restriction exists to prevent damaging the integrity of the timestamp key. Use xfServer in this situation. Note that utilities that gain exclusive access (e.g., fconvert and isutl) will allow this kind of access.

If both TIMESTAMP and CTIMESTAMP are in the same record, you can compare their values to see if the record’s original contents have been updated.

Duplicate keys

A duplicate key is a key value found in more than one record of an ISAM file. If you don’t allow duplicate key values in an index, each record in the file is uniquely identified by its key value. With duplicate keys, for example, we can define our zip code field as a duplicate key so that many different records can contain the same value for the zip code. However, if we also define a key for our customer number field, we probably don’t want to allow duplicate keys, so that there will only be one record in the index for each customer number.

When duplicate keys are allowed, you must also specify the order in which a set of duplicate key values are stored within an index and retrieved from a file. Since duplicate keys are internally unique, they are stored sequentially based on the order defined for duplicates. You can either insert duplicate keys at the end of a list of records possessing the same key value or insert duplicates at the front of such records. If you insert duplicate keys at the end, records are retrieved in the same order that they were stored in the file: “first in, first out” (FIFO) order. If you insert duplicates at the front, the first records retrieved are those stored most recently in the file: “last in, first out” (LIFO) order. The default is to insert at the end (FIFO), which is the same as on OpenVMS, where duplicate records are always inserted at the end.

For example, we can define our customer city field as a duplicate key with duplicates inserted at the front of a list of matching records. If our customer ISAM file contains five customers from Baltimore and we accessed that file by the customer city key, we’d retrieve the most recently stored customers first, as shown below:

STORE order	READS order
B. Jones	L. Peterson
C. Smith	R. Carey
A. Johnson	A. Johnson
R. Carey	C. Smith
L. Peterson	B. Jones

On Windows and Unix, allowing duplicates adds 3 bytes (4 bytes for terabyte files) to the internal size of the key, which cannot exceed a total of 254 bytes.

Modifiable keys

If a key is modifiable, Synergy ISAM allows your application to update an existing record and change the value of the defined key using the WRITE statement.

The primary key cannot be a modifiable key.

For example, if we define our customer telephone field as a modifiable key, we can change the value of this key using the WRITE statement if a customer’s phone number changes. However, we probably don’t want to define our customer number field as a modifiable key, since this value should not change during the life of the file. To change a nonmodifiable key, you must use the DELETE and STORE statements.

Segmented keys

Keys can consist of up to eight segments. The total length of the key (up to 254 characters on Windows and Unix [251 if the key allows duplicates or 250 if the key allows duplicates and this is a terabyte file], or 255 characters on OpenVMS) is equal to the sum of the lengths of the key segments. Key segments usually correspond to fields in a record, but they do not have to be in any particular order. Segments can be defined as different types and ordered ascending or descending.

On OpenVMS, due to an RMS limitation, multiple segments of a key must all have the same order.

For example, we can define a customer address key with four segments. The first segment can be 25 characters long and correspond to our street address field; the second can be 15 characters long and correspond to our city field; the third can be 2 characters long and correspond to our state field; and the fourth can be 5 characters long and correspond to our zip code field. The total length of this key is 47 characters long.

Different alpha keys and key segments can overlap each other in a record. Numeric keys and key segments cannot overlap any other key segments unless the segment types, starting positions, and lengths are equal.

To access a segmented key, you must first construct that key by concatenating each segment together. You can use the %KEYVAL intrinsic function to return the extracted key value from the specified record.

On Windows and Unix, partial key specifications on segmented keys are allowed when system option #45 is set.

Null keys

You can specify a null value for any key except the primary key. No entry is made in an index that is defined to have a null value if the inserted record contains the null value for that key. Therefore, when accessing a file by a null key, Synergy ISAM skips over records that contain the specified null value.

Null keys can be useful in a record that contains an optional key field. When the field is blank or contains a value of 0 (depending on the field’s data type), the field doesn’t occupy space in the index. Thus, the use of null keys reduces the size of the index file as well as the overhead time required to insert, delete, or modify a record with a null value. An index allowing null keys can only be used for limited optimization with our ODBC drivers.

You can specify one of three different types of null keys:

• Replicating
• Nonreplicating (Windows and Unix only)
• Short (Windows and Unix only)

A replicating null key’s value must be either a decimal character or its corresponding ASCII character. This type of null key generates a null entry if every byte of that key matches the specified null value.

The following table shows some possible null values in decimal and ASCII form:

	Null values for alpha keys
	Zero	Space	Null
Decimal	48	32	0
ASCII	“0”	“ ”	“\0”

The null value for a numeric key defined as a replicating null key is always binary zero for unsigned and integer keys and decimal zero for decimal keys. A specified null_value for this key type is ignored. When defining a replicating null key on a key that has multiple segments of different types, the null_value only refers to the alpha segment (if any). If there are no alpha segments and null_value is specified, null_value is ignored.

A nonreplicating null key’s value is a string (quotes are optional). This type of null key generates a null entry if the key matches the string for the length of the string starting at the beginning of the key. Nonreplicating null keys can be defined for either alpha or numeric keys; however, the allowable value depends on the type:

• If the key is alpha, an alpha string is specified for the null key value. If that key is segmented, the length of the alpha string must not cause the value to overlap a numeric segment.
• If the key is numeric, a numeric value is specified for the null key. The key may not be segmented. The allowable numeric values depend on the type and length of the key.

A short null key does not have a specified null value. This type of null key generates a null entry if the record doesn’t include the entire key on a STORE or WRITE operation. Short null keys can only be defined for ISAM files that are not fixed-length.

Ascending or descending keys

By default, Synergy ISAM sequentially retrieves keys in ascending order (lowest to highest). When creating an ISAM file, however, you can specify that you want a particular key or segment retrieved in descending order (highest to lowest).

Key density

You can define a specific density for an individual key or keys, while leaving the rest of the keys at the default file density. See ISAM index density for more information about density.