Thanks for your feedback!
Need to tell us more? Click here or use the Feedback button.

Is this page helpful?

Contents

Other Ways of Executing SQL
Invoking the SQL Shell
Storing and Recalling SQL Statements
Purging Cached Queries
Configuring the SQL Shell
SQL Metadata, Query Plan, and Performance Metrics
Transact-SQL Support

Using the SQL Shell Interface

One way to test SQL statements is to execute them from the InterSystems IRIS® data platform Terminal using the SQL Shell. This interactive SQL Shell allows you to execute SQL statements dynamically. The SQL Shell uses Dynamic SQL, which means that queries are prepared and executed at runtime. It accesses resources and performs operations within the current namespace.

Unless otherwise indicated, SQL Shell commands and SQL code are not case-sensitive.

Other Ways of Executing SQL

You can execute a single line of SQL code from the Terminal command line without invoking the SQL Shell by using the $SYSTEM.SQL.Execute()Opens in a new tab method. The following examples show how this method is used from the Terminal prompt:

USER>SET result=$SYSTEM.SQL.Execute("SELECT TOP 5 name,dob,ssn FROM Sample.Person")

USER>DO result.%Display()

USER>SET result=$SYSTEM.SQL.Execute("CALL Sample.PersonSets('M','MA')")

USER>DO result.%Display()

If the SQL statement contains an error, the Execute() method completes successfully; the %Display() method returns the error information, such as the following:

USER>DO result.%Display()

[SQLCODE: <-29>:<Field not found in the applicable tables>]
[%msg: < Field 'GAME' not found in the applicable tables^ SELECT TOP ? game ,>]
0 Rows Affected
USER>

The Execute() method also provides optional SelectMode, Dialect, and ObjectSelectMode parameters.

InterSystems IRIS supports numerous other ways to write and execute SQL code, as described in other chapters of this manual. These include:

Embedded SQL: SQL code embedded within ObjectScript code.
Dynamic SQL: using %SQL.StatementOpens in a new tab class methods to execute SQL statements from within ObjectScript code.
Management Portal SQL Interface: executing Dynamic SQL from the InterSystems IRIS Management Portal using the Execute Query interface.

Invoking the SQL Shell

You can use the $SYSTEM.SQL.Shell()Opens in a new tab method to invoke the SQL Shell from the Terminal prompt, as follows:

  DO $SYSTEM.SQL.Shell()

Alternatively, you can invoke the SQL Shell as an instantiated instance using the %SQL.ShellOpens in a new tab class, as follows:

  DO ##class(%SQL.Shell).%Go("IRIS")

  SET sqlsh=##class(%SQL.Shell).%New()
  DO sqlsh.%Go("IRIS")

Regardless of how invoked, the SQL Shell returns the SQL Shell prompt, which displays as follows:

[SQL]termprompt>>

Where [SQL] is a literal indicating that you are in the SQL Shell, termprompt is the configured terminal prompt, and >> is a literal indicating the SQL command line. By default, the SQL Shell prompt appears as follows [SQL]nsp>>, where nsp is the name of the current namespace.

At this prompt you can use either of the following Shell modes:

Single line mode: at the prompt type a line of SQL code. To end the SQL statement, press Enter. By default, this both prepares and executes the SQL code (this is known as Immediate execute mode). For a query, the result set is displayed on the terminal screen. For other SQL statements, the SQLCODE and row count values are displayed on the terminal screen.
Multiline mode: at the prompt press Enter. This puts you in multiline mode. You can type multiple lines of SQL code, each new line prompt indicating the line number. (A blank line does not increment the line number.) To conclude a multiline SQL statement, type GO and press Enter. By default, this both prepares and executes the SQL code. For a query, the result set is displayed on the terminal screen. For other SQL statements, the SQLCODE and row count values are displayed on the terminal screen.
Multiline mode provides the following commands, which you type at the multiline prompt and then press Enter: L or LIST to list all SQL code entered thus far. C or CLEAR to delete all SQL code entered thus far. C n or CLEAR n (where n is a line number integer) to delete a specific line of SQL code. G or GO to prepare and execute the SQL code and return to single line mode. Q or QUIT to delete all SQL code entered thus far and return to single line mode. These commands are not case-sensitive. Issuing a command does not increment the line number of the next multiline prompt. Typing ? at the multiline prompt lists these multiline commands.

To prepare an SQL statement, the SQL Shell first validates the statement, including confirming that the specified tables exist in the current namespace and the specified fields exist in the table. If not, it displays the appropriate SQLCODE.

The SQL Shell performs SQL privilege checking; you must have the appropriate privileges to access or modify a table, field, etc. For further details, refer to the “SQL Users, Roles, and Privileges” chapter of this manual.

If the statement is valid and you have appropriate privileges, the SQL Shell echoes your SQL statement, assigning a sequential number to it. These numbers are assigned sequentially for the duration of the terminal session, regardless of whether you change namespaces and/or exit and re-enter the SQL Shell. These assigned statement numbers permit you to recall prior SQL statements, as described below.

To list all the available SQL Shell commands, enter ? at the SQL prompt.

To terminate an SQL Shell session and return to the Terminal prompt, enter either the Q or QUIT command or the E or EXIT command at the SQL prompt. SQL Shell commands are not case-sensitive. The Ctrl-C command is disabled while in the SQL Shell.

The following is a sample SQL Shell session using the default parameter settings:

USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]USER>>SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
1. SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State

Name               Home_State
Djokovic,Josephine W.  AK
Klingman,Aviel P.      AK
Quine, Sam X.          AK
Xiang,Robert C.        AL
Roentgen,Alexandria Q. AR

