IBM IBM Certified Advanced Database Administrator C2090-614 Questions & Answers

• Question 21:

  If the following commands complete successfully for a single partition database: CREATE DATABASE mydb;

  CREATE BUFFERPOOL mybp; Which statement is true?

  A. Self tuning is enabled for the MYBP buffer pool.

  B. The initial size for the MYBP buffer pool is zero pages.

  C. The database has to be restarted before the MYBP buffer pool will become active.

  D. The page size for the MYBP buffer pool can be changed using the ALTER BUFFERPOOL statement.

  Correct Answer: A

• Question 22:

  When defining storage groups for multi-temperature data, storage paths are usually grouped according to their quality of service characteristics. What are three common quality of service characteristics used?

  A. Frequency of access; acceptable access time; volatility of the data.

  B. Acceptable access time; redundancy of the data; volatility of the data.

  C. TIR (table/index data ratio); frequency of access; volatility of the data;

  D. Frequency of access; redundancy of the data; acceptable access time;

  Correct Answer: A

• Question 23:

  A database named TEST and a storage group named SG_1 were created with these statements:

  

  an error was produced.Why did the error occur?

  A. The current default storage group cannot be dropped.

  B. New storage paths cannot be added to existing storage groups.

  C. The storage group IBMSTOGROUP cannot be dropped because it is a system-defined storage group.

  D. The storage group SG_1 was not the current default storage group at the time a new storage path was being added to it.

  Correct Answer: A

• Question 24:

  The following query is executed frequently against the SALES table:

  SELECT sales_id, sales_date, sales_person FROM sales;

  If data in the SALES_ID column is unique,

  Which statement will create an index for optimal query performance and ensure uniqueness of the SALES_ID column?

  A. CREATE INDEX sales_idx ON sales (sales_id, sales_date, sales_person)

  B. CREATE UNIQUE INDEX sales_idx ON sales (sales_id, sales_date, sales_person)

  C. CREATE INDEX sales_idx ON sales (sales_id) INCLUDE (sales_date, sales_person)

  D. CREATE UNIQUE INDEX sales_idx ON sales (sales_id) INCLUDE (sales_date, sales_person)

  Correct Answer: D

• Question 25:

  Which item is NOT eligible for adaptive compression?

  A. Indexes

  B. Base tables

  C. Temporary tables

  D. Inline LOB values

  Correct Answer: C

• Question 26:

  Which statement about block indexes is correct?

  A. Because of their large size, block indexes are extremely efficient.

  B. Block indexes improve performance when data is not clustered on sequential pages.

  C. Prefetching of data pages relies on sequential detection when block indexes are used.

  D. The records for each block are retrieved using a mini-relational scan of saved data pages.

  Correct Answer: C

• Question 27:

  Which is NOT a valid method for prefetching data?

  A. Smart data prefetching

  B. Read-ahead prefetching

  C. Consecutive prefetching

  D. Sequential detection prefetching

  Correct Answer: C

• Question 28:

  The following query is executed frequently against the EMPLOYEE table:

  SELECT empid, salary, bonus FROM employee ORDER BY bonus, salary; Which statement will create an index for optimal query performance?

  A. CREATE INDEX salary_bonus_idx ON employee (salary, bonus)

  B. CREATE INDEX bonus_salary_idx ON employee (bonus, salary)

  C. CREATE INDEX bonus_salary_idx ON employee (bonus, salary) CLUSTER

  D. CREATE INDEX salary_bonus_idx ON employee (salary, bonus) CLUSTER

  Correct Answer: C

• Question 29:

  Which tool provides the simplest method for monitoring buffer pool hit ratios?

  A. The BP_HITRATIO administrative view.

  B. The BP_WRITE_IO administrative view.

  C. The MON_GET_BUFFERPOOL table function.

  D. The MON_GET_PAGE_ACCESS_INFO table function.

  Correct Answer: A

• Question 30:

  Which would not significantly benefit from having the RUNSTATS command run on it?

  A. Tables that have had little change.

  B. Tables where a new index has been created.

  C. Tables that have been reorganized (using REORG).

  D. Tables that have been populated with a LOAD operation.

  Correct Answer: A