IBM IBM Certified Solution Developer C2090-545 Questions & Answers

• Question 31:

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

  A. SET CURRENT EXPLAINMODE=REOPT

  B. SET CURRENT EXPLAIN MODE=EXPLAIN

  C. SET CURRENT EXPLAIN MODE=YES

  D. SET CURRENT EXPLAIN MODE=PARAM

  Correct Answer: A

• Question 32:

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

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

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

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

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

  Correct Answer: C

• Question 33:

  Which of the following can be done while debugging a SQL procedure with IBM Data Studio?

  A. Change the order of statements being executed.

  B. Edit the procedure and continue.

  C. Change the value of any variables used.

  D. Re-execute the current statement.

  Correct Answer: C

• Question 34:

  When you right-click a SQL procedure in the Data Source Explorer of IBM Data Studio and select Drop from the options menu, what happens?

  A. The SQL procedure is temporarily hidden in the Data Source Explorer view.

  B. The SQL procedure is removed from the data development project.

  C. The SQL procedure is dropped from the database.

  D. The SQL procedure is dropped from the database and removed from the data development project.

  Correct Answer: C

• Question 35:

  Which statementdescribes 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; declare a cursor within the procedure body; open the cursor; exit the procedure without closing the cursor.

  B. Specify the clause DYNAMIC RESULT SETS 1 in the CREATE PROCEDURE statement; code a SELECT statement in the procedure body.

  C. 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.

  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.

  Correct Answer: A

• Question 36:

  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.

  Correct Answer: CD

• Question 37:

  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.

  Correct Answer: AC

• Question 38:

  What is the correct order for declarations in a compound statement defined by BEGIN and END?

  A. variable, cursor, handler, condition

  B. variable, condition, cursor, handler

  C. variable, condition, handler, cursor

  D. variable, cursor, condition, handler

  Correct Answer: B

• Question 39:

  Given the following procedures:

  

  What would be value of the variable TOTAL_SCORE if the PROCESS_SCORES procedure is called with the value 23 provided as input?

  A. 0

  B. 6B.6

  C. 23C.23

  D. 10D.10

  Correct Answer: B

• Question 40:

  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.

  Correct Answer: BE