Metadata inconsistencies in InterSystems IRIS can lead to unexpected query results, performance issues, and data integrity problems. SQL DATA LENS provides tools to identify and address these inconsistencies effectively. This guide outlines common scenarios and demonstrates how to use SQL DATA LENS to detect and resolve them.
The Problem: Bypassed Metadata Validation
When applications bypass relational modeling and access globals directly, stored values may not match the defined metadata. This can cause three types of issues:
1. Access Failure
- The driver cannot read the value.
- An exception is thrown at query time.
2. Silent Corruption
- The value is read successfully but does not match the expected metadata.
- No error is raised, but the inconsistency remains hidden.
3. Undetected Mutation
- The value is read and appears correct.
- However, it has been silently modified (e.g., coerced into a compatible type) by the driver.
Simulating the Behavior
To demonstrate these scenarios, I created the DATATYPE_SAMPLE database, available on the InterSystems Open Exchange:
🔗 Package page
🔗 GitHub repo
The table used for the demonstration:
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)
);Scenario 1: Access Failure
To simulate an inconsistency, I injected invalid values into the DOB (Date of Birth\Datatype DATE) column using direct global access. Specifically, the rows with primary keys 101, 180, 181, 182, 183, 184, and 185 were populated with values that do not represent valid dates.
With SQL DATA LENS, you get detailed information about the error, the affected row, and the actual database value. As shown in the screenshot, the internal value for the first row in columns DOB is "39146<Ruined>", which cannot be cast to a valid DATE.
SQL DATA LENS also allows you to configure whether result processing should stop at the first erroneous cell or continue reading to retrieve all available data.
How SQL DATA LENS Helps with
- Displays detailed error messages.
- Identifies the affected row and column.
- Shows the original stored value.
- Lets you choose whether to stop at the first error or continue scanning.
Scenario 2: Silent Corruption
In the Employee table of the DATATYPE_SAMPLE database, there is a deliberately manipulated record that demonstrates this behavior – it’s the row with ID = 110. At first glance – and even at second glance – no issues are apparent. Neither the database driver nor the query tool indicates a problem if read this row.
Only upon closer inspection does it become clear that the value in the red-marked cell does not match the transmitted (and defined) metadata.
The column “Name” is defined as VARCHAR(50), but the actual value is 60 characters long!
There are scenarios where this behavior doesn’t cause any issues — for example, when the driver handles such inconsistencies leniently.
However, problems can arise when downstream systems rely on the provided metadata. If further processing is based on these metadata definitions, errors may occur when the actual content doesn’t conform to the agreed interface.
What Happens
- Most tools read the value without issue.
- No warning or error is shown.
- The metadata violation is only detectable via deeper inspection.
Why It Matters
If downstream processes (e.g., ETL, schema generators) rely on metadata definitions, silent corruption can cause runtime errors or logic bugs.
Detect with SQL DATA LENS
SQL DATA LENS generate pre-built SQL queries that flags such issues:
Scenario 3: Undetected Mutation
To demonstrate this issue, the DATATYPE_SAMPLE database includes a faulty record specifically changed to illustrate this behavior. The record in question is the row with ID = 120
Again, neither the database driver nor the query tool will indicate a problem when reading this row. In this case, the value even appears to match the metadata! The column is defined as INTEGER, and the row returns an integer value (in this example: 0) in that cell. However, this value is not actually stored in the database! A direct look at the underlying Global reveals the true content. Through manipulation, a string value was injected into this field.
What Happens
- The driver coerces the value (e.g., to
0), and the tool shows it as a valid integer. - The underlying inconsistency remains hidden unless you inspect the raw global data.
Detect with SQL DATA LENS
A diagnostic query flags such hidden issues:
