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.

Three Steps to Build It

1. Design and Collect the Questionnaire

Start by designing a FHIR

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?

Of course, explicitely defining columns and types as part of the CREATE FOREIGN TABLE

Hi Community, 
In this article, we will explore the concepts of Dynamic SQL and Embedded SQL within the context of InterSystems IRIS, provide practical examples, and examine their differences to help you understand how to leverage them in your applications.

InterSystems SQL provides a full set of standard relational features, including the ability to define table schema, execute queries, and define and execute stored procedures. You can execute InterSystems SQL interactively from the Management Portal or programmatically using a SQL shell interface. Embedded SQL enables you to embed SQL statements in your ObjectScript code, while Dynamic SQL enables you to execute dynamic SQL statements from ObjectScript at runtime. While static SQL queries offer predictable performance, dynamic and embedded SQL offer flexibility and integration, respectively.

Hey Community!

We're happy to share a new video from our InterSystems Developers YouTube:

⏯  Document Server: Improving Performance While Reducing Stored Data @ Ready 2025
 

Window functions in InterSystems IRIS let you perform powerful analytics — like running totals, rankings, and moving averages — directly in SQL.
They operate over a "window" of rows related to the current row, without collapsing results like GROUP BY.
This means you can write cleaner, faster, and more maintainable queries — no loops, no joins, no temp tables.

In this article let's understand the mechanics of window functions by addressing some common data analisys tasks.

Introduction to SQL Window Functions in InterSystems IRIS

SQL window functions are a powerful tool for data analysis.
They

So, you checked your server and saw that IRISTEMP is growing too much. There's no need to panic. Let’s investigate the issue before your storage runs out.

Step 1: Confirm the IRISTEMP Growth Issue

Before assuming IRISTEMP is the problem, let’s check its actual size.

Check the Free Space

Run the following command in the IRIS terminal:

%SYS>do ^%FREECNT

When prompted, enter:

Database directory to show free space for (*=All)? /<your_iris_directory>/mgr/iristemp/

If the output shows very low free space, IRISTEMP is filling up your storage like an overstuffed closet. But if the free space is fine, yet

High-Performance Message Searching in Health Connect

The Problem

Have you ever tried to do a search in Message Viewer on a busy interface and had the query time out? This can become quite a problem as the amount of data increases. For context, the instance of Health Connect I am working with does roughly 155 million Message Headers per day with 21 day message retention. To try and help with search performance, we extended the built-in SearchTable with commonly used fields in hopes that indexing these fields would result in faster query times. Despite this, we still couldn't get some of these queries to finish at all.

Hello all,

I'm trying to build a cube based on a linked table but seems that IRIS is not able to do it :O

Long story short, I have a linked table in IRIS that sources a Microsoft SQL table (using standard linked feature from the portal). It works fine, I can access it using SQL as many other times. On top of that, I've created in DeepSee (ok, Analytics) a cube that uses this class as source. It compiles correctly, no errors given. When I build it with 100 records, all goes well and using Analyzer I can see results.

Existing cube deleted.

I was facing the same issue as Jerry faced when connecting IRIS to SQL server. My ODBC connection is configured to authenticate via windows authentication.

Configure IRIS ODBC connection with Windows authentication using a
 

How I fixed it for myself?

Root cause

• The SQL Server DSN was set to Windows Integrated Authentication.
• IRIS opens the ODBC connection via irisdb.exe, which runs under the IRIS Windows services’ logon account.
• My IRIS services were running as LocalSystem, so SQL saw the machine account<ORGNAME>\<ASSETID>$. SQL had no login for that identity → 18456 / 28000 “Login failed

Hi community.
I have a query:

SELECT
nameField,
dateField,
anotherDateField
FROM
(      
SELECTMIN(someDate) as dateField,       
nameField,
anotherDateField
FROM $$$SOURCEWHERE $$$RESTRICT               
GROUPby someOtherField    
)
WHERE dateField >= anotherDateField

This query should filter the data by the minimum value of the somDate field, but it doesn't. It displays all values ​​together, regardless of the external filter. The exact same query (without the $$$ tokens, of course) works fine in a regular SQL runtime.
My guess is that the $$$RESTRICT does this

WHERE source.%ID

The 2025.1.2 and 2024.1.5 maintenance releases of InterSystems IRIS^® data platform, InterSystems IRIS^® for Health^TM, and HealthShare^® Health Connect are now Generally Available (GA). These releases include the fixes for a number of recently issued alerts and advisories, including the following: 

• Alert: Transaction Rollbacks Fail When LogRollback is Enabled | InterSystems
• Advisory: Missing Globals with Mirror Database Download | InterSystems
• Alert: Risk of Silent Wrong Results When Using Specific OUTER JOIN Patterns in SQL Queries | InterSystems
• Alert: Risk of Silent Wrong Results When

Hello community,

I wanted to share my experience about working on Large Data projects. Over the years, I have had the opportunity to handle massive patient data, payor data and transactional logs while working in an hospital industry. I have had the chance to build huge reports which had to be written using advanced logics fetching data across multiple tables whose indexing was not helping me write efficient code.

Here is what I have learned about managing large data efficiently.

Choosing the right data access method.

As we all here in the community are aware of, IRIS provides multiple ways to access data. Choosing the right method, depends on the requirement.

• Direct Global Access: Fastest for bulk read/write operations. For example, if i have to traverse through indexes and fetch patient data, I can loop through the globals to process millions of records. This will save a lot of time.

Set ToDate=+HSet FromDate=+$H-1ForSet FromDate=$O(^PatientD("Date",FromDate)) Quit:FromDate>ToDate  Do
. Set PatId=""ForSet PatId=$Order(^PatientD("Date",FromDate,PatID)) Quit:PatId=""Do
. . Write$Get(^PatientD("Date",FromDate,PatID)),!

• Using SQL: Useful for reporting or analytical requirements, though slower for huge data sets.

