DB2 databases and storage groups (DDL)

DATABASE as a DDL object

In Db2 for z/OS a database is not “the whole subsystem” and not a schema. It is a named set of table spaces (and their tables) plus associated index spaces. CREATE DATABASE writes catalog rows; it does not allocate the big VSAM files by itself. Those files appear when you CREATE TABLESPACE or CREATE INDEX (or when Db2 creates them implicitly for CREATE TABLE).

Group related table spaces in one database so START, STOP, and DISPLAY have a sensible unit. Do not put every table in DSNDB04 just because the default exists.

CREATE DATABASE

1
2
3
4
5
6
7
8
9
10
11
12
CREATE STOGROUP HRSTO
  VOLUMES (*)
  VCAT DB2CAT
  DATACLAS DB2DATA
  MGMTCLAS DB2MGMT
  STORCLAS DB2STOR;
 
CREATE DATABASE HRDB
  STOGROUP HRSTO
  BUFFERPOOL BP2
  INDEXBP BP3
  CCSID UNICODE;

Special CREATE DATABASE forms exist for work file and temporary databases (AS WORKFILE / AS TEMP style usage). Those are subsystem design, not application HR tables. Do not create user tables in DSNDB06 (the catalog database) or in work-file databases.

Authorization is typically CREATEDBA (create and own) or CREATEDBC (create, DBCTRL later). The creator becomes the owner. Naming standards usually reserve SYS* for IBM objects.

ALTER DATABASE

ALTER DATABASE changes the defaults for the database:

• BUFFERPOOL — default for new table spaces
• INDEXBP — default for new indexes (4 KB through 32 KB pools as documented)
• STOGROUP — default storage group for new spaces and indexes
• CCSID — default encoding for new table spaces, only to a compatible CCSID (for example euro-symbol variants of the same encoding)

1
2
3
ALTER DATABASE HRDB
  BUFFERPOOL BP2
  INDEXBP BP2;

This does not rebuild EMP_TS that already exists. To move an existing table space to another buffer pool, ALTER TABLESPACE (often a pending change that needs REORG). Beginners mix up “database default” with “everything in the database just changed.”

Database buffer pool and encoding

Database buffer pool defaults are a convenience. Actual page size of a table space comes from the buffer pool named on CREATE TABLESPACE (BP0/BP1/BP2 are 4 KB; BP8K0 is 8 KB, and so on). Indexes usually live in 4 KB pools; large keys sometimes need 8 KB or 16 KB INDEXBP.

Database encoding (CCSID ASCII / EBCDIC / UNICODE) is the default encoding scheme for table spaces created in the database. Mixing EBCDIC and UNICODE in one SQL statement forces conversions. New application databases are often UNICODE; legacy HR systems are often EBCDIC. You cannot casually ALTER an existing table space from EBCDIC to UNICODE without a documented conversion path.

CREATE STOGROUP

A storage group names the DASD and catalog Db2 uses for Db2-managed data sets:

• VOLUMES (vol1, vol2, …) — explicit volumes (older style)
• VOLUMES (*) — SMS chooses volumes; preferred with DATACLAS, MGMTCLAS, STORCLAS
• VCAT — ICF catalog name or alias that will own the VSAM entries

1
2
3
CREATE STOGROUP DSN8G910
  VOLUMES (ABC005, DEF008)
  VCAT DSNCAT;

IBM recommends SMS for Db2 data sets. Volume lists go stale; SMS policies do not. CREATE STOGROUP does not fully validate that the volumes or catalog exist—the failure shows up when the first table space using the group is created (sometimes with an operator volume-mount request). ALTER STOGROUP can add or remove volumes; DROP STOGROUP requires that no spaces still use it.

You can also use user-managed VSAM (DEFINE CLUSTER yourself, then CREATE TABLESPACE USING VCAT). Storage groups are the Db2-managed path. Mixed shops exist; follow local standards.

Database status

Operations use Db2 commands, not SQL:

1
2
3
-DISPLAY DATABASE(HRDB) SPACENAM(*) LIMIT(*)
-START DATABASE(HRDB)
-STOP  DATABASE(HRDB) SPACENAM(EMPTS)

• RW — read/write, normal
• RO — read-only (sometimes during utilities)
• STOP — stopped; SQL cannot use the space
• COPY — copy-pending; take an image copy
• CHKP — check-pending (often RI)
• GRECP / LPL / GRECP family — group buffer pool recovery / LPL; data sharing recovery
• REORP / AREOR — REORG-pending or advisory REORG-pending

DISPLAY is the first tool when “the table exists but the program gets -904 / unavailable resource.” START DATABASE after STOP, or after a utility left a restricted state, is daily DBA work.

Database recovery

You rarely RECOVER a “database” as one blob. You recover table spaces (and indexes) that belong to the database, using image copies and logs. The database name is a convenient grouping on DISPLAY and on some utility control statements. After recovery, spaces may be in COPY-pending or require START. Catalog/directory databases (DSNDB01, DSNDB06) have special recovery procedures—do not experiment there.

Point-in-time recovery, QUIESCE, and COPY are table-space-level skills. The database object’s job in recovery is: know which spaces belong together, STOP what you must, DISPLAY until everything is RW.

Explain It Like I'm Five

A database is a labelled toy box: “HR toys go in this box, and new toys in this box should be blue (buffer pool) and written in this alphabet (CCSID).” A storage group is the shelf in the garage where the box actually sits—either you name the shelves (volumes) or you let the house rules (SMS) pick a shelf. CREATE DATABASE writes the label. CREATE STOGROUP picks the shelf. DISPLAY is looking at the box to see if someone put a “do not open” sticker (STOP, COPY-pending) on it.

Exercises

1. Write CREATE STOGROUP with VOLUMES (*) and a VCAT alias, then CREATE DATABASE that uses it with UNICODE and separate table-space and index buffer pools.
2. Explain why ALTER DATABASE BUFFERPOOL BP8K0 does not change an existing 4 KB table space.
3. List two restricted states DISPLAY DATABASE might show and what you would do next.
4. Why is SMS VOLUMES (*) usually better than a hard-coded volume list?
5. What is DSNDB04 and when would you still create your own database?

Frequently asked questions