Chapter 23: Advanced DB2 Programming -- Key Takeaways

Chapter Summary

Advanced DB2 programming techniques enable COBOL programs to handle the demanding data access patterns found in high-volume enterprise applications. This chapter built on the embedded SQL fundamentals from Chapter 22 to cover dynamic SQL, scrollable cursors, stored procedures written in COBOL, multi-row FETCH with ROWSET positioning, large object (LOB) handling, and the DB2 package and plan architecture. We also addressed SQL performance tuning strategies that are essential for programs processing millions of rows in batch or serving thousands of concurrent online users.

Dynamic SQL allows COBOL programs to construct and execute SQL statements at runtime rather than having them fixed at precompile time. This capability is essential for programs that must adapt their queries based on user input, configuration parameters, or runtime conditions. We examined the four forms of dynamic SQL: EXECUTE IMMEDIATE for non-SELECT statements, PREPARE and EXECUTE for parameterized non-SELECT statements, PREPARE with DECLARE CURSOR for dynamic queries, and DESCRIBE for discovering the structure of a dynamic result set through the SQLDA (SQL Descriptor Area).

Scrollable cursors extend the basic forward-only cursor model by allowing the program to move forward, backward, or to an absolute or relative position within a result set. Multi-row FETCH retrieves multiple rows in a single call, dramatically reducing the number of roundtrips between the COBOL program and the DB2 subsystem. We also covered COBOL stored procedures, which encapsulate DB2 logic on the database server, reducing network traffic and enabling shared business logic. The chapter concluded with LOB handling for columns containing large text or binary data, the architecture of DB2 packages and plans, and practical techniques for tuning SQL performance including EXPLAIN analysis, index utilization, and predicate optimization.

Key Concepts

Dynamic SQL constructs SQL statements at runtime using character strings, enabling programs to adapt queries based on runtime conditions rather than fixing all SQL at precompile time.
EXECUTE IMMEDIATE processes a complete SQL statement contained in a host variable; it is limited to non-SELECT statements such as INSERT, UPDATE, DELETE, and DDL.
PREPARE parses and optimizes a dynamic SQL statement and stores the result in a statement name; EXECUTE then runs the prepared statement with optional parameter markers.
Parameter markers (question marks) in a prepared statement serve as placeholders for host variable values supplied at EXECUTE time through the USING clause.
Dynamic cursors combine PREPARE with DECLARE CURSOR to execute dynamically constructed SELECT statements and FETCH results row by row.
The SQLDA (SQL Descriptor Area) describes the structure of a dynamic result set, including the number, types, and lengths of columns, enabling programs to handle result sets whose structure is not known at compile time.
Scrollable cursors, declared with the SCROLL keyword, support FETCH NEXT, FETCH PRIOR, FETCH FIRST, FETCH LAST, FETCH ABSOLUTE n, and FETCH RELATIVE n.
Multi-row FETCH retrieves a ROWSET of multiple rows in a single FETCH operation into host variable arrays, significantly improving performance for bulk data retrieval.
COBOL stored procedures are COBOL programs registered in the DB2 catalog and invoked via the SQL CALL statement; they execute on the DB2 server and can contain embedded SQL.
Stored procedures receive and return parameters through the LINKAGE SECTION and PROCEDURE DIVISION USING clause, and can return result sets through WITH RETURN cursors.
LOB (Large Object) data types include CLOB (character large object), BLOB (binary large object), and DBCLOB (double-byte character large object); COBOL handles them through LOB locators or materialized host variables.
A DB2 package contains the optimized access paths for the SQL statements in a single DBRM; a plan can contain one or more packages and represents the unit of authorization for a DB2 application.
EXPLAIN captures the access path chosen by the DB2 optimizer for each SQL statement and stores it in PLAN_TABLE, enabling performance analysis of query execution strategies.
Index utilization, predicate pushdown, stage 1 versus stage 2 predicates, and join method selection are key factors in SQL performance tuning.

Common Pitfalls

