IBM i (AS/400) Module

Work with an IBM i (AS/400, iSeries) from a workflow: query and change DB2 for i tables, call RPG, COBOL or CL programs and SQL procedures, run CL commands, and exchange entries with data queues. The engine connects through the system's database host server with the user profile you configure, so every step runs with that profile's authorities.

What you can build

• Customer or order lookup in chat or voice: a visitor gives an order number, Query reads it from the ERP's files, the flow answers.
• Order intake from a form: Execute SQL inserts the order lines, then Run CL command submits the batch job that processes them.
• Prices and availability from a legacy program: Call program passes the item and quantity to an RPG program and reads the price and status back.
• Hand-off to RPG through a data queue: Data queue sends an entry the way green-screen programs expect, and receives the reply the program puts on another queue.
• Nightly extraction: a Scheduler start, Query with "All rows", File Query writes the rows to Excel, Output emails it.
• Operations alerts: a scheduled flow queries QSYS2.SYSTEM_STATUS_INFO or QSYS2.MESSAGE_QUEUE_INFO and posts to Slack when a threshold is passed.

Requirements

• An ODBC driver for IBM i on the engine host. The module talks ODBC: install the IBM i Access ODBC Driver from IBM i Access Client Solutions (Windows and Linux builds; on Linux, unixODBC too) on the machine that runs the Engine. A managed App Service cannot install it: run the Engine on a VM, a container with the driver layered in, or behind a self-hosted host that reaches the system. Any other DB2 for i ODBC driver works through the connection string override.
• Network access from the engine host to the system's database host server: port 8471, or 9471 with TLS.
• A user profile for the flows, with the authorities they need and nothing more: *USE on the tables they read, *CHANGE on the ones they write, *USE on the programs and data queues. Commands and program calls run as this profile.
• For data queues: IBM i 7.3 with the SQL services QSYS2.SEND_DATA_QUEUE and QSYS2.RECEIVE_DATA_QUEUE (7.3 TR 5 / 7.4 or later).

Quick start

1. Install the IBM i Access ODBC Driver on the engine host and check it is listed among the ODBC drivers (Windows: ODBC Data Sources (64-bit) → Drivers; Linux: odbcinst -q -d).
2. On the IBM i, create a user profile for the flows (CRTUSRPRF USRPRF(WFLOWUSR) ...) and grant it the objects it needs.
3. In the portal, open the environment's modules, add IBM i (AS/400) and fill System, User profile and Password. Set Default library to the library your tables live in.
4. Add a Query step to a process: SELECT CUSNUM, CUSNAM FROM CUSTOMERS WHERE CUSNUM = {customerNo}, keep First row in {customer}.
5. Add an Output: Customer {customer.CUSNAM} found. and an error flow from the Query step with {as400Error.message}.
6. Deploy to a test environment and start a session with customerNo set. Done.

How it works

Each step opens a connection with the environment's settings, runs one statement and closes it (the driver manager pools the connections). {variables} in SQL are bound as parameters with the variable's own type, never pasted into the statement, so a value cannot change what the statement does. Every statement commits on its own (*NONE commit mode): the module never leaves a transaction open.

Naming follows the environment: SQL naming qualifies objects as LIB.FILE and resolves unqualified names in the default library; system naming uses LIB/FILE and the library list. Object names (libraries, programs, data queues) are checked before any statement is built.

Configuration

Setting	Required	Notes
System	yes	Host name or IP address of the IBM i.
Use TLS	no	Encrypts the connection (port 9471); the system's certificate must be trusted by the engine host.
User profile	yes	The profile the flows connect as, up to 10 characters.
Password	yes	Stored encrypted.
Default library	no	The library unqualified names resolve to (the current schema).
Library list	no	Libraries added to the job's library list, comma separated, in search order.
Naming	no	SQL (LIB.FILE, default) or System (LIB/FILE).
Statement timeout	no	1–300 seconds, default 30. A task can set its own.
ODBC driver	no	Advanced: the driver's name as installed. Empty: IBM i Access ODBC Driver.
Connection string override	no	Advanced: a full ODBC connection string used as it is (another driver, a DSN, extra keywords such as CCSID or TRANSLATE). Stored encrypted.

Without an override the module builds DRIVER={...};SYSTEM=...;UID=...;PWD=...;NAM=0|1;DBQ=default,lib1,lib2;CMT=0;SSL=1.

Tasks

