March 12, 2008
Here are more questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press and reprinted with permission. To prepare for the DB2 9 Database Administration exam, try these questions. Check back each week for more. In the meantime, you can and try past questions here.
QUESTION 21
Which two of the following are valid indexes that are supported by DB2?
A. Spatial grid indexes
B. Static bitmap indexes
C. Dynamic bitmap indexes
D. Page-based indexes
E. Multidimensional range-clustered indexes
QUESTION 22
The table EMPLOYEES was created by executing the following command:
CREATE TABLE employees (empid INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(25))
Assuming the file EMPDATA.DEL contains the following data:
100, “Kim Moutsos”
100, “Dwaine Snow”
200, “Rebecca Bond”
300, “Phil Gunning”
400, “Paul Zikopoulos”
If the following commands are executed:
CREATE TABLE emp_exp LIKE EMPLOYEES;
LOAD FROM empdata.del OF DEL
INSERT INTO EMPLOYEES
FOR EXCEPTION emp_exp;
Which of the following statements is true?
A. The table EMPLOYEES will contain four rows; the table EMP_EXP will be empty.
B. The table EMPLOYEES will contain five rows; the table EMP_EXP will contain five rows.
C. The table EMPLOYEES will contain four rows; the table EMP_EXP will con-tain one row.
D. The table EMPLOYEES will contain five rows; the table EMP_EXP will contain one row.
Answers
QUESTION 21
The correct answers are A and C. With DB2 9, several types of indexes are available:
--Relational indexes. Indexes that are optimized for a single dimension.
--Spatial Grid indexes. Indexes that are optimized for two dimensional data. (Each spatial grid index is created on the X and Y dimensions of a geometry; used by the DB2 Spatial Extender.)
--Dynamic Bitmap indexes. Indexes that are produced by ANDing the results of multiple index scans using Dynamic Bitmap techniques. (ANDed predicates can be applied to multiple indexes to keep underlying table accesses to a minimum.)
--Block Based indexes. Indexes that contain pointers to rows in a single dimen-sion of a Multidimensional Clustering (MDC) table.
--XML indexes. User-defined indexes over XML data that use a particular XML pattern (which is a limited XPath expression) to index paths and values in XML documents stored within a single column.
QUESTION 22
The correct answer is C. During the delete phase of a load operation, any rows that violated primary and/or unique key constraints defined on the target table are removed and copied to an exception table (if appropriate) and a message about each offending row is written to the appropriate message file so it can be modified and manually moved to the target table at some point in the future. Since the data in this example contains two records that have an employee ID value of 100, and because the EMPID column in the EMPLOYEES is a primary key, the first record will be loaded into the EMPLOYEES table and the second will be moved to the EMP_EXP table.
Trackback Pings
TrackBack URL for this entry:
http://www.ibmdatabasemag.com/blog/main/archives/2008/03/post_1.html
« DB2 9 DBA Certification Exam: DB2 Discovery and index keys on XML content | Main | DB2 LUW Performance: Direct I/O Times »
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.