Databases
IBM i (AS/400) Database Management in VS Code
Overview
Section titled “Overview”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.
Connecting
Section titled “Connecting”To connect to IBM i in DBCode:
- Open the DBCode extension and select Add Connection.
- Select IBM i as the database type.
- Enter the host name or IP address of your IBM i system.
- Enter your IBM i user profile and password.
- 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.
- Optionally, configure an SSH tunnel.
- 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.
Supported features
Section titled “Supported features”| Feature | Support |
|---|---|
| Run SQL | Yes |
| Visual query builder | Yes |
| Browse libraries (schemas) | Yes, loaded on demand |
| Tables and physical files | Columns, primary and unique keys, foreign keys (both directions), indexes, check constraints and triggers |
| Views and logical files | Yes, with their columns |
| Aliases | Yes, including aliases to tables on another system (DDM), whose columns load when you expand them |
| Procedures and functions | Yes, with their parameters |
| Sequences | Yes |
| Editable DDL | Views, procedures, functions and sequences |
| Insert, update, and delete rows | Yes |
| Edit grid cells | Yes |
| Run queries in the selected schema | Yes |
| Transactions | Yes, with commit and rollback, on journaled tables (tables in a library made with CREATE SCHEMA are journaled) |
| Read-only connections | Yes |
| Server monitoring | Database server jobs and the SQL each one is running (with Cancel Query), and system status |
| Table row counts and sizes | Yes |
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.
SQL examples
Section titled “SQL examples”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.