Database

In DB2 on the mainframe, a database is a logical structure that helps organize and manage tablespaces, tables, indexes, and other database objects. A database serves as a container for these objects and provides organizational and administrative control over the data within DB2.

What is a Database?

A DB2 database on the mainframe is essentially a collection of tablespaces, tables, and indexes that are logically grouped together. It enables data storage and retrieval while allowing centralized control over access, organization, and resource allocation. In DB2, each database is a self-contained unit, which makes it easier to manage large sets of data within a DB2 subsystem.

Purpose -

• Logical Organization: Provides a structured way to manage related tablespaces and tables.
• Access Control: Manages permissions for users and applications.
• Storage Management: Allows efficient use of storage by assigning tablespaces to different storage groups.

Creating a Database

Creating a database in DB2 involves defining its name and assigning it to a storage group (a collection of physical storage volumes). CREATE DATABASE statement is used to create the database.

Syntax -

CREATE DATABASE database_name
STOGROUP storage_group_name
BUFFERPOOL bufferpool_name
[CCSID UNICODE | EBCDIC];

• database_name: The name of the database you want to create. This name must be unique within the DB2 subsystem.
• STOGROUP: Specifies the storage group where the database's tablespaces will reside.
• BUFFERPOOL: Indicates the buffer pool to be used by this database.
• CCSID: Specifies the character encoding for data stored in this database.
  • UNICODE: Uses Unicode encoding for character data.
  • EBCDIC: Uses EBCDIC encoding (default on mainframes).

Examples - Creating a Database

CREATE DATABASE EMPLDB
STOGROUP EMPLSG
BUFFERPOOL BP0
CCSID UNICODE;

• This statement creates a database named EMPLDB.
• STOGROUP is set to EMPLSG, meaning that the tablespaces created in this database will use the physical volumes defined in this storage group.
• BUFFERPOOL is set to BP0, which will help DB2 efficiently cache data.
• CCSID is set to UNICODE, meaning that character data will be stored using Unicode encoding.

Altering a Database

The ALTER DATABASE statement allows us to modify certain properties of an existing database. We can change the storage group, buffer pool, or character encoding.

Syntax -

ALTER DATABASE database_name
STOGROUP new_storage_group_name
BUFFERPOOL new_bufferpool_name
CCSID new_encoding;

Examples - Altering a Database

ALTER DATABASE EMPLDB
STOGROUP EMPLSG1
BUFFERPOOL BP1;

• This command alters the EMPLDB database.
• STOGROUP is updated to EMPLSG1, meaning that any new tablespaces created will use volumes from the new storage group.
• BUFFERPOOL is changed to BP1, modifying the memory cache used for this database.

Deleting a Database

To delete a database, use the DROP DATABASE statement. This action removes the database along with all associated objects, including tablespaces, tables, and indexes.

Syntax -

DROP DATABASE database_name;

Examples - Altering a Database

DROP DATABASE EMPLDB;

Explanation: This command deletes the EMPLDB database and all associated objects within it.