Db2 databases

Db2 database as a logical container

IBM defines Db2 databases as a set of Db2 structures that include a collection of tables, their associated indexes, and the table spaces in which they reside. You define a database with CREATE DATABASE. Whenever a table space is created, it is assigned—explicitly or implicitly—to an existing database.

A single database can hold all the data for one application or a group of related applications. Collecting that data together lets you start or stop access as one operation and grant authorization patterns that treat the set as a unit. Authorized users can still access data across databases when privileges allow—databases organize management; they do not imprison SQL forever.

If you create a table space and do not specify a database name, the table space is created in the default database DSNDB04. If a table space is implicitly created and you omit IN on CREATE TABLE, Db2 can implicitly create a database for that table. Implicit names look like DSN00001, DSN00002, and so on. Sandboxes may rely on that; shared production usually prefers named databases your DBA standards recognize.

What a database is not

• Not a schema — schemas qualify SQL names (HR.EMPLOYEE)
• Not a table — tables are what SELECT names
• Not the entire Db2 subsystem — one subsystem hosts many databases
• Not automatically identical to a LUW “database” — product vocabulary differs; use z/OS meanings here

Database vs table space vs table

Application developers live at the table layer. Utilities and many operator commands care about table spaces and databases. When a DBA says “the PAYROLL database is stopped,” they mean access to the spaces in that database—not that the word PAYROLL disappeared from SQL dictionaries forever.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE APPDB
  STOGROUP APPSTO
  BUFFERPOOL BP1
  INDEXBP BP2;
 
CREATE TABLESPACE EMPTS IN APPDB
  USING STOGROUP APPSTO
  PRIQTY 100 SECQTY 100
  DEFINE YES;
 
CREATE TABLE HR.EMPLOYEE (
    EMPNO CHAR(6) NOT NULL,
    PRIMARY KEY (EMPNO)
) IN APPDB.EMPTS;

Read the stack bottom-up or top-down: APPDB contains EMPTS; EMPTS stores HR.EMPLOYEE’s data; SQL talks about HR.EMPLOYEE. Indexes on that table live in index spaces associated with the same database. Modern partition-by-growth and partition-by-range table spaces hold one table each. Deprecated multi-table segmented spaces still appear in older shops—know they exist, prefer current designs for new work.

Design pressure: do not overcrowd

IBM recommends minimizing the number of table spaces in each database. Too many spaces and tables in one database can hurt performance and manageability, increase contention, and grow log and maintenance cost. With UTS, “one table per table space” already pushes you toward more spaces—so spreading applications across thoughtfully named databases matters even more.

CREATE DATABASE mental model

When you CREATE DATABASE, you name an eventual collection of tables, indexes, and table spaces. The name is an unqualified identifier up to eight characters and must be unique among databases.

1
2
3
4
CREATE DATABASE MYDB
  STOGROUP MYSTOGRP
  BUFFERPOOL BP8K4
  INDEXBP BP4;

STOGROUP, BUFFERPOOL, and INDEXBP on CREATE DATABASE establish defaults you can override later on table space or index space definitions. Think of CREATE DATABASE as opening a labeled cabinet with default shelf rules—not as inserting rows.

Put tables that are used and operated together in the same database. Avoid dumping unrelated high-churn DDL workloads into one giant database—some CREATE/ALTER/DROP activity and utility phases limit concurrent access to that database. Very large internal database descriptors (DBDs) are another reason to split: DBDs grow as objects are defined and do not shrink immediately when objects are dropped.

Implicit creation reminder

If CREATE TABLE omits IN, Db2 may create an implicit database named DSNxxxxx. If CREATE TABLESPACE omits a database, the space goes into DSNDB04. Both behaviors are legal; both can surprise you in shared systems when objects land somewhere operators did not expect. Prefer explicit IN database.tablespace once you leave personal sandboxes.

Commands and operations (awareness)

Operators use -START DATABASE, -STOP DATABASE, and -DISPLAY DATABASE style commands (exact syntax depends on your interface) to manage availability and see status. Developers rarely issue those daily, but knowing why databases exist explains outage windows and “database is stopped” tickets.

1
2
3
4
5
6
-- Application SQL still names tables, not the database:
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00';
 
-- Placement was decided at CREATE time via IN APPDB.EMPTS

Explain It Like I'm Five

Think of a database as a labeled toy closet. Inside the closet are boxes (table spaces). Inside each modern box is one kind of toy set (a table). You play with the toys by name—“give me the red cars”—which is like SQL on tables. Grown-ups lock or open a whole closet at once when cleaning, which is like starting and stopping a database. The name sticker on the toys (schema) can say “Sam’s cars” even if the closet is labeled “Playroom.”

Exercises

1. Draw boxes labeled database → table space → table for a two-table ORDER application.
2. Write a CREATE DATABASE statement for APPORD with a storage group and buffer pool defaults (names can be fictional but valid eight-character DB name).
3. Why might two unrelated applications sharing one database cause operational pain during LOAD or DDL?
4. What database do you get if CREATE TABLESPACE omits the database name?
5. Explain to a Db2 LUW colleague why “database” on z/OS is not “the whole server catalog.”