5 Row(s) Affected
----------------------------------------------
[SQL]USER>>SELECT GETDATE()
2. SELECT GETDATE()

Expression_1
2009-09-29 11:41:42

1 Row(s) Affected
----------------------------------------------
[SQL]USER>>QUIT

USER>

The following is a multiline SQL Shell session using the default parameter settings:

USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]USER>>   << entering multiline statement mode >>
        1>>SELECT TOP 5
        2>>Name,Home_State
        3>>FROM Sample.Person
        4>>ORDER BY Home_State
        5>>GO

1. SELECT TOP 5
   Name,Home_State
   FROM Sample.Person
   ORDER BY Home_State

Name               Home_State
Djokovic,Josephine W.  AK
Klingman,Aviel P.      AK
Quine, Sam X.          AK
Xiang,Robert C.        AL
Roentgen,Alexandria Q. AR

5 Row(s) Affected
----------------------------------------------
[SQL]USER>>

GO Command

The SQL Shell GO command executes the most recent SQL statement. In single line mode, GO re-executes the SQL statement most recently executed. When in multiline mode, the GO command is used to execute the multiline SQL statement and exit multiline mode. A subsequent GO in single line mode re-executes the prior multiline SQL statement.

Input Parameters

The SQL Shell supports the use of input parameters using the “?” character in the SQL statement. Each time you execute the SQL statement, you are prompted to specify values for these input parameters. You must specified these values in the same sequence that the “?” characters appear in the SQL statement: the first prompt supplies a value to the first “?”, the second prompt supplies a value to the second “?”, and so on.

There is no limit on the number of input parameters. You can use input parameters to supply values to the TOP clause, the WHERE clause, and to supply expressions to the SELECT list; you cannot use input parameters to supply column names to the SELECT list.

You can specify a host variable as an input parameter value. At the input parameter prompt, specify a value prefaced by a colon (:). This value may be a public variable, an ObjectScript special variable, a numeric literal, or an expression. The SQL Shell then prompts you with “is this a literal (Y/N)?”. Specifying N (No) at this prompt (or just pressing Enter) means that the input value is parsed as a host variable. For example, :myval would be parsed as the value of the local variable myval; :^myval would be parsed as the value of the global variable ^myval; :$HOROLOG would be parsed as the value of the $HOROLOG special variable; :3 would be parsed as the number 3; :10-3 would be parsed as the number 7. Specifying Y (Yes) at this prompt means that the input value, including the colon preface, is supplied to the input parameter as a literal.

Executing ObjectScript Commands

Within the SQL Shell, you may wish to issue an ObjectScript command. For example, to change the InterSystems IRIS namespace by using the SET $NAMESPACE command to the namespace containing the SQL table or stored procedure you wish to reference. You can use the SQL Shell ! command or OBJ command to issue an ObjectScript command line, consisting of one or more ObjectScript commands. (OBJ is an abbreviation for OBJECTSCRIPT.) The !, OBJ, and OBJECTSCRIPT commands are synonyms. Use of these commands is shown in the following example:

%SYS>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]%SYS>>! SET oldns=$NAMESPACE SET $NAMESPACE="USER" WRITE "changed the namespace"
changed the namespace
[SQL]USER>>OBJ SET $NAMESPACE=oldns WRITE "reverted to old namespace"
reverted to old namespace
[SQL]%SYS>>

The rest of the command line following the OBJ command is treated as ObjectScript code. A space is not required between the ! and the ObjectScript command line. You can specify an OBJ command while in SQL Shell single-line mode or in SQL Shell multiline mode. The following example executes a SELECT query on a table defined in the USER namespace:

%SYS>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]%SYS>>  << entering multiline statement mode >>
        1>>OBJ SET $NAMESPACE="USER"

        1>>SELECT TOP 5 Name,Home_State
        2>>FROM Sample.Person
        3>>GO
   /* SQL query results */
[SQL]USER>>

Note that the OBJ statement does not advance the SQL line count.

In SQL Shell multiline mode, an OBJ command is executed upon line return, but an SQL statement is not issued until you specify GO. Thus, the following example is functionally identical to the previous example:

%SYS>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]%SYS>>  << entering multiline statement mode >>
        1>>SELECT TOP 5 Name,Home_State
        2>>FROM Sample.Person
        3>>OBJ SET $NAMESPACE="USER" WRITE "changed namespace"
changed namespace
        3>>GO
   /* SQL query results */
[SQL]USER>>

The following example uses an OBJ command to define a host variable:

USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]USER>>  << entering multiline statement mode >>
        1>>SELECT TOP :n Name,Home_State
        2>>FROM Sample.Person
        3>>OBJ SET n=5

        3>>GO

Browsing the Namespace

The SQL Shell supports a BROWSE command that displays the schemas, tables, and views defined in or accessible from the current namespace. The display consists of several levels of prompts. To return to the previous prompt level, press the Return key at a prompt. Names are case-sensitive.

Type BROWSE at the SQL Shell prompt to list the schemas in the current namespace.
At the Schema: prompt, select a schema by name or by number. This lists the tables and views in the schema.
At the Table/View: prompt, select a table (T) or view (V) by name or by number. This displays table information then presents a list of options.
At the Option: prompt, select an option by number. You can use this option to list the fields or maps defined for the table.
Specify option 1 (Fields by name) or option 2 (fields by number) to display the Field: prompt. Specify option 3 (maps) to display the Map: prompt.
At the Field: prompt, select a field by number or by name, or specify * to list all fields. This lists detailed field information.
At the Map: prompt, select a map by number or by name, or specify * to list all maps. This lists detailed map information.

CALL Command

You can use the SQL Shell to issue the SQL CALL statement to call an SQL stored procedure, as shown in the following example:

[SQL]USER>>CALL Sample.PersonSets('G','NY')

The SQL Shell issues an SQLCODE -428 error if the specified stored procedure does not exist in the current namespace.

The SQL Shell issues an SQLCODE -370 error if you specify more input parameters than are defined in the stored procedure. You can specify parameter values to the stored procedure using any combination of literals ('string'), host variables (:var), and input parameters (?).

You can use host variables in a CALL statement, as shown in the following example:
```
[SQL]USER>>OBJ SET a="G",b="NY"
[SQL]USER>>CALL Sample.PersonSets(:a,:b)
```
You can use input parameters (“?” characters) in a CALL statement, as shown in the following example:
```
[SQL]USER>>CALL Sample.PersonSets(?,?)
```
The SQL Shell prompts you for a value for each of these input parameters when the CALL statement is executed.

Executing an SQL Script File

The SQL Shell RUN command executes an SQL script file. The type of script file is determined by the DIALECT setting. The DIALECT default is IRIS (InterSystems SQL). For further details, see RUN Command later in this chapter.

Storing and Recalling SQL Statements

Recall by Number

The SQL Shell automatically stores each successful SQL statement issued during the terminal session in a local cache and assigns it a sequential number. These numbers are used for recalling prior SQL statements during the current Terminal process. SQL Shell only assigns numbers to SQL statements that are successful; if an error occurs during preparation of an SQL statement, no number is assigned. These number assignments are not namespace-specific. The following are the available recall by number commands:

#: You can use # to list all of the prior cached SQL statements with their assigned numbers.
#n: You can recall and execute a prior SQL statement by specifying #n at the SQL Shell prompt, where n is an integer that SQL Shell assigned to that statement.
#0: You can recall and execute the most recently prepared SQL statement by specifying #0 at the SQL Shell prompt. #0 recalls the most recently prepared SQL statement, not necessarily the most recently executed SQL statement. Therefore, recalling and executing SQL statements has no effect on which SQL statement is recalled by #0.

Recalling an SQL statement by number does not assign a new number to the statement. SQL Shell assigns numbers sequentially for the duration of the Terminal session; exiting and re-entering the SQL Shell or changing namespaces have no effect on number assignment or the validity of prior assigned numbers.

To delete all number assignments, use #CLEAR and confirm this action at the displayed prompt. This deletes all prior number assignments and restarts number assignment with 1.

Recall by Name

You can optionally assign a name to an SQL statement, then recall the statement by name. These names are used for recalling prior SQL statements issued from any of the current user's Terminal processes. There are two ways to save and recall an SQL statement by name:

Save to a global using SAVEGLOBAL; recall from a global using OPEN.
Save to a file using SAVE; recall from a file using LOAD.

Saving to a Global

To assign a global name to the most recent SQL statement, use the SQL Shell command SAVEGLOBAL name, which can be abbreviated as SG name. You can then use the SQL Shell command OPEN name to recall the SQL statement from the global. If EXECUTEMODE is IMMEDIATE, the SQL Shell both recalls and executes the statement. If EXECUTEMODE is DEFERRED, the statement will be prepared but will not be executed until you specify the GO command.

Each time you use OPEN name to recall an SQL statement by global name, the SQL Shell assigns a new number to the statement. Both the old and new numbers remain valid for recall by number.

A name can contain any printable characters except the blank space character. Letters in a name are case-sensitive. A name can be of any length. A name is specific to the current namespace. You can save the same SQL statement multiple times with different names; all of the saved names remain valid. If you attempt to save an SQL statement using a name already assigned, SQL Shell prompts you whether you wish to overwrite the existing name, reassigning it to the new SQL statement.

Global names are assigned for the current namespace. You can list all assigned global names for the current namespace using the SQL Shell L (or LIST) command. Once assigned, a name is available to all of the current user's Terminal processes. An assigned name persists after the Terminal process that created it has ended. If there are no name assignments, LIST returns a “No statements saved” message.

To delete a global name assignment, use CLEAR name. To delete all global name assignments for the current namespace, use CLEAR and confirm this action at the displayed prompt.

Saving to a File

To assign a file name to the most recent SQL statement, use the SQL Shell command SAVE name. You can then use the SQL Shell command LOAD name to recall the SQL statement. If EXECUTEMODE is IMMEDIATE, the SQL Shell both recalls and executes the statement. Each time you use LOAD name to recall an SQL statement by file name, the SQL Shell assigns a new number to the statement. Both the old and new numbers remain valid for recall by number.

Names are assigned for the current namespace. Once assigned, a name is available to all of the current user's Terminal processes. An assigned name persists after the Terminal process that created it has ended.

Purging Cached Queries

The SQL Shell provides a PURGE (abbreviated P) command to purge all cached queries in the current namespace. This command purges all cached queries in the namespace, not just those generated using the SQL Shell.

The $SYSTEM.SQL.Purge() methods and the Management Portal Actions drop-down list options provides you with more specific options to purge only selected cached queries or to purge all of the cached queries in the namespace.

Configuring the SQL Shell

You can configure the SQL Shell defaults system-wide using the Management Portal.
You can configure an individual SQL Shell using SQL Shell Parameters. Changing an SQL Shell parameter overrides the system-wide default for the current invocation of the SQL Shell; it does not change the system-wide SQL Shell default value.

The following are the available SQL Shell configuration options, the corresponding shell parameters, and the default settings:

