Which of the following tools can make recommendations for indexes and/or MQTs to improve the performance of DB2 applications?

A. Design Advisor

B. Visual Explain

C. Performance Advisor

D. Configuration Assistant

A user utilizing an alias to update a subset of columns in a table must have UPDATE privileges on which of the following DB2 objects?

A. Table

B. Columns

C. Table and Alias

D. Columns and Alias

The following statements:

CREATE TABLE t1 (col1 INT NOT NULL, PRIMARY KEY (col1)); CREATE TABLE t2 (col1 INT NOT NULL,

col2 CHAR(1) NOT NULL, PRIMARY KEY (col1, col2), FOREIGN KEY (col1) REFERENCES t1 (col1)

ON DELETE CASCADE ON UPDATE RESTRICT);

CREATE TABLE t3 (col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (col1, col2), FOREIGN KEY

(col1) REFERENCES t1 (col1)

ON DELETE NO ACTION ON UPDATE RESTRICT);

INSERT INTO t1 VALUES (1), (2);

INSERT INTO t2 VALUES (1, 'a'), (1, 'b'), (2,'c');

INSERT INTO t3 VALUES (1, 100), (2, 200), (2,300);

How many rows will be deleted by the following DELETE statement? DELETE FROM t1 WHERE col1 = 1;

B. 3

C. 1

D. 0

57 Given table T1 has column I1 containing the following data: I1

If the following sequence of SQL statements is applied within a single unit of work:

UPDATE t1 SET i1 = 3 WHERE i1 = 2; S

AVEPOINT s1 ON ROLLBACK RETAIN CURSORS;

UPDATE t1 SET i1 = 5 WHERE i1 = 3;

SAVEPOINT s2 ON ROLLBACK RETAIN CURSORS;

INSERT INTO t1 (i1) VALUES (6);

ROLLBACK TO SAVEPOINT s1;

UPDATE t1 SET i1 = 2 WHERE i1 = 4;

COMMIT;

What is the expected sequence of values returned from?

SELECT i1 FROM t1 ORDER BY i1

A. 1, 2, 3, 3

B. 1, 2, 2, 4

C. 1, 2, 3, 3, 6

D. 1, 2, 2, 5, 6

Given the following table definitions:

TABLE1

ID INT NAME CHAR(30) PERSON INT CITIES INT TABLE2

ID INT

LASTNAME CHAR(30)

Which of the following statements will remove all rows in table TABLE1 that have matching PERSONs in table

TABLE2?

A. DELETE FROM table1 WHERE id IN (SELECT id FROM table2)

B. DELETE FROM table1 WHERE id IN (SELECT person FROM table2)

C. DELETE FROM table1 WHERE person IN (SELECT id FROM table2)

D. DELETE FROM table1 WHERE person IN (SELECT person FROM table2)

Given the following set of statements:

CREATE TABLE tab1 (col1 INTEGER, col2 CHAR(20));

COMMIT;

INSERT INTO tab1 VALUES (123, 'Red');

INSERT INTO tab1 VALUES (456, 'Yellow');

SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS;

DELETE FROM tab1 WHERE col1 = 123;

INSERT INTO tab1 VALUES (789, 'Blue');

ROLLBACK TO SAVEPOINT s1;

INSERT INTO tab1 VALUES (789, 'Green');

UPDATE tab1 SET col2 = NULL WHERE col1 = 789;

COMMIT;

Which of the following records would be returned by the following statement?

SELECT * FROM tab1

A. COL1 COL2

123 Red

456 Yellow

2 record(s) selected.

B. COL1 COL2

456 Yellow 1 record(s) selected.

C. COL1 COL2

123 Red

456 Yellow

3 record(s) selected.

D. COL1 COL2

123 Red

456 Yellow

789 Green

3 record(s) selected.

Which of the following DB2 data types can be used to store 1000 MB of single-byte character data?

A. BLOB

B. CLOB

C. DBCLOB

D. GRAPHIC

What is the maximum size that can be specified when creating an XML column in a DB2 table?

A. No size is specified

B. The buffer pool size

C. The page size for the table space

D. The extent size for the table space

Given the following scenario:

Table TABLE1 needs to hold specific numeric values up to 9999999.999 in column COL1. Once TABLE1 is

populated, arithmetic operations will be performed on data stored in column COL1.

Which of the following would be the most appropriate DB2 data type to use for column COL1?

A. INTEGER

B. REAL

C. NUMERIC(7, 3)

D. DECIMAL(10, 3)

The EMPLOYEE table contains the following information:

EMPNO NAME WORKDEPT

101 SAM A11 102 JOHN C12 103 JANE 104 PAT Remote 105 ANNE

106 BOB A11 The MANAGER table contains the following information: MGRID NAME DEPTNO EMPCOUNT 1 WU B01

2 JONES A11 3 CHEN - 4 SMITH - 5 THOMAS C12

After this statement is executed:

UPDATE manager m SET empcount = (SELECT COUNT(workdept) FROM employee e WHERE

workdept=m.deptno)

What is the result of the following query?

SELECT mgrid, empcount FROM MANAGER WHERE empcount IS NOT NULL ORDER BY mgrid

A. MGRID EMPCOUNT ----- -------- 1 0 22 5 1

B. MGRID EMPCOUNT ----- -------- 1 0 22 3 0 4 0 5 1

C. MGRID EMPCOUNT ----- -------- 1 3 2 33 3 4 3 5 3

D. MGRID EMPCOUNT ----- -------- 1 0 22 3 2 4 2 5 1