DB2 Tutorial

Db2 for z/OS is IBM's relational database system on the mainframe. It stores data in tables and supports SQL for querying and updating. Application programs typically use embedded SQL: SQL statements written inside COBOL (or other host languages) and delimited by EXEC SQL and END-EXEC. Db2 returns status in the SQL Communication Area (SQLCA), and the program uses COMMIT and ROLLBACK to control transactions. This tutorial introduces Db2 on z/OS, the basics of embedded SQL in COBOL, host variables and SQLCA, and the difference between static and dynamic SQL.

Explain Like I'm Five: What Is DB2?

DB2 is like a big, organized filing cabinet that many programs can use at once. Instead of loose papers, it keeps information in tables—rows and columns. You ask questions in a special language called SQL, like "show me all the customers named Smith" or "add this new order." Your COBOL program can put these questions right inside its code (embedded SQL). After each question, DB2 tells the program whether it worked (SQLCODE) and, if something went wrong, what happened. When you are done with a set of changes, you say COMMIT to keep them or ROLLBACK to cancel them.

What Is Db2 for z/OS?

Db2 for z/OS is a relational database management system (RDBMS) that runs on the mainframe. Data is stored in tables (rows and columns); you access it with SQL (SELECT, INSERT, UPDATE, DELETE). Db2 handles indexing, locking, logging, and recovery so that many applications can use the same data safely. Programs connect to Db2 (via a thread or connection); in CICS, the region manages that connection and coordinates commit/rollback with CICS resources. Db2 supports both static SQL (embedded and bound at compile/bind time) and dynamic SQL (prepared at runtime). Understanding embedded SQL, host variables, and SQLCA is the first step to writing mainframe applications that use Db2.

Embedded SQL in COBOL: EXEC SQL and END-EXEC

In COBOL you embed SQL by placing the statement between EXEC SQL and END-EXEC. Each of these keywords must be on its own line and cannot be continued. Only SQL is allowed between them—no COBOL statements. The COBOL compiler (with the Db2 preprocessor or option) turns these into calls to Db2. Example: EXEC SQL SELECT COL1 INTO :WS-COL1 FROM TBL WHERE KEY = :WS-KEY END-EXEC. The colon prefix (:) identifies host variables—COBOL fields that pass data to or from Db2. Host variables must be defined in WORKING-STORAGE, LOCAL-STORAGE, or LINKAGE. There are size and type restrictions: for example, no COMP-1 in standard host variables, and no editing symbols in the PICTURE for fields used as host variables. For strict standard compliance, host variables are often placed between EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION.

1
2
3
4
5
6
7
8
9
10
11
12
13
       EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01 WS-KEY    PIC X(10).
       01 WS-COL1   PIC X(20).
       01 WS-SQLCODE PIC S9(9) COMP.
       EXEC SQL END DECLARE SECTION END-EXEC.
 
       EXEC SQL SELECT COL1 INTO :WS-COL1
         FROM MYTABLE WHERE KEY = :WS-KEY
       END-EXEC.
       MOVE SQLCODE TO WS-SQLCODE.
       IF WS-SQLCODE NOT = 0
           DISPLAY 'SQL ERROR ' WS-SQLCODE
       END-IF.

SQL Communication Area (SQLCA)

After each executable SQL statement, Db2 sets the SQL Communication Area. You include it with EXEC SQL INCLUDE SQLCA END-EXEC in WORKING-STORAGE (or LOCAL-STORAGE). The most important field is SQLCODE: 0 means success, negative means error, positive means warning (e.g. +100 means no row found for a singleton SELECT). SQLSTATE is a five-character standard status. Your program should check SQLCODE (or SQLSTATE) after every SQL statement and handle errors (e.g. not found, duplicate key, deadlock) and warnings. Ignoring SQLCODE can lead to incorrect logic or undetected failures.

Main SQL Verbs

The core SQL operations are SELECT (read), INSERT (add rows), UPDATE (change rows), and DELETE (remove rows). You also use COMMIT to end the unit of work and make changes permanent, and ROLLBACK to undo the unit of work. For a single row you can use a singleton SELECT ... INTO :hostvars. For multiple rows you use a cursor: DECLARE CURSOR, OPEN, FETCH in a loop, then CLOSE. INSERT, UPDATE, and DELETE can use a WHERE clause to limit which rows are affected.

Host Variables

Host variables are COBOL data items that you use in SQL. In the SQL statement they are prefixed with a colon (:). They must be in the DECLARE SECTION or in an area that the preprocessor accepts. Restrictions typically include: maximum length (e.g. 32,765 bytes for a single item), no COMP-1 (floating point) in many environments, and no editing symbols in the PICTURE. Use host variables for input (e.g. in WHERE) and output (e.g. in INTO). For nullable columns you use indicator variables (a halfword integer): negative means NULL, 0 or positive means the value is present. The Db2 COBOL preprocessor and documentation list the exact PICTURE and USAGE mappings for each SQL data type.

Static vs. Dynamic SQL

Static SQL is written in the program and bound (prepared) at compile time or in a separate bind step. The access path is chosen at bind time. Static SQL is efficient and easier to control for security (only preapproved statements run). Dynamic SQL is built at runtime—e.g. the program constructs a character string containing the SQL text and then uses EXECUTE IMMEDIATE or PREPARE followed by EXECUTE. Dynamic SQL is flexible (e.g. user-driven queries, variable table names) but requires care with binding, security, and performance. Many applications use static SQL for standard operations and dynamic SQL only where necessary.

Connections and Transactions

A program must establish a connection to Db2 before issuing SQL (in some environments the connection is implicit when the program runs in a Db2-attached region). A unit of work is the set of SQL and other resource changes between COMMIT and the next COMMIT or ROLLBACK. COMMIT makes all changes in the unit of work permanent; ROLLBACK cancels them. In CICS, the unit of work can include both CICS updates (files, queues) and Db2 updates; CICS syncpoint triggers a coordinated commit (e.g. two-phase commit) so that either all commit or all roll back. Understanding your environment's connection and transaction model is important for correct error handling and recovery.

Step-by-Step: Running a Simple SELECT in COBOL

1. Define host variables in WORKING-STORAGE (or in BEGIN/END DECLARE SECTION). Match the data types and lengths to the table columns.
2. Include SQLCA: EXEC SQL INCLUDE SQLCA END-EXEC.
3. Move the key value to the host variable used in the WHERE clause.
4. Issue EXEC SQL SELECT col1, col2 INTO :hv1, :hv2 FROM table WHERE key = :key-host END-EXEC.
5. Check SQLCODE. If 0, the row was found and host variables contain the data. If +100, no row matched. If negative, handle the error.

Step-by-Step: Using a Cursor for Multiple Rows

1. DECLARE a cursor for a SELECT that returns multiple rows (no INTO in the DECLARE).
2. OPEN the cursor. Check SQLCODE.
3. In a loop: FETCH cursor INTO :hostvars. If SQLCODE 0, process the row; if +100, no more rows—exit the loop. If negative, handle error.
4. CLOSE the cursor when done. Optionally COMMIT or ROLLBACK depending on whether you only read or also updated.

Best Practices

• Check SQLCODE (or SQLSTATE) after every executable SQL statement and handle all expected return codes.
• Use DECLARE SECTION for host variables when possible to avoid type or length mismatches.
• Use COMMIT only when a logical unit of work is complete; avoid committing in the middle of a multi-step operation unless intended.
• Prefer static SQL for fixed, performance-critical access; use dynamic SQL only where flexibility is required and secure it properly.
• In CICS, use syncpoint (or the recommended API) so that CICS and Db2 commit or roll back together.

Test Your Knowledge