DB2 Terminology

DB2 has its own vocabulary. This page defines the objects you will meet in every DB2 discussion: databases, tablespaces, tables, indexes, views, synonyms, aliases, STOGROUPs and bufferpools.

Database and DBD

• A database is a collection of physical and logical DB2 objects.
• One DB2 system can manage up to 64,000 separate databases; one database per application is recommended.
• Each database has a Data Base Descriptor (DBD), with a maximum size of 2 GB.
• Each time an object is created, DB2 writes descriptive and control information in the DBD.

Tablespace and indexspace

• A tablespace holds tables. There are three types:
  1. Simple: the default tablespace type.
  2. Segmented: ideal for storing more than one table, especially relatively small tables.
  3. Partitioned: holds a single table; each partition contains boundary values of specific columns.
• An indexspace holds an index and cannot hold more than one index.
• DB2 creates the indexspace automatically when you run the CREATE INDEX statement.

Table, index, view, synonym, alias

• Table: a DB2 object of columns and rows that defines the physical characteristics of the data to be stored.
• Index: contains pointers ordered by the values in specified columns; lets DB2 access data more efficiently.
• View: a virtual table defined by a SQL SELECT statement. It presents any or all of the data in one or more tables or views, but it never stores data: when accessed, its defining SQL is executed.
• Synonym: a private alternative name for a table or view, usable only by the person who creates it. When the table is dropped, its synonyms are dropped but its aliases are retained.
• Alias: a locally defined name for a table or view that gives location independence. It can be used by users other than the creator.

STOGROUP and bufferpool

• STOGROUP: a set of disk (DASD) volumes given a unique name, used to allocate VSAM data sets for DB2 objects. Tablespaces and indexspaces are created either using a STOGROUP or using a VSAM VCAT.
• Bufferpool: a memory area. Data read from a DB2 table comes from disk (DASD), moves into a bufferpool, and is then returned to the requestor.

Creating a view: example

• The statements below create a view of employees in departments starting with B, then read from the view:-
  
  CREATE VIEW V1 AS SELECT EMPNO, DEPTNO, LNAME, FNAME, EXT FROM MM01.EMPLOYEE WHERE DEPTNO LIKE 'B%'; SELECT * FROM MM01.V1 ORDER BY DEPTNO, LNAME;
• CREATE VIEW is a DDL statement; the view V1 stores no data, it stores the SELECT.
• WHERE DEPTNO LIKE 'B%' keeps only rows whose department starts with the letter B.
• The second SELECT reads from the view exactly as if V1 were a table; DB2 runs the stored SELECT behind the scenes.