Every physical file you have ever defined in DDS, every CHAIN and READ in your RPG programs, every logical file you built to get a different sort order — that was all DB2 for i. You have been using it for years. You just did not know it had a name.
DB2 for i is not a separate product you install. It is the database engine built into IBM i at the operating system level. There is no database server to configure, no connection string to manage, no separate service to start. The database and the OS are the same thing. That is unusual, and it is one of the reasons IBM i systems are so reliable — the database cannot go down independently of the operating system.
This post explains what that means in practice, how DB2 for i differs from databases you may have used elsewhere, and the features that matter most to an RPG developer making the shift toward modern development.
Physical files are DB2 tables
If you have written DDS, you have defined DB2 tables. The terminology is just different:
- A physical file is a DB2 table
- A logical file is a DB2 view or index
- A record format is a table schema
- A member is a partition within a table
When your RPG program does a CHAIN to CUSTMAST, DB2 is executing a keyed read against that table. When you READ through a logical file sorted by customer name, DB2 is using the index that logical file defined.
The difference today is that you can also access those same physical files directly with SQL — no conversion required.
-- This reads the exact same data your RPG CHAIN reads SELECT CustID, CustName, CustBalance FROM MYLIB.CUSTMAST WHERE CustID = '100234'
How DB2 for i differs from other databases
If you have worked with MySQL, SQL Server, or Oracle, DB2 for i will feel familiar in some ways and surprising in others.
Everything is an object. In DB2 for i, tables, views, indexes, stored procedures, and programs all live in the same object namespace as everything else on the system. A library is a schema. MYLIB.CUSTMAST in SQL is the same object as CUSTMAST in library MYLIB in native I/O.
No separate storage engine. There is no MyISAM vs InnoDB choice. DB2 for i has one storage engine, and it handles everything from simple key lookups to complex analytical queries.
Journaling instead of transaction logs. DB2 for i uses journaling for change tracking, recovery, and commitment control. Journaling is more granular and more integrated than a typical transaction log — you can replay individual changes, replicate data to another system, and audit exactly what changed and when.
Query optimiser is exceptionally good. The DB2 for i query optimiser has been refined over decades. It often finds better execution plans than developers expect, which is one reason embedded SQL frequently outperforms hand-written RPG I/O loops on complex queries.
Schemas and libraries
In SQL terminology, a schema is a namespace that contains tables, views, and other objects. In IBM i terminology, that is a library. They are the same thing, and you can use either name:
-- These are equivalent SELECT * FROM MYLIB.CUSTMAST SELECT * FROM MYLIB/CUSTMAST
The slash syntax is IBM i native notation. The dot syntax is standard SQL. Both work in DB2 for i.
You can create a proper SQL schema with:
CREATE SCHEMA MYSCHEMA
Or just use an existing library. Any library on the system is automatically a valid SQL schema.
Logical files vs SQL views
Logical files have been the IBM i way of creating filtered and sorted views of physical file data since the beginning. SQL views do the same thing, with more flexibility.
A logical file over CUSTMAST showing only active customers:
-- Equivalent SQL view
CREATE VIEW MYLIB.ACTIVE_CUSTOMERS AS
SELECT CustID, CustName, CustBalance, LastOrderDate
FROM MYLIB.CUSTMAST
WHERE Status = 'A'The view is more powerful because you can join multiple tables, compute derived columns, and use any SQL expression — things a logical file cannot do.
CREATE VIEW MYLIB.CUSTOMER_SUMMARY AS
SELECT c.CustID,
c.CustName,
c.CustBalance,
COUNT(o.OrderID) AS TotalOrders,
MAX(o.OrderDate) AS LastOrderDate,
SUM(o.OrderTotal) AS LifetimeValue
FROM MYLIB.CUSTMAST c
LEFT JOIN MYLIB.ORDERPF o ON o.CustID = c.CustID
GROUP BY c.CustID, c.CustName, c.CustBalanceYour RPG program can open this view just like a physical file, or you can query it with embedded SQL.
Indexes
Logical files also create indexes — they tell the system what key order to use when reading a physical file. SQL lets you create indexes directly, with more control:
-- Simple index for fast lookups by last name CREATE INDEX MYLIB.CUST_LNAME_IDX ON MYLIB.CUSTMAST (LastName, FirstName) -- Partial index — only index active customers CREATE INDEX MYLIB.ACTIVE_CUST_IDX ON MYLIB.CUSTMAST (CustBalance DESC) WHERE Status = 'A'
The query optimiser uses these automatically. You do not reference them directly in your SQL — you just write the query, and DB2 picks the best index for it.
Useful SQL table functions built into DB2 for i
DB2 for i includes a set of table functions under the QSYS2 schema that give you direct access to system information via SQL. These are genuinely useful for day-to-day work.
Check what jobs are running:
SELECT JOB_NAME, JOB_STATUS, JOB_TYPE, SUBMITTER_JOB_NAME FROM QSYS2.JOB_INFO WHERE JOB_STATUS = 'ACTIVE' ORDER BY JOB_NAME
Read the job log for a specific job:
SELECT MESSAGE_TIMESTAMP, MESSAGE_ID, MESSAGE_TEXT
FROM TABLE(QSYS2.JOBLOG_INFO('123456/MYUSER/MYJOB')) AS X
ORDER BY MESSAGE_TIMESTAMP DESC
FETCH FIRST 50 ROWS ONLYCheck object sizes across a library:
SELECT OBJNAME, OBJTYPE, OBJSIZE, OBJTEXTDESC
FROM QSYS2.OBJECT_STATISTICS('MYLIB', '*ALL')
ORDER BY OBJSIZE DESC
FETCH FIRST 20 ROWS ONLYSee all tables in a schema:
SELECT TABLE_NAME, NUMBER_ROWS, TABLE_TEXT FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = 'MYLIB' ORDER BY TABLE_NAME
These replace a lot of the green-screen commands you might otherwise use for system administration.
Commitment control and journaling
If you need transactions — where either all changes commit or none do — DB2 for i handles this through commitment control, backed by journaling.
In embedded SQL:
**FREE
// Start a transaction
exec sql SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
exec sql
UPDATE MYLIB.ACCOUNTPF
SET Balance = Balance - :TransferAmount
WHERE AccountID = :FromAccount;
exec sql
UPDATE MYLIB.ACCOUNTPF
SET Balance = Balance + :TransferAmount
WHERE AccountID = :ToAccount;
if SQLCODE = 0;
exec sql COMMIT;
else;
exec sql ROLLBACK;
endif;The journal records every change. If the job ends abnormally between the two updates, the incomplete transaction is automatically rolled back when the system recovers.
Running SQL interactively: ACS and STRSQL
Two tools for running SQL directly against your database:
IBM Access Client Solutions (ACS) — the modern option. A Java-based GUI you run on your PC. Has a SQL console with autocomplete, result grids, execution plans, and the ability to save and run scripts. If you are not using this already, download it — it changes how you work with DB2 for i.
STRSQL — the green-screen SQL console, still on every system. Type STRSQL from any command line to get an interactive SQL session. Limited compared to ACS but always available, even over a 5250 session.
What this means for your RPG programs
Nothing about your existing RPG code breaks. Physical files, logical files, native I/O — all of it still works exactly as it always has. DB2 for i supports both access methods simultaneously. The same table can be read by a CHAIN in one program and a SELECT in another, at the same time.
What changes is what you reach for when writing new code. For a simple sequential read of every record in a file, native I/O is fine. For anything involving joins, filters, aggregation, or sorted subsets — SQL is cleaner, shorter, and often faster.
The database was always there. Now you know what it can do.
Next post: IFS and file systems on IBM i — working with stream files, directories, and the integrated file system.