Article
· Sep 13, 2022 8m read
CI/CD with IRIS SQL

In the vast and varied SQL database market, InterSystems IRIS stands out as a platform that goes way beyond just SQL, offering a seamless multimodel experience and supporting a rich set of development paradigms. Especially the advanced Object-Relational engine has helped organizations use the best-fit development approach for each facet of their data-intensive workloads, for example ingesting data through Objects and simultaneously querying it through SQL. Persistent Classes correspond to SQL tables, their properties to table columns and business logic is easily accessed using User-Defined Functions or Stored Procedures. In this article, we'll zoom in on a little bit of the magic just below the surface, and discuss how it may affect your development and deployment practices. This is an area of the product where we have plans to evolve and improve, so please don't hesitate to share your views and experiences using the comments section below.

12 6
0 1.1K

Hi,

I'm doing a query in SQL and I need to sort my data by some non-repeated field.

Unfortunately, my data is grouped in a way that I cannot guarantee that any column will not have repeated data, so one solution would be to take the row number.

Also, the Cache is not accepting Row_Number () in my querry and I would like to know if there is another solution to return line numbers or some way to add this function to the Cache.

Best regards.

12 7
0 3.5K

The object and relational data models of the Caché database support three types of indexes, which are standard, bitmap, and bitslice. In addition to these three native types, developers can declare their own custom types of indexes and use them in any classes since version 2013.1. For example, iFind text indexes use that mechanism.

12 1
1 2.2K
Article
· Sep 11, 2024 9m read
Dates with InterSystems

Do not let the title of this article confuse you; we are not planning to take the InterSystems staff out to a fine Italian restaurant. Instead, this article will cover the principles of working with date and time data types in IRIS. When we use these data types, we should be aware of three different conversion issues:

  1. Converting between internal and ODBC formats.
  2. Converting between local time, UTC, and Posix time.
  3. Converting to and from various date display formats.

11 2
4 502

If you are a customer of the new InterSystems IRIS® Cloud SQL and InterSystems IRIS® Cloud IntegratedML® cloud offerings and want access to the metrics of your deployments and send them to your own Observability platform, here is a quick and dirty way to get it done by sending the metrics to Google Cloud Platform Monitoring (formerly StackDriver).

11 1
2 301

Suppose you have an application that allows users to write posts and comment on them. (Wait... that sounds familiar...)

For a given user, you want to be able to list all of the published posts with which that user has interacted - that is, either authored or commented on. How do you make this as fast as possible?

Here's what our %Persistent class definitions might look like as a starting point (storage definitions are important, but omitted for brevity):

11 3
5 473
Article
· Sep 26, 2023 1m read
Create JSON Objects and Arrays by SQL

The related package avoids adding %JSONAdaptor to each class but uses instead
SQL functions JSON_OBJECT() to create my JSON objects. With this approach, you can
add JSON to any class - even deployed ones - without any need for change or recompiling.

The trigger was the Export of M:N relationships as JSON objects or arrays.

11 0
0 409

This is the second piece in our series on 2021.2 SQL enhancements delivering an adaptive, high-performance SQL experience. In this article, we'll zoom in on the innovations in gathering Table Statistics, which are of course the primary input for the Run Time Plan Choice capability we described in the previous article.

11 4
1 818
Article
· Jan 2, 2022 3m read
DB Migration using SQLgateway

Thanks to @Yuri Marx we have seen a very nice example for DB migration from Postgres to IRIS.
My personal problem is the use of DBeaver as a migration tool.
Especially as one of the strengths of IRIS ( and also Caché) before is the availability of the
SQLgateways that allow access to any external Db as long as for them an access usinig
JDBC or ODBC is available. So I extended the package to demonstrate this.

11 3
2 765

This is the third article in our short series around innovations in IRIS SQL that deliver a more adaptive, high-performance experience for analysts and applications querying relational data on IRIS. It may be the last article in this series for 2021.2, but we have several more enhancements lined up in this area. In this article, we'll dig a little deeper into additional table statistics we're starting to gather in this release: Histograms

11 0
0 584
Announcement
· Aug 25, 2016
JSON changes in Caché 2016.2

As Bill has mentioned earlier in his post, we have carefully reviewed the JSON capabilities and made some adjustments to ensure they deliver the best benefit to you. In this post, I am going to describe the modifications in more detail and provide guidance for you to understand the implication for your code base.

11 8
1 4K

