DB2beginner
SQL Fundamentals for DB2 on z/OS
Understand the baseline SQL skills expected for DB2 z/OS interviews and how embedded SQL differs from ad hoc SQL.
DB2 on z/OS speaks ordinary SQL. What differs is how that SQL sits inside a COBOL program.
Embedded SQL is like filling out a bilingual form where certain blanks (host variables) are shared between two languages — English on one side, SQL on the other — and after submitting the form, you always check a stamped status code at the bottom before assuming the clerk actually processed it the way you expected.
Key Concepts
1
Embedded SQL, with host variables.
EXEC SQL
SELECT NAME, BALANCE
INTO :WS-NAME, :WS-BALANCE
FROM CUSTOMER
WHERE CUST_ID = :WS-CUST-ID
END-EXEC.2
A colon marks a COBOL field. :WS-CUST-ID supplies a value; :WS-NAME receives one.
:WS-CUST-ID:WS-NAME
3
Null needs a second field. A column that can be null requires an indicator variable, because COBOL has
no null:
4
INTO :WS-BALANCE :WS-BAL-IND5
WS-BAL-IND = 0 the value is real
WS-BAL-IND = -1 the column was null; WS-BALANCE is meaningless6
Reading a null column without an indicator raises -305 at runtime. Using the host variable without
checking the indicator is worse — you act on whatever was left in it.
-305
7
SELECT INTO expects exactly one row. It is the right tool for a primary key lookup and the wrong one
for anything else:
SELECT INTO
8
0 rows returned SQLCODE +100
1 row SQLCODE 0
2 or more SQLCODE -811, and the program fails9
Anything that might return several rows needs a cursor.
EXEC SQL DECLARE C1 CURSOR FOR
SELECT CUST_ID, NAME FROM CUSTOMER WHERE REGION = :WS-REGION
END-EXEC.10
EXEC SQL OPEN C1 END-EXEC.
PERFORM UNTIL SQLCODE = 100
EXEC SQL FETCH C1 INTO :WS-ID, :WS-NAME END-EXEC
IF SQLCODE = 0
PERFORM PROCESS-ROW
END-IF
END-PERFORM.
EXEC SQL CLOSE C1 END-EXEC.11
COBOL cannot receive a result set. A cursor hands rows over one at a time, which is why this pattern is
everywhere.
12
Check SQLCODE after every statement.
0 success
+100 no rows found -- usually not an error, just nothing matched
-305 null without an indicator variable
-811 SELECT INTO returned more than one row
-911 deadlock or timeout; DB2 has rolled you back
-803 duplicate keySQLCODE
13
+100 is the one that trips people. It is positive, it means "nothing matched", and it is frequently the
normal case — but a program that only tests IF SQLCODE < 0 treats it as success and carries on with
whatever the host variables held before.
+100IF SQLCODE < 0
14
SQLCA holds more than SQLCODE: SQLERRD(3) gives the number of rows affected by an update or delete,
which is how you tell "updated nothing" from "updated something".
SQLCASQLCODESQLERRD(3)
15
What the interviewer is probing.1. "What is special about SQL embedded in COBOL?" Probing: the mechanics. Stalls: "Nothing."
Moves up: it is precompiled into host-language calls, host variables carry the values, and every
statement must have its SQLCODE checked.
16
2. "What does a null indicator variable do?" Probing: the trap. Stalls: "Nothing." *Moves
up:* it reports whether the column was null — selecting a nullable column without one raises a
negative SQLCODE, not a blank value.
17
3. "What is the difference between a singleton SELECT and a cursor?" Probing: the distinction.
Stalls: "Size." Moves up: a singleton must return exactly one row or it errors; a cursor is for
a result set you fetch through.
18
4. "Why does the WITH UR clause get used so often here?" Probing: the practical knowledge.
Stalls: "Habit." Moves up: uncommitted read avoids taking locks on reporting queries, which
keeps them from blocking online transactions — at the cost of possibly dirty data.