1.Which of the following products is allowed to access other DB2 servers, but cannot accept requests from other remote clients? A. DB2 Personal Edition B. DB2 Workgroup Server Edition C. DB2Enterprise Server Edition D.DB2 Data Warehouse Edition 2.A client application on z/OS must access a DB2 database on a Solaris Server. At a minimum, which of the following products must be installed on the Solaris workstation? A. DB2 ConnectEnterprise Edition B. DB2 Workgroup Server Edition C. DB2 Workgroup Server Edition and DB2 ConnectEnterprise Edition D.DB2Enterprise Server Edition and DB2 Connect Enterprise Edition 3.What is the purpose of the Design Advisor? A. To analyze workloads and make recommendations for indexes and MQTs B. To present a graphical representation of a data access plan and recommend design changes that will improve performance C. To replicate data between a DB2 database and another relational database D.To configure clients so they can access databases stored on remote servers 4.Which of the following tools can be used to catalog a database? A. Visual Explain B. Alert Center C. Journal D.Configuration Assistant 5.Which of the following DB2 tools allows a user to set DB2 registry parameters? A. Task Center B. Visual Explain C. Configuration Assistant D.Satellite Administration Center 6.A user invoking a user-defined function requires which DB2 privilege? A. CALL B. USAGE C. EXECUTE D.REFERENCES 7.Which of the following statements is used to prevent user TOM from adding and deleting data in table TAB1? A. REVOKE ADD, DELETE FROM USER tom ON TABLE tab1 B. REVOKE ADD, DELETE ON TABLE tab1 FROM USER tom C. REVOKE INSERT, DELETE FROM USER tom ON TABLE tab1 D. REVOKE INSERT, DELETE ON TABLE tab1 FROM USER tom 8.Which of the following privileges permits a user to update the comment on a sequence? A. CONTROL B. UPDATE C. USAGE D. ALTER 9.Which of the following provides a logical grouping of database objects? A. View B. Table C. Schema D. Buffer pool 10.An Alias can be an alternate name for which DB2 object? A. Sequence B. Trigger C. Schema D. View 11.Which of the following is used to create and debug user-defined functions? A. SQL Assist B. Control Center C. Command Editor D.Developer Workbench 12.Which of the following statements allows BOB to revoke access to the SAMPLE database from user TOM? A. REVOKE ACCESS ON DATABASE FROM USER bob B. REVOKE CONNECT ON DATABASE FROM USER tom C. REVOKE tom FROM ACCESS ON DATABASE BY USER bob D .REVOKE tom FROM CONNECT ON DATABASE BY USER bob 13.When a client using the SERVER_ENCRYPT authentication type connects to a server using the SERVER authentication type, what happens? A. An error will occur. B. Data passed between the client and the server is encrypted. C. User IDs and passwords are passed to the server unencrypted. D. User IDs and passwords are encrypted before they are passed to the server. 14.Which of the following provides a logical grouping of database objects? A. View B. Table C. Schema D. Buffer pool 15.Which of the following is a characteristic of a sequence? A. A sequence will never generate duplicate values. B. The MAXVALUE of a sequence can be equal to the MINVALUE. C. It is not possible to create a sequence that generates a constant since the INCREMENT value must be greater than zero. D. When a sequence cycles back to either the MAXVALUE or MINVALUE, it will always be equal to the specified value of either of these two boundaries. 16.An Alias can be an alternate name for which DB2 object? A. Sequence B. Trigger C. Schema D. View 17.Which of the following statements allows BOB to revoke access to the SAMPLE database from user JACK? A. REVOKE ACCESS ON DATABASE FROM USER bob B. REVOKE CONNECT ON DATABASE FROM USER jack C. REVOKE jack FROM ACCESS ON DATABASE BY USER bob D. REVOKE jack FROM CONNECT ON DATABASE BY USER bob. 18.What is the maximum that can be specefied when creating an XML column in DB2 table? A. The buffer pool size B. No size specified C. The page size of the table space D. None of these 19. A trigger can be created on which of the following objects? A. View B. Catalog table C. Stored procedure D. Global temporary table 20. Which of the following tools can be recommendations for indexes or MTQ's to improve the performance of the DB2 applications? A. Visual Explain B. Design Advisor C. configuration Assistant D. performance Advisor 21.Which of the tools can be used to schedule the backup operation that is to be run at every Weekend? A.Journal B.Activity monitor C.Task center D.Command line processor 22.Which of the following describes how DB2 9 stores the XML document if the XML extender is not used? A. BLOB B. CLOB C. Hierarchically D. Rows and columns 23. Which of the following will delete all of the rows from table T1? A. DELETE * FROM T1 B. DELETE * FROM TABLE T1 C. DELETE FROM T1 D. DELETE ALL FROM T1 24. Which of the following is the feature of unit of work? A. Its value can be queried from the system catalog table . B. It applies to a single data server. C. It is a recoverable sequence of operations. D. It begins when the application connects to a data server. 25. Which of the following statements eliminates all but one of each set of duplicate rows in the DEPT column of STAFF table. A. SELECT DISTINCT DEPT FROM STAFF B. SELECT UNIQUE DEPT FROM STAFF C. SELECT(DEPT)UNIQUE FROM STAFF D. SELECT(DEPT)DISTINCT FROM STAFF 26. Which of the following resources can be explicitly locked? A. Table B. Row C. Page D. Column 27. Which of the following command is used to retrieve database names from the local catalog of DRDA host databases on system i and system z ? A. LIST DCS DIRECTORY. B. LIST NODE DIRECTORY. C.LIST ACTIVE DATABASES. D. LIST DB DIRECTORY. 28. Given the following CREATE TABLE statement: CREATE TABLE table2 LIKE table1 Which two of the following will NOT occur when the statement is executed? A. TABLE2 will have the same column names and column data types as TABLE1 B. TABLE2 will have the same column defaults as TABLE1 C. TABLE2 will have the same nullability characteristics as TABLE1 D. TABLE2 will have the same indexes as TABLE1. E. TABLE2 will have the same referential constraints as TABLE1 29. Given the following function: How can this function be used in an SQL statement? CREATE FUNCTION jobemployees (job VARCHAR(8)) RETURNS TABLE (empno CHAR(6), firstname VARCHAR(12), lastname VARCHAR(15)) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC RETURN SELECT empno, firstnme, lastname FROM employee WHERE employee.job = jobemployees.job A. SELECT TABLE(JOBEMPLOYEES()) FROM EMPLOYEE B. SELECT TABLE(JOBEMPLOYEES ()) AS t FROM EMPLOYEE C. SELECT JOBEMPLOYEES (id,firstname,lastname) FROM EMPLOYEE D. SELECT * FROM TABLE (jobemployees('CLERK')) 30. If table TAB1 is created using the following statement: CREATE TABLE tab1 (col1 INTEGER NOT NULL, col2 CHAR(5), CONSTRAINT cst1 CHECK (col1 in (1, 2, 3))) Which of the following statements will successfully insert a record into table TAB1? A. INSERT INTO tab1 VALUES (0, 'abc') B. INSERT INTO tab1 VALUES (NULL, 'abc') C. INSERT INTO tab1 VALUES (ABS(2), 'abc') D. INSERT INTO tab1 VALUES (DEFAULT, 'abc') 31. Application A holds a lock on a row in table TAB1. If lock timeout is set to 20, what will happen when Application B attempts to acquire a compatible lock on the same row? A. Application B will acquire the lock it needs. B. Application A will be rolled back if it still holds its lock after 20 seconds have elapsed. C. Application B will be rolled back if Application A still holds its lock after 20 seconds have elapsed. D. Both applications will be rolled back if Application A still holds its lock after 20 seconds have elapsed. 32. Which of the following modes, when used with the LOCK TABLE statement, will cause the DB2 Database Manager to acquire a table-level lock that prevents other concurrent transactions from accessing data stored in the table while the owning transaction is active? A. SHARE MODE B. ISOLATED MODE C. EXCLUSIVE MODE D. RESTRICT MODE 33. Application A issues the following SQL statements within a single transaction using the Uncommitted Read isolation level: SELECT * FROM department WHERE deptno = 'A00'; UPDATE department SET mgrno = '000100' WHERE deptno = 'A00'; As long as the transaction is not committed, which of the following statements is FALSE? A. Other applications not running under the Uncommitted Read isolation level are prohibited from reading the updated row B. Application A is allowed to read data stored in another table, even if an Exclusive lock is held on that table C. Other applications running under the Uncommitted Read isolation level are allowed to read the updated row D. Application A is not allowed to insert new rows into the DEPARTMENT table as long as the current transaction remains active 34. Which of the following DB2 UDB isolation levels will only lock rows during read processing if another transaction tries to drop the table the rows are being read from? A. Repeatable Read B. Read Stability C. Cursor Stability D. Uncommitted Read 35. To which of the following resources can a lock NOT be applied? A. Table spaces B. Buffer pools C. Tables D. Rows 36. Which of the following strings can NOT be inserted into an XML column using XMLPARSE()? A. "" B. "John Doe" C. "" D. "

