SQL Index Adviser - For IBMi
A VS Code extension that gives Db2 for i index recommendations without executing your SQL queries against data.
Uses a three-layer hybrid approach:
| Layer |
Connection needed? |
Source |
| ⚙️ Static Heuristics |
❌ None |
Built-in rule engine |
| 🗄️ EXPLAIN / SYSIXADV |
✅ IBM i (optimizer only — no data scan) |
Code for IBM i |
| 🤖 Claude AI |
❌ None (API key needed) |
Anthropic Claude |
Requirements
- Code for IBM i extension (for EXPLAIN layer)
- An active IBM i connection (for EXPLAIN layer)
- Anthropic API key (for AI layer — optional)
Quick Start
- Install this extension and Code for IBM i
- Connect to your IBM i system via Code for IBM i
- Open any
.sql file
- Press Ctrl+Shift+I (Cmd+Shift+I on Mac) or right-click → SQL Index Adviser: Analyse SQL & Suggest Indexes
- The Index Adviser Panel opens with recommendations from all three layers
How EXPLAIN Mode Works
The EXPLAIN layer uses IBM i's built-in optimizer analysis:
SET CURRENT EXPLAIN MODE = EXPLAIN;
-- your query is sent here
-- the optimizer analyses it but reads NO DATA
SET CURRENT EXPLAIN MODE = NO;
-- results are read from:
SELECT * FROM QSYS2.SYSIXADV WHERE LAST_ADVISED >= CURRENT_TIMESTAMP - 5 MINUTES;
This means:
- ✅ The query optimizer runs and identifies missing indexes
- ✅ No rows are scanned from your application tables
- ✅ Works on massive tables without any performance impact
- ✅ Uses the actual Db2 for i cardinality statistics for accurate advice
Setting Up Claude AI
The AI layer sends your SQL (and optional table DDL if connected to IBM i) to Claude for deeper, IBM i-specific index recommendations — composite indexes, Encoded Vector Indexes (EVI), sparse indexes, and patterns the static rules can't detect.
No IBM i connection is required for the AI layer.
Step 1 — Get an Anthropic API key
Sign up at console.anthropic.com, create an API key (begins with sk-ant-…), and copy it.
Step 2 — Save the key in VS Code
Open the Command Palette (Ctrl+Shift+P) and run:
SQL Index Adviser: Set Anthropic API Key
Paste your key into the password prompt and press Enter. The key is stored in the OS credential store (Windows Credential Manager / macOS Keychain) — it is never written to settings.json or any file on disk.
To remove the key, run the same command and clear the input field.
Step 3 — Run analysis as normal
Press Ctrl+Shift+I on any .sql file. Claude AI runs automatically as part of the analysis and its findings appear in the AI tab of the adviser panel.
Cost note: Each analysis uses approximately 1 500 tokens of Anthropic API credits.
Configuration
Open Settings → search SQL Index Adviser - For IBMi:
| Setting |
Default |
Description |
sqlIndexAdviser.enableHeuristics |
true |
Static rule-based analysis |
sqlIndexAdviser.enableExplain |
true |
EXPLAIN via Code for IBM i |
sqlIndexAdviser.enableAI |
true |
Claude AI recommendations |
sqlIndexAdviser.explainSchema |
"QTEMP" |
Schema for EXPLAIN results |
sqlIndexAdviser.autoAnalyseOnSave |
false |
Auto-analyse on save |
sqlIndexAdviser.schemaContext |
[] |
Schema names for AI context |
What the Heuristic Engine Detects
| Rule |
ID |
Description |
| WHERE column needs index |
H002 |
Equality/range predicate columns |
| ORDER BY without index |
H003 |
Sort columns not covered by index |
| GROUP BY without index |
H004 |
Grouping columns need index |
| Function on column |
H005 |
YEAR(COL), UPPER(COL) etc. prevent index use |
| Leading wildcard LIKE |
H006 |
LIKE '%...' forces full scan |
| OR across columns |
H007 |
May need UNION or separate indexes |
| SELECT * |
H008 |
Prevents covering index optimisation |
| Implicit type conversion |
H009 |
Character column vs. numeric literal |
| Multi-table no predicates |
H001 |
Possible Cartesian product |
Commands
| Command |
Shortcut |
Description |
| SQL Index Adviser: Analyse SQL & Suggest Indexes |
Ctrl+Shift+I |
Analyse selection or whole file |
| SQL Index Adviser: Analyse Entire SQL File |
— |
Always analyses full file |
| SQL Index Adviser: Run EXPLAIN (no data scan) |
— |
Forces EXPLAIN layer |
| SQL Index Adviser: Open Index Adviser Panel |
— |
Re-open last results |
| SQL Index Adviser: Clear Index Advice |
— |
Remove all diagnostics |
| SQL Index Adviser: Set Anthropic API Key |
— |
Save or remove your API key (stored in OS credential store) |
Building
npm install
npm run compile
# or watch mode:
npm run watch
Press F5 in VS Code to launch the extension in a new Extension Development Host window.
Disclaimer
This extension is an independent tool and is not affiliated with or endorsed by IBM.
License
MIT
