Defining keys

Key definitions are associated with each structure. You can define two types of keys: 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.

The order of your access keys determines the key of reference used by xfODBC and ReportWriter to access the file (unless you define an explicit key of reference). The maximum number of keys that can be defined within one structure is 99.

If you use RMS indexed files and want to access your file with a key other than one of the first 99, you will need to explicitly specify a key of reference in the Key of ref field. 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.

A relative file can have only one access key: the record number. When you create a structure with a file type of relative, Repository automatically creates an access key named RECORD_NUMBER. This key is ascending, allows no duplicates, and has one segment of type R. You can create additional foreign keys or delete the access key that Repository created; however, only one access key can exist for a relative file, and it must have the attributes described above.

Displaying the Key Definition list

To display the Key Definitions list,

In the Structure Definitions list, highlight the structure for which you want to define a key.

Select Structure Functions > Edit Attributes.

Select Attributes > Keys.

The Key Definitions list displays the following information for each key in the selected structure. The total number of keys for this structure is displayed at the bottom of the list. (See figure 1.)

1. The Key Definitions list.

KEY NAME—The unique key name.

TYPE—A for an access key or F for a foreign key.

ORDER—A for ascending or D for descending (for access keys only).

DUPS—Y if duplicates are allowed or N if they are not (for access keys only).

SEGMENTS—A list of segment types in the key.

SIZE—The size of the key.

Reordering keys in the Key Definitions list

Repository assumes that the first key in the list is the primary key. Access keys must all remain at the top of the list, followed by the foreign keys.

Highlight the key you want to move.

Select Key Functions > Reorder Keys. The highlighted key is enclosed in square brackets ([ ]).

Use the Up and Down Arrow keys to move the bracketed key to another location in the list.

Select Reorder Keys again to exit move mode. The key is inserted at the new location.

Defining a new key

You can define a new key from scratch or by copying and modifying an existing one. If key definitions already exist, new definitions are inserted below the highlighted entry.

From the Key Definitions list,

To define a key from scratch, select Key Functions > Add Key.
To define a key by copying, highlight the key you want to copy, and then select Key Functions > Copy Key.

The Key Definition input window is displayed.

Enter or modify data in each field as instructed below.

2. Defining a key.

Key name

Enter a unique key name. The key name is used to specify the key when you define relations. The key name must be unique within the current structure. The key name can have a maximum of 30 characters and must begin with a letter. The remaining characters can be letters, digits, underscores (_), or dollar signs ($).

Description

Enter a more descriptive identifier for the key with a maximum of 40 characters.

Key type

Select the key type: Access (the key is a true key in the data file; the default) or Foreign (the key is not a true key).

If you are defining both access keys and foreign keys, the access keys must be defined first, followed by the foreign keys. The order of the access keys determines the key of reference used by xfODBC and ReportWriter to access the file, unless you define an explicit key of reference. (See the description of Key of ref.)

The following eleven fields apply to access keys only. If you select Foreign in the Key type field, these fields are disabled, and the cursor moves to the first segment definition.

Sort order

Choose the option that defines how the key field data is stored: Asc (ascending; the default) or Desc (descending).

Dups allowed

Set this field to indicate that the key field allows duplicates.

Insert at

If you set Dups allowed, specify Front or End to indicate where records with duplicate keys are inserted relative to other records containing the same key value. This field applies only to access keys.

Modifiable

Set this field to indicate that the key is modifiable. This field applies only to access keys other than the primary key (assumed to be the first key in the list).

Null key

This field applies only to access keys other than the primary key (assumed to be the first key in the list). Select the null key type:

No = Not a null key (default)

Replicating = Replicating null key

Non-replicating = Non-replicating null key

Short = Short null key

Null value

If you selected Replicating or Non-replicating for Null key, you can optionally specify the null key value. If the null key value contains spaces, you must enclose the string in quotation marks. The default null key value is a space.

