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

• Question 81:

  Given a system-period temporal table POLICY_INFO with its associated history table HIST_POLICY_INFO, which statement is true?

  A. When querying with a time period specification, only the HIST_POLICY_INFO table is queried.

  B. When querying with no time period specification, only the HIST_POLICY_INFO table is queried.

  C. When querying with a time period specification, both the POLICY_INFO and HIST_POLICY_INFO tables are potentially queried.

  D. When querying with no time period specification, both the POLICY_INFO and HIST_POLICY_INFO tables are potentially queried.

  Correct Answer: C

• Question 82:

  Which statement about the ADMIN_MOVE_TABLE procedure is true?

  A. The ADMIN_MOVE_TABLE procedure immediately applies all changes that occur to data in the active (source) table during the move operationto the target table.

  B. The ADMIN_MOVE_TABLE procedure stores all changes that occur to data in the active (source) table during the move to a user-suppliedstaging table so they can be replayed against the target table when the move is complete.

  C. If the ADMIN_MOVE_TABLE procedure is used to move data in an active (source) table to a target table that was created beforehand, thetarget table is renamed (to the source table name) once the data has been copied into the target table.

  D. If the ADMIN_MOVE_TABLE procedure is invoked with the KEEP option specified, the active (source) table will be kept and if the target table isto be created automatically, a name that is different from that of the active table must be specified for the target table.

  Correct Answer: C

• Question 83:

  Table TAB1 was created as follows:

  

  Which statement(s) should be used to create an index on column COL1 of table TAB1 that is enabled for compression?

  A. CREATE INDEX tab1_idx ON tab1(col1);

  B. CREATE INDEX tab1_idx ON tab1(col1);ALTER INDEX tab1_idx COMPRESS YES;

  C. CREATE INDEX tab1_idx ON tab1(col1);REORG INDEX tab1_idx FOR TABLE tab1;

  D. CREATE INDEX tab1_idx ON tab1(col1);ALTER INDEX tab1_idx COMPRESS YES;REORG INDEX tab1_idx FOR TABLE tab1;

  Correct Answer: A

• Question 84:

  If an MQT named Q1_SALES was created as follows: Which statement will execute successfully?

  

  A. DELETE FROM q1_sales

  B. REFRESH TABLE q1_sales

  C. INSERT INTO q1_sales VALUES(80.2)

  D. ALTER TABLE q1_sales REFRESH IMMEDIATE

  Correct Answer: B

• Question 85:

  You have just converted an existing database to an automatic storage database. What can you use to move data from an existing DMS table space to an automatic storage table space, while the data remains online and available for access?

  A. The db2move command

  B. The db2relocatedb command

  C. The ADMIN_MOVE_TABLE procedure

  D. The ADMIN_MOVE_TABLE_UTIL procedure

  Correct Answer: C

• Question 86:

  Which statement about range-clustered tables is true?

  A. Range-clustered tables can be used with the REORG utility.

  B. Range-clustered tables can be used with multiple-column keys.

  C. Range-clustered tables can be used in a DB2 pureScale environment.

  D. Range-clustered tables can be used with value and default compression.

  Correct Answer: B

• Question 87:

  Which tool can be used to mimic statistics for tables by gathering statistics from a production database and placing them into a test database?

  A. db2pd

  B. db2dart

  C. db2look

  D. db2mimic

  Correct Answer: C

• Question 88:

  If the following statement is executed:

  

  What indexes will be created for table TAB1?

  A. 1 dimension-block index and 1 composite-block index

  B. 1 dimension-block index and 3 composite-block indexes

  C. 3 dimension-block indexes and 1 composite-block index

  D. 3 dimension-block indexes and 3 composite-block indexes

  Correct Answer: C

• Question 89:

  Which statements will enable automatic storage in storage group SG_HOT for a given database managed table space MY_TS and remove all existing non-automatic storage containers from the table space?

  A. ALTER TABLESPACE my_ts MANAGED BY AUTOMATIC STORAGE;ALTER TABLESPACE my_ts REBALANCE;

  B. ALTER TABLEPACE my_ts MANAGED BY DATABASE;ALTER TABLESPACE my_ts MANAGED BY AUTOMATIC STORAGE;ALTER TABLESPACE my_ts REBALANCE;

  C. ALTER TABLESPACE my_ts MANAGED BY AUTOMATIC STORAGE USE STOGROUP sg_hot;ALTER STOGROUP sg_hot DROP '/db2/filesystem1', '/db2/filesystem2';ALTER TABLESPACE my_ts REBALANCE;

  D. ALTER TABLESPACE my_ts MANAGED BY DATABASE USE STOGROUP sg_hot;ALTER STOGROUP sg_hot DROP '/db2/filesystem1', '/db2/filesystem2';ALTER TABLESPACE my_ts REBALANCE;

  Correct Answer: A

• Question 90:

  What can be used to provide input to the Design Advisor?

  A. An XML file.

  B. A query workload file.

  C. The database configuration file.

  D. The database manager configuration file.

  Correct Answer: B