DB2 9.7 Advanced DBA for LUW (000-544) Free Practice Test

Question 1

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

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

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

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

Correct Answer: C

Question 2

Which conditions must be met to implement HADR?

A. Operating systems on primary and standby servers must be the same; DB2 versions need not be the same; and the databases must have the same bit-size (32 or 64 bit).

B. Operating systems on primary and standby servers must be the same; DB2 versions must be the same; and the databases must have the same bit-size (32 or 64 bit).

C. Operating systems on primary and standby servers need not be the same; DB2 versions must be the same; and the databases need not have the same bit-size (32 or 64 bit).

D. Operating systems on primary and standby servers must be the same; DB2 versions must be the same; and the databases need not have the same bit-size (32 or 64 bit).

Correct Answer: B

Question 3

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. The database will reserve active log space for transactions that may need to roll back and write log records.

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

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

Correct Answer: A

Question 4

When the DBA chooses the dimensions for a multi-dimensional clustering (MDC) table, which two characteristics should be considered? (Choose two.)

A. prefetch size

B. numeric data versus character data

C. the combined cardinality of the candidate columns

D. the query transaction rate

E. extent size

Correct Answer: C,E

Question 5

Table TAB1 was created using the following statement: CREATE TABLE tab1 (c1 INT, c2 INT, c3 INT, c4 INT, c5 INT); If column C1 is unique and queries typically access columns C1, C2, C3 and C4 together, which statement(s) will create index(es) that will provide optimal query performance?

A. CREATE UNIQUE INDEX xtab1 ON tab1 (c1) INCLUDE (c2, c3, c4);

B. CREATE UNIQUE INDEX xtab1 ON tab1 (c4, c3, c2, c1);

C. CREATE UNIQUE INDEX xtab1 ON tab1 (c3) INCLUDE (c1, c2, c4);

D. CREATE UNIQUE INDEX xtab1 ON tab1 (c1); CREATE INDEX xtab2 ON tab1 (c2) INCLUDE (c3, c4);

Correct Answer: A

Question 6

Which factor should influence your selection of distribution key(s)?

A. The distribution key(s) should include the most frequently joined column(s).

B. The distribution key(s) should include column(s) with the smallest number of distinct values.

C. The distribution key(s) should include the longest column(s).

D. The distribution key(s) should include the most frequently accessed XML column(s).

Correct Answer: A

Question 7

In which two environments would intra-partition parallelism be used? (Choose two.)

A. Multiple database partitions, single processor core

B. Single database partition, multiple processor cores

C. Multiple database partitions, multiple processor cores

D. Single database partition, single processor core

Correct Answer: B,C

Question 8

Click the Exhibit button.

Refer to the table definition shown in the exhibit.
Which will improve performance based on the query SELECT c6, c9 FROM tab3 WHERE c9 = 123456?

A. CREATE INDEX idx98 ON tab3 (c6 ASC); CREATE INDEX idx99 ON tab3 (c9 DESC); RUNSTATS ON TABLE myschema.tab3;

B. CREATE INDEX idx98 ON tab3 (c2 ASC, c6 ASC); CREATE INDEX idx99 ON tab3 (c2 ASC, c9 ASC); RUNSTATS ON TABLE myschema.tab3;

C. CREATE INDEX idx99 ON tab3 (c6 ASC, c9 DESC); REORG TABLE myschema.tab3 AND INDEXES ALL;

D. CREATE INDEX idx99 ON tab3 (c2 ASC, c9 DESC); REORG TABLE myschema.tab3 AND INDEXES ALL;

Correct Answer: A

Welcome to TestSimulate

IBM DB2 9.7 Advanced DBA for LUW (000-544) Free Practice Test