Db2 tables

What a Db2 table is

In IBM’s terms, tables are logical structures that Db2 maintains. A table is a collection of rows that all share the same set of columns. At each intersection of a column and a row sits a value (or NULL). Every table must have one or more columns; the number of rows can be zero. Db2 accesses data by content—predicates on column values—not by asking your program for a disk track number.

Rows have no fixed order. If yesterday’s INSERT went in before today’s, that does not guarantee SELECT returns them that way. Column order, by contrast, is the order you defined when you created the table (until you alter the definition). When you need a display sequence, you ask for it with ORDER BY.

That logical picture matters because many beginners arrive from COBOL files. A file’s record layout lives in each program’s FD. A Db2 table’s layout lives in the catalog (for example SYSIBM.SYSTABLES for base tables). Privileges control who can SELECT or UPDATE. Many transactions can share the same table under Db2 locking and logging—something a private sequential file does not give you for free.

Values, types, and shared meaning

A column is a set of values of the same type. A row is a sequence of values where the nth value belongs to the nth column. Types (INTEGER, VARCHAR, DATE, and so on) tell Db2 how to store, compare, and cast data. Constraints—primary keys, unique keys, foreign keys, check constraints—encode business rules inside the DBMS so every program does not re-implement the same validation differently.

1
2
3
4
SELECT CUST_ID, CUST_NAME, CITY
  FROM CUSTOMER
 WHERE CITY = 'LONDON'
 ORDER BY CUST_NAME;

Mentally: CUSTOMER is the table; CUST_ID, CUST_NAME, and CITY are columns; each qualifying customer is a row; ORDER BY only affects the result presentation, not a permanent physical “sorted forever” promise.

Base tables vs views

IBM distinguishes several table-related objects. For beginners, the two you must separate clearly are the base table and the view.

Base tables

A base table is the most common type. You create it with CREATE TABLE. The description is persistent in the catalog, and the data is persistent until you delete or drop it. All programs that refer to that table name (with the right privileges) refer to the same description and the same instance of the data. That shared, catalogued object is why Db2 can enforce integrity and recover data after failures.

Views

A view is an alternative way of representing data that already exists in one or more tables. A view can include all or some of the columns from one or more base tables, and it can filter rows. Applications often SELECT from a view as if it were a table. The view itself is not the primary warehouse of the business rows—the base tables are. Views help security (expose only certain columns), simplicity (pre-join or pre-filter), and stability (shield callers from base-table renames when designed carefully).

1
2
3
4
CREATE VIEW ACTIVE_CUSTOMER AS
SELECT CUST_ID, CUST_NAME, CITY
  FROM CUSTOMER
 WHERE STATUS = 'A';

You will get a full views lesson later. For now: if someone says “table,” ask whether they mean a base table that stores data, or a view (or other object) that presents data.

Other table kinds (awareness only)

Db2 also supports temporary tables, materialized query tables, temporal tables, clone tables, archive-related tables, and more. You do not need every type on day one. Knowing that “table” in conversation sometimes means “something SELECT-able” helps you ask better questions of DBAs and docs.

How tables relate to table spaces

Applications think in tables. Operations and storage think in table spaces inside databases. A table space is a logical unit of storage—a page set of VSAM data sets that hold table data. Indexes live in related index spaces. Understanding the stack keeps CREATE TABLE clauses and DBA conversations from sounding like magic words.

For recommended partition-by-growth and partition-by-range (universal / UTS) table spaces, each table space contains data for only a single table. Older segmented and simple table spaces could hold multiple tables; those non-UTS designs are deprecated for base tables in current Db2 for z/OS guidance. Beginners should assume “one table per modern table space” unless a shop still runs legacy multi-table spaces.

When you CREATE TABLE, you often name a database and table space with an IN clause, or you let Db2 implicitly create storage objects. Either way, the table is not floating in the abstract—it occupies space that utilities COPY, REORG, and RECOVER can address at the table-space level. That is why DBAs care about table spaces even when application developers mostly name tables in SQL.

1
2
3
4
5
6
7
CREATE TABLE HR.EMPLOYEE (
    EMPNO      CHAR(6)      NOT NULL,
    FIRSTNME   VARCHAR(12)  NOT NULL,
    LASTNAME   VARCHAR(15)  NOT NULL,
    DEPTNO     CHAR(3),
    PRIMARY KEY (EMPNO)
) IN MYDB.MYTS;

Here HR is the schema qualifier, EMPLOYEE is the table name, MYDB is the database, and MYTS is the table space. You will deepen schemas, databases, and table spaces on their own pages; keep the relationship: table = logical data, table space = storage container.

Simple CREATE TABLE mental model

You do not need every CREATE TABLE clause on the first day. A useful mental checklist is:

• Name — usually schema.table (explicit qualifier) or an unqualified name that Db2 qualifies for you
• Columns — name, data type, length/precision, NULL or NOT NULL, optional DEFAULT
• Keys and rules — PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK as needed
• Placement — IN database.tablespace (or rely on implicit creation rules)

1
2
3
4
5
6
7
8
9
CREATE TABLE ORDERS (
    ORDER_ID    INTEGER        NOT NULL,
    CUST_ID     INTEGER        NOT NULL,
    ORDER_DATE  DATE           NOT NULL,
    ORDER_AMT   DECIMAL(11,2)  NOT NULL,
    STATUS      CHAR(1)        NOT NULL WITH DEFAULT 'O',
    PRIMARY KEY (ORDER_ID),
    FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER (CUST_ID)
);

Read that statement as a contract: every order has an id, a customer, a date, an amount, and a status; the id is unique; the customer must exist in CUSTOMER if the foreign key is enforced. After commit of the CREATE, INSERT statements add rows; SELECT reads sets of rows; utilities and indexes operate on the underlying storage. That is the everyday life cycle of a base table.

What CREATE TABLE is not

CREATE TABLE does not by itself guarantee good performance. Indexes, statistics (RUNSTATS), buffer pools, and SQL design matter. It also does not replace application design: if you model the wrong grain (for example one column holding five phone numbers), SQL stays painful. Treat CREATE TABLE as defining the shared shape of truth—then design access paths and programs around that shape.

Altering and dropping

Over time you ALTER TABLE to add columns or constraints, and DROP TABLE when an object is obsolete (with care for dependents such as views and foreign keys). Beginners should practice reading CREATE first; change management is a later operational skill. Always know whether you are changing a base table versus a view definition.

Explain It Like I'm Five

Imagine a big shared spreadsheet that the whole company uses. Each sheet is a Db2 table. The column headers are fixed (name, score, team). Each filled-in line is a row. The spreadsheet file sitting in a folder is a bit like the table space—the place the sheet’s data actually lives on disk. A view is like a saved filter that shows only some columns or only the rows you care about, without making a second full copy of every fact. CREATE TABLE is how grown-ups invent a new sheet and decide what the headers mean.

Exercises

1. In your own words, explain the difference between a base table and a view to someone who only knows Excel.
2. Sketch CUSTOMER and ORDERS with a primary key and a foreign key. Which object is the base table in each case?
3. Why might a DBA ask which table space a new table should use, even though your SQL only names the table?
4. Write a minimal CREATE TABLE for a PRODUCT table with PRODUCT_ID, PRODUCT_NAME, and PRICE. Decide which columns are NOT NULL.
5. Why is “SELECT without ORDER BY” not guaranteed to return rows in insert order?