SQL Basics on DB2

Introduction

The previous lesson introduced DB2 conceptually. This lesson gets hands-on with the SQL you would actually write against it. The reassuring news, worth repeating, is that the SQL itself — SELECT, INSERT, UPDATE — is standard SQL, the same language used across the relational database world. What is specific to the mainframe is the second half of this lesson: how that SQL gets embedded directly inside a COBOL program so batch jobs and CICS transactions can read and write DB2 data as part of their normal processing.

SELECT on DB2

A SELECT statement on DB2 looks exactly like a SELECT statement anywhere else. You name the columns you want, the table to pull them from, and — usually — a WHERE clause to narrow down which rows you actually care about.

SELECT ACCT_NUM, CUSTOMER_NAME, BALANCE
FROM ACCOUNTS
WHERE ACCT_TYPE = 'SAVINGS'
  AND BALANCE > 1000
ORDER BY BALANCE DESC;

Everything about that query — filtering with WHERE, sorting with ORDER BY, joining multiple tables together — behaves exactly the way it would in any relational database. There is no mainframe-specific SELECT syntax to learn separately.

INSERT and UPDATE

INSERT adds new rows, and UPDATE modifies existing ones, again with entirely standard syntax.

-- Add a brand-new account
INSERT INTO ACCOUNTS (ACCT_NUM, CUSTOMER_NAME, ACCT_TYPE, BALANCE)
VALUES ('100994512', 'J. NAKAMURA', 'CHECKING', 500.00);

-- Apply a deposit to an existing account
UPDATE ACCOUNTS
SET BALANCE = BALANCE + 250.00
WHERE ACCT_NUM = '100994512';

DELETE and Data Safety

DELETE removes rows, and it deserves particular respect in a mainframe shop, for the same reason it deserves respect anywhere: a DELETE without a precise WHERE clause can remove far more data than intended, in a system that may hold decades of financial or regulatory records. Production mainframe shops typically wrap DELETE (and UPDATE) statements in strict change-control processes, exactly the kind of discipline covered earlier in this course around JCL and job scheduling.

-- Dangerous: removes EVERY row in the table
DELETE FROM ACCOUNTS;

-- Correct: removes exactly one closed, zero-balance account
DELETE FROM ACCOUNTS
WHERE ACCT_NUM = '100004411'
  AND BALANCE = 0
  AND STATUS = 'CLOSED';

Embedded SQL in COBOL

A COBOL program does not call out to DB2 the way a web application might call a database over a network connection. Instead, SQL statements are written directly inside the COBOL source code, wrapped in an EXEC SQL ... END-EXEC block — the same pattern you saw with EXEC CICS blocks in the previous two lessons. A special preprocessor scans the COBOL source before compilation, finds these blocks, and translates them into calls the compiled program can actually use to talk to DB2 at runtime.

       WORKING-STORAGE SECTION.
       01 WS-ACCT-NUM       PIC X(9).
       01 WS-BALANCE         PIC S9(9)V99 COMP-3.

       PROCEDURE DIVISION.
           MOVE '100482913' TO WS-ACCT-NUM

           EXEC SQL
               SELECT BALANCE
               INTO :WS-BALANCE
               FROM ACCOUNTS
               WHERE ACCT_NUM = :WS-ACCT-NUM
           END-EXEC

           IF SQLCODE = 0
               DISPLAY 'BALANCE: ' WS-BALANCE
           ELSE
               DISPLAY 'ACCOUNT NOT FOUND, SQLCODE: ' SQLCODE
           END-IF.

Host Variables

Notice the colon in front of :WS-BALANCE and :WS-ACCT-NUM in that example. That colon marks a host variable — an ordinary COBOL data field being used to pass a value into SQL, or to receive a value back out of it. Host variables are the bridge between COBOL's data world and DB2's data world: SQL cannot reach directly into arbitrary COBOL fields, so the colon syntax makes the connection explicit and lets the SQL preprocessor generate correct code. After nearly every embedded SQL statement, a program also checks a special field called SQLCODE, which DB2 sets to indicate whether the statement succeeded, found no matching rows, or failed — the pattern shown in the IF check above.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

The SQL you would write against DB2 is genuinely the same SQL used across the relational database world — the real mainframe-specific skill is embedding that SQL inside COBOL using EXEC SQL blocks, host variables, and SQLCODE checks. With CICS and DB2 both covered now, the next lesson turns to a different but equally essential topic: RACF, the security system that decides who is allowed to do any of this in the first place.