SQL injection in dynamic SQL: Constructing dynamic SQL by concatenating user input directly into the SQL string creates security vulnerabilities. Always use parameter markers and the USING clause to pass variable values.
Excessive dynamic SQL overhead: Dynamic SQL requires runtime parsing and optimization. If the same statement is executed repeatedly, PREPARE it once and EXECUTE it multiple times rather than using EXECUTE IMMEDIATE in a loop.
Ignoring SQLCODE after PREPARE: A PREPARE can fail due to syntax errors in the dynamic SQL string. Always check SQLCODE after PREPARE to detect malformed statements.
Scrollable cursor performance assumptions: Scrollable cursors may require DB2 to materialize the entire result set in a work file. For very large result sets, this can consume significant temporary storage and degrade performance.
Multi-row FETCH array sizing: Declaring host variable arrays that are too large wastes memory, while arrays that are too small reduce the performance benefit. Typical ROWSET sizes of 100 to 1000 rows provide a good balance.
Stored procedure parameter mismatches: The number, order, and data types of parameters in the COBOL stored procedure must exactly match the procedure definition in the DB2 catalog. Mismatches cause runtime errors.
LOB memory consumption: Materializing a large LOB into a COBOL host variable can consume enormous amounts of working storage. Use LOB locators for LOBs that only need to be passed to other SQL operations without being fully read into COBOL memory.
Neglecting EXPLAIN analysis: Assuming that SQL is efficient without running EXPLAIN leads to programs that perform well in development with small data volumes but fail catastrophically in production with full-scale data.

Quick Reference

      * Dynamic SQL - EXECUTE IMMEDIATE
       01  WS-SQL-STMT      PIC X(500).
           STRING "DELETE FROM CUSTOMER "
                  "WHERE STATUS = 'I'"
               DELIMITED SIZE INTO WS-SQL-STMT
           EXEC SQL
               EXECUTE IMMEDIATE :WS-SQL-STMT
           END-EXEC

      * Dynamic SQL - PREPARE and EXECUTE
           MOVE "UPDATE CUSTOMER SET BALANCE = ? "
             &  "WHERE CUST_ID = ?"
               TO WS-SQL-STMT
           EXEC SQL
               PREPARE STMT1 FROM :WS-SQL-STMT
           END-EXEC
           EXEC SQL
               EXECUTE STMT1
               USING :HV-NEW-BAL, :HV-CUST-ID
           END-EXEC

      * Scrollable cursor
           EXEC SQL
               DECLARE CSR-SCROLL SCROLL CURSOR FOR
               SELECT CUST_ID, CUST_NAME
               FROM CUSTOMER
               ORDER BY CUST_NAME
           END-EXEC
           EXEC SQL OPEN CSR-SCROLL END-EXEC
           EXEC SQL
               FETCH FIRST CSR-SCROLL
               INTO :HV-CUST-ID, :HV-CUST-NAME
           END-EXEC
           EXEC SQL
               FETCH RELATIVE +5 CSR-SCROLL
               INTO :HV-CUST-ID, :HV-CUST-NAME
           END-EXEC

      * Multi-row FETCH with ROWSET
       01  HV-CUST-IDS.
           05  HV-ID          PIC X(10)
                               OCCURS 100 TIMES.
           EXEC SQL
               DECLARE CSR-BULK CURSOR
               WITH ROWSET POSITIONING FOR
               SELECT CUST_ID FROM CUSTOMER
           END-EXEC
           EXEC SQL OPEN CSR-BULK END-EXEC
           EXEC SQL
               FETCH NEXT ROWSET FROM CSR-BULK
               FOR 100 ROWS
               INTO :HV-ID
           END-EXEC

      * Calling a stored procedure
           EXEC SQL
               CALL PROC_UPDATE_CUST
                    (:HV-CUST-ID,
                     :HV-CUST-NAME,
                     :HV-RETURN-CODE)
           END-EXEC

What's Next

Chapter 24 introduces CICS transaction processing fundamentals, which represent the other major pillar of enterprise COBOL programming alongside DB2. You will learn how COBOL programs interact with CICS to provide online transaction processing, including the EXEC CICS command interface, pseudo-conversational programming, BMS screen maps, and the COMMAREA communication mechanism. Many production COBOL applications combine CICS for online user interaction with DB2 for data persistence, making both skill sets essential for enterprise developers.