DB2 UDB 703 - Test Sample 1. Which of the following static SQL statements can use a host variable? A. FETCH B. ROLLBACK C. DROP INDEX D. ALTER TABLE 2. Which of the following JDBC interfaces contains methods that are called to determine whether or not cursors persist across COMMIT boundaries? A. ResultSet B. Statement C. DatabaseMetaData D. ResultSetMetaData 3. Which of the following JDBC objects can be used to determine the SQLSTATE if execution of a query fails? A. Statement B. ResultSet C. SQLException D. ResultSetMetaData 4. Which of the following can be used to access a DB2 database from a workstation that does not have any DB2 code installed? A. Trigger B. Java Applet C. SQL Routines D. External Stored Procedure 5. Which of the following programming interfaces can be used to program static queries from a Java application? A. CLI B. JTA C. JDBC D. SQLJ E. OLEDB 6. Given a cursor C1 on table T1. For every row fetched from T1, open a cursor C2 on table T2. For every row fetched from T2, update column C1 in table T2 and issue a COMMIT. Which of the following must define cursor C1? A. DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM t1 B. DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM t1 C. DECLARE c1 CURSOR FOR SELECT * FROM t1 FOR UPDATE OF t2 D. DECLARE c1 CURSOR FOR SELECT * FROM t1 FOR UPDATE OF c1 7. Given an ODBC/CLI program with a single connection, two threads and the following actions: Thread 1: INSERT INTO mytab VALUES (1) Thread 2: INSERT INTO mytab VALUES (2) Thread 1: INSERT INTO mytab VALUES (3) Thread 1: ROLLBACK

Thread 2: INSERT INTO mytab VALUES (4) Thread 1: COMMIT Assuming that AUTOCOMMIT is ON, how many records will be successfully inserted into the table MYTAB? A. 1 B. 2 C. 3 D. 4 8. Given a table that has columns defined: SMALLINT_COLUMN SMALLINT NOT NULL VARCHAR_COLUMN VARCHAR(20) Which of the following statements is used to retrieve rows from the table and allows the second column to be checked for NULL values? A. FETCH * INTO :hv1, :hv2 B. FETCH CURSOR1 INTO :hv1, :hv2 C. FETCH * INTO :hv1, :hv2 :hv2ind D. FETCH CURSOR1 INTO :hv1, :hv2 :hv2ind 9. Given the following TWO SQL stored procedures: Procedure 1: CREATE PROCEDURE myStorproc(p1 int, p2 char) language sql specific spintchar BEGIN IF ( P1 = 0 OR P1 = 1 ) THEN UPDATE DUMMY SET COL1 = INT(P2); END IF ; END Procedure 2: CREATE PROCEDURE myStorproc(p1 int) language sql specific spint BEGIN IF ( P1 = 0 OR P1 = 1 ) THEN UPDATE DUMMY SET COL1 = P1 ; END IF ; END Which of the following statements will remove Procedure 1 from the database? A. DROP PROCEDURE myStorproc (p1, p2) B. DROP PROCEDURE myStorproc (int, char) C. DROP SPECIFIC PROCEDURE spintchar (p1, p2) D. DROP SPECIFIC PROCEDURE spintchar (int, char) E. DROP SPECIFIC PROCEDURE myStorproc (int, char) 10. During which of the following are SQL statements optimized when using DB2 CLI? A. cursor open B. precompile phase C. application binding D. statement preparation 11. Given the following table and trigger definition: TABLE: DEPTINFO DeptID DeptTitle DeptCount ====== ========= ========= 1 SALES 5 2 MKTG 2

3 DEVELOPMENT 17 CREATE TRIGGER notify AFTER UPDATE OF deptid,deptcount ON deptinfo REFERENCING OLD AS o NEW AS n FOR EACH ROW DB2SQL MODE WHEN(o.DeptCount=0 OR n.DeptId= 0) EXEC SQL FETCH c1 INTO :var1 :var2; 19. Which of the following is a required parameter for running db2profc? A. userid B. server name C. prep options D. profile name 20. Assume that the following SQL statements have been successfully issued: CONNECT TO db1 USER user1 USING pw1 DECLARE GLOBAL TEMPORARY TABLE temp1 (val INTEGER) ... Which of the following statements will successfully insert a row into the temporary table TEMP1? A. INSERT INTO db1.temp1 VALUES (100) B. INSERT INTO user1.temp1 VALUES (100) C. INSERT INTO session.temp1 VALUES (100) D. INSERT INTO temporary.temp1 VALUES (100) 21. Given the following table: CREATE TABLE employee (name CHAR(10), salary DEC) INSERT INTO employee (name, salary) VALUES ('SMITH', 30000) INSERT INTO employee (name) VALUES ('JONES') INSERT INTO employee (name, salary) VALUES ('ALI', 35000) Which of the following statements will retrieve the lowest computed value? A. SELECT SUM(SALARY)/COUNT(*) FROM EMPLOYEE B. SELECT MIN(SALARY) FROM EMPLOYEE C. SELECT AVG(SALARY) FROM EMPLOYEE D. SELECT SUM(SALARY) FROM EMPLOYEE 22. Given the following code from an SQLJ source file:

Connection con = DriverManager.getConnection(url); DefaultContext ctx = new DefaultContext(con); DefaultContext.setDefaultContext(ctx); If an UPDATE statement is issued using the ctx context, which of the following SQLJ methods should be used to obtain the number of rows modified by the UPDATE statement? A. ctx.rowsUpdated() B. ctx.SQLRowCount() C. ctx.getUpdateCount() D. con.getUpdateCount() 23. An SQLJ application needs to connect to a remote data source SAMPLE using a userid and password. Which of the following will establish the connection? A. #sql con={CONNECT TO SAMPLE :userid assword B. getConnection("jdbc b2:SAMPLE",userid,password) C. getConnection("jdbc b2:SAMPLE:userid assword") D. #sql con={CONNECT TO SAMPLE USER :userid USING assword 24. Which of the following CLI/ODBC functions will return the number of rows affected by an INSERT, UPDATE or a DELETE statement? A. SQLNumRows() B. SQLRowCount() C. SQLRowsChanged() D. SQLUpdateCount() 25. Given the SQL statement: WITH management (name,totalpay,department,location) AS ( SELECT a.name, (a.salary * :bonusrate), b.deptname, b.location FROM staff a, org b WHERE a.dept=b.deptnumb AND a.job='Mgr' ) SELECT * FROM management WHERE totalpay > :threshold Which of the following is the above an example of? A. Derived view B. Derived table C. Scalar subselect D. Common table expression 26. Which of the following must be used by an application to retrieve multiple rows? A. SQLCA B. CURSOR C. SELECT INTO statement D. COMPOUND SQL statement