C2090-614 Online Practice Questions and Answers

The following query is executed frequently against the SALES table:

SELECT sales_person, region FROM sales WHERE region = 'Quebec' Which statement will create an index for optimal query performance?

A. CREATE INDEX sales_idx ON sales (region)

B. CREATE INDEX sales_idx ON sales (sales_person)

C. CREATE INDEX sales_idx ON sales (region, sales_person)

D. CREATE INDEX sales_idx ON sales (region) INCLUDE (sales_person)

A DB2 9 database named MYDB that does not use automatic storage was migrated to DB2 10.1. When will a default storage group be defined for this database?

A. The first time the database is accessed after the migration.

B. The first time the CREATE STOGROUP statement is executed.

C. When the ALTER DATABASE mydb CONVERT statement is used to convert the database to an automatic storage database.

D. When the ALTER TABLESPACE statement is used to convert the table space that holds the system catalog for the database to automatic storage.

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.

Which statement about Q Replication is correct?

A. It uses continuous log shipping to replicate from the source database to the target database.

B. It uses HADR message queues to transmit transactions between source and target databases.

C. It uses WebSphere MQ message queues to transmit transactions between source and target databases.

D. It uses replication control tables and changed data tables to replicate from the source database to the target database.

Which is a poor choice for a clustering dimension of a multidimensional clustering (MDC) table?

A. Columns with low cardinalities

B. Columns with high cardinalities

C. Columns involved in roll-in or roll-out of data

D. Columns involved in equality or range predicate queries

Which statement about Range-Clustered Tables (RCTs) is correct?

A. More logging is required compared to similarly-sized regular tables.

B. More buffer pool memory is required to store the table's B+ index structure.

C. Space is pre-allocated and reserved for use by the table even when records do not yet exist.

D. More maintenance is required since indexes must be updated after every insert, update, or delete operation.

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).

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.

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.

What can be done to prevent applications from failing when DB2 cannot create a new log file?

A. Set MAX_LOG to 0

B. Set LOGBUFSZ to -1

C. Set LOGSECOND to -1

D. Set BLK_LOG_DSK_FUL to YES