DB2 Interview Questions and Answers For Graduates Part-3
22.What is meant by EXPLAIN?
Use of EXPLAIN is for displaying the access path as determined by the optimizer for SQL statement. It can also be used in case of SPUFI for single SQL statements or in the BIND step for embedded SQL.
23.How to do the EXPLAIN of any dynamic SQL statement?
User can use SPUFI or QMF statements to EXPLAIN the dynamic SQL statement. They can also include EXPLAIN command in the embedded dynamic SQL statements.
24.What are the isolation levels possible?
Two isolation levels are possible. One is CURSOR stability and the other one is Repeatable Read denoted as CS and RR respectively.
25.What is the difference between the CS and RR isolation levels?
CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of the transaction.
26.What is meant by lock escalation?
Lock escalation is the process of promoting PAGE lock sizes to table or table space lock size when the transaction has acquired more locks than the ones specified in NUMLKTS. Locks have to be taken on objects in single table space for escalations to take place.
27.What are the various types of locks?
There are three different types of locks such as SHARE, EXCLUSIVE, and UPDATE.
28.What is ALTER?
ALTER is the SQL command to change the definition of DB2 objects.
29.What is a DBRM, PLAN?
DBRM denotes Data Base Request Module. It has the SQL statements that are extracted from the host language program obtained by the pre-compiler. PLAN is the result of the BIND process and has executable code for SQL statements in DBRM.
30.What is meant by ACQUIRE/RELEASE in BIND?
ACQUIRE/RELEASE in BIND determines the point at which the DB2 either acquires or releases locks against the table and the table spaces. This includes the intent locks.