C2090-545 Online Practice Questions and Answers

Which procedure demonstrates the correct use of dynamic SQL?

A. CREATE PROCEDURE update_count1 (IN new_count INTEGER, IN item_code INTEGER) BEGIN DECLARE v_dynSQL VARCHAR(200); SET v_dynSQL = 'UPDATE stock SET quantity_on_hand=? WHERE item_number=?'; PREPARE v_stmt1 FROM v_dynSQL; EXECUTE v_stmt1 USING new_count, item_code; END

B. CREATE PROCEDURE update_count2 (IN tab_name VARCHAR(128), IN new_count INTEGER, IN item_code INTEGER) BEGIN DECLARE v_dynSQL VARCHAR(200); SET v_dynSQL = 'UPDATE ? SET quantity_on_hand=? WHERE item_number=?'; PREPARE v_stmt1 FROM v_dynSQL; EXECUTE v_stmt1 USING tab_name, new_count, item_code; END

C. CREATE PROCEDURE update_count4 (IN tab_name VARCHAR(128), IN col_name1 VARCHAR(128), IN col_name2 VARCHAR(128), IN new_count INTEGER, IN item_code INTEGER) BEGIN DECLARE v_dynSQL VARCHAR(200); SET v_dynSQL = 'UPDATE ? SET ?=? WHERE ?=?'; PREPARE v_stmt1 FROM v_dynSQL; EXECUTE v_stmt1 USING tab_name, col_name1,new_count, col_name2, item_code; END

D. CREATE PROCEDURE update_count5 (IN new_count INTEGER, IN item_code INTEGER) BEGIN DECLARE v_dynSQL VARCHAR(200); DECLARE v_col_name VARCHAR(128); SET v_col_name = 'item_number'; SET v_dynSQL = 'UPDATE stock SETquantity_on_hand=? WHERE ?=?'; PREPARE v_stmt1 FROM v_dynSQL; EXECUTE v_stmt1 USING new_count, v_col_name, item_code; END

Which SQL procedure declaration is coded correctly?

A. CREATE PROCEDURE myproc(IN salary DOUBLE, OUT commission DOUBLE) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SETcommission = 0; DECLARE my_cur CURSOR FOR SELECT * FROM employee; DECLARE a DOUBLE; SET a = .06 * salary; SET commission = a; END

B. CREATE PROCEDURE myproc(IN salary DOUBLE, OUT commission DOUBLE) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SET commission= 0; DECLARE a DOUBLE; DECLARE my_cur CURSOR FOR SELECT * FROM employee; SET a = .06 * salary; SET commission = a; END

C. CREATE PROCEDURE myproc(IN salary DOUBLE, OUT commission DOUBLE) BEGIN DECLARE a DOUBLE; DECLARE EXIT HANDLER FOR SQLEXCEPTION SETcommission = 0; DECLARE my_cur CURSOR FOR SELECT * FROM employee; SET a = .06 * salary; SET commission = a; END

D. CREATE PROCEDURE myproc(IN salary DOUBLE, OUT commission DOUBLE) BEGIN DECLARE a DOUBLE; DECLARE my_cur CURSOR FOR SELECT * FROM employee; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET commission = 0; SET a = .06 * salary; SET commission = a; END

In which type of table space must global temporary tables be created?

A. REGULAR

B. LONG

C. SYSTEM TEMPORARY

D. USER TEMPORARY

Which of the following statements is true for declared global temporary tables?

A. Declared global temporary table descriptions are stored in the system catalog

B. When a session terminates, all declared global temporary tables created in the session are dropped.

C. Declared global temporary tables are persistent and can be shared with multiple sessions.

D. Declared temporary tables support LONG VARCHAR columns.

Click the Exhibit button.

Which statement is true about the CASE statement shown in the exhibit?

A. An employee with a rating of 1 receives a 10% salary increase.

B. An employee with a rating of 3 receives no salary increase.

C. An employee with a rating of 2 receives a 3%salary increase.

D. All employees will receive at least a 5% salary increase.

Which object is used to return a result set from an SQLprocedure?

A. Temporary table

B. Array

C. Cursor

D. Scratchpad

Which two statements are true? (Choose two.)

A. SQL procedures can contain static and/or dynamic SQL statements.

B. Static or dynamic SQL execution is not associated with a package.

C. The SQL procedure isalways associated with a package that contains access paths of SQL statements in the procedure.

D. It is necessary for an end-user to have object level privileges if that user has execute privileges on an associated package and the SQL procedure.

E. SQL procedures can have COMMIT or ROLLBACK within atomic compound statements.

Which tool can be used to get quick static SQL Explain data from packages?

A. db2expln

B. db2exfmt

C. Control Center

D. DB2 command center

Which code fragment illustrates the proper way to perform error checking in SQL stored procedures?

A. ... IF EXCEPTION < 0 ELSE THEN; NULL; ENDIF; ...

B. ... BEGIN EXCEPTION < 0 ELSE; NULL; END; ...

C. ... IF SQLCODE < 0 THEN RETURN -6786; END IF; ...

D. ... BEGIN IF SQLCODE <0 THEN RETURN -6786 END;

Which two statements describe a CASE statement? (Choose two.)

A. CASE statements are used to enter into some logic based on a literal value.

B. CASE statements are used to enter into some logicbased on the value of an expression.

C. CASE statements are used to return control to the beginning of an expression.

D. CASE statements are used to enter into some condition and loop until the condition is met.

E. CASE statements are used to iterate intosome logic based on a literal value.