First Look: InterSystems SQL

Before You Begin

To use the procedure, you will need a running InterSystems IRIS instance. Your choices include several types of licensed and free evaluation instances; the instance need not be hosted by the system you are working on (although they must have network access to each other). For information on how to deploy each type of instance if you do not already have one to work with, see Deploying InterSystems IRISOpens in a new tab in InterSystems IRIS Basics: Connecting an IDE.

You will also need to obtain utility files for this guide from the GitHub repo https://github.com/intersystems/FirstLook-SQLBasicsOpens in a new tab. You should clone the repository to download the following files:

• stock_table_demo_one.sql, which contains SQL statements to create and load a small (20-row) table of stock data

• stock_table_demo_two.csv, which contains a million rows of stock table data

• Loader.xml, a class file that contains a utility method to load the data from stock_table_demo_two.csv into an InterSystems IRIS table

The FirstLook-SQLBasics sources must be accessible by the instance. The procedure for downloading the files depends on the type of instance you are using, as follows:

• If you are using an ICM-deployed instance:

  1. Use the icm sshOpens in a new tab command with the -machine and -interactive options to open your default shell on the node hosting the instance, for example:

     icm ssh -machine MYIRIS-AM-TEST-0004 -interactive
     

     

  2. On the Linux command line, use one of the following commands to clone the repo to the data storage volumeOpens in a new tab for the instance. For a configuration deployed on Azure, for example, the default mount pointOpens in a new tab for the data volume is /dev/sdd, so you would use commands like the following:

     $ git clone https://github.com/intersystems/FirstLook-SQLBasics /dev/sdd/FirstLook-SQLBasics
     OR
     $ wget -qO- https://github.com/intersystems/FirstLook-SQLBasics/archive/master.tar.gz | tar xvz -C /dev/sdd
     

     

  The files are now available to InterSystems IRIS in /irissys/data/FirstLook-SQLBasics on the container’s file system.

• If you are using a containerized instance (licensed or Community Edition) that you deployed by other means:

  1. Open a Linux command line on the host. (If you are using Community Edition on a cloud node, connect to the node using SSH, as described in Deploy and Explore InterSystems IRIS.)

  2. On the Linux command line, use either the git clone or the wget command, as described above, to clone the repo to a storage location that is mounted as a volume in the container.

     • For a Community Edition instance, you can clone to the instance’s durable %SYS directory (where instance-specific configuration data is stored). On the Linux file system, this directory is /opt/ISC/dur. This makes the files available to InterSystems IRIS in /ISC/dur/FirstLook-SQLBasics on the container’s file system.

     • For a licensed containerized instance, choose any storage location that is mounted as a volume in the container (including the durable %SYS directory if you use it). For example, if your docker run command included the option -v /home/user1:/external, and you clone the repo to /home/user1, the files are available to InterSystems IRIS in /external/FirstLook-SQLBasics on the container’s file system.

• If you are using an InterSystems Learning Labs instance:

  1. Open the command-line terminal in the integrated IDE.

  2. Change directories to /home/project/shared and use the git clone command to clone the repo:

     $ git clone https://github.com/intersystems/FirstLook-SQLBasics 
     

     

  The folder is added to the Explorer panel on the left under Shared, and the directory is available to InterSystems IRIS in /home/project/shared.

• If you are using an installed instance:

  • If the instance’s host is a Windows system with GitHub Desktop and GitHub Large File Storage installed:

    1. Go to https://github.com/intersystems/FirstLook-SQLBasicsOpens in a new tab in a web browser on the host.

    2. Select Clone or download and then choose Open in Desktop.

    The files are available to InterSystems IRIS in your GitHub directory, for example in C:\Users\User1\Documents\GitHub\FirstLook-SQLBasics.

  • If the host is a Linux system, simply use the git clone command or the wget command on the Linux command line to clone the repo to the location of your choice.

Creating and Populating a Table With a SQL Script File

For the purposes of this demo, we’ll use a SQL script file, stock_table_demo_one.sql, to create and load a table with a few rows of sample data.

To create and load the table:

1. Open the InterSystems Terminal using the procedure described for your instanceOpens in a new tab in InterSystems IRIS Basics: Connecting an IDE. You will see the following interactive prompt:

   USER>
   

   This prompt indicates that you are currently in the USER namespaceOpens in a new tab, which is empty by default and reserved for your use. From this prompt, you can execute ObjectScript.

2. Open the SQL Shell by entering

   DO $SYSTEM.SQL.Shell()
   

   at the prompt. This will display the following output:

   SQL Command Line Shell
   ----------------------------------------------
   The command prefix is currently set to: <<nothing>>.
   Enter q to quit, ? for help.
   [SQL]USER>>
   

3. Set the current SQL dialect to IRIS:

   SET DIALECT=IRIS
   

