I admit that when I first read this post I wasn't interested in commenting. I don't normally get involved in SQL mapping questions. @Brendan Bannon is the expert on that topic. As I read through this more, I became interested in what you didn't ask and I need to make sure that I'm on the right track.

Are you most interested in sharing the definition of a data node or do you have specific requirements that make you want to use SQL-mapped storage?


See answer by @Brendan Bannon about TuneTable.


“Relative cost” is an integer value which is computed from many factors as an abstract number for comparing the efficiency of different execution plans for the same query. This calculation takes into account (among other factors) the complexity of the query, the presence of indices, and the size of the table(s). Relative cost is not useful for comparing two different queries. proof

It's all about multi-model nature of IRIS and Caché.

Everything in Caché and InterSystems IRIS is being stored in globals - key-value sparse stored arrays.

Every persistent class (a class which stores data on disk) has the map of how the data should be stored in a particular global (globals).

ObjectScript Class definition provides the API of how the data will be stored and read from a global (globals) via Object Script or SQL access.

You can specify standard or custom storage strategy for a class. 

The persistent 'SomeClass' class with standard storage strategy will provide the API to store data in ^SomeClassD data for data itself and ^SomeClassI global for indexes.

You can see the storage strategy of a class in the source code of a persistent class immediately after successful compilation - it will be visible in a related XML block at the bottom of the class.

In your case you have a custom storage strategy which could be everything. Check @Brendan Bannon's series on that which could be really helpful for you.

As @Eduard Lebedyuk mentioned, if you delete the class as source code (e.g. via Management Portal)  it will not delete the data itself.

But! As @Robert Cemper mentioned if you delete it via SQL API with DROP statement it will delete data in a global too (because we suppose the data to be deleted if we DROP a table, right?).


The documentation is misleading about this.  It says:

When you upgrade Caché to a new version, existing Query Plans are automatically frozen.

And later:

The earliest upgrade that performs this operation is an upgrade to 2017.1.0.

What it needs to say is:

The earliest upgrade that performs this operation is an upgrade from 2016.2.0.

The docs do use 2016.2 as the "from" version in their example, but not in a way that communicates how this nice feature won't help with upgrades from pre-2016.2 versions.

@Brendan Bannon - please draw this to the attention the InterSystems documentation team.

Above I've deliberately linked explicitly to the 2017.2 doc rather than to "latest" because I hope that in due course the latter will get corrected.