UNIQUEKEY and NONUNIQUEKEY

UNIQUEKEY and NONUNIQUEKEY are DEFINE ALTERNATEINDEX parameters that specify whether the alternate key can have duplicate values across base cluster records. You must specify exactly one when defining an alternate index. UNIQUEKEY means every alternate key value must be unique—no two base records can have the same value in the alternate key field. NONUNIQUEKEY means duplicate alternate key values are allowed; many base records can share the same alternate key (e.g. the same department code or status). Your choice affects how BLDINDEX builds the AIX, how many records can be returned when you read by alternate key, and what happens if base data has duplicate alternate key values when you expected unique. This page explains UNIQUEKEY and NONUNIQUEKEY: what they do, when to use each, and how they differ from primary key uniqueness.

Where They Are Specified

UNIQUEKEY and NONUNIQUEKEY are specified in the IDCAMS DEFINE ALTERNATEINDEX (or DEFINE AIX) command. They do not apply to the base cluster or to the primary key of a KSDS. The primary key of a KSDS is always unique; there is no keyword to change that. Only the alternate index has a choice: the alternate key can be unique (UNIQUEKEY) or non-unique (NONUNIQUEKEY). So when you define an AIX with KEYS(length offset) to identify the alternate key field in the base record, you must also say whether that field is unique or can repeat.

Syntax

In DEFINE ALTERNATEINDEX you code one of:

1
2
  UNIQUEKEY     /* every alternate key value must be unique */
  NONUNIQUEKEY /* duplicate alternate key values allowed */

You cannot specify both. If you omit both, some systems may default to one or the other; to avoid ambiguity, always specify explicitly. The keyword is stored in the catalog and is used when the AIX is built (BLDINDEX) and when you access by alternate key (e.g. READ by key, READNEXT for duplicates).

UNIQUEKEY vs NONUNIQUEKEY

Effect on BLDINDEX and Load

When you run BLDINDEX to build the alternate index from the base cluster, the system scans the base records and extracts the alternate key value from each. With UNIQUEKEY, every key value must be different. If two or more base records have the same alternate key value, BLDINDEX may fail with a duplicate-key error or may reject the duplicate (behavior depends on implementation). So before building an AIX with UNIQUEKEY, ensure the base data actually has unique values in the alternate key field. With NONUNIQUEKEY, duplicates are expected. The AIX is built so that one index entry can point to multiple base records (e.g. a list of pointers or a structure that supports multiple RBA/RRN values per key). So BLDINDEX succeeds even when many records share the same alternate key.

Effect on Read by Alternate Key

When your program reads by alternate key (e.g. opens the path and does a READ or START followed by READ/READNEXT), the uniqueness attribute affects how many records you get. With UNIQUEKEY, a read by alternate key returns at most one record—the one (if any) with that key value. With NONUNIQUEKEY, a read by alternate key can return multiple records. You typically do a START at the key and then READNEXT (or equivalent) to retrieve all base records that have that alternate key value. So UNIQUEKEY gives a one-to-one mapping from alternate key to base record; NONUNIQUEKEY gives a one-to-many mapping.

Choosing UNIQUEKEY or NONUNIQUEKEY

Choose based on the meaning of the alternate key field in your data:

Example: DEFINE ALTERNATEINDEX with UNIQUEKEY

An alternate index on a unique field such as employee ID (when the base is keyed by something else, e.g. name):

1
2
3
4
5
6
7
8
9
10
  DEFINE ALTERNATEINDEX ( -
    NAME(MY.APPL.EMPID.AIX) -
    RELATE(MY.APPL.EMPLOYEE.KSDS) -
    KEYS(8 0) -
    UNIQUEKEY -
    UPGRADE -
    RECORDSIZE(48 48) -
    CYLINDERS(1 1)) -
  DATA (NAME(MY.APPL.EMPID.AIX.DATA)) -
  INDEX (NAME(MY.APPL.EMPID.AIX.INDEX))

Here the alternate key is 8 bytes at offset 0 (e.g. employee ID). UNIQUEKEY means every employee ID must be unique in the base cluster. BLDINDEX will fail if two records have the same employee ID.

Example: DEFINE ALTERNATEINDEX with NONUNIQUEKEY

An alternate index on department code (many employees per department):

1
2
3
4
5
6
7
8
9
10
  DEFINE ALTERNATEINDEX ( -
    NAME(MY.APPL.DEPT.AIX) -
    RELATE(MY.APPL.EMPLOYEE.KSDS) -
    KEYS(4 20) -
    NONUNIQUEKEY -
    UPGRADE -
    RECORDSIZE(44 44) -
    CYLINDERS(1 1)) -
  DATA (NAME(MY.APPL.DEPT.AIX.DATA)) -
  INDEX (NAME(MY.APPL.DEPT.AIX.INDEX))

The alternate key is 4 bytes at offset 20 (department code). NONUNIQUEKEY allows many records to have the same department code. When you read by department, you get the first record with that department and then READNEXT to get the rest.

Primary Key vs Alternate Key Uniqueness

The base cluster primary key (in a KSDS) is always unique. You do not specify UNIQUEKEY for the base; it is inherent. UNIQUEKEY and NONUNIQUEKEY apply only to alternate indexes. So you can have a KSDS with a unique primary key and one or more alternate indexes: some alternate keys might be unique (UNIQUEKEY) and others might allow duplicates (NONUNIQUEKEY). For example, the base might be keyed by transaction ID (unique); one AIX might be on customer ID (NONUNIQUEKEY—one customer has many transactions) and another AIX on transaction ID again (UNIQUEKEY—same as primary, alternate access path).

Key Takeaways

• In DEFINE ALTERNATEINDEX you must specify UNIQUEKEY or NONUNIQUEKEY. They control whether the alternate key can have duplicate values.
• UNIQUEKEY: every alternate key value must be unique. Use when the alternate key is a unique identifier. BLDINDEX fails or rejects if duplicates exist.
• NONUNIQUEKEY: duplicate alternate key values are allowed. Use when the alternate key is a category or group. Read by key can return multiple records (e.g. READNEXT).
• Primary key uniqueness (KSDS) is fixed; UNIQUEKEY/NONUNIQUEKEY apply only to the alternate index.

Explain Like I'm Five

Imagine a phone book (base cluster) and an index by first name (alternate index). If you say UNIQUEKEY, you are saying every first name must be different—only one "John" allowed. If you say NONUNIQUEKEY, you are saying many people can have the same first name—many "John"s, and when you look up "John" you get a list of all of them. So UNIQUEKEY = one name per person; NONUNIQUEKEY = many people can share a name.

Test Your Knowledge