Db2 columns

Column names and data types

IBM defines a column as a set of values of the same type. In CREATE TABLE you list each column with a name and a data type (and often length, precision, and nullability). Names should be clear, stable, and consistent across related tables—CUST_ID in CUSTOMER and ORDERS beats CUST_ID in one table and CUSTOMER_NUMBER in another without a good reason.

Within one table, column names must be unique. Across tables, reuse is normal. In joins, qualify columns (c.CITY, o.CITY) so Db2—and humans—know which table you mean.

1
2
3
4
5
6
7
8
CREATE TABLE CUSTOMER (
    CUST_ID     INTEGER       NOT NULL,
    CUST_NAME   VARCHAR(40)   NOT NULL,
    CITY        VARCHAR(30),
    STATUS      CHAR(1)       NOT NULL,
    OPENED_ON   DATE          NOT NULL,
    PRIMARY KEY (CUST_ID)
);

Type choice is not decoration. Comparison, sorting, arithmetic, and host-variable mapping all depend on it. Putting money in VARCHAR invites silent formatting bugs; putting codes in INTEGER when they have leading zeros invites display and join pain. Later tutorials deep-dive each type; here, treat type as part of the column’s contract with every program that touches the table.

Naming habits that age well

• Prefer readable names — ORDER_DATE over OD
• Avoid reserved-word traps — if you must use a reserved word, delimit carefully; better to rename
• Align with shop standards — abbreviations, prefixes, and case conventions exist so hundreds of tables stay searchable
• Document units — AMOUNT in cents vs dollars belongs in the name or comments and design docs

NULL vs NOT NULL

If you do not say otherwise, Db2 allows null values in a column. Users can insert a row without providing that column’s value. The NOT NULL clause disallows nulls. Primary keys must be defined as NOT NULL.

NULL is not blank and not zero. Predicates like CITY = 'LONDON' do not find rows where CITY is null. Use CITY IS NULL. Aggregates and joins also treat nulls specially—another reason to decide nullability deliberately instead of leaving every column nullable “just in case.”

1
2
3
4
5
6
7
8
9
-- Required facts vs optional facts
CREATE TABLE EMP (
    EMPNO     CHAR(6)      NOT NULL,
    FIRSTNME  VARCHAR(12)  NOT NULL,
    MIDINIT   CHAR(1),
    LASTNAME  VARCHAR(15)  NOT NULL,
    HIREDATE  DATE         NOT NULL,
    PRIMARY KEY (EMPNO)
);

MIDINIT can be null when someone has no middle initial. HIREDATE cannot—every employee has a hire date in this model. That single NOT NULL decision prevents a class of incomplete rows from ever entering the table.

Defaults

Db2 can supply default values when INSERT omits a column. Some defaults are built in for certain types; you define others with the DEFAULT clause on CREATE TABLE or ALTER TABLE. Defaults pair naturally with NOT NULL: the column is always populated, but callers need not repeat the same literal every insert.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE ORDERS (
    ORDER_ID    INTEGER        NOT NULL,
    CUST_ID     INTEGER        NOT NULL,
    ORDER_DATE  DATE           NOT NULL WITH DEFAULT CURRENT DATE,
    STATUS      CHAR(1)        NOT NULL WITH DEFAULT 'O',
    ORDER_AMT   DECIMAL(11,2)  NOT NULL WITH DEFAULT 0,
    PRIMARY KEY (ORDER_ID)
);
 
INSERT INTO ORDERS (ORDER_ID, CUST_ID)
VALUES (5001, 1001);
-- ORDER_DATE, STATUS, and ORDER_AMT take defaults

Defaults are not magic integrity. A default of zero for ORDER_AMT may hide a missing price if programmers forget to set the real amount. Use defaults for true standard values (open status, today’s date when that is the business rule), not as a silent cover for incomplete input.

NULL vs DEFAULT as design alternatives

For “unknown,” NULL is often clearer. For “not provided, use the normal starting value,” DEFAULT is clearer. Shops sometimes forbid nulls in application tables and use sentinels; that works only if everyone agrees what the sentinel means. Document the rule next to the column design.

Column order and SELECT * pitfalls

Rows have no fixed order. Columns do. The order of columns is the order you specified when you defined the table (IBM’s introduction material states this explicitly). That ordinal position shows up in SELECT *, in some utilities, and in anything that maps results by position instead of by name.

1
2
3
4
5
6
7
-- Fragile: shape changes if the table gains a column
SELECT * FROM CUSTOMER;
 
-- Stable for applications: ask for what you need
SELECT CUST_ID, CUST_NAME, STATUS
  FROM CUSTOMER
 WHERE CITY = 'LONDON';

SELECT * is fine for ad-hoc exploration in SPUFI or an interactive tool. In application programs it is a common source of breakage:

• Host structures — COBOL or C layouts expect a fixed list; a new column shifts or expands the result
• Positional FETCH — binding by ordinal fails when ordinals change
• Over-fetching — pulling LOB or wide columns you do not need wastes CPU and network
• Accidental exposure — views and column privileges exist partly so callers do not see every base column; SELECT * fights that discipline

INSERT column lists

The same idea applies to INSERT. Prefer an explicit column list so reordering or adding columns does not silently mis-map values:

1
2
3
4
5
6
7
-- Prefer this
INSERT INTO CUSTOMER (CUST_ID, CUST_NAME, STATUS)
VALUES (1002, 'NORTH WIND LLC', 'A');
 
-- Avoid relying on "all columns in create order" unless you control both sides tightly
INSERT INTO CUSTOMER
VALUES (1002, 'NORTH WIND LLC', NULL, 'A', CURRENT DATE);

ALTER TABLE and dependent code

When you ADD COLUMN, existing SELECT column lists keep working; SELECT * and positional inserts may not. Plan application changes with DDL changes. That operational habit is as important as knowing the syntax.

Explain It Like I'm Five

A column is one labeled blank on every card in the box—like “team name” or “score.” The label tells you what kind of answer fits (numbers, words, dates). Some blanks must be filled (NOT NULL). Some can say “I don’t know yet” (NULL). Sometimes if you skip a blank, a helper pencil writes a normal answer for you (DEFAULT). When you ask for cards, say which blanks you want—don’t yell “give me everything” (SELECT *) if you only need the names.

Exercises

1. Design columns for a BOOK table (isbn, title, published date, page count). Mark each NOT NULL or nullable and justify one choice.
2. Write CREATE TABLE for BOOK with at least one DEFAULT.
3. List three problems that can happen if a COBOL program uses SELECT * on BOOK and a DBA later adds a COVER_IMAGE CLOB column.
4. Explain why CITY VARCHAR(30) and CITY CHAR(30) are not interchangeable for trailing blanks and storage.
5. Rewrite INSERT INTO T VALUES (...) for a three-column table to use an explicit column list.