IBM IBM Certified Advanced Database Administrator A2090-544 Questions & Answers

• Question 91:

  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.

  Correct Answer: B

• Question 92:

  Which two operators would indicate inter-partition parallelism in an Optimizer Plan? (Choose two.)

  A. LTQ

  B. HSJOIN

  C. BTQ

  D. IP

  E. DTQ

  Correct Answer: CE

• Question 93:

  What steps, in the correct order, are performed by the SQL and XQuery compilers to generate executable code for non-federated queries?

  A. parse query; rewrite query; check semantics; pushdown predicates; execute access plan

  B. check semantics; parse query; rewrite query; build access plan

  C. parse query; check semantics; rewrite query; build access plan

  D. check semantics; parse query; build access plan; pushdown predicates; execute access plan

  Correct Answer: C

• Question 94:

  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

  Correct Answer: B

• Question 95:

  A journaling application inserts data with continually increasing sequential keys while a clean up process randomly deletes erroneous rows. What can be done to maximize insert performance?

  A. Issue a db2set DB2MAXFSCRSEARCH=1024 command.

  B. Issue an ALTER TABLESPACE EXTEND statement.

  C. Issue a db2set DB2SKIPDELETED=ON command.

  D. Issue an ALTER TABLE APPEND ON statement.

  Correct Answer: D

• Question 96:

  If the shared sort heap threshold parameter SHEAPTHRES_SHR is set to AUTOMATIC, what will happen?

  A. All sorts will be done in a temporary table space.

  B. The shared sort memory allocation will be controlled by DB2.

  C. No shared memory is allocated for sorting.

  D. All sorts will be done in shared memory.

  Correct Answer: B

• Question 97:

  Which statement about compression and temporary tables is NOT correct?

  A. When executing queries that require temporary tables, DB2 considers the storage savings and the impact to query performance to determine whether compression is worthwhile.

  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. The explain facility and the db2pd utility can be used to determine whether a temporary table has been compressed.

  Correct Answer: B

• Question 98:

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

  A. REORG

  B. LOAD

  C. INSPECT

  D. IMPORT

  Correct Answer: D

• Question 99:

  What determines the degree of inter-partition parallelism used?

  A. The number of database partitions and the definition of partition groups.

  B. The number of database connections and the definition of partitions groups.

  C. The value of the INTER_PARALLEL Database Manager configuration parameter.

  D. The value of the DFT_DEGREE database configuration parameter.

  Correct Answer: A

• Question 100:

  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

  Correct Answer: B