Db2 for IBM i Explained

Db2 for i is the relational database that is part of the IBM i operating system. You do not install it, size it separately or connect to it over a network by default: the database is simply there, and the system’s objects, security, backup and journaling all treat database files as first-class objects. That integration is the main thing to understand.

One Database, Two Vocabularies

Db2 for i can be used in two ways at once, and both act on the same data:

A table created with SQL is a *FILE object in a library, so commands such as DSPFD, DSPFFD, SAVOBJ and WRKOBJ work on it. A physical file created years ago from DDS (CRTPF) can be queried with SQL. RPG programs often read files with native record I/O (CHAIN, READ) and use embedded SQL for set-based work, on the same tables.

Older applications use DDS-described files. New work normally uses SQL: CREATE TABLE, CREATE VIEW and CREATE INDEX give you standard SQL features and are the better default.

SQL Names and System Names

Db2 for i has two naming conventions. The system naming form is LIBRARY/FILE, and the SQL naming form is SCHEMA.TABLE. Tools let you choose, and it matters when a statement uses unqualified names because system naming resolves them through the library list while SQL naming uses the current schema. Some SQL names are also longer than the 10-character system name, and IBM i generates a short system name for them.

A First Session

Run SQL from IBM i Access Client Solutions (Run SQL Scripts), from a STRSQL session, or from CL with RUNSQL.

CREATE SCHEMA demo;

CREATE TABLE demo.customer (
  customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        VARCHAR(60) NOT NULL,
  city        VARCHAR(40),
  created_on  DATE DEFAULT CURRENT DATE
);

INSERT INTO demo.customer (name, city)
VALUES ('Acme Ltd', 'Leeds'), ('Northwind', 'Dublin');

SELECT customer_id, name, city
  FROM demo.customer
 WHERE city = 'Leeds';

The same table is now visible to CL:

DSPFD FILE(DEMO/CUSTOMER)
DSPFFD FILE(DEMO/CUSTOMER)

DSPFD shows the file’s attributes and DSPFFD its fields. To run a single statement from CL or a scheduler, use RUNSQL:

RUNSQL SQL('DELETE FROM demo.customer WHERE city IS NULL') COMMIT(*NONE)

Finding Out About the Database with SQL

IBM i keeps a catalog, and IBM i services expose more:

-- Tables in a schema
SELECT table_name, table_type
  FROM qsys2.systables
 WHERE table_schema = 'DEMO'
 ORDER BY table_name;

-- Columns of a table
SELECT column_name, data_type, length, is_nullable
  FROM qsys2.syscolumns
 WHERE table_schema = 'DEMO' AND table_name = 'CUSTOMER'
 ORDER BY ordinal_position;

Catalog and service views are in the QSYS2 schema. They are the safest way to inspect objects from a program, and they are what most monitoring scripts use.

Data Types You Will See

Use the right numeric type rather than floating point for money. A DECIMAL(11, 2) column is a packed field in the record, and RPG programs read it directly as packed decimal.

SQL from RPG

RPG programs run SQL statements embedded in the source, with host variables that start with a colon:

**FREE
Dcl-S custName Varchar(60);

Exec SQL
  SELECT name INTO :custName
    FROM demo.customer
   WHERE customer_id = 1;

If SqlCode = 0;
  Dsply custName;
EndIf;
*InLR = *On;

SqlCode is set after each statement: 0 means success, 100 means no row was found, and a negative value means an error. Embedded SQL needs the source member type SQLRPGLE and is compiled with CRTSQLRPGI. For row-by-row access, RPG can use native record I/O on the same file, which is why both styles coexist in most shops.

Journaling and Commitment Control

Db2 for i can record changes in a journal and group them into transactions. When you create a schema with CREATE SCHEMA, the system creates a journal named QSQJRN in it, and tables created in that schema are journaled to it automatically. Journals support recovery, auditing and replication, and commitment control uses them to roll back a transaction that fails halfway. Journaling is a system feature, not something you code around, so check it early on any application that must not lose updates.

Security

Access to data is controlled by object authority on the table, so grants work the same way as for any other object, with SQL GRANT and REVOKE as an alternative to GRTOBJAUT. Authority is checked whichever interface the job uses. Applications that use adopted authority or row and column access control are an advanced topic; the security guides on this site cover it.

Performance in One Paragraph

The query optimizer chooses a plan from statistics and indexes. The first things to learn are creating the right indexes, writing selective WHERE clauses, and reading plans in Visual Explain. Do not tune before you can see what is slow; start with the performance guides when you reach that point.

Summary

Db2 for i is the database that is part of IBM i. SQL tables, views and indexes are file objects in libraries, so SQL and native access, CL commands, journaling, authority and backup all apply to the same data. Learn SQL first, understand how it maps onto libraries and files, check journaling early, and use the QSYS2 catalog and services to inspect what exists.

What to Learn First

1. Tables, views and indexes in SQL, and how they appear as objects.
2. Basic SELECT, INSERT, UPDATE, DELETE and joins.
3. Naming: system versus SQL naming, schemas and the library list.
4. Journaling and commitment control.
5. Authority on database objects.
6. Embedded SQL from RPG and stored procedures.

Where to Go Next

Continue with IBM i Libraries, Objects, and Members Explained for the object model underneath. When you are ready for more SQL, read Db2 for i CTEs and Recursive SQL, Stored Procedures, UDFs and CREATE PROCEDURE and Db2 for i Triggers, or open the Db2 for i guide.