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.

  1. System Health Overview
  2. Lock Wait Chains
  3. Limits Watch
  4. Security Scorecard
  5. Security Audit Journal
  6. Disk Units Map
  7. Embedded SQL Statement Statistics
  8. VS Code tasks for IBM i
  9. System Checks
  10. System service pages
  11. Workbench actions
  12. 50 more developer tools
    1. Text — any source, on the selection
    2. SQL — on the statement at the cursor
    3. RPG
    4. CL
  13. 50 more developer tools, second batch
    1. RPG snippets
    2. SQL builders — on the statement at the cursor
    3. CL snippets
    4. IBM i converters — any source
  14. 24 more developer tools, third batch
    1. RPG recipes
    2. SQL recipes — on the SELECT at the cursor
    3. CL recipes
  15. Fixed in 3.3.0
  16. 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.

Example with sample data
● Something needs attention now. DEV400 · read at 09:30
Disk spaceLook at it
77.5 %
  • 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).
Limits close to the maximumAct now
2
  • Maximum number of rows in a table partition (APPLIB/ORDHIST): 90.9 %
  • Maximum number of locks held by a job: 82 %
Jobs waiting for a lockAct now
3
  • 2 object(s) or row(s) contended.
  • Head blocker 412233/BATCH/NIGHTLY blocks 3 job(s).
Jobs waiting for a reply (MSGW)Act now
1
  • 412377/BATCH/INVOICES
Security scoreOK
86 (B)
  • 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.

Example with sample data
👑 412233/BATCH/NIGHTLYholds *SHRUPD
→ APPLIB/ORDERS *FILE (ORDERS) row 1042 ←
412301/QUSER/QZDASOINITwaits *SHRUPD412310/ANNA/QPADEV0007waits *SHRUPD
412301/QUSER/QZDASOINITholds *EXCL
→ APPLIB/ORDCTL *DTAARA ←
412355/MARC/QPADEV0012waits *EXCL
👑 Head blocker: 412233/BATCH/NIGHTLY blocks 3 jobs (directly or through others)3 jobs waiting

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.

Example with sample data
Maximum number of rows in a table partitionAPPLIB/ORDHIST
90.9 %
Maximum number of locks held by a job412233/BATCH/NIGHTLY
82 %
Maximum number of objects in an IFS directory/home/app/logs
64 %
Maximum number of members in a fileAPPLIB/QRPGLESRC
27.5 %

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.

Example with sample data
86Grade B
CheckValueCurrentRecommended
RiskPassword expirationQPWDEXPITV*NOMAX90 days or less
ReviewPublic authority to new objectsQCRTAUT*CHANGE*USE or *EXCLUDE
ReviewInactive job time-outQINACTITV*NONE60 minutes or less
ReviewRestore of security-sensitive objectsQALWOBJRST*ALL*NONE outside install windows
OKSecurity levelQSECURITY4040 or 50
OKPassword levelQPWDLVL32 or 3
OKMinimum password lengthQPWDMINLEN88 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.