Management Portal Shell Configuration	Shell Parameter	Default
Select Mode	selectmode	Logical
SQL Dialect (TSQL)	dialect (TSQL)	IRIS
Schema Search Path	path	none
Result Column Alignment	colalign	Delimiter
Command Prefix (TSQL)	commandprefix (TSQL)	none
Result Output Display Mode	displaymode	Current Device
Display Path	displaypath	none
Display File	displayfile	none
Display File Translate Table	displaytranslatetable	none
Echo Mode	echo	On
Execute Mode	executemode	Immediate
Messages Mode	messages	On
IF condition to allow execution		1
	log	Off

The parameters labelled (TSQL) are principally used for executing Sybase or MSSQL Transact-SQL code from the SQL Shell. They are described in the “Transact-SQL Support” section at the end of this chapter.

Configuring SQL Shell System-wide Defaults

Go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, SQL. Select the SQL Shell tab. View and set the current default settings for SQL Shell system-wide.

If you change one or more configuration settings, this is indicated by an asterisk (*) in the upper left-hand corner of the screen immediately following the Management Portal path. For example, System > Configuration > SQL *. Press the Save button to accept the changes. The changes are activated and the asterisk disappears.

Configuring Parameters for an SQL Shell

SQL Shell configuration parameters are specific to the current SQL Shell invocation on the current Terminal process. Settings apply across namespaces. However, if you exit the SQL Shell, all SQL Shell parameters reset to system-wide default values. InterSystems IRIS provides system default values; you can establish different default values for the current process using SET SAVE, as described below.

The SQL Shell SET command (with no arguments) displays the current shell configuration parameters, as shown in the following example. In this example, the SET shows the system default values, which are the values established when you invoke the SQL Shell:

[SQL]USER>>SET
 
commandprefix = ""
dialect = IRIS
displayfile =
displaymode = currentdevice
displaypath =
displaytranslatetable =
echo = on
executemode = immediate
log = off
messages = on
path = SQLUser
selectmode = logical
[SQL]USER>>

To display the current setting for a single configuration parameter, specify SET param. For example, SET SELECTMODE returns the current selectmode setting.

You can use the SQL Shell SET command to set a shell configuration parameter. A set value persists for the duration of the SQL Shell invocation; each time you invoke the SQL Shell, the parameters reset to default values. SET can use either of the following syntax forms:

SET param value SET param = value

Both param and value are not case-sensitive. Spaces are permitted, but not required, before and after the equal sign.

The SQL Shell SET SAVE command saves the current shell configuration parameter settings as the user defaults. These defaults are applied to all subsequent SQL Shell invocations from the current process. They are also applied as SQL shell defaults to any subsequently invoked SQL Shell on a Terminal process invoked by that user. They remain in effect until specifically reset. Using SET SAVE does not affect currently running SQL Shell invocations.

The SQL Shell SET CLEAR command clears (resets to system defaults) the current shell configuration parameter settings for the current process. InterSystems IRIS applies this reset to defaults to subsequent SQL Shell invocations by the current process, or any new Terminal process invoked by the current user. SET CLEAR does not affect currently running SQL Shell invocations.

Neither SET SAVE nor SET CLEAR change the system-wide SQL Shell default settings configured and displayed using the Management Portal.

Setting COLALIGN

You can use SET COLALIGN to specify the whitespace format used to display query resultset data and column headers. The available options are:

delimiter: Resultset header/data columns will be aligned based on the standard delimiter (tab). This is the default.
header: Resultset header/data columns will be aligned based on the length of the column header and the standard delimiter (tab).
data: Resultset header/data columns will be aligned based on the precision/length of the column data property and the standard delimiter (tab).

For further details, refer to the %Display() method in the “Using Dynamic SQL” chapter.

Setting DISPLAYMODE and DISPLAYTRANSLATETABLE

You can use SET DISPLAYMODE to specify the format used to display query data, as shown in the following example:

USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]USER>>SET DISPLAYMODE XML

displaymode = xml
[SQL]USER>>

The DISPLAYMODE default is CURRENTDEVICE, which displays the query data on the Terminal in TXT format. You can specify SET DISPLAYMODE = CUR to restore the CURRENTDEVICE default.

The other available options are TXT, HTML, PDF, XML, and CSV. The selection of a format determines the file type. InterSystems IRIS creates a file of this type, writes the query data to the file, and, when possible, launches the appropriate program to display this query data file. For all options except TXT, a second file is created to record result set messages. By default, SQL Shell creates these files in the InterSystems IRIS mgr\Temp\ directory and assigns a randomly generated file name with the appropriate file type suffix. The generated Message file name is the same as the data file name, except for the appended string “Messages”. For the HTML, PDF, and XML options, the Messages file has the same file type suffix as the query data file. For the CSV option, the Messages file has the TXT file type suffix.

The following is an example of the files created when DISPLAYMODE = TXT:

C:\InterSystems\IRIS\mgr\Temp\sGm7qLdVZn5VbA.txt
C:\InterSystems\IRIS\mgr\Temp\sGm7qLdVZn5VbAMessages.txt

Each time you run a query, the SQL Shell creates a new pair of files with randomly generated file names.

If DISPLAYMODE is TXT or CSV, you can optionally specify the name of a translate table to apply when performing format conversion. You can specify either SET DISPLAYTRANSLATE or SET DISPLAYTRANSLATETABLE. Translate table name values are case-sensitive.

If DISPLAYMODE is set to a value other than CURRENTDEVICE, any query result set data containing a control character results in a generated Warning message. Generally, control characters only appear in query result set data when it is in Logical mode. For example, data in a List structure contains control characters when displayed in Logical mode. For this reason, it is recommended that when you set DISPLAYMODE to a value other than CURRENTDEVICE that you also set SELECTMODE to either DISPLAY or ODBC.

