Further Reading -- Chapter 12: Views, Triggers, and Stored Procedures

IBM Official Documentation

  • DB2 for z/OS 13 SQL Reference -- CREATE VIEW IBM Publication: SC28-1884 Definitive reference for view syntax on z/OS, including updateable view rules, WITH CHECK OPTION semantics, and catalog table entries for views (SYSIBM.SYSVIEWS, SYSIBM.SYSVIEWDEP).

  • DB2 for z/OS 13 SQL Reference -- CREATE TRIGGER IBM Publication: SC28-1884 Complete trigger syntax for z/OS including BEFORE/AFTER/INSTEAD OF, transition variables and transition tables, trigger ordering (ORDER clause), and MODE DB2SQL vs. advanced trigger body syntax.

  • DB2 for z/OS 13 SQL Reference -- CREATE PROCEDURE IBM Publication: SC28-1884 Stored procedure syntax on z/OS including SQL PL, parameter modes, result sets, external procedures, and native SQL procedures with inline compilation.

  • Db2 11.5 LUW Knowledge Center -- Views IBM Documentation: "Creating views" LUW-specific guidance on view creation, updateable view rules, INSTEAD OF triggers on views, and the SYSCAT.VIEWS and SYSCAT.VIEWDEP catalog views.

  • Db2 11.5 LUW Knowledge Center -- Triggers IBM Documentation: "Creating triggers" LUW trigger documentation including activation time, granularity, transition variables and tables, compound SQL (compiled) trigger bodies, and trigger debugging with db2pd.

  • Db2 11.5 LUW Knowledge Center -- SQL PL IBM Documentation: "SQL Procedural Language (SQL PL)" Comprehensive reference for SQL PL constructs: variable declaration, assignment, control flow (IF, CASE, WHILE, FOR, LOOP), cursor management, condition handlers (CONTINUE, EXIT, UNDO), and SIGNAL/RESIGNAL.

  • Db2 11.5 LUW Knowledge Center -- Materialized Query Tables IBM Documentation: "Materialized query tables" MQT creation, refresh strategies (DEFERRED, IMMEDIATE), query rewrite by the optimizer, and the CURRENT REFRESH AGE special register.

Books

  • Mullins, Craig S. DB2 Developer's Guide (6th Edition). IBM Press, 2012. Chapters 12-14 cover views, triggers, and stored procedures from a z/OS perspective with practical guidance on when and how to use each feature. Mullins provides excellent coverage of trigger pitfalls and performance considerations.

  • Sloan, Robert and Hernandez, Michael. Db2 Developer's Guide (7th Edition). IBM Press, 2020. Updated coverage of SQL PL, native SQL procedures, and trigger enhancements in DB2 12 for z/OS. Particularly strong on the performance characteristics of native SQL procedures versus traditional (external) procedures.

  • Janmohamed, Zamil and Liu, Clara. DB2 SQL PL: Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, and z/OS. IBM Press, 2004. The definitive book on SQL PL programming. Though dated in version specifics, the core SQL PL constructs (variables, control flow, cursors, handlers) have remained stable. Chapters on debugging and performance tuning of SQL PL code remain relevant.

  • Saraswatipura, Mohankumar and Goel, Rakesh. Db2 12 for z/OS Database Administration. MC Press, 2018. Practical administration guidance for triggers and stored procedures in a z/OS environment, including deployment strategies, rebind considerations, and monitoring with DB2 traces.

  • Gunning, Paul and Yip, Clara. Advanced SQL and Stored Procedures for DB2. MC Press, 2009. Focused treatment of stored procedure development patterns, including error handling strategies, result set management, and integration with application frameworks.

Technical Articles and Papers

  • "Trigger Design Patterns for DB2" -- IBM developerWorks (archived) Practical patterns for audit triggers, derivation triggers, and cross-table validation triggers. Includes performance benchmarking methodology and results.

  • "The Case for Database Logic" and "The Case Against Database Logic" A balanced pair of perspectives. The "for" argument emphasizes consistency and security. The "against" argument emphasizes testability and deployment agility. Reading both is more valuable than reading either alone.

  • "SQL/PSM -- SQL Persistent Stored Modules" -- ISO/IEC 9075-4 The international standard that SQL PL is based on. Reading the standard clarifies which SQL PL features are portable across database systems and which are DB2-specific extensions.

  • Temporal Tables (DB2 for z/OS 12+, Db2 LUW 10+) System-period temporal tables automatically maintain a history of row changes. They are complementary to triggers -- temporal tables capture row-level history automatically, while triggers provide customizable logic and attribution. See Chapter 10 (Section 10.8) for temporal query syntax.

  • Change Data Capture (CDC) -- IBM InfoSphere Data Replication An asynchronous alternative to triggers for capturing data changes. CDC reads the DB2 log and publishes change records to targets. Covered in Chapter 38 (Data Integration).

  • Audit Policies (Db2 LUW) LUW provides built-in AUDIT policies that can track data access and modifications at the database level, without requiring custom triggers. See CREATE AUDIT POLICY and AUDIT statements. Covered in Chapter 22 (Security).

  • Row and Column Access Control (RCAC) DB2's row permission and column mask features provide a declarative alternative to security views. RCAC rules are enforced transparently on all SQL access. Covered in Chapter 22 (Security).

Online Resources

  • IBM DB2 Community and Forums https://community.ibm.com/community/user/datamanagement/communities/community-home?CommunityKey=ea430e89-8641-4279-8536-1a2a073bcf55 Active community for DB2 practitioners. Threads on trigger performance, stored procedure debugging, and view design patterns are particularly valuable.

  • DB2 for z/OS Performance Monitoring and Tuning Guide IBM Publication: SC28-1880 Essential for understanding the performance impact of triggers and stored procedures on z/OS. Covers DB2 trace classes, accounting record analysis, and EXPLAIN output for triggered SQL.

  • Stack Overflow -- [db2] Tag https://stackoverflow.com/questions/tagged/db2 Practical Q&A on DB2 views, triggers, and stored procedures. Filter by highest-voted answers for reliable information.

Practice Environments

  • Db2 Community Edition (LUW) Free edition of Db2 for Linux, Windows, and macOS (via Docker). All view, trigger, stored procedure, and UDF features covered in this chapter are available in the Community Edition. Ideal for hands-on practice.

  • IBM Z Xplore https://www.ibm.com/z/resources/zxplore Free learning platform that provides access to a z/OS environment with DB2. You can practice z/OS-specific trigger and stored procedure syntax.

Return to Chapter 12 | Return to Key Takeaways