IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 21:

  Application A is designed to execute the following SQL statements within a single Unit of Work (UOW).

  UPDATE employee SET salary = salary * 1.1 WHERE empno='000010' UPDATE department SET deptname =

  'NEW dept' WHERE deptno='A00' Application B is designed to execute the following SQL statements within a

  single Unit of Work (UOW).

  UPDATE department SET deptname = 'OLD DEPT' WHERE deptno='A00' UPDATE employee SET salary =

  salary * 0.5 WHERE empno='000010'

  Application A and application B execute their first SQL statement at the same time. When application A and

  application B try to execute their second SQL statement, a deadlock occurs.

  What will happen?

  A. The database managerwill rollback the transaction in both applications.

  B. The database managerwill rollback the transaction in one of the two applications.

  C. Application B will successfully update the EMPLOYEE and DEPARTMENT tables; Application A will be placed in a lock wait state.

  D. Application A will successfully update the EMPLOYEE and DEPARTMENT tables; Application B will terminate when the lock timeout value is reached.

  Correct Answer: B

• Question 22:

  Which of the following best describes the lock protection provided by DB2 for the current row of a cursor?

  A. The cursor is only protected from updates and deletes by concurrent applications.

  B. The row is only protected from positioned updates and deletes that reference another cursor of the same application.

  C. The cursor is protected from positioned updates and deletes that reference another cursor of a different application.

  D. The row is protected from updates and deletes by the current application and from positioned updates and deletes that reference another cursor of the same application.

  Correct Answer: A

• Question 23:

  If application A issues the following SQL statement, which of the following statements about concurrency is true?

  SELECT deptno, deptname, mgrno FROM dept WHERE admrdept = 'A00' FOR READ ONLY WITH RS

  A. Rows accessed by application A can be seen by other applications.

  B. Rows accessed by application A cannot be seen by other applications.

  C. Application A can see uncommitted changes made by other applications.

  D. Results produced by re-execution of the statement by application A will not be affected by other applications.

  Correct Answer: B

• Question 24:

  Application A currently holds an exclusive lock on a single row and application B tries to access that row. If lock timeout is set to -1 and both applications are using isolation level RS, which of the following will occur?

  A. Applications A and B will cause a deadlock situation.

  B. Application B will read the previous version of the row.

  C. Application B will read the row and see uncommitted changes made by application A.

  D. Application B will be placed in a lock-wait state until applicationA releases its lock.

  Correct Answer: D

• Question 25:

  Which of the following resources can be referenced in the LOCK statement?

  A. Row

  B. Table

  C. Column D. Table space

  Correct Answer: B

• Question 26:

  If DB2 detects a deadlock between application A and application B (each waiting for a lock held by the other one), what action will DB2 take?

  A. Rollback the transaction in both applications.

  B. Rollback the transaction in one of the two applications.

  C. Decrease the lock timeout value for both applications, thereby causing a lock timeout situation.

  D. Send a message to let the administrator know of the situation and decide which application or applications should be terminated.

  Correct Answer: B

• Question 27:

  How does DB2 protect the integrity of indexes when data is updated?

  A. Locks are acquired on the data.

  B. Locks are acquired on index keys.

  C. Locks are acquired on index pages.

  D. Locks are acquired on index pointers.

  Correct Answer: A

• Question 28:

  If an application issues the same query more than once in the same Unit of Work, which isolation level will not permit this application to see additional rows inserted by other applications?

  A. Read Stability (RS)

  B. Repeatable Read (RR)

  C. Uncommitted Read (UR)

  D. Cursor Stability (CS)

  Correct Answer: B

• Question 29:

  60 Which of the following best describes how locks are used in DB2?

  A. To maintain control of updated rows for commit processing

  B. To ensure only committed changes are altered by another application

  C. To allow two applications to update the same row of data simultaneously

  D. To prevent multiple applications from accessing the same data simultaneously

  Correct Answer: B

• Question 30:

  Application A issues the following SQL statements within in a single transaction using the Uncommitted Read

  isolation level:

  SELECT * FROM employee WHERE deptno='A00';

  UPDATE employee SET salary = salary * 1.1 WHERE deptno='A00';

  As long as the transaction has not been committed, which of the following is true for all other applications not

  running under the Uncommitted Read isolation level?

  A. They can read and make changes to data for any employee in department A00.

  B. They can read but not make changes to data for any employee in department A00.

  C. They must wait until the transaction is committed before accessing data of any employee in department A00.

  D. They must wait until application A disconnects before accessing data of any employee in department A00.

  Correct Answer: C