February 19, 2008
Try your hand at these questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press. The questions are reprinted with permission. Check back each week for more, and try past questions here.
QUESTION 15
Given the following CREATE TABLE statement:
CREATE TABLE tab1
(c1 SMALLINT,
c2 CHAR(4))
Table TAB1 is accessed frequently by two applications; one application returns the data in column C1 in ascending order, the other application returns the data in column C1 in des-cending order. To improve performance, a database administrator executes the following commands:
CREATE INDEX indx1 ON tab1(c1 ASC);
CREATE INDEX indx2 ON tab1(c1 DESC);
Which of the following will occur?
A. Index INDX1 will be created and the first application will use it when retrieving data; the index INDX2 will be created and the second application will use it when retrieving data.
B. Index INDX1 will be created. When an attempt is made to create index INDX2, DB2 will alter index INDX1 to allow reverse scans and index INDX1 be used by both applications when retrieving data.
C. Index INDX1 will be created. When an attempt is made to create index INDX2, DB2 will alter index INDX2 to allow reverse scans and drop index INDX1; index INDX2 will be used by both applications when retrieving data.
D. Index INDX1 will be created and allow reverse scans by default. When an at-tempt is made to create index INDX2, DB2 will issue a duplicate index warn-ing message. Index INDX1 be used by both applications when retrieving data.
QUESTION 16
In an attempt to improve query performance, a database administrator created an index for a table named EMPLOYEE, which contains 500,000 records. Performance of ad-hoc queries ran against the EMPLOYEE table has improved, but performance of a batch application that runs at night has not improved. Which of the following operations should correct this problem?
A. REORGCHK
B. REORG
C. FLUSH PACKAGE CACHE
D. REBIND
Answers
QUESTION 15
The correct answer is D. Because the DB2 Optimizer generates an access plan each time a dynamic SQL statement is prepared for execution, applications using dynamic SQL may see performance improvements immediately after new statistical information has been produced. Unfortunately, that is not the case for applications that use static SQL. That’s because the DB2 Optimizer only generates access plans for static SQL statements when the package that contains those statement is bound to the database. Therefore, in order for existing packages to take advantage of new statistical information produced by the RUNSTATS utility, they must be rebound to the database so the DB2 Optimizer will evaluate the new information and formulate new access plans (which may or may not perform better that the original access plan used). The easiest way to rebind an existing package – provided the application source code used to produce the package has not changed – is by executing the REBIND command.
QUESTION 16
The correct answer is D. If you create two indexes on the same table, one specifying ASC and the other DESC, and if you do not specify the DISALLOW REVERSE SCANS option in the CREATE INDEX statement used, both indexes will default to ALLOW REVERSE SCANS. As a result, the latter index will not be created and DB2 will issue a duplicate index warning message. (The ALLOW REVERSE SCANS option allows the same index to be used in two different queries that require the data to be in ascending and descending order.)
Trackback Pings
TrackBack URL for this entry:
http://www.ibmdatabasemag.com/blog/main/archives/2008/02/post_4.html
« DB2 9 Certification Study Questions: DB2 diagnostics log file and data row compression | Main | DB2 Magazine is now IBM Database Magazine »
This is a public forum. CMP Media and its affiliates are not responsible for and do not control what is posted herein. CMP Media makes no warranties or guarantees concerning any advice dispensed by its staff members or readers.
Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Media's Terms of Service.
Important Note: This comment area is NOT intended for commercial messages or solicitations of business.