Assessment: DB2 10.1 Advanced DBA for LUW (A2090-614) Free Practice Test

Question 1

The following message is frequently seen in the DB2 Diagnostics Log file (db2diag.log): MESSAGE : ADM5502W The escalation of "28581" locks on table "TEST.TABLE1" to lock intent "X" was successful. Which two database configuration parameter settings should be checked? (Choose two.)

A. MAXLOCKS

B. MAXAGENTS

C. MAXAPPLS

D. LOCKTIMEOUT

E. LOCKLIST

Correct Answer: A,E

Question 2

What is the FAILARCHPATH database configuration parameter used for?

A. To specify a path to which DB2 will attempt to store a copy of both active and archive log files to protect against disk failure and power outages.

B. To specify a path to which DB2 will attempt to store a copy of archive log files to protect against accidental deletion.

C. To specify a path that DB2 will use to look for archive log files that are needed for a rollforward recovery operation.

D. To specify a path to which DB2 will attempt to store archive log files if they cannot be written to the primary or secondary archive destination because of a media problem.

Correct Answer: D

Question 3

Consider the following statement:

Which indexes should be created for optimal query performance?

A. CREATE INDEX emp_idx ON employee (lastname, job, workdept) CLUSTER; CREATE INDEX dept_idx ON department (deptname);

B. CREATE INDEX emp_idx ON employee (workdept); CREATE INDEX dept_idx ON department (deptno);

C. CREATE INDEX emp_idx ON employee (workdept) CLUSTER; CREATE INDEX dept_idx ON department (deptname, deptno);

D. CREATE INDEX emp_idx ON employee (workdept, lastname, job); CREATE INDEX dept_idx ON department (deptname, deptno);

Correct Answer: D

Question 4

What advantage do block-based buffer pools offer compared to regular buffer pools?

A. They improve the performance of sequential prefetching by reading contiguous pages from disk and writing them into contiguous pages in a buffer pool.

B. They improve the performance of vectored reads by retrieving non-contiguous pages from disk, combining them into extent-sized blocks, and writing the blocks into contiguous pages in a buffer pool.

C. They improve the performance of random I/O by reading non-contiguous pages from disk and writing them into near-contiguous free space in a buffer pool.

D. They improve the performance of scattered writes by retrieving non-contiguous pages from a buffer pool, combining them into extent-sized blocks, and writing the blocks into contiguous pages on disk.

Correct Answer: A

Question 5

Which statement about partitioned indexes is FALSE?

A. Both system- and user-generated indexes can be partitioned.

B. Each index partition contains references only to data in its corresponding data partition.

C. A partitioned index is made up of a set of index partitions, each of which contains the index entries for a single data partition

D. When attaching new partitions, the SET INTEGRITY statement must be run before data in the newly attached partition will be reflected in the corresponding partition index.

Correct Answer: D

Question 6

A database named TEST was created and two buffer pools named BP_GENERAL and BP_AUDITING were created as follows:

IBM 000-614 : Practice Test
The majority of the database tables were created in table space TS1, while one large table was created in table space TS2. A set of triggers thatThe majority of the database tables were created in table space TS1, while one large table was created in table space TS2. A set of triggers that would populate the table in table space TS2 each time changes were made to the database were then defined and the database was put into production.
At first, ad-hoc queries performing random I/O against the table in table space TS2 ran fine, but performance has gotten worse as the table has gotten larger. What should be done to improve query performance?

A. Reduce the size of the BP_AUDITING buffer pool.

B. Increase the size of the BP_AUDITING buffer pool.

C. Replace the BP_AUDITING buffer pool with a block-based buffer pool.

D. Change the page size of the BP_AUDITING buffer pool to 16 or 32 K.

Correct Answer: A

Question 7

You need to restore a table space that was backed up earlier, but before you begin a restore operation you want to determine whether any tables in the table space were
populated with a LOAD ... NONRECOVERABLE operation. What can be used to provide this information?

A. The INSPECT utility

B. The database recovery history file

C. The db2dart command

D. The db2pd command

Correct Answer: B

Question 8

The following query is executed frequently against the EMPLOYEE table: SELECT empid, salary, bonus FROM employee ORDER BY bonus, salary; Which statement will create an index for optimal query performance?

A. CREATE INDEX bonus_salary_idx ON employee (bonus, salary)

B. CREATE INDEX bonus_salary_idx ON employee (bonus, salary) CLUSTER

C. CREATE INDEX salary_bonus_idx ON employee (salary, bonus) CLUSTER

D. CREATE INDEX salary_bonus_idx ON employee (salary, bonus)

Correct Answer: B

Welcome to TestSimulate

IBM Assessment: DB2 10.1 Advanced DBA for LUW (A2090-614) Free Practice Test