Chapter 34: Key Takeaways

1. Logic Placement Is an Architecture Decision

Placing business logic in DB2 stored procedures is appropriate when multiple applications must enforce the same rules, when security requires hiding table structures, when network latency is a bottleneck, or when transactional atomicity across many statements is critical. Keep logic in the application tier when it requires external system calls, rapid iteration, or CPU-intensive processing that would overload the database.

2. SQL PL Is More Powerful Than Most Developers Realize

Compound statements, nested control flow, cursor variables, FOR loops over queries, dynamic SQL with PREPARE/EXECUTE, arrays, and associative arrays provide a complete procedural programming environment. The FOR loop (iterating over a query with implicit cursor management) is particularly powerful for reducing boilerplate.

3. Error Handling Requires a Consistent Pattern

Every production procedure should use the same structure: DECLARE EXIT HANDLER for unexpected errors (with GET DIAGNOSTICS for details), named conditions for readability, SIGNAL for raising custom conditions, and RESIGNAL for re-throwing after logging. Return status codes and messages through OUT parameters so every caller gets actionable feedback.

4. Result Sets Are Returned via Open Cursors

Declare cursors WITH RETURN TO CLIENT, open them, and do NOT close them. DB2 returns the open cursor as a result set to the calling application. A single procedure can return multiple result sets by opening multiple cursors.

5. External Procedures Bridge Language Gaps

When SQL PL is insufficient, Java stored procedures provide access to the full Java ecosystem while running inside DB2's JVM. On z/OS, COBOL stored procedures leverage decades of existing business logic. OUT parameters use single-element arrays in Java (PARAMETER STYLE JAVA). z/OS procedures run in WLM-managed address spaces.

6. Scalar UDFs Extend SQL's Vocabulary

Create scalar UDFs for calculations that are reused across many queries. Mark them DETERMINISTIC when possible to enable caching. Use RETURNS NULL ON NULL INPUT to skip invocation for NULL inputs. Avoid scalar UDFs in WHERE clauses on indexed columns — they prevent predicate pushdown and index access.

7. Table UDFs Replace Complex Parameterized Views

Table functions accept parameters and return result sets, filling a gap that views cannot. Use them for parameterized queries, row generation (amortization schedules, date ranges), and replacing complex views that would benefit from input parameters.

8. Distinct Types Add Domain Safety

Distinct types prevent accidental mixing of semantically different values that share the same base type. USD_AMOUNT and EUR_AMOUNT are both DECIMAL(15,2) but cannot be added without explicit casting. Create sourced functions (SUM, AVG) for aggregate operations on distinct types.

9. Deadlock Prevention Is a Design Pattern

Always acquire locks in a consistent order. The transfer procedure updates accounts in ascending ID order regardless of which is the source and destination. This simple pattern eliminates deadlocks without requiring retry logic.

10. Version Management Requires Discipline

Store all database objects in source control. Deploy in dependency order (types, then functions, then procedures). Maintain backward compatibility through wrapper procedures. Use schema-based versioning for parallel deployments. Every deployment should have a corresponding rollback script.