Create Index and LF keyed in IBM i

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

Related Post

Read also :

• CRTPF and Create Table in IBM i
• Primary Key in DDS and DDL tables
• Data Validation in DDS and DDL tables
• Column Heading in DDS Files and DDL tables
• Column Text in DDS and DDL tables
• Attributes of DDL table
• Auto-update Timestamp Column using ROW CHANGE TIMESTAMP in DDL table
• ADD Constraint in DDL tables
• Create View and LF Non-Keyed in IBM i
• DDS to DDL Modernization : Level Check Error
• Identity column in DDL table
• QSQGNDDL API - IBM to convert DDS file into DDL table
• QSQGNDDL API - IBM to convert DDS file into DDL table (Continuing...)