Every task takes its error flow when something fails and writes {as400Error}: code to branch on, message a sentence safe to show an end user, detail what went wrong exactly (the driver's text), sqlState the SQLSTATE when the system gave one. See Error codes.

Query

Runs a SELECT and keeps its rows.

Property	Required	Notes
SQL	yes	One SELECT (DB2 for i SQL). {variables} are bound as parameters. A CALL that returns a result set works too.
Keep	no	All rows: a list of records. First row: one record ({row.CUSNAM}). Single value: the first column of the first row.
Maximum rows	no	1–10,000, default 1000. With more rows on the system, {result.truncated} is true and the flow gets the first ones.
Trim text	no	On by default: trailing blanks of CHAR columns are removed.
Timeout	no	Advanced. 0: the environment's default.
Rows variable	yes	Where the rows go.
Store result in	no	{result.count}, {result.columns}, {result.truncated}, {result.elapsedMs}.

Column names come as the system returns them (usually upper case): {rows.0.CUSNAM}. Use AS to pick friendlier names. Numbers come as numbers, dates as dates, binary columns as base64 text.

Tips: FETCH FIRST n ROWS ONLY limits on the system side; a {variable} holding true/false is bound as 1/0; a list or object variable is bound as its JSON text.

Execute SQL

Runs an INSERT, UPDATE, DELETE, MERGE or CALL and keeps the rows affected.

Property	Required	Notes
SQL	yes	One statement. {variables} are bound as parameters. A SELECT is refused: use Query.
Timeout	no	Advanced.
Store result in	no	{result.affected}, {result.elapsedMs}.

Each statement commits as soon as it runs. For several statements that must succeed together, write an SQL procedure on the system and call it.

Call program

Calls a program or SQL procedure: CALL LIBRARY.PROGRAM(?, ?, ...) with typed parameters.

Property	Required	Notes
Library	no	Empty: the default library (SQL naming) or the library list (system naming).
Program	yes	Up to 10 characters.
Parameters	no	In the program's order. Name (how the flow reads it back), Type CHAR / VARCHAR / DECIMAL / INTEGER / BIGINT / DATE / TIMESTAMP, Length (characters, or the digits of a DECIMAL), Scale (DECIMAL), Direction In / Out / In-Out, Value (supports {variables}).
Trim text	no	Trailing blanks removed from the CHAR values read back.
Timeout, Maximum rows	no	Advanced. Maximum rows applies to a result set the program returns.
Store result in	yes	{program.outputs.NAME} for each Out / In-Out parameter, {program.rows} and {program.rowCount} when the program returns a result set.

A CHAR value is blank-padded to its length before the call, as a fixed field expects; a longer value is refused. Empty numbers are passed as zero, empty dates as null. A DECIMAL accepts 12.5 or 12,5.

Making a program callable. The driver calls what DB2 for i knows as a procedure. For an RPG or CL program, declare it once on the system:

CREATE PROCEDURE MYLIB.GETPRICE (IN ITEM CHAR(15), IN QTY INTEGER, OUT PRICE DECIMAL(9,2), INOUT STATUS VARCHAR(10))
  LANGUAGE RPGLE PARAMETER STYLE GENERAL EXTERNAL NAME 'MYLIB/GETPRICE';

Then the task's parameters mirror that signature. Programs that take only CHAR and DECIMAL parameters can often be called without a declaration (the driver's direct program call); declaring them is the reliable way.

Run CL command

Runs a CL command through QSYS2.QCMDEXC.

Property	Required	Notes
CL command	yes	As typed on a command line, up to 32,000 characters. Line breaks become spaces. Supports {variables}.
Timeout	no	Advanced. A SBMJOB returns at once; the submitted job runs on its own.

The command runs as the configured profile with its library list. {variables} are inserted as text (CL has no parameters): put them inside quotes (MSG('Order {orderNo}')) and use them only for values you trust, such as numbers you validated or values the flow produced. A command that fails on the system (CPF message) takes the error flow with the message text in {as400Error.detail}.

Data queue

Sends an entry to a data queue (*DTAQ), or receives the next one.

Property	Required	Notes
Operation	yes	Send or Receive.
Data queue	yes	Up to 10 characters.
Library	no	Empty: *LIBL.
Data	Send	The entry as text: what the receiving program expects (fixed positions, a separator, JSON). Up to 64,512 characters.
Key	no	Keyed queues: the entry's key (Send) or the key to look for (Receive).
Key comparison	Receive	Keyed queues: Equal, Not equal, Greater, Greater or equal, Less, Less or equal.
Wait	Receive	0–120 seconds to wait for an entry when the queue is empty. The statement timeout is raised to cover it.
Trim text	Receive	Trailing blanks removed from the data and key received.
Store message in	Receive	{message.received} (false when the wait ended with nothing: not an error), {message.data}, {message.key}, {message.senderJob}.

Uses QSYS2.SEND_DATA_QUEUE and the QSYS2.RECEIVE_DATA_QUEUE table function; the entry is removed from the queue when received. Text goes to the system in the job's CCSID: a queue read by an RPG program sees the same bytes it would from QSNDDTAQ.

Common workflows

Order status in chat

Start (chat) → Input "Your order number?" → {orderNo}
  → Query: SELECT ORDNO, STATUS, SHIPDT FROM ORDERS WHERE ORDNO = {orderNo}  (First row → {order})
      ok   → Output "Order {order.ORDNO} is {order.STATUS}."
      error→ Output "{as400Error.message}"

Form to batch job

Form start → Execute SQL: INSERT INTO ORDHDR (...) VALUES ({form.fields.customer}, ...)
  → Run CL command: SBMJOB CMD(CALL PGM(MYLIB/PRCORD) PARM('{form.fields.orderNo}')) JOB(PRCORD)
  → Form reply "Order received."

Price from an RPG program

Start → Call program MYLIB.GETPRICE: ITEM CHAR(15) In {sku}, QTY INTEGER In {qty}, PRICE DECIMAL(9,2) Out
  → Switch on {program.outputs.PRICE} → Output "Price: {program.outputs.PRICE}"

Request and reply over data queues

Start → Data queue Send ORDERS/MYLIB: "{orderNo};{customerNo}"
  → Data queue Receive REPLIES/MYLIB, Key {orderNo}, Wait 30 s → {reply}
  → Switch {reply.received}: true → Output "{reply.data}" / false → Output "No answer yet."

Troubleshooting

"The IBM i driver is not installed where the flow runs" (driver_missing)

The engine host has no ODBC driver with the configured name (SQLSTATE IM002), or no ODBC driver manager. Install IBM i Access Client Solutions' ODBC driver on that host (64-bit), or set ODBC driver to the name it is registered under. On Linux, install unixODBC and the driver's .rpm/.deb, then check odbcinst -q -d.

"The IBM i system could not be reached" (connection)

SQLSTATE 08xxx: wrong host, a firewall between the engine host and port 8471 (9471 with TLS), or the database host server not started (STRHOSTSVR SERVER(*DATABASE) and STRTCPSVR SERVER(*DDM)).

"The IBM i system refused the user profile or password" (authentication)

SQLSTATE 28000: wrong password, a disabled profile (CHGUSRPRF ... STATUS(*ENABLED)), or an expired one. Check with DSPUSRPRF.

"An object the statement uses does not exist on the IBM i" (not_found)

SQLSTATE 42704 or 42884: the table, procedure or library is not where the statement looks. With SQL naming qualify names (MYLIB.CUSTOMERS) or set Default library; with system naming add the library to Library list. For Call program, the program is not declared as a procedure: see Call program.

"The IBM i rejected the statement" (sql)

Any other SQLSTATE: syntax, a type mismatch (compare a DECIMAL column with a number, not text), a missing authority (SQL0551), a constraint. {as400Error.detail} has the system's message and the SQLSTATE.

"The IBM i did not answer in time" (timeout)

The statement ran past the timeout: add an index or a FETCH FIRST, or raise the task's Timeout (up to 300 s). A Receive on an empty data queue is not a timeout: it returns received = false.

Rows come back with trailing blanks or as upper-case names

CHAR columns are blank-padded on the IBM i: keep Trim text on. Column names are as the system returns them; alias them in the SELECT (CUSNAM AS name).

Reference

Error codes

{as400Error.code}	Meaning
invalid_input	The step's own fields are wrong: empty SQL, a SELECT in Execute SQL, a bad library or program name, a value that is not of the parameter's type, an output that is not a {variable}.
not_configured	System, user profile or password missing in the environment's configuration, or a library name that is not valid.
driver_missing	No ODBC driver / driver manager on the engine host.
connection	The system could not be reached.
authentication	The profile or password was refused.
not_found	A table, program, procedure or library does not exist.
sql	The system rejected the statement.
timeout	The statement did not finish within the timeout.

Limits

Limit	Value
Rows a query returns to variables	10,000 (default 1,000)
Statement timeout	1–300 s (default 30)
Data queue wait	0–120 s
CL command length	32,000 characters
Data queue entry	64,512 characters
Program call parameters	64