DB2 Setup | Tiny Sparks Guru

Introduction to our sample database structure

Through the sample we will be using the basic structure of 2 tables to explain all the scenarios

Understand the EMP and Mentor Table

The EMP table represents all employees working in the organization. Every employee has a unique EMP_ID, a name, department, phone number, and salary details.

The MENTOR table captures employees who have volunteered to mentor others in specific skills. Here’s how it works:

When an employee decides to mentor in a particular skill (e.g., Java, SQL), the HR team adds an entry in the MENTOR table.
The MENTOR table references the EMP_ID from the EMP table, establishing a relationship between employees and the skills they are willing to mentor in.
Since an employee can mentor multiple skills, the same EMP_ID can appear multiple times in the MENTOR table, each time with a different skill.
The MENTOR_ID is a unique identifier for each entry in the MENTOR table and is automatically generated.

Relationship Between Tables

One-to-Many Relationship: A single employee (from EMP) can be associated with multiple entries in the MENTOR table (one per skill).
Foreign Key Constraint: The EMP_ID column in the MENTOR table references EMP_ID in the EMP table. If an employee is removed from the organization, the ON DELETE SET NULL constraint ensures that their mentorship records remain but without an associated employee ID.

Example Data Representation

EMP Table (Employees)

EMP_ID	NAME	DEPARTMENT	PHONE	SALARY
101	Alice	IT	9876543210	80000
102	Bob	HR	9123456789	75000
103	Charlie	IT	9345678901	90000

MENTOR Table (Mentorship Skills)

MENTOR_ID	EMP_ID	SKILLS
1	101	Java
2	101	SQL
3	103	Python
4	103	COBOL

Interpretation:

Alice (EMP_ID 101) is a mentor for Java and SQL.
Charlie (EMP_ID 103) is a mentor for Python and COBOL.
Bob (EMP_ID 102) is not a mentor for any skill, so he does not appear in the MENTOR table.

Relationship Diagram

SQL DML Queries

Make sure to clean up any existing elements before cleaning newer ones. Below query may fail if any of the element will be non existing. Fix those and run till we are sure all existing element are eliminate.

DROP VIEW EMP_VIEW;
DROP TRIGGER EMP_TRIG;
DROP SEQUENCE EMP_SEQ;
DROP SEQUENCE MENT_SEQ;
DROP TABLE MENTOR;
DROP TABLE EMP;
COMMIT;

Now create the element one after another.

Why we need unique Index explict

TBA (Add Note why we need to explict create unique index)

CREATE TABLE EMP(
    EMP_ID INTEGER NOT NULL PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    DEPARTMENT VARCHAR(10) NOT NULL,
    PHONE CHAR(10) NOT NULL,
    SALARY INTEGER)
IN SHRDB6.SHRTS6;

CREATE UNIQUE INDEX EMP_IDX
    ON EMP (EMP_ID);

TBA

CREATE TABLE MENTOR(
    MENTOR_ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    EMP_ID INTEGER,
    SKILLS VARCHAR(10),
    FOREIGN KEY (EMP_ID) REFERENCES EMP (EMP_ID)
    ON DELETE SET NULL
    )
IN SHRDB6.SHRTS6;

CREATE UNIQUE INDEX MENT_IDX
    ON MENTOR (MENTOR_ID);

Sequences

TBA

CREATE SEQUENCE EMP_SEQ
    START WITH 1000
    INCREMENT BY 1;
CREATE SEQUENCE MENT_SEQ
    START WITH 1000
    INCREMENT BY 1;

Trigger

note

Remove MODE DB2SQL for the DB2 v10 and above from below triggers.

CREATE TRIGGER EMP_TRIG
    NO CASCADE BEFORE INSERT ON EMP
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    WHEN (N.SALARY < 30000)
    SIGNAL SQLSTATE '30000'
    SET MESSAGE_TEXT = 'Salary too low!';

CREATE TRIGGER EMP_PHONE_CHK
    NO CASCADE BEFORE INSERT ON EMP
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    WHEN (N.PHONE NOT BETWEEN '0000000000' AND '9999999999')
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Invalid Phone Number!';

SQL Insert Queries

Reset any sequences and clean up any old remaining data for fresh testing.

-- Clean up any existing data
DELETE FROM MENTOR;
DELETE FROM EMP;
COMMIT;

-- Before Insert fix the sequence
ALTER SEQUENCE EMP_SEQ RESTART;
ALTER SEQUENCE MENT_SEQ RESTART;
COMMIT;

Insert 30 Rows in EMP table

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Alice', '9876543210', 50000, 'HR');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Bob', '8765432109', 60000,
'Research');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Charlie', '7654321098', 55000,
'Sales');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'David', '6543210987', 62000,
'Admin');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Eve', '5432109876', 58000, 'IT');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Frank', '4321098765', 61000, 'HR');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Grace', '3210987654', 57000,
'Research');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Hank', '2109876543', 63000,
'Sales');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Ivy', '1098765432', 52000,
'Admin');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Jack', '1987654321', 59000, 'IT');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Kate', '2876543210', 61000, 'HR');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Leo', '3765432109', 55000,
'Research');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Mona', '4654321098', 60000,
'Sales');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Nate', '5543210987', 58000,
'Admin');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Olivia', '6432109876', 62000, 'IT');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Paul', '7321098765', 53000, 'HR');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Quinn', '8210987654', 59000,
'Research');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Rachel', '9109876543', 60000,
'Sales');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Steve', '1098765433', 58000,
'Admin');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Tina', '2098765432', 61000, 'IT');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Uma', '3098765431', 54000, 'HR');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Victor', '4098765430', 63000,
'Research');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Wendy', '5098765439', 57000,
'Sales');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Xavier', '6098765438', 62000,
'Admin');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Yara', '7098765437', 59000, 'IT');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Zane', '8098765436', 61000, 'HR');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Aaron', '9198765435', 55000,
'Research');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Bella', '1298765434', 60000,
'Sales');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Carl', '2398765433', 58000,
'Admin');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT)
VALUES (NEXT VALUE FOR EMP_SEQ, 'Diana', '3498765432', 63000, 'IT');

COMMIT;

Now insert some 10 rows in Mentor which maps to 8 Employees.

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (NEXT VALUE FOR MENT_SEQ, 'Java');

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (NEXT VALUE FOR MENT_SEQ, 'Python');

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (NEXT VALUE FOR MENT_SEQ, 'SQL');

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (NEXT VALUE FOR MENT_SEQ, 'C++');

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (NEXT VALUE FOR MENT_SEQ, 'JS');

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (PREVIOUS VALUE FOR MENT_SEQ, 'DevOps');

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (NEXT VALUE FOR MENT_SEQ, 'Java');

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (NEXT VALUE FOR MENT_SEQ, 'ML');

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (NEXT VALUE FOR MENT_SEQ, 'JS');

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (PREVIOUS VALUE FOR MENT_SEQ, 'Cloud');

Introduction to our sample database structure​

Understand the EMP and Mentor Table​

Relationship Between Tables​

Example Data Representation​

Relationship Diagram​

SQL DML Queries​

Why we need unique Index explict​

Employee Table and its related index​

Mentor tables and its related index​

Sequences​

Trigger​

SQL Insert Queries​