You should not write %BuildIndices/%PurgeIndices. Can you provide the SQL map definition and the index definition of one of your indexes - just as an example? Perhaps we can start with a simple index on a table with a smaller number of rows?

That is a good question! I checked the internal change logs and found my changes were in 2020.1. Maybe earlier but I installed 2020.1 build 215 and tested a simple case.

IRIS for UNIX (Apple Mac OS X for x86-64) 2020.1 (Build 215U) Mon Mar 30 2020 20:24:45 EDT

The test is simple - I am running in the 2020R1 instance, namespace USER and there are no classes runnable in this namespace - just a clean, new install. I have several other instances running, all different versions/build numbers. I am testing against my XDBC instance which is currently labeled as 2020.4 (obviously not yet released but the IRIS Native code is essentially the same as in 2020.1). The XDBC instance is listening on port 51780. First, proof this doesn't work locally.

USER>write ##class(Sample.Person).CurrentAge($h-35000)                 

WRITE ##CLASS(Sample.Person).CurrentAge($H-35000)

And then, attempt the same function using the IRIS Native connection to the XDBC instance.

USER>set host="localhost",port=51780,namespace="USER",user="_SYSTEM",pwd="SYS" 

USER>set connection = ##class(%Net.DB.DataSource).CreateConnection(host, port, namespace, user, pwd)

USER>set iris = connection.CreateIris()                                                             

USER>write iris.ClassMethodValue("Sample.Person","CurrentAge",$h-35000)                             

This is just a sample. Browse the classes in the SMP Explorer, look at the class docs for %Net.DB.Iris.cls.

This functionality might also be present in a 2019 kit but I didn't test it.

Let me focus on the last two items in your list. IRIS Native for Java, Node.js, DotNet, Python - these are all consistent implementations of the IRIS Native API and the communication is over TCP or shared memory. IRIS Native for ObjectScript is just another - consistent - implementation of the IRIS Native API.

To get a connection to an IRIS server, the command is similar across all implementations of IRIS Native API:

set connection = ##class(%Net.DB.DataSource).CreateConnection(host, port, namespace, user, pwd)

Once you have a connection, you can get an IRIS object.

set iris = connection.CreateIris()

and from an iris object, you can invoke class methods, code implemented in routines, set/get globals, and so on.

Can you elaborate? How else can a session running on one IRIS instance invoke a class method on some other IRIS instance?

There are a number of limitations on the size of an SQL statement I think, maximum string length is one of those limitations. It is based on the normalized "signature", including the statement text, as a single string, even when you pass the source statement as an array. I haven't worked in this area for several years so I can't speak to the details.

Timothy Leavitt's excellent response notwithstanding, this is supported. I do fully embrace the option presented by Timothy Leavitt. The structures I demonstrate here actually produce a model very close to his and the index, since it includes both KEYS and ELEMENTS is projected to the child table projected from the addresses array. Of course, reversing KEYS and ELEMENTS in the index key specification would make the index more useful for searching on city name.

This definition:

Property addresses As array Of Sample.Address;
Index xA On (addresses(KEYS), addresses(ELEMENTS).City)

Not only works but the filing code also recognizes the ability to fold both properties in the index into the same iterator:

    If ('pIndexHandle)||($Ascii($Get(pIndexHandle("Sample.Person")),5)=1) {
        set bsv26N1 = $Order(^Sample.PersonD(id,"addresses",""))
        While bsv26N1 '= "" {
            Set bsv0N8=$zu(28,##class(Sample.Address).%Open($select(^Sample.PersonD(id,"addresses",bsv26N1)="":"",1:$listbuild(^Sample.PersonD(id,"addresses",bsv26N1)_""))).City,7,32768)
            Set ^Sample.PersonI("xA",bsv26N1,bsv0N8,id)=$listget(bsv0N2,1)
            set bsv26N1 = $Order(^Sample.PersonD(id,"addresses",bsv26N1))

And a quick test shows this structure is produced:

panther.local:XDBC:USER>d ##class(Sample.Person).Populate(10) 

panther.local:XDBC:USER>zw ^Sample.PersonI("xA")

^Sample.PersonI("xA","A886"," GANSEVOORT",3)=""

^Sample.PersonI("xA","B350"," MIAMI",6)=""

^Sample.PersonI("xA","B748"," NEWTON",3)=""

^Sample.PersonI("xA","C135"," UKIAH",9)=""

^Sample.PersonI("xA","C261"," ALBANY",1)=""

^Sample.PersonI("xA","C883"," DENVER",2)=""

^Sample.PersonI("xA","D162"," ST LOUIS",4)=""

And this has been in the product since maybe 2010. I couldn't find the original release note for this but I did find a change that fixed a problem when consolidating the iterators and that fix is present in 2010.2.

There is much to be discovered regarding Object Persistence. The ability to specify an expression in place of a literal global name in any of the various LOCATION keywords is just one. For example, you can add a public variable to a LOCATION keyword and its value at object filing time will be used to form the global location where the filer will update the data. There is risk involved and these "features" are most likely not documented. I do not recommend using these in production systems unless you fully understand the ramifications of doing so.

That said, there are a number of features that may or may not be documented but are certainly not private. In the context of this message, two come to mind. First is the Object Journal. Override the parameter OBJJOURNAL in a persistent class and all other classes referenced by this class and filing events are journaled. The Object Journal records each filing event for classes with the OBJJOURNAL parameter set and another class, %ObjectJournalTransaction, can be used to view the versions of those objects that were filed. All this would be wonderful but for a bug that I just discovered while coming up with an example for this post. I did a bit of research and it seems this bug has been present for a very long time and never reported. That indicates nobody is aware that this feature exists. I fixed the bug temporarily for the example.

SAMPLES>set person = ##class(Sample.Person).%OpenId(10)

SAMPLES>write oldperson.Name
Uhles,Ralph W.
SAMPLES>set person.Home.State = "NY"

SAMPLES>w person.%Save()
SAMPLES>set person.Office.State = "FL"

SAMPLES>set person.Name = "Book, John J"

SAMPLES>write person.%Save()
SAMPLES>write person.Name
Book, John J
SAMPLES>set journal = ##class(%ObjectJournalRecord).IDKeyOpen(3,4)

SAMPLES>set oldperson = journal.OpenObjectVersion(.s)

SAMPLES>write oldperson.Name
Uhles,Ralph W.

The second item of interest is something that has been around for a while as well and is definitely a fully documented and supported feature. This feature, Triggers, was previously an SQL only feature but it is now (and has been for several versions) available for Object filing as well. Using a save trigger for Objects and SQL allows access to the old and new values as well as a way to detect which values have been modified. I am happy to post an example if anyone is interested.

While the ability to specify an expression as a LOCATION keyword value and the use of Object Journal are not well known or mainstream features, Triggers are very much mainstream and can be quite useful.

The difference between runs can be caused simply by normal variations in system load. The difference between Concurrency = 0 and Concurrency = 1 is only present when the object is stored in multiple global nodes. There are five possible values that are described in the %Library.Persistent class documentation (viewable through the SMP). Concurrency = 1 is referred to as "atomic read". For single node objects there is no extra work required for atomic read. 

The name of the <index>Open method is based on the name of the index. I suspect that in one case the name of the IDKEY index is "IDKEY" and in another it is "idkey". No <index>Open method is generated if the index is not unique, primary or id.

What I don't know is if you need SQL mapped storage because default storage doesn't work for your case. Inheritance of storage is not the same as for other class members. I wrote storage inheritance and I am intrigued by the case where there are common serializations shared by multiple classes. Steve Canzano's idea is really close to what I think is a good solution but introducing %SerialObject brings with it some complications. Interestingly enough, the idea of SQL Map inheritance was probably brought up first by Steve several years ago.

What is clear is that no storage is inherited from anything other than the primary super class. I would like to understand your need to see if that restriction is preventing you from doing as you wish. Relaxing that restriction is not a simple task so I would also like to know if there is an existing pattern that can address your need.