Stored Procedures in DB2

11 February 2025 11:29 PM

Precompiled set of SQL statements and procedural logic stored in the database
Advantages:
• Performance Improvement – Reduces SQL query execution time by precompiling queries.
• Reduced Network Traffic – Only procedure calls are transmitted instead of multiple queries.

Syntax:
CREATE PROCEDURE procedure_name (parameters)
LANGUAGE SQL
SPECIFIC procedure_specific_name
MODIFIES SQL DATA
BEGIN
-- SQL Statements
END
Example:
CREATE PROCEDURE UPDATE_EMP_SALARY (
IN P_EMP_ID INT,
IN P_NEW_SALARY DECIMAL(10,2)
)
LANGUAGE SQL
SPECIFIC UPDATE_EMP_SALARY_SP
MODIFIES SQL DATA
BEGIN
DECLARE V_OLD_SALARY DECIMAL(10,2);

-- Retrieve the current salary

SELECT SALARY INTO V_OLD_SALARY FROM EMPLOYEES WHERE EMP_ID = P_EMP_ID;

-- Update the salary

UPDATE EMPLOYEES
SET SALARY = P_NEW_SALARY
WHERE EMP_ID = P_EMP_ID;

-- Insert a record in the audit table

INSERT INTO SALARY_AUDIT (EMP_ID, OLD_SALARY, NEW_SALARY)
VALUES (P_EMP_ID, V_OLD_SALARY, P_NEW_SALARY);
END;

In COBOL program,
EXEC SQL
CALL UPDATE_EMP_SALARY(:EMP-ID, :NEW-SALARY)
END-EXEC.

DB2 Page 1
automatically executes when an INSERT, UPDATE, or DELETE operation occurs on a specified table.
Triggers It is event-driven specialized procedures
11 February 2025 11:38 PM Advantages:
Enforce Business Rules – Ensures data validation without external programs.
Automate Auditing – Maintains historical data for compliance.

CREATE TRIGGER TRG_SALARY_AUDIT

AFTER UPDATE OF SALARY ON EMPLOYEES
REFERENCING OLD AS OLD_ROW NEW AS NEW_ROW
FOR EACH ROW
MODE DB2SQL
BEGIN
-- Insert old and new salary details into audit table
INSERT INTO SALARY_AUDIT (EMP_ID, OLD_SALARY, NEW_SALARY)
VALUES (OLD_ROW.EMP_ID, OLD_ROW.SALARY, NEW_ROW.SALARY);
END;

Feature Stored Procedure Trigger

Definitio A stored procedure is a precompiled group of A trigger is an automatic execution of
n SQL statements and procedural logic that is SQL logic when a specific event (INSERT,
executed manually or by an application. UPDATE, DELETE) occurs on a table.
Executio Explicitly invoked by a CALL statement from an Automatically triggered when a
n application, program (COBOL, Java, etc.), or another specific database event occurs.
SQL procedure.
Timing Executed whenever it is called by the Fires BEFORE, AFTER, or INSTEAD OF a
application. database operation.
Use Used for batch processing, complex business Used for auditing, enforcing constraints,
Case logic, and integration between applications and automating business rules at the
and databases. database level.
Paramete Can accept input (IN), output (OUT), and No parameters are allowed. Uses OLD
rs input-output (INOUT) parameters. and NEW values for reference.
Performance Improves performance by reducing Can slow down operations if too many
Impact network traffic between the application triggers execute frequently on a large
and database. dataset.

DB2 Page 2
Db2 utilites • Which utility is used for bulk data loading? → LOAD
11 February 2025 11:56 PM • How do you optimize DB2 table performance? → REORG & RUNSTATS
• How do you back up a DB2 table? → COPY
• How do you recover data after accidental deletion? → RECOVER
• How do you verify data consistency after loading? → CHECK DATA

. LOAD Utility
Purpose: Used to bulk insert large amounts of data into a DB2 table.
Why is it important?: It is faster than INSERT because it bypasses logging for performance.
Example Scenario: Loading employee records from a file into a DB2 table.

