DEFINE ALTERNATEINDEX

DEFINE ALTERNATEINDEX (or DEFINE AIX) is the IDCAMS command that creates an alternate index—a secondary index that lets you access a base cluster (KSDS or ESDS) by a key other than the primary key. The AIX is itself a VSAM cluster with data and index components; it stores alternate key values and pointers to the base cluster records. Defining the AIX only allocates the structure; you must also run DEFINE PATH to link the AIX to the base cluster and BLDINDEX to build the index from the base data. This page explains the DEFINE ALTERNATEINDEX syntax, the main parameters (RELATE, KEYS, UNIQUEKEY/NONUNIQUEKEY, UPGRADE/NOUPGRADE), and how it fits into the three-step process of creating and using an alternate index.

What DEFINE ALTERNATEINDEX Creates

When you run DEFINE ALTERNATEINDEX, IDCAMS creates a new VSAM cluster. That cluster is the alternate index. It has a data component and an index component, just like a KSDS. The data component does not hold your application records; it holds AIX records, which typically consist of an alternate key value and a pointer (e.g. RBA or RRN) to the corresponding record in the base cluster. The index component allows fast lookup by alternate key. So the AIX is a separate catalog object with its own name, its own space, and its own structure. It is tied to the base cluster by the RELATE parameter and later by DEFINE PATH, so that when you open the path, the system knows to use this AIX to resolve alternate key requests and return base cluster records.

General Syntax

The command can be written as DEFINE ALTERNATEINDEX or DEFINE AIX. It is followed by parameters that identify the base cluster, the alternate key, and the AIX attributes. General form:

1
2
3
4
5
6
7
8
9
10
11
12
  DEFINE ALTERNATEINDEX ( -
    NAME(aix-cluster-name) -
    RELATE(base-cluster-name) -
    KEYS(length offset) -
    UNIQUEKEY | NONUNIQUEKEY -
    [ UPGRADE | NOUPGRADE ] -
    [ RECORDSIZE(...) ] -
    [ FREESPACE(...) ] -
    [ CYLINDERS(primary secondary) ]) -
  DATA ( ... ) -
  INDEX ( ... )
 

NAME is the name of the AIX cluster. RELATE is the name of the base cluster (KSDS or ESDS). KEYS(length offset) gives the length and offset of the alternate key in the base record—the same format as KEYS in DEFINE CLUSTER. You must specify either UNIQUEKEY or NONUNIQUEKEY. UPGRADE (default in many systems) means the AIX is maintained when the base is updated; NOUPGRADE means it is not. RECORDSIZE, FREESPACE, and space allocation apply to the AIX cluster. DATA and INDEX clauses can name the AIX data and index components, just as in DEFINE CLUSTER.

Required and Key Parameters

RELATE is required; without it the system does not know which base cluster the AIX belongs to. KEYS and UNIQUEKEY/NONUNIQUEKEY are required. NAME is required to identify the AIX in the catalog. The rest are optional but RECORDSIZE and space allocation are usually specified so the AIX has enough room. AIX record size is determined by the alternate key length and the pointer size; consult your IDCAMS documentation or use the recommended value for your key length and UNIQUEKEY/NONUNIQUEKEY.

RELATE: The Base Cluster

RELATE(base-cluster-name) specifies the base cluster. The base must be a KSDS or an ESDS. It must already exist and be cataloged. The AIX will be built from this base (when you run BLDINDEX) and will point to its records. You cannot RELATE to an RRDS, LDS, reusable cluster, catalog, another AIX, or a non-VSAM dataset. If the base is in a user catalog, ensure the job has access to that catalog (JOBCAT/STEPCAT) when you run DEFINE ALTERNATEINDEX and BLDINDEX.

KEYS: Alternate Key in the Base Record

