Chapter 23 Exercises: Advanced DB2 Programming

Tier 1: Comprehension (Exercises 1-8)

Exercise 1: Dynamic SQL Classification

Classify each of the following SQL operations as static SQL or dynamic SQL. Explain why each classification applies and state whether PREPARE/EXECUTE is needed.

      * (a) Fixed query compiled with DBRM
       EXEC SQL
           SELECT ACCT_BALANCE
             INTO :WS-BALANCE
             FROM ACCOUNTS
            WHERE ACCT_ID = :WS-ACCT-ID
       END-EXEC

      * (b) SQL text assembled at runtime
       STRING 'SELECT ACCT_BALANCE FROM ACCOUNTS'
              ' WHERE BRANCH_CODE = '''
              WS-BRANCH
              ''''
              DELIMITED BY SIZE
              INTO WS-SQL-STMT
       END-STRING
       EXEC SQL
           PREPARE STMT1 FROM :WS-SQL-STMT
       END-EXEC
       EXEC SQL
           EXECUTE STMT1
       END-EXEC

      * (c) EXECUTE IMMEDIATE for DDL
       MOVE 'GRANT SELECT ON ACCOUNTS TO AUDITOR'
         TO WS-SQL-STMT
       EXEC SQL
           EXECUTE IMMEDIATE :WS-SQL-STMT
       END-EXEC

      * (d) Parameterized dynamic SQL
       MOVE 'SELECT CUST_NAME FROM CUSTOMERS WHERE REGION = ?'
         TO WS-SQL-STMT
       EXEC SQL
           PREPARE STMT2 FROM :WS-SQL-STMT
       END-EXEC
       EXEC SQL
           EXECUTE STMT2 USING :WS-REGION
       END-EXEC

Exercise 2: SQLCODE Interpretation

For each SQLCODE value below, state the meaning and describe the appropriate COBOL response the program should take. Indicate which are warnings, which are errors, and which are normal.

  1. SQLCODE = 0
  2. SQLCODE = 100
  3. SQLCODE = -803
  4. SQLCODE = -811
  5. SQLCODE = -904
  6. SQLCODE = -911
  7. SQLCODE = +100
  8. SQLCODE = -180

Exercise 3: Scrollable Cursor Basics

Given the following scrollable cursor declaration, write the FETCH statements needed to perform each operation listed below.

       EXEC SQL
           DECLARE CSR-TRANSACTIONS SCROLL CURSOR FOR
             SELECT TRANS_ID, TRANS_DATE, TRANS_AMOUNT,
                    TRANS_TYPE, DESCRIPTION
               FROM TRANSACTION_HISTORY
              WHERE ACCT_ID = :WS-ACCT-ID
              ORDER BY TRANS_DATE DESC
       END-EXEC

Write the FETCH for each: 1. Retrieve the first row 2. Retrieve the last row 3. Retrieve the next row after the current position 4. Move backward one row from the current position 5. Jump to the 50th row in the result set 6. Move 10 rows forward from the current position

Exercise 4: Multi-Row FETCH Data Structures

A program needs to fetch account records 100 rows at a time using a multi-row FETCH with ROWSET positioning. Define the complete WORKING-STORAGE data structures needed for the host variable arrays and indicator arrays to support the following columns:

Column DB2 Type COBOL Equivalent
ACCT_ID CHAR(10) PIC X(10)
CUST_NAME VARCHAR(40) PIC X(40)
BALANCE DECIMAL(13,2) PIC S9(11)V99 COMP-3
OPEN_DATE DATE PIC X(10)
STATUS CHAR(1) PIC X(01)

Then write the FETCH ROWSET statement that populates these arrays.

Exercise 5: Package and Plan Relationships

Answer the following about DB2 packages and plans:

  1. What is the difference between a DB2 package and a plan?
  2. What command creates a package from a DBRM?
  3. How does a plan reference multiple packages?
  4. What is the advantage of binding individual packages versus embedding all SQL directly in a plan?
  5. If subprogram ACCTVAL has its SQL rebound into a new package, does the calling plan need to be rebound? Why or why not?

Exercise 6: LOB Data Types

A banking application stores scanned check images as BLOBs and customer correspondence as CLOBs. For each scenario, identify the correct LOB technique:

  1. Inserting a 2 MB check image from a file into a BLOB column
  2. Reading a 500 KB letter stored as a CLOB into a host variable
  3. Retrieving only the first 200 bytes of a CLOB to display a preview
  4. Determining the length of a BLOB without reading its content
  5. Updating a portion of a CLOB (replacing one paragraph) without rewriting the entire value

Write the COBOL host variable declarations needed for a BLOB column that stores check images up to 5 MB.

Exercise 7: Stored Procedure Parameters

Given this stored procedure definition:

CREATE PROCEDURE BANK.CALC_INTEREST
  (IN  P_ACCT_ID     CHAR(10),
   IN  P_RATE_CODE   CHAR(3),
   OUT P_INTEREST     DECIMAL(13,2),
   OUT P_NEW_BALANCE  DECIMAL(13,2),
   INOUT P_STATUS     CHAR(1))
  LANGUAGE COBOL
  EXTERNAL NAME CALCINT
  PARAMETER STYLE GENERAL WITH NULLS

Write the complete EXEC SQL CALL statement from a COBOL client program, including all necessary host variable declarations and indicator variables. Show the SQLCODE checking after the CALL.

Exercise 8: Isolation Level Effects

A transaction reads an account balance, computes interest, and updates the balance. Describe what could go wrong under each isolation level and which level is appropriate:

  1. Uncommitted Read (UR) -- What risks exist?
  2. Cursor Stability (CS) -- What protection does it provide? What gap remains?
  3. Read Stability (RS) -- How does it differ from CS for this scenario?
  4. Repeatable Read (RR) -- What does it guarantee? What is the performance cost?

Tier 2: Application (Exercises 9-16)

Exercise 9: Dynamic WHERE Clause Builder

Write a COBOL paragraph that constructs a dynamic SQL WHERE clause based on user-supplied filter criteria. The user may supply any combination of the following:

  • Account type (checking, savings, money market)
  • Branch code (3-character code)
  • Minimum balance threshold
  • Account open date range (from-date, to-date)
  • Customer last name (partial match with LIKE)

The paragraph should: 1. Start with a base SELECT statement 2. Append WHERE conditions only for non-empty filter fields 3. Use parameter markers (?) for each value 4. Track the number of parameters for the USING clause 5. PREPARE the completed statement 6. OPEN a cursor with the dynamic statement using DESCRIPTOR

       WORKING-STORAGE SECTION.
       01  WS-FILTERS.
           05  WS-ACCT-TYPE       PIC X(02).
           05  WS-BRANCH-CODE     PIC X(03).
           05  WS-MIN-BALANCE     PIC S9(11)V99 COMP-3.
           05  WS-DATE-FROM       PIC X(10).
           05  WS-DATE-TO         PIC X(10).
           05  WS-CUST-LAST-NAME  PIC X(30).
       01  WS-SQL-STMT            PIC X(2000).
       01  WS-PARM-COUNT          PIC S9(04) COMP.

Exercise 10: Scrollable Cursor Paging

Write a complete COBOL program fragment that implements a paginated account statement viewer using a scrollable cursor. The program should:

  1. Declare a SENSITIVE STATIC SCROLL cursor for transaction history
  2. Allow the user to enter commands: NEXT, PREV, FIRST, LAST, GOTO nnn
  3. Display 20 transactions per page with a header showing page number and total rows
  4. Handle SQLCODE +100 (end of result set) gracefully
  5. Use FETCH RELATIVE to implement page navigation

Include all necessary WORKING-STORAGE definitions and the main navigation loop.

Exercise 11: Multi-Row INSERT for Batch Processing

A nightly batch process loads daily transactions from a sequential file into DB2. Write a COBOL program fragment that:

  1. Reads transaction records from a sequential file
  2. Accumulates them into a 100-row array
  3. Uses a multi-row INSERT to write the batch to DB2
  4. Handles partial batches at end-of-file (last batch may have fewer than 100 rows)
  5. Commits every 1,000 rows
  6. Tracks and reports the total rows inserted, total committed, and elapsed time
       01  WS-TRANS-ARRAY.
           05  WS-TRANS-ROW OCCURS 100 TIMES.
               10  WS-TR-ACCT-ID    PIC X(10).
               10  WS-TR-TRANS-DATE PIC X(10).
               10  WS-TR-AMOUNT     PIC S9(11)V99 COMP-3.
               10  WS-TR-TYPE       PIC X(01).
               10  WS-TR-DESC       PIC X(40).
       01  WS-ROW-INDEX            PIC S9(04) COMP VALUE 0.
       01  WS-TOTAL-INSERTED       PIC 9(09) VALUE 0.
       01  WS-COMMIT-COUNT         PIC 9(09) VALUE 0.

Exercise 12: Stored Procedure with Result Set

Write a COBOL stored procedure named ACCTLIST that:

  1. Accepts an input branch code and account status filter
  2. Opens a cursor that selects all matching accounts with their balances
  3. Returns the cursor as a result set to the caller
  4. Sets an output parameter with the count of rows returned
  5. Handles errors by setting a status code and message

Then write the client-side COBOL code that calls this stored procedure and processes the returned result set using ASSOCIATE LOCATORS and ALLOCATE CURSOR.

Exercise 13: SQLDA for Fully Dynamic SQL

Write a COBOL program that uses a full SQLDA (SQL Descriptor Area) to handle a SELECT statement where the number and types of columns are unknown at compile time. The program should:

  1. Accept an arbitrary SELECT statement from the user
  2. PREPARE and DESCRIBE the statement to populate the SQLDA
  3. Allocate appropriate host variables based on the SQLDA column descriptions
  4. FETCH rows and display them with proper formatting based on data type
  5. Handle at least CHAR, INTEGER, DECIMAL, and DATE types

Exercise 14: Deadlock Retry Logic

Write a reusable COBOL paragraph that implements deadlock retry logic for DB2 operations. The paragraph should:

  1. Detect SQLCODE -911 (deadlock or timeout) and -913 (deadlock)
  2. Roll back the current unit of work
  3. Wait a configurable delay before retrying (using a busy-wait loop or CALL to a delay routine)
  4. Retry up to a configurable maximum number of times
  5. Log each retry attempt with timestamp, SQLCODE, and retry count
  6. Give up after the maximum retries and set an error status
       01  WS-RETRY-CONFIG.
           05  WS-MAX-RETRIES      PIC 9(02) VALUE 03.
           05  WS-RETRY-DELAY-MS   PIC 9(05) VALUE 01000.
       01  WS-RETRY-STATE.
           05  WS-RETRY-COUNT      PIC 9(02) VALUE 0.
           05  WS-RETRY-SQLCODE    PIC S9(09) COMP VALUE 0.
           05  WS-RETRY-NEEDED     PIC X(01) VALUE 'N'.
               88  RETRY-YES       VALUE 'Y'.
               88  RETRY-NO        VALUE 'N'.

Exercise 15: BIND Parameters Analysis

Given the following BIND PLAN command, explain each parameter and its effect on application behavior:

//BIND     EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2P)
  BIND PLAN(ACCTPLAN)                -
       MEMBER(ACCTMAIN,ACCTVAL,ACCTRPT) -
       PKLIST(COLL1.ACCTVAL,            -
              COLL1.ACCTRPT,             -
              COLL1.CALCINT)             -
       ACTION(REPLACE)               -
       RETAIN                        -
       ISOLATION(CS)                 -
       VALIDATE(BIND)                -
       ACQUIRE(USE)                  -
       RELEASE(COMMIT)               -
       DYNAMICRULES(BIND)            -
       QUALIFIER(BANKPROD)           -
       ENCODING(EBCDIC)              -
       EXPLAIN(YES)
  END
/*

For each parameter (ACTION, RETAIN, ISOLATION, VALIDATE, ACQUIRE, RELEASE, DYNAMICRULES, QUALIFIER), describe what it controls and what the alternatives are.

Exercise 16: Connection Pooling Logic

Write a COBOL program fragment that manages connections to multiple DB2 subsystems. The program should:

  1. Use CONNECT statements to establish connections to three DB2 systems: DB2P (production), DB2R (reporting), DB2A (archive)
  2. Use SET CONNECTION to switch between them
  3. Query account data from DB2P, aggregate reporting data from DB2R, and fetch historical records from DB2A
  4. Properly handle connection failures for each system
  5. Release all connections at program termination

Tier 3: Analysis and Design (Exercises 17-22)

Exercise 17: Static vs. Dynamic SQL Decision Matrix

For each of the following application scenarios, recommend whether to use static SQL, dynamic SQL with parameter markers, or EXECUTE IMMEDIATE. Justify each recommendation considering performance, security, flexibility, and maintenance.

  1. A nightly batch job that processes all accounts in a fixed table with a fixed WHERE clause
  2. An ad-hoc query tool that lets users type any SELECT statement
  3. A transaction program where the table name varies based on the fiscal year (TRANS_2024, TRANS_2025, TRANS_2026)
  4. A search screen where the user can filter on any combination of 8 optional fields
  5. A utility program that runs ALTER TABLE to add columns during schema migration
  6. A high-volume online transaction that inserts a single row at a time with fixed column layout
  7. A report program where the ORDER BY columns are chosen by the user

Exercise 18: Cursor Design Comparison

A bank statement application needs to display the last 200 transactions for an account, with forward and backward scrolling 20 at a time. Compare three cursor strategies:

Strategy A: Static cursor -- fetch all 200 rows into a COBOL table, scroll in memory.

Strategy B: Scrollable cursor -- declare a SCROLL cursor, use FETCH FIRST/NEXT/PRIOR/ABSOLUTE for navigation.

Strategy C: Keyset pagination -- use a static cursor with WHERE TRANS_ID > :last-seen-id FETCH FIRST 20 ROWS ONLY, re-open for each page.

For each strategy, analyze: 1. Memory consumption in the COBOL program 2. DB2 resource consumption (locks, buffer pool, thread time) 3. Handling of concurrent updates (what if new transactions appear during browsing?) 4. Implementation complexity 5. Which strategy would you recommend and why?

Exercise 19: SQL Tuning Analysis

The following query runs in a COBOL batch program and takes 45 minutes to process 2 million accounts. Analyze it and propose optimizations.

SELECT A.ACCT_ID, A.CUST_ID, A.BALANCE,
       C.CUST_NAME, C.CUST_ADDR,
       (SELECT SUM(T.TRANS_AMOUNT)
          FROM TRANSACTIONS T
         WHERE T.ACCT_ID = A.ACCT_ID
           AND T.TRANS_DATE >= CURRENT DATE - 30 DAYS) AS RECENT_ACTIVITY,
       (SELECT COUNT(*)
          FROM TRANSACTIONS T
         WHERE T.ACCT_ID = A.ACCT_ID
           AND T.TRANS_DATE >= CURRENT DATE - 365 DAYS) AS ANNUAL_COUNT
  FROM ACCOUNTS A
  JOIN CUSTOMERS C ON A.CUST_ID = C.CUST_ID
 WHERE A.STATUS = 'A'
   AND A.BALANCE > 0
 ORDER BY A.BRANCH_CODE, A.ACCT_ID

Address: 1. The correlated subquery problem and how to eliminate it 2. Index recommendations for the ACCOUNTS and TRANSACTIONS tables 3. How to restructure this as a join-based query 4. Whether the ORDER BY is contributing to the performance problem 5. How multi-row FETCH with ROWSET could reduce network overhead 6. How to parallelize this in a batch environment (e.g., partition by branch)

Exercise 20: Package Rebind Impact Analysis

A banking application has the following package dependency graph:

Plan: BANKPLAN
  |
  +-- Package: COLL1.ACCTMAIN (main menu program)
  |     Uses tables: ACCOUNTS, CUSTOMERS
  |
  +-- Package: COLL1.TRANSFER (fund transfer program)
  |     Uses tables: ACCOUNTS, TRANSACTIONS, AUDIT_LOG
  |
  +-- Package: COLL1.STMTGEN (statement generator)
  |     Uses tables: ACCOUNTS, TRANSACTIONS, CUSTOMERS
  |
  +-- Package: COLL1.CALCINT (interest calculation)
        Uses tables: ACCOUNTS, INTEREST_RATES

A DBA needs to reorganize the TRANSACTIONS table and rebuild its indexes. Answer: 1. Which packages must be rebound? Why? 2. Can the rebind be done while the application is running? 3. What is the effect of BIND with ACTION(REPLACE) on active threads? 4. What sequence of operations would minimize application outage? 5. How does FREE PACKAGE differ from REBIND PACKAGE?

Exercise 21: LOB Storage Strategy

A bank is designing a document management system that stores: - Check images (JPEG, 50-200 KB each, 10 million per month) - Account statements (PDF, 100-500 KB each, 2 million per month) - Customer signatures (PNG, 5-20 KB each, queried frequently) - Loan documents (PDF, 1-5 MB each, 500,000 per year)

For each document type, recommend: 1. BLOB vs. CLOB vs. DBCLOB -- which LOB type and why 2. Inline storage vs. auxiliary table storage -- threshold recommendation 3. LOB table space configuration (LOG YES/NO, COMPRESS YES/NO) 4. Retrieval strategy in COBOL (full fetch vs. LOB locator vs. streaming) 5. Archival strategy for documents older than 7 years

Exercise 22: Stored Procedure Architecture

Design a stored procedure architecture for the following banking operations. For each, specify whether it should be a single stored procedure or decomposed into multiple, and justify your choice.

  1. Account opening -- Validate customer, create account record, set initial balance, create audit trail, send welcome notification
  2. End-of-day settlement -- Aggregate all branch transactions, compute net positions, update GL entries, generate reports
  3. Balance inquiry -- Look up account, return current balance and last 5 transactions
  4. Wire transfer -- Debit source account, credit destination account, log to SWIFT interface, update compliance tables

For each, specify: - Input/output parameters - Result sets returned (if any) - Isolation level needed - Commit scope (COMMIT ON RETURN YES or NO) - Error handling strategy

Tier 4: Advanced Challenges (Exercises 23-30)

Exercise 23: Generic Report Engine with Dynamic SQL

Write a COBOL program that serves as a general-purpose report engine. The program should:

  1. Read a report definition file that specifies: - Table name(s) and join conditions - Column list with display headers and formats - WHERE clause filters - GROUP BY and ORDER BY clauses - Report title and subtitle
  2. Construct the SELECT statement dynamically
  3. Use DESCRIBE to determine column types and widths
  4. FETCH rows and format them into a columnar report with: - Page headers with report title, date, and page number - Column headers derived from the report definition - Proper numeric formatting (commas, decimals, dollar signs) - Page breaks every 60 lines - Grand totals for numeric columns

Exercise 24: Multi-Threaded Cursor Processing

Design a COBOL/DB2 batch program that processes a large account table in parallel using DB2's partitioning. The program should:

  1. Query the DB2 catalog to determine the number of partitions for the ACCOUNTS table
  2. Use a control table to assign partition ranges to multiple batch jobs
  3. Each job opens a cursor with a WHERE clause limiting it to its assigned partition
  4. Use multi-row FETCH with ROWSET of 100 to minimize DB2 calls
  5. Implement checkpoint/restart logic: after every 10,000 rows, commit and record the last-processed key
  6. On restart, skip to the last checkpoint position

Write the control table DDL, the partition-assignment logic, and the main processing loop with checkpoint/restart.

Exercise 25: Optimistic Locking Implementation

Write a COBOL program that implements optimistic concurrency control for account updates. The program should:

  1. Read an account row with a VERSION_NUM column (no FOR UPDATE -- no lock held)
  2. Display the data to the user and accept modifications
  3. Attempt the UPDATE with a WHERE clause that includes the original VERSION_NUM
  4. If SQLERRD(3) = 0 (no rows updated), another user modified the row -- fetch the current version and display a conflict message
  5. Allow the user to retry with the new data or cancel
  6. Include a maximum retry counter to prevent infinite loops
       EXEC SQL
           UPDATE ACCOUNTS
              SET BALANCE     = :WS-NEW-BALANCE,
                  LAST_UPDATE = CURRENT TIMESTAMP,
                  VERSION_NUM = :WS-VERSION-NUM + 1
            WHERE ACCT_ID     = :WS-ACCT-ID
              AND VERSION_NUM = :WS-ORIG-VERSION
       END-EXEC

Exercise 26: Temporal Data Query Program

Write a COBOL program that queries DB2 temporal tables to provide "as-of" account information. The program should:

  1. Accept an account ID and a historical date from the user
  2. Query a system-period temporal table to retrieve the account balance as of that date
  3. Query a business-time temporal table to find the interest rate that was effective on that date
  4. Calculate what the interest accrual would have been using the historical rate and balance
  5. Display a comparison between the historical and current values
       EXEC SQL
           SELECT ACCT_ID, BALANCE, INTEREST_RATE
             INTO :WS-ACCT-ID, :WS-HIST-BALANCE, :WS-HIST-RATE
             FROM ACCOUNTS
              FOR SYSTEM_TIME AS OF :WS-HISTORY-DATE
            WHERE ACCT_ID = :WS-INPUT-ACCT
       END-EXEC

Exercise 27: XML and JSON Processing in DB2

Write a COBOL program that:

  1. Extracts account data from DB2 and formats it as an XML document using SQL/XML functions (XMLELEMENT, XMLFOREST, XMLAGG)
  2. Stores the generated XML in a CLOB column
  3. Queries the XML using XMLTABLE to extract specific elements back into relational host variables
  4. Generates a JSON representation of account data using JSON_OBJECT and JSON_ARRAY functions
  5. Parses the JSON back using JSON_TABLE

Include the SQL statements and the COBOL host variable declarations for both the XML and JSON operations.

Exercise 28: Distributed Transaction Across DB2 Subsystems

Write a COBOL program that implements a fund transfer across two DB2 subsystems (DB2A and DB2B). The program should:

  1. Connect to both subsystems
  2. Debit the source account on DB2A
  3. Credit the destination account on DB2B
  4. Insert audit records on both subsystems
  5. Commit the distributed transaction atomically (two-phase commit)
  6. Handle the case where one subsystem is unavailable
  7. Implement compensation logic if the distributed commit fails

Exercise 29: Performance Monitoring Dashboard

Write a COBOL program that queries DB2 system catalog tables and monitoring data to produce a performance dashboard. The program should:

  1. Query SYSIBM.SYSPACKSTMT for the top 10 SQL statements by elapsed time
  2. Query the DB2 statistics trace data for buffer pool hit ratios
  3. Identify packages with the most lock timeouts from the accounting trace
  4. Display EXPLAIN output for the most expensive query
  5. Format the results into a summary report suitable for a DBA review

Exercise 30: Complete Dynamic Batch Framework

Design and implement a data-driven batch processing framework where:

  1. A control table defines batch jobs: job name, SQL statement, processing program, commit frequency, restart capability
  2. The framework reads the control table and dynamically PREPAREs the SQL
  3. For each row fetched, the framework dynamically CALLs the specified processing program
  4. Checkpoint records are maintained in a restart table
  5. On abnormal termination and restart, the framework repositions to the last checkpoint
  6. Statistics (rows processed, elapsed time, CPU time, DB2 getpages) are logged to an audit table

Write the control table DDL, restart table DDL, and the main framework program.

Tier 5: Research and Exploration (Exercises 31-38)

Exercise 31: DB2 z/OS vs. DB2 LUW

Research the differences between DB2 for z/OS and DB2 for Linux/UNIX/Windows (LUW) as they affect COBOL programming. Compare: - SQL syntax differences for embedded COBOL - BIND process differences - Dynamic SQL behavior differences - Host variable type mapping differences - Stored procedure creation and deployment

Exercise 32: DB2 REST Services from COBOL

Research how DB2 for z/OS native REST services can expose COBOL stored procedures as RESTful APIs. Investigate: - How a COBOL stored procedure is registered as a REST service - What JSON/XML transformations DB2 performs automatically - How authentication and authorization are handled - Performance considerations compared to direct SQL access - Real-world use cases in banking and insurance

Exercise 33: COBOL and DB2 pureXML

Research DB2 pureXML capabilities and how they are accessed from COBOL programs. Investigate: - Declaring XML host variables in COBOL - Using XMLPARSE and XMLSERIALIZE - XPath expressions in SQL/XML queries from COBOL - Performance of XML indexes for common banking document queries - Comparison with storing XML as CLOBs

Exercise 34: Advanced BIND Strategies

Research advanced BIND strategies used in large DB2 shops. Investigate: - Incremental BIND (rebinding individual packages without affecting the plan) - PLANMGMT(EXTENDED) for keeping previous and original plan copies - Auto-rebind behavior and when it triggers - BIND with EXPLAIN(ONLY) for access path analysis - Strategies for managing hundreds of packages in a large banking application

Exercise 35: DB2 Analytics Accelerator Integration

Research how DB2 Analytics Accelerator (IDAA) affects COBOL batch programs. Investigate: - How existing COBOL SQL is transparently accelerated - What query patterns benefit most from acceleration - How to use EXPLAIN to verify a query is routed to the accelerator - Restrictions on SQL that can be accelerated - Real-world performance improvements reported by financial institutions

Exercise 36: Continuous Delivery for DB2 Programs

Research how continuous delivery pipelines are implemented for COBOL/DB2 applications. Investigate: - Automated BIND processes in CI/CD - Schema versioning strategies for DB2 tables - Blue/green deployment for DB2 packages - Automated regression testing of dynamic SQL - Tools available (IBM UrbanCode, Jenkins plugins, DBB)

Exercise 37: DB2 Machine Learning from COBOL

Research how DB2 for z/OS integrates with machine learning models and how COBOL programs can invoke predictive scoring. Investigate: - DB2 AI functions (PREDICT_SCORE, PREDICT_CLUSTER) - Deploying PMML or ONNX models into DB2 - Calling the scoring function from a COBOL program via embedded SQL - Use cases in fraud detection and credit scoring - Performance considerations for inline scoring

Exercise 38: Global Temporary Tables in Batch Processing

Research the use of DB2 created and declared global temporary tables in COBOL batch programs. Compare: - DECLARE GLOBAL TEMPORARY TABLE vs. CREATE GLOBAL TEMPORARY TABLE - Scope and lifetime of each type - When to use a temporary table vs. a cursor vs. a COBOL internal table - Index support on temporary tables - Performance implications for a batch program that joins temporary results with permanent tables