Create Index and LF keyed in IBM i

Create Index and LF keyed in IBM i
Create Index and LF keyed in IBM i, CREATE INDEX, index, keyed LF, logical file, sql index, db2, db2 for i sql, as400,ibmi, dds to ddl, dds to sql
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

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain