Part VII: Application Development Patterns

DB2 does not exist for its own sake. It exists to serve applications. Every table you design, every index you create, every buffer pool you tune — all of it is in service of an application that needs to read data, write data, and do so correctly under concurrent access. If you have spent your career on the DBA side, this part shows you what the developers deal with. If you have spent your career on the development side, this part shows you how to interact with DB2 properly rather than through the fog of an ORM that hides everything important.

The scope here is wide. We start with embedded SQL in COBOL and C — the access method that has been running mission-critical transactions on mainframes for four decades — and we end with REST APIs, GraphQL interfaces, and Kafka-based event streaming. That range is not random. It reflects the actual landscape of DB2 application development in production environments today. Somewhere in your organization, there is a COBOL program with embedded SQL that processes a million transactions a day, and there is also a Python microservice that queries the same database through JDBC. Both patterns are legitimate. Both require specific knowledge to implement well.

What This Part Covers

Four chapters, each addressing a distinct integration paradigm.

Chapter 32 covers embedded SQL and static SQL programming. This is the original DB2 application development model: SQL statements coded directly into host-language programs (COBOL, C, PL/I, FORTRAN, Assembler), preprocessed by the DB2 precompiler, bound into packages and plans, and executed with the stability and predictability that static SQL provides. We cover the precompile-bind-run cycle, host variables, indicator variables, cursor processing, error handling through SQLCODE and SQLSTATE, and the DCLGEN utility that generates host-variable declarations from table definitions. On z/OS, this is still the dominant access method for batch and CICS-based online transaction processing, and understanding it is non-negotiable for anyone working in that environment. We also cover static SQL on LUW through embedded C and the precompiler that LUW provides, though the usage is less common than on z/OS.

Chapter 33 addresses the modern programmatic interfaces: JDBC, ODBC, CLI, and Python (ibm_db and ibm_db_dbi). These are the dynamic SQL interfaces that most distributed applications use. We cover connection management — connection pooling, connection concentration, and the WLM integration that routes connections to appropriate service classes. We cover prepared statements and parameter markers, which are the dynamic SQL equivalent of static SQL's host variables: they prevent SQL injection, enable access path reuse, and are the single most important performance practice in dynamic SQL programming. We work through complete application examples in Java (using JDBC), Python (using ibm_db), and C (using CLI/ODBC), demonstrating transaction management, result set processing, error handling, and batch operations. The examples are not toy programs. They implement real Meridian Bank operations: account balance inquiries, fund transfers with proper commit/rollback logic, and batch statement processing for end-of-day reconciliation.

Chapter 34 goes deep on stored procedures, user-defined functions, and server-side programming. We covered the SQL syntax for these objects in Chapter 12; this chapter focuses on the application architecture patterns they enable. When should logic live in a stored procedure versus application code? How do you design a stored procedure API that is stable enough for multiple applications to depend on? How do you handle error propagation from a stored procedure back to the calling application? How do you debug server-side code? We cover SQL PL (the SQL procedural language) in depth, including control flow, condition handlers, cursors, result sets, temporary tables, and dynamic SQL within procedures. On z/OS, we cover the WLM-managed stored procedure address spaces and the performance characteristics of native SQL procedures versus external procedures. On LUW, we cover the trusted and fenced execution modes and their security implications.

Chapter 35 brings DB2 into the modern application architecture: REST APIs, GraphQL, and event-driven patterns with Apache Kafka. IBM's Db2 REST service allows you to expose stored procedures and SQL statements as RESTful endpoints without writing application server code. We cover the setup, the security model, and the appropriate use cases — along with the scenarios where a custom application server gives you more control. GraphQL integration receives practical treatment: building a GraphQL layer over DB2 that provides flexible querying without the N+1 query problem that plagues naive implementations. The Kafka section covers change data capture (CDC) from DB2 to Kafka topics, enabling event-driven architectures where downstream systems react to database changes in near-real-time. On z/OS, we cover the IBM InfoSphere CDC engine and the z/OS Connect EE framework. On LUW, we cover the Kafka Connect Db2 source connector and the SQL replication infrastructure.

Why It Matters

The most perfectly tuned database in the world is useless if the applications that connect to it are poorly written. I have seen applications that open a new connection for every SQL statement. Applications that use SELECT * when they need two columns. Applications that fetch a million rows to the client and filter them in Java. Applications that build SQL strings through concatenation and wonder why they get hacked. Applications that never commit, holding locks for the duration of an entire user session.

Every one of those patterns causes a problem that eventually lands on the DBA's desk as a "database performance issue." It is not a database issue. It is an application issue. But if you are the DBA, you still need to understand the application-side patterns well enough to diagnose the real cause and explain the fix to the development team.

Conversely, if you are a developer, understanding how DB2 processes your application's requests transforms the quality of your code. When you understand that a prepared statement with parameter markers allows the optimizer to reuse an access path, you stop building dynamic SQL strings. When you understand that an uncommitted transaction holds locks that block other users, you commit early and often. When you understand that fetching rows one at a time across a network incurs per-row overhead, you use block fetch or array fetch. The knowledge flows both ways.

The Spectrum from Mainframe to Microservices

Part VII deliberately spans the full technology timeline. This is not nostalgia for embedded SQL or hype for microservices. It is recognition that production DB2 environments — especially in financial services, insurance, healthcare, and government — contain applications from every era, all running simultaneously and all depending on the same database.

At Meridian National Bank, the core transaction processing runs in COBOL with embedded SQL on z/OS CICS. The digital banking platform is a Java application using JDBC connection pools. The data science team accesses DB2 through Python. The mobile banking API layer is a REST-based service. The fraud detection system consumes a Kafka stream of transaction events sourced from DB2 change data capture.

All of these coexist. All of them interact with the same underlying data. Understanding the full spectrum — not just the piece you work on — makes you effective in the cross-functional conversations where architectural decisions get made.

How to Approach This Part

Your starting point depends on your role and platform.

If you are a z/OS COBOL developer or DBA, start with Chapter 32. Embedded SQL is your daily reality, and the depth we cover here goes beyond what most shops teach internally. Then read Chapter 34 for stored procedures, which are increasingly used on z/OS for modernization. Chapters 33 and 35 give you context for how the distributed side works.

If you are a distributed application developer, start with Chapter 33 (JDBC/ODBC/Python) and Chapter 35 (REST/GraphQL/Kafka). These are your primary interfaces. Then read Chapter 34 for stored procedures, which remain the most efficient way to execute complex multi-statement logic on the server. Chapter 32 gives you context for the mainframe integration patterns you may encounter.

If you are an architect or a full-stack DB2 professional, read all four chapters in order. The progression from embedded SQL to event-driven microservices tells a coherent story about how database access patterns have evolved and where they are heading.

Regardless of your path, build and run the code examples. Every chapter includes working programs you can compile and execute against your Meridian Bank database. Reading code teaches concepts. Running code builds skills.

Chapters in This Part