List of DB2 V11 Interview Questions on isolation levels:
1) What is default isolation level?
A) If we do not mention isolation level. DB2 consider it as RR. The package Isolation level overrides other isolation level like Result table isolation level and PLAN BIND isolation level.
2) What is the best way to specify the result table is READ ONLY?
A) Use FOR READ ONLY in select statement of Cursor declaration
3) Package Isolation level is “CS”, then will that apply to all the SQL statements under this package?
A) Yes, it applies to all SQL statements of this package
4) What are the different locks you can give SQL declaration of cursor?
A)USE AND KEEP EXCLUSIVE LOCKS
USE AND KEEP UPDATE LOCKS
USE AND KEEP SHARE LOCKS
Here’s more on DB2-isolation-level-in-programs.
5) Best ways to choose Isolation level?
A) Bind most applications with the ISOLATION(CS) and CURRENTDATA(NO) options. These options enable DB2 to release locks early and avoid taking locks in many cases. ISOLATION(CS) typically enables DB2 to release acquired locks as soon as possible.
The CURRENTDATA(NO) typically enables DB2 to acquire the fewest number of locks, for better lock avoidance. When you use ISOLATION(CS) and CURRENTDATA(NO), consider using the SKIPUNCI subsystem parameter value to YES so that readers do not wait for the outcome of uncommitted inserts.
6) What is the default order of ISOLATION levels?
A)Cursor stability (CS)
Uncommitted read (UR)
Read stability (RS)
Repeatable read (RR)
7) What is ISOLATION Level CS?
A) The ISOLATION (CS) or cursor stability option allows maximum concurrency with data integrity. Under the ISOLATION (CS) option, a transaction holds locks only on its uncommitted changes and on the current row of each of its cursors.
8) What is ISOLATION Level UR?
A) The ISOLATION (UR) or uncommitted read option allows an application to read while acquiring few locks, at the risk of reading uncommitted data. UR isolation applies only to the following read-only operations: SELECT, SELECT INTO, or FETCH from a read-only result table.
9) What is ISOLATION Level RS?
A) The ISOLATION (RS) or read stability option enables an application to read the same pages or rows more than once and prevents updates or deletes to qualifying rows by other processes. However, other applications can insert or update rows that did not satisfy the search condition of the original application.
10) What is ISOLATION Level RR?
A) The ISOLATION (RR) or repeatable read option allows the application to read the same pages or rows more than once without allowing any update, insert, or delete operations by other processes. All accessed rows or pages are locked, even if they do not satisfy the predicate. Under the ISOLATION (RR) option, the data that an application references cannot be updated by any other applications before the application reaches a commit point.
Related Posts
Srinimf 