DB2 Schema

DB2 Schema is a collection of named objects classify logically in the database.

In a database, it is not possible to create multiple database objects with same name. To do so schema provides a group environment.

You can create multiple schemas in a database as well as multiple database objects with same name, with different schema groups.

A schema can contain tables, functions, indices, tablespaces, procedures, triggers etc.

For example, you create two different schemas named as "Regular" and "Parttime" for an "employee" database.

You can also create two different tables with the same name "Employee" where one table has regular information and the other has parttime information of employee. It doesn't have actually two tables with the same name in spite they have two different schemas "Regular" and "Parttime".

It facilitates user to work with both without facing any problem. This feature is useful when there are constraints on the naming of tables.

Schema Image representation

Get currently active schema

Syntax:

We have a database "employee":

Let's take an example to get the current database schema:

Use the following command to get and change the current schema you are logged in:

Output:

Set another schema to current environment

Syntax:

Example:

Let's change the schema by using the following command:

Output:

Verify the changed schema

You can verify that the schema has been successfully changed by using the following command:

Create a new Schema

Syntax:

Example:

Let's create a new schema with a different authorized user id. Create "new_schema" schema authorized with 'sonoo'

Output:

Let us create two different tables with same name but two different schemas. Here, you create employee table with two different schemas, one for Regular and the other for Parttime.

Step 1: Create two schemas.

Schema 1: [To create schema named Regular]

db2 create schema Regular authorization db2admin

Schema 2: [To create schema named Parttime]

db2 create schema Parttime authorization db2admin

Now you can see that both commands are executed successfully.

Now create two tables with the same name for Employee details

Table1: Regular.employee