Lesson 10 · Node.js on IBM i — From RPG Developer to Modern IBM i Developer
Create the Tools Rental Tables in Db2 for i
Create the TOOLRENT schema, add keys and constraints, load a small data set, and keep every SQL change in Git.
The data model becomes useful when Db2 can enforce it. We will create the tables with primary keys, relationships, and a few checks rather than leaving every rule to JavaScript.
Work in a development schema. Do not run a learning script against an existing production library.
Create the schema and tables
Create sql/001-create-tables.sql:
CREATE SCHEMA TOOLRENT;
CREATE TABLE TOOLRENT.TOOLS (
TOOL_ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
NAME VARCHAR(100) NOT NULL,
CATEGORY VARCHAR(40) NOT NULL,
DAILY_RATE DECIMAL(9, 2) NOT NULL,
STATUS VARCHAR(12) NOT NULL DEFAULT 'AVAILABLE',
CONSTRAINT TOOL_RATE_POSITIVE CHECK (DAILY_RATE >= 0),
CONSTRAINT TOOL_STATUS_VALID CHECK (STATUS IN ('AVAILABLE', 'RENTED'))
);
CREATE TABLE TOOLRENT.CUSTOMERS (
CUSTOMER_ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
NAME VARCHAR(100) NOT NULL,
EMAIL VARCHAR(254) NOT NULL,
ACTIVE SMALLINT NOT NULL DEFAULT 1,
CONSTRAINT CUSTOMER_EMAIL_UNIQUE UNIQUE (EMAIL),
CONSTRAINT CUSTOMER_ACTIVE_VALID CHECK (ACTIVE IN (0, 1))
);
CREATE TABLE TOOLRENT.RENTALS (
RENTAL_ID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
TOOL_ID INTEGER NOT NULL,
CUSTOMER_ID INTEGER NOT NULL,
RENTED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
DUE_DATE DATE NOT NULL,
RETURNED_AT TIMESTAMP,
CONSTRAINT RENTAL_TOOL_FK
FOREIGN KEY (TOOL_ID) REFERENCES TOOLRENT.TOOLS (TOOL_ID),
CONSTRAINT RENTAL_CUSTOMER_FK
FOREIGN KEY (CUSTOMER_ID) REFERENCES TOOLRENT.CUSTOMERS (CUSTOMER_ID),
CONSTRAINT RENTAL_DATE_VALID
CHECK (DUE_DATE >= DATE(RENTED_AT))
);
CREATE INDEX TOOLRENT.RENTALS_BY_TOOL
ON TOOLRENT.RENTALS (TOOL_ID, RETURNED_AT);
Run the script with Run SQL Scripts in IBM i Access Client Solutions. If your team uses a migration tool, use that established process instead.
CREATE SCHEMA does more than create a library. It also creates a journal and journal receiver in the schema, and tables created there are journaled automatically. The rental transaction in a later lesson depends on that. If you place these tables in an existing library that was created with CRTLIB, start journaling them yourself, or the rental procedure will fail with SQL7008.
The identity columns let Db2 assign identifiers. The foreign keys prevent a rental from referring to a tool or customer that does not exist. The check constraints reject impossible status and date values no matter which program writes the data.
Add sample data
Create sql/002-seed-development-data.sql:
INSERT INTO TOOLRENT.TOOLS (NAME, CATEGORY, DAILY_RATE)
VALUES
('Cordless Drill', 'DRILL', 14.00),
('Circular Saw', 'SAW', 18.50),
('Orbital Sander', 'SANDER', 12.00),
('Pressure Washer', 'CLEANING', 32.00);
INSERT INTO TOOLRENT.CUSTOMERS (NAME, EMAIL)
VALUES
('Avery Singh', 'avery@example.test'),
('Morgan Lee', 'morgan@example.test');
The .test email domain is reserved for examples and avoids using a real person’s address.
Run this script only in a development or disposable test environment. Production data should come through approved application or migration processes.
Verify what Db2 created
SELECT TOOL_ID, NAME, CATEGORY, DAILY_RATE, STATUS
FROM TOOLRENT.TOOLS
ORDER BY TOOL_ID;
SELECT CUSTOMER_ID, NAME, EMAIL, ACTIVE
FROM TOOLRENT.CUSTOMERS
ORDER BY CUSTOMER_ID;
You should see four available tools and two active customers.
Try one invalid statement in the development environment:
INSERT INTO TOOLRENT.TOOLS (NAME, CATEGORY, DAILY_RATE, STATUS)
VALUES ('Broken example', 'TEST', -1, 'UNKNOWN');
Db2 should reject it. That failure confirms that the constraints are doing useful work.
Commit the database change
git add sql
git commit -m "Create tools rental database"
The repository now records both the intended design and the exact SQL used to create it. In the next lesson, Node.js will read these rows through a small database module.
References
Documentation and references used for this lesson.

Comments
Share your thoughts, questions, or real-world IBM i experiences related to this article.