DB2 Interview Questions

What are the disadvantages of PAGE level lock?
High resource utilization if large updates are to be done

What is lock escalation?

Promoting a PAGE lock-size to table or tablespace lock-size when a transaction has acquired more locks than specified in NUMLKTS. Locks should be taken on objects in single tablespace for escalation to occur.

 What are the various locks available?
SHARE, EXCLUSIVE, UPDATE
Can I use LOCK TABLE on a view?
No. To lock a view, take lock on the underlying tables.
What is ALTER ?
How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/QMF? Give an example with a host variable in WHERE clause.)
Use a question mark in place of a host variable ( or an unknown value ). e.g.
SELECT EMP_NAME
FROM EMP
WHERE EMP_SALARY > ?
 What are the isolation levels possible ?
CS: Cursor Stability
RR: Repeatable Read
 What is the difference between CS and RR isolation levels?
CS: Releases the lock on a page after use
RR: Retains all locks acquired till end of transaction
 Where do you specify them ?
ISOLATION LEVEL is a parameter for the bind process.
 When do you specify the isolation level? How?
During the BIND process. ISOLATION ( CS/RR )…
I use CS and update a page. Will the lock be released after I am done with that page?
No.
What are the various locking levels available?
PAGE, TABLE, TABLESPACE
 How does DB2 determine what lock-size to use?
1. Based on the lock-size given while creating the tablespace
2. Programmer can direct the DB2 what lock-size to use
3. If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE