Defining Alternate index

• ‘DEFINE AIX’ or ‘DEFINE ALTERNATEINDEX’ command is used to define the alternate index
• Simplified Syntax with important parameters:-
  DEFINE ALTERNATEINDEX - (NAME(alternate-index-name) - RELATE(vsam-file-name) - [CONTROLINTERVALSIZE ( size ) ] - [FREESPACE ( CI-percent [CA-percent] | 0 0 ) ] - [KEYS ( length offset | 64 0 ) ] - [UNIQUEKEY | NONUNIQUEKEY ] - [UPGRADE | NOUPGRADE] - [RECORDSIZE ( average maximum ) ]) - [DATA - (NAME(vsam-file-name.data))] - [INDEX - (NAME(vsam-file-name.index))]
• We have already discussed some parameters in Define Cluster module Below are the most important parameters of DEFINE AIX:-

  Parameter	Description
  RELATE	Relates AIX with base cluster
  KEYS	Defines the length and offset of the alternate key in base cluster
  UNIQUEKEY | NONUNIQUEKEY	When specified UNIQUEKEY, alternate index field must hold unique values only When specified NOUNIQUEKEY, alternate index field allowed to contain duplicates
  UPGRADE | NOUPGRADE	When specified UPGRADE, the alternate index should be modified if the base cluster is modified When specified NOUPGRADE, the alternate indexes should be left alone if the base cluster is modified. UPGRAE is default
  RECORDSIZE	Specifies the record size of the alternate index record. It is calculated using the formula in the next table

• Alternate Index record size calculation

  	UNIQUE alternate key	NOUNIQUE alternate key with maximum n duplicates
  KSDS	5 + Alternate key length + Primary key length	5 + Alternate key length + (n * Primary key length)
  ESDS	5 + Alternate key length + RBA size	5 + Alternate key length + (n* RBA size)

• In above shown calculations the first five bytes are used for storing control information of Alternate Index record and below we have described purpose of each byte:-
  
  • Byte 1: Type of cluster; Hex ’00’ indicates ESDS and Hex ‘01’ indicates KSDS
  • Byte 2: Used to hold length of base cluster pointer in alternate index; Primary key length for KSDS and Hex ‘04’ for ESDS
  • Byte 3 & 4: Half word value indicating the number of occurrences of the base cluster pointers within the alternate index record; X'0001' for a unique alternate key
  • Byte 5: Length of alternate key

Example:-

Defining alternate index