KEYS(length offset) defines where the alternate key is in the base record. Length is the key length in bytes; offset is the position of the first byte (0-based). For example, if the base record is 100 bytes and the alternate key (e.g. department code) is at bytes 20–27, you would specify KEYS(8 20). The alternate key must lie entirely within the base record. The same field can be used for multiple AIXs if you need access by several different keys (each with its own DEFINE ALTERNATEINDEX and DEFINE PATH).

UNIQUEKEY vs NONUNIQUEKEY

UNIQUEKEY means every value in the alternate key field must be unique across all base records. The AIX will have one entry per base record. Use it when the alternate field is a unique identifier (e.g. a second key that is unique). NONUNIQUEKEY allows duplicate values: many base records can have the same alternate key (e.g. department ID 10). The AIX stores one index entry per distinct key value, with multiple pointers when there are duplicates. When you read by alternate key, you get the first matching record and can use READ NEXT to get the rest. Choose NONUNIQUEKEY when the alternate field is not unique (e.g. category, department, or status).

UPGRADE and NOUPGRADE

UPGRADE means that when the base cluster is updated (insert, delete, rewrite), the access method will update the AIX so it stays in sync. That way, new records get their alternate key in the AIX, and deleted or updated records are reflected. Use UPGRADE when the base is read-write and you need the alternate index to always reflect the current data. NOUPGRADE means the AIX is not updated when the base changes. The AIX can become stale. Use NOUPGRADE only when the base is read-only or when you plan to rebuild the AIX periodically with BLDINDEX. In most applications that update the base, UPGRADE is the right choice.

Example: Defining an Alternate Index

Suppose the base cluster is MY.EMPL.KSDS with 200-byte records and the primary key at bytes 0–7. You want an alternate index on department code at bytes 8–11 (4 bytes). Department codes can repeat (NONUNIQUEKEY). You want the AIX to stay in sync (UPGRADE).

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

After this, you would run DEFINE PATH to create a path (e.g. MY.EMPL.BY.DEPT) with PATHENTRY(MY.EMPL.AIX.DEPT), and then BLDINDEX to build the AIX from the base cluster. Once BLDINDEX completes, you can open the path and read by department code.

After DEFINE ALTERNATEINDEX: PATH and BLDINDEX

Defining the AIX does not make it usable yet. You must: (1) DEFINE PATH to link the AIX to the base cluster. The path has a name that applications use when opening the file to access by alternate key. (2) BLDINDEX to build the index. BLDINDEX reads the base cluster, extracts the alternate key (and pointer) for each record, and writes the AIX. After BLDINDEX, the AIX contains the keys and pointers; reads via the path will use this index to find base records. If you skip DEFINE PATH, there is no way to open the base by alternate key. If you skip BLDINDEX, the AIX is empty and no alternate key reads will return data.

Key Takeaways

• DEFINE ALTERNATEINDEX (or DEFINE AIX) creates the alternate index cluster; it does not build the index data.
• RELATE(base-cluster-name) specifies the base cluster (KSDS or ESDS). Required.
• KEYS(length offset) gives the alternate key position in the base record. UNIQUEKEY or NONUNIQUEKEY is required.
• UPGRADE keeps the AIX in sync when the base is updated; NOUPGRADE does not (rebuild with BLDINDEX to refresh).
• After DEFINE ALTERNATEINDEX, run DEFINE PATH and BLDINDEX before using the AIX.

Explain Like I'm Five

Imagine a big filing cabinet (base cluster) where folders are in order by employee number. You want to find folders by department instead. DEFINE ALTERNATEINDEX is like ordering a second, smaller cabinet (the AIX) that will hold a list: department number and which drawer in the big cabinet has that department. You tell the system which big cabinet (RELATE), which part of each folder is the department (KEYS), and whether many folders can share the same department (NONUNIQUEKEY). After you build that list (BLDINDEX) and connect it to the big cabinet (DEFINE PATH), you can ask by department and the system will look in the small cabinet to find the right drawer in the big one.

Test Your Knowledge