IBM DB2 9 Family Fundamentals Sample Questions:

1. Application A is designed to execute the following SQL statements within a single Unit of Work
(UOW).
UPDATE employee SET salary = salary * 1.1 WHERE empno='000010'
UPDATE department SET deptname = 'NEW dept' WHERE deptno='A00'
Application B is designed to execute the following SQL statements within a single Unit of Work
(UOW).
UPDATE department SET deptname = 'OLD DEPT' WHERE deptno='A00'
UPDATE employee SET salary = salary * 0.5 WHERE empno='000010'
Application A and application B execute their first SQL statement at the same time. When
application A and application B try to execute their second SQL statement, a deadlock occurs.
What will happen?

A) The database managerwill rollback the transaction in one of the two applications.
B) Application B will successfully update the EMPLOYEE and DEPARTMENT tables; Application A will be placed in a lock wait state.
C) Application A will successfully update the EMPLOYEE and DEPARTMENT tables; Application B will terminate when the lock timeout value is reached.
D) The database managerwill rollback the transaction in both applications.

2. Which of the following would NOT provide access to data stored in table TABLE1 using the name T1?

A) An alias named T1 that references table TABLE1
B) A view named T1 that references table TABLE1
C) An alias named T1 that references a view named V1 that references table TABLE1
D) A schema named T1 that references table TABLE1

3. After the following SQL statement is executed: GRANT ALL PRIVILEGES ON TABLE employee TO USER user1
Assuming user USER1 has no other authorities or privileges, which of the following actions is user USER1 allowed to perform?

A) Drop an index on the EMPLOYEE table
B) Alter the table definition
C) Grant all privileges on the EMPLOYEE table to other users
D) Drop the EMPLOYEE table

4. A view is created with the following statement:
CREATE VIEW v1 AS SELECT col1, col2, col3, col4 FROM t1 WHERE col4 > 1000 WITH CHECK OPTION
What is the effect of the CHECK OPTION clause?

A) Any row inserted or updated through view V1 must meet the condition that col4 > 1000.
B) At view creation, DB2 will check the data in table T1, and if in any row doesn't meet the condition col4 > 1000, the view creation will be rejected.
C) Any row inserted or updated through view V1 must meet the condition that col4 > 1000 and no row in table T1 can be updated such that col4 <= 1000, but new rows in the table can be inserted with col4 <= 1000.
D) From now on, any row inserted or updated in table T1 must meet the condition that col4 > 1000, but existing rows in the table are not checked.

5. A sequence was created with the DDL statement shown below:
CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10
User USER1 successfully executes the following statements in the order shown:
VALUES NEXT VALUE FOR my_seq INTO :hvar; VALUES NEXT VALUE FOR my_seq INTO :hvar;
User USER2 successfully executes the following statements in the order shown:
ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5; VALUES NEXT VALUE FOR my_seq INTO :hvar;
After users USER1 and USER2 are finished, user USER3 executes the following query:
SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1
What value will be returned by the query?

A) 20
B) 30
C) 10
D) 5

Solutions: