1.Why do we have indexes?
A). Indexes can be used to enforce uniqueness of keys. Indexes are mainly created to improve SQL performance
2. What’s the cost of indexes ?
A). Indexes consume a lot of disk space (DASD). An index adds ~30% of CPU on INSERTs / DELETEs
3. DB2 v10 new feature?
A).ALTER INDEX IX ADD INCLUDE (COL)
4. What is it good for ?
A). The new INCLUDE feature in DB2 v10 allows you to
reduce the number of indexes, it means: Cost savings in DASD and Cost savings in CPU
5. How to locate this scenario in your DB2 environment ?
A). Query the system catalog to locate indexes whose keys overlap with the unique index !
6) Another DB2 v10 new feature?
A). improves the performance of applications accessing
LOB data, (reduces the need to access the auxiliary LOB Table Space) enables the creation of expression-based indexes on the inline portion of a LOB column (improves performance of searches through a LOB column)
the Inline portion of the LOB can be compressed
6) Where Stored procedures will be stored ?
A).SELECT CREATED BY,OWNER,NAME,ORIGIN,
WHERE ORIGIN = ‘N’
AND CREATEDTS < ( SELECT
WHERE NAME = ‘SYSAUTOALERTS’
AND CREATOR = ‘SYSIBM’ );
Stored procedures are listed in SYSIBM.SYSROUTINES,
and the column ORIGIN = ‘N’ indicates that we deals with a Native SQL Procedure.