Also interested to see submissions / ideas on handling really BIG globals. DataMove is a powerful feature for moving data from one database to another in a live environment, but it operates at a fairly low level. Adding a layer around it for easy archiving of table data, an interface to keep track of (potentially large numbers of) subscript-level mappings, etc.. would be examples of helpful utilities in the big global space. 

FWIW, we have several roadmap items for IRIS in this area, but I won't go into detail on them as it's much more exciting and informative for us to learn about the problems you are seeing and the solutions you'd propose to them :-)

Two small notes:

• The difference between %Dictionary.IndexDefinition and %Dictionary.CompiledIndex is that the former has all the indices you defined in that class itself, and the latter also the ones it got through class inheritance. Obviously, you can only delete the ones in the former list, unless you're sure you're not accidentally dropping one that still matters for another subclass of the same parent in which it was defined.
• If you use %DeleteId() to drop an index, you'll need to call %PurgeIndices() for those indices first (iianm) and recompile the affected class(es) afterwards. A slightly more elegant approach would be to call DROP INDEX for each entry in the list. That'll take care of the recompiling and data-dropping for you.

you're right. I didn't properly say that with "array of" I actually meant the underlying STORAGEDEFUALT = "array" behaviour. So the combination that'll be newly supported in IRIS 2022.1 is STORAGEDEFAULT = "list" with SQLPROJECTION = "table" (and table/column)

FWIW, here's my warranty-free code to project list-style collection properties to a table: https://github.com/bdeboe/isc-sql-utils

what do you mean with "for object properties too"? If you're asking whether 

Property Addresses as array of Sample.Address;

projects as a child table; yes it does. (and "list of" will support this too in 2022.1)

Can you be more specific about what you mean with "can't be deleted"? If this is a <PROTECT> error because they are mapped to ENSLIB (as part of enabling the namespace for Interop as Robert described), you can just ignore that error in a try / catch block.

I'm not sure about the general application context but if you need to go through the full list of globals for your app, it may be be better to query the globals that are in your application's database (directory) using %SYS.GlobalQuery:DirectoryList() rather than all the ones that are visible in the namespace using %SYS.GlobalQuery:NamespaceList() or ^$GLOBAL. That'll avoid the issues with ^EnsEDI.

Note that if you are actually using DeepSee or interop (or more generally rely on any features that may be writing their own globals), just looping through the global list for this kind of operations is very risky. Most built-in IRIS features (besides DeepSee and interop, that is) will write to globals prefixed with ^IRIS.*

woops, I got that quite wrong indeed. I got confused by the mentions of TSQL earlier in the thread and mistook it for some other lock-related syntax that's only there for TSQL. you are correct: LOCK TABLE is part of IRIS SQL.

I'm not sure whether the error message you got would have further clues, but likely you're better off filing this with the WRC. Given that it works for embedded SQL, I'm suspicious it may have to do with the pre-parser for .NET, which does an initial parsing of the command on the client side.

Did you set the SQL dialect to MSSQL or Sybase? That LOCK TABLE command is not part of IRIS SQL. I'm not familiar with .NET myself, but found this older code sample (rename to IRISConnection()):

                _tconn = new CacheConnection(connectionString);
                _tconn.SQLDialect = "MSSQL";
                _tconn.Open();

thanks for contributing this @Andreas Schneider! Your testing and feedback will help us build a better product, and we'll likely get most of this out of the way before 2021.2 goes GA 

Thanks @Kevin Chan for helping out (he's the author of the parser piece of LOAD DATA ;-) )

In addition to his comments on the encoding issues, you can also take a look at the %SQL_Diag.Result table which will likely have more detailed error info than what we're able to return through SQL semantics (though we're still working on ways to make it clearer). There's also a %SQL_Diag.Message table with row-level errors, which can help you figure out the reason for individual row failures.

Congratulations!

We're lucky to have airpods as the third place prize. Those were easy to split equally between the tied contestants :-)

This sounds exciting! Do you have a demo video, screenshots or a link to the full product page to make it more tangible?

Here are two examples using Synthea:

• https://github.com/intersystems-community/irisdemo-base-synthea
• https://github.com/grongierisc/synthea-fhir-iris

And two example repositories using LOAD DATA (but not yet ZPM-enabled):

• https://github.com/bdeboe/isc-tpch-script
• https://github.com/bdeboe/isc-adventureworks (using the DWH flavour of this well-known dataset only)