New post
AllAccepted answers
Benjamin De Boe · Oct 12, 2020

Robert is correct, you can leverage our TSQL support to mimic that INSERT SELECT model Microsoft and Sybase offer. I would not recommend mixing TSQL with plain SQL on an ongoing basis to build your non-TSQL application, but just using the command on a one-off basis (it's a temp table you're after anyhow) should be just fine.

in IRIS SQL (and most other databases), INSERT SELECT will just do that, insert based on the selection, and not implicitly create the table if it doesn't exist. For that purpose, the CREATE TABLE .. AS SELECT .. syntax is offered by several other databases, and will appear in IRIS SQL soon. As a matter of fact, a developer is working on that command this very week :-)

Benjamin De Boe · Sep 11, 2020

Hi Mark,

can you be more specific on the nature of the data and possibly application that's going to be migrated? HealthShare is more a suite of applications with standardized data models underneath, running on top of InterSystems IRIS for Health. To ingest data into HealthShare means transforming it through its APIs, whereas IRIS for Health is more comparable to a relational database like DB2.

SQL Gateways allow mapping tables in remote non-InterSystems databases (such as DB2) to IRIS so they can be accessed as if they were local. That can be helpful during migrations as well as heterogeneous production environments. TSQL is a specific set of extensions of the SQL language used by Sybase and MS SQL Server that we support to ease migration scenarios for new customers with applications written in TSQL.

hope this helps,
benjamin

Benjamin De Boe · Sep 10, 2020

Hi Mohamed,

there is no reason to start any new projects with InterSystems Caché. It's a platform that has proven itself for numerous years, but it's now being superseded by InterSystems IRIS, which builds on the same core strengths but has also been bolstered by pretty much all the new development at InterSystems over the past three years. 

This said, to complement Kevin's excellent list of links, you can also check out https://www.intersystems.com/try for a free trial hosted by us (so you won't need your cloud account just yet) and https://gettingstarted.intersystems.com/ with more entry-level documentation and tutorials.

thanks,
benjamin

Benjamin De Boe · Aug 25, 2020

This is not possible today, but happens to be something due for 2020.4 or 2021.1 in the form of expression indices.

The documentation may be slightly confusing here as the BuildValueArray() mechanism applies to non-collection properties and not "array of" or "list of" properties. The section that describes it just happens to be placed right after the section describing how to use the ELEMENTS trick to index them in their entirety. Note that you can use BuildValueArray() for %ArrayOfObjects properties, but those aren't projected as a child table.

A possible workaround would be through a separate property that's just there as an intermediary for your index:

Property AR As array Of Sample.Address;
Property AllStates As %String [ Calculated, ReadOnly, SqlComputeCode = {set {*} = {%%ID}}, SqlComputed ];
Index AllStatesIDX On AllStates(ELEMENTS);
ClassMethod AllStatesBuildValueArray(value, ByRef valueArray) As %Status
{
  kill valueArray
  set tObj = ..%OpenId(value), tKey = ""
  for {
    set tAddress = tObj.AR.GetNext(.tKey)
    quit:tKey=""
    set valueArray(tKey) = tAddress.State
  }
  quit $$$OK
}

Then you can include it in queries:

select * from sample.person where FOR SOME %ELEMENT(AllStates) (%VALUE = 'KY')
Benjamin De Boe · Jul 17, 2020

the contents of those %Dictionary tables is a little geared towards class/object models. If you want a more SQL-focused view on your tables, you can look at the INFORMATION_SCHEMA package, which adheres to mainstream JDBC/ODBC dictionary structure and is used by mainstream SQL and BI tools like DBeaver, VSCode-SQLTools, Tableau, PowerBI, etc

Note you'll need to tick the "System" checkbox when browsing this schema in the System Management Portal.

Benjamin De Boe · Jul 15, 2020

Hi David, maybe you can elaborate a little more on the particular latency challenge you faced?

As Dmitriy mentioned, sharding will spread your data and corresponding query workload across multiple nodes in order to achieve higher efficiencies on very large datasets and is especially fit for read-mostly workloads. ECP, when used in a typical application server setup, is meant to distribute user-bound workload across multiple servers, so serving a slightly different goal. Depending on your use case, either of those (or a combination of them) can be more appropriate. See also this overview and introductory video for more info.

Benjamin De Boe · Jul 9, 2020

Could you add a little more detail on what you mean with "classes" and "used"?

If you are talking about generic ObjectScript classes and look at usage as plain invocations from any ObjectScript code, you may not be able to find that at all. If, on the other hand, you're looking for Business Services, Processes and Operations classes, there's a good chance we can pull much of that from available metadata (up to your most recent purge). Also, for persistent classes, you may be able to find whether they were recently accessed through SQL by looking into the Statement Index, but that of course only is about SQL and doesn't guarantee you they weren't used otherwise.

Benjamin De Boe · Jul 9, 2020

if that column is currently in a string format (you can tell from the column/resultset metadata), you can use a different pattern to check if it meets the raw format:

SELECT CASE WHEN '01/01/2020' %PATTERN '1.2N1"/"1.2N1"/"4N' THEN 'valid' ELSE 'invalid' END

if you also want to check if that resolves to a valid date, you can use CAST or TO_DATE, but those will throw errors if they don't resolve to a valid date, so a small custom function in ObjectScript is perhaps not a bad idea

Benjamin De Boe · Jul 8, 2020

Examples where System is useful include when you have a set of classes that are used in compile-time tasks such as method generators and projection classes, as used by several InterSystems components such as BI cube definitions and NLP domain definitions. The Class Parameter expression example you describe is probably another example in the same category.

Benjamin De Boe · Jun 22, 2020

Great suggestions @Eduard Lebedyuk !

There's a vast surface area here, so we're really looking forward to something that showcases innovation on the technology end, something that carries a real-world data set/feed, or both! I can't wait until the week is over :-)

PS: don't hesitate to use this channel for any questions you have. Some of our technology in this area is still fresh and we're always happy to learn how we can make it easier to use.

Benjamin De Boe · Jun 8, 2020

Can you tie those performance regressions to the new (one-time) embedded SQL late compilation behaviour or is there anything else happening?

As a ground rule, we consider performance regressions upon upgrading to be bugs and will happily work with you to get to the root cause and reconsider optimizer changes and updated heuristics. Please reach out through the WRC if you think we can help.

Benjamin De Boe · Jun 8, 2020

You can force embedded SQL to be compiled along with the embedding application code using a compile flag /compileembedded=1. This is not recommended though, so we'd really like to know more about why the current model doesn't suit you. In fact this first-run compiling would allow your customers to leverage the statistics for their data rather than what you baked into it when compiling the embedded SQL. See also this article.