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?
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.
 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?
What are the various locking levels available?
 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

No comments: