Assessment: DB2 9.7 SQL Procedure Developer (A2090-545) Free Practice Test

Question 1

Which code fragment illustrates the proper way to capture and save SQL error handler values so they can be used to control procedure logic?

A. DECLARE ERROR HANDLING FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET retcode = SQLMSG;

B. DECLARE ERROR HANDLING SQLEXCEPTION, SQLWARNING, NOT FOUND SET retcode = SQLERRN;

C. DECLARE ERROR HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET retcode = EXCEPTION;

D. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET retcode = SQLCODE;

Correct Answer: D

Question 2

Which statement can be used to declare a variable inside an SQL procedure that can be used to represent a monetary value?

A. DECLARE v_money DOUBLE;

B. DECLARE v_money CURRENCY;

C. DECLARE v_money DECIMAL(9,2);

D. DECLARE v_money MONEY;

Correct Answer: C

Question 3

Which of the following steps are required to debug a stored procedure using IBM Data Studio?

A. Debug the stored procedure from the IBM Data Studio command line console.

B. Deploy the stored procedure from the data development project, debug the stored procedure.

C. Debug the stored procedure from the Data Source Explorer.

D. Deploy the stored procedure from the data development project, choose option for debugging and then debug.

Correct Answer: D

Question 4

Which command can be used to optimize static SQL statements containing parameterized queries?

A. SET CURRENT EXPLAIN MODE=YES

B. SET CURRENT EXPLAIN MODE=REOPT

C. SET CURRENT EXPLAIN MODE=EXPLAIN

D. SET CURRENT EXPLAIN MODE=PARAM

Correct Answer: B

Question 5

Which command can be used to convert literals in SQL statements to parameter markers to avoid compilation of similar SQL statements?

A. UPDATE DB CFG USING LIT_PARM ON

B. UPDATE DB CFG USING CURSOR_SHARING ON

C. UPDATE DB CFG USING CURSOR_SHARING FORCED

D. UPDATE DB CFG USING STMT_CONC ON

Correct Answer: D

Question 6

In the function shown below:

Which statement can be used to invoke the function above?

A. SELECT * FROM fcn1('B01')

B. SELECT TABLE(fcn1('B01')) FROM SYSIBM.SYSDUMMY1

C. SELECT fcn1('B01') FROM SYSIBM.SYSDUMMY1

D. SELECT * FROM TABLE(fcn1('B01'))

Correct Answer: D

Question 7

The CREATE PROCEDURE statement shown below was executed against a database called MYDB.
CREATE PROCEDURE myschema.proc1(IN p1 INTEGER, OUT p2 CHAR(4), OUT p3 SMALLINT)
BEGIN
SET p2 = 'abc';
END
Which two CREATE PROCEDURE statements, when executed against the same database, will succeed? (Choose two.)

A. CREATE PROCEDURE myschema.proc1(IN p1 CHAR(4), OUT p2 INTEGER)
BEGIN
SET p2 = 123;
END

B. CREATE PROCEDURE myschema.proc1(IN p1 CHAR(4), OUT p2 INTEGER, OUT p3
SMALLINT)
BEGIN
SET p2 = 123;
END

C. CREATE PROCEDURE myschema.proc1(IN p1 INTEGER, OUT p2 CHAR(4), OUT p3
CHAR(4))
BEGIN
SET p2 = 'abc';
END

D. CREATE PROCEDURE myschema.proc1(IN p1 NUMBER, OUT p2 NUMBER, OUT p3 NUMBER) BEGIN SET p2 = 'abc'; END

E. CREATE PROCEDURE otherschema.proc1(IN p1 CHAR(4), OUT p2 CHAR(4), OUT p3 CHAR(4)) BEGIN SET p2 = 'abc'; END

Correct Answer: A,E

Question 8

Which steps must be followed to return a result set from an SQL procedure?

A. 1. Create the procedure using the DYNAMIC RESULT SETS clause.
2.Declare the cursor.
3.Open the cursor in the SQL procedure.
4.Close the cursor.
5.Return to the application.

B. 1. Create the procedure using the WITH RETURN clause.
2.Declare the cursor using the DYNAMIC RESULT SETS clause.
3.Open the cursor in the SQL procedure.
4.Close the cursor.

C. 1. Create the procedure using the DYNAMIC RESULT SETS clause.
2.Declare the cursor using the WITH RETURN clause.
3.Open the cursor in the SQL procedure.
4.Return to the application.

D. 1. Create the procedure using the WITH RETURN clause.
2.Declare the cursor using the DYNAMIC RESULT SETS clause.
3.Open the cursor in the SQL procedure.
4.Return to the application.

Correct Answer: C

Welcome to TestSimulate

IBM Assessment: DB2 9.7 SQL Procedure Developer (A2090-545) Free Practice Test