IBM i track · Core · self-paced ~24h (5–6 weeks at 5h/week) · prerequisite: CS210
SQL on IBM i for RPG programmers — the step most IBM i job listings now ask for after RPG. DB2 for i is built into the operating system: you create your own schema, tables with keys and rules (next to the DDS you know), indexes and views; query them with SELECT, joins (the IBM i EXCEPTION JOIN included), GROUP BY, CASE and the common functions; change them with INSERT, UPDATE and DELETE under commitment control; and run SQL from STRSQL, from scripts with RUNSQLSTM and from CL with RUNSQL. Then SQL inside free-form RPG: host variables, SELECT INTO, SQLCODE and SQLSTATE, cursors and multiple-row FETCH, built with CRTSQLRPGI — ending with a report program of your own that runs in batch. Eleven sessions, fifty short videos, every lab in your own schema on the same environment.
Self-paced available Cohort
Open for enrollment now. It follows CS210 RPG Programming I: you sign on to the 5250, work in PDM and SEU, compile and call RPG programs and have read and changed DB2 for i files with native I/O; no SQL is assumed. The lesson videos for this course are not released yet: every session below is published as its lessons and lab now and marked Coming soon until its videos are released. Where this environment differs from a production IBM i, the course says so rather than faking it. Where next: CS200 IBM i / AS400 RPG Track, the cohort, for display files, subfiles and ILE as a team, and the Modernization track for what happens to these programs next.
Plans: Free individual at no cost, or Paid individual at $249 per course, one-time — code review, extra exercises, grading and priority support.
Every session ends in a lab on your own environment. Labs are machine-checked against expected outputs — you and your L&D team always know exactly where you stand.
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Four videos. On IBM i the database is not a product you install: DB2 for i is part of the operating system, and every physical file you read with native I/O in CS210 is a table. The two vocabularies for the same objects — a schema is a library, a table a physical file, a row a record, a column a field, views and indexes are logical files — and where SQL fits beside record-level I/O: one statement about a whole set of rows instead of a loop, and the four doors this course uses (STRSQL, RUNSQLSTM, RUNSQL and embedded SQL with CRTSQLRPGI). Live on the system: IBM’s sample file QIWS/QCUSTCDT seen as an object, as fields in DSPFFD and as rows in STRSQL; your first SELECT with WHERE; and CREATE SCHEMA, which makes your own library with its journal QSQJRN.
LAB Create a schema named exactly like your user profile, show its journal and receiver with DSPLIB, read the empty schema in QSYS2.SYSTABLES, and run a SELECT over QIWS.QCUSTCDT for one state. Session 1 checkpoint (SQ01).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Five videos. CREATE TABLE with the types an RPG programmer already knows — CHAR and VARCHAR, DECIMAL (packed) and NUMERIC (zoned), INTEGER and SMALLINT, DATE — and the rules DB2 enforces on every write, whoever writes: NOT NULL, DEFAULT, PRIMARY KEY and CHECK. The catalog views QSYS2.SYSCOLUMNS and SYSCST describe what you built; DROP TABLE removes it. Then the same file two ways: a DDS physical file built with CRTPF next to the SQL table, compared field by field in DSPFFD. And DDL kept as source the way shops do it: a member of type SQL in QSQLSRC, run with RUNSQLSTM.
LAB Build the course schema: CUSTOMER with its key, defaults and CHECK, ITEM from SQL beside its DDS twin ITEMDDS, and ORDERS and ORDLINE (a two-column key) created by RUNSQLSTM from members of your own QSQLSRC. Session 2 checkpoint (SQ02).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Five videos. INSERT with a column list and several rows in one VALUES, defaults and NULL; SELECT with WHERE — comparisons, AND and OR, IN, BETWEEN and LIKE; ORDER BY, DISTINCT and FETCH FIRST n ROWS ONLY; and NULL, which equals nothing: IS NULL and COALESCE. Test data loaded by script, so it can be loaded again — and the rules of Session 2 at work: SQL0803 for a duplicate key, SQL0545 for a CHECK and SQL0407 for a missing value.
LAB Load the data script: 5 customers, 3 items, 4 orders and 5 order lines in your tables, each of the three error messages seen once. Session 3 checkpoint (SQ03).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Four videos. A select list that computes: arithmetic, || and named results with AS, DECIMAL for the precision of a result; the string functions TRIM, UPPER, SUBSTR, LENGTH, LOCATE and REPLACE, and why TRIM matters with CHAR columns; dates — YEAR, MONTH, DAYOFWEEK, + 30 DAYS, DAYS() differences and CHAR(date, USA); and CASE, searched and simple, the first true WHEN winning.
LAB One SELECT over CUSTOMER with a computed label, the account age in days at 2026-11-30 and a credit tier from CASE, every computed column named. Session 4 checkpoint (SQ04).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Four videos. Inner joins with JOIN … ON and correlation names, and the older comma form; LEFT OUTER JOIN for “all customers, with their orders if any”; the IBM i EXCEPTION JOIN and the portable NOT EXISTS for “customers with no order”; and four tables in one statement.
LAB The order detail: ORDERS, CUSTOMER, ORDLINE and ITEM joined, five rows with a named AMOUNT. Session 5 checkpoint (SQ05).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Four videos. COUNT, SUM, AVG, MIN and MAX; GROUP BY and HAVING, and ROLLUP for a grand total; subqueries with IN, EXISTS and in the select list; and the totals per customer that come from joining first and grouping after.
LAB Sales per customer — the number of orders counted correctly and the amounts summed, largest first. Session 6 checkpoint (SQ06).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Five videos. UPDATE and DELETE with the WHERE you test as a SELECT first, INSERT … SELECT to copy and archive, and the rules of the table still guarding every change. CREATE INDEX and CREATE VIEW — on IBM i both are logical files — and a view that hides a four-table join for the report program to come.
LAB The index CUSTSTATE and the views NYCUST and ORDDETAIL in your schema, and one UPDATE and one DELETE of your own with their row counts. Session 7 checkpoint (SQ07).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Four videos. The journal QSQJRN that CREATE SCHEMA made, and its entries in DSPJRN — a row added, the before and after of an update, a row deleted. Commitment control: pending changes, COMMIT and ROLLBACK as one unit of work, RUNSQL with COMMIT(*CHG) and the CL COMMIT and ROLLBACK commands, RUNSQLSTM’s own default. SQL7008 for a file that is not journaled, and journaling it with CRTJRNRCV, CRTJRN and STRJRNPF.
LAB A script that adds an order and its lines under COMMIT(*CHG): one failed run rolled back, one good run committed, and its entries found in DSPJRN. Session 8 checkpoint (SQ08).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Five videos. SQL inside free-form RPG: exec sql in a member of type SQLRPGLE, built with CRTSQLRPGI — the precompiler, then the RPG compiler, one listing — with COMMIT(*NONE), and unqualified table names found through the library list. Host variables and SELECT INTO; SQLCODE and SQLSTATE after every statement — 0, 100 for no row, -803, -811 and -204 — and SQLER3 for the rows changed; NULL indicators, and the listing’s SQL0204 that still creates the program.
LAB The customer inquiry CUSTINQ: a parameter, SELECT INTO with an indicator, and an answer for a customer found, a customer with no city and a number that does not exist. Session 9 checkpoint (SQ09).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Five videos. DECLARE CURSOR, OPEN, FETCH and CLOSE; the prime-and-loop shape you know from read and %eof, ending on SQLCODE 100; host variables in a cursor’s query, taken at OPEN; and the multiple-row FETCH into an array of data structures, with SQLER3 counting the rows of each block.
LAB ORDSUM: a cursor over a join with GROUP BY that shows the three customers with orders and their totals, largest first. Session 10 checkpoint (SQ10).
Coming soon The videos for this session are being produced; its lessons and lab are published here now.
Five videos. A batch report written to the printer file QSYSPRT from a cursor: fixed columns from a data structure, amounts right-aligned with evalr and %editc, a total per order and a grand total. Run in batch with SBMJOB, followed with WRKSBMJOB and read in WRKSPLF — and the library list a batch job needs. The final project, then a review of what the course built and where it leads.
LAB Final project (SQ11): ORDRPT, built with CRTSQLRPGI and submitted in batch, writes every order with its lines, the totals 193.00, 94.50, 170.00 and 12.50 and the grand total 470.00 to QSYSPRT.
CobolStack Certificate of Completion — SQL for IBM i (CS220) is issued from the lab harness’s own record of your runs — not from attendance. To earn it you must clear every gate below:
The certificate attests that you can use SQL on IBM i the way current RPG shops do — define tables with their rules beside the DDS already there, query and join them, change them under commitment control, and embed SQL in free-form RPG with host variables, SQLCODE checks and cursors, up to a batch report of your own: the SQL that IBM i developer postings ask for alongside RPG, verified from your own work.
Your seat is a private, full IBM i-compatible environment (SteelFrame X) with 5250 in the browser: DB2 for i SQL through STRSQL, RUNSQLSTM and RUNSQL, CREATE SCHEMA with its journal, the QSYS2 catalog views, commitment control with COMMIT and ROLLBACK, DDS physical files, PDM and SEU, CRTSQLRPGI for free-form SQLRPGLE, SBMJOB and spooled files in the browser — nothing to install, snapshot and reset per exercise. By the end of the course the lab harness will have verified: a schema of your own with four tables, their keys and rules, an index and two views; a data script and a commit-or-rollback script run with RUNSQLSTM; SELECT statements with expressions, CASE, four-table joins, an exception join and grouped totals; and three SQLRPGLE programs built with CRTSQLRPGI — a customer inquiry that handles every SQLCODE, a cursor program with totals per customer, and the ORDRPT report, submitted in batch, whose grand total is 470.00.