Setting DISPLAYFILE and DISPLAYPATH

If DISPLAYMODE is set to a value other than CURRENTDEVICE, you can specify the target file location using the DISPLAYFILE and DISPLAYPATH parameters:

DISPLAYFILE: set this parameter to a simple file name with no suffix; for example, SET DISPLAYFILE = myfile. You can also set this parameter to a partially-qualified path, which InterSystems IRIS appends to the DISPLAYPATH value or the default directory, creating subdirectories as needed; for example, SET DISPLAYFILE = mydir\myfile. If DISPLAYPATH is set, the system creates a file with this file name in the specified directory; if DISPLAYPATH is not set, the system creates a file with this file name in the InterSystems IRIS mgr\Temp\ directory.
DISPLAYPATH: set this parameter to an existing fully-qualified directory path structure ending in a slash (“/”) or backslash (“\”), depending on operating system platform. If DISPLAYFILE is set, the system creates a file with the DISPLAYFILE name in this directory; if DISPLAYFILE is not set, the system creates a file with a randomly-generated name in this directory. If the DISPLAYPATH directory does not exist, InterSystems IRIS ignores DISPLAYPATH and DISPLAYFILE settings and instead uses the default directory and default randomly-generated file name.

When necessary, the system automatically adds a slash (or backslash) to the end of your DISPLAYPATH value and/or removes a slash (or backslash) from the beginning of your DISPLAYFILE value to create a valid fully-qualified directory path.

The following example sets DISPLAYMODE, DISPLAYFILE, and DISPLAYPATH:

[SQL]USER>>SET DISPLAYMODE XML

displaymode = xml
[SQL]USER>>SET DISPLAYFILE = myfile

displayfile = myfile
[SQL]USER>>SET DISPLAYPATH = C:\temp\mydir\

displaypath = C:\temp\mydir\
[SQL]USER>>

When you execute a query the SQL Shell will generate the following files. The first contains the query data. The second contains any messages resulting from the query execution:

C:\temp\mydir\myfile.xml
C:\temp\mydir\myfileMessages.xml

If you specify neither DISPLAYFILE or DISPLAYPATH, the system creates files in the Mgr\Temp\ directory for your InterSystems IRIS installation (for example, C:\InterSystems\IRIS\Mgr\Temp\) with a randomly generated file name.

If DISPLAYMODE is not set to CURRENTDEVICE, each time you run a query with DISPLAYFILE set, any existing data in the named file and the corresponding Messages file is replaced by the new query data. Each time you run a query with DISPLAYFILE not set, the SQL Shell creates a new file with a randomly generated file name and a new corresponding Messages file.

If DISPLAYMODE is set to CURRENTDEVICE, the DISPLAYFILE and DISPLAYPATH parameters have no effect.

Setting EXECUTEMODE

The SQL Shell supports immediate and deferred SQL statement execution. Immediate execution prepares and executes the specified SQL statement when you press Enter. Deferred execution prepares the statement when you press Enter, but does not execute it until you specify GO at the SQL prompt.

The available options are SET EXECUTEMODE IMMEDIATE (the default), SET EXECUTEMODE DEFERRED, and SET EXECUTEMODE to display the current mode setting. The following example sets the execute mode:

USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]USER>>SET EXECUTEMODE DEFERRED

Executemode = deferred
[SQL]USER>>

Deferred execution allows you to prepare multiple SQL queries, then recall them by name or number for execution. To execute a prepared SQL statement, recall the desired statement (from the appropriate namespace) then specify GO.

The following example shows the preparation of three queries in Deferred mode. The first two are saved and assigned a recall name; the third is not assigned a name, but can be recalled by number:

[SQL]USER>>SELECT TOP 5 Name,Home_State FROM Sample.Person
1. SELECT TOP 5 Name,Home_State FROM Sample.Person
[SQL]USER>>SAVE 5sample
Query saved as: 5sample
[SQL]USER>>SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
2. SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
[SQL]USER>>SAVE 5ordered
Query saved as: 5ordered
[SQL]USER>>SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
3. SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
[SQL]USER>>

The following example shows the deferred mode execution of two of the queries defined in the previous example. Note that this example recalls one query by name (upon recall the SQL Shell gives it a new number), and one query by number:

[SQL]USER>>OPEN 5ordered
SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
4. SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
----------------------------------------------
[SQL]USER>>GO

Name               Home_State
Djokovic,Josephine W.  AK
Klingman,Aviel P.      AK
Quine, Sam X.          AK
Xiang,Robert C.        AL
Roentgen,Alexandria Q. AR

5 Row(s) Affected
----------------------------------------------
[SQL]USER>>#3
SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
3. SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
----------------------------------------------
[SQL]USER>>GO
.
.
.

Setting ECHO

You can use SET ECHO to specify whether to echo the query results to the SQL Shell. If you specify SET ECHO=OFF, the query is prepared, a cached query is defined, and the query is executed. No query results are displayed to the Terminal. This is shown in the following example:

[SQL]USER>>set echo=off
 
echo = off
[SQL]USER>>SELECT Name,Age FROM Sample.MyTest
4.      SELECT Name,Age FROM Sample.MyTest
 
 
statement prepare time(s)/globals/cmds/disk: 0.0002s/5/155/0ms
          execute time(s)/globals/cmds/disk: 0.0001s/0/105/0ms
                          cached query class: %sqlcq.USER.cls3
---------------------------------------------------------------------------
[SQL]USER>>

If you specify SET ECHO=ON (the default) the query results are displayed to the Terminal. This is shown in the following example:

