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...
 

SELECTFROMLEFT

I 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

Unified AI-agent dashboard - 1.

We're excited to announce the Early Access Program for IntegratedML Custom Models, a powerful new capability coming in IRIS 2026.1!

What Is It?

IntegratedML Custom Models extends the existing IntegratedML/AutoML feature by letting you deploy your own custom Python ML models directly within SQL queries.

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:

Hey Community!

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

⏯  Query Optimization in Hybrid Databases @ Ready 2025

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.

Understanding how drivers behave in these edge cases is crucial for diagnosing legacy data issues and ensuring application reliability.
The DATATYPE_SAMPLE database is designed to help analyze error scenarios where column values do not conform to the data types or constraints defined in the metadata.

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.

1. Aggregate-over-Window Functions

Overview

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:

• AVG() — average of values in the window frame.

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:

1.  Setting the auto parallel option system-wide.
   

2. Using the %PARALLEL keyword in the FROM clause of specific queries.
   

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 Name:tName

The colon syntax is used to identify local variables, in this case tName and tSSN. Here, tSSN will be defined prior to execution and  tName  will be set during execution of the statement. &SQL indicates to our compiler that this is SQL syntax.

InterSystems Cloud JDBC SQL Shell

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

• SQL
• Python
• TSQL
• MDX

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.

Three Steps to Build It

1.

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?

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, andexamine 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.

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

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.
Fact table built:             1,000 fact(s) (Max number of worker(s) used at a time: 1)
Fact indices built:           1,000 fact(s) (1 worker(s) used)

Complete
Elapsed time:                  1.788246s
Source expression time:        0.000856s

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>$

Hi community.
I have a query:

SELECTFROMSELECT

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 IN (

And outer WHERE is just left unseen
Is there a way to fix this?

The 2025.1.2 and 2024.1.5 maintenance releases of InterSystems IRIS^® data platform, InterSystems IRIS^® for Health, and InterSystems Health Connect™ are now Generally Available (GA).

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=+H
Set FromDate=+$H-1 For  Set FromDate=$O(^PatientD("Date",FromDate)) Quit:FromDate>ToDate  Do
. Set PatId="" For  Set 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.

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.