What DB2 for z/OS is

DB2 is a relational database that runs as a set of address spaces under z/OS. The SQL is standard enough that anything you know from other databases transfers. What is different is how programs get connected to it, and how seriously it takes concurrency at very high volumes.

The pieces

How DB2 is organised

SubsystemA named DB2 instance, e.g. DBP1 — several address spaces working together

DatabaseA logical grouping of table spaces and index spaces

Table spaceThe physical storage holding one or more tables

TableRows and columns, as you would expect

IndexA separate structure making lookups fast

Where you will meet it

Batch — a COBOL program with embedded SQL, run under the DB2 batch attachment.
Online — a CICS transaction issuing SQL, with DB2 participating in the transaction's commit.
Interactive — SPUFI or a query tool, for running ad-hoc SQL from a terminal.
Utilities — LOAD, UNLOAD, RUNSTATS, REORG, COPY: bulk operations run as batch jobs.

The catalog: DB2 describing itself

DB2 keeps its own metadata in ordinary tables you can query. This is unreasonably useful and underused by beginners.

Questions you can answer with SQL alone

-- what columns does this table have?
SELECT NAME, COLTYPE, LENGTH, SCALE, NULLS
  FROM SYSIBM.SYSCOLUMNS
 WHERE TBNAME = 'ACCOUNT' AND TBCREATOR = 'PAYPROD'
 ORDER BY COLNO;

-- which programs use this table?
SELECT DISTINCT DNAME, DTYPE
  FROM SYSIBM.SYSPACKDEP
 WHERE BNAME = 'ACCOUNT';

-- what indexes exist on it?
SELECT NAME, UNIQUERULE, COLCOUNT
  FROM SYSIBM.SYSINDEXES
 WHERE TBNAME = 'ACCOUNT';

The pieces

Where you will meet it

The catalog: DB2 describing itself

Common mistakes

What you will see at work

Key terms