[SQL]USER>>set echo=on
 
echo = on
[SQL]USER>>SELECT Name,Age FROM Sample.MyTest
5.      SELECT Name,Age FROM Sample.MyTest
 
Name    Age
Fred Flintstone 41
Wilma Flintstone        38
Barney Rubble   40
Betty Rubble    42
 
4 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0002s/5/155/0ms
          execute time(s)/globals/cmds/disk: 0.0002s/5/719/0ms
                          cached query class: %sqlcq.USER.cls3
---------------------------------------------------------------------------
[SQL]USER>>

SET ECHO is only meaningful if DISPLAYMODE=CURRENTDEVICE (the default).

SET ECHO and SET MESSAGES specify what is displayed on the Terminal; they do not affect the prepare or execution of the query. If both SET MESSAGES=OFF and SET ECHO=OFF, the query is prepared, a cached query is created, and query execution creates a query result set, but nothing is returned to the Terminal.

Setting MESSAGES

You can use SET MESSAGES to specify whether to display the query error message (if unsuccessful), or query execution information (if successful):

If query execution is unsuccessful: If you specify SET MESSAGES=OFF, nothing is displayed to the Terminal. If you specify SET MESSAGES=ON (the default) the query error message is displayed, such as the following: ERROR #5540: SQLCODE: -30 Message: Table 'SAMPLE.NOTABLE' not found.
If query execution is successful: If you specify SET MESSAGES=OFF, only the query results and the line n Rows(s) Affected are displayed to the Terminal. If you specify SET MESSAGES=ON (the default) the query results and the line n Rows(s) Affected are followed by the statement prepare metrics, the statement execution metrics, and the name of the generated cached query.
Prepare and Execute metrics are measured in elapsed time (in fractional seconds), total number of global references, total number of commands executed, and disk read latency (in milliseconds).

The information displayed when SET MESSAGES=ON is not changed by setting DISPLAYMODE. Some DISPLAYMODE options create both a query result set file and a messages file. This messages file contains result set messages, not the query prepare and execute messages displayed to the Terminal when SET MESSAGES=ON.

SET MESSAGES and SET ECHO specify what is displayed on the Terminal; they do not affect the prepare or execution of the query. If both SET MESSAGES=OFF and SET ECHO=OFF, a successful query is prepared, a cached query is created, and query execution creates a query result set, but nothing is returned to the Terminal.

Setting LOG

You can use SET LOG to specify whether to log SQL Shell activity to a file. The available options are:

SET LOG OFF: The default. InterSystems IRIS does not log activity for the current SQL Shell.
SET LOG ON: InterSystems IRIS logs SQL Shell activity to the default log file.
SET LOG pathname: InterSystems IRIS logs SQL Shell activity to the file specified by pathname.

SET LOG ON creates a log file in IRIS\mgr\namespace, where namespace is the name of the current namespace for the process. This default log file is named xsqlnnnn.log, where nnnn is the process ID (pid) number for the current process.

By default, a log file is specific to the current process and the current namespace. To log SQL Shell activity from multiple processes and/or from multiple namespaces in the same log, specify SET LOG pathname for each process and/or namespace using the same pathname.

A log file can be suspended and resumed. Once a log file has been created, SET LOG OFF suspends writing to that log file. SET LOG ON resumes writing to the default log file. Log restarted: date time is written to the log file when logging resumes. SET LOG ON always activates the default log file. Thus, if you suspend writing to a specified pathname log file, you must specify SET LOG pathname when resuming.

Activating a log file creates a copy of SQL Shell activity displayed on the terminal; it does not redirect SQL Shell terminal output. The SQL Shell log records SQL errors for failed SQL execution and the SQL code and resulting row count for successful SQL execution. The SQL Shell log does not record result set data.

If a log is already active, specifying SET LOG ON has no effect. If a log is already active, specifying SET LOG pathname suspends the current log and activates the log specified by pathname.

Setting PATH

You can use SET PATH schema to set the schema search path, which SQL uses to supply the correct schema name for an unqualified table name. schema can be a single schema name, or a comma-separated list of schema names, as shown in the following example:

[SQL]USER>>SET PATH cinema,sample,user

SET PATH with no argument deletes the current schema search path, reverting to the system-wide default schema name.

If SET PATH schema is not specified, or the table is not found in the specified schemas, SQL Shell uses the system-wide default schema name. For further details on schema search paths, refer to the #sqlcompile path macro in the “ObjectScript Macros and the Macro Preprocessor” chapter of Using ObjectScript.

Setting SELECTMODE

You can use SET SELECTMODE to specify the mode used to display query data.

USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]USER>>SET SELECTMODE DISPLAY

selectmode = display
[SQL]USER>>

The available options are DISPLAY, LOGICAL, and ODBC. LOGICAL is the default. To determine the current mode, specify SET SELECTMODE without a value:

[SQL]USER>>SET SELECTMODE

selectmode = logical
[SQL]USER>>

%List data is encoded using non-printing characters. Therefore, when selectmode=logical, SQL Shell displays a %List data value as a $LISTBUILD statement, such as the following: $lb("White","Green"). Time data type data supports fractional seconds. Therefore, when selectmode=odbc, SQL Shell displays fractional seconds, which does not correspond to the ODBC standard. The actual ODBC TIME data type truncates fractional seconds.

For further details on SelectMode options, refer to “Data Display Options” in the “InterSystems IRIS SQL Basics” chapter of this book.

You can also use SET SELECTMODE to specify whether input data will be converted from display format to logical storage format. For this data conversion to occur, the SQL code must have been compiled with a select mode of RUNTIME. At execution time, SET SELECTMODE must be set to LOGICAL (the default). For further details, refer to the INSERT or UPDATE statement in the InterSystems SQL Reference.

