InterSystems IRIS Data Platform 2019.2  /  Using InterSystems SQL

Using InterSystems SQL
Using the Management Portal SQL Interface
Previous section           Next section
InterSystems: The power behind what matters   

This chapter describes how to perform SQL operations from the InterSystems IRIS Data Platform™ Management Portal. The Management Portal interface uses Dynamic SQL, which means that queries are prepared and executed at runtime. The Management Portal interface is intended as an aid for developing and testing SQL code against small data sets. It is not intended to be used as an interface for SQL execution in a production environment.
The Management Portal also provides various options to configure SQL. For further details, refer to SQL configuration settings described in Advanced Configuration Settings Reference.
For general information on using the Management Portal, select the Help button found in the upper left corner. You can report an issue with InterSystems software to the InterSystems Worldwide Response Center (WRC) from the Management Portal by using the Contact button found in the upper left corner. To report an SQL performance issue to the WRC, refer to the Tools section of this chapter.
Management Portal SQL Facilities
InterSystems IRIS allows you to examine and manipulate data using SQL tools from the InterSystems IRIS Management Portal. The starting point for this is the Management Portal System Explorer option. From there you select the SQL option. This displays the SQL interface, which allow you to:
Selecting a Namespace
All SQL operations occur within a specific namespace. Therefore, you must first specify which namespace you wish to use by clicking the Switch option at the top of the SQL interface page. This displays the list of available namespaces, from which you can make your selection.
You can set your Management Portal default namespace. From the Management Portal select System Administration, Security, Users. Click the name of the desired user. This allows you to edit the user definition. From the General tab, select a Startup Namespace from the drop-down list. Click Save. If no startup namespace is selected, it defaults to %SYS.
User Customization
Many of the Management Portal SQL operations are automatically customized for each user. If you set a filter, maximum, mode, or other option in the Execute Query tab or the SQL Statements tab, this user-specified value is retained for future use. When the same user activates the Management Portal, the user’s prior settings are shown. Restarting InterSystems IRIS returns all options to default values.
Namespace selection is not customized. It reverts to the user definition Startup Namespace.
For details on using Filter options, see Filtering Schema Contents.
Executing SQL Query
From the Management Portal select System Explorer, then SQL. Select a namespace with the Switch option at the top of the page; this displays the list of available namespaces. To execute an SQL query, there are three options:
Writing SQL Statements
The Execute Query text box allows you to write not only SELECT and CALL queries, but most SQL statements, including DDL statements such as CREATE TABLE, and DML statements such as INSERT, UPDATE, and DELETE.
You can specify SQL code in the Execute Query text box using the following:
SQL code in the Execute Query text box can include:
After writing SQL code in the text box, you can click the Show Plan button to check the SQL code without executing the SQL code. If the code is valid, Show Plan displays a Query Plan. If the code is invalid, Show Plan displays an SQLCODE error value and message. You can also use the Show Plan button to display this information for the most-recently-executed SQL code.
To execute the SQL code, click the Execute button.
Table Drag and Drop
You can generate a query by dragging a table (or view) from the Tables list (or Views list) on the left side of the screen and dropping it into the Execute Query text box. This generates a SELECT with a select-item list of all of the non-hidden fields in the table and a FROM clause specifying the table. You can then further modify this query and execute it using the Execute button.
You can also drag and drop a procedure name from the Procedures list on the left side of the screen.
Execute Query Options
The SQL execution interface has the following options:
If you click the more option, the SQL execution interface displays the following additional options:
All of these options are user customized.
Show Plan Button
The Show Plan button displays the Statement Text and the Query Plan including the relative cost (overhead) of the query that is currently in the page’s text box. You can invoke Show Plan from either the Execute Query or Show History interface. A query plan is generated when a query is Prepared (compiled); this occurs when you write a query and select the Show Plan button. You do not have to execute a query to show its query plan. Show Plan displays an SQLCODE and error message when invoked for an invalid query.
SQL Statement Results
After writing SQL code in the Execute Query text box, you can execute the code by clicking the Execute button. This either successfully executes the SQL statement and displays the results below the code window, or the SQL code fails and it displays an error message (in red) below the code window.
Query Data Display
The result set is returned as a table with a row counter displayed as the first column (#), if the Row Number box is checked. The remaining columns are displayed in the order specified. The RowID (ID field) may be displayed or hidden. Each column is identified by the column name (or the column alias, if specified). An aggregate, expression, subquery, host variable, or literal SELECT item is either identified by the word Aggregate_, Expression_, Subquery_, HostVar_, or Literal_ followed by the SELECT item sequence number (by default), or by a column alias (if specified).
If a row column contains no data (NULL) the result set displays a blank table cell. Specifying an empty string literal displays a HostVar_ field with a blank table cell. Specify NULL displays a Literal_ field with a blank table cell.
If a selected field is a date, time, timestamp, or %List-encoded field, the displayed value depends on the Display Mode.
The following display features are unique to the Management Portal SQL interface Execute Query results display and Open Table data display:
These result display features do not occur when the same query is executed using Dynamic SQL code, the SQL Shell, or Embedded SQL code.
Query Execution Metrics
If successful, Execute Query displays performance information and the name of the cached query routine. If there is resulting data to display, this appears below the performance information. The execution information includes the Row count, the Performance, the Cached Query showing the cached query name, and Last update specifying the timestamp for the last execution of the query.
If unsuccessful, Execute Query displays an error message. You can click the Show Plan button to display the corresponding SQLCODE error value and message.
Show History
Click Show History to list prior SQL statements executed during the current session. Show History lists all SQL statements invoked from this interface, both those successfully executed and those whose execution failed. By default, SQL statements are listed by Execution Time, with the most recently executed appearing at the top of the list. You can click on any of the column headings to order the SQL statements in ascending or descending order by column values. Executing an SQL Statement from the Show History listing updates its Execution Time (local date and time stamp), and increments its Count (number of times executed).
You can filter the Show History listing, as follows: in the Filter box specify a string then press the Tab key. Only those history items that contain that string will be included in the refreshed listing. The filter string can either be a string found in the SQL Statement column (such as a table name), or it can be a string found in the Execution Time column (such as a date). The filter string is not case-sensitive. A filter string remains in effect until you explicitly change it.
You can modify and execute an SQL statement from Show History by selecting the statement, which causes it to be displayed in the Execute Query text box. In Execute Query you can modify the SQL code and then click Execute. Making any change to an SQL statement retrieved from Show History causes it to be stored in Show History as a new statement; this include changes that do not affect execution, such as changing letter case, whitespace, or comments. Whitespace is not shown in Show History, but it is preserved when an SQL statement is retrieved from Show History.
You can execute (re-run) an unmodified SQL statement directly from the Show History list by clicking the Execute button found to the right of the SQL statement in the Show History listing.
Note that the Show History listing is not the same as the list of cached queries. Show History lists all invoked SQL statements from the current session, including those that failed during execution.
Other SQL Interfaces
InterSystems IRIS supports numerous other ways to write and execute SQL code, as described in other chapters of this manual. These include:
Filtering Schema Contents
The left side of the Management Portal SQL interface allows you to view the contents of a schema (or multiple schemas that match a filter pattern).
  1. Specify which namespace you wish to use by clicking the Switch option at the top of the SQL interface page. This displays the list of available namespaces, from which you can make your selection.
  2. Apply a Filter or select a schema from the Schema drop-down list.
    You can use the Filter field to filter the lists by typing a search pattern. You can filter for schemas, or for table/view/procedure names (items) within a schema or within multiple schemas. A search pattern consists of the name of a schema, a dot (.), and the name of an item — each name composed of some combination of literals and wildcards. Literals are not case-sensitive. The wildcards are:
    For example, S* returns all schemas that begin with S. S*.Person returns all Person items in all schemas that begin with S. *.Person* returns all items that begin with Person in all schemas. You can use a comma-separated list of search patterns to select all items that fulfil any one of the listed patterns (OR logic). For example, *.Person*,*.Employee* selects all Person and Employee items in all schemas.
    To apply a Filter search pattern, click the refresh button, or press the Tab key.
    A Filter search pattern remains in effect until you explicitly change it. The “x” button to the right of the Filter field clears the search pattern.
  3. Selecting a schema from the Schema drop-down list overrides and resets any prior Filter search pattern, selecting for a single schema. Specifying a Filter search pattern overrides any prior Schema.
  4. Optionally, use the drop-down “applies to” list to specify which categories of item to list: Tables, Views, Procedures, Cached Queries, or all of the above. The default is All. Any category that was specified in the “applies to” drop-down list is limited by Filter or Schema. Those categories not specified in “applies to” continue to list all of the items of that category type in the namespace.
  5. Optionally, click the System check box to include system items (items whose names begin with %). The default is to not include system items.
  6. Expand the list for a category to list its items for the specified Schema or specified Filter search pattern. When you expand a list, any category that contains no items does not expand.
  7. Click on an item in an expanded list to display its Catalog Details on the right side of the SQL interface.
    If the selected item is a Table or a Procedure, the Catalog Details Class Name information provides a link to the corresponding Class Reference documentation.
Note that Filter settings are user customized, and are retained for future use for that user.
Browse Tab
The Browse tab provides a convenient way to quickly view all the schemas in a namespace, or a filtered subset of the schemas in the namespace. You can select Show All Schemas or Show Schemas with Filter, which applies the filter specified on the left side of the Management Portal SQL interface. By clicking on the Schema Name heading, you can list the schemas in ascending or descending alphabetical order.
Each listed schema provides links to lists of its associated Tables, Views, Procedures, and Queries (cached queries). If the schema has no items of that type, a hyphen (rather than a named link) is shown in that schema list column. This enables you to quickly get information about the contents of schemas.
Clicking a Tables, Views, Procedures, or Queries link displays a table of basic information about those items. By clicking on a table heading, you can sort the list by that column’s values in ascending or descending order. The Procedures table always includes Extent procedures, regardless of the Procedures setting on the left side of the Management Portal SQL interface.
You can get more information on individual Tables, Views, Procedures, and Cached Queries using the Catalog Details tab. Selecting a Table or View from the Browse tab does not activate the Open Table link for that table.
Catalog Details
The Management Portal provides Catalog Details information for each Table, View, Procedure, and Cached Query. The filtering schema contents (left side) component of the Management Portal SQL interface allows you to select an individual item to display its Catalog Details.
Catalog Details for a Table
The following Catalog Details options are provided for each table:
Catalog Details for a View
Management Portal SQL interface also provides Catalog Details for views, procedures, and cached queries:
The following Catalog Details options are provided for each view:
Catalog Details for a Stored Procedure
The following Catalog Details options are provided for each procedure:
Catalog Details for a Cached Query
Cached Query provides the full text of the query, an option to show the query execution plan, and an option to interactively execute the cached query.
Open Table
If you select a table or view on the left side of the Management Portal SQL interface, the Catalog Details for that table or view are displayed. The Open Table link at the top of the page also becomes active. Open Table displays the actual data in the table (or accessed via the view). The data is shown in Display format.
By default, the first 100 rows of data are displayed; this default is modifiable by setting the Number of rows to load when table is opened in the Catalog Details tab Table Info. If there are more rows in the table than this number of rows to load value, the More data... indicator is shown at the bottom of the data display. If there are fewer rows in the table than this number of rows to load value, the Complete indicator is shown at the bottom of the data display.
If the data in a column is too long to be displayed, the first 100 characters of the data for that column are displayed followed by an ellipsis (...) indicating additional data.
A column of data type %Stream.GlobalCharacter displays the actual data (up to 100 characters) as a string. A column of data type %Stream.GlobalBinary displays as <binary>.
The System Explorer, SQL, Tools drop-down list provides access to the following tools. These are the same tools available from System Explorer, Tools, SQL Performance Tools:

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-08-16 05:39:10