IBM Assessment: DB2 9.7 Advanced DBA for LUW Sample Questions:

1. Which statement about compression and temporary tables is NOT correct?

A) The explain facility and the db2pd utility can be used to determine whether a temporary table has been compressed.
B) Compression is only applied to created temporary tables that were defined with the CREATE TEMPORARY TABLE ?COMPRESS YES statement.
C) The minimum size that a table must be before compression is used is larger for temporary tables than for regular tables.
D) When executing queries that require temporary tables, DB2 considers the storage savings and the impact to query performance to determine whether compression is worthwhile.

2. 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) 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 enabled for compression because the associated table is compressed; however, the index will not be compressed until it is REORGed.
D) 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.

3. What would indicate intra-partition parallelism in an Optimizer Plan?

A) DTQ
B) BTQ
C) IP
D) LTQ

4. 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 the database?

A) ALTER DATABASE mydb ADD STORAGE ON ?mnt/path_1? ?mnt/path_2?
B) ALTER DATABASE mydb AUTOMATIC STORAGE YES ON ?mnt/path_1? ?mnt/path_2?
C) ALTER DATABASE mydb ADD STORAGE ON ?mnt/path_1? ?mnt/path_2? ALTER TABLESPACE tbsp1 MANAGED BY AUTOMATIC STORAGE;
D) ALTER DATABASE mydb AUTOMATIC STORAGE YES ON ?mnt/path_1? ?mnt/path_2? ALTER TABLESPACE tbsp1 MANAGED BY AUTOMATIC STORAGE;

5. If you know that the table space containers for your database reside on RAID-5 3+p arrays, which registry variable should be assigned the value *:3 to help improve performance?

A) DB2_PARALLEL_IO
B) DB2_RESOURCE_POLICY
C) DB2_EXTENDED_IO_FEATURES
D) DB2_APM_PERFORMANCE

Solutions: