Detecting and resolving metadata inconsistencies in InterSystems IRIS

Metadata inconsistencies in InterSystems IRIS lead to unexpected query results, avoidable exceptions and data that quietly stops meaning what the schema says it means. The frustrating part is that only one of the three failure modes announces itself. The other two look completely healthy in most SQL clients. This article walks through all three, using a sample database you can install yourself, and shows what SQL DATA LENS surfaces in each case.

The problem: bypassed metadata validation

IRIS lets you reach the data two ways: through the relational and object layers, which validate against the class definition, and directly through globals, which do not. Applications that write globals directly — usually for speed, or because the code predates the SQL projection — can store values that the class definition says are impossible.

Nothing breaks at write time. The mismatch only appears later, when a driver reads the value and has to reconcile it with the metadata the class definition advertises. That reconciliation can go three ways:

1. Access failure — the driver cannot read the value at all and an exception is thrown at query time.
2. Silent corruption — the value is read successfully but does not match the declared metadata. No error is raised, so the inconsistency stays hidden.
3. Undetected mutation — the value is read, looks correct, and is not what is stored. The driver has coerced it into something type-compatible on the way out.

The first is a nuisance. The second and third are the dangerous ones, because every downstream consumer that trusts the metadata — an ETL job, a schema generator, a report, an interface contract — is now working from a promise the data does not keep.

Reproducing the behaviour

To make the scenarios reproducible rather than anecdotal, they are built into the DATATYPE_SAMPLE database, which is published on the InterSystems Open Exchange:

• Package page on Open Exchange
• GitHub repository

All three scenarios use one table:

CREATE TABLE SQLUser.Employee (
   ID              BIGINT          NOT NULL AUTO_INCREMENT,
   Age             INTEGER,
   Company         BIGINT,
   DOB             DATE,
   FavoriteColors  VARCHAR(4096),
   Name            VARCHAR(50)     NOT NULL,
   Notes           LONGVARCHAR,
   Picture         LONGVARBINARY,
   SSN             VARCHAR(50)     NOT NULL,
   Salary          INTEGER,
   Spouse          BIGINT,
   Title           VARCHAR(50),
   Home_City       VARCHAR(80),
   Home_State      VARCHAR(2),
   Home_Street     VARCHAR(80),
   Home_Zip        VARCHAR(5),
   Office_City     VARCHAR(80),
   Office_State    VARCHAR(2),
   Office_Street   VARCHAR(80),
   Office_Zip      VARCHAR(5)
);

The rows referenced below are deliberately damaged through direct global access, exactly the way a legacy routine would do it.

Scenario 1: access failure

The DOB column is declared DATE. In the sample database, the rows with primary keys 101, 180, 181, 182, 183, 184 and 185 hold values that are not valid dates, written straight into the global.

Most clients respond with a single generic conversion exception and no indication of where it came from. SQL DATA LENS reports the error, the row and column it occurred in, and the value actually held in the database. For the first affected row, the internal value in DOB is 39146<Ruined> — a valid date serial with a string appended to it, which is why the cast to DATE fails.

You can also decide how the result set should behave when it hits such a cell. By default, reading stops at the first faulty value; switching that off lets the query run to completion so you can collect every bad row in one pass instead of fixing them one exception at a time. The toggle is on the main toolbar and applies globally.

Symptoms

• A conversion or cast exception at query time, often with no row reference.
• The same query works when the offending rows are excluded by a WHERE clause.
• Reports and exports fail part-way through with partial output.

Resolution steps

1. Run the query with read-error handling set to continue, so the full list of affected rows is collected in one execution.
2. Note the primary keys and the raw values shown for the failing cells.
3. Inspect the underlying global with the Global Browser to confirm what is stored and how it got there.
4. Correct the stored values — either back to a valid representation of the intended date, or to NULL where the original value is unrecoverable.
5. Fix the routine that wrote them. Until the direct global write goes through validation or is corrected, the rows will come back.

Scenario 2: silent corruption

Row ID = 110 of the Employee table looks unremarkable. At first glance, and at second glance, nothing is wrong: every client reads it, no warning appears, and the grid shows a perfectly ordinary employee record.

The Name column is declared VARCHAR(50). The value in that row is 60 characters long.

What happens

• Most tools read the value without complaint, because the driver is lenient about over-length strings on the way out.
• No warning, no error, nothing in the log.
• The violation is only visible if you compare the value against the metadata deliberately.

Why it matters

Whether this hurts depends entirely on what happens next. If the value is only ever displayed, nothing breaks. If it crosses an interface where the metadata is treated as a contract, it breaks there instead of here, and a long way from the cause:

• An ETL target column sized from the source metadata rejects or truncates the row.
• A generated class, DTO or Avro/Parquet schema declares 50 characters and fails on write.
• A downstream database with stricter type checking refuses the insert.
• Data is silently truncated, and nobody notices until a reconciliation fails.

Resolution steps

1. From the table’s context menu in the Table Viewer, generate the integrity-check SQL for the table. SQL DATA LENS builds the checks from the current metadata, so every character-length, numeric and date constraint is covered without you writing predicates by hand. The same generation works for views and stored procedures.
2. Run the checks and treat each returned row as a finding: it is a value that contradicts the declaration.
3. For each finding, decide which side is wrong. Either the data is bad and must be corrected, or the declaration is too narrow and the class definition should be widened and recompiled.
4. Re-run the checks after the fix, and keep them as a scheduled script in the Script Manager so a regression is caught by the next run rather than by a downstream consumer.

Scenario 3: undetected mutation

Row ID = 120 is the subtle one. Neither the driver nor the client reports a problem, and this time the value even looks like it matches the metadata. The column is declared INTEGER and the grid duly shows an integer — 0.

That zero is not in the database. A string was injected into the field through direct global access, and the driver, unable to make an integer out of it, produced 0 on the way out. The result set is internally consistent and completely wrong.

What happens

• The driver coerces the stored value into something that fits the declared type.
• The client shows a plausible value, so no human review flags it.
• Only a direct look at the global reveals the real content.

This is the failure mode to worry about in reporting and migration work. An access failure stops the job; a mutation lets it finish with a wrong number in it. A coerced 0 in a Salary, Age or quantity column will be aggregated, averaged and charted without complaint.

Resolution steps

1. Generate and run the diagnostic queries for the table. Because the checks compare the stored representation against the declared type rather than reading through the same coercion, they flag values that the ordinary SELECT reports as valid.
2. Open the affected node in the Global Browser and read the raw subscript value. This is the only view that shows the content without driver interpretation, and it is where the string in row 120 becomes visible.
3. Use the Data Inspector on the cell where the value may be structured — $LISTBUILD, JSON, XML or binary — to see how it is encoded rather than how it renders.
4. Correct the stored value, then re-run any aggregate that touched the column. A mutated value does not just affect its own row; it has already been rolled into every total calculated since it was written.

A practical routine

Metadata drift is not a one-off event, so it is worth handling like any other data quality check rather than as an incident:

• Generate integrity checks for the tables, views and stored procedures in the namespace and keep the scripts under version control.
• Run them after every migration, data load or upgrade, and after any change to code that writes globals directly.
• Leave read-error handling set to continue while investigating, so one pass produces the full picture.
• When a finding turns out to be a legitimately widened field, fix the class definition rather than the check.

The three scenarios above are all reproducible with the DATATYPE_SAMPLE database, so it is worth installing it and running through them once against your own driver version before you meet them in production. If you have not installed the tool yet, start at the download page; the user’s guide covers the SQL editor options referenced here in more detail.