SQL is a standard language for storing, manipulating and retrieving data in relational databases.
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.
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:
In this article we'll cover:
Are you signed up to take the InterSystems IRIS SQL Specialist or Professional exam?
Get ready with these in-person and self-paced learning resources!
Hi Community,
Do you build and manage applications with InterSystems IRIS SQL?
🎓Two tiers of certifications—specialist and professional (new!)—are now available!
Common Table Expressions (CTEs) provide a structured framework for defining reusable intermediate result sets within SQL statements. InterSystems IRIS implements CTEs via the WITH clause, enabling clearer query composition and modular analytical processing while remaining fully integrated with the IRIS cost-based optimizer.
This article explores the semantics of CTEs in InterSystems IRIS, explains their interaction with query optimization, discusses appropriate deployment scenarios, and presents executable examples illustrating practical patterns for production environments.
Hello Community,
What is the most efficient, memory-safe way to get the names of the corrupted indexes on very large tables for a rebuild. However, if an index has millions of corrupted rows, the .errors array in %ValidateIndices grows too large and throws a errorerror.
Data privacy regulations such as GDPR, LGPD, and HIPAA demand that organizations know exactly where Personally Identifiable Information (PII) lives inside their databases. Yet in practice, most teams rely on manual inventories, tribal knowledge, or external scanning tools that require data to leave the database engine — a process that itself creates privacy and security risks.
This article presents an MVP that takes a different approach: it runs PII detection inside InterSystems IRIS using Embedded Python, analyzing data where it lives and never exporting it to an external process.
I need to analyse and improve the performance on some old SQL statements.
One statement uses NVL in the WHERE clause (no wonder why it's slow) but I still have to improve the performance and the data really needs to be the same when returned.
SELECT *
FROM TOURHead, TOURFIND, SGNRFIND, TNRHead
WHERE TOURHead.cl = '123'
AND TOURHead.cl = TOURFIND.cl
AND TOURHead.TOURNR = TOURFIND.TOURNR
AND TOURFIND.cl =* SGNRFIND.cl
AND TOURFIND.SGNR =* SGNRFIND.SGNR
AND TOURFIND.cl = TNRHead.cl
AND NVL(TOURFIND.TNR, SGNRFIND.TNR) = TNRHead.TNR;
Really hope someone can help me with this one.
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 our previous article, we explored the basics of unit testing in IRIS and the ways to apply it to a REST API. We even figured out how to test logic before finalizing network configurations and authentication, allowing us to focus solely on testing the API contents. Today, we will build upon that foundation and elevate our unit testing strategy by using another tool: %Populate .
At first glance, the %Populate class appears to be very simple. You can create a class that extends both %Persistent and %Populate and inherit a Populate method designed to generate randomized records.
Hi, Community!
Have you tried using serial objects in InterSystems IRIS? Explore some of the benefits and see how to start using them:
Hello everyone,
The Certification Team of InterSystems Learning Services is excited to announce the release of our new InterSystems IRIS SQL Professional exam. It is now available for purchase and scheduling in the InterSystems exam catalog. This exam is geared towards IRIS SQL experts, and it deals with a variety of advanced IRIS SQL topics that you can find in the exam page.
How can I remove UNIQUE NOT NULL from an existing column via DDL?
I currently have a table in production that was originally created via DDL, where the column was defined as UNIQUE NOT NULL:
CREATE TABLE database.Books (
ID %Library.UniqueIdentifier PRIMARY KEY,
TagExternal VARCHAR('') UNIQUE NOT NULL,
I now need to change this column so that it is no longer UNIQUE and no longer NOT NULL, making it equivalent to:
TagExternal VARCHAR(''),
As mentioned in the previous article, it is possible to configure the way you wish your collections to be stored and queried in SQL.
.png)
To set this up, you will need to use the following field parameters and settings:
In InterSystems IRIS, when you compile a persistent class, you automatically get a SQL table. Sometimes, there are situations that require having a slightly (or not so slightly) different names/options in SQL compared to Object model. Here are some of the settings that you can change to make it happen.
They define the "Face" of your class when it appears as a table in the SQL catalog.
SqlTableName allows you to change the name of SQL table that holds objects from your class. This is especially useful, when your class name uses reserved SQL words in a name, e.g. Group.
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.
The issue: ObjectScript/IRIS ships with many system classes and packages (%Library, %SYS, %Persistent, %SQL, etc.).
We have a large, existing production taking a feed from a single up-stream system and sending to a wide-variety of downstream systems. One of the downstream systems has been decommissioned, and we have deleted the classes that implemented the routing/transformations from the namespace, and deleted the components from the production (Wardview and WV Out in the diagram below). We now want to delete the messages associated with those components without deleting the messages associated with any other components.
Ever since I started using IRIS, I have wondered if we could create agents on IRIS. It seemed obvious: we have an Interoperability GUI that can trace messages, we have an underlying object database that can store SQL, Vectors and even Base64 images. We currently have a Python SDK that allows us to interface with the platform using Python, but not particularly optimized for developing agentic workflows. This was my attempt to create a Python SDK that can leverage several parts of IRIS to support development of agentic systems.
We were testing upgrades on our internal systems recently, and were initially surprised to see a change in some instances of SQL behavior. On further investigation, the changes make a lot of sense, and are easy to adapt to. But, to hopefully save you some time and provide additional information in case this is something you too find curious or interesting, we thought we would share our understanding of the changes and how we got around it to preserve previous behavior where desired.
Starting in IRIS version 2024.2.
This demo walks you through the new Table Partitioning feature in IRIS SQL, explaining what it does and how it works along the way. We'll only use a few dozen rows to prove the concept, but obviously the capability is focused on datasets many orders of magnitude larger.
If you'd like a shorter, higher-level introduction to Table Partitioning, check out the online learning module Managing Tables with Partitioning in InterSystems IRIS
ℹ️ Table Partitioning is included in IRIS 2026.1 as an experimental feature
This is the second part of an article pair where I walk you through:
As mentioned the goal is to give you a smooth “first run” experience.
Customer support questions span structured data (orders, products 🗃️), unstructured knowledge (docs/FAQs 📚), and live systems (shipping updates 🚚). In this post we’ll ship a compact AI agent that handles all three—using:
The 2025.1.4 and 2024.1.6 maintenance releases of InterSystems IRIS® data platform, InterSystems IRIS® for Health, and InterSystemsHealth Connect™ are now Generally Available (GA). These releases include the fixes for a number of recently issued alerts and advisories, including the following:
While starting with Intersystems IRIS or Cache, developers often encounter three core concepts: Dynamic Objects, Globals & Relational Table. Each has its role in building scalable and maintainable solutions. In this article, we'll walk through practical code examples, highlight best practices, and show how these concepts tie together.
1. Working with Dynamic Objects:
Dynamic objects (%DynamicObject and %DynamicArray) allow developers to manipulate JSON-like structures directly in Objectscript. They are especially useful for modern applications that need to parse, transform or generate JSON.
hi 😊,
i'm able to LOAD DATA in IRIS from a rather complex, say Oracle's, query. It works pretty well but requires a target table created aforehand:
LOAD DATA FROM JDBC CONNECTION SOME_OTHER_SERVER QUERY 'complex query here' INTO TargetTableis there a way to base such target table on the same query ?
CREATE FOREIGN TABLE seems to require a column definition which I would prefer to be taken from the query
CREATE TABLE AS SELECT seems to be for local queries and other methods, like Linked Table Wizard or %SYSTEM.SQL.Schema.
If you already know Java (or .Net) and perhaps also have used other document databases (or looking for one), but you are new to the InterSystems world, this post should help you.
InterSystems IRIS Cloud Document is a fully managed document database that lets you store JSON documents and query them with familiar SQL syntax, delivered as a cloud service managed by InterSystems.
In this article pair I’ll walk you through:
The goal is to give you a smooth “first run” experience.
Since I started using Claude Code, my motivation to create things has skyrocketed.
Previously, even if I wanted to build something, actually doing the coding felt like a hassle, so unless there was a very strong need, I rarely went as far as programming. But now, if I just jot down the specifications, Claude Code handles the rest automatically, resulting in a dramatic improvement in productivity.
I come from a generation native to ObjectScript, so I used to feel some hesitation when it came to switching to Python.
The InterSystems Ready 2026 Conference is approaching fast — it's a great time to prepare and check your knowledge before attempting one of the Certification exams onsite .
InterSystems certifications aren't easy: you need to practice with questions aligned to the real exam objectives.
exam-prep.es is a free platform built for exactly that: practice, identify weak spots, and show up better prepared.
Hi Community,
An error occurs during the analysis process in the FHIR SQL Builder. How can I identify the exact cause of the error?
Migrating from Oracle, MSSQL, or other purely relational database systems to a multimodel InterSystems IRIS is a strategic decision that requires careful planning and execution. While this transition offers significant benefits, including enhanced performance, scalability, and support for modern architectures, it also comes with challenges. In this article I will highlight some of the considerations connected to coding to ensure a successful migration. I will leave everything connected to an actual migration of structures and data outside the scope of this article.
.png)
First, when you're considering migrating to a different database system, you need to understand your business logic, whether it's on the side of the application (application server) or the database server. Basically, where do you have your SQL statements that you will need to potentially rewrite?