InterSystems IRIS™ data platform provides high-performance, full-featured SQL access. You can use SQL at scales from queries running on a single CPU core, to parallel queries using dozens of cores, to distributed queries across a cluster of InterSystems IRIS servers.
SQL features available in InterSystems IRIS include:
For this activity, you will use the InterSystems Management Portal to run SQL queries, leveraging several SQL features of InterSystems IRIS to retrieve petstore data from an instance of the database.
If you are using Learning Labs Sandbox, the sample pet store data is already loaded. Skip to the next step.
Otherwise, clone or download this repository:
https://github.com/intersystems/Samples-Petstore-Data. A folder named data contains all files needed. In the Management Portal, navigate to System Explorer > Classes and import
PetUtilCls.xml files into the USER namespace from the data folder.
Access the InterSystems IRIS Terminal through the InterSystems IRIS launcher menu on Windows or using the iris terminal command in the operating system command line. Type the following command:
Demo.Petto the Execute Query box. Edit this query to include
where color = 'Yellow' and type = 'bird'and execute.
A UI is provided to easily view your schemas and stored procedures, and to view data and test your queries.
SELECT petshop->location->city, petshop->location->state, count(*) as NumberYellow FROM demo.pet WHERE color = 'yellow' and type = 'bird' GROUP BY petshop->location->city, petshop->location->state ORDER BY NumberYellow desc
Demo.Peton color and type to make the previous query fast. To be able to compare this fast query to the next query you will run without bitmap indices, note the execution time as indicated by Performance, below the execution box.
%NOINDEXto run the query ignoring indices:
SELECT petshop->location->city, petshop->location->state, count(*) as NumberYellow FROM demo.pet WHERE %NOINDEX color = 'yellow' and %NOINDEX type = 'bird' GROUP BY petshop->location->city, petshop->location->state ORDER BY NumberYellow desc
Notice the query performance below the execution. It should be quite a bit slower than the previous query. While execution takes less than a second in these cases, you can imagine the impact on a larger set of data. It should be noted that there may be situations where you want to use
%NOINDEX to improve performance such as if almost all data is included by the condition. Additional optimization hints are available should you need greater control over your queries.
DisplayAllBirdsByLocation(), which takes a city and state as arguments. This method queries for all yellow birds based on the city and state and applies additional formatting to the date to print out a formatted report. Since the data was randomly populated using the built-in population tools of InterSystems IRIS, call this method, replacing TopCity and TopState with the values you found above:
Using objects and SQL side by side provides great flexibility, so you can choose the best model for each task. While this example uses ObjectScript, the multi-model nature of InterSystems IRIS provides this capability within other languages as well, such as Java and .NET.
While built-in tools are available to view and manage your data, you can easily connect and use your own SQL client with InterSystems IRIS as well.
In the following video, you will see how to connect the SQuirreL SQL Client, one example of a third-party SQL client, to InterSystems IRIS. In order to follow along with this video, you need to have SQuirreL installed with JDK1.8. You also need an instance of InterSystems IRIS preloaded sample stock data. Still need an instance? Check out your options here. If you will be using one of the cloud providers — AWS, Azure, or GCP — you can install the stock data using the following command: