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<4) BEGIN INSERT INTO notifylog VALUES(CURRENT TIMESTAMP,'Check on Dept Changes'); END How many rows will be inserted into the NOTIFYLOG table as a result of the trigger activations caused by the successful execution of the following set of SQL statements? UPDATE DeptInfo SET DeptCount = DeptCount+1 UPDATE DeptInfo SET DeptTitle = 'MARKETING' WHERE DeptId=2 UPDATE DeptInfo SET DeptCount = 10 WHERE DeptID=1 A. 2 B. 3 C. 4 D. 5 12. Given the following embedded SQL pseudocode: Start Program EXEC SQL BEGIN DECLARE SECTION USERA CHARACTER (8) USERB CHARACTER (8) PW CHARACTER (8) COLVAL CHARACTER (16) EXEC SQL END DECLARE SECTION EXEC SQL INCLUDE SQLCA EXEC SQL WHENEVER SQLERROR GOTO ERRCHK (program logic) (:usera contains the string "usera") ( wa contains a valid password) (:userb now contains the string "userb") ( wb contains a valid password) EXEC SQL CONNECT TO samplea USER :usera USING wa EXEC SQL SELECT col1, col2 FROM tablea
(check error information in SQLCA) (Cleanup) End Program What table will be updated in "samplea" if the second DELETE completes successfully? A. usera.tablex B. userb.tablea C. usera.tablea D. userb.tablex 13. Which of the following ODBC/CLI statement handle attributes permits an application to change the memory address of all bound parameter markers without making any ODBC/CLI API calls? A. SQL_ATTR_PARAM_BIND_TYPE B. SQL_ATTR_PARAM_STATUS_PTR C. SQL_ATTR_PARAM_OPERATION_PTR D. SQL_ATTR_PARAM_BIND_OFFSET_PTR 14. Given a table created using the statement: CREATE TABLE abc.stuff (i INT) A user called XYZ is to be enabled to access data from table ABC.STUFF using an implicit schema. Assuming the necessary privileges have been granted, which of the following statements issued by user ABC will provide this result? A. CREATE ALIAS stuff FOR abc.stuff B. CREATE VIEW abc.stuff FOR xyz.stuff C. CREATE ALIAS abc.stuff FOR xyz.stuff D. CREATE VIEW xyz.stuff AS SELECT i FROM abc.stuff 15. Which of the following is required to successfully execute a DB2 Call Level Interface (CLI) application? A. The user must have EXECUTE privilege on the application. B. The developer must have EXECUTE privilege on the application. C. The user must have sufficient privileges on referenced tables. D. The developer must have sufficient privileges on referenced tables.
(program logic to retrieve results) EXEC SQL COMMIT (more program logic) EXEC SQL CONNECT TO sampleb USER :userb USING wb EXEC SQL SELECT col1, col2 FROM tablex EXEC SQL DELETE FROM tablea WHERE col1= :colval //1st delete EXEC SQL COMMIT (more program logic) EXEC SQL SET CONNECTION samplea EXEC SQL DELETE from tablea where col1= :colval //2nd delete EXEC SQL COMMIT EXEC SQL CONNECT RESET ERRCHK
16. USERA has authority to access all tables in a database. Which DB2 application development interface should be used to restrict USERA from accessing tables with payroll information during application runtime? A. CLI B. JDBC C. Embedded static SQL D. Embedded dynamic SQL 17. Given the following: EXEC SQL BEGIN DECLARE SECTION; char var1[20]; short var2; EXEC SQL END DECLARE SECTION; Which two of the following examples correctly demonstrate the use of host variables within an SQL statement?
(Choose 2) A. EXEC SQL CONNECT TO var1 B. EXEC SQL FETCH c1 INTO :var1 :var2 C. EXEC SQL UPDATE name_column INTO :var1 D. EXEC SQL SELECT name_column INTO :var1 FROM T1 E. EXEC SQL SELECT name_column INTO :var1, :var2 FROM T1 18. Given that any referenced database objects exist and all host variables have been declared appropriately. Which two of the following lines of embedded SQL code can be successfully precompiled into the appropriate host-language source? (Choose 2) A. EXEC SQL FETCH c1 INTO :var1 :var2 :var3; B. #sql iter = { SELECT col1 FROM tab1 WHERE col2=:var1 ; C. SQL UPDATE tab1 SET col1 = :var1 :var2 WHERE col2 = :var3; D. #sql exec { SELECT col1, col2 FROM tab1 WHERE col2 = :var1 ; E. EXEC SQL OPEN c1; if (SQLCODE >= 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