IBM C2090-614 Online Practice Questions and Exam Preparation

IBM C2090-614 Online Questions & Answers

• Question 91:

  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.
  A. The current default storage group cannot be dropped.

• Question 92:

  What contributes to the efficient use of block-based buffer pools?

  A. The application does not use sequential prefetching.
  B. The application performs a lot of random I/O operations.
  C. The table space extent size is equal to the block size specified for the buffer pool.
  D. The table space prefetch size is equal to the block size specified for the buffer pool.
  C. The table space extent size is equal to the block size specified for the buffer pool.

• Question 93:

  Which statement about value compression is correct?

  A. To use value compression, you must have a license for the DB2 Storage Optimization Feature.
  B. Value compression uses a page-level dictionary-based compression algorithm to compress data based on repetition of values found in a single column.
  C. Value compression involves removing duplicate entries for a value, only storing one copy, and keeping track of the location of any references to the stored copy.
  D. When value compression is used, the byte count of compressed NULLs, zeros, and blanks might be larger than that of the uncompressed version of the same values.
  C. Value compression involves removing duplicate entries for a value, only storing one copy, and keeping track of the location of any references to the stored copy.

• Question 94:

  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
  B. ORGANIZE BY INSERT TIME

• Question 95:

  Which monitoring tool can be used to find out how many data pages have been read from the table space containers for regular, large, and temporary table spaces?

  A. The MON_DB_SUMMARY administrative view.
  B. The MON_BP_UTILIZATION administrative view.
  C. The MON_GET_ACTIVITY_DETAILS table function.
  D. The MON_GET_EXTENT_MOVEMENT_STATUS table function.
  B. The MON_BP_UTILIZATION administrative view.

• Question 96:

  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).
  B. AIC is initiated if a range-partitioned table contains one or more nonpartitioned indexes.

• Question 97:

  When will the existing data for a new partition be visible to applications querying a range-partitioned table?

  A. After the ALTER TABLE ... ADD statement is executed.
  B. After the ALTER TABLE ... ATTACH statement is executed.
  C. After the ALTER TABLE ... ADD and SET INTEGRITY statements are executed.
  D. After the ALTER TABLE ... ATTACH and SET INTEGRITY statements are executed.
  D. After the ALTER TABLE ... ATTACH and SET INTEGRITY statements are executed.

• Question 98:

  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
  C. CREATE INDEX bonus_salary_idx ON employee (bonus, salary) CLUSTER

• Question 99:

  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
  A. ALTER TABLESPACE ts1 REBALANCE

• Question 100:

  In which scenario should a DBA consider creating a multidimensional clustering (MDC) table instead of a regular base table?

  A. To improve access performance for tables in an OLTP database.
  B. To improve index performance in a data warehouse environment.
  C. To improve query performance in a data warehouse environment.
  D. To improve insert/delete performance for tables in an OLTP database.
  C. To improve query performance in a data warehouse environment.