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

• Question 61:

  Which Range-Clustered Tables (RCT) statement is correct?

  A. Reverse scans on RCTs are not supported.

  B. Clustering indexes are compatible with RCTs.

  C. The Design Advisor can recommend the use of RCTs.

  D. The IMPORT utility can be used with the REPLACE option to populate RCTs.

  Correct Answer: A

• Question 62:

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

  Data for the first half of the year will reside in table space TBSP1; the index for this data will reside in table space TBSP21.

  Data for the second half of the year will reside in table space TBSP2; the index for this data will reside in table space TBSP22. Which clause must be used with the CREATE TABLE statement to define this table?

  A. DISTRIBUTE BY HASH

  B. PARTITION BY RANGE

  C. ORGANIZE BY DIMENSIONS

  D. ORGANIZE BY KEY SEQUENCE

  Correct Answer: B

• Question 63:

  What must be done to convert an existing table into an Insert-Time-Clustered (ITC) table?

  A. Call the ADMIN_MOVE_TABLE procedure.

  B. Call the ADMIN_REVALIDATE_DB_OBJECTS procedure.

  C. Issue a REORG TABLE statement with the ORGANIZE BY INSERT TIME option.

  D. Issue an ALTER TABLE statement with the ORGANIZE BY INSERT TIME option.

  Correct Answer: A

• Question 64:

  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 65:

  Which statement about partitioned indexes is FALSE?

  A. Both system- and user-generated indexes can be partitioned.

  B. Each index partition contains references only to data in its corresponding data partition.

  C. A partitioned index is made up of a set of index partitions, each of which contains the index entries for a single data partition

  D. When attaching new partitions, the SET INTEGRITY statement must be run before data in the newly attached partition will be reflected in the corresponding partition index.

  Correct Answer: D

• Question 66:

  What is a benefit of multi-temperature data storage?

  A. It helps reduce redundant I/O.

  B. It helps improve data compression ratios.

  C. It helps reduce storage costs for infrequently accessed data.

  D. It helps identify data access patterns to aid in storage planning.

  Correct Answer: C

• Question 67:

  A storage group named HOT_SG was created as follows:

  

  What does the DATA TAG 1 clause do?

  A. It indicates that the multiple storage paths referenced in the HOT_SG storage group definition exist in one physical location.

  B. It tells the DB2 Optimizer that database objects stored in the HOT_SG storage group are to be given preference over other objects when data access plans are generated.

  C. It indicates that the value 1 is to be assigned to data stored in the HOT_SG storage group, which Workload Manager (WLM) can then use to determine the processing priority to assign to database activities that interact with this data.

  D. It assigns a unique identifier to the instance of the HOT_SG storage group being defined; this identifier can then be used when moving table spaces to and from the storage group, when dropping the storage group, or when commenting on the storage group.

  Correct Answer: C

• Question 68:

  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.

  Correct Answer: C

• Question 69:

  A DBA wants to create a set of tables in a storage group named SG_1, but no table spaces have been assigned to this group. What kind of table spaces must the DBA assign to the storage group before the desired tables can be created?

  A. Automatic storage (AS) table spaces

  B. System managed space (SMS) table spaces

  C. Database managed space (DMS) table spaces that use FILE containers

  D. Database managed space (DMS) table spaces that use RAW containers

  Correct Answer: A

• Question 70:

  The following statement was used to create a table space named USER_TMP in a non-partitioned database:

  CREATE USER TEMPORARY TABLESPACE user_tmp MANAGED BY SYSTEM USING (mnt/fsystem4 mnt/fsystem5

  Which operation can be performed on the USER_TMP table space with an ALTER TABLESPACE statement?

  A. New storage containers can be added to it.

  B. A different buffer pool can be associated with it.

  C. A hard limit can be placed on the size to which its storage containers can grow.

  D. Its high water mark can be lowered and its size can be reduced to the high water mark.

  Correct Answer: B