DB2 – Schemas
This chapter introduces and describes the concept of Schema.
A schema is a collection of named objects classified logically in the database.
In a database, you cannot create multiple database objects with same name. To do so, the schema provides a group environment. You can create multiple schemas in a database and you can create 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 “Professional” and “Personal” for an “employee” database. It is possible to make two different tables with the same name “Employee”. In this environment, one table has professional information and the other has personal information of employee. In spite of having two tables with the same name, they have two different schemas “Personal” and “Professional”. Hence, the user can work with both without encountering any problem. This feature is useful when there are constraints on the naming of tables.
Let us see few commands related to Schema:
Getting currently active schema
db2 get schema
Example: [To get current database schema]
db2 get schema
Setting another schema to current environment
db2 set schema=<schema_name>
Example: [To arrange ‘schema1’ to current instance environment]
db2 set schema=schema1
Creating a new Schema
Syntax: [To create a new schema with authorized user id]
db2 create schema <schema_name> authroization <inst_user>
Example: [To create “schema1” schema authorized with ‘db2inst2”]
db2 create schema schema1 authorization db2inst2
Let us create two different tables with same name but two different schemas. Here, you create employee table with two different schemas, one for personal and the other for professional information.
Step 1: Create two schemas.
Schema 1: [To create schema named professional]
db2 create schema professional authorization db2inst2
Schema 2: [To create schema named personal]
db2 create schema personal authorization db2inst2
Step 2: Create two tables with the same name for Employee details
[To create a new table ‘employee’ in the database using schema name ‘professional’]
db2 create table professional.employee(id number, name varchar(20), profession varchar(20), join_date date, salary number);
[To create a new table ‘employee’ in the same database, with schema name ‘personal’]
db2 create table personal.employee(id number, name varchar(20), d_birth date, phone bigint, address varchar(200));
After executing these steps, you get two tables with same name ’employee’, with two different schemas.