11.10. Review Questions
| 1: | A batch operation is encountering lock escalations. If it is the only application running when the lock escalation occurs, which database configuration parameter can be used to reduce the lock escalations? |
| 2: | Sam issues a SELECT statement that returns the following result set of three rows:
Name Seat ---------------- Liu 1A Chong 14F Snow 3B Without committing or rolling back the current transaction, he issues the same SELECT statement again. The following is returned: Name Seat ---------------- - 1A Chong 14F Qi 3B Why is that? |
| 3: | What database objects can a DB2 user explicitly lock using a DB2 command or statement? |
| 4: | If an application holds a U lock on a row, what lock must another application request to access this row concurrently? |
| 5: | What does error SQL0911N with reason code 68 mean? |
| 6: | What does error SQL0911N with reason code 2 mean? |
| 7: | A user complained about poor performance. With the DB2 Snapshot Monitor you obtained the following information:
Locks held currently = 855 Lock waits = 1123 Time database waited on locks (ms) = 3157040000 Lock list memory in use (Bytes) = 16920 Deadlocks detected = 0 Lock escalations = 103 Exclusive lock escalations = 0 Agents currently waiting on locks = 38 Lock Timeouts = 2232 How would you troubleshoot the high number of lock escalations? |
| 8: | What tools that come with DB2 can assist you in diagnosing lock problems? |
| 9: | The following is captured by the Snapshot Monitor. What does it tell you?
Application Snapshot Application handle = 14 Application status = Lock-wait Status change time = 08-15-2004 14:30:36.907312 Snapshot timestamp = 08-15-2004 14:30:43.414574 Time application waited on locks (ms) = 6507 Total time UOW waited on locks (ms) = 6507 UOW start timestamp = 08-15-2004 14:30:36.889356 Statement start timestamp = 08-15-2004 14:30:36.890986 Dynamic SQL statement text: select * from org ID of agent holding lock = 13 Application ID holding lock = *LOCAL.DB2.011905182946 Lock name = 0x02000200000000000000000054 Lock attributes = 0x00000000 Release flags = 0x00000001 Lock object type = Table Lock mode = Exclusive Lock (X) Lock mode requested = Intention Share Lock (IS) Name of tablespace holding lock = USERSPACE1 Schema of table holding lock = WILKINS Name of table holding lock = ORG Lock wait start timestamp = 08-15-2004 14:30:36.907318 |
| 10: | Bob was connected to the sample database. He turned auto-commit OFF and issued the following statement:
UPDATE employee SET salary = salary * 1.5 WHERE empno='000010'
A database administrator, Mike, who had just joined the company was monitoring the system. He noticed that Bob had acquired a table lock on the employee table. Since Bob did not commit or roll back the transaction, no one can access the table (except for UR applications). Mike asked Bob to commit or roll back the transaction. That released the locks and business went on as usual. Then another user, Mary, issued the following statement: SELECT name, salary FROM employee WHERE empno = '000020'
Mary also had auto-commit turned OFF and didn't commit or rollback the transaction. Once again, the employee table was locked. Mike is concerned about these two locking incidents, Could you assist him with what might be the cause? |
| 11: | Which of the following is not a DB2 isolation level?
|
| 12: | On which of the following objects does DB2 not obtain locks?
|
| 13: | Which of the following is the default isolation level in DB2?
|
| 14: | Which of the following isolation levels typically causes the most locks to be obtained?
|
| 15: | Which of the following isolation levels does not obtain row level locks?
|
| 16: | Which of the following isolation levels lets you see data that has been updated by other applications before it is committed?
|
| 17: | Given a transaction that issues the same SQL statement twice. Which of the following isolation levels will allow new rows to be returned in the result set, but will not allow rows to be removed from the result set?
|
| 18: | If the current session has an isolation level of CS, which of the following will change the isolation level to UR for the current statement?
|
| 19: | Using the alter table statement, which two of the following can you change the locksize to?
|
| 20: | To specify that your application should return immediately rather than wait for a lock, which of the following commands must be used?
|