DB2 - Schemas

Db2 - Schemas

This chapter introduces the concept of a schema, a fundamental organizational and security mechanism in a Db2 database.

Introduction to Schemas

A schema is a named collection of database objects, such as tables, views, triggers, and procedures. It functions as a logical grouping mechanism, or a ‘namespace’, within the database. Schemas are essential for organizing a database and managing permissions.

One of the primary benefits of schemas is that they allow you to have multiple objects with the same name in a single database, as long as they belong to different schemas. For example, you could have SALES.CUSTOMERS and MARKETING.CUSTOMERS, which are two distinct tables.

Every object in a Db2 database is uniquely identified by its two-part name: schema_name.object_name. If you do not specify a schema when referencing an object, Db2 uses the schema defined in the CURRENT_SCHEMA special register, which is typically set to your user ID by default.

Common Schema Commands

Getting the Currently Active Schema

To check your current default schema, query the CURRENT_SCHEMA special register. This is the standard SQL way to retrieve this information.

VALUES CURRENT_SCHEMA;

Output:

1
-----------
DB2INST1

  1 record(s) selected.

Setting the Current Schema for a Session

You can change your default schema for the duration of your database session. This is useful to avoid having to type the schema name repeatedly.

SET SCHEMA = 'NEWSCHEMA';

After this command, any SQL statement like CREATE TABLE my_table... will create NEWSCHEMA.MY_TABLE.

Creating a New Schema

You can create a new schema explicitly. Optionally, you can assign ownership of the schema to a different user or role.

Syntax:

CREATE SCHEMA <schema_name>
AUTHORIZATION <user_or_role_name>;

Example: Create a schema named FINANCE owned by the user db2admin.

CREATE SCHEMA FINANCE AUTHORIZATION db2admin;

A schema is also created implicitly the first time an object is created with a new, valid schema name by a user with IMPLICIT_SCHEMA authority (which is granted to PUBLIC by default).

Practical Exercise: Using Schemas to Organize Data

Let’s walk through a practical example of using schemas to separate employee data into professional and personal categories.

Step 1: Create the Schemas

First, we create two distinct schemas to act as containers for our tables.

-- Create a schema for professional employee data
CREATE SCHEMA PROFESSIONAL;

-- Create a schema for personal employee data
CREATE SCHEMA PERSONAL;

Step 2: Create Tables within Each Schema

Now, we create two tables, both named EMPLOYEES, but each within a different schema. Notice the different column definitions for each table.

-- Table for professional information
CREATE TABLE PROFESSIONAL.EMPLOYEES (
    employee_id INT NOT NULL PRIMARY KEY,
    job_title VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

-- Table for personal information
CREATE TABLE PERSONAL.EMPLOYEES (
    employee_id INT NOT NULL PRIMARY KEY,
    full_name VARCHAR(100),
    date_of_birth DATE,
    home_phone VARCHAR(20),
    address VARCHAR(200)
);

Step 3: Insert and Query Data

When inserting or querying data, we must use the fully qualified schema.table name to avoid ambiguity.

-- Insert data into the professional table
INSERT INTO PROFESSIONAL.EMPLOYEES (employee_id, job_title, hire_date, salary)
VALUES (101, 'Software Engineer', '2023-08-15', 85000.00);

-- Insert data into the personal table
INSERT INTO PERSONAL.EMPLOYEES (employee_id, full_name, date_of_birth, address)
VALUES (101, 'Jane Doe', '1995-05-20', '123 Main St, Anytown');

-- Retrieve data from both tables for employee 101
SELECT * FROM PROFESSIONAL.EMPLOYEES WHERE employee_id = 101;
SELECT * FROM PERSONAL.EMPLOYEES WHERE employee_id = 101;

This exercise demonstrates how schemas enable clean data organization and prevent naming conflicts, which is crucial in complex database environments.