LOAD DATA INFILE 'EMPLOYEE_DATA.DAT'

INTO TABLE EMPLOYEES;
Real-World Use: Used by banks, telecom companies, and large enterprises to load millions of records
at once.

UNLOAD Utility
Purpose: Extracts (exports) data from DB2 tables into a file.
Why is it important?: Useful for backup, migration, or data transfer between environments.
Example Scenario: Extracting all employee records for reporting.
Example Command
UNLOAD TABLE EMPLOYEES
TO 'EMPLOYEE_BACKUP.DAT';
Real-World Use: Used when moving data from production to test environments.

Purpose: Optimizes table storage and improves performance.

Why is it important?: Over time, tables become fragmented, slowing down queries.
Example Scenario: A table with frequent updates and deletes may need reorganization.
Example Command
REORG TABLE EMPLOYEES;
Real-World Use: Helps in improving query performance in large-scale DB2 databases.

RUNSTATS Utility
Purpose: Collects statistics about table data to help the DB2 optimizer choose the best query
execution plan.
Why is it important?: Ensures efficient indexing and query performance.
Example Scenario: After loading new data, run RUNSTATS so that queries execute faster.
Example Command
RUNSTATS TABLESPACE EMP_TBSP;
Real-World Use: Used in database tuning and performance optimization.

Purpose: Creates a backup (image copy) of a DB2 table.

Why is it important?: Essential for disaster recovery and data protection.
Example Scenario: Making a backup of employee data before running a bulk update.
Example Command

COPY TABLESPACE EMP_TBSP;

Real-World Use: Used for safety before performing risky operations.

Purpose: Restores a table from a backup (COPY) in case of data loss.

Why is it important?: Helps in disaster recovery.
Example Scenario: If someone accidentally deletes all employee data, use RECOVER to restore it.
Example Command
RECOVER TABLESPACE EMP_TBSP;
Real-World Use: Used in banking and finance to restore critical data after failures.

CHECK DATA Utility ✅

Purpose: Checks for data inconsistencies and constraint violations.
Why is it important?: Ensures data integrity after batch processing or migrations.
Example Scenario: Verifying foreign key constraints in an employee database.
Example Command

DB2 Page 3
Example Command
CHECK DATA TABLESPACE EMP_TBSP;
Real-World Use: Used after data migrations or bulk loads to validate correctness.

QUIESCE Utility
Purpose: Temporarily pauses database activity to ensure consistent backups.
Why is it important?: Prevents conflicts when taking snapshots of critical data.
Example Scenario: Before copying employee payroll data for backup.
Example Command
QUIESCE TABLESPACE EMP_TBSP;
Real-World Use: Used in highly transactional databases where data consistency is critical.

DB2 Page 4
Concurrency ,Isolation Locking and Deadlocks
12 February 2025 12:10 AM

✅ How does DB2 handle multiple users accessing the same data? → Using Locks & Isolation Levels
✅ What is the safest isolation level? → RR (Repeatable Read)
✅ What is the fastest isolation level? → UR (Uncommitted Read)
✅ How do you avoid dirty reads? → Use CS, RS, or RR
✅ What is the default isolation level in DB2? → CS (Cursor Stability).
✅ How do you prevent lost updates? → Use Exclusive Locks (X)
What is locking in DB2? → Prevents data conflicts when multiple users access the same data.
✅ What are the types of locks in DB2? → Shared (S), Exclusive (X), Update (U), and Intent Locks (IS,
IX, SIX).
✅ What is the difference between row-level and table-level locking?
• Row-level: Locks a single row (better concurrency).
• Table-level: Locks the entire table (better for batch jobs).
✅ What is a deadlock in DB2? → A situation where two transactions wait for each other, causing a
system freeze.
✅ How do you prevent deadlocks? → Use short transactions(COMMIT), access tables in the same
order, and set LOCK TIMEOUT.

