Chapter 12: Key Takeaways

These are the essential points from Chapter 12 that you will need throughout the rest of this book. If you can explain each of these clearly and from memory, you have a solid understanding of how to encapsulate logic in the database.

Core Concepts to Remember

1. Views are named queries, not tables. A view stores a SELECT statement in the catalog. It does not store data. Every query against a view executes the underlying SELECT. The optimizer merges the view definition with the outer query, so in most cases there is zero performance penalty for using views.

2. Views solve three problems: security, abstraction, and stability. Security: restrict which rows and columns a user can see. Abstraction: encapsulate complex joins and computations into a simple "table." Stability: insulate applications from schema changes by redefining the view instead of rewriting queries.

3. Updateable views must reference a single base table with no aggregation, DISTINCT, GROUP BY, or derived columns. If any of these conditions is violated, the view is read-only. INSTEAD OF triggers can make read-only views appear updateable by intercepting DML and redirecting it to the base tables.

4. WITH CHECK OPTION is mandatory on updateable views used as security boundaries. Without it, a row can be modified through the view in a way that causes it to disappear from the view. WITH CHECK OPTION prevents this by rejecting operations that would violate the view's WHERE clause.

5. Materialized Query Tables (MQTs) store precomputed query results. Use them to accelerate complex aggregation queries on large tables. REFRESH DEFERRED is the common choice for reporting workloads; REFRESH IMMEDIATE keeps data current but adds overhead to every base table modification. The optimizer can automatically route queries to MQTs (query rewrite).

6. BEFORE triggers validate and transform data before it reaches the table. Use them for data normalization (lowercase email, trim whitespace), derived column computation, and business rule validation (SIGNAL SQLSTATE to reject invalid data). BEFORE triggers should not modify other tables.

7. AFTER triggers react to completed modifications. Use them for audit logging, cascading updates to related tables, and notification/queueing. AFTER triggers can (and commonly do) modify other tables.

8. Trigger chains are the single greatest risk of trigger-based architectures. A trigger on Table A that modifies Table B can fire a trigger on Table B that modifies Table C, and so on. Keep chains to a maximum depth of two. Document all triggers in a central registry. DB2 terminates chains at 16 levels deep.

9. Triggers add measurable overhead to every DML statement. The overhead includes plan compilation, execution, additional lock acquisition, and log record generation. Benchmark trigger overhead under realistic load, especially for high-throughput OLTP systems. Consider alternatives (change data capture, asynchronous processing) if trigger overhead exceeds your latency budget.

10. Stored procedures reduce network round trips and enforce centralized business logic. A multi-step operation (like a fund transfer) that would require 8 round trips as individual SQL statements requires only 1 round trip when encapsulated in a procedure. GRANT EXECUTE on the procedure without granting table access creates a strict security API.

11. SQL PL provides the procedural logic for procedures, functions, and trigger bodies. Key constructs: DECLARE for variables, IF/ELSEIF/ELSE for conditionals, WHILE/FOR for loops, cursors for row-by-row processing, and DECLARE HANDLER for structured error handling (CONTINUE, EXIT, UNDO).

12. The database-vs-application logic decision is a trade-off, not a dogma. Put audit trails and absolute data rules in the database (triggers, constraints). Put multi-step transactional operations in stored procedures when latency or security demands it. Put frequently-changing business workflows and user-facing logic in application code. This hybrid approach puts each type of logic where it works best.

13. User-Defined Functions (UDFs) extend SQL's vocabulary. Scalar UDFs return a single value and can be used in SELECT, WHERE, and ORDER BY. Table UDFs return a result set and appear in the FROM clause. Unlike procedures, functions cannot (typically) modify data and are designed for reusable computations within SQL statements.

What to Carry Forward

As you proceed through Part III and beyond, keep these principles in mind:

• Views are your primary tool for logical data independence. When you design physical tables in the normalization chapters, think about which views will expose that data to applications. The physical design can change; the view layer absorbs the shock.

• Triggers are your enforcement mechanism for rules that must be absolute. Not "should be followed" -- must be. Audit trails, regulatory requirements, data integrity rules that no application should be able to bypass. If a rule is optional or frequently changing, it does not belong in a trigger.

• Stored procedures are your security perimeter. In Part III's security chapter, you will learn to grant EXECUTE on procedures rather than SELECT/INSERT/UPDATE/DELETE on tables. This is the most robust access control pattern in DB2.

• Performance awareness is non-negotiable. Every view, trigger, and procedure has a runtime cost. Measure it. In Part IV's performance chapters, you will learn to use EXPLAIN, accounting traces, and event monitors to quantify the cost of database logic.

Part II Summary

With Chapter 12, you have completed Part II: SQL Mastery. You now have a comprehensive toolkit:

You are ready for Part III, where you will learn to design databases that are correct, secure, and performant.

Return to Chapter 12 | Continue to Further Reading