IBM C2090-545 Actual Tests : DB2 9.7 SQL Procedure Developer

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

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 using the WITH RETURN clause.
  3.Open the cursor in the SQL procedure.
  4.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.Return to the application.
• C. 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.
• 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.Close the cursor.

In the code segment shown below:

Which statement accurately describes the result?

• A. For activities whose description has changed, update the description in the ARCHIVE table. For new activities, insert into the ARCHIVE table. The ARCHIVE and ACTIVITIES table both have ACTIVITY as a primary key.
• B. The statement will fail since MERGE is not a supported operator.
• C. For activities whose description has not changed, update the description in the ARCHIVE table. For new activities, insert into the ARCHIVE table. The ARCHIVE and ACTIVITIES table both have ACTIVITY as a primary key.
• D. For activities whose description has changed, update the description in the ACTIVITY table. For new activities, insert into the ACTIVITIES table. The ARCHIVE and ACTIVITIES table both have ACTIVITY as a primary key.

Which statement describes what must be done to create an SQL procedure that returns a result set?

• A. Specify the clause DYNAMIC RESULT SETS 1 in the CREATE PROCEDURE statement; code a SELECT statement in the procedure body.
• B. Execute the CREATE PROCEDURE statement using the defaults; declare a cursor within the procedure body; open the cursor; exit the procedure without closing the cursor.
• C. Specify the clause DYNAMIC RESULT SETS 1 in the CREATE PROCEDURE statement; declare a cursor within the procedure body; open the cursor; exit the procedure without closing the cursor.
• D. Execute the CREATE PROCEDURE statement using the defaults; declare a cursor within the procedure body; open the cursor; retrieve each row into output variables; close the cursor before exiting the procedure.

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

• A. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQL procedure defined with the NO SQL clause.
• B. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQL procedure defined with the READS SQL DATA clause.
• C. An SQL procedure cannot invoke procedures built in other languages (e.g., C, Java).
• D. An SQL procedure can invoke the same SQL procedure more than once.
• E. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQL procedure defined with the MODIFIES SQL DATA clause.