The Null value can have a maximum length of 255 characters; however, the field allows you to enter only a 20-character string. To enter a longer string, select Edit Key Functions > Edit Entire Text.

To edit a string that exceeds 20 characters, you must also use the Edit Entire Text option. (If you edit it in the Null value field, the portion that is not displayed will be lost. Similarly, deleting the 20 characters that are displayed in the field will delete the entire string.)

Key of ref

By default, the order of the access keys determines the key of reference used by xfODBC and ReportWriter to access the file. For example, the first access key is key of reference 0, the second access key is key of reference 1, and so on. Use the Key of ref field 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.

This field can also be used to specify a key of reference greater than 99 when using RMS indexed files.

Key density

If you want to override the key density (defined at the file level) for this specific key, enter a number between 50 and 100 that represents the density percentage for the key. Density represents the percentage that each index block is filled. If unspecified, the density for this key will be the density specified for the file.

Compress index

Set this option to indicate that the key’s index is compressed. Only RMS indexed files use this option. Compress index applies only to access keys.

Compress record

Set this option to indicate that the record within the data is compressed. Only RMS indexed files use this option. Compress record applies only to access keys.

Compress key

Set this option to indicate that the key within the data is compressed. Only RMS indexed files use this option. Compress key applies only to access keys.

Excluded by ODBC

This value determines whether a key is included by xfODBC in the system catalog. Set this field if you want this key to be excluded from the system catalog, which prevents xfODBC from attempting to use it for optimization when generating a system catalog. Excluded by ODBC is not set by default, which means the key will be used for optimization, as deemed appropriate by xfODBC. For more information on xfODBC optimization, see Optimizing with keys.

Seg type

A key must contain at least one segment definition. A selection window with a list of the valid segment types is displayed for each of the eight Seg type fields at the bottom of the window. Select the segment type you want to use for the first key segment:

F (field) = Defines a field in the current structure as a segment.

L (literal) = Defines a literal as a segment, enabling you to append a constant to the beginning or end of a key or embed a constant within a key.

E (external) = Defines a field in another structure as a segment.

<blank> = Clears the segment type.

Only foreign keys can contain literal or external segment types. See Using literal key segments and Using external key segments for more information.

If you select segment type F (field), enter a field name from the current structure in the Field name or Literal column. Select Edit Key Functions > List Selections to display a list of available fields.
If the field is an arrayed field, Repository uses only the first element of that array. If you want to specify an element other than the first, define an overlay field that overlays the desired element and flag the field as excluded by ReportWriter.
If the field is a group, you cannot select fields within it, but you can define an overlay field to overlay the group members and then use the overlay field as the key.

Type: This field defines whether the data type for this specific key segment overrides the data type for the key. When a key is created, Repository assigns it a default key data type. If all segments have the same data type, that type becomes the key data type. If the segments have mixed data types, the key data type is set to alpha. You can override the key data type for one or more segments by specifying a value in the Type field.

If the field is alpha, its segment data type can be set to N for no case (case-insensitive) alpha.
If the field is integer, its segment data type can be set to S for sequence, T for timestamp, C for create timestamp, or U for unsigned integer.
All fields can be set to A for alpha.
User-defined fields can be set to any type.

Set Type to S, T, or C to define an autokey. Autokeys are keys that are filled in by Synergy DBMS with the appropriate values. They can contain only one segment, consisting of an 8-byte field. Autokeys cannot be null, modifiable, or allow duplicates. See Key type for more information on autokeys.

Order: The default key sort order is defined in the Sort order field. To override the sort order for a specific key segment, select A for ascending or D for descending. The default key segment order is unspecified, which means it defaults to the sort order of the key.

If you select segment type L (literal), enter a literal value in the Field name or Literal column. The maximum size of a literal is 30. If you don’t enter a value, Repository assumes a literal segment consisting of 30 blanks. If you want a literal segment value to have trailing blanks, enclose the literal in double or single quotation marks (“ ” or ‘ ’).
If you select segment type E (external), enter a structure name in the Structure name column and a field name in the Field name or Literal column. The field name must belong to the structure, and the structure cannot be the current one. Select Edit Key Functions > List Selections to display a list of available field or structure names.

