A2090-544 Online Practice Questions and Answers

Your UNIX system administrator has indicated that there appears to be a lot of I/O wait time associated

with your database, which is stored in a 7+p RAID array. An investigation reveals the following information:

DB2_PARALLEL_IO=*

Tablespace with ID 7 has a single container and is dedicated to the largest table. It has a page size of 16K,

an extent size of 32, and a prefetch size of 64.

Which setting would optimize I/O for the largest table?

A. DB2_PARALLEL_IO=7:64

B. DB2_PARALLEL_IO=7:7

C. DB2_PARALLEL_IO=7:16

D. DB2_PARALLEL_IO=7:32

Which command CANNOT be used to explicitly build a compression dictionary for a table?

A. REORG

B. LOAD

C. INSPECT

D. IMPORT

Which command will prevent prefetching during crash recovery?

A. db2set DB2_AVOID_PREFETCH=OFF

B. db2set DB2_AVOID_PREFETCH=ON

C. db2set DB2_RECOVERY_PREFETCHING=NO

D. db2set DB2_RECOVERY_PREFETCHING=YES

A table named TAB_A was created with the COMPRESS YES option specified and populated with 100,000 rows. If a DBA wants to create an index on table TAB_A, which statement is true?

A. In order for the index to be compressed, it must be enabled for compression by specifying the COMPRESS YES option with the CREATE INDEX statement that is used to create it.

B. The index will automatically be compressed since the table it is associated with is compressed; the ALTER INDEX command does not have to be executed.

C. In order for the index to be compressed, it must be enabled for compression by specifying the COMPRESS YES option in the CREATE INDEX statement used to create it; however, the index will not be compressed until the ALTER INDEX command is used to create an index compression dictionary.

D. The index will automatically be enabled for compression because the associated table is compressed; however, the index will not be compressed until it is REORGed.

What is the minimum authority required to execute the LOAD command?

A. DATAACCESS

B. SQLADM

C. ACCESSCTRL

D. SYSCTRL

Given a database created as follows:

If each table (T1 and T2) has 50,000 rows, which of the following commands will ensure that the temporary table created by the select statement will fit entirely in the buffer pool, assuming that there is a primary key/ foreign key relationship between column C1 in table T1, and column C1 in table T2?

A. CREATE BUFFERPOOL bp1 SIZE 20000 PAGESIZE 4k;ALTER TABLESPACE tempspace1 BUFFERPOOL bp1;

B. CREATE BUFFERPOOL bp1 size 10000 PAGESIZE 8k;

C. CREATE BUFFERPOOL bp1 size 5000 PAGESIZE 16k;ALTER TABLESPACE tempspace1 BUFFERPOOL bp1;

D. CREATE BUFFERPOOL bp1 size 25000 PAGESIZE 32k;

A DBA needs to create a federated database and configure access to join data from three Oracle instances and one DB2 database. Which objects are needed to establish the specified connections?

A. 1 Oracle server , 1 DB2 server , 3 Oracle wrappers , and nicknames for each database

B. 1 Oracle server , 1 DB2 server , 1 Oracle wrapper , 1 DB2 wrapper , and nicknames for each database

C. 3 Oracle servers , 1 DB2 server , 3 Oracle wrappers , 1 DB2 wrapper , and nicknames for each database

D. 3 Oracle servers , 1 DB2 server , 1 Oracle wrapper , 1 DB2 wrapper , and nicknames for each database

If LOGSECOND=-1 then which of the following statements is true?

A. You still use LOGPRIMARY and LOGFILSIZ configuration parameters to specify how many log files the database manager should keep in the active log path.

B. You must configure the OVERFLOWLOGPATH configuration parameter if your active log path is not on a raw device.

C. The database will reserve active log space for transactions that may need to roll back and write log records.

D. An infinite number of overflow log files will be used with archival logging.

Which option of the ALTER TABLE statement tells the DB2 optimizer to use an index scan on a table that might vary in size at run time from being empty to containing a large number of rows?

A. CARDINALITY VOLATILE

B. INDEXSCAN

C. VOLATILE

D. CURRENT CARDINALITY

How can you change an existing non-partitioned index for a partitioned table to a partitioned index?

A. Use the ALTER INDEX command.

B. Use the DROP INDEX and CREATE INDEX commands.

C. Use the REORG INDEX command.

D. Use the ALTER INDEX and REORG INDEX commands.