Here are the selected questions with answers on DB2.
Questions
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
7) Where Stored procedures will be stored ?
A).SELECT CREATED BY,OWNER,NAME,ORIGIN,
CREATEDTS
FROM SYSIBM.SYSROUTINES
WHERE ORIGIN = 'N'
AND CREATEDTS < ( SELECT
CREATEDTS
FROM SYSIBM.SYSTABLES
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.
Related
Srinimf 