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

Question 1

At which two levels can events be audited? (Choose two.)

A. Server level

B. Node level

C. Instance level

D. Operating System level

E. Database level

Correct Answer: C,E

Question 2

A database and a table space were created using the following commands:

Afterwards, two tables were created in table space TBSP1 and both tables were populated with 100,000 rows. If a DBA decides that database MYDB should use automatic storage, which command(s) are needed to convert both the database and table space TBSP1?

A. ALTER DATABASE mydb AUTOMATIC STORAGE YES ON ?mnt/path_1? ?mnt/path_2?

B. ALTER DATABASE mydb AUTOMATIC STORAGE YES ON ?mnt/path_1? ?mnt/path_2? ALTER TABLESPACE tbsp1 MANAGED BY AUTOMATIC STORAGE;

C. ALTER DATABASE mydb ADD STORAGE ON ?mnt/path_1? ?mnt/path_2?

D. ALTER DATABASE mydb ADD STORAGE ON ?mnt/path_1? ?mnt/path_2? ALTER TABLESPACE tbsp1 MANAGED BY AUTOMATIC STORAGE;

Correct Answer: D

Question 3

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

B. VOLATILE

C. CURRENT CARDINALITY

D. CARDINALITY VOLATILE

Correct Answer: B

Question 4

Setting the DB2_FMP_COMM_HEAPSZ=0 will result in which the following?

A. User-defined function calls will be disabled.

B. In a partitioned environment, communications will not be buffered.

C. Non-fenced routines can be invoked, however automatic database maintenance will be disabled.

D. In a partitioned environment, infinite communications will be enabled between partitions.

Correct Answer: C

Question 5

If table space TBSP1 is using automatic storage, and the existing storage paths are full, which command would increase the available storage?

A. ALTER DATABASE ADD STORAGE ON '/db2/filesystem1'

B. ALTER DATABASE ADD STORAGE FOR tbsp1 ('/db2/filesystem1')

C. ALTER TABLESPACE tbsp1 ADD ('/db2/filesystem1')

D. ALTER TABLESPACE tbsp1 EXTEND USING ('/db2/filesystem1')

Correct Answer: A

Question 6

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

Question 7

What is the minimum authority required to execute the RUNSTATS command against a table?

A. SECADM

B. SYSMON

C. SQLADM

D. DATAACCESS

Correct Answer: C

Question 8

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

Welcome to TestSimulate

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