Chapter 10 Quiz

Instructions

Select the best answer for each question. Questions cover all sections of Chapter 10.

Question 1

What is the primary advantage of encapsulating business logic in a DB2 stored procedure rather than implementing it in multiple calling programs?

A) Stored procedures always execute faster than application-level COBOL B) A single source of truth prevents logic divergence across multiple access paths C) Stored procedures eliminate the need for DB2 precompilation D) Stored procedures can use CICS services that regular COBOL programs cannot

Answer: B Explanation: The core value proposition of stored procedures is centralizing logic so that all callers execute the same implementation, preventing drift. Stored procedures do not always execute faster (CPU overhead can increase), they still require precompilation if they contain SQL, and they cannot access CICS services.

Question 2

Which type of stored procedure is implemented as a COBOL program compiled by the Enterprise COBOL compiler?

A) Native SQL procedure B) Internal stored procedure C) External stored procedure D) Inline stored procedure

Answer: C Explanation: External stored procedures are implemented in a host language (COBOL, PL/I, C, Java). Native SQL procedures are written in SQL PL and compiled by DB2 internally. "Internal" and "inline" are not DB2 stored procedure types.

Question 3

In a CREATE PROCEDURE statement, what does PARAMETER STYLE GENERAL specify?

A) Parameters are passed using the SQL calling convention with null indicators B) Parameters are passed directly as host variables without null indicator arrays C) Parameters are passed via a shared memory segment D) Parameters are passed as a single group-level COBOL structure

Answer: B Explanation: PARAMETER STYLE GENERAL passes parameters directly without null indicators. PARAMETER STYLE GENERAL WITH NULLS adds indicator arrays. PARAMETER STYLE SQL (required for UDFs) adds indicators, SQLSTATE, function names, and diagnostic message.

Question 4

A stored procedure is defined with COMMIT ON RETURN NO. What does this mean for the calling program?

A) The stored procedure cannot execute any COMMIT statements B) DB2 automatically rolls back the stored procedure's work when it returns C) The calling program controls the commit scope — the procedure's work is part of the caller's unit of work D) The stored procedure must explicitly issue COMMIT before returning

Answer: C Explanation: COMMIT ON RETURN NO means DB2 does not automatically commit after the procedure returns. The procedure's SQL operations are part of the caller's unit of work, and the caller decides when to commit or rollback.

Question 5

What happens if a COBOL external stored procedure executes STOP RUN instead of GOBACK?

A) The procedure returns normally with SQLCODE 0 B) The WLM-managed address space is terminated, and it must be restarted C) DB2 intercepts the STOP RUN and converts it to a normal return D) The calling program receives SQLCODE +100

Answer: B Explanation: STOP RUN terminates the Language Environment enclave and the WLM-managed address space. This is catastrophic — all concurrent stored procedure calls in that address space are affected. Always use GOBACK.

Question 6

How does a COBOL stored procedure return a result set to the caller?

A) By writing rows to a temporary table that the caller reads B) By declaring a cursor WITH RETURN, opening it, and leaving it open when the procedure returns via GOBACK C) By populating an OUT parameter with an array of rows D) By executing a SELECT INTO for each row the caller should receive

Answer: B Explanation: Result sets are returned via cursors declared WITH RETURN. The cursor must be opened and left open (not closed) when the procedure returns. The caller uses ASSOCIATE LOCATORS and ALLOCATE CURSOR to access the rows.

Question 7

How does a calling program receive a result set from a stored procedure?

A) The result set automatically appears as rows in the SQLCA B) The caller uses ASSOCIATE LOCATORS to get a locator and ALLOCATE CURSOR to create a fetchable cursor C) The caller opens a cursor with the same name used in the stored procedure D) The caller reads from a DB2 global temporary table

Answer: B Explanation: The caller must use ASSOCIATE LOCATORS WITH PROCEDURE to get result set locators, then ALLOCATE CURSOR FOR RESULT SET to create cursors that can be fetched from.

Question 8

Which of the following is NOT a valid SQL data access level for CREATE PROCEDURE?

A) NO SQL B) CONTAINS SQL C) READS SQL DATA D) EXECUTES SQL DATA

Answer: D Explanation: The valid levels are NO SQL, CONTAINS SQL, READS SQL DATA, and MODIFIES SQL DATA. "EXECUTES SQL DATA" is not a valid option.

Question 9

What is the purpose of the ASUTIME LIMIT clause in CREATE PROCEDURE?

A) Sets the maximum wall-clock time the procedure can run B) Sets the maximum number of SQL statements the procedure can execute C) Sets the maximum CPU time in service units to prevent runaway procedures D) Sets the maximum amount of memory the procedure can allocate

Answer: C Explanation: ASUTIME LIMIT sets a CPU time limit in service units. If the procedure exceeds this limit, DB2 terminates it. This is a safety mechanism against infinite loops or unexpectedly expensive processing.

Question 10

What is the key structural difference between a COBOL scalar UDF and a COBOL stored procedure in terms of parameter passing?

A) UDFs use PARAMETER STYLE GENERAL; stored procedures use PARAMETER STYLE SQL B) UDFs require PARAMETER STYLE SQL, which includes null indicators, SQLSTATE, function names, and diagnostic message parameters C) UDFs pass parameters via WORKING-STORAGE; stored procedures use LINKAGE SECTION D) There is no structural difference — the parameter passing is identical

Answer: B Explanation: UDFs require PARAMETER STYLE SQL, which mandates null indicators for each parameter, SQLSTATE, qualified function name, specific function name, and diagnostic message. Stored procedures typically use PARAMETER STYLE GENERAL, which is simpler.

