Introduction to DB2 for i

IBM i comes with an integrated relational database known as Db2 for i (formerly DB2/400). Unlike traditional databases that require separate installations, Db2 for i is built directly into the operating system, making it highly optimized for performance, security, and reliability.

In this blog, we’ll cover the basics of Db2 for i, including how to create and manage tables and execute basic SQL queries.

Key Features of Db2 for i

• Integrated with IBM i – No need for separate database software.
• Object-based Storage – Database objects like tables and indexes are stored within IBM i libraries.
• SQL and Non-SQL Access – Users can interact with data using both SQL and native IBM i methods.
• Security and Reliability – Built-in access controls and journaling for high availability.

Understanding Libraries, Tables, and Files in Db2 for i

In IBM i, libraries act as containers for database objects. Tables (physical files) and indexes (logical files) are stored within these libraries.

• Library → Acts like a database schema
• Table (Physical File) → Stores data in rows and columns
• Index (Logical File) → Provides a way to access data efficiently

Creating a Library (Database Schema) in Db2 for i

Before creating tables, you may need to create a library (database schema) to store them. Use the CREATE SCHEMA statement:

CREATE SCHEMA MYLIB;

This command creates a library named MYLIB, which will hold database objects such as tables and indexes.

Creating a Table in Db2 for i

To create a table (physical file) using SQL, you can use the CREATE TABLE statement. Below is an example:

CREATE TABLE MYLIB/EMPLOYEES (

    EMP_ID INT PRIMARY KEY,

    FIRST_NAME VARCHAR(50),

    LAST_NAME VARCHAR(50),

    DEPARTMENT VARCHAR(50),

    SALARY DECIMAL(10,2)

);

In this example:

• MYLIB is the library where the table is stored.
• EMP_ID is the primary key.
• VARCHAR stores text values.
• DECIMAL(10,2) stores a numeric value with two decimal places.

Creating an Index (Logical File) in Db2 for i

An index (logical file) helps in improving query performance and organizing data access. You can create an index using the CREATE INDEX statement. Below is an example:

CREATE INDEX MYLIB/EMP_INDEX

ON MYLIB/EMPLOYEES (LAST_NAME, FIRST_NAME);

In this example:

• The index EMP_INDEX is created on the EMPLOYEES table.
• It helps in efficiently retrieving employees based on their last and first names.

To use the index in a query:

SELECT * FROM MYLIB/EMPLOYEES

WHERE LAST_NAME = 'Khanna'

ORDER BY FIRST_NAME;

Inserting Data into a Table

To add data to the table, use the INSERT INTO statement:

INSERT INTO MYLIB/EMPLOYEES (EMP_ID, FIRST_NAME, LAST_NAME, DEPARTMENT, SALARY)

VALUES (101, 'Gaurav', 'Khanna', 'IT', 60000.00);

Querying Data from a Table

To retrieve data, use the SELECT statement:

SELECT * FROM MYLIB/EMPLOYEES;

To filter results, use the WHERE clause:

SELECT * FROM MYLIB/EMPLOYEES WHERE DEPARTMENT = 'IT';

Updating and Deleting Records

To update a record:

UPDATE MYLIB/EMPLOYEES

SET SALARY = 65000.00

WHERE EMP_ID = 101;

To delete a record:

DELETE FROM MYLIB/EMPLOYEES WHERE EMP_ID = 101;

Managing Tables

To modify a table structure, use ALTER TABLE:

ALTER TABLE MYLIB/EMPLOYEES ADD COLUMN HIRE_DATE DATE;

To remove a table:

DROP TABLE MYLIB/EMPLOYEES;

Using Interactive SQL (STRSQL)

IBM i provides an interactive SQL tool called STRSQL, which allows users to execute SQL queries directly on the system. To use it:

1. Type STRSQL in the command line and press Enter.
2. Enter SQL commands to interact with Db2 for i.

Did You Know?

• Db2 for i supports over 1 million transactions per minute on high-end systems.
• It uses row-level locking and journaling to protect your data.
• You can access it using JDBC, ODBC, Python, Node.js, or RPG.
• All objects are stored with single-level storage, making performance exceptional.

Conclusion

Db2 for i is a powerful, integrated database system designed specifically for IBM i. Understanding its structure, commands, and SQL capabilities can help users efficiently manage data. By learning the basics of table creation, indexing, querying, and management, beginners can start leveraging IBM i’s robust database capabilities effectively.