Let's understand Mainframe
Home Tutorials Interview Q&A Quiz Mainframe Memes Contact us About us

Module 4: Alternate INDEX


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 from abive syntax in IDCAMS Define Cluster article. 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

//DEFNAIX EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //SYSIN DD * DEFINE AIX (NAME(PAYT.PAY.KSDSAIX) - RELATE(PAYT.PAY.KSDS) - FREESPACE(20,20) - KEYS(35,40) - UNIQUEKEY - UPGRADE - RECORDSIZE(80,80)) - DATA(NAME(PAYT.PAY.KSDSAIX.DATA)) - INDEX(NAME(PAYT.PAY.KSDSAIX.INDEX)) /*



Advertisements






© copyright mainframebug.com
Privacy Policy