Chapter 22 covered the fundamentals of COBOL-DB2 programming: host variables, the SQLCA, single-row operations, cursors, indicator variables, and the precompile/bind process. Those fundamentals are sufficient for many batch programs that read data...
In This Chapter
- Introduction: Beyond the Basics
- 23.1 Dynamic SQL
- 23.2 Scrollable Cursors
- 23.3 Multi-Row FETCH and INSERT
- 23.4 COBOL Stored Procedures
- 23.5 User-Defined Functions (UDFs)
- 23.6 Commit Strategies
- 23.7 Locking and Concurrency
- 23.8 Error Handling Patterns for Advanced DB2
- 23.9 Performance Tuning
- 23.9 LOB Handling
- 23.10 Temporal Tables and System-Period Data Versioning
- 23.11 Advanced Cursor Techniques
- 23.12 Complete Advanced DB2 Batch Program
- 23.13 Package Versioning
- Summary
- Exercises
Chapter 23: Advanced DB2 Programming
IBM Enterprise COBOL
Introduction: Beyond the Basics
Chapter 22 covered the fundamentals of COBOL-DB2 programming: host variables, the SQLCA, single-row operations, cursors, indicator variables, and the precompile/bind process. Those fundamentals are sufficient for many batch programs that read data, process it, and write results. But enterprise applications demand more.
A customer service application needs to construct SQL statements dynamically based on user input. A high-volume batch program needs to fetch hundreds of rows at a time to maximize throughput. A complex business process needs to be encapsulated in a stored procedure that multiple applications can call. A nightly batch window needs to process fifty million rows without deadlocking against the online system. A reporting application needs to page forward and backward through a result set.
This chapter covers the advanced DB2 programming techniques that address these requirements: dynamic SQL for runtime query construction, scrollable cursors for bidirectional navigation, multi-row FETCH and INSERT for high-volume performance, stored procedures and user-defined functions for server-side logic, commit strategies and concurrency control for production reliability, performance tuning for batch efficiency, and LOB handling for large data objects.
These topics build directly on the fundamentals from Chapter 22. If you have not read that chapter, do so before continuing.
23.1 Dynamic SQL
In Chapter 22, every SQL statement was written directly in the COBOL source code and was fully known at compile time. This is called static SQL. Dynamic SQL, by contrast, constructs SQL statements at runtime from character strings. The SQL text is not known until the program executes, which provides flexibility at the cost of some complexity and performance.
When to Use Dynamic SQL
Dynamic SQL is appropriate when:
- The table name, column list, or WHERE clause conditions are not known until runtime
- A user or configuration file specifies the query criteria
- The program needs to execute DDL (CREATE, ALTER, DROP) or utility statements
- The same program needs to work with different table structures
Three Forms of Dynamic SQL
DB2 provides three forms of dynamic SQL, each with increasing flexibility and complexity:
- EXECUTE IMMEDIATE: For statements that have no parameter markers and no result set (INSERT, UPDATE, DELETE, DDL).
- PREPARE + EXECUTE: For statements with parameter markers but no result set.
- PREPARE + DECLARE CURSOR + OPEN + FETCH: For statements that return a result set.
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE takes a character string containing a complete SQL statement and executes it immediately:
WORKING-STORAGE SECTION.
01 WS-SQL-STATEMENT PIC X(500).
PROCEDURE DIVISION.
2000-DYNAMIC-UPDATE.
STRING
'UPDATE CUSTOMER'
' SET CUST_STATUS = '
QUOTE 'I' QUOTE
' WHERE LAST_ACTIVITY < '
QUOTE HV-CUTOFF-DATE QUOTE
DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
END-STRING
EXEC SQL
EXECUTE IMMEDIATE :WS-SQL-STATEMENT
END-EXEC
EVALUATE SQLCODE
WHEN 0
DISPLAY 'ROWS UPDATED: ' SQLERRD(3)
WHEN OTHER
DISPLAY 'DYNAMIC SQL ERROR: ' SQLCODE
DISPLAY 'STATEMENT: '
WS-SQL-STATEMENT
PERFORM 9100-SQL-ERROR
END-EVALUATE
.
PREPARE and EXECUTE
When an SQL statement will be executed multiple times with different parameter values, PREPARE converts the SQL text into an executable form once, and EXECUTE runs it with specific parameter values:
WORKING-STORAGE SECTION.
01 WS-SQL-TEXT PIC X(500).
01 HV-NEW-STATUS PIC X(1).
01 HV-CUST-ID PIC S9(9) COMP.
PROCEDURE DIVISION.
1500-PREPARE-UPDATE.
MOVE 'UPDATE CUSTOMER SET CUST_STATUS = ?'
' WHERE CUST_ID = ?'
TO WS-SQL-TEXT
EXEC SQL
PREPARE UPDATE-STMT FROM :WS-SQL-TEXT
END-EXEC
IF SQLCODE NOT = 0
DISPLAY 'PREPARE ERROR: ' SQLCODE
PERFORM 9100-SQL-ERROR
END-IF
.
2000-EXECUTE-UPDATE.
* Set parameter values
MOVE 'I' TO HV-NEW-STATUS
MOVE WS-INPUT-CUST-ID TO HV-CUST-ID
* Execute with parameters replacing ? markers
EXEC SQL
EXECUTE UPDATE-STMT
USING :HV-NEW-STATUS,
:HV-CUST-ID
END-EXEC
EVALUATE SQLCODE
WHEN 0
ADD SQLERRD(3) TO CT-UPDATED
WHEN OTHER
PERFORM 9100-SQL-ERROR
END-EVALUATE
.
Dynamic SELECT with Cursor
For dynamic queries that return rows, combine PREPARE with a cursor:
WORKING-STORAGE SECTION.
01 WS-SQL-QUERY PIC X(1000).
01 WS-WHERE-CLAUSE PIC X(500).
01 WS-ORDER-CLAUSE PIC X(100).
01 HV-CUST-ID PIC S9(9) COMP.
01 HV-CUST-NAME PIC X(30).
01 HV-CUST-BALANCE PIC S9(7)V99 COMP-3.
PROCEDURE DIVISION.
1500-BUILD-QUERY.
* Build the query dynamically based on user criteria
MOVE SPACES TO WS-SQL-QUERY
STRING
'SELECT CUST_ID, CUST_NAME, CUST_BALANCE'
' FROM CUSTOMER'
DELIMITED BY SIZE
INTO WS-SQL-QUERY
END-STRING
* Add WHERE clause based on search criteria
IF WS-SEARCH-BY-STATUS
STRING WS-SQL-QUERY DELIMITED BY ' '
' WHERE CUST_STATUS = ?'
DELIMITED BY SIZE
INTO WS-SQL-QUERY
END-STRING
END-IF
IF WS-SEARCH-BY-REGION
STRING WS-SQL-QUERY DELIMITED BY ' '
' AND CUST_REGION = ?'
DELIMITED BY SIZE
INTO WS-SQL-QUERY
END-STRING
END-IF
* Add ORDER BY
STRING WS-SQL-QUERY DELIMITED BY ' '
' ORDER BY CUST_NAME'
DELIMITED BY SIZE
INTO WS-SQL-QUERY
END-STRING
* Prepare the statement
EXEC SQL
PREPARE DYN-QUERY FROM :WS-SQL-QUERY
END-EXEC
IF SQLCODE NOT = 0
DISPLAY 'PREPARE FAILED: ' SQLCODE
DISPLAY 'QUERY: ' WS-SQL-QUERY
PERFORM 9100-SQL-ERROR
END-IF
* Declare cursor for the prepared statement
EXEC SQL
DECLARE DYN-CURSOR CURSOR FOR DYN-QUERY
END-EXEC
.
2000-EXECUTE-QUERY.
* Open cursor with parameter values
EXEC SQL
OPEN DYN-CURSOR
USING :HV-STATUS-PARM,
:HV-REGION-PARM
END-EXEC
IF SQLCODE NOT = 0
DISPLAY 'OPEN CURSOR ERROR: ' SQLCODE
PERFORM 9100-SQL-ERROR
END-IF
* Fetch loop
PERFORM 2100-FETCH-ROW
PERFORM UNTIL SQLCODE = +100
PERFORM 2200-PROCESS-ROW
PERFORM 2100-FETCH-ROW
END-PERFORM
EXEC SQL CLOSE DYN-CURSOR END-EXEC
.
2100-FETCH-ROW.
EXEC SQL
FETCH DYN-CURSOR
INTO :HV-CUST-ID,
:HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
.
The SQLDA (SQL Descriptor Area)
For truly dynamic queries where even the number and types of result columns are unknown at compile time, you need the SQLDA:
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLDA END-EXEC.
01 WS-SQLDA-PTR USAGE POINTER.
01 WS-NUM-COLUMNS PIC S9(4) COMP.
PROCEDURE DIVISION.
1500-DESCRIBE-QUERY.
EXEC SQL
PREPARE DYN-STMT FROM :WS-SQL-TEXT
END-EXEC
* DESCRIBE returns information about the result columns
EXEC SQL
DESCRIBE DYN-STMT INTO :SQLDA
END-EXEC
MOVE SQLD TO WS-NUM-COLUMNS
DISPLAY 'QUERY RETURNS ' WS-NUM-COLUMNS
' COLUMNS'
.
The SQLDA is an advanced topic primarily used in utility programs and query tools. Most application programs know their column structures at compile time and use the simpler forms of dynamic SQL.
23.2 Scrollable Cursors
Standard cursors move forward only -- each FETCH retrieves the next row. Scrollable cursors allow you to move in any direction through the result set: forward, backward, to a specific position, or to the first or last row.
Declaring a Scrollable Cursor
EXEC SQL
DECLARE SCROLL-CURSOR SCROLL CURSOR FOR
SELECT CUST_ID, CUST_NAME, CUST_BALANCE
FROM CUSTOMER
WHERE CUST_STATUS = :HV-STATUS
ORDER BY CUST_NAME
END-EXEC
The keyword SCROLL after CURSOR enables scrollable functionality.
FETCH Orientations
* Fetch the next row (same as standard cursor)
EXEC SQL
FETCH NEXT SCROLL-CURSOR
INTO :HV-CUST-ID, :HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
* Fetch the previous row
EXEC SQL
FETCH PRIOR SCROLL-CURSOR
INTO :HV-CUST-ID, :HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
* Fetch the first row
EXEC SQL
FETCH FIRST SCROLL-CURSOR
INTO :HV-CUST-ID, :HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
* Fetch the last row
EXEC SQL
FETCH LAST SCROLL-CURSOR
INTO :HV-CUST-ID, :HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
* Fetch an absolute position (row 50)
MOVE 50 TO HV-ROW-POSITION
EXEC SQL
FETCH ABSOLUTE :HV-ROW-POSITION
SCROLL-CURSOR
INTO :HV-CUST-ID, :HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
* Fetch relative to current position (5 rows forward)
MOVE 5 TO HV-ROW-OFFSET
EXEC SQL
FETCH RELATIVE :HV-ROW-OFFSET
SCROLL-CURSOR
INTO :HV-CUST-ID, :HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
Scrollable Cursor for Pagination
A common use case for scrollable cursors is implementing pagination in a reporting or browsing application:
01 WS-PAGE-SIZE PIC S9(4) COMP VALUE 20.
01 WS-CURRENT-PAGE PIC S9(4) COMP VALUE 1.
01 WS-START-ROW PIC S9(9) COMP.
01 WS-ROW-ON-PAGE PIC S9(4) COMP.
2000-DISPLAY-PAGE.
COMPUTE WS-START-ROW =
(WS-CURRENT-PAGE - 1) * WS-PAGE-SIZE + 1
EXEC SQL
FETCH ABSOLUTE :WS-START-ROW
SCROLL-CURSOR
INTO :HV-CUST-ID, :HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
MOVE 0 TO WS-ROW-ON-PAGE
PERFORM UNTIL SQLCODE = +100
OR WS-ROW-ON-PAGE >= WS-PAGE-SIZE
ADD 1 TO WS-ROW-ON-PAGE
DISPLAY WS-ROW-ON-PAGE '. '
HV-CUST-ID ' '
HV-CUST-NAME ' '
HV-CUST-BALANCE
EXEC SQL
FETCH NEXT SCROLL-CURSOR
INTO :HV-CUST-ID, :HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
END-PERFORM
DISPLAY 'PAGE ' WS-CURRENT-PAGE
' (' WS-ROW-ON-PAGE ' ROWS)'
.
SENSITIVE vs. INSENSITIVE
Scrollable cursors can be declared SENSITIVE (sees changes made by other transactions or by the program itself) or INSENSITIVE (sees a snapshot of the data as of cursor open time):
* INSENSITIVE: static snapshot
EXEC SQL
DECLARE SNAP-CURSOR INSENSITIVE SCROLL
CURSOR FOR
SELECT CUST_ID, CUST_NAME FROM CUSTOMER
END-EXEC
* SENSITIVE: sees concurrent changes
EXEC SQL
DECLARE LIVE-CURSOR SENSITIVE SCROLL
CURSOR FOR
SELECT CUST_ID, CUST_NAME FROM CUSTOMER
END-EXEC
23.3 Multi-Row FETCH and INSERT
For high-volume batch processing, fetching or inserting one row at a time is inefficient. Multi-row FETCH retrieves a block of rows in a single operation, and multi-row INSERT sends a block of rows to DB2 in a single operation. Both dramatically reduce the number of interactions between the COBOL program and the DB2 engine.
Multi-Row FETCH
WORKING-STORAGE SECTION.
01 WS-FETCH-SIZE PIC S9(4) COMP VALUE 100.
01 WS-ROWS-FETCHED PIC S9(4) COMP.
01 HV-CUST-ARRAY.
05 HV-CUST-ID-ARR PIC S9(9) COMP
OCCURS 100 TIMES.
05 HV-CUST-NAME-ARR PIC X(30)
OCCURS 100 TIMES.
05 HV-CUST-BAL-ARR PIC S9(7)V99 COMP-3
OCCURS 100 TIMES.
EXEC SQL
DECLARE BULK-CURSOR CURSOR WITH HOLD FOR
SELECT CUST_ID, CUST_NAME, CUST_BALANCE
FROM CUSTOMER
WHERE CUST_STATUS = 'A'
ORDER BY CUST_ID
END-EXEC
PROCEDURE DIVISION.
2000-PROCESS-BULK.
EXEC SQL OPEN BULK-CURSOR END-EXEC
PERFORM UNTIL SQLCODE = +100
EXEC SQL
FETCH BULK-CURSOR
FOR :WS-FETCH-SIZE ROWS
INTO :HV-CUST-ID-ARR,
:HV-CUST-NAME-ARR,
:HV-CUST-BAL-ARR
END-EXEC
IF SQLCODE = 0 OR SQLCODE = +100
MOVE SQLERRD(3) TO WS-ROWS-FETCHED
PERFORM 2100-PROCESS-BLOCK
ELSE
PERFORM 9100-SQL-ERROR
END-IF
END-PERFORM
EXEC SQL CLOSE BULK-CURSOR END-EXEC
.
2100-PROCESS-BLOCK.
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-ROWS-FETCHED
DISPLAY HV-CUST-ID-ARR(WS-IDX) ' '
HV-CUST-NAME-ARR(WS-IDX) ' '
HV-CUST-BAL-ARR(WS-IDX)
ADD 1 TO CT-TOTAL-PROCESSED
END-PERFORM
.
Multi-Row INSERT
WORKING-STORAGE SECTION.
01 WS-INSERT-COUNT PIC S9(4) COMP VALUE 0.
01 WS-BATCH-SIZE PIC S9(4) COMP VALUE 100.
01 HV-INS-ARRAY.
05 HV-INS-ID-ARR PIC S9(9) COMP
OCCURS 100 TIMES.
05 HV-INS-NAME-ARR PIC X(30)
OCCURS 100 TIMES.
05 HV-INS-BAL-ARR PIC S9(7)V99 COMP-3
OCCURS 100 TIMES.
PROCEDURE DIVISION.
2300-BUFFER-INSERT.
ADD 1 TO WS-INSERT-COUNT
MOVE WS-NEW-ID TO HV-INS-ID-ARR(WS-INSERT-COUNT)
MOVE WS-NEW-NAME TO HV-INS-NAME-ARR(WS-INSERT-COUNT)
MOVE WS-NEW-BAL TO HV-INS-BAL-ARR(WS-INSERT-COUNT)
IF WS-INSERT-COUNT >= WS-BATCH-SIZE
PERFORM 2310-FLUSH-INSERT-BUFFER
END-IF
.
2310-FLUSH-INSERT-BUFFER.
IF WS-INSERT-COUNT > 0
EXEC SQL
INSERT INTO CUSTOMER
(CUST_ID, CUST_NAME, CUST_BALANCE)
VALUES (:HV-INS-ID-ARR,
:HV-INS-NAME-ARR,
:HV-INS-BAL-ARR)
FOR :WS-INSERT-COUNT ROWS
END-EXEC
IF SQLCODE NOT = 0
DISPLAY 'MULTI-ROW INSERT ERROR: '
SQLCODE
PERFORM 9100-SQL-ERROR
ELSE
ADD WS-INSERT-COUNT TO CT-INSERTED
MOVE 0 TO WS-INSERT-COUNT
END-IF
END-IF
.
23.4 COBOL Stored Procedures
A stored procedure is a program that runs on the DB2 server (in a WLM-managed address space) and can be called from any DB2 client application. COBOL stored procedures encapsulate business logic on the server side, reducing network traffic and centralizing logic.
Creating a COBOL Stored Procedure
IDENTIFICATION DIVISION.
PROGRAM-ID. CUSTSRCH.
*========================================================*
* STORED PROCEDURE: CUSTSRCH *
* PURPOSE: Search customers by name pattern *
* PARAMETERS: *
* IN: LS-NAME-PATTERN - Search pattern *
* IN: LS-MAX-ROWS - Maximum rows to return *
* OUT: LS-RESULT-COUNT - Number of rows found *
* OUT: LS-RETURN-CODE - 0=success, 8=error *
* OUT: LS-RESULT-SET - Cursor result set *
*========================================================*
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 HV-NAME-PATTERN PIC X(30).
01 HV-MAX-ROWS PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL
DECLARE RESULT-CURSOR CURSOR
WITH RETURN TO CALLER
FOR
SELECT CUST_ID, CUST_NAME, CUST_BALANCE
FROM CUSTOMER
WHERE CUST_NAME LIKE :HV-NAME-PATTERN
ORDER BY CUST_NAME
FETCH FIRST :HV-MAX-ROWS ROWS ONLY
END-EXEC
LINKAGE SECTION.
01 LS-NAME-PATTERN PIC X(30).
01 LS-MAX-ROWS PIC S9(9) COMP.
01 LS-RESULT-COUNT PIC S9(9) COMP.
01 LS-RETURN-CODE PIC S9(9) COMP.
PROCEDURE DIVISION USING LS-NAME-PATTERN
LS-MAX-ROWS
LS-RESULT-COUNT
LS-RETURN-CODE.
0000-MAIN.
MOVE 0 TO LS-RETURN-CODE
MOVE 0 TO LS-RESULT-COUNT
MOVE LS-NAME-PATTERN TO HV-NAME-PATTERN
MOVE LS-MAX-ROWS TO HV-MAX-ROWS
EXEC SQL
OPEN RESULT-CURSOR
END-EXEC
IF SQLCODE = 0
MOVE SQLERRD(3) TO LS-RESULT-COUNT
ELSE
MOVE 8 TO LS-RETURN-CODE
END-IF
GOBACK
.
Registering the Stored Procedure in DB2
CREATE PROCEDURE CUSTSRCH
(IN NAME_PATTERN CHAR(30),
IN MAX_ROWS INTEGER,
OUT RESULT_COUNT INTEGER,
OUT RETURN_CODE INTEGER)
EXTERNAL NAME CUSTSRCH
LANGUAGE COBOL
PARAMETER STYLE GENERAL
RESULT SETS 1
WLM ENVIRONMENT WLMENV1
COLLID CUSTCOLL;
Calling a Stored Procedure from COBOL
2000-SEARCH-CUSTOMERS.
MOVE 'SMITH%' TO HV-SEARCH-PATTERN
MOVE 50 TO HV-MAX-ROWS
EXEC SQL
CALL CUSTSRCH(:HV-SEARCH-PATTERN,
:HV-MAX-ROWS,
:HV-RESULT-COUNT,
:HV-SP-RETURN-CODE)
END-EXEC
EVALUATE SQLCODE
WHEN 0
DISPLAY 'RESULTS: ' HV-RESULT-COUNT
PERFORM 2100-PROCESS-RESULT-SET
WHEN +466
* Result set returned
PERFORM 2100-PROCESS-RESULT-SET
WHEN OTHER
DISPLAY 'CALL ERROR: ' SQLCODE
PERFORM 9100-SQL-ERROR
END-EVALUATE
.
2100-PROCESS-RESULT-SET.
* Associate a cursor with the result set
EXEC SQL
ASSOCIATE LOCATORS (:WS-RS-LOCATOR)
WITH PROCEDURE CUSTSRCH
END-EXEC
EXEC SQL
ALLOCATE RS-CURSOR CURSOR FOR
RESULT SET :WS-RS-LOCATOR
END-EXEC
* Fetch from the result set cursor
PERFORM UNTIL SQLCODE = +100
EXEC SQL
FETCH RS-CURSOR
INTO :HV-CUST-ID,
:HV-CUST-NAME,
:HV-CUST-BALANCE
END-EXEC
IF SQLCODE = 0
DISPLAY HV-CUST-ID ' '
HV-CUST-NAME ' '
HV-CUST-BALANCE
END-IF
END-PERFORM
EXEC SQL CLOSE RS-CURSOR END-EXEC
.
23.5 User-Defined Functions (UDFs)
DB2 user-defined functions extend SQL with custom business logic. A COBOL UDF is a program that DB2 calls during SQL statement execution to compute a value.
Creating a COBOL UDF
IDENTIFICATION DIVISION.
PROGRAM-ID. CALCTAX.
*========================================================*
* UDF: CALCTAX *
* Calculates sales tax based on state and amount *
*========================================================*
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-TAX-RATES.
05 FILLER PIC X(22) VALUE 'CA0725'.
05 FILLER PIC X(22) VALUE 'NY0800'.
05 FILLER PIC X(22) VALUE 'TX0625'.
01 WS-TAX-TABLE REDEFINES WS-TAX-RATES.
05 WS-TAX-ENTRY OCCURS 3 TIMES.
10 WS-TAX-STATE PIC XX.
10 WS-TAX-RATE PIC V9(4).
LINKAGE SECTION.
01 LS-STATE PIC X(2).
01 LS-AMOUNT PIC S9(7)V99 COMP-3.
01 LS-TAX-RESULT PIC S9(7)V99 COMP-3.
01 LS-STATE-IND PIC S9(4) COMP.
01 LS-AMOUNT-IND PIC S9(4) COMP.
01 LS-RESULT-IND PIC S9(4) COMP.
01 LS-SQLSTATE PIC X(5).
01 LS-FUNC-NAME PIC X(139).
01 LS-SPEC-NAME PIC X(128).
01 LS-MSG-TEXT PIC X(70).
PROCEDURE DIVISION USING LS-STATE
LS-AMOUNT
LS-TAX-RESULT
LS-STATE-IND
LS-AMOUNT-IND
LS-RESULT-IND
LS-SQLSTATE
LS-FUNC-NAME
LS-SPEC-NAME
LS-MSG-TEXT.
0000-MAIN.
IF LS-STATE-IND < 0 OR LS-AMOUNT-IND < 0
MOVE -1 TO LS-RESULT-IND
MOVE '00000' TO LS-SQLSTATE
GOBACK
END-IF
MOVE 0 TO LS-RESULT-IND
MOVE 0 TO LS-TAX-RESULT
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > 3
IF WS-TAX-STATE(WS-IDX) = LS-STATE
COMPUTE LS-TAX-RESULT =
LS-AMOUNT * WS-TAX-RATE(WS-IDX)
END-IF
END-PERFORM
MOVE '00000' TO LS-SQLSTATE
GOBACK
.
Registering and Using the UDF
-- Register the UDF
CREATE FUNCTION CALCTAX(STATE CHAR(2), AMOUNT DECIMAL(9,2))
RETURNS DECIMAL(9,2)
EXTERNAL NAME CALCTAX
LANGUAGE COBOL
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC;
-- Use the UDF in SQL
SELECT CUST_ID, CUST_NAME,
ORDER_AMOUNT,
CALCTAX(CUST_STATE, ORDER_AMOUNT) AS TAX_AMOUNT
FROM CUSTOMER_ORDERS
WHERE ORDER_DATE = CURRENT DATE;
23.6 Commit Strategies
In batch programs that process millions of rows, commit strategy is critical. Committing too frequently adds overhead. Committing too infrequently risks losing large amounts of work if the program fails and risks holding locks that block online users.
Periodic Commit Pattern
01 WS-COMMIT-FREQ PIC S9(9) COMP VALUE 1000.
01 WS-SINCE-COMMIT PIC S9(9) COMP VALUE 0.
01 CT-COMMITS PIC S9(7) COMP VALUE 0.
2000-PROCESS-ROW.
* ... process the row ...
ADD 1 TO WS-SINCE-COMMIT
IF WS-SINCE-COMMIT >= WS-COMMIT-FREQ
PERFORM 2500-COMMIT
END-IF
.
2500-COMMIT.
EXEC SQL COMMIT END-EXEC
IF SQLCODE = 0
ADD 1 TO CT-COMMITS
MOVE 0 TO WS-SINCE-COMMIT
DISPLAY 'COMMIT AT RECORD '
CT-TOTAL-PROCESSED
ELSE
DISPLAY 'COMMIT ERROR: ' SQLCODE
PERFORM 9100-SQL-ERROR
END-IF
.
Commit with Restart Capability
For very long-running batch programs, design for restartability. Save a checkpoint key at each commit so that the program can restart from where it left off:
01 WS-CHECKPOINT-KEY PIC S9(9) COMP.
2500-COMMIT-WITH-CHECKPOINT.
MOVE HV-CURRENT-KEY TO WS-CHECKPOINT-KEY
EXEC SQL
UPDATE CHECKPOINT_TABLE
SET LAST_KEY = :WS-CHECKPOINT-KEY,
LAST_COMMIT_TS = CURRENT TIMESTAMP,
RECORDS_PROCESSED = :CT-TOTAL-PROCESSED
WHERE PROGRAM_ID = 'BATCHPGM'
END-EXEC
EXEC SQL COMMIT END-EXEC
IF SQLCODE = 0
ADD 1 TO CT-COMMITS
MOVE 0 TO WS-SINCE-COMMIT
ELSE
PERFORM 9100-SQL-ERROR
END-IF
.
1000-INITIALIZE.
* Check for restart
EXEC SQL
SELECT LAST_KEY
INTO :WS-CHECKPOINT-KEY
FROM CHECKPOINT_TABLE
WHERE PROGRAM_ID = 'BATCHPGM'
END-EXEC
IF SQLCODE = 0 AND WS-CHECKPOINT-KEY > 0
DISPLAY 'RESTARTING FROM KEY: '
WS-CHECKPOINT-KEY
MOVE WS-CHECKPOINT-KEY TO HV-START-KEY
ELSE
MOVE 0 TO HV-START-KEY
END-IF
.
23.7 Locking and Concurrency
When multiple programs access the same DB2 data concurrently, locks prevent conflicts. Understanding isolation levels and handling deadlocks is essential for production programs.
Isolation Levels
| Level | Abbreviation | Behavior |
|---|---|---|
| Uncommitted Read | UR | Reads do not acquire locks. Can see uncommitted data ("dirty reads"). Best throughput. |
| Cursor Stability | CS | Locks the current cursor row only. Releases lock when cursor moves. Default level. |
| Read Stability | RS | Locks all rows that qualify for the query. No phantom reads within the result set. |
| Repeatable Read | RR | Locks all rows examined, including those that do not qualify. Prevents all anomalies. Worst throughput. |
Specifying Isolation Level
Isolation level is set at BIND time or can be overridden per statement:
-- At BIND time:
BIND PACKAGE(CUSTCOLL) MEMBER(CUSTUPDT) ISO(CS)
-- Per statement (DB2 10+):
EXEC SQL
SELECT CUST_NAME, CUST_BALANCE
INTO :HV-CUST-NAME, :HV-CUST-BALANCE
FROM CUSTOMER
WHERE CUST_ID = :HV-CUST-ID
WITH UR
END-EXEC
Deadlock Handling (-911 and -913)
A deadlock occurs when two programs each hold a lock that the other needs. DB2 detects the deadlock and rolls back one of the programs, returning SQLCODE -911 (with automatic rollback) or -913 (timeout without automatic rollback).
Production deadlock retry pattern:
01 WS-RETRY-COUNT PIC 9(3) COMP VALUE 0.
01 WS-MAX-RETRIES PIC 9(3) COMP VALUE 3.
01 WS-DEADLOCK-FLAG PIC 9 VALUE 0.
88 WS-NO-DEADLOCK VALUE 0.
88 WS-DEADLOCK VALUE 1.
2000-UPDATE-WITH-RETRY.
MOVE 0 TO WS-RETRY-COUNT
SET WS-NO-DEADLOCK TO TRUE
PERFORM 2100-ATTEMPT-UPDATE
PERFORM UNTIL WS-NO-DEADLOCK
OR WS-RETRY-COUNT >= WS-MAX-RETRIES
ADD 1 TO WS-RETRY-COUNT
DISPLAY 'DEADLOCK RETRY ' WS-RETRY-COUNT
' OF ' WS-MAX-RETRIES
* Wait before retrying (use LE callable service)
CALL 'CEE3DLY' USING WS-DELAY-SECONDS
WS-FC
END-CALL
PERFORM 2100-ATTEMPT-UPDATE
END-PERFORM
IF WS-DEADLOCK AND
WS-RETRY-COUNT >= WS-MAX-RETRIES
DISPLAY 'DEADLOCK - MAX RETRIES EXCEEDED'
PERFORM 9100-SQL-ERROR
END-IF
.
2100-ATTEMPT-UPDATE.
SET WS-NO-DEADLOCK TO TRUE
EXEC SQL
UPDATE CUSTOMER
SET CUST_BALANCE = CUST_BALANCE
+ :HV-TRANS-AMOUNT
WHERE CUST_ID = :HV-CUST-ID
END-EXEC
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN -911
* Deadlock with automatic rollback
SET WS-DEADLOCK TO TRUE
WHEN -913
* Timeout, no automatic rollback
SET WS-DEADLOCK TO TRUE
EXEC SQL ROLLBACK END-EXEC
WHEN OTHER
PERFORM 9100-SQL-ERROR
END-EVALUATE
.
23.8 Error Handling Patterns for Advanced DB2
Advanced DB2 programming introduces error conditions that do not arise in simple static SQL programs. This section covers error handling strategies specific to the advanced features covered in this chapter.
Dynamic SQL Error Handling
Dynamic SQL introduces errors that static SQL cannot produce, because the SQL text is not validated until runtime. SQLCODE -204 (object not defined), -206 (column not found), and -104 (syntax error) are common when SQL is constructed from user input or configuration data:
9200-DYNAMIC-SQL-ERROR.
DISPLAY '*** DYNAMIC SQL ERROR ***'
DISPLAY 'SQLCODE: ' SQLCODE
DISPLAY 'SQLSTATE: ' SQLSTATE
DISPLAY 'SQL TEXT: ' WS-SQL-STATEMENT
EVALUATE SQLCODE
WHEN -104
DISPLAY 'CAUSE: SQL SYNTAX ERROR'
DISPLAY 'CHECK GENERATED SQL STATEMENT'
WHEN -204
DISPLAY 'CAUSE: TABLE OR VIEW NOT FOUND'
DISPLAY 'CHECK TABLE NAME PARAMETER'
WHEN -206
DISPLAY 'CAUSE: COLUMN NOT FOUND'
DISPLAY 'CHECK COLUMN NAME PARAMETERS'
WHEN -551
DISPLAY 'CAUSE: AUTHORIZATION FAILURE'
DISPLAY 'USER LACKS EXECUTE PRIVILEGE'
WHEN OTHER
DISPLAY 'CAUSE: SEE DB2 MESSAGES MANUAL'
END-EVALUATE
MOVE 16 TO RETURN-CODE
PERFORM 9900-ABORT
.
Stored Procedure Error Handling
When calling stored procedures, errors can occur at multiple levels: the CALL itself might fail (SQLCODE -471 for stored procedure not found), the stored procedure might detect a business logic error and return a non-zero return code, or the stored procedure might encounter an SQL error internally:
2050-CALL-SP-WITH-HANDLING.
EXEC SQL
CALL CUSTSRCH(:HV-PATTERN,
:HV-MAX-ROWS,
:HV-RESULT-COUNT,
:HV-SP-RC)
END-EXEC
EVALUATE SQLCODE
WHEN 0
IF HV-SP-RC = 0
PERFORM 2060-PROCESS-RESULTS
ELSE
DISPLAY 'SP RETURNED ERROR RC='
HV-SP-RC
PERFORM 2070-HANDLE-SP-ERROR
END-IF
WHEN +466
* Result set returned (normal for SP with
* cursor)
PERFORM 2060-PROCESS-RESULTS
WHEN -471
DISPLAY 'STORED PROCEDURE NOT FOUND'
MOVE 12 TO RETURN-CODE
WHEN -904
DISPLAY 'RESOURCE UNAVAILABLE - SP'
MOVE 12 TO RETURN-CODE
WHEN OTHER
DISPLAY 'CALL ERROR: ' SQLCODE
PERFORM 9100-SQL-ERROR
END-EVALUATE
.
Multi-Row Operation Error Handling
Multi-row FETCH and INSERT require special error handling because a partial batch might succeed. When SQLCODE is +100 on a multi-row FETCH, some rows were fetched (check SQLERRD(3) for the count). When a multi-row INSERT fails, SQLERRD(3) tells you how many rows were successfully inserted before the error:
2320-CHECK-MULTI-ROW-INSERT.
EVALUATE SQLCODE
WHEN 0
ADD WS-INSERT-COUNT TO CT-INSERTED
DISPLAY 'BATCH INSERT COMPLETE: '
WS-INSERT-COUNT ' ROWS'
WHEN -803
* Duplicate key in the batch
DISPLAY 'DUPLICATE KEY IN BATCH'
DISPLAY 'ROWS INSERTED BEFORE ERROR: '
SQLERRD(3)
ADD SQLERRD(3) TO CT-INSERTED
ADD 1 TO CT-DUPLICATES
* Remaining rows in the batch were not inserted
PERFORM 2330-RETRY-REMAINING-ROWS
WHEN OTHER
DISPLAY 'MULTI-ROW INSERT ERROR: '
SQLCODE
DISPLAY 'ROWS INSERTED: ' SQLERRD(3)
PERFORM 9100-SQL-ERROR
END-EVALUATE
.
Timeout Prevention Strategies
In high-concurrency environments, timeout errors (-913) can occur even without true deadlocks. Strategies to prevent timeouts include ordering your updates by primary key (so that concurrent programs lock rows in the same sequence), using SKIP LOCKED DATA to skip rows that are currently locked by another transaction, and keeping transactions short:
* SKIP LOCKED DATA: Skip rows that are locked
* instead of waiting for the lock (DB2 10+)
EXEC SQL
DECLARE SKIP-CURSOR CURSOR FOR
SELECT CUST_ID, CUST_NAME, CUST_BALANCE
FROM CUSTOMER
WHERE CUST_STATUS = 'P'
FOR UPDATE OF CUST_STATUS
SKIP LOCKED DATA
END-EXEC
The SKIP LOCKED DATA clause causes the cursor to silently skip over any rows that are currently locked by another transaction. This eliminates lock waits entirely at the cost of potentially incomplete processing -- the skipped rows must be processed by a subsequent run of the program.
Connection Error Recovery
In distributed DB2 environments or when accessing remote databases, connection errors can occur mid-transaction. SQLCODE -30081 (communication failure) requires reconnection and transaction restart logic:
9300-HANDLE-COMMUNICATION-ERROR.
DISPLAY 'COMMUNICATION ERROR: ' SQLCODE
DISPLAY 'ATTEMPTING RECONNECTION...'
EXEC SQL ROLLBACK END-EXEC
* Wait before retry
CALL 'CEE3DLY' USING WS-RECONNECT-DELAY
WS-FC
END-CALL
* The application may need to re-establish the
* connection and restart the unit of work
SET WS-NEEDS-RESTART TO TRUE
.
23.9 Performance Tuning
Understanding EXPLAIN
The EXPLAIN statement shows how DB2 will access data for a given query. Understanding the access path is crucial for performance tuning:
* Run EXPLAIN on a statement
EXEC SQL
EXPLAIN PLAN SET QUERYNO = 100 FOR
SELECT CUST_ID, CUST_NAME, CUST_BALANCE
FROM CUSTOMER
WHERE CUST_STATUS = 'A'
AND CUST_REGION = :HV-REGION
ORDER BY CUST_NAME
END-EXEC
The results are stored in the PLAN_TABLE, which can be queried to understand the access path chosen by DB2's optimizer.
Index Usage Best Practices
-
Ensure predicates match index columns: If you have an index on (CUST_STATUS, CUST_REGION), include both columns in your WHERE clause for best index utilization.
-
Avoid expressions on indexed columns:
* BAD: Function on column prevents index usage
EXEC SQL
SELECT ...
FROM CUSTOMER
WHERE SUBSTR(CUST_NAME, 1, 3) = 'SMI'
END-EXEC
* GOOD: Predicate allows index range scan
EXEC SQL
SELECT ...
FROM CUSTOMER
WHERE CUST_NAME LIKE 'SMI%'
END-EXEC
- Use parameter markers instead of literals for better plan reuse with dynamic SQL.
BIND Options for Performance
| BIND Option | Values | Performance Impact |
|---|---|---|
| ISOLATION | UR, CS, RS, RR | UR is fastest (no read locks), RR is slowest |
| CURRENTDATA | YES, NO | NO allows DB2 to avoid extra I/O for currency |
| DEGREE | 1, ANY | ANY allows query parallelism |
| REOPT | NONE, ALWAYS, ONCE | Controls when DB2 re-optimizes for parameter values |
| RELEASE | COMMIT, DEALLOCATE | DEALLOCATE holds pages in buffer pool longer |
Batch Performance Patterns
* Use multi-row FETCH for read-heavy batch programs
* Use COMMIT frequency of 500-5000 rows
* Use WITH HOLD cursors when committing during cursor processing
* Use isolation level UR for read-only reports
* Use OPTIMIZE FOR n ROWS on cursors that process limited rows
EXEC SQL
DECLARE BATCH-CURSOR CURSOR WITH HOLD FOR
SELECT CUST_ID, CUST_NAME, CUST_BALANCE
FROM CUSTOMER
WHERE CUST_STATUS = 'A'
ORDER BY CUST_ID
OPTIMIZE FOR 1000 ROWS
END-EXEC
23.9 LOB Handling
Large Objects (LOBs) store data that exceeds the normal column size limits: BLOB for binary data (images, documents), CLOB for character data (large text), and DBCLOB for double-byte character data.
Declaring LOB Host Variables
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 HV-DOC-ID PIC S9(9) COMP.
* CLOB host variable
01 HV-DOCUMENT.
05 HV-DOC-LENGTH PIC S9(9) COMP.
05 HV-DOC-DATA PIC X(32000).
* LOB locator (for very large LOBs)
01 HV-LOB-LOCATOR USAGE SQL TYPE IS
CLOB-LOCATOR.
EXEC SQL END DECLARE SECTION END-EXEC.
Reading a LOB Value
2300-READ-DOCUMENT.
EXEC SQL
SELECT DOC_ID, DOC_CONTENT
INTO :HV-DOC-ID, :HV-DOCUMENT
FROM DOCUMENTS
WHERE DOC_ID = :HV-SEARCH-ID
END-EXEC
IF SQLCODE = 0
DISPLAY 'DOCUMENT ID: ' HV-DOC-ID
DISPLAY 'LENGTH: ' HV-DOC-LENGTH
DISPLAY 'FIRST 80 CHARS: '
HV-DOC-DATA(1:80)
END-IF
.
Using LOB Locators for Large LOBs
For LOBs that are too large to fit in WORKING-STORAGE, use LOB locators. A locator is a token that references the LOB value in DB2 without bringing the data into the program:
2400-PROCESS-LARGE-LOB.
* Get a locator for the LOB
EXEC SQL
SELECT DOC_CONTENT
INTO :HV-LOB-LOCATOR
FROM DOCUMENTS
WHERE DOC_ID = :HV-SEARCH-ID
END-EXEC
* Get the length of the LOB
EXEC SQL
VALUES(LENGTH(:HV-LOB-LOCATOR))
INTO :HV-LOB-LENGTH
END-EXEC
DISPLAY 'LOB LENGTH: ' HV-LOB-LENGTH
* Read a portion of the LOB (first 1000 bytes)
EXEC SQL
VALUES(SUBSTR(:HV-LOB-LOCATOR, 1, 1000))
INTO :HV-LOB-CHUNK
END-EXEC
* Free the locator when done
EXEC SQL
FREE LOCATOR :HV-LOB-LOCATOR
END-EXEC
.
23.10 Temporal Tables and System-Period Data Versioning
DB2 for z/OS (version 10 and later) supports temporal tables, which automatically maintain historical versions of rows. When you update or delete a row, DB2 saves the previous version in a history table. This is invaluable for auditing, regulatory compliance, and point-in-time reporting.
Querying Historical Data from COBOL
* Query the current data (default behavior)
EXEC SQL
SELECT CUST_NAME, CUST_BALANCE
INTO :HV-CUST-NAME, :HV-CUST-BALANCE
FROM CUSTOMER
WHERE CUST_ID = :HV-CUST-ID
END-EXEC
* Query the data as it was on a specific date
EXEC SQL
SELECT CUST_NAME, CUST_BALANCE
INTO :HV-CUST-NAME, :HV-CUST-BALANCE
FROM CUSTOMER
FOR SYSTEM_TIME AS OF :HV-QUERY-TIMESTAMP
WHERE CUST_ID = :HV-CUST-ID
END-EXEC
* Query all versions of a row over a time period
EXEC SQL
DECLARE HIST-CURSOR CURSOR FOR
SELECT CUST_NAME, CUST_BALANCE,
SYS_START, SYS_END
FROM CUSTOMER
FOR SYSTEM_TIME FROM :HV-START-TS
TO :HV-END-TS
WHERE CUST_ID = :HV-CUST-ID
ORDER BY SYS_START
END-EXEC
Temporal queries allow COBOL programs to answer questions like "What was this customer's balance on December 31st?" without requiring the application to maintain its own audit trail. DB2 handles the versioning transparently.
23.11 Advanced Cursor Techniques
Cursor Sensitivity and Positioned Operations
When a cursor is used for both reading and updating, the interaction between the cursor's sensitivity and the update can produce unexpected results. Understanding these interactions is essential for correct behavior:
* A cursor that processes rows and updates them
* must be carefully designed to avoid reprocessing
* updated rows or skipping rows
* PATTERN: Process and update with ordered cursor
EXEC SQL
DECLARE PROCESS-CURSOR CURSOR WITH HOLD FOR
SELECT CUST_ID, CUST_BALANCE, CUST_STATUS
FROM CUSTOMER
WHERE CUST_STATUS = 'P'
ORDER BY CUST_ID
FOR UPDATE OF CUST_STATUS, CUST_BALANCE
END-EXEC
2000-PROCESS-PENDING.
EXEC SQL OPEN PROCESS-CURSOR END-EXEC
MOVE 0 TO WS-SINCE-COMMIT
PERFORM UNTIL SQLCODE = +100
EXEC SQL
FETCH PROCESS-CURSOR
INTO :HV-CUST-ID,
:HV-CUST-BALANCE,
:HV-CUST-STATUS
END-EXEC
IF SQLCODE = 0
PERFORM 2100-APPLY-BUSINESS-RULES
EXEC SQL
UPDATE CUSTOMER
SET CUST_STATUS = 'A',
CUST_BALANCE = :HV-NEW-BALANCE,
LAST_UPDATE = CURRENT TIMESTAMP
WHERE CURRENT OF PROCESS-CURSOR
END-EXEC
ADD 1 TO CT-PROCESSED
ADD 1 TO WS-SINCE-COMMIT
IF WS-SINCE-COMMIT >= WS-COMMIT-FREQ
PERFORM 2500-COMMIT
END-IF
END-IF
END-PERFORM
EXEC SQL CLOSE PROCESS-CURSOR END-EXEC
.
Cursor Stability and Ambiguous Cursors
An ambiguous cursor is one where the cursor's result set could be affected by the cursor's own updates. For example, if a cursor selects rows WHERE STATUS = 'P' and the program updates STATUS to 'A' for each row, the cursor might or might not re-encounter the updated row depending on the access path and isolation level. The ORDER BY clause helps prevent this ambiguity by establishing a stable processing order.
Using GET DIAGNOSTICS
The GET DIAGNOSTICS statement provides more detailed information about the last SQL statement than SQLCODE and SQLCA alone:
01 HV-ROW-COUNT PIC S9(9) COMP.
01 HV-DIAG-SQLCODE PIC S9(9) COMP.
01 HV-DIAG-MESSAGE PIC X(500).
9150-GET-DIAGNOSTICS.
EXEC SQL
GET DIAGNOSTICS
:HV-ROW-COUNT = ROW_COUNT
END-EXEC
DISPLAY 'ROWS AFFECTED: ' HV-ROW-COUNT
EXEC SQL
GET DIAGNOSTICS CONDITION 1
:HV-DIAG-SQLCODE = DB2_SQLD_STMTINFO,
:HV-DIAG-MESSAGE = MESSAGE_TEXT
END-EXEC
DISPLAY 'DIAGNOSTIC MESSAGE: '
HV-DIAG-MESSAGE
.
23.12 Complete Advanced DB2 Batch Program
The following program demonstrates many of the advanced techniques covered in this chapter in a single cohesive example. It processes a large customer file, applies interest calculations, uses multi-row FETCH for performance, commits periodically with checkpoint/restart capability, and includes deadlock retry logic:
IDENTIFICATION DIVISION.
PROGRAM-ID. INTCALC.
*========================================================*
* PROGRAM: INTCALC *
* PURPOSE: Calculate and apply monthly interest to all *
* active customer accounts *
* DB2: CUSTOMER table (read/update) *
* CHECKPOINT_TABLE (checkpoint/restart) *
*========================================================*
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 HV-CUST-ID-ARR PIC S9(9) COMP
OCCURS 100 TIMES.
01 HV-CUST-BAL-ARR PIC S9(7)V99 COMP-3
OCCURS 100 TIMES.
01 HV-CUST-RATE-ARR PIC S9(3)V9(4) COMP-3
OCCURS 100 TIMES.
01 HV-FETCH-SIZE PIC S9(4) COMP VALUE 100.
01 HV-SINGLE-ID PIC S9(9) COMP.
01 HV-NEW-BALANCE PIC S9(7)V99 COMP-3.
01 HV-INTEREST-AMT PIC S9(7)V99 COMP-3.
01 HV-CHECKPOINT-KEY PIC S9(9) COMP.
01 HV-RECORDS-DONE PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL
DECLARE INT-CURSOR CURSOR WITH HOLD FOR
SELECT CUST_ID, CUST_BALANCE, INTEREST_RATE
FROM CUSTOMER
WHERE CUST_STATUS = 'A'
AND CUST_ID > :HV-CHECKPOINT-KEY
ORDER BY CUST_ID
OPTIMIZE FOR 100 ROWS
END-EXEC
01 WS-ROWS-FETCHED PIC S9(4) COMP.
01 WS-IDX PIC S9(4) COMP.
01 WS-COMMIT-FREQ PIC S9(5) COMP VALUE 1000.
01 WS-SINCE-COMMIT PIC S9(5) COMP VALUE 0.
01 CT-PROCESSED PIC S9(9) COMP VALUE 0.
01 CT-COMMITS PIC S9(5) COMP VALUE 0.
01 CT-DEADLOCKS PIC S9(5) COMP VALUE 0.
01 WS-RETRY-COUNT PIC S9(3) COMP.
01 WS-MAX-RETRIES PIC S9(3) COMP VALUE 3.
01 WS-DEADLOCK-FLAG PIC 9.
88 WS-NO-DEADLOCK VALUE 0.
88 WS-DEADLOCK VALUE 1.
01 WS-RESTART-FLAG PIC 9 VALUE 0.
88 WS-FRESH-START VALUE 0.
88 WS-RESTARTING VALUE 1.
PROCEDURE DIVISION.
0000-MAIN.
PERFORM 1000-INITIALIZE
PERFORM 2000-PROCESS-BATCH
PERFORM 3000-TERMINATE
STOP RUN
.
1000-INITIALIZE.
MOVE 0 TO HV-CHECKPOINT-KEY
EXEC SQL
SELECT LAST_KEY, RECORDS_PROCESSED
INTO :HV-CHECKPOINT-KEY, :HV-RECORDS-DONE
FROM CHECKPOINT_TABLE
WHERE PROGRAM_ID = 'INTCALC'
END-EXEC
IF SQLCODE = 0 AND HV-CHECKPOINT-KEY > 0
SET WS-RESTARTING TO TRUE
MOVE HV-RECORDS-DONE TO CT-PROCESSED
DISPLAY 'RESTART FROM KEY: '
HV-CHECKPOINT-KEY
DISPLAY 'PREVIOUSLY PROCESSED: '
CT-PROCESSED
ELSE
SET WS-FRESH-START TO TRUE
DISPLAY 'STARTING FRESH RUN'
END-IF
EXEC SQL OPEN INT-CURSOR END-EXEC
IF SQLCODE NOT = 0
DISPLAY 'CURSOR OPEN ERROR: ' SQLCODE
MOVE 16 TO RETURN-CODE
STOP RUN
END-IF
.
2000-PROCESS-BATCH.
PERFORM UNTIL SQLCODE = +100
EXEC SQL
FETCH INT-CURSOR
FOR :HV-FETCH-SIZE ROWS
INTO :HV-CUST-ID-ARR,
:HV-CUST-BAL-ARR,
:HV-CUST-RATE-ARR
END-EXEC
IF SQLCODE = 0 OR SQLCODE = +100
MOVE SQLERRD(3) TO WS-ROWS-FETCHED
PERFORM 2100-PROCESS-BLOCK
ELSE
DISPLAY 'FETCH ERROR: ' SQLCODE
PERFORM 9100-SQL-ERROR
END-IF
END-PERFORM
.
2100-PROCESS-BLOCK.
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-ROWS-FETCHED
MOVE HV-CUST-ID-ARR(WS-IDX) TO HV-SINGLE-ID
COMPUTE HV-INTEREST-AMT =
HV-CUST-BAL-ARR(WS-IDX) *
HV-CUST-RATE-ARR(WS-IDX) / 12
COMPUTE HV-NEW-BALANCE =
HV-CUST-BAL-ARR(WS-IDX) + HV-INTEREST-AMT
PERFORM 2200-UPDATE-WITH-RETRY
ADD 1 TO CT-PROCESSED
ADD 1 TO WS-SINCE-COMMIT
IF WS-SINCE-COMMIT >= WS-COMMIT-FREQ
MOVE HV-SINGLE-ID TO HV-CHECKPOINT-KEY
PERFORM 2500-COMMIT-CHECKPOINT
END-IF
END-PERFORM
.
2200-UPDATE-WITH-RETRY.
MOVE 0 TO WS-RETRY-COUNT
SET WS-NO-DEADLOCK TO TRUE
PERFORM 2210-ATTEMPT-UPDATE
PERFORM UNTIL WS-NO-DEADLOCK
OR WS-RETRY-COUNT >= WS-MAX-RETRIES
ADD 1 TO WS-RETRY-COUNT
ADD 1 TO CT-DEADLOCKS
PERFORM 2210-ATTEMPT-UPDATE
END-PERFORM
IF WS-DEADLOCK
DISPLAY 'DEADLOCK UNRESOLVED FOR CUST: '
HV-SINGLE-ID
PERFORM 9100-SQL-ERROR
END-IF
.
2210-ATTEMPT-UPDATE.
SET WS-NO-DEADLOCK TO TRUE
EXEC SQL
UPDATE CUSTOMER
SET CUST_BALANCE = :HV-NEW-BALANCE,
LAST_UPDATE = CURRENT TIMESTAMP
WHERE CUST_ID = :HV-SINGLE-ID
END-EXEC
EVALUATE SQLCODE
WHEN 0 CONTINUE
WHEN -911 SET WS-DEADLOCK TO TRUE
WHEN -913
SET WS-DEADLOCK TO TRUE
EXEC SQL ROLLBACK END-EXEC
WHEN OTHER
PERFORM 9100-SQL-ERROR
END-EVALUATE
.
2500-COMMIT-CHECKPOINT.
MOVE CT-PROCESSED TO HV-RECORDS-DONE
EXEC SQL
UPDATE CHECKPOINT_TABLE
SET LAST_KEY = :HV-CHECKPOINT-KEY,
LAST_COMMIT_TS = CURRENT TIMESTAMP,
RECORDS_PROCESSED = :HV-RECORDS-DONE
WHERE PROGRAM_ID = 'INTCALC'
END-EXEC
EXEC SQL COMMIT END-EXEC
IF SQLCODE = 0
ADD 1 TO CT-COMMITS
MOVE 0 TO WS-SINCE-COMMIT
END-IF
.
3000-TERMINATE.
PERFORM 2500-COMMIT-CHECKPOINT
EXEC SQL CLOSE INT-CURSOR END-EXEC
DISPLAY '================================'
DISPLAY 'INTCALC PROCESSING SUMMARY'
DISPLAY '================================'
DISPLAY 'ACCOUNTS PROCESSED: ' CT-PROCESSED
DISPLAY 'COMMITS: ' CT-COMMITS
DISPLAY 'DEADLOCKS: ' CT-DEADLOCKS
DISPLAY '================================'
MOVE 0 TO RETURN-CODE
.
9100-SQL-ERROR.
DISPLAY '*** SQL ERROR ***'
DISPLAY 'SQLCODE: ' SQLCODE
DISPLAY 'SQLSTATE: ' SQLSTATE
EXEC SQL ROLLBACK END-EXEC
MOVE 16 TO RETURN-CODE
STOP RUN
.
23.13 Package Versioning
In production environments, you need the ability to deploy new versions of DB2 packages without disrupting running applications. DB2 package versioning allows multiple versions of a package to coexist.
Binding with a Version
BIND PACKAGE(CUSTCOLL) -
MEMBER(CUSTUPDT) -
ACT(ADD) -
VERSION(V2025.06.15) -
VALIDATE(BIND)
Managing Package Versions
-- List all versions of a package
SELECT COLLID, NAME, VERSION, BINDTIME
FROM SYSIBM.SYSPACKAGE
WHERE NAME = 'CUSTUPDT'
ORDER BY BINDTIME DESC;
-- Free an old version
FREE PACKAGE(CUSTCOLL.CUSTUPDT.V2024.01.01);
Version Strategy for Production
A common version strategy uses the deployment date as the version identifier:
- Development: BIND with VERSION(DEV) using ACT(REP)
- QA: BIND with VERSION(QA-yyyy.mm.dd) using ACT(ADD)
- Production: BIND with VERSION(yyyy.mm.dd) using ACT(ADD)
- Cleanup: FREE old versions after confirming the new version is stable
Summary
This chapter covered advanced DB2 programming techniques that are essential for enterprise COBOL-DB2 applications:
- Dynamic SQL provides runtime flexibility through EXECUTE IMMEDIATE (for DDL and simple DML), PREPARE+EXECUTE (for parameterized statements), and PREPARE+CURSOR (for dynamic queries with result sets). The SQLDA enables fully dynamic queries with unknown column structures.
- Scrollable cursors add bidirectional navigation with FETCH NEXT, PRIOR, FIRST, LAST, ABSOLUTE, and RELATIVE orientations, enabling pagination and random-access patterns.
- Multi-row FETCH and INSERT improve batch performance by exchanging blocks of rows in a single operation, reducing the overhead of individual row processing.
- Stored procedures encapsulate server-side business logic in COBOL programs that DB2 manages and executes, providing reusability across applications and reducing network traffic.
- User-defined functions extend SQL with custom COBOL logic that can be used directly in SELECT, WHERE, and other SQL clauses.
- Commit strategies balance data integrity against performance, with periodic commits, checkpoint/restart capability, and WITH HOLD cursors for batch processing.
- Locking and concurrency management through isolation levels (UR, CS, RS, RR) and deadlock retry patterns (for SQLCODE -911 and -913) ensure production reliability.
- Performance tuning through EXPLAIN analysis, index-aware query design, appropriate BIND options, and batch-optimized patterns (OPTIMIZE FOR n ROWS, multi-row operations).
- LOB handling with host variables and LOB locators manages large binary and character data.
- Package versioning enables safe production deployments by maintaining multiple package versions simultaneously.
These advanced techniques transform COBOL-DB2 programming from basic data access into high-performance, production-grade enterprise application development.
Exercises
-
Dynamic SQL: Write a program that accepts a table name and a WHERE clause as input parameters, dynamically constructs a SELECT COUNT(*) statement, executes it, and displays the row count. Handle SQL errors including invalid table names.
-
Multi-Row FETCH: Modify a batch program from Chapter 22 to use multi-row FETCH with an array size of 100. Compare the logical flow to the single-row version.
-
Deadlock Retry: Write a program that updates customer balances and includes a deadlock retry mechanism that retries up to 3 times with a 2-second delay between retries.
-
Stored Procedure: Design a COBOL stored procedure that accepts a customer ID and returns the customer's total transaction amount for the current month, the number of transactions, and the average transaction amount. Write both the stored procedure COBOL code and the CALL statement from a client program.
-
Commit Strategy: Write a batch program that reads a file of 100,000 transactions and updates a DB2 table. Implement a configurable commit frequency (default 1000), WITH HOLD cursor, and checkpoint/restart capability. Test with commit frequencies of 100, 1000, and 10000 to understand the tradeoffs.