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

• Question 31:

  Which statement about compression dictionaries is FALSE?

  A. Page-level dictionaries are stored in hidden rows within the table.

  B. Table-level dictionaries are stored in hidden rows within the table.

  C. Page-level dictionaries are automatically created or recreated by the database manager as pages are filled.

  D. Table-level dictionaries are automatically created or recreated as by the database manager as pages are filled.

  Correct Answer: D

• Question 32:

  A query against the system catalog revealed that data for a table named SALES in a partitioned database is distributed as follows:

  

  What can be done to improve the performance of a query that joins SALES and DATE_DIM tables (where SALES is a fact table and DATE_DIM is a dimension table) using the DATE_ID column?

  A. Reload table SALES and use only one partition with a collocated join to the DATE_DIM table using the primary key DATE_ID

  B. Reload table DATE_DIM and use only one partition with a collocated join to the SALES table using the primary key DATE_ID

  C. Create a replicated table REPL_SALES from the SALES table, refresh REPL_SALES table, create index on DATE_ID column in the REPL_SALES table, and collect statistics on REPL_SALES table.

  D. Create a replicated table REPL_DATE_DIM from the DATE_DIM table, refresh REPL_DATE_DIM table, create index on DATE_ID column in the REPL_DATE_DIM table, and collect statistics on REPL_DATE_DIM table.

  Correct Answer: D

• Question 33:

  A storage group named MED_SG was created as follows:

  CREATE STOGROUP med_sg ON db2fs1 db2fs2 '/db2fs3' Then, a table space named TS1 was created in the storage group and two tables (TAB_A and TAB_B) were created in the TS1 table space. Four months later, a DBA discovered that 90% of the space that had been allocated to the MED_SG storage group had been consumed. So, more space was added to the storage group by executing the following command: ALTER STOGROUP med_sg ADD db2fs4 ALTER STOGROUP med_sg ADD db2fs4 Shortly afterwards, queries against tables in the TS1 table space began to run slower. Which statement should be executed to correct the performance problem?

  A. ALTER TABLESPACE ts1 REBALANCE

  B. ALTER STOGROUP med_sg ADD db2fs5ALTER STOGROUP med_sg ADD db2fs5

  C. ALTER STOGROUP med_sg DROP '/db2fs4'

  D. ALTER TABLESPACE ts1 ADD '/db2fs4' REBALANCE

  Correct Answer: A

• Question 34:

  A database named TEST was created and two buffer pools named BP_GENERAL and BP_AUDITING were created as follows:

  

  The majority of the database tables were created in table space TS1, while one large table was created in table space TS2. A set of triggers thatThe majority of the database tables were created in table space TS1, while one large table was created in table space TS2. A set of triggers that would populate the table in table space TS2 each time changes were made to the database were then defined and the database was put into production. At first, ad-hoc queries performing random I/O against the table in table space TS2 ran fine, but performance has gotten worse as the table has gotten larger. What should be done to improve query performance?

  A. Reduce the size of the BP_AUDITING buffer pool.

  B. Increase the size of the BP_AUDITING buffer pool.

  C. Change the page size of the BP_AUDITING buffer pool to 16 or 32 K.

  D. Replace the BP_AUDITING buffer pool with a block-based buffer pool.

  Correct Answer: A

• Question 35:

  If a large amount of sequential prefetching is occurring, what can be done to improve efficiency?

  A. Set SEQDETECT to NO.

  B. Set NUMBLOCKPAGES to 0 in all buffer pools.

  C. Allocate a block-based area in the appropriate buffer pool.

  D. Create indexes that encourage the optimizer to use index-only access.

  Correct Answer: C

• Question 36:

  Given the following output:

  

  Which Explain procedure can be used to find the most expensive SQL statement in terms of CPU cost over a period of time?

  A. EXPLAIN_FROM_DATA

  B. EXPLAIN_FROM_ACTIVITY

  C. EXPLAIN_FROM_SECTION

  D. EXPLAIN_FROM_CATALOG

  Correct Answer: B

• Question 37:

  What statement regarding Asynchronous index cleanup (AIC) is correct?

  A. AIC removes all invalid index entries that refer to the attached partitions.

  B. AIC is initiated if a range-partitioned table contains one or more nonpartitioned indexes.

  C. AIC activates a newly attached partition after the index entries have been synchronized.

  D. AIC will not be initiated if a range-partitioned table has dependent materialized query tables (MQTs).

  Correct Answer: B

• Question 38:

  Which command(s) should be used to drop database partition 10 from a database named MYDB?

  A. STOP DBM DROP DBPARTITIONNUM 10; DROP DBPARTITIONNUM VERIFY;

  B. DROP DBPARTITIONNUM VERIFY; STOP DBM DROP DBPARTITIONNUM 10;

  C. CONNECT TO mydb; DROP DBPARTITIONNUM 10; CONNECT RESET;

  D. CONNECT TO mydb; DROP DBPARTITIONNUM 10; ACTIVATE DB mydb;

  Correct Answer: B

• Question 39:

  What must be done to convert a populated base table to a multidimensional clustering (MDC) table?

  A. Export the data, create an MDC table, and then import or load the data into the MDC table.

  B. Execute the ALTER TABLE ... ORGANIZE BY DIMENSIONS statement to convert the table.

  C. Create an MDC table and then move the data in the base table to the MDC table with the db2move command.

  D. Export the data, execute the ALTER TABLE ... ORGANIZE BY DIMENSIONS statement to convert the table, and then import or load the data into the converted table.

  Correct Answer: A

• Question 40:

  A DBA wants to create a table named SALES that has the following characteristics:

  Effective data clustering and management of space utilization Rows in the table should be clustered based on when they are added to the table Which clause must be used with the CREATE TABLE statement to define this table?

  A. ORGANIZE BY DIMENSIONS

  B. ORGANIZE BY INSERT TIME

  C. ORGANIZE BY TIME PERIOD

  D. ORGANIZE BY KEY SEQUENCE

  Correct Answer: B