" 37. Which of the following are all valid DB2 data types ? A. DECIMAL ,DATE ,DBCLOB,INTERVAL B. NUMERIC, TIMESTAMP, BYTE, FLOAT C.NUM, TIME, XML, DOUBLE D. LONG VARCHAR,NUMBER, SMALLINT,BLOB 38. What type of constraint can be used to ensure that, in any given row in a table, the value of one column will never exceeds the value of another column ? A. Informational B. Check C.Range D. Referential 39. Which of the following strings can be inserter into an XML column using XML parse ? A. “” B. “” C.“” D. “” 40. Given the following two tables: TAB1 ---------------------- COL_1 COL_2 ----- ----- A 10 B 12 C 14 TAB2 ---------------------- COL_A COL_B ----- ----- A 21 C 23 D 25 Assuming the following results are desired: COL_1 COL_2 COL_A COL_B A 10 A 21 B 12 - - C 14 C 23 - - D 25 Which of the following joins will produce the desired results? A. SELECT * FROM tab1 INNER JOIN tab2 ON col_1 = col_a B. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON col_1= col_a C. SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON col_1=col_a D. SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1=col_a 41. Which two of the following privileges is required in order to use a package? A. BINDADD B. BIND C. CONNECT D. EXECUTE E. USE 42.CREATE TABLE employee (empid INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 5), name VARCHAR(20), dept INT CHECK (dept BETWEEN 1 AND 20), hiredate DATE WITH DEFAULT CURRENT DATE, salary DECIMAL(7,2), PRIMARY KEY(empid), CONSTRAINT cst1 CHECK (YEAR(hiredate) > 2006 OR Salary > 60500)); Which of the following INSERT statements will fail? A. INSERT INTO employee VALUES (15, 'Smith', 5, '01/22/2004', 92500.00) B. INSERT INTO employee VALUES (DEFAULT, 'Smith', 2, '10/07/2002', 80250.00) C. INSERT INTO employee VALUES (20, 'Smith', 5, NULL, 65000.00) D. INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00) 43 Application A issues the following SQL statements within a single transaction using the Uncommitted Read isolation level: SELECT * FROM department WHERE deptno = 'A00'; UPDATE department SET mgrno = '000100' WHERE deptno = 'A00'; As long as the transaction is not committed, which of the following statements is FALSE? A. Other applications not running under the Uncommitted Read isolation level are prohibited from reading the updated row B. Application A is allowed to read data stored in another table, even if an Exclusive lock is held on that table C. Other applications running under the Uncommitted Read isolation level are allowed to read the updated row D.Application A is not allowed to insert new rows into the DEPARTMENT table as long as the current transaction remains active 44.Given the following statements: CREATE TABLE table1 (col1 INTEGER, col2 CHAR(3)); CREATE VIEW view1 AS SELECT col1, col2 FROM table1 WHERE col1 < 100 WITH LOCAL CHECK OPTION; Which of the following INSERT statements will execute successfully? A. INSERT INTO view1 VALUES (50, abc) B. INSERT INTO view1 VALUES(100, abc) C. INSERT INTO view1 VALUES(50, 'abc') D. INSERT INTO view1 VALUES(100, 'abc') 45. Which of the following is not true about XML columns ? A. Data can be retrieved by the XQUERY. B. Access to any portion of the XML document can be direct without reading the whole document. C.XML columns must be altered to accommodate the additional parent and child columns. D. Data can be retrieved by the SQL. 46. A stored procedure has been created with the following statement: CREATE PROCEDURE proc1 (IN var1 int, OUT rc INTEGER) SPECIFIC myproc LANGUAGE SQL … What is the correct way to invoke this procedure from the command line processor (CLP)? A. CALL proc1 ('24', ?) B. CALL myproc ('SALES', ?) C. CALL proc1 (24, ?) D. RUN proc1 (SALES, ?) 47. Which of the following deletion rules on CREATE TABLE will allow parent table rows to be deleted if a dependent row exists? A. ON DELETE RESTRICT B. ON DELETE NO ACTION C. ON DELETE SET NO VALUE D. ON DELETE CASCADE 48. Which of the following is NOT an attribute of Declared Global Temporary Tables (DGTTs)? A. Each application that defines a DGTT has its own instance of the DGTT B. Two different applications cannot create DGTTs that have the same name C. DGTTs can only be used by the application that creates them, and only for the life of the application D. Data stored in a DGTT can exist across transaction boundaries 49. User USER1 wants to utilize an alias to remove rows from a table. Assuming USER1 has no authorities or privileges, which of the following privileges are needed? A. DELETE privilege on the table B. DELETE privilege on the alias C. DELETE privilege on the alias; REFERENCES privilege on the table D. REFERENCES privilege on the alias; DELETE privilege on the table 50. 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. 5 B. 10 C. 20 D. 30