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

Given the two SQL procedures shown below:

What is the expected output if procedure S1 is invoked with the value 1provided for parameter V1?

A. NULL

B. 2

C. 5

D. 7

Click the Exhibit button.

Which statement correctly describes the result of the FOR loop shown in the exhibit?

A. FULLNAME isset to the last name of the employee, followed by a comma, the first name, a blank space, and the middle initial. Only the last value for FULLNAME is inserted into table TNAMES.

B. FULLNAME is set to the last name of the employee, followed by a comma, thefirst name, a blank space, and the middle initial. Only the first value for FULLNAME is inserted into table TNAMES.

C. FULLNAME is set to the last name of the employee, followed by a comma, the first name, a blank space, and the middle initial for each row. Each value for FULLNAME is inserted into table TNAMES in alphabetical order.

D. FULLNAME is set to the last name of the employee, followed by a comma, the first name, a blank space, and the middle initial for each row. Each value for FULLNAME is insertedinto table TNAMES.

Which action is supported by Data Studio in the SQL procedure lifecycle?

A. Create global temporary tables to use in the SQL procedure.

B. Debug the SQL procedure.

C. Use auto complete SQL statements.

D. Create user-define types in the SQL procedure.

Which CREATE TRIGGER statement is valid?

A. CREATE TRIGGER test1 NO CASCADE AFTER INSERT ON employee REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.bonus ISNULL) SET n.salary = n.salary + 2000

B. CREATE TRIGGER test1 NO CASCADE BEFORE INSERT ON employee REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.bonus IS NULL) SET n.salary = n.salary + 2000

C. CREATE TRIGGER test1 AFTER DELETE ON employee REFERENCING OLD_TABLE AS oldemployee FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE id IN (SELECT empno FROM oldemployee)

D. CREATE TRIGGER test1 NO CASCASE BEFORE DELETE ON employee FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE id IN (SELECT empnoFROM oldemployee)

Which two statements are true with regards to nesting SQL procedures? (Choose two.)

A. An SQL procedure cannot invoke procedures built in other languages (e.g., C, Java).

B. An SQL procedure can invoke the same SQL procedure more than once.

C. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQL procedure defined with theREADS SQL DATA clause.

D. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQL procedure defined with the MODIFIES SQL DATA clause.

E. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQL procedure defined with the NOSQL clause.

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 two statements are true about SQL procedures? (Choose 2)

A. SQL procedures reside outside of the database for easy portability.

B. SQL procedurescannot be called from triggers.

C. SQL procedures may return multiple result sets to the caller or to a client application.

D. SQL procedures support recursion.

E. SQL procedures support the PRINT statement.

Which two DB2 commands can be performed using the ADMIN_CMD procedure? (Choose two.)

A. RUNSTATS

B. BACKUP

C. RESTORE

D. RECOVER

E. CREATE DATABASE

Which declaration is not valid?

A. DECLARE var1 DECIMAL(9,2);

B. DECLARE var1 VARCHAR(20);

C. DECLARE var1 CHAR;

D. DECLARE var1 INTEGER DEFAULT;