Db2 rows

What a row represents

IBM describes a row as a sequence of values such that the nth value is a value of the nth column of the table. In business language: a row is one instance of whatever the table is about. In CUSTOMER, one row is one customer. In ORDERS, one row is one order. In ORDER_LINE, one row is one line on an order.

Getting that grain right is a design skill. If you store three phone numbers mashed into one column, you no longer have clean rows for “phone.” If you create one row per phone but forget which customer owns it, you lose the relationship. Rows are cheap to insert; messy grain is expensive forever.

The rows of a relational table have no fixed order. Db2 may return them in an order that happens to match an index today and a different order tomorrow after REORG. When order matters—reports, pagination, deterministic tests—use ORDER BY. When you process rows in a COBOL cursor loop, each FETCH yields one row from the current result set, not “the next physical record on disk” in a file sense.

1
2
3
4
5
6
7
INSERT INTO CUSTOMER (CUST_ID, CUST_NAME, CITY, STATUS)
VALUES (1001, 'RIVER BANK LTD', 'LONDON', 'A');
 
SELECT CUST_ID, CUST_NAME
  FROM CUSTOMER
 WHERE STATUS = 'A'
 ORDER BY CUST_NAME;

INSERT adds a row. SELECT returns a result table—a set of rows produced by the query—which may be empty, one row, or many rows. The result table is not a permanent base table object; it is what the statement produced this time.

Empty tables are still tables

A table can have zero rows. That is normal after CREATE TABLE and before the first INSERT, or after deletes. SELECT simply returns no rows. Programs must handle “not found” (SQLCODE +100 in many embedded SQL styles) as a valid outcome, not as a system crash.

Fixed vs variable-length rows

Beginners often hear DBAs talk about “row length” and “varchar rows.” The idea is simple: some column types contribute a predictable number of bytes; others grow and shrink with the data.

A column defined as CHAR(10) always reserves ten characters of character data (blank-padded if the value is shorter). VARCHAR(40) stores the characters you actually use plus length information, so a name of three characters does not permanently occupy forty characters of payload. Numeric types have their own fixed storage sizes. Mix CHAR and VARCHAR in one table and two rows can differ in total length even though they share the same column list.

1
2
3
4
5
6
CREATE TABLE PRODUCT (
    PRODUCT_ID   INTEGER      NOT NULL,
    SKU          CHAR(12)     NOT NULL,
    DESCRIPTION  VARCHAR(100) NOT NULL,
    PRIMARY KEY (PRODUCT_ID)
);

SKU is fixed-width; DESCRIPTION varies. A product with a short description occupies less variable space than one with a long description. That matters for page packing, REORG behavior, and estimating space—topics DBAs own—but application developers should still choose CHAR vs VARCHAR intentionally: codes and flags often fit CHAR; names and free text usually fit VARCHAR.

What “fixed vs variable row” does not mean

It does not mean the table somehow changes its columns per row. Every row still has the same columns. It also does not mean you can skip defining types. Variable length is about storage of values inside a stable column layout.

NULL in a row

Some columns cannot have a meaningful value in every row. Db2 uses a special indicator—the null value—to mean that no data is present (unknown or not applicable). If you do not specify otherwise, Db2 allows nulls in a column. NOT NULL disallows them. Primary key columns must be NOT NULL.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE DEPT (
    DEPTNO    CHAR(3)      NOT NULL,
    DEPTNAME  VARCHAR(36)  NOT NULL,
    MGRNO     CHAR(6),
    ADMRDEPT  CHAR(3)      NOT NULL,
    PRIMARY KEY (DEPTNO)
);
 
-- Find departments with no manager assigned
SELECT DEPTNO, DEPTNAME
  FROM DEPT
 WHERE MGRNO IS NULL;

MGRNO can be NULL when a department has no manager yet. That is different from storing six blanks or a fake id like '999999'. Nulls do not satisfy ordinary comparisons: MGRNO = '000010' is unknown when MGRNO is null, not true or false in the usual two-valued sense. Use IS NULL and IS NOT NULL. Host languages often need null indicator variables when fetching nullable columns.

NULL vs defaults

Sometimes shops prefer a default value (spaces, zero, a sentinel date) instead of NULL so programs never see indicators. Defaults are familiar; nulls are more honest for “unknown.” Either choice is a design decision—just be consistent and document it. Mixing “sometimes null, sometimes blank meaning unknown” creates bugs that are hard to spot.

Row identity vs business keys

“Which row is this?” is both a business question and a database question. Keep the vocabulary straight.

A business key is what the organization already uses: customer number, policy number, International Bank Account Number. When that value is unique and stable, it often becomes the primary key. When business keys change, collide, or are composite and awkward, designers introduce a surrogate key—an integer identity, a sequence-generated value—and keep the business key as a unique alternate column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Business key as primary key
CREATE TABLE CUSTOMER (
    CUST_NO    CHAR(8)      NOT NULL,
    CUST_NAME  VARCHAR(40)  NOT NULL,
    PRIMARY KEY (CUST_NO)
);
 
-- Surrogate primary key + unique business key
CREATE TABLE CUSTOMER_SUR (
    CUST_ID    INTEGER      NOT NULL,
    CUST_NO    CHAR(8)      NOT NULL,
    CUST_NAME  VARCHAR(40)  NOT NULL,
    PRIMARY KEY (CUST_ID),
    UNIQUE (CUST_NO)
);

Foreign keys usually reference the primary (or unique) key of the parent. Child rows “point” by value, not by a physical address you hard-code in the application. That is relational identity: stable key values, not “record number 47 on the disk.”

Duplicates and discipline

Without uniqueness rules, you could insert two identical rows and lose the ability to update or delete just one of them cleanly. Primary keys and unique constraints exist so each row has a clear identity. When debugging “I updated two rows,” check whether your WHERE clause matched a non-unique business attribute (last name only) instead of the key.

• Identify — primary / unique key values
• Describe — other columns on the row
• Relate — foreign key columns pointing at other tables’ keys

Explain It Like I'm Five

Think of a box of trading cards. Each card is a row. Every card has the same printed blanks—name, team, number—those blanks are the columns. Some cards write a short name; some write a long name (variable length). If the “manager” blank is empty, that is like NULL—we do not know yet. The shiny number that no other card shares is how we tell cards apart, like a key. The box can be empty, but it is still a box for cards.

Exercises

1. For an ENROLLMENT table linking students and courses, what does one row represent? Write one sentence.
2. Give one reason to use VARCHAR for a street address and CHAR for a two-letter state code.
3. Write a SELECT that finds rows where MIDDLE_NAME is unknown, assuming nulls are used.
4. Pick a real entity (library book, employee, invoice). Propose a business key and say whether you would also want a surrogate primary key.
5. Explain why UPDATE EMPLOYEE SET SALARY = SALARY * 1.05 WHERE LASTNAME = 'SMITH' can be dangerous without a unique key in the predicate.