#SQL

Beginning in Caché 2013.1, InterSystems introduced Outlier Selectivity to improve query plan selection involving fields with one atypical value.

In this article, I hope to use an example 'Projects' table to demonstrate what Outlier Selectivity is, how it helps SQL performance and a few considerations for writing queries.

With InterSystems IRIS 2022.2, we introduced Columnar Storage as a new option for persisting your IRIS SQL tables that can boost your analytical queries by an order of magnitude. The capability is marked as experimental in 2022.2 and 2022.3, but will "graduate" to a fully supported production capability in the upcoming 2023.1 release.

The product documentation and this introductory video, already describe the differences between row storage, still the default on IRIS and used throughout our customer base, and columnar table storage and provide high-level guidance on choosing the appropriate storage layout for your use case. In this article, we'll elaborate on this subject and share some recommendations based on industry-practice modelling principles, internal testing, and feedback from Early Access Program participants.

An experiment on how to use the LangChain framework, IRIS Vector Search, and LLMs to generate IRIS-compatible SQL from user prompts.

This article was based in this notebook. You can run it with a ready to use environment with this application in OpenExchange.

Setup

First, we need to install the necessary libraries:

On the Latest GlobalSummit 2022, InterSystems Introduced Cloud SQL. So, you may have lightweight InterSystems IRIS with access to SQL only. Well, what if you would still need some Interoperability features in the cloud as well? There are various solutions on the market nowadays, which offer a bunch of integration adapters out of the box and can be extended with support from the community. Some time ago, I've implemented an adapter for the Node-RED project, which can be deployed manually everywhere you want. Now I would like to introduce a new integration with my recent discovery, n8n.io

n8n.io is a workflow automation platform, that supports over 200 different integrations out of the box and from a community, and now including InterSystems IRIS.

Hi,

If you want to import data from a mySQL export file (exported with mysqldump), you will find here a little script that could help.

Only the INSERT commands in the sql file are executed into Caché. Indices are not computed for better performance.
%NOINDEX, %NOCHECK and %NOLOCK are generated on each INSERT line.

Currently, the file can not contain a "),(" pattern inside the values part of the INSERT command. If this is the case, the line is skipped. This feature may be implemented in the extractValuesList method.

In a customer project I was asked how you can keep track of database changes: Who changed what at which date and time. Goal was to track insert, update and delete for both SQL and object access.

This is the table that I created to keep the Change Log:

This is a translation of the following article. Thanks [@Evgeny Shvarov] for the help in translation.

Someone posted a question on DC asking whether it was possible to determine access rights for a particular table row always at runtime, and if it was, how could one do that?
Answer: it is possible and it’s not hard at all.

Our team is reworking an application to use REST services that use the same database as our current ZEN application. One of the new REST endpoints uses a query that ran very slowly when first implemented. After some analysis, we found that an index on one of the fields in the table greatly improved performance (a query that took 35 seconds was now taking a fraction of a second).

I've asked a lot of questions leading up to this, so I wanted to share some of my progress.

The blue line represents the number of messages processed. The background color represents the average response time. You can see ticks for each hour (and bigger ticks for each day). Hovering over any point in the graph will show you the numbers for that period in time.

This is super useful for "at a glance" performance monitoring as well as establishing patterns in our utilization.

InterSystems FAQ rubric

Countermeasures against SQL injection have been published on various websites, but we believe that it is possible to prevent SQL injection in applications using InterSystems SQL as well as other RDBMS by implementing these countermeasures appropriately. In addition, InterSystems Data Platform (hereinafter referred to as IRIS) incorporates several measures that make SQL injection more difficult than general RDBMS.

InterSystems FAQ rubric

It can be retrieved using the schema INFORMATION_SCHEMA.

INFORMATION_SCHEMA is a system schema and is not displayed by default in the SQL menu of the Management Portal.

The method to display it is as follows.

1. Open Management Portal → System Explorer → SQL menu.
2. Check "System" on the left of the schema drop-down.
3. Select INFORMATION_SCHEMA from the schema dropdown.

Introduction

This is a simple tutorial on the quickest way I found to create a sample database for any purposes such as testing, making samples for tutorials, etc.

Creating a namespace

1. Open the terminal
2. Write the command "D $SYSTEM.SQL.Shell()"
3. Write "CREATE DATABASE " and the name you want for your namespace.

Now you have a new namespace in a faster way than creating it from the Management Portal - which of course offers way more configuration options.

What do you do if you want to have the ID field have a meaningful name for your application?

Sometimes it comes to pass that when you're making a new table that you want to have the unique row identifier (a.k.a. IDKEY) to be a field that has a name that is meaningful for your data. Moreover, sometimes you want to set this value directly. Caché fully supports this functionality and it works Suppose you have a class Test.Kyle. The data will be stored like so:

^Test.Kyle(IDKEY)=$LB("",Field1,Field2,...,Fieldn)

Named parameters can be achieved with SQLAlchemy :

from sqlalchemy import create_engine, text,types,engine

_engine = create_engine('iris+emb:///')

with _engine.connect() as conn:
    rs = conn.execute(text("select :some_private_name"), {"some_private_name": 1})
    print(rs.all())

or with native api

from sqlalchemy import create_engine, text,types,engine

# set URL for SQLAlchemy
url = engine.url.URL.create('iris', username='SuperUser', password='SYS', host='localhost', port=33782, database='FHIRSERVER')

_engine = create_engine(url)

with _engine.connect() as conn:
    rs = conn.execute(text("select :some_private_name"), {"some_private_name": 1})
    print(rs.all())