Skip to content

Databases

IBM i (AS/400) Database Management in VS Code

IBM i is a fully integrated platform combining hardware, OS, and a built-in relational database (Db2 for i). DBCode connects through the JT400 JDBC driver (IBM Toolbox for Java), giving you direct access to SQL tables, views, procedures, and more from Visual Studio Code.

The DBCode driver requires a Java runtime on your machine. DBCode downloads the JT400 driver itself the first time you connect.

To connect to IBM i in DBCode:

  1. Open the DBCode extension and select Add Connection.
  2. Select IBM i as the database type.
  3. Enter the host name or IP address of your IBM i system.
  4. Enter your IBM i user profile and password.
  5. Optionally, turn on Enable SSL/TLS. DBCode then encrypts the connection and verifies the server’s certificate against the trusted certificates of the Java runtime that runs the driver.
  6. Optionally, configure an SSH tunnel.
  7. Save the connection and open it from the DB Explorer.

A direct connection finds the database server through the IBM i port mapper, so it reaches the system on ports 449 (port mapper), 8476 (sign-on server) and 8471 (database server), or 9476 and 9471 with SSL/TLS.

The Port in the connection settings is the database server port: 8471, or 9471 with SSL/TLS. DBCode uses it when you connect through an SSH tunnel: the tunnel forwards that port and DBCode connects straight to it. A new connection fills in 9471 when you turn on SSL/TLS.

DBCode connects with SQL naming, so you qualify objects as LIBRARY.TABLE, and the default schema is the one named after your user profile (set another with SET SCHEMA in the connection’s post-connection SQL). Libraries appear as schemas in the DB Explorer.

FeatureSupport
Run SQLYes
Visual query builderYes
Browse libraries (schemas)Yes, loaded on demand
Tables and physical filesColumns, primary and unique keys, foreign keys (both directions), indexes, check constraints and triggers
Views and logical filesYes, with their columns
AliasesYes, including aliases to tables on another system (DDM), whose columns load when you expand them
Procedures and functionsYes, with their parameters
SequencesYes
Editable DDLViews, procedures, functions and sequences
Insert, update, and delete rowsYes
Edit grid cellsYes
Run queries in the selected schemaYes
TransactionsYes, with commit and rollback, on journaled tables (tables in a library made with CREATE SCHEMA are journaled)
Read-only connectionsYes
Server monitoringDatabase server jobs and the SQL each one is running (with Cancel Query), and system status
Table row counts and sizesYes

DATE, TIME and TIMESTAMP values show as the server stores them, whatever time zone your machine is in, including every fractional digit of a TIMESTAMP(12) column. DBCode’s date and time formatting settings control how values are displayed. Connections use ISO date format (yyyy-mm-dd), so write date literals with four-digit years, such as '2024-03-10'. Large BIGINT, DECIMAL and DECFLOAT values keep every digit, and NVARCHAR and VARCHAR ... CCSID 1208 columns keep any Unicode text, emoji included.

A read-only connection rejects INSERT, UPDATE, DELETE, MERGE and DDL statements, in every editor and notebook as well as the DB Explorer. Procedure calls (CALL) and data-change queries such as SELECT ... FROM FINAL TABLE (INSERT ...) still run, so to make sure a connection can only read, connect with an IBM i user profile that has read authority only. The connection’s post-connection SQL runs before read-only applies, so it can set the schema or path.

Cancel Query in the monitoring panel ends the statement a database server job is running, through QSYS2.CANCEL_SQL. Cancelling another user’s statement needs *JOBCTL special authority.

CREATE TABLE MYLIB.ACCOUNTS (
ID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR(50) CCSID 1208,
BALANCE DECIMAL(15, 2),
CREATED TIMESTAMP
);
INSERT INTO MYLIB.ACCOUNTS (ID, NAME, BALANCE, CREATED)
VALUES (1, 'Ada', 1000.00, '2026-08-29 10:30:00');
SELECT * FROM MYLIB.ACCOUNTS FETCH FIRST 100 ROWS ONLY;

For more information about IBM i, check out IBM i.