IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 41:

  Application A is running under the Repeatable Read isolation level and holds an Update lock on table TAB1. Application B wants to query table TAB1 and cannot wait for Application A to release its lock. Which isolation level should Application B run under to achieve this objective?

  A. Repeatable Read

  B. Read Stability

  C. Cursor Stability

  D. Uncommitted Read

  Correct Answer: D

• Question 42:

  Two applications have created a deadlock cycle in the locking subsystem. If lock timeout is set to 30 and both applications were started at the same time, what action will the deadlock detector take when it "wakes up" and discovers the deadlock?

  A. It will randomly pick an application and rollback its current transaction

  B. Itwill rollback the current transactions of both applications

  C. It will wait 30 seconds, then rollback the current transactions of both applications if the deadlock has not been resolved

  D. It will go back to sleep for 30 seconds, then if the deadlock still exists, it will randomly pick an application and rollback its current transaction

  Correct Answer: A

• Question 43:

  A transaction using the Read Stability isolation level scans the same table multiple times before it terminates. Which of the following can occur within this transaction's processing?

  A. Uncommitted changes made by other transactions can be seen from one scan to the next.

  B. Rows removed by other transactions that appeared in one scan will no longer appear in subsequent scans.

  C. Rows added by other transactions that did not appear in one scan can be seen in subsequent scans.

  D. Rows that have been updated can be changed by other transactions from one scan to the next.

  Correct Answer: C

• Question 44:

  Application A holds an Exclusive lock on table TAB1 and needs to acquire an Exclusive lock on table TAB2. Application B holds an Exclusive lock on table TAB2 and needs to acquire an Stability isolation level, which of the following will occur?

  A. Applications A and B will cause a deadlock situation

  B. Application B will read the copy of table TAB1 that was loaded into memory when Application A first read it

  C. Application B will read the data in table TAB1 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: A

• Question 45:

  Which of the following isolation levels will lock all rows scanned to build a result data set?

  A. Uncommitted Read

  B. Cursor Stability

  C. Read Stability D. Repeatable Read

  Correct Answer: D

• Question 46:

  Which of the following is NOT a characteristic of a declared temporary table?

  A. Declared temporary tables are not persistent and can only be used by the application that creates them

  B. It is possible for many applications to create declared temporary tables that have the same name

  C. Declared temporary tables are created by executing a CREATE TABLE statement with the DECLARED GLOBAL TEMPORARY clause specified

  D. Once the application that created a global temporary table is terminated, any records in the table are deleted and the table is automatically destroyed Data Concurrency

  Correct Answer: C

• Question 47:

  If the following SQL statement is executed:

  CREATE TABLE sales

  (invoice_no NOT NULL PRIMARY KEY,

  sales_date DATE,

  sales_amt NUMERIC(7,2))

  IN tbsp0, tbsp1, tbsp2, tbsp3

  PARTITION BY RANGE (sales_date NULLS FIRST)

  (STARTING '1/1/2007' ENDING '12/31/2007'

  EVERY 3 MONTHS)

  Which of the following statements is true?

  A. Administrative tasks such as backing up, restoring, and reorganizing data stored in the SALES table must be done at the table level; not at the partition level

  B. Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement

  C. If an index is created for the SALES table, its data must be stored in table space TBSP0

  D. When resolving queries against the SALES table, each partition used is scanned asynchronously and the results of each partition scan are merged to produce the result data set returned

  Correct Answer: B

• Question 48:

  Which of the following CREATE TABLE statements will NOT be successful?

  A. CREATE TABLE t1 (c1 XML NOT NULL UNIQUE, c2 INT)

  B. CREATE TABLE t1 (c1 XML NOT NULL, c2CHAR(20))

  C. CREATE TABLE t1 (c1 XML CHECK (c1 IS VALIDATED), c2 INT)

  D. CREATE TABLE t1 (c1 XML, c2 XML)

  Correct Answer: A

• Question 49:

  Which of the following is NOT a difference between a unique index and a primary key?

  A. A primary key is a special form of a unique constraint; both use a unique index.

  B. Unique indexes can be defined over one or more columns; primary keys can only be defined on a single column.

  C. A table can have many unique indexes but only one primary key.

  D. Unique indexes can be defined over one or more columns that allow null values; primary keys cannot contain null values.

  Correct Answer: B

• Question 50:

  Which of the following actions will NOT cause a trigger to be fired?

  A. INSERT

  B. DELETE

  C. ALTER

  D. UPDATE

  Correct Answer: C