SQL Metadata, Query Plan, and Performance Metrics

Displaying Metadata

The SQL Shell supports the M or METADATA commands to display metadata information about the current query.

For each result set item, this command lists the following metadata: Column Name (SQL field name), Type (ODBC data type integer code), Prec (precision or maximum length), Scale (maximum fractional digits), Null (boolean: 1=NULL allowed, 0=NULL not allowed), Label (header label, see column alias), Table (SQL table name), Schema (schema name), CType (client data type, see the %SQL.StatementColumnOpens in a new tab clientTypeOpens in a new tab property).

For further details, refer to Select-item Metadata in the “Using Dynamic SQL” chapter.

For further details on InterSystems SQL Shell commands, enter ? at the SQL prompt, or refer to %SYSTEM.SQL.Shell() in the InterSystems Class Reference.

SHOW STATEMENT

You can executing a query, then issue SHOW STATEMENT or SHOW ST to display the prepared SQL statement. By default, you must execute the query. You can avoid executing the query by setting executemode=deferred, issuing the query, then issuing the SHOW STATEMENT SQL Shell command.

SHOW STATEMENT information consists of the Implementation Class (cached query name), the Arguments (a comma-separated list of the actual argument values, such as the TOP clause and WHERE clause literal values), and the Statement Text (the literal text of the SQL command, including letter case and argument values).

EXPLAIN and Show Plan

There are two ways to display the query plan for an SQL query; both can display alternate query plans if desired.

EXPLAIN: preface a SELECT query with the EXPLAIN command. For example:

SQL]USER>>EXPLAIN SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'

SHOW PLAN: issue a query, then issue the SHOW PLAN Shell command. For example:

SQL]USER>>SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'
SQL]USER>>SHOW PLAN

The EXPLAIN SQL command displays query plan information about a specified SELECT query without executing the query. EXPLAIN ALT allows you to display alternate queries plans. EXPLAIN STAT returns performance statistics as well as the query plan(s). EXPLAIN can only be used to return a query plan for a SELECT query; it does not return a query plan for other commands such as INSERT, UPDATE, or DELETE statements that perform a query operation.

The SHOW PLAN SQL Shell command allows you to display the query plan information for the last query successfully issued by the SQL Shell. SHOW PLAN can be used for any SQL command that performs a query operation, including SELECT, INSERT, UPDATE, and DELETE. By default, you must execute the query. You can avoid executing the query by setting executemode=deferred, issuing the query, then issuing one of the following SQL Shell commands:

SHOW PLAN, SHOW PL (or simply SHOW) to display query plan information about the current query. The query plan can be used for debugging and optimizing the performance of a query. It specifies how the query executes, including the use of indexes and a cost value for the query. A query plan can be returned for the following statements: SELECT, DECLARE, non-cursor UPDATE or DELETE, and INSERT...SELECT. This command has a V (VERBOSE) option.
SHOW PLANALT to display alternate show plans for the current query. This command has a V (VERBOSE) option. For further details, refer to Alternate Show Plans in the SQL Optimization Guide.

For further details on InterSystems SQL Shell commands, enter ? at the SQL prompt, or refer to %SYSTEM.SQL.Shell() in the InterSystems Class Reference.

You can generate query plans from ObjectScript using the $SYSTEM.SQL.Explain()Opens in a new tab method. For further details refer to Query Execution Plans in the SQL Optimization Guide.

For further details on interpreting a query plan, see “Interpreting an SQL Query Plan” in the SQL Optimization Guide.

SQL Shell Performance

Following the successful execution of an SQL statement, the SQL Shell displays four statement prepare values (times(s)/globals/cmds/disk) and four statement execute values (times(s)/globals/cmds/disk):

The statement prepare time is the time it took to prepare the dynamic statement. This includes the time it took to generate and compile the statement. It includes the time it took to find the statement in the statement cache. Thus, if a statement is executed, then recalled by number or recalled by name, the prepare time on the recalled statement is near zero. If a statement is prepared and executed, then re-executed by issuing the GO command, the prepare time on the re-execution is zero.
The elapsed execute time is the elapsed time from the call to %Execute() until the return from %Display(). It does not include wait time for input parameter values.

The statement globals is the count of global references, cmds is the count of SQL commands executed, and disk is the disk latency time in milliseconds. The SQL Shell keeps separate counts for the Prepare operation and the Execute operation.

These performance values are only displayed when DISPLAYMODE is set to currentdevice, and MESSAGES is set to ON. These are the SQL Shell default settings.

Transact-SQL Support

By default, the SQL Shell executes InterSystems SQL code. However, the SQL Shell can be used to execute Sybase or MSSQL code.

Setting DIALECT

By default, the SQL Shell parses code as InterSystems SQL. You can use SET DIALECT to configure the SQL Shell to execute Sybase or MSSQL code. To change the current dialect, SET DIALECT to Sybase, MSSQL, or IRIS. The default is Dialect=IRIS. These SET DIALECT options are not case-sensitive.

he following is an example of the executing a MSSQL program from the SQL Shell:

USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]USER>>SET DIALECT MSSQL

dialect = MSSQL
[SQL]USER>>SELECT TOP 5 name + '-' + ssn FROM Sample.Person
1.      SELECT TOP 5 name + '-' + ssn FROM Sample.Person
 
Expression_1
Zweifelhofer,Maria H.-559-20-7648
Vonnegut,Bill A.-552-41-2071
Clinton,Terry E.-757-30-8013
Bachman,Peter U.-775-59-3756
Avery,Emily N.-833-18-9563
 
