Indeed, that's the recommended SQL way of achieving what Eduard described about PPGs. Drawbacks are that queries on these tables cannot be parallelized (as that implies multiple processes, of course).

Our TSQL support is meant for Sybase customers wishing to redeploy their TSQL applications on IRIS (especially now that SAP/Sybase is terminating support for those platforms). Just temporary table support by itself wouldn't be a reason to start building TSQL applications and abandon IRIS SQL/ObjectScript, of course :-). However, for a recent TSQL migration we did some work on our TSQL temp table support and were considering to roll that out to regular IRIS SQL, so this thread is a good place to share your experiences and requirements so we can make sure to do that properly, as needed. yes

Hi Darko,

LONGVARCHAR is actually storing the text as a stream, so to make LIKE work, you'll need to convert it to a string, e.g. using SUBSTRING(), in the expression you're serving to the LIKE operator. The following works fine for me:

SELECT SUBSTRING(text, 1, 999999) FROM table WHERE SUBSTRING(text, 1, 9999999) LIKE '%abc%'

This looks a little clumsy, but streams are really meant for huge data that wouldn't fit in a VARCHAR. Note that you can get a massive amount of text in VARCHAR (%String) columns these days, so maybe that's worth considering.

Separately, when working with iFind, that'll provide faster search results and it also transparently takes care of the stream aspect.

The Jupyter support is very exciting, adding a neat and highly appropriate mechanism for exposing IRIS-side concepts to a typical Python environment (Jupyter). This release is introducing a first taste of such an interaction, but we're very interested in learning from your experiences and ideas on making this even more effective at adding process control to your Python work. yes

InterSystems IRIS (and Caché before that) will indeed make this decision for you. The SQL optimizer will analyze all the conditions in your query and select the best query plan based on the available table statistics, which includes column selectivity. See also this article on collecting those stats with the TuneTable command.

As a matter of fact, our development team is making some exciting enhancements to the cost functions used to turn those table statistics into the actual cost estimates for the possible query plans. More about that at our upcoming Global Summit!

IRIS NLP, previously known as iKnow, is an embedded technology, meaning it's there in the form of APIs. These articles on building a domain and using the knowledge portal should be a helpful start, as is this series of step-by-step videos (which are a little older I'll admit; start with the "fundamentals" one) and of course other articles on the developer community tagged for iKnow.

Hi Guillaume,

iFind indices, like bitmap indices before, require a bitmap-friendly ID key (positive integer). When you make a table the child in a parent-child relationship, the underlying storage structure will use a composite key that therefore no longer satisfies the bitmap friendliness. We do plan to lift this limitation in a future release, as it's already the case for bitmap indices, but for now you'll have to review your schema and see if a one-to-many or (preferred) foreign key would work for you.

Thanks,
benjamin