Chapter 34: Quiz — Stored Procedures, UDFs, and Application Logic in the Database

Chapter 34: Quiz — Stored Procedures, UDFs, and Application Logic in the Database

Conceptual Questions

Question 1

Which of the following is the strongest argument for placing business logic in DB2 stored procedures rather than in application code?

A. Stored procedures are easier to debug than application code. B. SQL PL has more language features than Java or Python. C. Multiple applications can enforce the same rules through a single stored procedure, regardless of which application initiates the operation. D. Stored procedures always execute faster than equivalent application code.

Question 2

What is the key difference between BEGIN ATOMIC and a regular BEGIN...END compound statement in SQL PL?

A. BEGIN ATOMIC allows nested compound statements; regular BEGIN...END does not. B. BEGIN ATOMIC creates an implicit savepoint and rolls back all changes if any statement fails; regular BEGIN...END does not. C. BEGIN ATOMIC executes statements in parallel; regular BEGIN...END is sequential. D. BEGIN ATOMIC supports cursors; regular BEGIN...END does not.

Question 3

When a stored procedure declares a cursor WITH RETURN TO CLIENT for returning a result set, what must the procedure do with the cursor before exiting?

A. Close the cursor and commit the transaction. B. Open the cursor but NOT close it. C. Both open and close the cursor. D. Neither open nor close the cursor; DB2 handles both automatically.

Question 4

Which handler type should you use in SQL PL when you want to catch an error, log it, and then re-raise the error to the caller?

A. CONTINUE handler with RESIGNAL B. EXIT handler with RESIGNAL C. UNDO handler with RETURN D. CONTINUE handler with SIGNAL

Question 5

In a Java external stored procedure registered with PARAMETER STYLE JAVA, how are OUT parameters represented?

A. As return values from the Java method. B. As single-element arrays (e.g., int[] for INTEGER, String[] for VARCHAR). C. As Java wrapper objects (e.g., Integer, String). D. As special DB2 parameter objects.

Question 6

What does the DETERMINISTIC attribute on a user-defined function tell DB2?

A. The function will always complete within a fixed time. B. The function always returns the same result for the same input values, allowing DB2 to cache results and apply optimizations. C. The function does not modify any SQL data. D. The function can be executed in parallel across multiple partitions.

Question 7

When creating a distinct type CREATE DISTINCT TYPE MERIDIAN.USD_AMOUNT AS DECIMAL(15,2) WITH COMPARISONS, what happens if you try to add a USD_AMOUNT value to a plain DECIMAL(15,2) value?

A. DB2 automatically converts and performs the addition. B. DB2 raises a type error; an explicit CAST is required. C. DB2 rounds the result to 2 decimal places. D. The addition succeeds but returns a DECIMAL(15,2), not USD_AMOUNT.

Question 8

What is the primary purpose of the WLM ENVIRONMENT clause in a z/OS stored procedure definition?

A. It specifies the database schema where the procedure is stored. B. It defines the WLM-managed address space where the external stored procedure will execute, controlling isolation and resource management. C. It sets the maximum execution time for the procedure. D. It enables workload balancing across multiple DB2 subsystems.

Question 9

Why does the PROC_TRANSFER_FUNDS procedure in Section 34.12 update accounts in ascending ID order, regardless of which is the source and destination?

A. To improve index performance on the ACCOUNTS table. B. To ensure the transaction log entries are in a consistent order. C. To prevent deadlocks by always acquiring row locks in the same order. D. To minimize the number of page splits during updates.

Question 10

Which SQL PL statement retrieves the number of rows affected by the most recent SQL operation?

A. GET DIAGNOSTICS v_count = ROWS_AFFECTED B. SET v_count = SQL_ROW_COUNT C. GET DIAGNOSTICS v_count = ROW_COUNT D. SELECT ROWS_AFFECTED() INTO v_count FROM SYSIBM.SYSDUMMY1

Practical Questions

Question 11

Given this procedure fragment:

DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
    SET v_error = 'DUPLICATE';

INSERT INTO MERIDIAN.ACCOUNTS (ACCOUNT_ID, BALANCE)
VALUES (p_acct_id, 100.00);

IF v_error = 'DUPLICATE' THEN
    UPDATE MERIDIAN.ACCOUNTS SET BALANCE = BALANCE + 100.00
    WHERE ACCOUNT_ID = p_acct_id;
END IF;

What happens if the INSERT encounters a duplicate key?

A. The procedure terminates with an error. B. The handler sets v_error to 'DUPLICATE', then control passes to the statement AFTER the INSERT (the IF statement), which executes the UPDATE. C. The handler sets v_error to 'DUPLICATE', and the INSERT is retried. D. The handler sets v_error to 'DUPLICATE', and control exits the compound statement.

Question 12

What is wrong with this UDF when used in a WHERE clause?

CREATE FUNCTION MERIDIAN.FN_ACTIVE_BRANCH(p_acct_id INTEGER)
RETURNS INTEGER
NOT DETERMINISTIC
READS SQL DATA
RETURN (SELECT BRANCH_ID FROM MERIDIAN.ACCOUNTS WHERE ACCOUNT_ID = p_acct_id);

-- Usage:
SELECT * FROM MERIDIAN.BRANCHES
WHERE BRANCH_ID = MERIDIAN.FN_ACTIVE_BRANCH(1001);

A. Nothing is wrong; this is an efficient pattern. B. The function is NOT DETERMINISTIC and READS SQL DATA, so DB2 cannot push the predicate down or use an index effectively. The function is called for every row in BRANCHES. C. The function returns NULL if the account does not exist, causing a full table scan. D. The function should be declared as a table function, not a scalar function.

Question 13

You need to pass a list of 50 account IDs to a stored procedure for batch processing. Which approach is most appropriate in DB2?

A. Pass them as a comma-separated VARCHAR string and parse inside the procedure. B. Use an array type parameter (INTEGER ARRAY[100]). C. Create a global temporary table, insert the IDs, and have the procedure read from it. D. Call the procedure 50 times, once per account.

Answer Key

C — Multiple applications enforcing the same rules is the strongest argument for database-tier logic.
B — BEGIN ATOMIC creates a savepoint and rolls back on failure.
B — The cursor must be opened but NOT closed; DB2 returns the open cursor as a result set.
B — EXIT handler with RESIGNAL: catches the error (allows logging), then re-raises it.
B — OUT parameters use single-element arrays in PARAMETER STYLE JAVA.
B — DETERMINISTIC means same inputs always produce same outputs, enabling caching.
B — Distinct types are not interchangeable; explicit CAST is required.
B — WLM ENVIRONMENT specifies the managed address space for execution.
C — Ascending ID order prevents deadlocks through consistent lock ordering.
C — GET DIAGNOSTICS v_count = ROW_COUNT is the correct syntax.
B — CONTINUE handler executes, then control returns to the next statement after the one that caused the condition.
B — The scalar UDF prevents predicate pushdown and efficient index use.
B — Array type parameters are the most natural and efficient approach in DB2.