Using a Secondary Index in C-ISAM and ISAM Files

When a request contains search conditions for a field with a secondary index, the Adapters for C-ISAM and ISAM can access the secondary index directly, then retrieve the primary index value and use it to read the record. This process is much faster than reading an entire file sequentially and verifying each record against the search condition. It provides particular performance advantages when accessing large ISAM files, where secondary indexes are usually built on fields that are used in search conditions.


Top of page

Example: Using a Secondary Index in a C-ISAM File

The C-ISAM file's secondary index field must be defined in the Master File with the attributes ALIAS=KEYn and FIELDTYPE=I.

The following Master File has a primary key field, EMPLOYEE_ID5, and four secondary indexes, SSN5, STATE_CODE5, CITIZENSHIP5 and DEPARTMENT5. These fields are defined with FIELDTYPE=I and the ALIAS names KEY1, KEY2, KEY3 and KEY4:

FILENAME=EMPLOYEE, SUFFIX=CISAM   ,
 DATASET=/qa/edamvt/CISAM/employee, $
  SEGMENT=SEG1, SEGTYPE=S0, $
  GROUP=G5, ALIAS=KEY, ELEMENTS=1, $
   FIELDNAME=EMPLOYEE_ID5, ALIAS=E3, USAGE=I11, ACTUAL=I4, $ 
   FIELDNAME=SSN5, ALIAS=KEY1, USAGE=A11, ACTUAL=A11, FIELDTYPE=I,$ 
   FIELDNAME=FILLER, ALIAS=E5, USAGE=A1, ACTUAL=A1, $
   FIELDNAME=LAST_NAME5, ALIAS=E6, USAGE=A20, ACTUAL=A20, $
   FIELDNAME=FIRST_NAME5, ALIAS=E7, USAGE=A15, ACTUAL=A15, $
   FIELDNAME=FILLER, ALIAS=E8, USAGE=A1, ACTUAL=A1, $
   FIELDNAME=BIRTHDATE5, ALIAS=E9, USAGE=I11, ACTUAL=I4, $
   FIELDNAME=SEX5, ALIAS=E10, USAGE=A1, ACTUAL=A1, $
   FIELDNAME=FILLER, ALIAS=E11, USAGE=A3, ACTUAL=A3, $
   FIELDNAME=ETHNIC_GROU5, ALIAS=E12, USAGE=A15, ACTUAL=A15, $
   FIELDNAME=FILLER, ALIAS=E13, USAGE=A1, ACTUAL=A1, $
   FIELDNAME=STREET5, ALIAS=E14, USAGE=A20, ACTUAL=A20, $
   FIELDNAME=CITY5, ALIAS=E15, USAGE=A15, ACTUAL=A15, $
   FIELDNAME=FILLER, ALIAS=E16, USAGE=A1, ACTUAL=A1, $
   FIELDNAME=STATE_CODE5, ALIAS=KEY2, USAGE=A2, ACTUAL=A2, FIELDTYPE=I, $ 
   FIELDNAME=FILLER, ALIAS=E18, USAGE=A2,ACTUAL=A2,$
   FIELDNAME=ZIP5, ALIAS=E19, USAGE=A5, ACTUAL=A5, $
   FIELDNAME=FILLER, ALIAS=E20, USAGE=A3, ACTUAL=A3, $
   FIELDNAME=HOME_PHONE5, ALIAS=E21, USAGE=A12, ACTUAL=A12, $
   FIELDNAME=WORK_PHONE5, ALIAS=E22, USAGE=A4, ACTUAL=A4, $
   FIELDNAME=CLEARANCE5, ALIAS=E23, USAGE=A2, ACTUAL=A2, $
   FIELDNAME=FILLER, ALIAS=E24, USAGE=A2, ACTUAL=A2, $
   FIELDNAME=START_DATE5, ALIAS=E25, USAGE=I11, ACTUAL=I4, $
   FIELDNAME=SALARY5, ALIAS=E26, USAGE=I11, ACTUAL=I4, $
   FIELDNAME=JOB_TITLE5, ALIAS=E27, USAGE=A24, ACTUAL=A24, $
   FIELDNAME=JOB_LEVEL5, ALIAS=E28, USAGE=I11, ACTUAL=I4, $
   FIELDNAME=SCHEDULE5, ALIAS=E29, USAGE=I11, ACTUAL=I4, $
   FIELDNAME=CITIZENSHIP5, ALIAS=KEY3, USAGE=A5, ACTUAL=A5, FIELDTYPE=I, $ 
   FIELDNAME=FILLER, ALIAS=E31, USAGE=A3, ACTUAL=A3, $
   FIELDNAME=SUPERVISOR5, ALIAS=E32, USAGE=P15.0, ACTUAL=P8, $
   FIELDNAME=DEPARTMENT5, ALIAS=KEY4, USAGE=I11, ACTUAL=I4, FIELDTYPE=I,  $ 
   FIELDNAME=DISABILITY5, ALIAS=E34, USAGE=A1, ACTUAL=A1, $
   FIELDNAME=FILLER, ALIAS=E35, USAGE=A3, ACTUAL=A3, $

To process the following request,

TABLE FILE EMPLOYEE
PRINT EMPLOYEE_ID5 SSN5 LAST_NAME5 IF STATE_CODE5 EQ 'IL'
END

the adapter uses STATE_CODE5 in a secondary index search. The C-ISAM table is accessed using the E (EQ) option on the secondary key. If more than one record has the same value for the secondary key, the adapter performs a sequential read (S) on the secondary index until all values of the secondary key are retrieved.


iWay Software