Concurrency:
Multiple users accessing the same database at the same time without conflicts.
When multiple users or transactions access the same data, issues may arise:
– Two users upda ng the same record one update gets lost.
– Reading uncommi ed (temporary) changes of another transac on.
– hen a transac on reads the same uery mul ple mes and results are
not same each me.
– A transac on sees new rows when re reading data.

Real-Time Example: Bank Account Transfer

Scenario: Two users (A and B) are accessing the same bank account.
Without Concurrency Control (Problem)

ithdraws → Updates balance

With Concurrency Control (Solution)

Using CS (Cursor Stability) or RR (Repeatable Read) isolation level:

Example SQL with Isolation Level

SET CURRENT ISOLATION = RR;
UPDATE ACCOUNTS SET BALANCE = BALANCE - 3000 WHERE ACC_ID = 101;
COMMIT;

Locking:
Prevents multiple users from modifying the same data at the same time, ensuring data consistency and
avoiding conflicts
Types of Locks in DB2
Lock Type What It Does Who Can Access?
Shared (S) Lock Allows reading but prevents Multiple users can read, but no one can
modifications. update.
Exclusive (X) Lock Prevents both reading and updating Only one user can access.
by others.
Update (U) Lock Prevents deadlocks when modifying Only one user can modify, others can read
data. but not update.
Intent Locks (IS, Signals intent to read or write at Controls locking strategy efficiently.
IX, SIX) table/page level.

Why Are Intent Locks Needed?

DB2 Page 5
Why Are Intent Locks Needed?
Intent locks prevent deadlocks and improve performance by allowing different types of access at the
same time.
Scenario: Multiple Users Accessing the Same Table
• User A is viewing a product (SELECT query).
• User B is updating the stock quantity (UPDATE query).
Without Intent Locks (Problem):

With Intent Locks (Solution):

• User A (SELECT) places an Intent Shared (IS) lock on the table → other users can still update
different rows.
• User B (UPDATE) places an Intent Exclusive (IX) lock → can update a specific row without blocking
the entire table.
SQL Example Using Intent Locks

SELECT * FROM PRODUCTS

WHERE PRODUCT_ID = 101
WITH UR; -- Uses IS (Intent Shared) lock
UPDATE PRODUCTS
SET STOCK_QTY = STOCK_QTY - 1
WHERE PRODUCT_ID = 101
WITH RS; -- Uses IX (Intent Exclusive) lock

Types of Intent Locks in DB2

Lock Type What It Means W ’ ?
Intent Shared (IS) Transaction wants to read some rows but When running SELECT queries.
won’t modify them.
Intent Exclusive (IX) Transaction wants to modify some rows. When running UPDATE, DELETE,
or INSERT.
Shared Intent Transaction reads most rows but modifies When reading a table but
Exclusive (SIX) some. updating a few rows.

Locking Granularity (Where Locks Are Applied)

DB2 can place locks at different levels for better performance.
Lock Level What It Means Use Case
Row-Level Lock Locks a single row (most flexible). High-concurrency systems (many
users).
Page-Level Lock Locks a group of rows (a page). Medium-concurrency.
Table-Level Lock Locks the entire table. Large batch updates (low
concurrency).
Tablespace-Level Locks the entire tablespace (least Used for backup & recovery.
Lock flexible).

Example: How DB2 Locks Work in Banking

Suppose two customers are transferring money from the same account at the same time.
UPDATE ACCOUNTS
SET BALANCE = BALANCE - 2000
WHERE ACC_ID = 101
FOR UPDATE WITH RS;
COMMIT;
FOR UPDATE WITH RS (Read Stability) → Ensures data isn’t changed by another user before updating.

Deadlocks & How to Avoid Them

What is a Deadlock?
• A deadlock happens when f ’ , and neither can proceed.
Example of Deadlock

How to Prevent Deadlocks?

