SQL is a standard language for storing, manipulating and retrieving data in relational databases.
Hi,
We’re launching an Early Access Program for an upcoming Table Partitioning feature that will help IRIS customers manage very large tables, and distribute row data and associated indices across databases and storage tiers. Table Partitioning cuts deep into the core of IRIS relational data management, so we want to make sure we get things right through working with a few engaged customers who can provide feedback on the initial deliverables, and fine-tune as needed.
I want to extract data from IRIS (Health Share Provider Directory) and write the data out to a file that I can use within an external system. Using EnsLib.SQL.Service.GenericService do I need to supply Credentials if I am querying IRIS locally? I have always used JDBC to connect externally when creating Services and Operations to read/write SQL data from external Databases, but never internally. Normally I would just use sql or code to pull the data within a Process or DTC. But in this case, I want the Service to drive the workflow, and query the data from Local Tables
.png)
Apache Airflow is the leading open-source platform to programmatically author, schedule, and monitor data pipelines and workflows using Python. Workflows are defined as code (DAGs), making them version-controlled, testable, and reusable. With a rich UI, 100+ built-in operators, dynamic task generation, and native support for cloud providers, Airflow powers ETL/ELT, ML pipelines, and batch jobs at companies like Airbnb, Netflix, and Spotify.
Hey Community!
We're happy to share a new video from our InterSystems Developers YouTube:
Hello Community! 👋
Welcome to the second part of the IRIS IO Utility series. This extension represents my submission for the InterSystems "Bringing Ideas to Reality" Contest 2025 and offers you an intuitive and powerful interface for importing and exporting data directly inside VS Code.
If you find this extension useful, please consider voting for me at the contest!
In the previous article, we covered:
Hi everyone. Long time no see. Again, I would like to share what I am studying recently Create Foreign Tables Using SQL via JDBC.
Since I am learning about the IRIS BI at the same time, so I started with this Sample-BI docker environment
https://github.com/intersystems/Samples-BI
After starting the environment, login to the management portal http://localhost:52773/csp/sys/UtilHome.csp with the user Superuser.
Switch the namespace to IRISAPP.
😀 We will find out that all the sample data are stored in this namespace IRISAPP. Let's find a simple table for testing the Foreign Table feature 🤣
Supply Chain refers to a set of processes and activities performed by the company's business areas and its suppliers and partners (stakeholders), from the acquisition of raw materials, through production, to delivery to the end consumer. It can be better managed using SCM solutions with the orchestration of the InterSystems IRIS:
I'm pleased to announce the publication of gj :: dataLoader, a new VS Code extension that simplifies the task of loading data from local CSV files into SQL tables on your InterSystems IRIS servers.
Here's an introductory video:
gj :: dataLoader is now available on Marketplace for direct installation into VS Code. It is my entry in the "Bringing Ideas to Reality" Contest 2025 and implements this idea - https://ideas.intersystems.com/ideas/DPI-I-667
Feedback is welcome.
Modern SQL engines are enormously complex pieces of software. Even when they appear stable and mature, subtle bugs can hide in their optimizers, type systems, predicate evaluation, or execution layers. These bugs rarely announce themselves loudly. Instead, they quietly produce incorrect results, behave inconsistently, or fail abruptly under specific combinations of SQL constructs.
This is precisely why tools like SQLancer exist. SQLancer automatically generates SQL queries and uses logical “oracles” to detect when a database behaves incorrectly. It has revealed hundreds of real bugs in widely used systems such as MySQL, PostgreSQL, SQLite, and DuckDB.
With this in mind, I attempted to bring SQLancer to InterSystems IRIS, starting with the NOREC oracle — a powerful method for detecting optimizer correctness issues. The journey, however, uncovered not just potential SQL correctness problems, but also a surprising number of driver-level and server-level failures that prevented SQLancer from running at full strength.
This article summarizes why SQLancer is important, how the NOREC oracle works, and what unexpected findings appeared while testing IRIS.
Auditing is a critical capability ensuring the security of your server, and for quite a while ago we have included the ability to audit SQL statements run on the server.
With v2024.3 out already we're providing finer-grained options for defining these events to audit.
How to set up RAG for OpenAI agents using IRIS Vector DB in Python
In this article, I’ll walk you through an example of using InterSystems IRIS Vector DB to store embeddings and integrate them with an OpenAI agent.
To demonstrate this, we’ll create an OpenAI agent with knowledge of InterSystems technology. We’ll achieve this by storing embeddings of some InterSystems documentation in IRIS and then using IRIS vector search to retrieve relevant content—enabling a Retrieval-Augmented Generation (RAG) workflow.
Hello Developers! 👋
I’m excited to introduce IRIS IO Utility, my submission for the InterSystems "Bringing Ideas to Reality" Contest 2025. This VS Code extension provides you an intuitive and powerful interface for importing and exporting data without leaving your IDE. The extension implements and expand this idea submitted on the Ideas Portal turning a common developer need into a real tool: seamless IO operations with any IRIS instance directly inside VS Code.
Hey Community!
We're happy to share a new video from our InterSystems Developers YouTube:
⏯ Smarter Table Statistics @ Ready 2025
Could someone explain how and why a HL7 ACK be showing up as a Orphaned message when I run the following SQL...
SELECT HL7.ID,HL7.DocType,HL7.Envelope,HL7.Identifier,HL7.MessageTypeCategory,HL7.Name,HL7.OriginalDocId,HL7.ParentId, HL7.TimeCreated
FROM EnsLib_HL7.Message HL7
LEFTJOIN Ens.MessageHeader hdr
ON HL7.Id=hdr.MessageBodyId
WHERE hdr.MessageBodyId ISNULLI am trying to find the problem code that is causing the Orphaned messages, and an ACK showing up seems kind of Odd. While we do have Archive IO/Trace on, and Index NOT OK's set why would they show up as Orphaned messages?
InterSystems IRIS provides extensive configurable security options, yet many developers primarily use roles and resources to secure entire tables or routines. Today, we will delve deeper. We can also secure individual columns and rows separately, but these two mechanisms operate very differently. Let's begin with the columns.
Column Security
For testing and demonstration, we will keep our table structure concise and straightforward. We have a table called "Person" in the USER namespace that contains an ID column, a date of birth column (DOB), first name, and last name.
Welcome to the monthly recap of updates and releases to VS Code and InterSystems-related extensions that are relevant to IRIS developers.
We'll break down the updates that are relevant to InterSystems developers with tips on how they can be applied to your day-to-day projects.
Don't forget, if you're migrating from InterSystems Studio to VS Code, or want to deepen your knowledge, check out the VS Code training courses from George James Software: georgejames.com/vscode-training.
VS Code version 1.106
We're excited to announce the Early Access Program for IntegratedML Custom Models, a powerful new capability coming in IRIS 2026.1!
The Load Data utility it is an excellent tool to load data from CSV/TXT files into an IRIS SQL Table, but it is required send the target file to the IRIS server and write the Load Data sentence to ingest the file content. Now it is possible select a file in VSCode, set the table destination and submit the request. The vscode-load-data utility will send the file to the IRIS server and run the Load Data command to you! Very simple:
.png)
Hey Community!
We're happy to share a new video from our InterSystems Developers YouTube:
When using standard SQL or the object layer in InterSystems IRIS, metadata consistency is usually maintained through built-in validation and type enforcement. However, legacy systems that bypass these layers—directly accessing globals—can introduce subtle and serious inconsistencies.
In Part 1, we explored how window functions operate. We learned the logic behind PARTITION BY, ORDER BY, and such functions as ROW_NUMBER() and RANK(). Now, in Part 2, let's delve into more window functions with practical examples.
These functions compute an aggregate (e.g., sum, average, min, max, count, etc.) over the defined window frame but don’t collapse rows.
Each row remains visible, augmented with aggregated values for its partition.
Supported functions include the following:
Parallel query hinting boosts certain query performances on multi-processor systems via parallel processing. The SQL optimizer determines when this is beneficial. On single-processor systems, this hint has no effect.
Parallel processing can be managed by:
Setting the auto parallel option system-wide.
Using the %PARALLEL keyword in the FROM clause of specific queries.
%PARALLEL is ignored when it applied to:
Embedded SQL is a tool that allows us to execute SQL statements in Caché Object Script. For example, to select the name of a person with a particular SSN from the Sample.Person class we can do the following:
&SQL(SELECT Nameinto:tNameFromSample.PersonWhereSSN = :tSSN )
.png)
A simple way to connect, explore, and debug InterSytems Cloud Services over JDBC with sqlline.
Hi,
When we open a terminal in IRIS, we are entering the ObjectScript shell. Within this shell, we can execute IRIS commands, such as:
In other words, the ObjectScript command is executed in the current shell. But it's always good to remember that IRIS has other shells
One very interesting aspect is shortcuts. We can access these shells through their calls or via shortcuts, as shown in the table below:
Hey Community!
We're happy to share a new video from our InterSystems Developers YouTube:
⏯ Optimizing Parallel Aggregation Using Shared Globals @ Ready 2025
In today’s healthcare data landscape, FHIR has become the standard for structured clinical data exchange. However, while FHIR excels at interoperability, its JSON format makes analytics challenging—including FHIR QuestionnaireResponse.
This project demonstrates how to transform FHIR QuestionnaireResponse data from nested JSON into relational SQL tables and vector embeddings. By integrating the InterSystems IRIS FHIR SQL Builder and Vector Search, we unlock the semantic meaning behind patient answers.
Hi everyone,
I'm getting prepared to take the following certification exam: "InterSystems IRIS Development Professional".
Can you give some advice on how to prepare (aside from the official course page: https://www.intersystems.com/certifications/intersystems-iris-development-professional/ )?
Do you have examples of quiz questions that simulate the real exam or any material that helped you getting prepared?
Thanks you in advance,
Arber
Hey Community!
We're happy to share a new video from our InterSystems Developers YouTube:
⏯ Foreign Tables In 2025.2 @ Ready 2025
Hello!
I'm trying to create some foreign tables to a PostgreSQL database. In some cases, columns with certain datatypes cannot be consumed by IRIS and the following error is thrown:
[SQLCODE: <-237>:<Schema import for foreign table did not return column metadata>]
[%msg: <Unkown data type returned by external database>]
For example: serial4 typed ID columns are typical examples. Is it possible, what's the best way of resolving these datatypes, which- seemingly- don't have proper JDBC metadata mappings?