Define Alternate Index (for Experienced)


Define Alternate Index process defines the alternate index components (cluster, DATA, and INDEX) and their catalog entries. At this point, an alternate index does not have any link with the base cluster data.

The DEFINE ALTERNATEINDEX command is used to define an alternate index.

Notes -

  • The alternate index's subparameters are specified with the ALTERNATEINDEX parameter.
  • The parameters coded at the cluster level apply to the cluster component or the whole alternate index (including DATA and INDEX components).
  • The parameters coded at the DATA or INDEX components can individually apply to the respective components.
Note! An alternate index defines over a KSDS or ESDS cluster. An alternate index not allowed to define on a reusable cluster, a fixed or variable-length RRDS, an extended ESDS, a catalog, another alternate index, an LDS, or a non-VSAM dataset.

The alternate index has three components, and those are -

  • An alternate index cluster - represents the alternate index as a key-sequenced cluster.
  • DATA Component - represents the alternate index's data component.
  • INDEX Component - represents the alternate index's index component.

High-level Syntax -


//JOBNAME  JOB job-card-parameters
//STEP1    EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  DEFINE ALTERNATEINDEX (parameters) 
	[DATA(parameters)]
	[INDEX(parameters)]
	[CATALOG(subparameters)]
/*

Detailed Syntax -


//JOBNAME  JOB job-card-parameters
//STEP1    EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  DEFINE ALTERNATEINDEX
  	  (NAME(entryname)
  	  RELATE(entryname)
  	  {CYLINDERS(primary[ secondary])|
  	   KILOBYTES(primary[ secondary])|
  	   MEGABYTES(primary[ secondary])|
  	   RECORDS(primary[ secondary])|
  	   TRACKS(primary[ secondary])}
  	  VOLUMES(volser[ volser...])
  	  [BUFFERSPACE(size)]
  	  [CONTROLINTERVALSIZE(size)]
  	  [ERASE|NOERASE]
  	  [FILE(ddname)]
  	  [FREESPACE(CI-percent[ CA-percent]| 0 0)]
  	  [KEYS(length   offset|64 0)  ]
  	  [MODEL(entryname[ catname])]
  	  [OWNER(ownerid)]
  	  [RECORDSIZE(average   maximum| 4086 32600)]
  	  [REUSE|NOREUSE]
  	  [SHAREOPTIONS(crossregion[ crosssystem]| 1 3)]
  	  [SPEED|RECOVERY]
	  [UNIQUEKEY|NONUNIQUEKEY]
  	  [UPGRADE|NOUPGRADE]
  	  [WRITECHECK|NOWRITECHECK])
	[DATA (
  	  [NAME(entryname)]
  	  {CYLINDERS(primary[ secondary])|
  	   KILOBYTES(primary[ secondary])|
  	   MEGABYTES(primary[ secondary])|
  	   RECORDS(primary[ secondary])|
  	   TRACKS(primary[ secondary])}
  	  [VOLUMES(volser[ volser...])]
  	  [ATTEMPTS(number)]
  	  [AUTHORIZATION(entrypoint[ string])]
  	  [BUFFERSPACE(size)]
  	  [CODE(code)]
  	  [CONTROLINTERVALSIZE(size)]
  	  [ERASE|NOERASE]
  	  [FREESPACE(CI-percent[ CA-percent])]
  	  [KEYS(length  offset)]
  	  [MODEL(entryname [ catname ])]
  	  [OWNER(ownerid)]
  	  [RECORDSIZE(average  maximum)]
  	  [REUSE|NOREUSE]
  	  [SHAREOPTIONS(crossregion[ crosssystem])]
  	  [SPEED|RECOVERY]
  	  [UNIQUEKEY|NONUNIQUEKEY]
  	  [WRITECHECK|NOWRITECHECK])]
	[INDEX (
  	  [NAME(entryname)]
  	  {CYLINDERS(primary[ secondary])|
  	   KILOBYTES(primary[ secondary])|
  	   MEGABYTES(primary[ secondary])|
  	   RECORDS(primary[ secondary])|
  	   TRACKS(primary[ secondary])}
  	  [VOLUMES(volser[ volser...])]
  	  [ATTEMPTS(number)]
  	  [AUTHORIZATION(entrypoint[ string])]
  	  [CODE(code)]
  	  [CONTROLINTERVALSIZE(size)]
  	  [EXCEPTIONEXIT(entrypoint)]
  	  [MODEL(entryname[ catname ])]
  	  [OWNER(ownerid)]
  	  [REUSE|NOREUSE]
  	  [SHAREOPTIONS(crossregion[ crosssystem])]
  	  [WRITECHECK|NOWRITECHECK])]
  	[CATALOG(catname)]
/*
Note!
  • The parameters enclosed with [ ] are optional. The remaining parameters (without [ ]) are mandatory.
  • The parameters are enclosed in parentheses ( ) and are followed by other parameters separated by space for all (Cluster, DATA and INDEX) components.

Parameters


The DEFINE ALTERNATEINDEX command uses to create the alternate index, and below are the mandatory and optional parameters.

Mandatory Parameters


ALTERNATEINDEX


  • ALTERNATEINDEX parameter is used to define the cluster or re-catalog cluster or cluster entry.
  • The parameters specified with the ALTERNATEINDEX keyword are applied to the whole cluster that is going to create.
  • The set of sub-parameters for the ALTERNATEINDEX parameter is enclosed with parenthesis ().

Short Description: AIX

NAME (entryname)


  • NAME parameter specifies/used to define the AIX cluster's entry name or the name of each of its components.
  • The entryname is unique to the cluster and is not shared with the cluster's (DATA and INDEX) components.
  • NAME (entryname) is mandatory and should be specified for the Cluster component.
  • entrynames are different for the cluster, its data component, and its index component.
  • The DATA and INDEX components should be named individually to set the separate names for each component.
  • The name is automatically generated if no name is specified for the DATA or INDEX component.
  • The name for a AIX can be up to 44 alphanumeric characters.

RELATE (entryname)


  • Specifies the alternate index base cluster name.
  • This is a mandatory entry while creating alternate index. If missed to provide, the DEFINE ALTERNATEINDEX job gets abended.

CYLINDERS (primary[ secondary])
KILOBYTES (primary[ secondary])
MEGABYTES (primary[ secondary])
RECORDS (primary[ secondary])
TRACKS (primary[ secondary])


  • The above parameters are used to specify the amount of space allocated to the AIX cluster in CYLINDERS (CYL), KILOBYTES (KB), MEGABYTES (MB), RECORDS (REC), or TRACKS (TRK) from the volume's available space.
  • A MEAGBYTES (MB) parameter specifies that the memory allocation is always done in cylinders.
  • A KILOBYTES (KB) and RECORDS (REC) parameters specifies that the memory allocation is always done in tracks.
  • If the KILOBYTES keyword is coded, the memory allocated in tracks that is equal to the number of KILOBYTES specified. Similarly, for MEGABYTES.
  • If the RECORDS keyword is coded, the memory allocated in tracks that is equal to containing the given number of records.
  • Regardless of the allocation type, The CA (control area) size is calculated based on the smaller of the two allocation quantities (primary or secondary) but not based on the allocation type.
  • A CA is never greater than a single-cylinder; it might be smaller than a cylinder, depending on the quantity and type of allocation.

When multiple volumes are used for a dataset -

  • The first volume is defined as the primary volume. The initial allocation of a dataset is on the primary volume.
  • A dataset's secondary space allocation (if defined) is the space allocated when the primary space is filled, and the dataset needs additional space on the same volume.
  • The space is divided between the DATA and INDEX components. The calculations are based on the control interval size, record size, device type, and other data set attributes.
  • The total space specified is allocated to the DATA component. An additional amount of space is allocated to the INDEX component, depending on control interval size, record size, device type, and other data set attributes.

primary

Specifies the amount of space that should allocate initially to the AIX cluster.

secondary

Specifies an amount of space required each time when the AIX cluster extends.

Short Description: CYL, KB, MB, REC, TRK

VOLUMES (volser[ volser...])


  • VOLUMES parameter specifies the volume name on which AIX needs to be created.
  • VOLUMES must be specified either as a AIX parameter or as a parameter of both DATA and INDEX.
  • Volumes are allocated to the AIX or used by the AIX in the same order they specify.
  • If there is not enough space on the volume as specified, the allocation is unsuccessful.

Short Description: VOL

Optional Parameters


BUFFERSPACE (size)


  • BUFFERSPACE parameter is used to specify the minimum space for buffers.
  • The AIX uses the buffer size to calculate the DATA and INDEX component's control interval sizes.
  • Suppose BUFFERSPACE is not provided and the data is key sequenced. In that case, AIX attempts to get enough space to contain two DATA component CIs and one INDEX component CI.

size

The size should be enough (equal or greater than) to contain two DATA component CIs and one INDEX CI if the data is key sequenced.

Short Description: BUFSP or BUFSPC

CONTROLINTERVALSIZE (size)


  • CONTROLINTERVALSIZE parameter specifies the size of the control interval for the AIX cluster or component.
  • If CONTROLINTERVALSIZE is specified at the cluster level, it applies to the component where no CONTROLINTERVALSIZE has been specified.
  • The size of the control interval depends on the maximum size of the data records and the amount of buffer space provided in the AIX cluster definition.
  • If CONTROLINTERVALSIZE is not coded, AIX determines the size of control intervals.

size

Specifies cluster DATA and INDEX components CI size.

Short Description: CISZ or CNVSZ

DATACLASS (class)


  • Specifies allocation attributes for the new AIX datasets. The storage administrator provides the data class name (1-8 characters).

Short Description: DATACLAS

ERASE | NOERASE


These options specify whether the AIX cluster components memory is to be erased or not erased when AIX cluster entry in the catalog is deleted.

Default: NOERASE

ERASE

ERASE parameter specifies the AIX cluster each component memory erased (overwritten with binary zeros) when its catalog entry is deleted.

Short Description: ERAS

NOERASE

NOERASE parameter specifies that each AIX cluster component memory is not to erased and left as it is.

Short Description: NERAS

FILE (ddname)


  • FILE parameter names the DD statement that identifies and allocates the volumes specified by the VOLUMES keyword.
  • If more than one volume is specified, all volumes must be the same device type.
  • If the FILE parameter is not specified, an attempt is made to allocate the required volumes dynamically.
  • FILE parameter should specify only when the UNIQUE parameter is specified.
  • A FILE parameter should be specified for every unique component individually or on the cluster level.
  • If both components (DATA and INDEX) are unique and reside on the same volume, the FILE parameter should be specified for both DATA and INDEX.

Restriction -

When FILE refers to multi-volume of the same device type, the volumes that are represented by the DD statement cannot be concatenated.

FREESPACE (CI-percent[ CA-percent]|0 0)


  • FREESPACE specifies the percentage of free space that is to be left empty in each control interval and control area when the cluster is initially loaded or after the mass insert.
  • CI-percent is a percentage of the space to be kept aside for adding new records and updating existing records with a length change.
  • CI-percent and CA-percent should be equal to, or less than, 100 and can be expressed in decimal (n), hexadecimal (X'n'), or binary (B‘n’) form.
  • If FREESPACE(100 100) is specified, the CIs and CAs are not left empty.

Short Description: FSPC

Default: FREESPACE (0 0)

KEYS (length offset | 64 0)


  • KEYS parameter specifies the information about the alternate key field in the base cluster's data record.

length offset

  • Specifies key length and its displacement (in bytes) positions from the offset the record.
  • The sum of length plus offset cannot exceed the length of the shortest record.

Default: KEYS (64 0)

MODEL (entryname[ catname])


  • The MODEL parameter is used to specify an already-defined cluster entry is to be used as a model for the entry being built.
  • The entry using as a model should be of the same entry type as the new entry being built.

entryname

  • Specifies the name of the cluster or component entry to be used as a model.

catname

  • Names the model entry's catalog.

OWNER (ownerid)


  • specifies the AIX cluster's owner.
  • If the dataset is created in TSO/E, the TSO/E user's userid becomes the ownerid.

RECORDSIZE (average maximum|4086 32600)


  • RECORDSIZE specifies the average and maximum lengths in bytes of the alternate index records in the DATA component.

Short Description: RECSZ

SHAREOPTIONS (crossregion[ crosssystem]|1 3)


  • SHAREOPTIONS parameter specifies the sharing options of how a AIX component or cluster can share among users.
crossregion

crossregion specifies the amount of sharing allowed among regions within the same system or multiple systems.

OPT 1

  • Any number of users can access the dataset for reading, or one user can access it for writing.

OPT 2

  • Any number of users can access the dataset for reading, and one user can access it for writing.

OPT 3

  • Any number of users can fully share the dataset. Each user is responsible for maintaining both read and write integrity for the data the program is accessing.

OPT 4

  • Any number of users can fully share the dataset. VSAM refreshes the buffers for each request that is used for direct processing.
crosssystem

crosssystem option specifies the amount of sharing allowed among systems.

OPT 1

Reserved

OPT 2

Reserved

OPT 3

  • OPT 3 specifies the dataset is fully shared. With this option, each user is responsible for maintaining both read and write integrity for the data that the user's program is accessing.

OPT 4

  • OPT 4 specifies that the dataset is fully shared. VSAM refreshes the buffers for each request that is used for direct processing.

Short Description: SHR

Default: SHR (1 3)

SPEED | RECOVERY


  • These parameters specify whether the data component control areas are to be preformatted before alternate index records are loaded into them.
  • This parameter is only considered during the actual loading (creation) of a dataset.

SPEED

  • The SPEED parameter does not preformat the DATA component's space.

Default: RECOVERY

RECOVERY

  • The RECOVERY parameter specifies that does preformat the data component's space prior to writing the data records.
  • RECOVERY is a way to verify used storage for each CA on the device before the data is written.

Short Description: RCVY

REUSE | NOREUSE


  • These parameters specify whether the AIX cluster can open again and again for reuse.

REUSE

  • Specifies that the AIX cluster can open again and again for usage.

Short Description: RUS

NOREUSE

  • Specifies that the AIX cluster cannot be opened again as a new cluster.

Short Description: NRUS

WRITECHECK | NOWRITECHECK


This option is outdated and has no effect.

UNIQUEKEY | NONUNIQUEKEY


These parameters specify whether the alternate key is unique or more than one data record can contain the same key value for the alternate index.

UNIQUEKEY

The UNIQUEKEY parameter used to specify the AIX is unique, and no duplicate keys are allowed. While building the AIX for the dataset, if more than one data record contains the same key value for the AIX key, the BLDINDEX processing ends with an error message.

Short Description: UNQK

NONUNIQUEKEY

The NONUNIQUEKEY parameter used to define the AIX is nonunique, and duplicate keys are allowed.

Short Description: NUNQK

UPGRADE | NOUPGRADE


Specifies whether or not the alternate index is to be kept up to date when its base cluster is modified.

UPGRADE

Upgrades the cluster's AIX to reflect new changes when the base cluster's records are added to, updated, or erased.

Short Description: UPG

NOUPGRADE

Specifies that the AIX does not upgrade when its base cluster is modified.

Short Description: NUPG

CATALOG (catname)


  • Specifies the catalog name in which the AIX cluster is to be defined.

Short Description: CAT

Example -


Requirement - Create alternate index for EMPL-DEPT-ID on the employee KSDS file.

Input File Layout (COBOL) -

01 EMPL-REC.
   05 EMPL-ID			PIC X(03).
   05 EMPL-NAME			PIC X(15).
   05 EMPL-DESIGNATION	PIC X(05).
   05 EMPL-DEPT-ID		PIC X(05).
   05 EMPL-LOC			PIC X(05).
   05 EMPL-SALARY		PIC 9(07).

Input File - MATEPK.EMPL.KSDS

Employee File Mapping

Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
//MATEPKA JOB (123),'MTH',CLASS=A,MSGCLASS=A,MSGLEVEL=(1,1),            
//             NOTIFY=&SYSUID                                           
//************************************************************          
//* DEFINE ALTERNAME INDEX                                              
//************************************************************          
//STEP10   EXEC PGM=IDCAMS                                              
//SYSPRINT DD SYSOUT=*                                                  
//SYSIN    DD  *                                                        
  DEFINE AIX  -                                                         
     (NAME(MATEPK.EMPL.DEPTAIX) -                                       
      RELATE(MATEPK.EMPL.KSDS)  -                                       
      CISZ(4096)                -                                       
      KEYS(5,24)                -                                       
      NONUNIQUEKEY              -                                       
      UPGRADE                   -                                       
      RECORDSIZE(47,47)         -                                       
      CYLINDERS(2,1)            -                                       
      FREESPACE(10,20)          -                                       
      VOLUMES(DEVHD4))          -                                       
  DATA(NAME(MATEPK.EMPL.DEPTAIX.DATA))  -                               
  INDEX(NAME(MATEPK.EMPL.DEPTAIX.INDEX))                                
/*                                                                      
//                                                                      
**************************** Bottom of Data ****************************

In the above JCL, MATEPK is the userid and change it as required.

Output -

Once the above JCL is submitted, check the MAXCC of the job for any errors. If the MAXCC is 00 or 04, AIX successfully created.

********************************* TOP OF DATA **********************************
IDCAMS  SYSTEM SERVICES                                           TIME: 10:58:56
                                                                                
  DEFINE AIX  -                                                                 
     (NAME(MATEPK.EMPL.DEPTAIX) -                                               
      RELATE(MATEPK.EMPL.KSDS)  -                                               
      CISZ(4096)                -                                               
      KEYS(5,24)                -                                               
      NONUNIQUEKEY              -                                               
      UPGRADE                   -                                               
      RECORDSIZE(47,47)         -                                               
      CYLINDERS(2,1)            -                                               
      FREESPACE(10,20)          -                                               
      VOLUMES(DEVHD4))          -                                               
  DATA(NAME(MATEPK.EMPL.DEPTAIX.DATA))  -                                       
  INDEX(NAME(MATEPK.EMPL.DEPTAIX.INDEX))                                        
IDC0508I DATA ALLOCATION STATUS FOR VOLUME DEVHD4 IS 0                          
IDC0509I INDEX ALLOCATION STATUS FOR VOLUME DEVHD4 IS 0                         
IDC0001I FUNCTION COMPLETED, HIGHEST CONDITION CODE WAS 0                       
                                                                                
IDC0002I IDCAMS PROCESSING COMPLETE. MAXIMUM CONDITION CODE WAS 0               
******************************** BOTTOM OF DATA ********************************

Verify the AIX in 3.4 (Dataset List utility) or any File management tools.

Verify AIX

Explaining Example -

In the above example,

  • AIX (NAME(MATEPK.EMPLDEPT.AIX) specifies the AIX cluster name.
  • RECORDSIZE(47,47) specifies the record average length is 47, and the maximum size is 47. So the KSDS file used is fixed-length.
  • CYLINDERS(2,1) specifies the primary memory allocation is 2 CYLINDERS, and secondary memory allocation is 1 CYLINDER.
  • CISZ(4096) specifies the control interval size is 4096.
  • KEYS(5,24) specifies the alternate key position in the EMPL KSDS file.
  • VOLUMES(DEVHD4) specifies that allocate the ESDS on volume DEVHD4.
  • NONUNIQUEKEY parameter specifies the alternate key can have duplicates.
  • UPGRADE specifies the AIX should update when base cluster is updated.
  • DATA(NAME(MATEPK.EMPLDEPT.AIX.DATA)) specifies the AIX DATA component name.
  • INDEX(NAME(MATEPK.EMPLDEPT.AIX.INDEX)) specifies the AIX INDEX component name.