FAQs(166 to 180)

166) How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/QMF? Give an example with a host variable in WHERE clause)

A) Use a question mark in place of a host variable (or an unknown value). For instance,

SELECT EMP_NAME FROM EMP WHERE EMP_SALARY > ?

167) What are the isolation levels possible ? 

A) CS: Cursor Stability

RR: Repeatable Read

168) What is the difference between CS and RR isolation levels?

A) CS: Releases the lock on a page after use

RR: Retains all locks acquired till end of transaction

169) When do you specify the isolation level? How?

A) During the BIND process(ISOLATION LEVEL is a parameter for the bind process). ISOLATION ( CS/RR )...

170) I use CS and update a page. Will the lock be released after I am done with that page?

A) No.

171) What are the various locking levels available?

A) PAGE, TABLE, TABLESPACE

172) How does DB2 determine what lock-size to use?

A) There are three methods to determine the lock-size. They are:

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 choses a lock-size of PAGE

173) What are the disadvantages of PAGE level lock?

A) High resource utilization if large updates are to be done

174) What is lock escalation?

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

175) What are the various locks available?

A) SHARE, EXCLUSIVE, UPDATE

176) Can I use LOCK TABLE on a view?

A) No. To lock a view, take lock on the underlying tables.

177) What is ALTER ? 

A) SQL command used to change the definition of DB2 objects.

178) What is a DBRM, PLAN ?

A) DBRM: Data Base Request Module, has the SQL statements extracted from the host language program by the pre-compiler. PLAN: A result of the BIND process. It has the executable code for the SQL statements in the

DBRM.

179) What is ACQUIRE/RELEASE in BIND?

A) Determine the point at which DB2 acquires or releases locks against table and tablespaces, including intent locks.

180) What else is there in the PLAN apart from the access path? 

A) PLAN has the executable code for the SQL statements in the host program