SQL Introduction for Db2

What is SQL?

SQL means Structured Query Language. It is a declarative language: you describe what data you want or what change you want, and Db2’s optimizer chooses an access path to do it. That differs from procedural COBOL, where you spell out loops and file reads step by step.

On Db2 for z/OS, SQL is the application interface to relational data. You can:

• Embed SQL in host languages (COBOL, PL/I, C, Assembler, and others)
• Run interactive SQL through site tools (SPUFI, QMF, Db2 Admin, and more)
• Send dynamic SQL from remote clients through DDF (JDBC, ODBC, and similar)
• Use SQL inside stored procedures and triggers

SQL is not JCL, not a utility control statement, and not a CICS command—though all of those may surround an application that uses SQL. Keeping those layers straight prevents confusion when something fails.

Why SQL feels different from reading a file

A COBOL program reading a VSAM KSDS usually navigates records with keys and loops. SQL works on sets. One SELECT can return thousands of rows matching a predicate. One UPDATE can change every row that satisfies a WHERE clause. That power is why a missing WHERE clause on DELETE is dangerous—and why thinking in sets is the core SQL skill.

1
2
3
4
5
-- Set-oriented: all open orders for one customer
SELECT ORDER_ID, ORDER_DATE, STATUS
  FROM ORDERS
 WHERE CUST_ID = 'C10021'
   AND STATUS = 'OPEN';

Db2 decides whether to use an index, scan a table space, or apply another strategy. Your job as a beginner is to write clear, correct SQL; later lessons cover explaining and tuning access paths.

Essential SQL terminology

A few more phrases appear early. A schema qualifies object names (for example HR.EMPLOYEE). A qualified name includes that qualifier. A literal is a constant written in SQL text, such as 'OPEN' or 100. A expression computes a value from columns, literals, operators, and functions. You will deepen each term in later pages; learn the vocabulary now so examples make sense.

SQL statements — the big categories

Queries with SELECT

SELECT retrieves data. You name columns (or use * carefully), identify the FROM table or join, and optionally filter with WHERE, group with GROUP BY, filter groups with HAVING, and order with ORDER BY. The output is a result table. In programs, a singleton SELECT INTO host variables returns one row; multi-row results usually need a cursor.

1
2
3
4
5
6
7
8
9
10
11
       EXEC SQL
            SELECT CUST_NAME
              INTO :WS-CUST-NAME
              FROM CUSTOMER
             WHERE CUST_ID = :WS-CUST-ID
       END-EXEC.
       IF SQLCODE = 0
           DISPLAY 'OK'
       ELSE
           DISPLAY 'SQLCODE=' SQLCODE
       END-IF.

DML — changing data

Data Manipulation Language statements change table contents:

• INSERT — add one or more rows
• UPDATE — change column values in existing rows
• DELETE — remove rows
• MERGE — insert or update based on matching logic (when used at your site)

DML participates in a unit of work. Changes become permanent at COMMIT (or the environment’s syncpoint) and disappear on ROLLBACK if not committed. Always consider WHERE clauses on UPDATE and DELETE.

1
2
3
4
5
6
UPDATE ACCOUNT
   SET BALANCE = BALANCE - 50.00
 WHERE ACCT_ID = '0001234567';
 
INSERT INTO AUDIT_LOG (ACCT_ID, ACTION, AMOUNT)
VALUES ('0001234567', 'WITHDRAW', 50.00);

DDL — defining objects

Data Definition Language creates and maintains objects: tables, indexes, views, databases, table spaces, and more. Application developers may have limited DDL authority in production; DBAs often own CREATE/ALTER/DROP there. Still, every developer should read DDL to understand the model they query.

1
2
3
4
5
6
7
CREATE TABLE EMPLOYEE (
  EMP_ID      CHAR(6)       NOT NULL,
  EMP_NAME    VARCHAR(40)   NOT NULL,
  DEPT_ID     CHAR(3),
  HIRE_DATE   DATE,
  PRIMARY KEY (EMP_ID)
);

DCL — privileges

Data Control Language centers on GRANT and REVOKE. Privileges control who can SELECT, UPDATE, or run packages, among other rights. Security design is a full topic; beginners should know that “SQL failed with authorization error” often means missing privilege, not a syntax typo.

Transaction control

COMMIT makes the current unit of work permanent. ROLLBACK undoes it. In CICS, syncpoint coordinates Db2 with other resources. In batch, commit frequency affects lock duration and restart design. SQL data changes and transaction control always travel together in real systems.

Static SQL vs dynamic SQL

Static SQL is coded in the program, precompiled or coprocessed, and bound into a package or plan. Access paths are chosen at bind time (with some reoptimization options). Dynamic SQL builds statement text at run time and prepares it then. Both are valid. Mainframe COBOL systems historically favor static SQL for predictability; distributed applications often use dynamic SQL with parameter markers.

SQLCODE and thinking like a programmer

After executable SQL in a host program, check SQLCODE (and prefer SQLSTATE for portability where your standards say so). Zero means success. +100 often means no row found for a singleton SELECT or end of cursor data. Negative codes are errors. Ignoring SQLCODE is a common production defect.

How this introduction fits the tutorial path

Next lessons teach how to read syntax diagrams, how identifiers and qualified names work, and then literals, predicates, and operators. Later sections cover SELECT deeply, joins, subqueries, and DML patterns. Architecture pages explained where SQL runs; this page explains what SQL is. Together they form the foundation for every Db2 application lesson.

Explain It Like I'm Five

SQL is a polite way to talk to a giant spreadsheet keeper named Db2. Instead of walking through every drawer yourself, you say: “Please show me all the red toys” (SELECT), “Put this new toy in the box” (INSERT), “Change this label” (UPDATE), or “Throw away the broken ones” (DELETE). You can also say “Build a new labeled box” (CREATE TABLE). Db2 figures out how to do it; you say what you want. When you finish a game that changed two toys, you either keep both changes (COMMIT) or put them back (ROLLBACK).

Exercises

1. Write a SELECT that returns EMP_ID and EMP_NAME from EMPLOYEE where DEPT_ID equals 'A01'.
2. Label each statement as query, DML, DDL, or DCL: GRANT SELECT; CREATE INDEX; DELETE FROM T; SELECT COUNT(*).
3. Explain in two sentences how SQL’s set orientation differs from reading a file record by record in a loop.
4. Why is UPDATE ACCOUNT SET BALANCE = 0; without a WHERE clause dangerous?
5. List three places SQL might run on z/OS (for example embedded COBOL, interactive tool, remote client) and one risk or benefit of each.