IBM DB2 9.7 SQL Procedure Developer Sample Questions:

1. 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) Declared global temporary tables are persistent and can be shared with multiple sessions.
C) Declared temporary tables support LONG VARCHAR columns.
D) When a session terminates, all declared global temporary tables created in the session are dropped.

2. Using Optimistic Locking, both Transaction 1 and Transaction 2 have read the same row including the RID_BIT and ROW CHANGE TOKEN values. Transaction 1 updates the row after ensuring that the row has not changed after the previous SELECT by adding a RID_BIT and ROW CHANGE TOKEN predicate to the UPDATE statement. Transaction 2 now tries to update that same row using the same predicate as transaction 1. What will be the result?

A) The row will not be found because the value of the ROW CHANGE TOKEN has changed in regard to the UPDATE of transaction 1. Transaction 2 will complete with an insert of a new row.
B) The row will be found with the same ROW CHANGE TOKEN. Transaction 2 will now insert its values replacing those of transaction 1.
C) The row will be found with a different ROW CHANGE TOKEN and Transaction 2 will overwrite the values set in transaction 1.
D) The row will not be found because the value of the ROW CHANGE TOKEN has changed in regard to the UPDATE of transaction 1. Transaction 2 has to retry in order to retrieve the current data.

3. 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 statement shown below:
VALUES e_degree(SMALLINT(17))
What will be returned?

A) 1
MASTER 1 record(s) selected.
B) 1
UNKNOWN 1 record(s) selected.
C) SQL0440N No authorized routine named "E_DEGREE" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
D) 1
BACHELOR 1 record(s) selected.

4. Click the Exhibit button.

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

A) 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.
B) FULLNAME is set to the last name of the employee, followed by a comma, the first 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. Only the last value for FULLNAME is inserted into table TNAMES.
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 inserted into table TNAMES.

5. In a SQL Procedure, rows_fetched is an integer variable and curEmp is a cursor 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 = ROWCOUNT(curemp)
C) SET rows_fetched = curEmp%ROWCOUNT
D) SET rows_fetched = CURSOR_ROWCOUNT(curemp)

Solutions: