New in 3.3
System health and security, System Checks, system service pages, workbench actions and 124 more developer tools: one overview page, which job blocks which, what is close to an IBM i limit, a security score, the security audit journal hour by hour, the disk units of each ASP, the statistics of the SQL inside your programs, and VS Code tasks that run on the IBM i.
Released in version 3.3.0. Update the extension (Extensions view → Vanthrex → Update) to get every page below. The pictures are drawn with Vanthrex’s own code and sample data; in VS Code each page is interactive.
- System Health Overview
- Lock Wait Chains
- Limits Watch
- Security Scorecard
- Security Audit Journal
- Disk Units Map
- Embedded SQL Statement Statistics
- VS Code tasks for IBM i
- System Checks
- System service pages
- Workbench actions
- 50 more developer tools
- 50 more developer tools, second batch
- 24 more developer tools, third batch
- Fixed in 3.3.0
- Where to find them
System Health Overview
One page that answers “is the system OK right now?”: disk space per ASP, limits close to their maximum, jobs waiting for a lock, jobs waiting for a reply (MSGW) and the security score, each as a card with a traffic light and a button to its detail page.
- ASP 1: 75 % used of 4.00 TB
- ASP 2: 77.5 % used of 2.00 TB
- Units filled unevenly in ASP 1, 2 (STRASPBAL balances them).
- Maximum number of rows in a table partition (APPLIB/ORDHIST): 90.9 %
- Maximum number of locks held by a job: 82 %
- 2 object(s) or row(s) contended.
- Head blocker 412233/BATCH/NIGHTLY blocks 3 job(s).
- 412377/BATCH/INVOICES
- QPWDEXPITV = *NOMAX (recommended: 90 days or less)
Open it: System Health Overview (Visual) from the command palette or the quick menu (Ctrl+Alt+I).
What you can do:
- Read the overall status at the top: Everything looks fine, Some things are worth a look or Something needs attention now.
- Click Open … on a card for the detail page: Disk Units Map, Limits Watch, Lock Wait Chains, Active Jobs Map or Security Scorecard.
- Tick Refresh every minute to keep it on a second screen.
- Each card is read on its own: a card you are not authorized to read says why, and the others still work.
| Card | Orange | Red |
|---|---|---|
| Disk space | an ASP at 80 % used, or its units filled unevenly | an ASP at 90 % used |
| Limits | a limit at 75 % of its maximum | a limit at 90 % |
| Lock waits | a job waiting for a lock | 5 jobs waiting, or one job blocking 3 or more |
| Messages waiting | — | any job waiting for a reply (MSGW) |
| Security | score below 75 | score below 60 |
Lock Wait Chains
Which job blocks which, right now: every object and row a job waits for, with the jobs holding it on one side and the jobs waiting on the other. The head blocker (👑) holds a lock others wait for and waits for nothing itself — usually the job to look at.
APPLIB/ORDERS *FILE (ORDERS) row 1042 ←APPLIB/ORDCTL *DTAARA ←Open it: Lock Wait Chains (Visual) from the command palette or the quick menu. Click a job for its job log, right-click for Work with Active Jobs; tick Refresh every 10 seconds while you watch a lock clear.
Limits Watch
What is getting close to an IBM i limit (QSYS2.SYSLIMITS) — rows in a table, locks held by a job, objects in an IFS directory, members in a file… — as gauges of % of the maximum, the closest first.
Open it: Limits Watch (Visual) from the command palette or the quick menu. Filter by category or by text. Orange from 75 %, red from 90 %.
Security Scorecard
A score from 0 to 100 for 14 security system values (QSECURITY, QPWDLVL, QPWDEXPITV, QMAXSIGN, QAUDCTL, QCRTAUT, QALWOBJRST…) against common recommendations, each with its current value and why it matters, plus the enabled profiles with *ALLOBJ, with a password older than a year or unused for 180 days.
| Check | Value | Current | Recommended | |
|---|---|---|---|---|
| Risk | Password expiration | QPWDEXPITV | *NOMAX | 90 days or less |
| Review | Public authority to new objects | QCRTAUT | *CHANGE | *USE or *EXCLUDE |
| Review | Inactive job time-out | QINACTITV | *NONE | 60 minutes or less |
| Review | Restore of security-sensitive objects | QALWOBJRST | *ALL | *NONE outside install windows |
| OK | Security level | QSECURITY | 40 | 40 or 50 |
| OK | Password level | QPWDLVL | 3 | 2 or 3 |
| OK | Minimum password length | QPWDMINLEN | 8 | 8 or more |
Copy as a Markdown report puts the scorecard on the clipboard — or opens it in an editor — for an audit file, a ticket or an e-mail:
# Security scorecard — DEV400
2026-11-06 09:30 · score **86 / 100** (grade B) · 1 risk(s), 3 to review
| State | Check | System value | Current | Recommended |
|---|---|---|---|---|
| Risk | Password expiration | QPWDEXPITV | `*NOMAX` | 90 days or less |
| Review | Public authority to new objects | QCRTAUT | `*CHANGE` | *USE or *EXCLUDE |
| Review | Inactive job time-out | QINACTITV | `*NONE` | 60 minutes or less |
| Review | Restore of security-sensitive objects | QALWOBJRST | `*ALL` | *NONE outside install windows |
…
Open it: Security Scorecard (Visual) from the command palette or the quick menu. *SECADM or *ALLOBJ shows every profile. Nothing is changed from the page.
Security Audit Journal
The security audit journal (QAUDJRN) of the last 6 hours to 7 days as a heat map, one row per entry type and one square per hour: authority and sign-on failures in red, changes to authorities, profiles and system values in orange, other activity in blue.
Open it: Security Audit Journal (Visual) from the command palette or the quick menu. Next to the map: the users with the most entries (with their authority and password failures) and the latest failures and security changes; click one for the job log. Reading QAUDJRN needs *AUDIT or *ALLOBJ special authority.
Disk Units Map
Every disk unit grouped by ASP, drawn as a can filled to its % used, with a bar for how busy its disk arm is.
Open it: Disk Units Map (Visual) from the command palette or the quick menu. It says when the units of an ASP are filled unevenly (STRASPBAL balances them) and when a disk arm is so busy that jobs wait.
Embedded SQL Statement Statistics
Every SQL statement in the programs of a library (or of one program), from QSYS2.SYSPROGRAMSTMTSTAT: how often it ran, total, average and longest time, how often its access plan was rebuilt — slowest first, with hints.
| Program | Stmt | Runs | Total ms | Avg ms | Rebuilds | Hint |
|---|---|---|---|---|---|---|
| ORDLIST | 412 | 18,240 | 912,000 | 50 | 9800 | Access plan rebuilt on most runs |
| INV100 | 88 | 4,120 | 205,000 | 49.76 | 2 | |
| ORDSRV | 1530 | 98,000 | 98,000 | 1 | 0 | |
| PURGE | 60 | 0 | 0 | 0 | Never run since the statistics were collected |
Open it: right-click a library or a program → Embedded SQL Statement Statistics, or from the command palette. Statement numbers are those of the compile listing; use Explain on a slow statement to see its access plan.
VS Code tasks for IBM i
A vanthrex task type for .vscode/tasks.json that runs a CL command (with extra libraries on the library list) or a PASE command (in an IFS folder) over the connection, with the Code for IBM i style variables &CURLIB, &USER, &HOST, &NAME, &EXT, &FILENAME and &RELATIVEPATH.
{
"version": "2.0.0",
"tasks": [
{
"label": "Compile this RPG from the IFS",
"type": "vanthrex",
"kind": "cl",
"command": "CRTBNDRPG PGM(&CURLIB/&NAME) SRCSTMF('/home/&USER/src/&FILENAME') DBGVIEW(*SOURCE)",
"libraries": ["APPLIB", "UTILLIB"],
"problemMatcher": []
},
{
"label": "Build with makei",
"type": "vanthrex",
"kind": "pase",
"command": "makei build",
"cwd": "/home/&USER/myproject"
}
]
}
Run it: Terminal → Run Task…; Vanthrex also offers ready-made tasks (compile RPG from the IFS, build with makei, show the library list). The output appears in the terminal panel.
System Checks
One menu — System Checks (Backups, Sign-on, Security…)… in the command palette or the quick menu (Ctrl+Alt+I) — for the questions asked when something feels wrong, with five new pages. Every page only reads; a fix opens the CL prompter (F4), so nothing changes until you press Enter there.
| Check | What it answers | Read from |
|---|---|---|
| Backup freshness | Which user libraries were never saved, or not for more than vanthrex.backupDays days (7 by default)? | QSYS2.OBJECT_STATISTICS (*ALLUSR *LIB) |
| Sign-on problems | Who is locked out, close to it, or has a password or profile expired or expiring — and the CHGUSRPRF that fixes it | QSYS2.USER_INFO, QMAXSIGN |
| Job descriptions that name a user | Which job descriptions run jobs as a named user instead of USER(*RQD), and can *PUBLIC use them? | QSYS2.JOB_DESCRIPTION_INFO, OBJECT_PRIVILEGES |
| User objects in IBM libraries | Which of our programs, files or commands sit in QSYS, QSYS2 or QGPL, at risk on a release upgrade? | QSYS2.OBJECT_STATISTICS |
| Environment variables | What are JAVA_HOME, QIBM_QSH_CMD_OUTPUT, PASE_PATH… set to, at the system level and in my job? | QSYS2.ENVIRONMENT_VARIABLE_INFO |
The menu also leads to the System Health Overview, Lock Wait Chains, Limits Watch and Failed Jobs, and to the new System Diagnostics (problems logged, ended jobs, temporary storage, watches, JVMs), Security Configuration, Fix Currency, Hardware and Devices, Network Configuration, Subsystem Setup and Save Files and Tapes pages.
Backup freshness
Every user library with the date of its last SAVLIB or SAVCHGOBJ, the oldest first. Click a library for its information.
Level Library Last saved Days Finding──────── ─────── ────────── ──── ───────────────────────● High TESTLIB — Never saved.● High ARCHIVE 2026-06-30 134 Not saved for 134 days.● Medium APPDTA 2026-10-20 22 Not saved for 22 days. Info APPLIB 2026-11-11 0 Saved 0 day(s) ago.
Sign-on problems
For the help desk: who cannot sign on and why. Fix (F4)… opens CHGUSRPRF in the prompter, filled in.
Level User Problem Fix (F4)──────── ──────── ─────────────────────────────────────────────────────────── ─────────────────────────────────────────● High BATCHUSR Password expired on 2026-11-04. CHGUSRPRF USRPRF(BATCHUSR) PWDEXP(*YES)● High JSMITH Disabled after 3 invalid sign-on attempts. CHGUSRPRF USRPRF(JSMITH) STATUS(*ENABLED)● High MLEROY 2 invalid sign-on attempt(s): the profile is disabled at 3.● Medium APARKER Password expires in 3 day(s).
Job descriptions that name a user
A job description with USER(APPADMIN) that *PUBLIC may use lets anyone submit jobs that run as APPADMIN.
Level Job description User *PUBLIC Finding────── ─────────────── ──────── ──────── ──────────────────────────────────────────────────────────────────────────────● High APPLIB/NIGHTLY APPADMIN *USE *PUBLIC *USE: anyone can submit jobs that run as APPADMIN, a powerful profile. Info APPLIB/BATCH APPUSR *EXCLUDE Runs as APPUSR.
System service pages
Nine read-only pages for the parts of the system you normally reach through several green-screen commands. Open them from the command palette, the quick menu (Ctrl+Alt+I) or System Checks.
| Page | What it shows |
|---|---|
| Network Configuration | TCP/IP of the connection, network attributes, the Telnet, SMTP and time (NTP) servers, HTTP servers, relational database entries and the subsystems server jobs are routed to (QSYS2.TCPIP_INFO, NETWORK_ATTRIBUTE_INFO, TELNET_SERVER_ATTRIBUTES, SMTP_ATTRIBUTES, TIME_PROTOCOL_INFO, HTTP_SERVER_INFO, RDB_ENTRY_INFO, SERVER_SBS_ROUTING). Read only. |
| Fix Currency | Firmware level against IBM’s, defective PTFs installed and CVEs not yet fixed, plus the Electronic Service Agent status (QSYS2.FIRMWARE_CURRENCY, DEFECTIVE_PTF_CURRENCY, CVE_CURRENCY, ELECTRONIC_SERVICE_AGENT_INFO). Read only. |
| Hardware and Devices | The status of lines, controllers and devices, the hardware resources and NVMe devices, failed or damaged ones in red (QSYS2.CONFIGURATION_STATUS, HARDWARE_RESOURCE_INFO, NVME_INFO). Read only. |
| Subsystem Setup | For one subsystem or all: autostart jobs, routing entries, prestart jobs, job queue entries, workstation and communications entries and pools (QSYS2.AUTOSTART_JOB_INFO, ROUTING_ENTRY_INFO, PRESTART_JOB_INFO, JOB_QUEUE_ENTRIES, WORKSTATION_INFO, COMMUNICATIONS_ENTRY_INFO, SUBSYSTEM_POOL_INFO). Read only. |
| Save Files and Tapes | Save files with what and when they saved, tape cartridges and media libraries (QSYS2.SAVE_FILE_INFO, TAPE_CARTRIDGE_INFO, MEDIA_LIBRARY_INFO). Read only. |
| System Diagnostics | Problems logged, recently ended jobs (abnormal ends in red), the largest temporary storage buckets, active watches, Java virtual machines and Collection Services (QSYS2.PROBLEM_INFO, ENDED_JOB_INFO, SYSTMPSTG, WATCH_INFO, JVM_INFO, COLLECTION_SERVICES_INFO). Read only. |
| Security Configuration | The security attributes, the certificates applications use and the exit points with their programs (QSYS2.SECURITY_INFO, CERTIFICATE_USAGE_INFO, EXIT_POINT_INFO). Read only. |
| Job Internals | For one job: its open files, the IFS objects it uses and its database connections (QSYS2.OPEN_FILES, IFS_JOB_INFO, ACTIVE_DB_CONNECTIONS). Read only. |
| Maintained Temporary Indexes | The temporary indexes the query engine maintains (QSYS2.MTI_INFO), most used first. |
Workbench actions
Everyday actions on members, libraries, IFS files and tables, from the right-click menus of the views and the command palette.
| Action | What it does |
|---|---|
| Rename Member and Change Descriptions | Rename a member (RNMM) and change the description of a member (CHGPFM) or an object (CHGOBJD) from the Libraries view. |
| Library List Order and Clean-up | Move a library up or down in your library list, and remove the libraries that no longer exist. |
| Library Actions | Change the description of a library, copy it to a new library (CPYLIB), rename it, clear it or delete it. Clear and delete ask you to type the library name; IBM libraries (Q…, SYS…, #…) cannot be renamed, cleared or deleted from here, and the library list follows a rename or a delete. |
| Download Members to This PC | One member, a source file or every source file of a library to a local folder, laid out Library → File → Member, File → Member or all in one folder. |
| Replace a Member from a File on This PC | Upload a local file into a member: replace a member, or add one to a source file (the name and type come from the file name). |
| Compare with an IFS File or a File on This PC | Compare the open member or file side by side with any IFS file or a local file. |
| Shared Snippets | Save the selection (or the whole file) as a snippet in an IFS folder and insert snippets from it; point vanthrex.sharedSnippets.folder to a folder the team can write to so everyone shares them. |
| Find IFS Files by Name | Find stream files by name or pattern (*.rpgle, order…) under a folder and all its subfolders, and open one. |
| Who Has It Locked? (IFS) | Right-click an IFS file or folder (or the editor of an IFS file) to see the jobs that have it open right now (QSYS2.IFS_OBJECT_LOCK_INFO), with their user, number of opens and how they use it. Jobs that keep others from writing it (share-none modes, save locks, check-outs) are first and in red; click one for its job log, or Work with the blocking jobs. On a folder, every stream file directly in it is checked, and Include subfolders checks the whole tree below it. When saving an IFS file fails while other jobs have it open, Vanthrex says how many (and how many keep others from writing it) and offers the page in one click. Each job shows its status in plain words (waiting for a reply, waiting for a lock, running…), its function and subsystem; a job waiting for a reply is marked, since a stuck job is often the one holding the file. Tell me when it is free watches a file every 10 seconds (up to 30 minutes, cancellable) and says when no job keeps others from writing it any more, with a button to open it. Read only; needs IBM i 7.3 TR11, 7.4 TR5 or later. |
| Next and Previous Changed Block | Jump to the next or previous block of lines changed since the highlight date (or not saved yet). |
| Clear and Copy Table Data | Clear the rows of a table (CLRPFM, after typing its name), or copy its rows to another table: replace or add them, columns matched by name, or create the copy (CPYF). |
| Generate CALL Statement | A ready-to-fill CALL (procedure), VALUES (scalar function) or SELECT … FROM TABLE() (table function), one named parameter per line with its mode and type, read from QSYS2.SYSROUTINES and SYSPARMS. |
| SQL from a JSON Sample | The selected (or copied) JSON turned into a VALUES JSON_OBJECT / JSON_ARRAY statement that returns it; replace the sample values with your columns. |
50 more developer tools
Fifty new tools in the Developer Toolbox (Ctrl+Alt+A, or Vanthrex Tools: … in the command palette): the small edits that eat a developer’s day, done in one step. Select the lines (or put the cursor in the SQL statement), press Ctrl+Alt+A and type a word of the tool; Ctrl+Z undoes any of them.
From a CREATE TABLE to its MERGE
Put the cursor in the CREATE TABLE and pick MERGE (insert or update) from this CREATE TABLE: the key comes from PRIMARY KEY (or the first column).
CREATE TABLE APP.ORDERS ( ORDER_NO DECIMAL(9, 0) NOT NULL, CUSTNO DECIMAL(7, 0), STATUS CHAR(1), PRIMARY KEY (ORDER_NO));
MERGE INTO APP.ORDERS TUSING (VALUES (?, ?, ?)) S (ORDER_NO, CUSTNO, STATUS) ON T.ORDER_NO = S.ORDER_NO WHEN MATCHED THEN UPDATE SET CUSTNO = S.CUSTNO, STATUS = S.STATUS WHEN NOT MATCHED THEN INSERT (ORDER_NO, CUSTNO, STATUS) VALUES (S.ORDER_NO, S.CUSTNO, S.STATUS)
LABEL ON for every column
The text that 5250 tools, Query and DSPFFD show, from the column names — then adjust the words.
LABEL ON TABLE APP.ORDERS IS 'Orders';LABEL ON COLUMN APP.ORDERS ( ORDER_NO TEXT IS 'Order No', CUSTNO TEXT IS 'Custno', STATUS TEXT IS 'Status');
An index for this SELECT
The columns compared with = in the WHERE, then the ORDER BY — the shape of index the optimizer can use.
SELECT * FROM APP.ORDERS WHERE CUSTNO = ? AND STATUS = 'O' ORDER BY ORDDATE DESC
CREATE INDEX APP.ORDERS_IX_CUSTNO ON APP.ORDERS (CUSTNO, STATUS, ORDDATE)
Variables set to empty, or shown while testing
Select DCL-S lines: Set the selected variables to empty writes the right empty value for each type; DSPLY the selected variables writes one line each.
dcl-s custName char(30);dcl-s total packed(11:2);dcl-s found ind;dcl-s note varchar(100);
custName = *blanks;total = 0;found = *off;note = '';dsply ('custName=' + %trimr(custName));dsply ('total=' + %char(total));dsply ('found=' + %char(found));dsply ('note=' + %trimr(note));
READ and cursor loops
Insert a READ loop over a file asks for the file and the key; Insert an SQL cursor loop asks for the SELECT.
setll (custNo) orders;reade (custNo) orders;dow not %eof(orders); // process the record reade (custNo) orders;enddo;
exec sql declare C1 cursor for SELECT ORDER_NO, AMOUNT FROM ORDERS WHERE STATUS = ?;exec sql open C1;dow 1 = 1; exec sql fetch next from C1 into :row; if sqlcode = 100 or sqlcode < 0; leave; endif; // process the rowenddo;if sqlcode < 0; // handle the SQL error (SQLSTATE in sqlstate)endif;exec sql close C1;
CHKOBJ OBJ(APPLIB/ORDERS) OBJTYPE(*FILE)MONMSG MSGID(CPF9801) EXEC(DO) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('ORDERS *FILE not found in APPLIB') MSGTYPE(*ESCAPE)ENDDO
ORDER 42 → x'D6D9C4C5D940F4F2'Text — any source, on the selection
| Tool | What it does |
|---|---|
| Sort the lines (A → Z) | B, A, C10, C9 → A, B, C9, C10 |
| Sort the lines (Z → A) | C, B, A |
| Remove duplicate lines | A, B, A → A, B |
| Reverse the lines | 1, 2, 3 → 3, 2, 1 |
| Trim the lines | ' a ' → 'a' |
| Upper case (literals untouched) | if name = 'Abc' → IF NAME = 'Abc' |
| Lower case (literals untouched) | IF NAME = 'Abc' → if name = 'Abc' |
| Join the lines with commas | A⏎B⏎C → A, B, C |
| Split a comma list into lines | A, B; C → A⏎B⏎C |
| Quote each line | it's → 'it''s', |
| Number the lines | 1 a, 2 b… |
| Count lines, words and characters | Of the selection, or of the whole source |
| Text to an EBCDIC hex literal | AB → x'C1C2' (CCSID 37) |
| EBCDIC hex literal to text | x'C1C2' → AB (CCSID 37) |
| Put the lines in a comment box | // ====⏎// Title⏎// ==== |
| Names to camelCase | CUSTOMER_NAME → customerName |
| Names to UPPER_SNAKE (SQL style) | customerName → CUSTOMER_NAME |
| Align the selection as a table | Columns separated by two spaces or a tab |
SQL — on the statement at the cursor
| Tool | What it does |
|---|---|
| SELECT → SELECT COUNT(*) | SELECT a, b FROM t WHERE … → SELECT COUNT(*) FROM t WHERE … |
| Wrap in a common table expression (WITH) | WITH X AS (…) SELECT * FROM X |
| Add a row number column | ROW_NUMBER() OVER (…) AS RN |
| Add paging (OFFSET … FETCH NEXT) | OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY |
| Read without waiting for locks (WITH UR) | SELECT … WITH UR |
| CREATE TABLE → CREATE OR REPLACE TABLE | ON REPLACE PRESERVE ROWS |
| LABEL ON for every column of this CREATE TABLE | LABEL ON COLUMN T (A TEXT IS '…') |
| COMMENT ON for every column of this CREATE TABLE | COMMENT ON COLUMN T (A IS '…') |
| INSERT from this CREATE TABLE | INSERT INTO T (A, B) VALUES (?, ?) |
| SELECT by key from this CREATE TABLE | SELECT A, B FROM T WHERE KEY = ? |
| UPDATE by key from this CREATE TABLE | UPDATE T SET B = ? WHERE KEY = ? |
| MERGE (insert or update) from this CREATE TABLE | MERGE INTO T USING (VALUES …) ON KEY … |
| CREATE INDEX for this SELECT | Columns compared with = in the WHERE, then the ORDER BY |
RPG
| Tool | What it does |
|---|---|
| CLEAR the selected variables | dcl-s a … → clear a; |
| RESET the selected variables | dcl-s a … → reset a; |
| Set the selected variables to empty | a = 0; b = *blanks; c = ''; d = *off; |
| DSPLY the selected variables (for testing) | dsply ('a=' + %char(a)); |
| DCL-S lines → a parameter list (DCL-PI) | dcl-s a char(10); → dcl-pi … a char(10) const; |
| Parameter lines → DCL-S | a char(10) const; → dcl-s a char(10); |
| Values → a SELECT / WHEN | A⏎B → select; when x = 'A'; … endsl; |
| Values → an array initialised with them | dcl-ds …; *n char(5) inz('A'); … dim(n) pos(1); |
| Insert a READ loop over a file | setll / read / dow not %eof / enddo |
| Insert an SQL cursor loop | declare / open / fetch until SQLCODE 100 / close |
| Insert the program status data structure (PSDS) | program, status, message ID, job, user |
| Insert a file information data structure (INFDS) | file, status, opcode, record, RRN |
| Comment out with a date, author and reason | // 2026-11-06 GG: reason |
CL
| Tool | What it does |
|---|---|
| Insert RTVJOBA with its variables | JOB, USER, NBR, TYPE, CURLIB |
| Insert CHKOBJ with MONMSG | CHKOBJ … MONMSG CPF9801 EXEC(DO) SNDPGMMSG … *ESCAPE |
| Insert a RCVF loop over a file | DCLF, RCVF, MONMSG CPF0864 |
| Insert SNDPGMMSG (escape, completion…) | SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) … |
| Put the selection between OVRDBF and DLTOVR | OVRDBF FILE(…) TOFILE(…) … DLTOVR |
| Insert RTVSYSVAL with its variable | DCL … RTVSYSVAL SYSVAL(…) RTNVAR(…) |
50 more developer tools, second batch
Fifty more tools in the Developer Toolbox (Ctrl+Alt+A, or Vanthrex Tools: … in the command palette) for the code IBM i developers write again and again: IFS files, data areas and queues, job log messages and commit blocks in RPG; JSON, views, safe previews, audit columns and triggers in SQL; a CL program with error handling, retries and batch jobs; and the conversions you otherwise look up. Ctrl+Z undoes any of them.
A SELECT as one JSON array
Put the cursor in a SELECT and pick SELECT → one JSON array (JSON_ARRAYAGG): one JSON object per row, named after the columns — ready for a web service or an IFS file.
SELECT ORDNO, CUSTNO, TOTAL FROM APPLIB.ORDERS WHERE STATUS = 'O'
SELECT JSON_ARRAYAGG( JSON_OBJECT('ordno' VALUE ORDNO, 'custno' VALUE CUSTNO, 'total' VALUE TOTAL) ) AS JSON FROM ( SELECT ORDNO, CUSTNO, TOTAL FROM APPLIB.ORDERS WHERE STATUS = 'O' ) X
See the rows before you delete them
DELETE → SELECT (and UPDATE → SELECT) writes the SELECT of the very rows the statement would touch, with the same WHERE.
DELETE FROM APPLIB.ORDERS WHERE STATUS = 'X' AND ORDDATE < CURRENT DATE - 1 YEAR
SELECT * FROM APPLIB.ORDERS WHERE STATUS = 'X' AND ORDDATE < CURRENT DATE - 1 YEAR
Audit columns filled by Db2
Add audit columns to this CREATE TABLE: who created each row and when, and a ROW CHANGE TIMESTAMP that Db2 updates by itself.
CREATE TABLE APPLIB.ORDERS ( ORDNO INTEGER NOT NULL PRIMARY KEY, TOTAL DECIMAL(11, 2))
CREATE TABLE APPLIB.ORDERS ( ORDNO INTEGER NOT NULL PRIMARY KEY, TOTAL DECIMAL(11, 2), CREATED_AT TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, CREATED_BY VARCHAR(18) NOT NULL DEFAULT USER, CHANGED_AT TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)
Who has this table locked?
An IBM i Services query for the table of the statement at the cursor — run it with Ctrl+Enter.
SELECT JOB_NAME, LOCK_STATE, LOCK_STATUS, LOCK_SCOPE FROM QSYS2.OBJECT_LOCK_INFO WHERE SYSTEM_OBJECT_SCHEMA = 'APPLIB' AND SYSTEM_OBJECT_NAME = 'ORDERS'
A CL command from RPG
Select a CL command in RPG and pick CL command → RPG call (QSYS2.QCMDEXC): the quotes are doubled for you.
CPYF FROMFILE(APPLIB/ORDERS) TOFILE(QTEMP/ORDERS) CRTFILE(*YES)exec sql call qsys2.qcmdexc('CPYF FROMFILE(APPLIB/ORDERS) TOFILE(QTEMP/ORDERS) CRTFILE(*YES)');if sqlcode < 0; // the command failed: the CPF message is in the job logendif;
JSON from RPG variables
Select DCL-S lines and pick A JSON object from the selected variables (SQL).
dcl-s custNo packed(7:0);dcl-s name char(30);dcl-s created date;
exec sql values json_object( 'custNo' value :custNo, 'name' value :name, 'created' value :created ) into :json;
Retry while an object is in use
Insert a retry loop with DLYJOB asks for the command; it is tried 3 times, 10 seconds apart, then the program stops with a clear message.
DCL VAR(&TRY) TYPE(*DEC) LEN(3 0) VALUE(0) /* put it with the other DCLs */RETRY: CHGVAR VAR(&TRY) VALUE(&TRY + 1)ALCOBJ OBJ((APPLIB/ORDERS *FILE *EXCL)) WAIT(0)MONMSG MSGID(CPF0000) EXEC(DO) IF COND(&TRY *LT 3) THEN(DO) DLYJOB DLY(10) GOTO CMDLBL(RETRY) ENDDO SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Still failing after the retries') MSGTYPE(*ESCAPE)ENDDO
CALL PGM(APPLIB/NIGHTLY) PARM('2026')SBMJOB CMD(CALL PGM(APPLIB/NIGHTLY) PARM('2026')) JOB(NIGHTLY) JOBQ(QBATCH)One date, every IBM i format
Select 1261106 (or 2026-11-06, 06.11.2026, 26310…) and pick Convert a date between IBM i formats: choose the format to replace it with.
2026-11-06 *ISO20261106 *ISO01261106 *CYMD261106 *YMD110626 *MDY061126 *DMY06.11.2026 *EUR11/06/2026 *USA26310 *JUL2026310 *LONGJUL
RPG snippets
In an RPG source: put the cursor where the code goes, or select the variables or the CL command first. (Source: src/rpg/rpgSnippets.ts.)
| Tool | Example |
|---|---|
| Insert a date conversion between formats | %char(%date(x: *ymd): *iso) in MONITOR |
| Insert writing lines to an IFS file (SQL) | QSYS2.IFS_WRITE_UTF8 REPLACE / APPEND |
| Insert reading an IFS file line by line (SQL) | QSYS2.IFS_READ_UTF8 in a cursor |
| CL command → RPG call (QSYS2.QCMDEXC) | exec sql call qsys2.qcmdexc('…'); |
| Insert reading and writing a data area | DTAARA data structure, IN *LOCK / OUT |
| Insert sending and receiving a data queue entry | QSNDDTAQ / QRCVDTAQ prototypes and calls |
| Insert a job log message (QMHSNDPM) | CPF9898 *INFO in the job log |
| Insert a time measurement (milliseconds) | %diff(%timestamp(): started: *mseconds) |
| Insert a commit / rollback block | MONITOR … COMMIT … ON-ERROR … ROLLBACK |
| Insert a %LOOKUP in a sorted array | SORTA + %LOOKUP (binary search) |
| A CSV line from the selected variables | csv = %trim(a) + ';' + %char(b)… |
| A JSON object from the selected variables (SQL) | exec sql values json_object('a' value :a, …) into :json; |
| Getter and setter procedures for the selected variables | getName() / setName(value) |
| Insert a retry loop with a delay | FOR … MONITOR … DLYJOB |
| Insert sending an e-mail (SNDSMTPEMM) | QSYS2.QCMDEXC with SNDSMTPEMM |
SQL builders — on the statement at the cursor
In an SQL source: put the cursor in the statement; the result is added below it. (Source: src/core/sqlBuilders.ts.)
| Tool | Example |
|---|---|
| SELECT → one JSON array (JSON_ARRAYAGG) | JSON_ARRAYAGG(JSON_OBJECT(…)) |
| CREATE VIEW from this SELECT | CREATE OR REPLACE VIEW … AS SELECT … |
| A table from this SELECT (CREATE TABLE AS … WITH DATA) | CREATE TABLE … AS (SELECT …) WITH DATA |
| DELETE → SELECT (see the rows first) | DELETE FROM t WHERE … → SELECT * FROM t WHERE … |
| UPDATE → SELECT (see the rows first) | UPDATE t SET … WHERE … → SELECT * FROM t WHERE … |
| Add audit columns to this CREATE TABLE | CREATED_AT, CREATED_BY, CHANGED_AT (ROW CHANGE TIMESTAMP) |
| Add an identity column to this CREATE TABLE | ID BIGINT GENERATED ALWAYS AS IDENTITY |
| DROP TABLE IF EXISTS for this CREATE TABLE | DROP TABLE … IF EXISTS |
| Insert an SQL procedure skeleton | CREATE OR REPLACE PROCEDURE … with an error handler |
| Insert an SQL function skeleton | CREATE OR REPLACE FUNCTION … RETURNS … |
| Audit trigger for this CREATE TABLE | AFTER INSERT OR UPDATE OR DELETE → T_AUDIT |
| Find duplicate rows in this table | GROUP BY … HAVING COUNT(*) > 1 |
| Only the last days (date filter) | WHERE col >= CURRENT TIMESTAMP - n DAYS |
| Who has this table locked? (IBM i Services) | QSYS2.OBJECT_LOCK_INFO |
| Size and rows of this table (IBM i Services) | QSYS2.SYSTABLESTAT |
CL snippets
In a CL source; RUNSQL also works from an SQL source and copies the command to the clipboard. (Source: src/core/clSnippets.ts.)
| Tool | Example |
|---|---|
| Insert a CL program skeleton with error handling | PGM … MONMSG CPF0000 … RCVMSG … SNDPGMMSG … ENDPGM |
| Insert a retry loop with DLYJOB | RETRY: … MONMSG … DLYJOB … GOTO RETRY |
| Put the selection between ALCOBJ and DLCOBJ | ALCOBJ … *EXCL … DLCOBJ |
| Submit the selected command to batch (SBMJOB) | SBMJOB CMD(…) JOB(…) JOBQ(QBATCH) |
| Insert CPYF (created when missing) | CPYF … MBROPT(*REPLACE) CRTFILE(*YES) |
| Insert saving a library to a save file | CRTSAVF / CLRSAVF / SAVOBJ |
| Insert a date conversion (CVTDAT) | CVTDAT … FROMFMT(*YMD) TOFMT(*ISO) |
| SQL statement → RUNSQL for CL | RUNSQL SQL('…') COMMIT(*NONE) |
| Insert a question to the user (SNDUSRMSG) | SNDUSRMSG … VALUES(Y N) MSGRPY(&REPLY) |
| Insert RTVOBJD (exists, last used, changed) | RTVOBJD … CHGDATE USEDATE TEXT |
IBM i converters — any source
Select the value (or put the cursor on it) in any file. (Source: src/core/ibmiConverters.ts.)
| Tool | Example |
|---|---|
| Convert a date between IBM i formats | 1261106 → 2026-11-06, 06.11.2026, 26310… |
| Insert today’s date in an IBM i format | *ISO, *CYMD, *YMD, *JUL… |
| Hex ↔ decimal | 31 ↔ x'1F' |
| Short system name for a long SQL name | CUSTOMER_ORDER_HISTORY → CSTORDHSTR (FOR SYSTEM NAME) |
| What is this CCSID? | 37, 297, 1208, 65535… |
| Copy DSPMSGD for the message ID | CPF9801 → DSPMSGD RANGE(CPF9801) MSGF(QCPFMSG) |
| SQL types → RPG types | DECIMAL(9, 2) → packed(9:2) |
| RPG types → SQL types | packed(9:2) → DECIMAL(9, 2) |
| Seconds ↔ h:mm:ss | 3725 ↔ 1:02:05 |
| Size and range of a numeric type | packed(9:2) → 5 bytes, ±9999999.99 |
24 more developer tools, third batch
Twenty-four more tools in the Developer Toolbox (Ctrl+Alt+A): error handling that is easy to forget, modern RPG whose syntax few remember, the SQL analytic queries people search the web for, and the CL loops every shop writes again. Ctrl+Z undoes any of them.
An SQLSTATE check after every EXEC SQL
Select the code (or nothing for the whole source) and pick Add an SQLSTATE check after every EXEC SQL. Statements already followed by a check of SQLSTATE or SQLCODE, and DECLARE or SET OPTION, are left alone.
exec sql update orders set status = :st where ordno = :ordNo;exec sql delete from ordwork where ordno = :ordNo;
exec sql update orders set status = :st where ordno = :ordNo;if %subst(sqlstate: 1: 2) > '02'; // SQL error: the message is in the job log (or use GET DIAGNOSTICS)endif;exec sql delete from ordwork where ordno = :ordNo;if %subst(sqlstate: 1: 2) > '02'; // SQL error: the message is in the job log (or use GET DIAGNOSTICS)endif;
Top rows per group
Put the cursor in a SELECT and pick Top rows per group (ROW_NUMBER): the group, the order and how many rows — here the last 3 orders of each customer.
SELECT CUSTNO, ORDDATE, AMOUNT FROM APPLIB.ORDERS WHERE STATUS = 'O'
SELECT * FROM ( SELECT X.*, ROW_NUMBER() OVER (PARTITION BY CUSTNO ORDER BY ORDDATE DESC) AS RN FROM ( SELECT CUSTNO, ORDDATE, AMOUNT FROM APPLIB.ORDERS WHERE STATUS = 'O' ) X ) Y WHERE RN <= 3 ORDER BY CUSTNO, RN
Pivot: one column per value
The values of STATUS become columns, the AMOUNT added up for each customer.
SELECT CUSTNO, SUM(CASE WHEN STATUS = 'O' THEN AMOUNT ELSE 0 END) AS "O", SUM(CASE WHEN STATUS = 'C' THEN AMOUNT ELSE 0 END) AS "C", SUM(CASE WHEN STATUS = 'X' THEN AMOUNT ELSE 0 END) AS "X", SUM(AMOUNT) AS TOTAL FROM ( SELECT CUSTNO, ORDDATE, STATUS, AMOUNT FROM APPLIB.ORDERS ) X GROUP BY CUSTNO ORDER BY CUSTNO
Delete duplicates, safely
First the SELECT of the rows that would go, then the DELETE that keeps the first row (lowest RRN) of each EMAIL.
-- 1. The rows that would be deleted (all but the first of each EMAIL):SELECT RRN(A) AS RRN, A.* FROM APPLIB.CUSTOMERS A WHERE RRN(A) > (SELECT MIN(RRN(B)) FROM APPLIB.CUSTOMERS B WHERE B.EMAIL = A.EMAIL);-- 2. Delete them (rows with a null key are kept):DELETE FROM APPLIB.CUSTOMERS A WHERE RRN(A) > (SELECT MIN(RRN(B)) FROM APPLIB.CUSTOMERS B WHERE B.EMAIL = A.EMAIL)
100 rows at a time
Insert a multi-row FETCH: a cursor read into a data structure array, much faster than one FETCH per row.
dcl-ds rows qualified dim(100); // the columns of the SELECT, in the same orderend-ds;dcl-s fetched int(10);dcl-s r int(10);exec sql declare rowsCur cursor for select custno, name from customers for read only;exec sql open rowsCur;dou fetched < %elem(rows); exec sql fetch next from rowsCur for 100 rows into :rows; if sqlcode < 0; leave; endif; exec sql get diagnostics :fetched = row_count; for r = 1 to fetched; // process rows(r) endfor;enddo;exec sql close rowsCur;
A QShell command CL can monitor
With QIBM_QSH_CMD_ESCAPE_MSG set, a failing command sends QSH0005 — the program stops with a clear message instead of carrying on.
ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y) REPLACE(*YES)ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) VALUE(NONE) REPLACE(*YES) /* or FILE=/tmp/qsh.log */QSH CMD('cp /home/app/in.csv /home/app/archive/')MONMSG MSGID(QSH0005 QSH0006 QSH0007) EXEC(DO) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('The QShell command failed') MSGTYPE(*ESCAPE)ENDDO
RPG recipes
(Source: src/rpg/rpgRecipes.ts.)
| Tool | Example |
|---|---|
| Add an SQLSTATE check after every EXEC SQL | if %subst(sqlstate: 1: 2) > '02'; … endif; |
| Insert GET DIAGNOSTICS (message of the last SQL error) | get diagnostics condition 1 :msg = message_text |
| Insert splitting a text into an array | %scan loop (and %split on 7.4 TR) |
| Insert a FOR-EACH loop over an array | for-each item in array; … endfor; (7.4 TR) |
| Insert sorting a data structure array by a subfield | sorta orders(*).total; |
| Insert the first and last day of the month | firstDay / lastDay of a date |
| Insert days, months and years between two dates | %diff(to: from: *days / *months / *years) |
| Insert SND-MSG (job log or escape message) | snd-msg 'text'; (7.4 TR) |
| Insert a multi-row FETCH (100 rows at a time) | fetch next from c for 100 rows into :rows |
| Insert a check that an object exists (SQL) | qsys2.object_statistics(lib, type) where objname = … |
SQL recipes — on the SELECT at the cursor
(Source: src/core/sqlRecipes.ts.)
| Tool | Example |
|---|---|
| Top rows per group (ROW_NUMBER) | ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) <= n |
| Add a running total column | SUM(x) OVER (ORDER BY … ROWS UNBOUNDED PRECEDING) |
| Add the previous row’s value and the change (LAG) | LAG(x) OVER (ORDER BY …), x - LAG(x) |
| Add each row’s percent of the total | x * 100.0 / SUM(x) OVER () |
| Pivot: one column per value | SUM(CASE WHEN col = 'A' THEN x ELSE 0 END) AS A, … |
| Values of a column as one list per group (LISTAGG) | LISTAGG(x, ', ') WITHIN GROUP (ORDER BY x) |
| Rows that differ from another table (EXCEPT) | ONLY IN A / ONLY IN B |
| Delete duplicate rows, keep the first (preview first) | RRN(A) > (SELECT MIN(RRN(B)) …) |
| Missing numbers in a numbered column (gaps) | LEAD(x) OVER (ORDER BY x) > x + 1 |
| Insert a recursive query (parent / child tree) | WITH TREE … UNION ALL … JOIN TREE |
CL recipes
(Source: src/core/clRecipes.ts.)
| Tool | Example |
|---|---|
| Insert a loop over the objects of a library | DSPOBJD … OUTFILE + RCVF (QADSPOBJ) |
| Insert a loop over the members of a file | DSPFD *MBRLIST … OUTFILE + RCVF (QAFDMBRL) |
| Insert a QShell command that stops on failure | QIBM_QSH_CMD_ESCAPE_MSG + QSH + MONMSG QSH0005 |
| Insert a library list change that is put back | RTVJOBA USRLIBL … CHGLIBL … QCMDEXC |
Fixed in 3.3.0
- PDM: work with objects (WRKOBJPDM) showed an empty list with
SQL0104 Token EXISTS not validon some systems (issue #16). The query now uses a LEFT JOIN that every Db2 for i level accepts; the Messages view had the same pattern and is fixed too. - Members opened for display (PDM option 5) are now marked read-only in a way every VS Code version accepts.
Where to find them
| Page | Command palette | Also |
|---|---|---|
| System Health Overview | System Health Overview (Visual) | quick menu |
| Lock Wait Chains | Lock Wait Chains (Visual) | quick menu, a card of the overview |
| Limits Watch | Limits Watch (Visual) | quick menu, a card of the overview |
| Security Scorecard | Security Scorecard (Visual) | quick menu, a card of the overview |
| Security Audit Journal | Security Audit Journal (Visual) | quick menu |
| Disk Units Map | Disk Units Map (Visual) | quick menu, a card of the overview |
| Embedded SQL Statement Statistics | Embedded SQL Statement Statistics… | right-click a library or a program |
| Tasks | Terminal → Run Task… | .vscode/tasks.json |
| System Checks | System Checks (Backups, Sign-on, Security…)… | quick menu |
| System service pages | Network Configuration, Fix Currency, Hardware and Devices… | quick menu, System Checks |
| Workbench actions | Rename Member, Library Actions, Download Members… | right-click in the Libraries and IFS views |
| 124 developer tools | Vanthrex Tools: … | Ctrl+Alt+A in a source |
All the pages only read: nothing is created or changed on the IBM i. The workbench actions change only what you pick (clear and delete ask you to type the name first), and the developer tools only change the source in the editor.