If the total size of a key’s segments exceeds 255, the size is set to 255. ReportWriter uses this size when building the key for data file access.

When a key is created, Repository assigns it a data type. ReportWriter uses this key data type when accessing related data in other files. (See the description of the Type field above for more information.) When keys are used in relations, we recommend that they have the same key data type and size, but this is not required.

Exit the window to save the new key definition.

Using literal key segments

Literal key segments enable you to establish a relationship between two files where part of the key data is constant.

Let’s assume the COMPANY_ID field in file B is a combination of a four-digit CLIENT_ID from file A and a two-digit COMPANY_CODE. Let’s also assume that for a particular user, the company code will always be 01 and is therefore not stored in any file. You can create a key for file A that is composed of a literal segment whose value is 01 and a field segment using the CLIENT_ID field. You would then use this key to establish a relationship to the COMPANY_ID field in file B.

You must pad literal key segments with blanks to reach the desired length if you want exact key matches. For example, if your “from” key consists of an a4 field and the literal “ABC,” and your “to” key consists of an a4 field and an a6 field, you must pad the literal with three blanks (“ABC ”) to do an exact match; otherwise, ReportWriter will do a partial key match on seven characters.

Using external key segments

Repository permits a special kind of relationship between files called an external relation. An external relation involves three or more files, where one file is accessed by a key composed of segments from the remaining files. For example, the item type from one file (file A), along with the item number from a second file (file B), can be used to access the item ID in a third file (file C).

The following example describes how to define the external relation defined above. (Assume that structure A is assigned to file A, and so forth.) We’ll assume the following fields are in structures A, B, and C:

Structure A

A_ITMTYP        ,a2     ; Item type
A_TRANNO        ,d5     ; Transaction #

Structure B

B_ITMNO         ,d5     ; Item #
B_TRANNO        ,d5     ; Transaction #

Structure C

C_ITMID         ,a7     ; Item ID

Now you would do the following:

Establish a relationship between file A and file B. This should be a one-to-one relationship. (Each record in file A should correspond to only one record in file B.) To do this, define a key for each structure, and define the relationship in structure A as follows:

Structure B

Define the access key B_TRANKEY using field segment B_TRANNO.

Structure A

Define the access key A_TRANKEY using field segment A_TRANNO.

Define a relation using key A_TRANKEY related to key B_TRANKEY in structure B.

Define a key for structure A that contains the external key segment (the external key segment refers to a field in file B).

Structure A

Define the foreign key A_ITMKEY using field segment A_ITMTYP and external segment B_ITMNO from structure B.

Define a key for structure C.

Structure C

Define the access key C_ITMKEY using field segment C_ITMID.

Define a relation (using the key containing the external segment) from file A to file C.

Structure A

Define a relation using key A_ITMKEY related to key C_ITMKEY in structure C.

See Using external key segments for a discussion of how this relationship appears in ReportWriter.

Modifying a key

From the Key Definitions list, highlight the key you want to modify and press Enter.

In the Definition window, modify data as desired. For detailed information on the fields, see step 2 under Defining a new key. The key name cannot be modified.

Before you modify segment definitions in the lower portion of the input window, you should check whether any relations use this key. Changing segment definitions might change the key data type, which will affect relations that use the key. We recommend that related keys have the same key data type and size, but to give Repository more flexibility, this is not required.

Exit the window to save your changes.

Deleting a key

You can delete a key only when both of the following conditions are true:

The key is not used in a relation defined by the current structure.
The key is not used in a relation defined by another structure.

Highlight the key in the Key Definitions list.

Select Key Functions > Delete Key.

At the prompt, select Yes to delete the key or No to cancel the deletion.