Example with sample data
10:0013:0016:0019:0022:0001:0004:0007:00PW Sign-on or password failurePW 2026-11-05 10:00 — 0PW 2026-11-05 11:00 — 0PW 2026-11-05 12:00 — 0PW 2026-11-05 13:00 — 0PW 2026-11-05 14:00 — 0PW 2026-11-05 15:00 — 0PW 2026-11-05 16:00 — 0PW 2026-11-05 17:00 — 0PW 2026-11-05 18:00 — 0PW 2026-11-05 19:00 — 0PW 2026-11-05 20:00 — 1PW 2026-11-05 21:00 — 0PW 2026-11-05 22:00 — 0PW 2026-11-05 23:00 — 1PW 2026-11-06 00:00 — 0PW 2026-11-06 01:00 — 0PW 2026-11-06 02:00 — 0PW 2026-11-06 03:00 — 34PW 2026-11-06 04:00 — 29PW 2026-11-06 05:00 — 0PW 2026-11-06 06:00 — 0PW 2026-11-06 07:00 — 1PW 2026-11-06 08:00 — 0PW 2026-11-06 09:00 — 066AF Authority failureAF 2026-11-05 10:00 — 0AF 2026-11-05 11:00 — 0AF 2026-11-05 12:00 — 2AF 2026-11-05 13:00 — 2AF 2026-11-05 14:00 — 0AF 2026-11-05 15:00 — 0AF 2026-11-05 16:00 — 0AF 2026-11-05 17:00 — 0AF 2026-11-05 18:00 — 0AF 2026-11-05 19:00 — 3AF 2026-11-05 20:00 — 0AF 2026-11-05 21:00 — 0AF 2026-11-05 22:00 — 0AF 2026-11-05 23:00 — 0AF 2026-11-06 00:00 — 0AF 2026-11-06 01:00 — 2AF 2026-11-06 02:00 — 0AF 2026-11-06 03:00 — 3AF 2026-11-06 04:00 — 0AF 2026-11-06 05:00 — 1AF 2026-11-06 06:00 — 0AF 2026-11-06 07:00 — 0AF 2026-11-06 08:00 — 0AF 2026-11-06 09:00 — 316CA Authority changeCA 2026-11-05 10:00 — 0CA 2026-11-05 11:00 — 0CA 2026-11-05 12:00 — 0CA 2026-11-05 13:00 — 0CA 2026-11-05 14:00 — 0CA 2026-11-05 15:00 — 0CA 2026-11-05 16:00 — 0CA 2026-11-05 17:00 — 0CA 2026-11-05 18:00 — 0CA 2026-11-05 19:00 — 0CA 2026-11-05 20:00 — 0CA 2026-11-05 21:00 — 0CA 2026-11-05 22:00 — 0CA 2026-11-05 23:00 — 0CA 2026-11-06 00:00 — 0CA 2026-11-06 01:00 — 0CA 2026-11-06 02:00 — 0CA 2026-11-06 03:00 — 0CA 2026-11-06 04:00 — 0CA 2026-11-06 05:00 — 0CA 2026-11-06 06:00 — 0CA 2026-11-06 07:00 — 0CA 2026-11-06 08:00 — 6CA 2026-11-06 09:00 — 612DO Object deletedDO 2026-11-05 10:00 — 2DO 2026-11-05 11:00 — 0DO 2026-11-05 12:00 — 2DO 2026-11-05 13:00 — 0DO 2026-11-05 14:00 — 2DO 2026-11-05 15:00 — 0DO 2026-11-05 16:00 — 0DO 2026-11-05 17:00 — 0DO 2026-11-05 18:00 — 0DO 2026-11-05 19:00 — 0DO 2026-11-05 20:00 — 0DO 2026-11-05 21:00 — 0DO 2026-11-05 22:00 — 0DO 2026-11-05 23:00 — 0DO 2026-11-06 00:00 — 0DO 2026-11-06 01:00 — 2DO 2026-11-06 02:00 — 0DO 2026-11-06 03:00 — 0DO 2026-11-06 04:00 — 2DO 2026-11-06 05:00 — 0DO 2026-11-06 06:00 — 0DO 2026-11-06 07:00 — 0DO 2026-11-06 08:00 — 0DO 2026-11-06 09:00 — 010CP User profile changedCP 2026-11-05 10:00 — 0CP 2026-11-05 11:00 — 0CP 2026-11-05 12:00 — 0CP 2026-11-05 13:00 — 0CP 2026-11-05 14:00 — 0CP 2026-11-05 15:00 — 0CP 2026-11-05 16:00 — 0CP 2026-11-05 17:00 — 0CP 2026-11-05 18:00 — 0CP 2026-11-05 19:00 — 0CP 2026-11-05 20:00 — 0CP 2026-11-05 21:00 — 0CP 2026-11-05 22:00 — 0CP 2026-11-05 23:00 — 0CP 2026-11-06 00:00 — 0CP 2026-11-06 01:00 — 0CP 2026-11-06 02:00 — 0CP 2026-11-06 03:00 — 0CP 2026-11-06 04:00 — 0CP 2026-11-06 05:00 — 0CP 2026-11-06 06:00 — 0CP 2026-11-06 07:00 — 0CP 2026-11-06 08:00 — 3CP 2026-11-06 09:00 — 03SV System value changedSV 2026-11-05 10:00 — 0SV 2026-11-05 11:00 — 0SV 2026-11-05 12:00 — 0SV 2026-11-05 13:00 — 0SV 2026-11-05 14:00 — 0SV 2026-11-05 15:00 — 0SV 2026-11-05 16:00 — 0SV 2026-11-05 17:00 — 0SV 2026-11-05 18:00 — 0SV 2026-11-05 19:00 — 0SV 2026-11-05 20:00 — 0SV 2026-11-05 21:00 — 0SV 2026-11-05 22:00 — 0SV 2026-11-05 23:00 — 0SV 2026-11-06 00:00 — 0SV 2026-11-06 01:00 — 0SV 2026-11-06 02:00 — 1SV 2026-11-06 03:00 — 0SV 2026-11-06 04:00 — 0SV 2026-11-06 05:00 — 0SV 2026-11-06 06:00 — 0SV 2026-11-06 07:00 — 0SV 2026-11-06 08:00 — 0SV 2026-11-06 09:00 — 01JS Job start, end or changeJS 2026-11-05 10:00 — 36JS 2026-11-05 11:00 — 67JS 2026-11-05 12:00 — 65JS 2026-11-05 13:00 — 62JS 2026-11-05 14:00 — 34JS 2026-11-05 15:00 — 52JS 2026-11-05 16:00 — 83JS 2026-11-05 17:00 — 47JS 2026-11-05 18:00 — 55JS 2026-11-05 19:00 — 51JS 2026-11-05 20:00 — 65JS 2026-11-05 21:00 — 71JS 2026-11-05 22:00 — 81JS 2026-11-05 23:00 — 64JS 2026-11-06 00:00 — 86JS 2026-11-06 01:00 — 73JS 2026-11-06 02:00 — 81JS 2026-11-06 03:00 — 53JS 2026-11-06 04:00 — 61JS 2026-11-06 05:00 — 78JS 2026-11-06 06:00 — 53JS 2026-11-06 07:00 — 41JS 2026-11-06 08:00 — 43JS 2026-11-06 09:00 — 451447CD Command runCD 2026-11-05 10:00 — 32CD 2026-11-05 11:00 — 34CD 2026-11-05 12:00 — 38CD 2026-11-05 13:00 — 38CD 2026-11-05 14:00 — 22CD 2026-11-05 15:00 — 54CD 2026-11-05 16:00 — 32CD 2026-11-05 17:00 — 50CD 2026-11-05 18:00 — 55CD 2026-11-05 19:00 — 27CD 2026-11-05 20:00 — 53CD 2026-11-05 21:00 — 47CD 2026-11-05 22:00 — 24CD 2026-11-05 23:00 — 44CD 2026-11-06 00:00 — 42CD 2026-11-06 01:00 — 36CD 2026-11-06 02:00 — 54CD 2026-11-06 03:00 — 20CD 2026-11-06 04:00 — 26CD 2026-11-06 05:00 — 44CD 2026-11-06 06:00 — 56CD 2026-11-06 07:00 — 41CD 2026-11-06 08:00 — 41CD 2026-11-06 09:00 — 39949CO Object createdCO 2026-11-05 10:00 — 11CO 2026-11-05 11:00 — 0CO 2026-11-05 12:00 — 12CO 2026-11-05 13:00 — 10CO 2026-11-05 14:00 — 13CO 2026-11-05 15:00 — 0CO 2026-11-05 16:00 — 0CO 2026-11-05 17:00 — 8CO 2026-11-05 18:00 — 10CO 2026-11-05 19:00 — 0CO 2026-11-05 20:00 — 1CO 2026-11-05 21:00 — 2CO 2026-11-05 22:00 — 0CO 2026-11-05 23:00 — 0CO 2026-11-06 00:00 — 6CO 2026-11-06 01:00 — 1CO 2026-11-06 02:00 — 5CO 2026-11-06 03:00 — 0CO 2026-11-06 04:00 — 1CO 2026-11-06 05:00 — 0CO 2026-11-06 06:00 — 0CO 2026-11-06 07:00 — 13CO 2026-11-06 08:00 — 0CO 2026-11-06 09:00 — 194
Failures (82)Security changes (26)Other activity (2490)

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.

Example with sample data
ASP 1 · 75 % used of 4.00 TB · uneven by 16 points
71%
1
73%
2
86%
3
70%
4
ASP 2 · 77.5 % used of 2.00 TB · uneven by 27 points
91%
5 ⚡
64%
6 ⚡
Can: % usedBar under it: % busy80 % used / 40 % busy90 % used / 60 % busy

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.

Example with sample data
ProgramStmtRunsTotal msAvg msRebuildsHint
ORDLIST41218,240912,000509800Access plan rebuilt on most runs
INV100884,120205,00049.762
ORDSRV153098,00098,00010
PURGE60000Never 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.

Backup freshnesssample data
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.

Sign-on problemsQMAXSIGN = 3
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.

Job descriptions that name a usersample data
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).

Before
orders.sql
CREATE TABLE APP.ORDERS (  ORDER_NO DECIMAL(9, 0) NOT NULL,  CUSTNO DECIMAL(7, 0),  STATUS CHAR(1),  PRIMARY KEY (ORDER_NO));
After
orders.sql
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.

orders.sql
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.

Before
report.sql
SELECT * FROM APP.ORDERS WHERE CUSTNO = ? AND STATUS = 'O' ORDER BY ORDDATE DESC
After
report.sql
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.

Before
ORDERS.rpgle
dcl-s custName char(30);dcl-s total packed(11:2);dcl-s found ind;dcl-s note varchar(100);
After
ORDERS.rpgle
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.

