Create Index and LF keyed in IBM i

Index and LF are applied to the existing tables to access their data more quickly and efficiently.

This article will cover the creation of the Index and Key LF.

LF Keyed

DDS File

A          R RDDS8                                                  
A            FLD1          10A         TEXT('FIRST TEXT FLD')       
A                                      COLHDG('FIRST' 'TEXT' 'FLD') 
A            FLD2           2P 0       TEXT('FIELD2')               
A                                      COLHDG('FIELD2')

To create the LF on the above DDS file PFILE keyword with the file name is used at the record level.

A          R Record Formate                    PFILE(File Name)

Now, we will create the Keyed LF on the above DDS file

Keyed LF without Unique field

A          R RDDS8                     PFILE(DDS8) 
A            FLD1                                  
A            FLD2                                  
A          K FLD1

DSPFD on keyed LF without unique field

Keyed LF with Unique field

A                                      UNIQUE        
A          R RDDS8                     PFILE(DDS8)   
A            FLD1                                    
A            FLD2                                    
A          K FLD1

DSPFD on keyed LF with unique field

Index

DDL Table

CREATE TABLE LONGNAMETABLEDDL11(                                        
  LONGNAMECOLUMN_FLD1 FOR COLUMN FLD1 CHAR(10) NOT NULL WITH DEFAULT,   
  LONGNAMECOLUMN_FLD2 FOR COLUMN FLD2 DECIMAL(2) NOT NULL WITH DEFAULT  
                  )                                                     
                  RCDFMT RDDS8;                                         
   LABEL ON LONGNAMETABLEDDL11(                                         
     FLD1 IS 'FIRST               TEXT                FLD',             
     FLD2 IS 'FIELD2'                                                   
                 );                                                     
   LABEL ON LONGNAMETABLEDDL11(                                         
     FLD1 TEXT IS 'FIRST TEXT FLD',                                     
     FLD2 TEXT IS 'FIELD2'                                              
                 );                                                     
                                                                        
   RENAME LONGNAMETABLEDDL11 TO SYSTEM NAME DDL11;

To create the INDEX on the above DDL table Create Index keyword is used.

CREATE INDEX Long Name ON Table Name(Field Name) RCDFMT Record Formate

Now, we will create the Index on the above DDL table.

Index without Unique field

CREATE INDEX LONGNAMEINDEX11 ON DDL11(FLD1) RCDFMT RDDS8;   
RENAME INDEX LONGNAMEINDEX11 TO SYSTEM NAME DDL11IDX1;

DSPFD on Index without unique field

Index with Unique field

CREATE UNIQUE INDEX LONGNAMEUNIQUEINDEX11 ON DDL11(FLD1) RCDFMT RDDS8;   
RENAME INDEX LONGNAMEUNIQUEINDEX11 TO SYSTEM NAME DDL11IDX2;

DSPFD on Index with unique field

Read also :

AS400 and SQL Tricks