DB2 9.7 SQL Procedure Developer v6.0
Question 1
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.
Answer : AB
Question 2
STION NO: 24 -
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.
Answer : C
Question 3
Click the Exhibit button.
Given the user-defined function shown in the exhibit, what will the function return if invoked using the statement shown below?
SELECT check_id(1) FROMSYSIBM.SYSDUMMY1
- A. Error: ID 1 is not valid
- B. Application Error
- C. SQLSTATE 80000 will be returned, with the custom error message "Error: ID 1 is not valid"
- D. NULL
Answer : D
Question 4
Which two types of storedprocedures can be developed in IBM Data Studio? (Choose two.)
- A. SQL
- B. PHP
- C. PL/SQL
- D. Perl
- E. Ruby
Answer : AC
Question 5
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.
Answer : CD
Question 6
In a SQL Procedure, rows_fetched is an integer variable and curEmp is acursor variable of
SELECT statement on a table. After opening the cursor curemp, which of the statement will determine the number of rows fetched?
- A. SET rows_fetched = curEmp%CURSOR_ROWCOUNT
- B. SET rows_fetched = curEmp%ROWCOUNT
- C. SET rows_fetched =CURSOR_ROWCOUNT(curemp)
- D. SET rows_fetched = ROWCOUNT(curemp)
Answer : C
Question 7
Given the SQL statement shown below:
Which statement correctly describesthe cursor MY_CURSOR?
- A. The cursor does not have sensitivity to insert, update, or delete operations that are made to rows in the underlying result table.
- B. The cursor will not be closed as a consequence of a commit operation.
- C. The result table of the cursor is intended to be used as a result set that will be returned from a procedure.
- D. The cursor can be used to perform positioned delete operations.
Answer : D
Question 8
Given the procedure show below:
What will be the value of P2?
- A. 50
- B. 51
- C. 100
- D. 102
Answer : C
Question 9
Given the following CREATE PROCEDURE statement:
In what order must declarations occur within the lines ap: BEGIN and END ap?
- A. Variables, cursors, and condition handlers
- B. Cursors, variables, condition handlers
- C. Condition handlers, variables, cursors
- D. Condition handlers, cursors, variables
Answer : A
Question 10
If the following SQL statements were executed in the order shown:
What would be the values of RET_CODE1 and RET_CODE2 after executing the
SIZEPRINTERprocedure?
- A. 100, 100
- B. 100 , 1
- C. 123, 123
- D. 100, 0
Answer : B
Question 11
Click the Exhibit button.
An SQL function was created using the statement shown in the exhibit. Assume this function is invoked by executing the SQL statementshown below:
VALUES e_degree(SMALLINT(17))
What will be returned?
- A. SQL0440N No authorized routine named "E_DEGREE"of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
- B. 1 ------------ BACHELOR 1 record(s) selected.
- C. 1 ------------ MASTER 1 record(s) selected.
- D. 1 ------------ UNKNOWN 1 record(s) selected.
Answer : D
Question 12
Which of the following statements is true for declared global temporary tables(DGTT)?
- A. The value of the USER special register is the implicit schema name of the DGTT
- B. B. The DGTT must be stored in a system temporary table space
- C. SESSION is the implicit schema name of the DGTT
- D. The data in a DGTT cannot be compressed.
Answer : C
Question 13
Which statement will let you use the result set from the nested procedure CALLEE?
- A. ASSOCIATE RESULT SET LOCATOR( loc1) WITH PROCEDURE callee;
- B. BIND RESULT SET WITH PARAMETERS FOR PROCEDUREcallee;
- C. INSERT RESULT SET FROM callee INTO CURSOR c1;
- D. SELECT * FROM callee;
Answer : A
Question 14
Which statement will change the value of a special register?
- A. UPDATE SPECIAL REGISTER TIME = ?2:30:00?UPDATE SPECIAL REGISTER TIME = ?2:30:00
- B. UPDATE SPECIAL REGISTERSCHEMA = 'DB2ADMIN'
- C. SET CURRENT TIME = ?2:30:00?SET CURRENT TIME = ?2:30:00
- D. SET CURRENT SCHEMA = 'DB2ADMIN'
Answer : D
Question 15
When considering authorization for CREATE OR REPLACE PROCEDURE statements, the authorization id must have:
- A. DBADM authority
- B. group privileges on the table or view specified in the procedure
- C. IMPLICIT_SCHEMA authority on the database
- D. ownership of the existing procedure
Answer : D