In the modern world, the most valuable asset for companies is their data. Everything from business processes and applications to transactions is based on data which defines the success of the organization's operations, analysis, and decisions. In this scenario, the data structures need to be ready for frequent changes, yet in a managed and governed way. Otherwise, we will inevitably lose money, time, and quality of corporate solutions.
For a long time, data management and governance were solely based on the data itself, with excellent backup, restoration, ACID (Atomicity, Consistency,

Hi Team, 

Can I please check if anyone has built a simple web interface for maintaining custom SQL lookup class.   

We have a simple persistent class in HealthShare which is used for storing Pathology test codes. Test codes in this lookup class is used for message filtering and applying additional logic when processing pathology results/orders. 

We want to make this class available to external users from pathology (not the usual management portal users) to maintain so that they can add/edit/delete test codes as required. 

Has anyone implemented something similar to expose the HealthShare SQL

Hey Community!

We're happy to share a new video from our InterSystems Developers YouTube:

⏯  Advanced SQL join table cardinality estimates @ Ready 2025

Is there a way to create a table for the results of a SQL statement ?

Example: Consider the below query and results.

Select ID,Name,DOB,Gender from EMS.EMS

RESULT 

So, for the

In my previous article, Using LIKE with Variables and Patterns in SQL, we explored how the LIKE predicate behaves in different scenarios, from Embedded SQL to Dynamic SQL, and what happens to performance when wildcards and variables come into play. That piece was about getting comfortable writing a working LIKE query. But writing SQL that works is only the starting point. To build applications that are reliable, scalable, and secure, you need to understand the best practices that underpin all SQL, including queries that use LIKE.

This article takes the next step. We’ll look at a few key points to help strengthen your SQL code, avoid common pitfalls, and make sure your SELECT statements run not just correctly, but also efficiently and safely. I'll use SELECT statements with LIKE predicate as an example along the way, showing how these broader principles directly affect your queries and their results.

*This is what Gemini came up with for this article, kinda cute.

Introduction

In a previous article, I presented the IRIStool module, which seamlessly integrates the pandas Python library with the IRIS database. Now, I'm explaining how we can use IRIStool to leverage InterSystems IRIS as a foundation for intelligent, semantic search over healthcare data in FHIR format.

This article covers what I did to create the database for another of my projects, the FHIR Data Explorer. Both projects are candidates in the current InterSystems contest, so please vote for them if you find them useful.

You can find them at the Open Exchange:

• IRIStool and Data Manager
• FHIR Data Explorer

In this article we'll cover:

• Connecting to InterSystems IRIS database through Python
• Creating a FHIR-ready database schema
• Importing FHIR data with vector embeddings for semantic search

Hi everyone! 👋
I’m excited to share the project I’ve submitted to the current InterSystems .Net, Java, Python, and JavaScript Contest — it’s called IRIStool and Data Manager, and you can find it on the InterSystems Open Exchange and on my GitHub page.

When we need to integrate Caché/IRIS with other relational databases, one common question arises: “How do I set up the JDBC connection?”.
The official documentation doesn’t always provide a straightforward step-by-step guide, which can be frustrating, especially for beginners.

In this article, I’ll walk you through the entire process of configuring a JDBC connection with MySQL, from downloading the connector to linking tables in Caché/IRIS.

Note: JDBC connections in Caché/IRIS have some limitations, especially regarding caching and the use of ResultSet. In many cases, you may need to adapt

Hi Community,
I’m trying to execute a directory query in InterSystems IRIS using %SQL.Statement, but encountering an unexpected error.

Details:
The following command confirms that the directory exists:

Set dirPath="\\MYNETWORK_DRIVE\DFS-Shared_Product\GXM"
Write ##class(%File).DirectoryExists(dirPath)

It returns 1, meaning the path is valid and accessible.

However, when I try to execute this SQL query:

Set File=##Class(%SQL.Statement).%New()
Set Status=File.%PrepareClassQuery("%File","FileSet")
Set Result=File.%Execute(dirPath)
If Result.%SQLCODE {
    Write Result.%Message
}

I get the

I joined InterSystems less than a year ago. Diving into ObjectScript and IRIS was exciting, but also full of small surprises that tripped me up at the beginning. In this article I collect the most common mistakes I, and many new colleagues, make, explain why they happen, and show concrete examples and practical fixes. My goal is to help other new developers save time and avoid the same bumps in the road.

1. Getting lost among system classes and where to start

The issue: ObjectScript/IRIS ships with many system classes and packages (%Library, %SYS, %Persistent, %SQL, etc.).

In this tutorial, I will discuss how can you connect your IRIS data platform to sql server db  .

Prereq: 

• Have ODBC driver installed in your device. You can download it from here: Download ODBC Driver for SQL Server - ODBC Driver for SQL Server | Microsoft Learn. 
• Have SQL Server and IRIS installed in your device
• Have SQL Server connection Authentication enabled in SQL server

InterSystems FAQ rubric

One way to optimize query performance is to use query parallelism on a per-query or system-wide basis (a standard feature).

This is a technique for dividing the execution of a particular query among processors on a multi-processor system. The query optimizer will execute parallel processing only if there is a possibility of benefiting from parallel processing. Parallel processing is only applicable to SELECT statements.

Note that the number of parallel processes cannot be specified, as it is automatically adjusted according to the number of CPUs.

We're excited to announce a new version release of the SQLTools VS Code extension. 

SQLTools connects VS Code users to the most commonly used databases using drivers, including InterSystems IRIS. With over 3.5 million downloads, it is helping users work with their data much more easily.

Version 0.28.2 is now available and includes:
- New CSV export format options.
- Improved connection quickpick and status bar item.
- Query error strings now wrap.
- Document settings that require a restart after changing.
- Resolve contradictory SSL settings in MySQL driver.
- Fix connecting to AWS RDS by PostgreSQL

In the previous article, we saw how to build a customer service AI agent with smolagents and InterSystems IRIS, combining SQL, RAG with vector search, and interoperability.

In that case, we used cloud models (OpenAI) for the LLM and embeddings.

This time, we’ll take it one step further: running the same agent, but with local models thanks to Ollama.

I have been walking through this with a few team members and as such I thought there might be others out there who could use it, especially if you work with HL7 & Ensemble/HealthConnect/HealthShare and never venture out past the Interoperability section. 

First, I would like to establish that this is an extension of the already established documentation on importing and exporting SQL data found here: https://docs.intersystems.com/iris20241/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_impexp#GSQL_impexp_import

This tutorial will explicitly detail how to create, populate or update a Data LookUp Table