go to post Benjamin De Boe · Jan 5, 2021 the Find() one implements the %SQL.AbstractFind interface is the longhand (opposite of shorthand :-) ) for what search_index() does in your %FIND predicate. The Rank() function is documented in the class ref
go to post Benjamin De Boe · Jan 5, 2021 It does not seem to be aware of the index so index parameters would be missed as only INDEXOPTION can be passed. hmm, that shouldn't happen. if you could file a reproducible test case through our internal systems, we'll take a look. Or perhaps you didn't use the implicitly-generated [package name].[table name]_[index name]Highlight() procedure?
go to post Benjamin De Boe · Jan 5, 2021 You can expose this information through setting the IFINDMAPPINGS parameter to 1: [class_name]_[index_name]_WordRec: stores which words appear in each record in this index. See also %iFind.Index.AbstractWordRec. [class_name]_[index_name]_WordSpread: stores the total number of records in which this word appears in this index. See also %iFind.Index.AbstractWordSpread. [class_name]_[index_name]_WordPos stores which word occurs at which position in a record, so it can be joined to the AttributePos table. See also So by doing a COUNT() on the WordPos table, you should find what you're looking for IFF it corresponds to an actual word. If you're using wildcards, you might combine with %iFind.FindWords() as a TVF, but that'd still be looking for individual words only: SELECT COUNT(*) FROM Test_IFind.IFind_IF_WordPos WHERE WordId IN (SELECT WordId FROM %iFind.FindWords('ab*')) If you want to count any kind of match, your highlight trick is probably the nicest way to get at it. (as an aside: with the introduction of the %iFind.Index.Minimal index type in 2020.1, which BTW was Eduard's suggestion ;-), it seems the class reference for the IFINDMAPPINGS projections added by the .Basic class but not in .Minimal got lost. We'll fix that shortly!)
go to post Benjamin De Boe · Nov 10, 2020 Thanks for the reference. That's indeed a very good approach to solve the international-exact-sort question through NLS collations (see also this note). The new SQL collation described above is meant to provide an easy way to have an international-broad-brush transformation to accommodate the non-exact cases, such as using a LIKE operator that doesn't trip over a single-accent difference.
go to post Benjamin De Boe · Nov 9, 2020 The ICU does suggest standardization (or at least standardized nomenclature) for script transliteration, which I believe is the more boring word for slugification :-) What are you referring to with "cost-effective"? Just the avoidance of wide characters or something else?
go to post Benjamin De Boe · Nov 4, 2020 No, that capability is projected for official release with 2021.1. Please reach out to @Bob Kuszewski if you are interested in joining an expert panel or join this Discord channel for the latest on our Python support.
go to post Benjamin De Boe · Nov 3, 2020 UPDATE: We uploaded an updated version of our 2020.4 preview release, including a small number of additional enhancements in the broader SQL area that missed the boat for the scheduled preview release date. These features meet the quality requirements for inclusion in the GA release later this month so we thought it was worthwhile sharing them in preview mode for users to try ahead of time. The new build number is 524, up from 521. The new images have been posted on the ICR, Docker Store and WRC locations as described above and the docker pull scripts have been updated. As always, we're looking forward to your feedback!
go to post Benjamin De Boe · Oct 28, 2020 Are you asking about the web interface or the underlying API? We are currently working on a new all-SQL loader that would be easy to use from any application or prompt (or to build a GUI on :-) )
go to post Benjamin De Boe · Oct 21, 2020 Nice article. Very much looking forward to your views on %SYSTEM.WorkMgr, which has been getting a lot of attention to help us serve the most demanding SQL workloads from our customers. You'll also be able to monitor some of its key metrics such as active worker jobs and average queue length in SAM starting with 2021.1.
go to post 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 :-)
go to post Benjamin De Boe · Sep 18, 2020 Not sure which version you are on, but the concept works fine on a recent IRIS kit I tried and as such this might be an older bug. In any case, this kind of errors warrants a call with the WRC.
go to post 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
go to post 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
go to post Benjamin De Boe · Aug 26, 2020 Or Class Test.Person Extends (%Persistent, %XML.Adaptor) { Property Name As %String; Property Id As %Integer [ Identity ]; }
go to post Benjamin De Boe · Aug 25, 2020 I agree entirely a separate table with FK links between the two is the SQL-friendliest way to go about this today. With expression indices (and later next year LATERAL JOIN support), we will soon have a practical way to leverage these Object-ish data models in all their beauty from SQL too!
go to post 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')
go to post Benjamin De Boe · Aug 25, 2020 I believe this section of the docs has the answer to your question: https://docs.intersystems.com/iris20201/csp/docbook/Doc.View.cls?KEY=GSQLOPT_indices#GSQLOPT_indices_collection
go to post 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.
go to post 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.
go to post 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.