Introduction to Db2 for i: A Powerful Database Integrated with IBM i

IBM i's integrated relational database, Db2 for i (formerly known as DB2/400), is a cornerstone of the platform's efficiency and reliability. Unlike standalone databases that require separate installations, Db2 for i is seamlessly embedded within the IBM i operating system. This integration delivers exceptional performance, robust security, and high availability, making it ideal for businesses running critical applications.

In this post, we'll explore the fundamentals of Db2 for i, including how to create and manage tables and execute basic SQL queries, with practical examples to get you started.

Key Features of Db2 for i

Db2 for i stands out due to its tight integration with IBM i and its enterprise-grade capabilities. Here are its core strengths:

Integrated with IBM i: No need for separate database software, reducing complexity and overhead. For example, a retail company can use Db2 for i to manage inventory directly within their IBM i ERP system, streamlining operations.
Object-based Storage: Database objects like tables, indexes, and views are organized within IBM i libraries, ensuring efficient data management. For instance, a library named `SALESLIB` could store all sales-related tables and indexes.
SQL and Non-SQL Access: Users can query data using standard SQL or native IBM i methods like RPG or CL programs. This flexibility allows developers to use SQL for modern analytics or native commands for legacy applications.
Security and Reliability: Built-in access controls, auditing, and journaling ensure data integrity and high availability. For example, a financial institution can rely on Db2 for i's journaling to recover transactions in case of system failures.

Understanding Libraries, Tables, and Files in Db2 for i

In Db2 for i, data is organized using a unique, object-based structure within the IBM i environment. Here's a breakdown of the key components:

Library (Schema): Acts as a container or schema for database objects, similar to a folder. For example, a library named `CUSTDATA` might contain all customer-related tables and indexes.
Table (Physical File): Stores data in rows and columns, like a spreadsheet. For instance, a table named `CUSTOMERS` in the `CUSTDATA` library could hold customer details like names, IDs, and contact information.
View (Logical File): Provides efficient data access by defining alternate ways to retrieve data. For example, a view on the `CUSTOMERS` table could be created to quickly sort or filter by customer region.

Example: Creating a Table in Db2 for i

To illustrate, let's create a simple `CUSTOMERS` table in the `CUSTDATA` library using SQL:

```sql
CREATE SCHEMA CUSTDATA;

CREATE TABLE CUSTDATA.CUSTOMERS (
    CustomerID INTEGER NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Region VARCHAR(30),
    PRIMARY KEY (CustomerID)
);
```

This SQL statement creates a library (`CUSTDATA`) and a table (`CUSTOMERS`) with columns for customer details. You can run this query using tools like IBM Navigator for i or the STRSQL command, or Run SQL Scripts with IBM i Access Client Solutions.

Example: Querying Data

Once the table is populated, you can retrieve data with a simple SQL query:

```sql
SELECT FirstName, LastName, Region
FROM CUSTDATA.CUSTOMERS
WHERE Region = 'Northwest';
```

This query retrieves all customers in the Northwest region, demonstrating Db2 for i's SQL capabilities.

------------------------------
Tim Molter
Advanced Systems Programmer / Analyst
Douglas Machine Inc.
Alexandria MN
------------------------------