Before
ORDERS.rpgle
setll (custNo) orders;reade (custNo) orders;dow not %eof(orders);  // process the record  reade (custNo) orders;enddo;
After
ORDERS.rpgle
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 with its MONMSG

A CL check that stops with a clear message when the object is missing.

NIGHTLY.clle
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

Text to EBCDIC hex

For a hex literal in RPG, CL or SQL, or to read one back: CCSID 37.

Selection
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.

Before
orders.sql
SELECT ORDNO, CUSTNO, TOTAL  FROM APPLIB.ORDERS WHERE STATUS = 'O'
After
orders.sql
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.

Before
cleanup.sql
DELETE FROM APPLIB.ORDERS WHERE STATUS = 'X'   AND ORDDATE < CURRENT DATE - 1 YEAR
After
cleanup.sql
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.

Before
orders.sql
CREATE TABLE APPLIB.ORDERS (  ORDNO INTEGER NOT NULL PRIMARY KEY,  TOTAL DECIMAL(11, 2))
After
orders.sql
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.

orders.sqlQSYS2.OBJECT_LOCK_INFO
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.

Before
ORDERS.rpgle
CPYF FROMFILE(APPLIB/ORDERS) TOFILE(QTEMP/ORDERS) CRTFILE(*YES)
After
ORDERS.rpgle
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).

Before
ORDERS.rpgle
dcl-s custNo packed(7:0);dcl-s name char(30);dcl-s created date;
After
ORDERS.rpgle
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.

NIGHTLY.clle
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

Submit to batch

Select a command and pick Submit the selected command to batch (SBMJOB).

Before
NIGHTLY.clle
CALL PGM(APPLIB/NIGHTLY) PARM('2026')
After
NIGHTLY.clle
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.

Quick pickReplace with which format?
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.

Before
ORDERS.rpgle
exec sql update orders  set status = :st  where ordno = :ordNo;exec sql delete from ordwork where ordno = :ordNo;
After
ORDERS.rpgle
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.

Before
orders.sql
SELECT CUSTNO, ORDDATE, AMOUNT  FROM APPLIB.ORDERS WHERE STATUS = 'O'
After
orders.sql
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.

orders.sql
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.

cleanup.sql
-- 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.

CUSTLIST.rpgle
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.

ARCHIVE.clle
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 valid on 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.