If you define more indexes ( Example 5 out of 10 cols ) on a table , it helps when you fetch the rows from table.
But excessive use of indexing affects the performance of the table when you update or insert into the table.
As a programmer , all we can do is SQL performance tuning and DBA does the DB2 performance tuning.
Indexing is one of the performance tuning technique , but one should be cautious as excess use of it is bad..
Let me give an example :
Suppose you have a table with 5 cols and Emp Id is one of the col's :
When you define primary key as Emp id , Index is automatically created for Emp id.
Hence you dont need to create a index exclusively.
Suppose you are using some other column name( other than prmary key ) from the same table in most of
youe SQL queries to fetch the rows , and teh table is extremely large , then you can go for indexing
that column too.
Let me know if you need any other info..