Hi,
What is the best approach for storing Indexes:-
(1) Index should be store same database data table space?

(2) We should create separate table space for indexes, and it should be separate for every database?

(3) One separate table space for all database single table space for storing the indexes.

(4) Is any schema\user security associate with indexes while storing in table space.

Thanks

Generally, indexes should be stored in separate tablespaces so that they can be placed in a separate bufferpool from the data. This is based on the assumption that you may want to have a higher bufferpool hit ratio for indexes than for the data, and that the entire database (tables and indexes) is larger than the amount of bufferpool memory that can be allocated on that server.

The number of tablespaces for data and indexes depends on the size of the database and each table (and whether you will need to do tablespace level backup/restores). Also, having more than one tablespace can improve backup and restore performance in most cases, if the database is large enough.

The above information is for DB2 LUW. DB2 for z/OS has different considerations.

Is any schema\user security associate with indexes while storing in table space.