This article describes a significant enhancement of how InterSystems IRIS deals with table statistics, a crucial element for IRIS SQL processing, in the 2025.2 release. We'll start with a brief refresher on what table statistics are, how they are used, and why we needed this enhancement. Then, we'll dive into the details of the new infrastructure for collecting and saving table statistics, after which we'll zoom in onto what the change means in practice for your applications. We'll end with a few additional notes on patterns enabled by the new model, and look forward to the follow-on phases of this initial delivery.

10 5
3 107

With the release of InterSystems IRIS Cloud SQL, we're getting more frequent questions about how to establish secure connections over JDBC and other driver technologies. While we have nice summary and detailed documentation on the driver technologies themselves, our documentation does not go as far to describe individual client tools, such as our personal favourite DBeaver. In this article, we'll describe the steps to create a secure connection from DBeaver to your Cloud SQL deployment.

10 21
2 1.9K

What I find really useful about IRIS when teaching my subject of Postrelational databases is the fact that it is a multi model database. Which means that I can actually go into architecture and structure and all that only once but then show the usage of different models (like object, document, hierarchy) using the same language and approach. And it is not a huge leap to go from an object oriented programming language (like C#, Java etc) to an object oriented database.

However, along with advantages (which are many) come some drawbacks when we switch from object oriented model to relational. When I say that you can get access to the same data using different models I need to also explain how it is possible to work with lists and arrays from object model in relational table. With arrays it is very simple - by default they are represented as separate tables and that's the end of it. With lists - it's harder because by default it's a string. But one still wants to do something about it without damaging the structure and making this list unreadable in the object model.

So in this article I will showcase a couple of predicates and a function that are useful when working with lists, and not just as fields.

10 2
1 298

Hello IRIS Community,

InterSystems Certification is developing a certification exam for InterSystems IRIS SQL specialists, and if you match the exam candidate description given below, we would like you to beta test the exam. The exam will be available for beta testing on June 9 - 12, 2024 at InterSystems Global Summit 2024, but only for Summit registrants (visit this page to learn more about Certification at GS24). Beta testing will open for all other interested beta testers on June 24, 2024. However, interested beta testers should sign up now by emailing certification@intersystems.com (please let us know if you will be beta testing at Global Summit or in our online proctored environment). The beta testing must be completed by August 2, 2024.

10 5
7 1.4K

We have a yummy dataset with recipes written by multiple Reddit users, however most of the information is free text as the title or description of a post. Let's find out how we can very easily load the dataset, extract some features and analyze it using features from OpenAI large language model within Embedded Python and the Langchain framework.

10 3
2 381
Article
· Feb 22, 2024 4m read
IRIS 2024.1 Preview - New Feature

There is an interesting new feature in the recently announced 2024.1 preview, JSON_TABLE. JSON_TABLE is one of a family of functions introduced by the 2016 version of the SQL Standard (ISO Standard, published in early 2017). It allows for JSON values to be mapped to columns and queried using SQL. JSON_TABLE is valid in the FROM clause of some SQL statements.

The syntax of JSON_TABLE is quite large, allowing for exceptional conditions where the provided JSON values don't match expectations, nested structures and so on.

10 4
3 482
Article
· Sep 18, 2023 7m read
Vectors support, well almost

Nowadays so much noise around LLM, AI, and so on. Vector databases are kind of a part of it, and already many different realizations for the support in the world outside of IRIS.

Why Vector?

  • Similarity Search: Vectors allow for efficient similarity search, such as finding the most similar items or documents in a dataset. Traditional relational databases are designed for exact match searches, which are not suitable for tasks like image or text similarity search.
  • Flexibility: Vector representations are versatile and can be derived from various data types, such as text (via embeddings like Word2Vec, BERT), images (via deep learning models), and more.
  • Cross-Modal Searches: Vectors enable searching across different data modalities. For instance, given a vector representation of an image, one can search for similar images or related texts in a multimodal database.

And many other reasons.

So, for this pyhon contest, I decided to try to implement this support. And unfortunately I did not manage to finish it in time, below I'll explain why.

10 7
3 1.2K

InterSystems is pleased to announce the General Availability of InterSystems IRIS Cloud SQL and InterSystems IRIS Cloud IntegratedML, two foundational services for developing cloud-native solutions powered by the proven, enterprise-class performance and reliability of InterSystems IRIS technology.

10 2
0 347
Article
· Apr 4, 2023 2m read
InterSystems SQL Cheat Sheet

Hi developers!

As you know InterSystems IRIS besides globals, object, document and XML data-models also support relational where SQL is expected as a language to deal with the data.

And as in other relational DBMS InterSystems IRIS has its own dialect.

I start this post to support an SQL cheatsheet and invite you to share your favorites - I'll update the content upon incoming comments.

Here we go!

10 26
7 1.4K