Db2 Relational Database Anatomy: Tables, Rows, Columns, Keys, and Views

Tables give SQL a structure to query.

A COBOL program does not read a Db2 table by track number or page location. It issues SQL against a logical structure: tables, rows, columns, keys, relationships, and views. That separation is one reason a Db2 application can keep working even when storage, indexes, or access paths change underneath it.

This refresh explains the anatomy of a relational database from a Db2 application developer's point of view. The focus is not catalog administration. The focus is the structure a COBOL or SQL developer needs to understand before writing SELECT, INSERT, UPDATE, DELETE, and cursor logic.

What Relational Database Anatomy Means

A relational database stores data in tables. A table has rows and columns. Keys identify rows, foreign keys connect tables, and views present selected data from one or more base tables.

Db2 uses that logical model while the engine handles physical storage, access paths, locking, logging, and recovery. A developer usually works with the logical model first.

Tables Represent One Subject

A table should represent one subject, such as a customer, account, policy, claim, employee, transaction, or order. Mixing subjects in one table makes SQL harder to read and makes update rules harder to control.

CREATE TABLE CUSTOMER
 (CUST_NO     CHAR(10)     NOT NULL,
  CUST_NAME   VARCHAR(60)  NOT NULL,
  STATUS      CHAR(1)      NOT NULL,
  OPEN_DATE   DATE,
  PRIMARY KEY (CUST_NO));

In this example, the subject is one customer. Account balances, claim lines, and policy coverage rows belong in separate tables unless the business rule says otherwise.

Columns Store Named Values

A column stores one named value about the table subject. The column name should tell the reader what the value means, and the data type should match how Db2 and COBOL will use it.

Column	Meaning	Db2 type example	COBOL concern
CUST_NO	Customer identifier	CHAR(10)	Match host variable length exactly.
CUST_NAME	Customer display name	VARCHAR(60)	Use a varying-length host structure when needed.
STATUS	Business state	CHAR(1)	Validate expected values such as A or I.
OPEN_DATE	Date the customer was opened	DATE	Use the date format expected by the program and Db2.

Rows Represent Individual Facts

A row is one instance of the table subject. In a CUSTOMER table, one row represents one customer. In an ACCOUNT_TXN table, one row represents one transaction.

Rows have no guaranteed physical order from a SQL point of view. If a COBOL report needs account transactions by date, the SQL must say so:

SELECT TXN_DATE,
       TXN_AMT
  FROM ACCOUNT_TXN
 WHERE ACCT_NO = :WS-ACCT-NO
 ORDER BY TXN_DATE

Without ORDER BY, a program should not depend on row order.

Primary Keys Identify Rows

A primary key identifies one row in a table. It can be one column or a set of columns. A good primary key is stable, unique, and not nullable.

CREATE TABLE ACCOUNT
 (ACCT_NO     CHAR(12) NOT NULL,
  CUST_NO     CHAR(10) NOT NULL,
  BALANCE     DECIMAL(13,2),
  PRIMARY KEY (ACCT_NO));

In application work, primary keys show up in WHERE clauses, cursor predicates, update statements, delete statements, and joins. A weak key design usually becomes a maintenance problem later.

Composite Keys Use More Than One Column

A composite key uses two or more columns to identify a row. Linking tables often use composite keys because a row is unique only when two parent keys are combined.

CREATE TABLE ENROLLMENT
 (STUD_ID     CHAR(8) NOT NULL,
  COURSE_ID   CHAR(8) NOT NULL,
  ENROLL_DATE DATE,
  PRIMARY KEY (STUD_ID, COURSE_ID));

A COBOL program must provide all key columns when it reads or updates a row identified by a composite key.

Foreign Keys Connect Tables

A foreign key stores a value that points to a parent table key. This is how relational databases avoid disconnected business facts, such as an order without a customer.

ALTER TABLE ACCOUNT
  ADD CONSTRAINT FK_ACCOUNT_CUSTOMER
  FOREIGN KEY (CUST_NO)
  REFERENCES CUSTOMER (CUST_NO);

The related Db2 Relationships article explains one-to-one, one-to-many, and many-to-many patterns in more detail.

Views Present a Useful Shape of Data

A view is a named SQL definition that presents data from one or more base tables. A view can hide columns, join tables, simplify a query, or expose a controlled read-only shape to programs.

CREATE VIEW ACTIVE_CUSTOMER_V AS
SELECT CUST_NO,
       CUST_NAME,
       OPEN_DATE
  FROM CUSTOMER
 WHERE STATUS = 'A';

A program can then read the view as if it were a table:

SELECT CUST_NAME
  INTO :WS-CUST-NAME
  FROM ACTIVE_CUSTOMER_V
 WHERE CUST_NO = :WS-CUST-NO

For view types and update considerations, see Db2 View Classification.

Logical Design Is Separate from Physical Storage

The relational model lets the application work with tables and columns while Db2 manages physical storage. A table can have indexes, table spaces, buffer pools, partitions, and statistics that affect performance without changing the SELECT list a program uses.

That separation is useful, but it is not a reason to ignore physical design. A COBOL cursor can be logically correct and still slow if the access path is poor. Use Db2 Indexing and Db2 SQL Optimization Tips for COBOL Programs when performance matters.

How COBOL Uses the Anatomy

A COBOL program usually sees the relational anatomy through host variables and embedded SQL:

EXEC SQL
    SELECT CUST_NAME,
           STATUS,
           OPEN_DATE
      INTO :WS-CUST-NAME,
           :WS-STATUS,
           :WS-OPEN-DATE
      FROM CUSTOMER
     WHERE CUST_NO = :WS-CUST-NO
END-EXEC.

The SELECT list maps to columns. The WHERE clause uses a key. The host variables must match the Db2 column types closely enough to avoid conversion problems or unexpected truncation.

Common Beginner Mistakes

• Treating row order as fixed without using ORDER BY.
• Using SELECT * in production cursors when the program needs only a few columns.
• Putting multiple values in one column instead of using child rows.
• Choosing a primary key that can change during normal business processing.
• Confusing a view with stored data when the view only stores a query definition.

Anatomy Checklist Before Writing SQL

Question	Why it matters
What table owns the data?	Prevents joining to a table that only looks similar.
What row should qualify?	Drives the WHERE clause and host variables.
What columns are actually needed?	Keeps SELECT lists and COBOL target variables clean.
What key identifies the row?	Reduces accidental duplicate reads or updates.
Is a view hiding any filter or join?	Prevents surprise predicates or read-only behavior.

How This Fits with Db2 Objects

This article covers logical relational anatomy: tables, rows, columns, keys, relationships, and views. The later Db2 Objects article is the better place for a wider object list such as databases, table spaces, indexes, aliases, and packages.

For current product context, see IBM's Db2 for z/OS page and the ISO SQL framework.

FAQ

What are the basic parts of a relational database?

The basic parts are tables, rows, columns, keys, relationships, and views. Db2 also has physical and administrative objects, but those are separate from the basic logical model.

What is the difference between a row and a column?

A row is one instance of the table subject, such as one customer. A column is one named value about that subject, such as customer number or status.

Is a Db2 view the same as a table?

No. A view presents data through a stored query definition. The data comes from one or more base tables unless a specific Db2 feature stores results separately.

When the table subject, columns, keys, and relationships are clear, the embedded SQL in a COBOL program becomes easier to write, test, bind, and maintain.