Question 11

A scalar UDF is declared as DETERMINISTIC. What optimization does this enable?

A) DB2 can execute the function in parallel across multiple partitions B) DB2 can cache the result and skip re-invocation for identical inputs C) DB2 can inline the function's SQL into the calling query D) DB2 can execute the function at compile time instead of runtime

Answer: B Explanation: DETERMINISTIC tells the optimizer that the function always returns the same result for the same inputs. DB2 can then avoid calling the function again if it has already computed the result for the same parameter values within the same query.

Question 12

In a COBOL table function, what does the SCRATCHPAD provide?

A) Temporary disk space for intermediate calculations B) Persistent memory across OPEN, FETCH, and CLOSE calls to maintain state C) A buffer for the result set rows before they are returned to the caller D) Shared memory that multiple concurrent callers can access

Answer: B Explanation: The scratchpad is persistent storage that DB2 allocates for the table function and preserves across the multiple calls (OPEN, FETCH, CLOSE). It allows the function to maintain state such as cursor position and counters.

Question 13

What call-type value does DB2 pass to a COBOL table function to signal "return the next row"?

A) -1 B) 0 C) 1 D) 2

Answer: B Explanation: DB2 uses -1 for OPEN, 0 for FETCH (return next row), and 1 for CLOSE. The table function sets SQLSTATE to '02000' when there are no more rows to return.

Question 14

Why is placing a scalar UDF in a WHERE clause potentially dangerous for performance?

A) DB2 must create a temporary table for every UDF invocation B) The optimizer cannot see inside the UDF to determine selectivity and must invoke it for every candidate row C) UDFs in WHERE clauses cause DB2 to use table-level locks D) WHERE clause UDFs force DB2 to use nested loop joins

Answer: B Explanation: The optimizer treats external UDFs as "black boxes" — it cannot determine their selectivity or push predicates through them. This means the UDF may be called for millions of rows before filtering occurs. This was the performance trap that Pinnacle Health encountered.

Question 15

At CNB, Rob Nguyen's benchmark showed that the stored procedure version of account validation had higher CPU per transaction (0.11ms vs 0.08ms) but 74% lower elapsed time. Why?

A) The stored procedure used more efficient SQL B) The stored procedure eliminated 4 of 5 network round-trips, and the round-trip savings exceeded the WLM/DB2 overhead increase C) The stored procedure ran at a higher WLM priority D) The stored procedure used parallel processing

Answer: B Explanation: Stored procedures trade higher per-transaction CPU (due to WLM scheduling and DB2 accounting overhead) for lower elapsed time (by eliminating network round-trips). When the bottleneck is elapsed time or throughput rather than CPU, this is a favorable trade-off.

Question 16

After deploying a new version of a stored procedure's load module, what must you do before callers will execute the new version?

A) Re-BIND the DB2 package B) Issue -STOP PROCEDURE followed by -START PROCEDURE, or REFRESH the WLM application environment C) DROP and re-CREATE the procedure D) Restart the DB2 subsystem

Answer: B Explanation: DB2 and WLM may continue using the previously loaded copy of the program. You must either stop/start the specific procedure or refresh the WLM environment to force DB2 to load the new module. Re-BIND is also needed if the DBRM changed, but the question specifically asks about the load module.

Question 17

Which security benefit do stored procedures provide compared to granting direct table access?

A) Stored procedures encrypt data in transit B) Callers can EXECUTE the procedure without needing direct SELECT/UPDATE privileges on underlying tables C) Stored procedures automatically implement row-level security D) Stored procedures prevent SQL injection

Answer: B Explanation: The stored procedure's package runs with the authority of the package owner. Callers only need EXECUTE privilege on the procedure, not direct access to the underlying tables. This creates a controlled API to the data, implementing a "need to know" pattern.

Question 18

In Sandra Kowalski's versioning convention, why does she include the version number in the procedure name (e.g., CALC_ELIGIBILITY_V3) rather than simply updating the existing procedure?

A) DB2 does not support ALTER PROCEDURE B) Including the version in the name allows callers to migrate incrementally while both versions coexist C) DB2 requires unique names for each version of a procedure D) It is a DB2 catalog limitation

Answer: B Explanation: When a breaking change is needed (parameter signature change), creating a new version (V4) while keeping the old version (V3) allows callers to migrate on their own timeline. After all callers have moved, the old version is deprecated and eventually dropped.

Question 19

Using the scoring matrix from Section 10.7, which dimension most strongly favors keeping logic in the application rather than a stored procedure?

A) Logic is called by 8 different programs B) Logic reads from 6 tables with complex joins C) Logic changes weekly based on business decisions D) Logic enforces security-sensitive referential integrity

Answer: C Explanation: High change frequency (score 1 on the "Change frequency" dimension) strongly favors keeping logic in the application, because stored procedure deployment requires BIND, WLM refresh, and DBA coordination. Weekly changes make that overhead burdensome.

Question 20

In the HA Banking system design, why is the fund transfer identified as a strong stored procedure candidate (score 22) while the account balance inquiry is not (score 14)?

A) The fund transfer is called more frequently B) The fund transfer involves multiple tables, multiple access paths, and security-sensitive operations; the balance inquiry is a simple single-table read C) The fund transfer requires CICS services D) The balance inquiry cannot be implemented as a stored procedure

Answer: B Explanation: The fund transfer scores high on access paths (5), data intensity (5 — six tables), stability (4), security (4), and moderate compute (4) = 22. The balance inquiry scores lower because its data intensity is low (one table), reducing the benefit of server-side execution. The round-trip savings don't justify the WLM overhead for a single-table read.