Menu

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.

IBM iDb2 for iSQLDDLTools Rental

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.

← Return to Node.js on IBM i

Comments

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