Case Study 1: Dynamic Report Generator
Background
Continental Savings Bank operates a regional branch network with 85 branches across four states. The operations department generates dozens of account-level reports daily -- portfolio summaries, dormant account lists, overdraft reports, high-balance alerts, and regulatory compliance extracts. For decades, each report was a separate COBOL/DB2 batch program. Over time, the bank accumulated over 120 report programs, each with its own embedded SQL, formatting logic, and JCL.
The maintenance burden became unsustainable. When a column was added to the ACCOUNTS table, 40 programs needed modification. When the compliance team requested a new report, the development queue was six weeks long for what amounted to a straightforward SELECT with different WHERE criteria and column selections.
The solution: build a Dynamic Report Generator -- a single COBOL/DB2 program that constructs SQL statements at runtime based on a report definition file. Instead of writing new COBOL programs for each report, analysts define report specifications in a control file, and the generator does the rest.
Project Requirements
Functional Requirements
The Dynamic Report Generator (program name: DYNRPTG) must:
- Read a report definition from a sequential file that specifies the table(s), columns, filters, sort order, and formatting options.
- Construct a valid SELECT statement dynamically from the definition.
- PREPARE and EXECUTE the dynamic SQL using parameter markers for filter values.
- Use DESCRIBE to determine result set metadata when column types are not predefined.
- Format output into a paginated report with headers, detail lines, and totals.
- Support optional GROUP BY aggregation with subtotals.
- Commit periodically during large reports to avoid holding locks.
Report Definition Format
Each report definition is a sequential file with tagged records:
REPORT-ID=DORM-ACCT-30
TITLE=Dormant Accounts - No Activity 30+ Days
SUBTITLE=Generated for Operations Review
TABLE=ACCOUNTS A
JOIN=CUSTOMERS C ON A.CUST_ID = C.CUST_ID
COLUMN=A.ACCT_ID|Account ID|CHAR|10|L
COLUMN=C.CUST_NAME|Customer Name|CHAR|30|L
COLUMN=A.ACCT_TYPE|Type|CHAR|2|L
COLUMN=A.BALANCE|Current Balance|DECIMAL|13.2|R
COLUMN=A.LAST_ACTIVITY|Last Activity|DATE|10|L
FILTER=A.STATUS|=|A
FILTER=A.LAST_ACTIVITY|<|?
FILTER=A.BRANCH_CODE|=|?
ORDERBY=A.BRANCH_CODE,A.ACCT_ID
GROUPBY=A.BRANCH_CODE
PAGESIZE=60
The ? markers in FILTER lines indicate values supplied at runtime through a parameter file.
Design Approach
Architecture Overview
The program is structured into four major phases:
Phase 1: Parse Definition Phase 2: Build SQL
+-------------------+ +-------------------+
| Read definition | | Construct SELECT |
| file records |------>| with columns, |
| Build internal | | JOINs, WHERE, |
| structures | | ORDER BY |
+-------------------+ +-------------------+
|
Phase 4: Format Output Phase 3: Execute SQL
+-------------------+ +-------------------+
| Page headers | | PREPARE statement |
| Detail lines |<------| DESCRIBE columns |
| Subtotals/totals | | OPEN cursor, FETCH|
+-------------------+ +-------------------+
Data Structures
The WORKING-STORAGE holds parsed report metadata, the dynamically built SQL statement, and result buffers:
WORKING-STORAGE SECTION.
*---------------------------------------------------------
* Report definition structures
*---------------------------------------------------------
01 WS-REPORT-DEF.
05 WS-REPORT-ID PIC X(20).
05 WS-REPORT-TITLE PIC X(80).
05 WS-REPORT-SUBTITLE PIC X(80).
05 WS-TABLE-NAME PIC X(50).
05 WS-JOIN-CLAUSE PIC X(200).
05 WS-ORDERBY-CLAUSE PIC X(200).
05 WS-GROUPBY-CLAUSE PIC X(100).
05 WS-PAGE-SIZE PIC 9(03) VALUE 60.
01 WS-COLUMN-TABLE.
05 WS-COLUMN-COUNT PIC 9(02) VALUE 0.
05 WS-COLUMN-DEF OCCURS 20 TIMES.
10 WS-COL-EXPRESSION PIC X(50).
10 WS-COL-HEADER PIC X(30).
10 WS-COL-TYPE PIC X(08).
10 WS-COL-LENGTH PIC 9(05).
10 WS-COL-SCALE PIC 9(02).
10 WS-COL-ALIGN PIC X(01).
88 COL-ALIGN-LEFT VALUE 'L'.
88 COL-ALIGN-RIGHT VALUE 'R'.
01 WS-FILTER-TABLE.
05 WS-FILTER-COUNT PIC 9(02) VALUE 0.
05 WS-FILTER-DEF OCCURS 10 TIMES.
10 WS-FLT-COLUMN PIC X(30).
10 WS-FLT-OPERATOR PIC X(05).
10 WS-FLT-VALUE PIC X(50).
10 WS-FLT-IS-PARM PIC X(01).
88 FLT-USES-PARM VALUE 'Y'.
*---------------------------------------------------------
* Dynamic SQL construction area
*---------------------------------------------------------
01 WS-SQL-STATEMENT PIC X(4000).
01 WS-SQL-LENGTH PIC S9(04) COMP.
*---------------------------------------------------------
* Parameter marker host variables
*---------------------------------------------------------
01 WS-PARM-COUNT PIC 9(02) VALUE 0.
01 WS-PARM-VALUES.
05 WS-PARM-VAL OCCURS 10 TIMES
PIC X(50).
*---------------------------------------------------------
* Result set buffers (max 20 columns)
*---------------------------------------------------------
01 WS-RESULT-BUFFER.
05 WS-RES-COL OCCURS 20 TIMES
PIC X(50).
01 WS-IND-BUFFER.
05 WS-IND-COL OCCURS 20 TIMES
PIC S9(04) COMP.
*---------------------------------------------------------
* Report formatting
*---------------------------------------------------------
01 WS-PAGE-NUMBER PIC 9(04) VALUE 0.
01 WS-LINE-COUNT PIC 9(03) VALUE 99.
01 WS-ROW-COUNT PIC 9(09) VALUE 0.
01 WS-REPORT-LINE PIC X(132).
01 WS-CURRENT-DATE-TIME.
05 WS-RPT-DATE PIC X(10).
05 WS-RPT-TIME PIC X(08).
*---------------------------------------------------------
* Subtotal accumulators for numeric columns
*---------------------------------------------------------
01 WS-SUBTOTALS.
05 WS-SUB-TOTAL OCCURS 20 TIMES
PIC S9(13)V99 COMP-3.
01 WS-GRAND-TOTALS.
05 WS-GRD-TOTAL OCCURS 20 TIMES
PIC S9(15)V99 COMP-3.
01 WS-PREV-GROUP-KEY PIC X(50).
01 WS-CURR-GROUP-KEY PIC X(50).
01 WS-GROUP-COUNT PIC 9(07) VALUE 0.
*---------------------------------------------------------
* SQLCA for error handling
*---------------------------------------------------------
EXEC SQL INCLUDE SQLCA END-EXEC
Phase 1: Parsing the Report Definition
The definition parser reads tagged records and populates the internal structures:
1000-PARSE-DEFINITION.
OPEN INPUT DEF-FILE
READ DEF-FILE INTO WS-DEF-RECORD
AT END SET DEF-EOF TO TRUE
END-READ
PERFORM UNTIL DEF-EOF
EVALUATE TRUE
WHEN WS-DEF-RECORD(1:10) = 'REPORT-ID='
MOVE WS-DEF-RECORD(11:20)
TO WS-REPORT-ID
INSPECT WS-REPORT-ID
REPLACING TRAILING SPACES
BY LOW-VALUES
WHEN WS-DEF-RECORD(1:6) = 'TITLE='
MOVE WS-DEF-RECORD(7:80)
TO WS-REPORT-TITLE
WHEN WS-DEF-RECORD(1:9) = 'SUBTITLE='
MOVE WS-DEF-RECORD(10:80)
TO WS-REPORT-SUBTITLE
WHEN WS-DEF-RECORD(1:6) = 'TABLE='
MOVE WS-DEF-RECORD(7:50)
TO WS-TABLE-NAME
WHEN WS-DEF-RECORD(1:5) = 'JOIN='
MOVE WS-DEF-RECORD(6:200)
TO WS-JOIN-CLAUSE
WHEN WS-DEF-RECORD(1:7) = 'COLUMN='
ADD 1 TO WS-COLUMN-COUNT
PERFORM 1100-PARSE-COLUMN
WHEN WS-DEF-RECORD(1:7) = 'FILTER='
ADD 1 TO WS-FILTER-COUNT
PERFORM 1200-PARSE-FILTER
WHEN WS-DEF-RECORD(1:8) = 'ORDERBY='
MOVE WS-DEF-RECORD(9:200)
TO WS-ORDERBY-CLAUSE
WHEN WS-DEF-RECORD(1:8) = 'GROUPBY='
MOVE WS-DEF-RECORD(9:100)
TO WS-GROUPBY-CLAUSE
WHEN WS-DEF-RECORD(1:9) = 'PAGESIZE='
MOVE WS-DEF-RECORD(10:3)
TO WS-PAGE-SIZE
END-EVALUATE
READ DEF-FILE INTO WS-DEF-RECORD
AT END SET DEF-EOF TO TRUE
END-READ
END-PERFORM
CLOSE DEF-FILE
.
1100-PARSE-COLUMN.
* Parse pipe-delimited column definition:
* EXPRESSION|HEADER|TYPE|LENGTH|ALIGN
UNSTRING WS-DEF-RECORD(8:)
DELIMITED BY '|'
INTO WS-COL-EXPRESSION(WS-COLUMN-COUNT)
WS-COL-HEADER(WS-COLUMN-COUNT)
WS-COL-TYPE(WS-COLUMN-COUNT)
WS-COL-LENGTH(WS-COLUMN-COUNT)
WS-COL-ALIGN(WS-COLUMN-COUNT)
END-UNSTRING
.
1200-PARSE-FILTER.
* Parse pipe-delimited filter definition:
* COLUMN|OPERATOR|VALUE
UNSTRING WS-DEF-RECORD(8:)
DELIMITED BY '|'
INTO WS-FLT-COLUMN(WS-FILTER-COUNT)
WS-FLT-OPERATOR(WS-FILTER-COUNT)
WS-FLT-VALUE(WS-FILTER-COUNT)
END-UNSTRING
IF WS-FLT-VALUE(WS-FILTER-COUNT) = '?'
SET FLT-USES-PARM(WS-FILTER-COUNT) TO TRUE
ADD 1 TO WS-PARM-COUNT
END-IF
.
Phase 2: Building the SQL Statement
The SQL builder concatenates the parsed elements into a complete SELECT statement, substituting parameter markers for runtime filter values:
2000-BUILD-SQL.
INITIALIZE WS-SQL-STATEMENT
MOVE 0 TO WS-SQL-LENGTH
* Build SELECT clause
STRING 'SELECT ' DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-COLUMN-COUNT
IF WS-IDX > 1
STRING ', ' DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
END-IF
STRING WS-COL-EXPRESSION(WS-IDX)
DELIMITED BY ' '
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
END-PERFORM
* Build FROM clause
STRING ' FROM ' DELIMITED BY SIZE
WS-TABLE-NAME DELIMITED BY ' '
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
* Build JOIN clause if present
IF WS-JOIN-CLAUSE NOT = SPACES
STRING ' JOIN ' DELIMITED BY SIZE
WS-JOIN-CLAUSE DELIMITED BY ' '
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
END-IF
* Build WHERE clause from filters
IF WS-FILTER-COUNT > 0
STRING ' WHERE ' DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-FILTER-COUNT
IF WS-IDX > 1
STRING ' AND ' DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
END-IF
STRING WS-FLT-COLUMN(WS-IDX)
DELIMITED BY ' '
' ' DELIMITED BY SIZE
WS-FLT-OPERATOR(WS-IDX)
DELIMITED BY ' '
' ' DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
IF FLT-USES-PARM(WS-IDX)
STRING '?' DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
ELSE
STRING '''' DELIMITED BY SIZE
WS-FLT-VALUE(WS-IDX)
DELIMITED BY ' '
'''' DELIMITED BY SIZE
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
END-IF
END-PERFORM
END-IF
* Build ORDER BY clause if present
IF WS-ORDERBY-CLAUSE NOT = SPACES
STRING ' ORDER BY ' DELIMITED BY SIZE
WS-ORDERBY-CLAUSE DELIMITED BY ' '
INTO WS-SQL-STATEMENT
WITH POINTER WS-SQL-LENGTH
END-STRING
END-IF
.
Phase 3: Preparing and Executing the Dynamic SQL
With the SQL statement constructed, the program uses PREPARE to compile it, DESCRIBE to learn the result set structure, and then opens a cursor:
3000-PREPARE-AND-EXECUTE.
* Prepare the dynamic statement
EXEC SQL
PREPARE RPT-STMT FROM :WS-SQL-STATEMENT
END-EXEC
IF SQLCODE NOT = 0
DISPLAY 'PREPARE failed. SQLCODE=' SQLCODE
DISPLAY 'SQL: ' WS-SQL-STATEMENT
PERFORM 9000-SQL-ERROR
STOP RUN
END-IF
* Declare cursor for the prepared statement
EXEC SQL
DECLARE CSR-REPORT CURSOR WITH HOLD FOR
RPT-STMT
END-EXEC
* Open cursor with parameter marker values
EVALUATE WS-PARM-COUNT
WHEN 0
EXEC SQL
OPEN CSR-REPORT
END-EXEC
WHEN 1
EXEC SQL
OPEN CSR-REPORT
USING :WS-PARM-VAL(1)
END-EXEC
WHEN 2
EXEC SQL
OPEN CSR-REPORT
USING :WS-PARM-VAL(1),
:WS-PARM-VAL(2)
END-EXEC
WHEN 3
EXEC SQL
OPEN CSR-REPORT
USING :WS-PARM-VAL(1),
:WS-PARM-VAL(2),
:WS-PARM-VAL(3)
END-EXEC
END-EVALUATE
IF SQLCODE NOT = 0
DISPLAY 'OPEN CURSOR failed. SQLCODE='
SQLCODE
PERFORM 9000-SQL-ERROR
STOP RUN
END-IF
.
3100-FETCH-AND-FORMAT.
PERFORM UNTIL SQLCODE = +100
PERFORM 3200-FETCH-ROW
IF SQLCODE = 0
ADD 1 TO WS-ROW-COUNT
PERFORM 3300-CHECK-GROUP-BREAK
PERFORM 4000-FORMAT-DETAIL-LINE
PERFORM 3400-ACCUMULATE-TOTALS
IF FUNCTION MOD(WS-ROW-COUNT, 1000) = 0
EXEC SQL COMMIT END-EXEC
END-IF
END-IF
END-PERFORM
* Print final subtotal if grouping is active
IF WS-GROUPBY-CLAUSE NOT = SPACES
PERFORM 4200-PRINT-SUBTOTAL
END-IF
PERFORM 4300-PRINT-GRAND-TOTAL
EXEC SQL CLOSE CSR-REPORT END-EXEC
EXEC SQL COMMIT END-EXEC
.
3200-FETCH-ROW.
* Fetch into generic result buffers
* For simplicity, this example fetches into
* character buffers. A production version would
* use the SQLDA with typed buffers.
EVALUATE WS-COLUMN-COUNT
WHEN 4
EXEC SQL
FETCH CSR-REPORT
INTO :WS-RES-COL(1) :WS-IND-COL(1),
:WS-RES-COL(2) :WS-IND-COL(2),
:WS-RES-COL(3) :WS-IND-COL(3),
:WS-RES-COL(4) :WS-IND-COL(4)
END-EXEC
WHEN 5
EXEC SQL
FETCH CSR-REPORT
INTO :WS-RES-COL(1) :WS-IND-COL(1),
:WS-RES-COL(2) :WS-IND-COL(2),
:WS-RES-COL(3) :WS-IND-COL(3),
:WS-RES-COL(4) :WS-IND-COL(4),
:WS-RES-COL(5) :WS-IND-COL(5)
END-EXEC
WHEN OTHER
DISPLAY 'Unsupported column count: '
WS-COLUMN-COUNT
MOVE +100 TO SQLCODE
END-EVALUATE
.
Phase 4: Report Formatting
The formatter produces page headers, detail lines with proper alignment, and group subtotals:
4000-FORMAT-DETAIL-LINE.
IF WS-LINE-COUNT >= WS-PAGE-SIZE
PERFORM 4100-PRINT-PAGE-HEADER
END-IF
INITIALIZE WS-REPORT-LINE
MOVE 1 TO WS-POS
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-COLUMN-COUNT
IF WS-IND-COL(WS-IDX) < 0
MOVE '(null)' TO WS-FORMATTED-VAL
ELSE
EVALUATE WS-COL-TYPE(WS-IDX)
WHEN 'DECIMAL'
PERFORM 4010-FORMAT-DECIMAL
WHEN 'DATE'
MOVE WS-RES-COL(WS-IDX)
TO WS-FORMATTED-VAL
WHEN OTHER
MOVE WS-RES-COL(WS-IDX)
TO WS-FORMATTED-VAL
END-EVALUATE
END-IF
MOVE WS-FORMATTED-VAL TO
WS-REPORT-LINE(WS-POS:
WS-COL-LENGTH(WS-IDX))
ADD WS-COL-LENGTH(WS-IDX) TO WS-POS
ADD 2 TO WS-POS
END-PERFORM
WRITE RPT-RECORD FROM WS-REPORT-LINE
ADD 1 TO WS-LINE-COUNT
.
4010-FORMAT-DECIMAL.
* Convert character-represented decimal to
* formatted currency display
MOVE WS-RES-COL(WS-IDX) TO WS-WORK-NUM
MOVE WS-WORK-NUM TO WS-FORMATTED-CURRENCY
STRING '$' DELIMITED BY SIZE
WS-FORMATTED-CURRENCY DELIMITED BY ' '
INTO WS-FORMATTED-VAL
END-STRING
.
4100-PRINT-PAGE-HEADER.
ADD 1 TO WS-PAGE-NUMBER
IF WS-PAGE-NUMBER > 1
WRITE RPT-RECORD FROM WS-FORM-FEED
END-IF
MOVE SPACES TO WS-REPORT-LINE
STRING '=' DELIMITED BY SIZE
INTO WS-REPORT-LINE
END-STRING
INSPECT WS-REPORT-LINE REPLACING
FIRST ' ' BY '='
MOVE ALL '=' TO WS-REPORT-LINE
WRITE RPT-RECORD FROM WS-REPORT-LINE
MOVE SPACES TO WS-REPORT-LINE
MOVE WS-REPORT-TITLE TO WS-REPORT-LINE
WRITE RPT-RECORD FROM WS-REPORT-LINE
IF WS-REPORT-SUBTITLE NOT = SPACES
MOVE SPACES TO WS-REPORT-LINE
MOVE WS-REPORT-SUBTITLE TO WS-REPORT-LINE
WRITE RPT-RECORD FROM WS-REPORT-LINE
END-IF
MOVE SPACES TO WS-REPORT-LINE
STRING 'Date: ' DELIMITED BY SIZE
WS-RPT-DATE DELIMITED BY SIZE
' Page: ' DELIMITED BY SIZE
INTO WS-REPORT-LINE
END-STRING
MOVE WS-PAGE-NUMBER TO WS-REPORT-LINE(30:4)
WRITE RPT-RECORD FROM WS-REPORT-LINE
MOVE ALL '=' TO WS-REPORT-LINE
WRITE RPT-RECORD FROM WS-REPORT-LINE
* Write column headers
INITIALIZE WS-REPORT-LINE
MOVE 1 TO WS-POS
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-COLUMN-COUNT
MOVE WS-COL-HEADER(WS-IDX) TO
WS-REPORT-LINE(WS-POS:
WS-COL-LENGTH(WS-IDX))
ADD WS-COL-LENGTH(WS-IDX) TO WS-POS
ADD 2 TO WS-POS
END-PERFORM
WRITE RPT-RECORD FROM WS-REPORT-LINE
* Write header underlines
INITIALIZE WS-REPORT-LINE
MOVE 1 TO WS-POS
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-COLUMN-COUNT
MOVE ALL '-' TO WS-UNDERLINE-SEGMENT
MOVE WS-UNDERLINE-SEGMENT TO
WS-REPORT-LINE(WS-POS:
WS-COL-LENGTH(WS-IDX))
ADD WS-COL-LENGTH(WS-IDX) TO WS-POS
ADD 2 TO WS-POS
END-PERFORM
WRITE RPT-RECORD FROM WS-REPORT-LINE
MOVE 6 TO WS-LINE-COUNT
.
4200-PRINT-SUBTOTAL.
MOVE SPACES TO WS-REPORT-LINE
STRING 'Subtotal for '
DELIMITED BY SIZE
WS-PREV-GROUP-KEY
DELIMITED BY ' '
': '
DELIMITED BY SIZE
INTO WS-REPORT-LINE
END-STRING
MOVE 1 TO WS-POS
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-COLUMN-COUNT
IF WS-COL-TYPE(WS-IDX) = 'DECIMAL'
MOVE WS-SUB-TOTAL(WS-IDX)
TO WS-FORMATTED-CURRENCY
END-IF
ADD WS-COL-LENGTH(WS-IDX) TO WS-POS
ADD 2 TO WS-POS
END-PERFORM
WRITE RPT-RECORD FROM WS-REPORT-LINE
MOVE SPACES TO WS-REPORT-LINE
WRITE RPT-RECORD FROM WS-REPORT-LINE
ADD 2 TO WS-LINE-COUNT
INITIALIZE WS-SUBTOTALS
MOVE 0 TO WS-GROUP-COUNT
.
4300-PRINT-GRAND-TOTAL.
MOVE SPACES TO WS-REPORT-LINE
MOVE ALL '=' TO WS-REPORT-LINE
WRITE RPT-RECORD FROM WS-REPORT-LINE
MOVE SPACES TO WS-REPORT-LINE
STRING 'GRAND TOTAL Rows: '
DELIMITED BY SIZE
INTO WS-REPORT-LINE
END-STRING
MOVE WS-ROW-COUNT TO WS-REPORT-LINE(20:9)
WRITE RPT-RECORD FROM WS-REPORT-LINE
PERFORM VARYING WS-IDX FROM 1 BY 1
UNTIL WS-IDX > WS-COLUMN-COUNT
IF WS-COL-TYPE(WS-IDX) = 'DECIMAL'
MOVE SPACES TO WS-REPORT-LINE
STRING WS-COL-HEADER(WS-IDX)
DELIMITED BY ' '
' Total: '
DELIMITED BY SIZE
INTO WS-REPORT-LINE
END-STRING
MOVE WS-GRD-TOTAL(WS-IDX)
TO WS-FORMATTED-CURRENCY
MOVE WS-FORMATTED-CURRENCY
TO WS-REPORT-LINE(45:20)
WRITE RPT-RECORD FROM WS-REPORT-LINE
END-IF
END-PERFORM
MOVE ALL '=' TO WS-REPORT-LINE
WRITE RPT-RECORD FROM WS-REPORT-LINE
.
JCL for Execution
The JCL supplies the report definition file, parameter values, and output dataset:
//DYNRPTG JOB (ACCT),'DYNAMIC REPORT',CLASS=A,
// MSGCLASS=X,NOTIFY=&SYSUID
//*
//STEP01 EXEC PGM=DYNRPTG
//STEPLIB DD DSN=BANK.PROD.LOADLIB,DISP=SHR
//DEFFILE DD DSN=BANK.REPORTS.DEFS(DORMACCT),DISP=SHR
//PARMFILE DD *
2025-11-01
BR001
/*
//RPTOUT DD DSN=BANK.REPORTS.OUTPUT(DORMACCT),
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(5,2)),
// DCB=(RECFM=FBA,LRECL=133,BLKSIZE=0)
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
Error Handling
The program implements centralized SQL error handling that captures the full SQLCA diagnostics:
9000-SQL-ERROR.
DISPLAY '============================================'
DISPLAY 'SQL ERROR DETECTED'
DISPLAY '============================================'
DISPLAY 'SQLCODE : ' SQLCODE
DISPLAY 'SQLERRM : ' SQLERRMC
DISPLAY 'SQLERRD(1): ' SQLERRD(1)
DISPLAY 'SQLERRD(2): ' SQLERRD(2)
DISPLAY 'SQLERRD(3): ' SQLERRD(3)
DISPLAY 'SQLWARN0 : ' SQLWARN0
DISPLAY 'SQLSTATE : ' SQLSTATE
DISPLAY '============================================'
DISPLAY 'SQL STATEMENT:'
DISPLAY WS-SQL-STATEMENT(1:80)
IF WS-SQL-LENGTH > 80
DISPLAY WS-SQL-STATEMENT(81:80)
END-IF
DISPLAY '============================================'
.
Performance Considerations
Dynamic SQL Caching
Each time DYNRPTG runs, it PREPAREs one SQL statement. The PREPARE cost is paid once per execution, and DB2's dynamic statement cache will retain the access path if the same report definition is run again with the same SQL text. Only the parameter marker values change between runs, so the cache hit rate for recurring reports is high.
Commit Frequency
The program commits every 1,000 rows to release locks acquired during the cursor fetch. Because the cursor is declared WITH HOLD, it remains open across commits. Without this periodic commit, a report scanning 500,000 rows would hold read locks on every page touched -- potentially blocking online transactions.
Multi-Row FETCH Enhancement
For large reports, the program could be enhanced to use multi-row FETCH with ROWSET positioning. Instead of fetching one row at a time, fetching 100 rows per DB2 call reduces the number of calls from 500,000 to 5,000 -- a significant reduction in path length and elapsed time.
Results and Lessons Learned
After deploying DYNRPTG, Continental Savings Bank reduced their report program inventory from 120 programs to 30 (the remaining 30 had complex logic that could not be driven by a simple definition file). New report requests that previously required a six-week development cycle are now fulfilled in one to two days by writing a definition file.
The key lessons from the project:
-
Dynamic SQL is powerful but requires discipline. The SQL builder must properly handle quoting, parameter markers, and edge cases (empty filter values, NULL handling) to avoid SQL injection and syntax errors.
-
PREPARE once, EXECUTE many. Even though DYNRPTG only executes each report SQL once per run, the dynamic statement cache benefits recurring batch schedules. Daily reports that run with the same SQL text but different date parameters get cache hits on the PREPARE.
-
Parameter markers are non-negotiable for user-supplied values. Early prototypes concatenated filter values directly into the SQL string. A code review caught this as a SQL injection risk, and the design was corrected to use parameter markers for all runtime-supplied values.
-
Commit frequently in batch reports. The original design committed only at the end. In production, a 300,000-row report held locks for 12 minutes, causing timeouts for online tellers. Adding COMMIT every 1,000 rows with a WITH HOLD cursor eliminated the contention.
-
DESCRIBE enables true generality. By using DESCRIBE to learn column types at runtime, the generator handles any table structure without compile-time knowledge. This was the key insight that made a single program sufficient for dozens of different report layouts.