4. To run the statements in stock_table_demo_one.sql, enter the command

   RUN <Path>stock_table_demo_one.sql
   

   where Path is the location in which you placed the file (see Before You Begin). You are prompted to specify names for log files containing the statements in the file and their output, how to handle errors, and the statement delimiter. Accept all defaults.

   The statements create a table and insert 20 rows. The first few lines of the file are:

   CREATE TABLE FirstLook.StockTableDemoOne (ClientID INTEGER, BrokerID INTEGER,
      Symbol VARCHAR(10), TransactionType VARCHAR(4), TransactionDate TIMESTAMP, 
       Quantity INTEGER, Price DECIMAL(15,2), CommmissionRate DECIMAL(15,2))
   GO
   INSERT INTO FirstLook.StockTableDemoOne (ClientID, BrokerID, Symbol, 
      TransactionType, TransactionDate, Quantity, Price, CommmissionRate) 
      VALUES (29834783, 3103, 'RTYU', 'SELL', '2016-01-03', 342, 5.05, 3.25)
   GO
   
   

   As the script runs, you’ll see output after each SQL statement is processed:

   1. INSERT INTO FirstLook.StockTableDemoOne (ClientID, BrokerID, Symbol, 
   2.    TransactionType, TransactionDate, Quantity, 
   3.    Price, CommissionRate)
   4.    VALUES (92609349, 3103, 'HWVT', 'BUY',  '2017-10-25', 1500, 451.09, 3.25)
   1 Row Affected
   

   After all statements are processed, the SQL Shell lists the number of statements compiled as well as errors and warnings reported, and reports the elapsed time:

   Statements
   .................compiled: 21
   .....with errors reported: 0
   ...with warnings reported: 0
    
    Elapsed time: .125181 seconds
   

Running Queries Directly in SQL Shell

Now that you have a populated table, you can run queries against it. You can use single-line or multiline mode to do this, but may find the latter more convenient.

1. To enter multiline mode, press Enter at the prompt. You’ll see confirmation that you’re in multiline mode.

2. Enter the following SQL syntax, line by line. The keyword GO instructs the shell to execute the query and exit multiline mode:

   SELECT BrokerID, TO_CHAR((Quantity * Price),'9,999,999.99') as SubTotal, 
      TransactionDate FROM FirstLook.StockTableDemoOne
   WHERE TransactionType='SELL'
   ORDER BY SubTotal DESC
   GO
   
   

   The statement you entered will be echoed to the SQL Shell, and query results will follow.

   2.      SELECT BrokerID, TO_CHAR((Quantity * Price), '9,999,999.99') as SubTotal,
                 TransactionDate FROM FirstLook.StockTableDemoOne
           WHERE TransactionType='SELL'
           ORDER BY SubTotal DESC
    
   BrokerID        SubTotal          TransactionDate 
   5001       302,780.00   2017-11-06 09:51:24.735
   5002        92,350.00   2018-01-15 22:21:17.638
   3103        57,645.00   2017-09-24 19:36:43.079
   3103        45,015.00   2016-10-31 19:21:08.913
   5001        23,180.50   2017-07-31 23:05:49.83
   5001        13,113.60   2015-11-13 22:13:49.457
   5001        12,636.00   2015-10-13 05:50:23.209
   3103         1,727.10   2016-01-03 13:59:01.098
   1009         1,693.50   2016-01-15 18:18:15.346
   

   After the query results, you’ll see information on how long it took to prepare and execute the statements:

   9 Rows(s) Affected 
   statement prepare time(s)/globals/cmds/disk: 0.0625s/47683/263292/0ms 
   execute time(s)/globals/cmds/disk: 0.0006s/64/2903/0ms                           
   cached query class: %sqlcq.USER.cls47
   

   The preparation step includes the generation of executable code from the syntax of a SQL statement. This code is cached for re-use, so a statement is typically prepared fully only once. Subsequent preparations need only locate the cached code using a hash of the statement’s text.

   The execution step includes executing the code that was generated for a query and returning its results.

   Within each step’s listing are the following metrics:

   • The time each step took.

   • The count of globals, which is the number of references that were made to InterSystems IRIS storage to prepare or execute the SQL statement. For more information on globals, see the “Introduction to Globals” chapter of the Orientation Guide for Server-Side Programming.

   • The count of ObjectScript commands that were executed to prepare or execute the SQL statement.

   At the end of the display is the cached query class, which is the ObjectScript class that caches the code generated when the statement is first prepared.

3. Aggregate functions and GROUP BY are also available. Note that you can order by the alias used for the aggregate function:

   SELECT BrokerID, TO_CHAR(SUM(Quantity * Price), '9,999,999.99') as SubTotal
      FROM FirstLook.StockTableDemoOne
      GROUP BY BrokerID
      ORDER BY SubTotal DESC
      GO
   
   2.      SELECT BrokerID, TO_CHAR(SUM(Quantity * Price), '9,999,999.99') as SubTotal
           FROM FirstLook.StockTableDemoOne
           GROUP BY BrokerID
           ORDER BY SubTotal DESC
    
   BrokerID        SubTotal
   3103       868,993.60
   1009       808,453.50
   5001       593,242.82
   5002       187,560.00
    
   4 Rows(s) Affected
   statement prepare time(s)/globals/cmds/disk: 0.1665s/45832/237712/77ms
             execute time(s)/globals/cmds/disk: 0.0025s/122/2434/2ms
                             cached query class: %sqlcq.USER.cls9
   

Introductory Material

• Using InterSystems SQL

• InterSystems SQL Reference

• InterSystems SQL OverviewOpens in a new tab

SQL Development

• SQL – Things You Should KnowOpens in a new tab

• Developing with InterSystems Objects and SQLOpens in a new tabOpens in a new tab

Query Optimization

• First Look: Optimizing SQL Performance with InterSystems IRIS

• InterSystems SQL Optimization Guide

• Academy – Optimizing SQL PerformanceOpens in a new tab

• Optimizing SQL QueriesOpens in a new tab

Sharding and Scalability

• First Look: Scaling for Data Volume with Sharding

• Scalability Guide

SQL Search

• Searching Unstructured Text DataOpens in a new tab

• Using InterSystems SQL Search

• Creating iFind Indices for Searching Text FieldsOpens in a new tab

JDBC

• JDBC and InterSystems IRIS

• Using Java JDBC with InterSystems IRIS (documentation)

• Java OverviewOpens in a new tab

• Using JDBC with InterSystems IRISOpens in a new tab (online learning)