Define Alternate Index
Define Alternate Index (for Beginners)
Summary
For extensive information, go through the 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.
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...])
[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]
[WRITECHECK|NOWRITECHECK])
[DATA (
[NAME(entryname)]
[data-component-parameters]
[INDEX (
[NAME(entryname)]
[index-component-parameters]
[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
| Parameters | Description |
|---|---|
| ALTERNATEINDEX | ALTERNATEINDEX parameter is used to define the cluster or re-catalog cluster or cluster entry. 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. entrynames is unique to the cluster and is different from the cluster's (DATA and INDEX) components. |
| RELATE (entryname) | Specifies the alternate index base cluster name. |
| CYLINDERS (primary[ secondary]) KILOBYTES (primary[ secondary]) MEGABYTES (primary[ secondary]) RECORDS (primary[ secondary]) TRACKS (primary[ secondary]) |
These parameters are used to specify the amount of space allocated to the AIX in CYLINDERS (CYL), KILOBYTES (KB), MEGABYTES (MB), RECORDS (REC), or TRACKS (TRK) from the volume's available space. 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. Short Description: VOL |
Optional Parameters
| Parameters | Description |
|---|---|
| CONTROLINTERVALSIZE (size) | CONTROLINTERVALSIZE parameter specifies the size of the control interval for the AIX cluster or component. Short Description: CISZ or CNVSZ |
| 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 Short Description(ERASE): ERAS Short Description(NOERASE): NERAS |
| 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. 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. Default: KEYS (64 0) |
| OWNER (ownerid) | specifies the AIX cluster's owner. |
| 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 |
| SPEED | RECOVERY | These parameters specify whether the data component control areas are to be preformatted before alternate index records are loaded into them. Default: RECOVERY Short Description: RCVY |
| 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. Short Description: UNQK 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. Short Description: UPG Short Description: NUPG |
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
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.
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.