✅ Always access tables in the same order.
✅ Use short transactions (commit quickly).
✅ Use LOCK TIMEOUT D 2 ’
Example SQL to Avoid Deadlock

DB2 Page 6
SET CURRENT LOCK TIMEOUT 5;
(Wait 5 seconds, then stop if lock is not available.)

Isolation:
how transactions interact with each other when accessing the same data.It helps prevent issues like
dirty reads, lost updates, and inconsistent data when multiple users are working at the same time.

DB2 Isolation Levels

Isolation Level What It Does Pros Cons
UR Allows reading uncommitted Fastest, best for reports. Risk of dirty reads (seeing
(Uncommitted changes. unconfirmed changes).
Read)
CS (Cursor Reads committed data but Avoids dirty reads, better Can lead to non-
Stability) allows changes by other concurrency. repeatable reads.
transactions.
RS (Read Prevents dirty & non-repeatable Ensures rows stay the Still allows phantom
Stability) reads. same during a reads.
transaction.
RR Prevents dirty, non-repeatable, Ensures full consistency. Slower, can block other
(Repeatable and phantom reads. users.
Read)

.
Scenario: A user sees 5 items in stock and tries to buy 3 items while another user buys at the same
time.

• User A sees 5 items in stock.

• User B buys 3 items (but hasn't committed yet).
• User A still sees 5 items (incorrect).
• If User B rolls back, User A saw wrong data → Dirty Read Issue! ❌

• User A sees 5 items.

• User B buys 3 items and commits.
• Now, User A sees only 2 items left (correct).
• Avoids dirty reads, but User A might see stock change while browsing.

• User A sees 5 items and locks those rows.

• User B must wait to modify the stock.
• Ensures ’ , but new products may still appear
(phantom read).

• User A sees 5 items and locks the entire stock data.

• User B must wait completely until User A is done.
• Ensures absolutely no changes to the stock during the transaction.
• Slowest but safest!

DB2 Page 7
Integrity ,Primary key,Foreign Key
12 February 2025 01:23 AM

Data Integrity ensures that the data in a database is accurate, consistent, and reliable.
It prevents duplicate, missing, or incorrect data from being stored.

Types of Data Integrity in DB2

Type What It Ensures Example
Entity Integrity Each row has a unique identifier No two employees can have the same
(Primary Key). Employee_ID.
Referential Relationships between tables are If a department is deleted, employees in that
Integrity valid (Foreign Key). department should also be handled properly.
Domain Values in a column follow Salary should always be greater than zero.
Integrity predefined rules.
User-Defined Custom business rules enforced A customer cannot place an order if their credit
Integrity by triggers or constraints. limit is exceeded.
How does DB2 enforce data integrity? → Using Primary Keys, Foreign Keys, Constraints, and
Triggers.
✅ What happens if referential integrity is violated? → DB2 rejects the operation or applies ON
DELETE CASCADE rules

Primary Key:

A Primary Key is a column (or a set of columns) in a database table that uniquely identifies
each record in that table. It ensures that no two records in the table have the same value for
the primary key column(s). The primary key must always contain unique values, and it cannot
contain NULL values.

Simple Example:

Imagine a table called Students, where each student has a unique Student ID. Here, the
Student ID column would be the Primary Key because it uniquely identifies each student.

| Student ID (Primary Key) | Student Name | Age |

|--------------------------|--------------|-----|
| 101 | John | 20 |
| 102 | Alice | 22 |
| 103 | Bob | 21 |

In this example:
- Student ID is the Primary Key because it uniquely identifies each student.

Foreign Key:

A Foreign Key is a column (or a set of columns) in one table that links to the Primary Key in
another table. The foreign key establishes a relationship between two tables by referring to
the primary key of another table. The foreign key can contain duplicate values and may allow
NULL values.

Simple Example:
Now, let's say we have another table called Courses, where each course is assigned to a
specific student. The Student ID in the Courses table would be a Foreign Key because it refers
to the Student ID in the Students table (which is the Primary Key in the Students table).

| Course ID | Course Name | Student ID (Foreign Key) |

|-----------|---------------|--------------------------|
| C001 | Math 101 | 101 |
| C002 | Science 102 | 102 |
| C003 | History 103 | 103 |

In this example:
- Student ID in the Courses table is the Foreign Key because it references the Primary Key
Student ID in the Students table.

Key Differences:
- Primary Key: Uniquely identifies a record in a table and cannot contain NULL values.
- Foreign Key: Establishes a relationship between two tables by referencing a Primary Key in
another table, and can contain NULL values.

DB2 Page 8
another table, and can contain NULL values.

DB2 Page 9
System catalog tables
System catalog tables are special tables in DB2 that store metadata (information about the
12 February 2025 01:38 AM
database structure).
These tables do not store user data but contain details about tables, columns, indexes, constraints,
stored procedures, and more.
They help DBAs and developers manage and query database objects efficiently.
Important System Catalog Tables in DB2
System Table What It Stores Example Query
SYSIBM.SYSTABL List of all tables in the database. SELECT NAME, TYPE FROM SYSIBM.SYSTABLES;
ES
SYSIBM.SYSCOLU Details of table columns (name, SELECT NAME, COLTYPE FROM
MNS data type, length). SYSIBM.SYSCOLUMNS WHERE TBNAME =
'EMPLOYEES';
SYSIBM.SYSINDE Information about indexes on SELECT NAME, TBNAME FROM
XES tables. SYSIBM.SYSINDEXES;
SYSIBM.SYSCONS Stores constraints (Primary Key, SELECT CONSTNAME, TBNAME FROM
TRAINTS Foreign Key, etc.). SYSIBM.SYSCONSTRAINTS;
SYSIBM.SYSROUT Details of stored procedures and SELECT ROUTINENAME, ROUTINETYPE FROM
INES functions. SYSIBM.SYSROUTINES;
SYSIBM.SYSUSER Information about database SELECT GRANTEE, PRIVILEGE FROM
S users and their privileges. SYSIBM.SYSUSERS;

Real-Time Example: Checking Table Information

Scenario: A Developer Needs to Find All User Tables in DB2
Query to List All Tables in the Database

sql
CopyEdit
SELECT NAME, CREATOR, TYPE
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T';
Explanation:
• NAME → Table name.
• CREATOR → The owner of the table.
• TYPE = 'T' → Filters only user tables (ignores system tables).

Scenario: A DBA Wants to Find All Indexes on the EMPLOYEES Table

Query to Find Indexes on a Specific Table

sql
CopyEdit
SELECT NAME, TBNAME, UNIQUERULE
FROM SYSIBM.SYSINDEXES
WHERE TBNAME = 'EMPLOYEES';
Explanation:
• Shows index name, table name, and whether the index is unique or non-unique.
• Helps in query optimization by checking existing indexes.

Scenario: Checking Constraints on a Table

Query to View Constraints on EMPLOYEES Table

sql
CopyEdit
SELECT CONSTNAME, TBNAME, TYPE
FROM SYSIBM.SYSCONSTRAINTS
WHERE TBNAME = 'EMPLOYEES';
Explanation:
• Lists primary keys (P), foreign keys (F), and check constraints (C) on the table.
• Helps ensure data integrity is properly enforced.

Interview Tips System Catalog Tables in DB2

✅ What are system catalog tables? → Special DB2 tables that store metadata (database structure
information).
✅ Why are system catalog tables important? → Helps in managing tables, indexes, constraints, and
user privileges.
✅ Which system catalog table stores table details? → SYSIBM.SYSTABLES.
✅ How to check column details of a table? → Query SYSIBM.SYSCOLUMNS.
✅ How to find all indexes on a table? → Query SYSIBM.SYSINDEXES.

DB2 Page 10
✅ How to find all indexes on a table? → Query SYSIBM.SYSINDEXES.

DB2 Page 11