5 Rows(s) Affected
statement prepare time: 0.2894s, elapsed execute time: 0.0467s.
---------------------------------------------------------------------------
[SQL]USER>>

The Sybase and MSSQL dialects support a limited subset of SQL statements in these dialects. They support the SELECT, INSERT, UPDATE, and DELETE statements. They support the CREATE TABLE statement for permanent tables, but not for temporary tables. CREATE VIEW is supported. CREATE TRIGGER and DROP TRIGGER are supported. However, this implementation does not support transaction rollback should the CREATE TRIGGER statement partially succeed but then fail on class compile. CREATE PROCEDURE and CREATE FUNCTION are supported.

Setting COMMANDPREFIX

You can use SET COMMANDPREFIX to specify a prefix (usually a single character) that must be appended to subsequent SQL Shell commands. This prefix is not used on SQL statements issued from the SQL Shell prompt. The purpose of this prefix is to prevent ambiguity between SQL Shell commands and SQL code statements. For example, SET is an SQL Shell command; SET is also an SQL code statement in Sybase and MSSQL.

By default, there is no command prefix. To establish a command prefix, SET COMMANDPREFIX=prefix, with prefix specified without quotation marks. To revert to having no command prefix, SET COMMANDPREFIX="". The following example shows the command prefix / (the slash character) being set, used, and reverted:

USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]USER>>SET COMMANDPREFIX=/
 
commandprefix = /
[SQL]USER>>/SET LOG=ON
 
log = xsql4148.log
[SQL]USER>>  << entering multiline statement mode >>
        1>>SELECT TOP 3 Name,Age
        2>>FROM Sample.Person
        3>>/GO
9.      SELECT TOP 3 Name,Age
        FROM Sample.Person
 
Name    Age
Frith,Jose M.   13
Finn,William D. 15
Ximines,Uma Y.  44
 
3 Rows(s) Affected
statement prepare time: 0.0010s, elapsed execute time: 0.0014s.
---------------------------------------------------------------------------
[SQL]USER>>/SET COMMANDPREFIX
 
commandprefix = /
[SQL]USER>>/SET COMMANDPREFIX=""
 
commandprefix = ""
[SQL]USER>>SET COMMANDPREFIX
 
commandprefix =
[SQL]USER>>

When a command prefix is set, the command prefix is required for all SQL Shell commands, except ?, #, and GO; these three SQL Shell commands can be issued with or without the command prefix.

The SQL Shell displays the current command prefix as part of the SQL Shell initialization, when you issue a SET or a SET COMMANDPREFIX command, and at the end of the ? commands option display.

RUN Command

The SQL Shell RUN command executes an SQL script file. You must SET DIALECT before issuing a RUN command to specify either IRIS (InterSystems SQL), Sybase (Sybase TSQL), or MSSQL (Microsoft SQL); the default dialect is IRIS. You can either invoke RUN scriptname or just invoke RUN and be prompted for the script file name.

RUN loads the script file, then prepares and executes each statement contained in the file. Statements in the script file must be delimited, usually either with a GO line, or with a semicolon (;). The RUN command prompts you to specify the delimiter.

The SQL script file results are displayed on the current device and, optionally, in a log file. Optionally, a file containing statements that failed to prepare can be produced.

The RUN command returns prompts to specify these options, as shown in the following example:

[SQL]USER>>SET DIALECT=Sybase

dialect = Sybase
[SQL]USER>>RUN
 
Enter the name of the SQL script file to run: SybaseTest
 
Enter the file name that will contain a log of statements, results and errors (.log): SyTest.log
     SyTest.log
 
Many script files contain statements not supported by IRIS SQL.
Would you like to log the statements not supported to a file so they
can be dealt with manually, if applicable?   Y=> y
Enter the file name in which to record non-supported statements (_Unsupported.log): SyTest_Unsupported.log
 
Please enter the end-of-statement delimiter (Default is 'GO'):  GO=>
 
Pause how many seconds after error?   5 => 3
 
Sybase Conversion Utility (v3)
Reading source from file:
Statements, results and messages will be logged to: SyTest.log
.
.
.

TSQL Examples

The following SQL Shell example creates a Sybase procedure AvgAge. It executes this procedure using the Sybase EXEC command. It then changes the dialect to InterSystems IRIS and executes the same procedure using the InterSystems SQL CALL command.

[SQL]USER>>SET DIALECT Sybase
 
dialect = Sybase
[SQL]USER>>  << entering multiline statement mode >>
        1>>CREATE PROCEDURE AvgAge
        2>>AS SELECT AVG(Age) FROM Sample.Person
        3>>GO
12.     CREATE PROCEDURE AvgAge
        AS SELECT AVG(Age) FROM Sample.Person
 
 
statement prepare time: 0.1114s, elapsed execute time: 0.4364s.
---------------------------------------------------------------------------
[SQL]USER>>EXEC AvgAge
13.     EXEC AvgAge
 
 
 
Dumping result #1
Aggregate_1
44.35
 
1 Rows(s) Affected
statement prepare time: 0.0956s, elapsed execute time: 1.1761s.
---------------------------------------------------------------------------

[SQL]USER>>SET DIALECT=IRIS
 
dialect = IRIS
[SQL]USER>>CALL AvgAge()
14.     CALL AvgAge()
 
 
 
Dumping result #1
Aggregate_1
44.35
 
1 Rows(s) Affected
statement prepare time: 0.0418s, elapsed execute time: 0.0040s.
---------------------------------------------------------------------------
[SQL]USER>>

Using the Management Portal SQL Interface

Using Dynamic SQL