 |
| Creating Indexes in DB2 for i SQL |
Indexes can be created using CREATE INDEX statement and can be used to sort and select data. Indexes also helps the system to retrieve the data faster for performance management. Indexes are similar to keyed LF.
CREATE INDEX INDEX1LONGNAME FOR SYSTEM NAME INDEX1 ON PF1(EMPID)
Here we created the index name INDEX1LONGNAME but since its a long name so we use FOR SYSTEM NAME clause to provide system name as INDEX1 otherwise system name would have been generated as INDEX00001. Index is created on table PF1 and column EMPID.
We can create any number of indexes. However, it is maintained by system so it is not suitable to create large number of indexes on a table.
Do WRKOBJ INDEX1
Opt Object Type Library Attribute Text
INDEX1 *FILE EASYCLASS1 LF
Also, one most important thing to discuss is that we cannot run SELECT statement on SQL indexes. If we try to run this query from STRSQL session it gives us below error.
INDEX1 in EASYCLASS1 not table, view, or physical file.SQL7011
Additional Message Information
Message ID . . . . . . : SQL7011 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Message . . . . : INDEX1 in EASYCLASS1 not table, view, or physical file.
Cause . . . . . : The SQL statement *N cannot be performed on a file which
is not a table, view, single format logical file, or physical file.
Recovery . . . : Do one of the following:
-- Use a control language (CL) command to do the function.
-- Select the correct table, view, logical, or physical file.