DB2 - Databases

DB2 - Working with Databases

This chapter covers the fundamental operations of creating, connecting to, and managing databases in Db2.

Database Logical Architecture

A Db2 instance can manage multiple databases. Each database is a self-contained unit with its own set of data, logs, and configuration. The logical hierarchy is as follows:

• Instance: The Db2 server process that manages system resources.
• Database: A collection of related data objects. It’s the primary unit of connection.
• Tablespaces: Logical storage units that group tables. They map to physical storage (containers).
• Schemas: A way to group and organize objects (like tables, views, indexes) within a database.
• Tables, Views, Indexes: The actual objects that store and provide access to data.

Physically, Db2 organizes all this information in a structured directory on the server’s file system, but as a user, you primarily interact with the logical objects through SQL.

Creating a Database

The CREATE DATABASE command initializes a new database. By default, it grants a set of privileges to PUBLIC, allowing any user to connect and create tables.

Standard Database Creation

Syntax to create a new database with default settings:

CREATE DATABASE <database_name>

Example:

db2 CREATE DATABASE sample

Output:

DB20000I  The CREATE DATABASE command completed successfully.

Best Practice: Specifying a Codeset

It is highly recommended to specify the UTF-8 codeset to ensure your database can handle international characters.

db2 CREATE DATABASE myappdb USING CODESET UTF-8 TERRITORY US

Creating a Restrictive Database

A restrictive database does not grant default privileges to PUBLIC. You must explicitly grant permissions for users to access it. This is a best practice for production environments.

Syntax:

CREATE DATABASE <database_name> RESTRICTIVE

Example:

db2 CREATE DATABASE proddb RESTRICTIVE

Listing Databases

To see all databases cataloged within the current instance:

db2 LIST DATABASE DIRECTORY

Output (example):

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/db2inst1

Database 2 entry:

 Database alias                       = PRODDB
 Database name                        = PRODDB
 Local database directory             = /home/db2inst1

Connecting to a Database

Before you can execute SQL queries, you must establish a connection to a specific database.

Syntax:

CONNECT TO <database_name>

Example:

db2 CONNECT TO sample

Output:

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.8
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

To disconnect, use db2 CONNECT RESET.

Activating and Deactivating a Database

Activating a database (ACTIVATE DATABASE) pre-starts its services and allocates memory, which can improve the performance of the first connection. This is often done for production databases after a server restart.

db2 ACTIVATE DATABASE sample

Deactivating (DEACTIVATE DATABASE) shuts down the database services if there are no active connections. This is rarely needed unless performing specific maintenance tasks.

db2 DEACTIVATE DATABASE sample

Configuring the Database

Db2 provides extensive configuration parameters at both the instance (Database Manager) and database levels. You can view and update them using the CLP.

View Database Configuration

Syntax:

db2 GET DATABASE CONFIGURATION FOR <db_name>

# Short alias
db2 GET DB CFG FOR <db_name>

Update Database Configuration

Syntax:

db2 UPDATE DB CFG FOR <db_name> USING <parameter_name> <value>

Example: Increase the primary log file size.

db2 UPDATE DB CFG FOR sample USING LOGFILSIZ 4096

Note: Some parameter changes require a database reconnection or reactivation to take effect.

Dropping a Database

Warning: This command is irreversible and will permanently delete the database, all its objects, and all its data.

You must not be connected to the database you intend to drop.

Syntax:

DROP DATABASE <db_name>

Example:

db2 DROP DATABASE sample

Output:

DB20000I  The DROP DATABASE command completed successfully.