Chapter 10: Further Reading

IBM Documentation

DB2 for z/OS Knowledge Center

  • "CREATE PROCEDURE (external)" — The definitive reference for all CREATE PROCEDURE options. Pay particular attention to the interaction between PARAMETER STYLE, LANGUAGE, and WLM ENVIRONMENT clauses.

  • IBM Documentation: DB2 13 for z/OS > SQL Reference > CREATE PROCEDURE (external)

  • "CREATE FUNCTION (external scalar)" and "CREATE FUNCTION (external table)" — Covers the complete syntax for scalar and table UDFs, including SCRATCHPAD, FINAL CALL, and DISALLOW PARALLEL options.

  • IBM Documentation: DB2 13 for z/OS > SQL Reference > CREATE FUNCTION

  • "Writing external stored procedures" — The programming guide section covering COBOL, PL/I, and C stored procedure implementation patterns, including result set handling and error processing.

  • IBM Documentation: DB2 13 for z/OS > Application Programming and SQL Guide > Writing stored procedures

  • "Writing user-defined functions" — Covers the PARAMETER STYLE SQL contract, scratchpad usage for table functions, and the OPEN/FETCH/CLOSE call-type protocol.

  • IBM Documentation: DB2 13 for z/OS > Application Programming and SQL Guide > Writing user-defined functions

WLM Configuration for Stored Procedures

  • "Setting up the WLM environment for stored procedures" — Covers JCL procedure setup, NUMTCB configuration, and the relationship between WLM service classes and stored procedure performance.

  • IBM Documentation: DB2 13 for z/OS > Installation and Migration > WLM environment setup

  • "Managing WLM application environments" — The z/OS WLM documentation covering VARY WLM commands, application environment lifecycle, and monitoring.

  • IBM Documentation: z/OS MVS Planning: Workload Management

DB2 Performance

  • "Monitoring stored procedures" — Covers IFCID 0170 (stored procedure detail), SMF 101 subtype 3 (stored procedure statistics), and DSN_STATEMNT_TABLE analysis.

  • IBM Documentation: DB2 13 for z/OS > Performance Monitoring and Tuning

  • "Resource Limit Facility (RLF)" — Setting CPU time limits for dynamic SQL, including special considerations for queries that invoke UDFs.

  • IBM Documentation: DB2 13 for z/OS > Managing Performance > Resource Limit Facility

IBM Redbooks

SG24-8480: DB2 for z/OS Stored Procedures: Through the CALL and Beyond

The most comprehensive Redbook on DB2 stored procedures. Covers external and native SQL procedures, WLM environment design, debugging, performance tuning, and migration strategies. Chapters 5 and 6 focus specifically on COBOL external procedures and are directly relevant to this chapter's content.

SG24-8421: DB2 for z/OS: Performance Monitoring and Tuning

Chapter 12 covers stored procedure and UDF performance, including CPU accounting, WLM queue analysis, and the impact of UDFs on query optimization. The case studies on UDF performance traps mirror the Pinnacle Health scenario in this chapter.

SG24-8329: Deploying DB2 Stored Procedures

Focuses on the deployment pipeline: precompile, compile, bind, create procedure, and WLM environment management. Includes automation examples using REXX and JCL.

Conference Papers and Articles

IDUG (International DB2 Users Group)

  • "Stored Procedure Best Practices for the Enterprise" — Presented at IDUG North America. Covers governance, versioning, and the organizational challenges of shared stored procedures. Includes a decision framework similar to the scoring matrix in Section 10.7.

  • "UDF Performance: What the Optimizer Doesn't Tell You" — A detailed analysis of how the DB2 optimizer handles external UDFs, including cost estimation, predicate evaluation order, and the impact of DETERMINISTIC and NO EXTERNAL ACTION declarations.

SHARE

  • "COBOL Stored Procedures: Lessons from a 10-Year Journey" — A practitioner presentation covering the evolution of stored procedure usage at a large financial institution. Documents the shift from "put everything in stored procedures" to a more nuanced approach based on workload characteristics.

  • "WLM Tuning for Stored Procedures: Beyond NUMTCB" — Covers advanced WLM configuration including service class goals, importance levels, and the interaction between stored procedure address spaces and DB2 buffer pools.

Books

DB2 Developer's Guide by Craig S. Mullins (6th Edition)

Chapters 17-18 cover stored procedures and user-defined functions. Mullins provides practical guidance on when to use each mechanism, with COBOL examples. The section on CPU accounting and chargeback models is particularly relevant to Section 10.5.

DB2 for z/OS Version 12: Everything You Ever Wanted to Know by IBM Press

Chapter 23 covers stored procedures, UDFs, and triggers. The discussion of the interaction between stored procedures and the DB2 optimizer is the most detailed available outside IBM internal documentation.

COBOL for the 21st Century by Stern, Stern, and Ley (12th Edition)

While not specifically about stored procedures, Chapter 19 covers the COBOL language features used in stored procedure development: LINKAGE SECTION, PROCEDURE DIVISION USING, GOBACK, and the interaction between COBOL and DB2 host variables.

Online Resources

IBM Developer Community

  • "DB2 Stored Procedures and UDFs: A Developer's Checklist" — A concise checklist covering the most common deployment failures, parameter mismatches, and WLM configuration errors.

z/OS Hot Topics Newsletter

  • "The Cost of a Stored Procedure Call" — An IBM performance engineer's analysis of the CPU and elapsed time components of a stored procedure call: WLM scheduling, Language Environment initialization, SQL execution, and result marshaling. Essential reading for Section 10.5.
  • Chapter 5: Workload Manager and DB2 — WLM application environments, service classes, and goals. Required reading before deploying any stored procedure.
  • Chapter 6: The DB2 Optimizer — How the optimizer uses UDF attributes (DETERMINISTIC, NO EXTERNAL ACTION, data access level) to make optimization decisions.
  • Chapter 8: Locking and Concurrency — Lock behavior in stored procedures, COMMIT ON RETURN implications, and the interaction between stored procedure isolation and caller isolation levels.
  • Chapter 11: Dynamic SQL in COBOL — How stored procedures can use dynamic SQL internally, and the security implications of combining stored procedures with dynamic SQL.
  • Chapter 14: Triggers — An alternative to stored procedures for enforcing business rules at the database layer. Comparison of triggers vs. stored procedures for different use cases.