Skip to main content

Using the Management Portal SQL Interface

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 the SQL and Object Settings Pages listed in System Administration Guide.

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)Opens in a new tab from the Management Portal by using the Contact button found in the top right 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:

  • Execute SQL Query — write and run SQL commands. You can execute an SQL query against existing tables and data, create a table, or insert, update, or delete table data. You can either write the SQL code directly into a text box (including SELECT, INSERT, UPDATE, DELETE, CREATE TABLE and other SQL statements), retrieve a statement from the SQL history into the text box, drag and drop a table into the text box to generate a query (SELECT statement), or compose a query (SELECT statement) using the Query Builder interface.

  • Filtering Schema Contents — on the left side of the screen display the SQL schemas for the current namespace or a filtered subset of these schemas, with each schema’s tables, views, procedures, and cached queries. You can select an individual table, view, procedure, or cached query to display its Catalog Details.

  • Wizards — execute a wizard to perform data import, data export, or data migration. Execute a wizard to link to tables or views or to link to stored procedures.

  • Actions — define a view; print out the details of a table definition; improve the performance of a query by running Tune Table and/or rebuilding indices; or perform clean up by purging unwanted cached queries and/or dropping unwanted table, view, or procedure definitions.

  • Open Table — display the current data in the table in Display mode. This is commonly not the complete data in the table: both the number of records and the length of data in a column are restricted to provide a manageable display.

  • Tools — execute one of the following tools: SQL Runtime Statistics, Index Analyzer, Alternate Show Plans, Generate Report, Import Report.

  • Documentation — Allows you to view the list of SQL error codes and the list of SQL reserved words. If you select a table, allows you display Class Documentation (the Class Reference page for that table).

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:

  • Execute Query: write and execute an SQL command. The SQL command can be a SELECT query, or it can be an InterSystems SQL DDL or DML statement; the statement is validated on the InterSystems IRIS server when it executes.

  • Show History: recall a previously run SQL statement, and either re-run it, or modify it and then run it. All executed statements are listed, including those that did not successfully execute.

  • Query Builder: invoke the SQL Query Builder (which is exclusively for creating SELECT statements). Within the SQL Query Builder, create an SQL SELECT query by choosing tables, columns, WHERE clause predicates, and other query components. You can then run the query by clicking Execute Query.

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:

  • Type (or paste) the SQL code into the text box. The SQL code area does not colorize SQL text or provide any syntax or existence validation. However, it does provide automatic spelling verification. You can erase the contents of the text box using the X icon.

  • Use the Show History list to select a prior SQL statement. The selected statement is copied into the text box. Upon execution, this statement moves to the top of the Show History list. Note that Show History lists all previously executed statements, including those that failed execution.

  • Use Table Drag and Drop to construct SQL code in the text box.

  • You can use the Query Builder, rather than the Execute Query text box, to specify and execute a SELECT query. A SELECT query executed using Query Builder is not shown in Execute Query or listed in Show History.

SQL code in the Execute Query text box can include:

  • ? Input Parameters. If you specify input parameters, such as TOP ? or WHERE Age BETWEEN ? AND ?, the Execute button displays the Enter Parameter Value for Query window, with entry fields for each input parameter in the order specified in the query. For further details on ? input parameters, refer to Executing an SQL Statement in the “Using Dynamic SQL” chapter of this manual.

  • Whitespace Characters. You can specify multiple blank spaces, single and multiple line returns. The tab key is disabled; when copying code into the SQL code area, existing tabs are converted to single blank spaces. Line returns and multiple blank spaces are not retained.

  • Comments. The SQL code area supports single-line and multiline comments. Comments are retained and shown in the Show History display. Comments are not shown in the Show Plan Statement Text display or in cached queries.

  • Queries that Return Multiple Result Sets.

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:

  • The Select Mode drop-down list with a SELECT specifies the format that the query should use to supply data values (for example, in the WHERE clause) and to display data values in the query result set. The options are Display Mode (the default), ODBC Mode, and Logical Mode. For further details on these options, refer to “Data Display Options” in the “InterSystems IRIS Basics” chapter of this book.

    The Select Mode drop-down list with an INSERT or UPDATE allows you 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, the Select Mode drop-down list must be set to Logical Mode. For further details, refer to the INSERT or UPDATE statement in the InterSystems SQL Reference.

    Select Mode is meaningful for data types whose Logical storage format differs from the desired display format (Display or ODBC), such as InterSystems IRIS dates and times and ObjectScript %List structured data.

  • The Max field allows you to limit how many rows of data to return from a query. It can be set to any positive integer, including 0. Once you set Max, that value is used for all queries for the duration of the session, unless explicitly changed. The default is 1000. The maximum value is 100,000, which is the default if you enter no value (set Max to null), enter a value greater than 100,000, or a non-numeric value. You can also limit the number of rows of data to return by using a TOP clause. Max has no effect on other SQL statements, such as DELETE.

If you click the more option, the SQL execution interface displays the following additional options:

  • Dialect: the dialect of SQL code. Available values are IRIS, Sybase, and MSSQL. The default is IRIS. Sybase and MSSQL are described in the InterSystems Transact-SQL (TSQL) Migration Guide . Note that the dialect you select becomes the user customized default the next time you access the Management Portal.

    If Dialect is Sybase or MSSQL, you can specify multiple SQL commands in the Execute Query text box. All insert, delete, and update commands are executed first, then SELECT commands in the order specified. Multiple result sets are returned in separate tabs.

  • Row Number: a check box specifying whether to include a row count number for each row in the result set display. Row Number is a sequential integer assigned to each row in the result set. This is simply a numbering of the returned rows, it does not correspond to either the RowID or the %VID. The row number column header name is #. The default is to not display row numbers.

  • Execute Query in the foreground: a check box specifying whether or not to run the query in the foreground. Simple queries run in the foreground are often significantly faster than those run in the background. However, long queries run in the foreground may cause the Management Portal to be unresponsive during query execution. The default is to run all queries in the background.

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 current query plan for the query 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. If the SQL code fails, it displays an error message (in red) below the code window; pressing the Show Plan button displays the SQLCODE error and error message.

Execute Query SQL code execution is performed as a background process. While the code is executing, the Execute button is replaced by a Cancel button. This allows you to cancel execution of a long-running query.

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 a column alias (if specified), or by the word Aggregate_, Expression_, Subquery_, HostVar_, or Literal_ followed by the SELECT item sequence number (by default).

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:

  • A stream field of data type %Stream.GlobalCharacterOpens in a new tab displays the actual data (up to 100 characters) as a string. If the data in a stream field is longer than 100 characters, the first 100 characters of the data are displayed followed by an ellipsis (...) indicating additional data.

  • A stream field of data type %Stream.GlobalBinaryOpens in a new tab displays as <binary>.

  • A string data field displays the actual data in full with line wrapping as needed.

  • An integer field is right-aligned within the result table cell. RowID, numeric, and all other fields are left-aligned.

These result display features do not occur when the same query is executed using Dynamic SQL code, the SQL Shell, or Embedded SQL code.

If the specified query returns more than one result set, Execute Query displays these result sets as named tabs: Result #1, Result #2, and so forth.

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.

  • Row count: For a DDL statement such as CREATE TABLE, displays Row count: 0 if the operation was successful. For a DML statement such as INSERT, UPDATE, or DELETE, displays the number of rows affected.

    For a SELECT, displays the number of rows returned as a result set. Note that the number of rows returned is governed by the Max setting, which may be lower than the number of rows which could have been selected. For multiple result sets, the number of rows for each result set are listed, separated by the / character. A query that specifies one or more aggregate functions (and no selected fields) always displays Row count: 1 and returns the results of expressions, subqueries, and aggregate functions, even if the FROM clause table contains no rows. A query that specifies no aggregate functions and selects no rows always displays Row count: 0 and returns no results, even if the query specifies only expressions and subqueries that do not reference the FROM clause table. A query with no FROM clause always displays Row count: 1 and returns the results of expressions, subqueries, and aggregate functions.

  • Performance: measured in elapsed time (in fractional seconds), total number of global references, total number of commands executed, and disk read latency (in milliseconds). If a cached query exists for the query these performance metrics are for executing the cached query. Therefore, the first execution of a query will have substantially higher performance metrics than subsequent executions. If the specified query returns more than one result set, these performance metrics are totals for all of the queries.

    To analyze these performance metrics in greater depth you can run MONLBL (the monitor line-by-line utility) and specify the Routine Name using the asterisk wildcard as %sqlcq*. Refer to Examining Routine Performance Using ^%SYS.MONLBL.

  • Cached Query: the automatically generated cached query class name. For example, %sqlcq.USER.cls2 indicating the second cached query in the USER namespace. Each new query is assigned a new cached query name with the next consecutive integer. By clicking this cached query name, you can display information about the cached query and further links to display its Show Plan or to Execute the cached query. (For a DDL statement, see SQL Commands That Are Not Cached.)

    Closing the Management Portal or stopping InterSystems IRIS does not delete cached queries or reset cached query numbering. To purge cached queries from the current namespace, invoke the %SYSTEM.SQL.Purge()Opens in a new tab method.

    Not all SQL statements result in a cached query. A query that is the same as an existing cached query, except for literal substitution values (such as the TOP clause value and predicate literals) does not create a new cached query. Some SQL statements are not cached, including DDL statements and privilege assignment statements. Non-query SQL statements, such as CREATE TABLE, also display a cached query name. However, this cached query name is created then immediately deleted; the next SQL statement (query or non-query) reuses the same cached query name.

  • Frozen state: if a query plan is frozen, the frozen plan state (in parentheses) is displayed after the cached query class name. For example (Frozen/Explicit). If the query plan is not frozen, nothing is displayed here.

  • Last update: the date and time that the last Execute Query (or other SQL operation) was performed. This timestamp is reset each time the query is executed, even when repeatedly executing the identical query.

  • Successful execution also provides a Print link that displays the Print Query window, which gives you the options to either print or export to a file the query text and/or the query result set. The clickable Query and Result toggles enable you to display or hide the query text or the query result set. The displayed query result set includes the namespace name, the result set data and row count, a timestamp, and the cached query name. (Note that the timestamp is the time when the Print Query window was invoked, not the time when the query was executed.) The Print Query window Print button prints a screenshot of the Print Query window. The Export to File check box displays options to specify an export file format (xml, hdml, pdf, txt, csv) and an export file pathname. The Export option ignores the Query and Result toggles and always exports only the result set data (by default to: exportQuery.pdf) and the row count (by default to: exportQueryMessages.pdf); the query text, namespace, timestamp, and cached query name are not included.

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.

You can also select buttons for Plan, Print, or Delete. The Print button displays the Print Query window, which gives you the options to either print or export to a file the query text and/or the query result set. The Delete button deletes the SQL statement from the history; the Delete All button at the end of the Show History list deletes all the SQL statements in the history.

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:

    • asterisk (*) meaning 0 or more characters of any type.

    • underscore (_) meaning a single character of any type.

    • an apostrophe (') inversion prefix meaning “not” (everything except).

    • a backslash (\) escape character: \_ means a literal underscore character.

    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:

  • Table Info: Table Type: either TABLE, GLOBAL TEMPORARY, or SYSTEM TABLE (system tables are only displayed if the System check box is selected), Owner name, Last Compiled timestamp, External and Readonly boolean values, Class Name, Extent Size, the name of the Child Table(s) and/or the Parent Table (if relevant) and one or more References fields to other tables (if relevant), whether it uses the %Storage.Persistent default storage class, whether it Supports Bitmap Indices, the RowID field name, a list of the fields that RowId is based on (if relevant), and whether the table is sharded. If there is an explicit shard key, it displays the shard key fields.

    Class Name is a link to the corresponding entry in the InterSystems Class Reference documentation. The Class Name is a unique package.class name derived from the table name by removing punctuation characters, as described in Identifiers and Class Entity Names.

    References only appears in Table Info if there is one or more references from a field in the current table to another table. These references to other tables are listed as links to the Table Info for the referenced table.

    Sharded: if the table is a shard-master table, the Table Info displays the name of the shard-local class and table with a link to the corresponding entry in the InterSystems Class Reference documentation. If the table is a shard-local table, the Table Info displays the name of the shard-master class and table with a link to the corresponding entry in the InterSystems Class Reference documentation. Shard-local tables are only displayed if the System check box is selected.

    This option also provides a modifiable value for the Number of rows to load when table is opened. This sets the maximum number of rows to display in Open Table. The available range is from 1 to 10,000; the default is 100. The Management Portal corrects a value outside the available range to a valid value: 0 corrects to 100; a fractional number rounds up to the next higher integer; a number greater than 10,000 corrects to 10,000.

  • Fields: a list of the fields (columns) in the table showing: Field Name, Datatype, Column #, Required, Unique, Collation, Hidden, MaxLen, MaxVal, MinVal, Stream, Container, SQLType, Reference To, Version Column, Selectivity, Outlier Selectivity, and Average Field Size.

  • Maps/Indices: a list of the indices defined for the table showing: Index Name, SQL Map Name, Columns, Type, Block Count, Map Inherited, and Global.

    Index Name is the index property name and follows property naming conventions; when generated from an SQL index name, punctuation characters (such as underscores) in the SQL index name are stripped out. The SQL Map Name is the SQL name for the index. A generated SQL Map Name is the same as the Constraint Name, and follows the same naming conventions (described below). Columns specifies a field or a comma-separated list of fields specified for the index; it may specify the index collation type and full schema.table.field reference, as in the following example: $$SQLUPPER({Sample.People.Name}). Type can be one of the following: Bitmap Extent, Data/Master, Index (standard index), Bitmap, or Bitslice index, and the Unique constraint. The Block Count contains both the count and how that count was determined: set explicitly by the class author (Defined), computed by TuneTable (Measured), or estimated by the class compiler (Estimated). If Map Inherited? is Yes, this map was inherited from a superclass. Global is the name of the subscripted global containing the index data. The naming conventions for index globals are described in Index Global Names. You can supply this global name to ZWRITE to display the index data.

    This option also provides a link for each index to rebuild the index.

  • Triggers: a list of the triggers defined for the table showing: Trigger Name, Time Event, Order, Code.

  • Constraints: a list of the constraints for fields of the table showing: Constraint Name, Constraint Type, and Constraint Data (field name(s) listed in parentheses). Constraints include primary key, foreign key, and unique constraints. A primary key is, by definition, unique; it is only listed once. This option list constraints by constraint name; a constraint involving multiple fields is listed once with Constraint Data displaying a comma-separated list of the component fields. The Constraint Type can be UNIQUE, PRIMARY KEY, Implicit PRIMARY KEY, FOREIGN KEY, or Implicit FOREIGN KEY.

    You can also list constraints by invoking INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE. This list constraints by field name. The following example returns the name of the field and the name of the constraint for all UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK constraints: 

    SELECT Column_Name,Constraint_Name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_SCHEMA='Sample' AND TABLE_NAME='Person'

    If the table is defined with %PUBLICROWID and no explicit primary key is defined, the RowID field is listed with a Constraint Type of Implicit PRIMARY KEY with the Constraint Name RowIDField_As_PKey.

    For explicit constraints, the Constraint Name is generated as follows:

    • Constraint specified in the field definition: For example, FullName VARCHAR(48) UNIQUE or FullName VARCHAR(48) PRIMARY KEY. The Constraint Name value for the field is a generated value with the syntax TABLENAME_CTYPE#, where CTYPE is UNIQUE, PKEY, or FKEY, and # is a sequential integer assigned to unnamed constraints in the order specified in the table definition. For example, if FullName has the 2nd unnamed unique constraint (excluding the ID field) in the MyTest table, the generated Constraint Name for FullName would be MYTEST_UNIQUE2; if FullName is the primary key and the 3rd unnamed constraint (excluding the ID field) specified in the MyTest table, the generated Constraint Name for FullName would be MYTEST_PKEY3.

    • CONSTRAINT keyword named constraint clause: For example, CONSTRAINT UFullName UNIQUE(FirstName,LastName) or CONSTRAINT PKName PRIMARY KEY(FullName)), the Constraint Name is the specified unique constraint name. For example, FirstName and LastName in the MyTest table would each have the Constraint Name UFullName; FullName would have the Constraint Name PKName.

    • Unnamed constraint clause: For example, UNIQUE(FirstName,LastName) or PRIMARY KEY (FullName). the Constraint Name value is a generated value with the syntax TABLENAMECType#, where CType is Unique, PKey, or FKey, and # is a sequential integer assigned to unnamed constraints in the order specified in the table definition. For example, if FirstName and LastName have the 2nd unnamed unique constraint (excluding the ID field) in the MyTest table, the generated Constraint Name for FirstName and LastName would be MYTESTUnique2; if FullName is the primary key and the 3rd unnamed constraint (excluding the ID field) specified in the MyTest table, the generated Constraint Name for FullName would be MYTESTPKey3. (Note mixed uppercase/lowercase and absence of an underscore.)

    If a field is involved in more than one uniqueness constraint, it is listed separately for each Constraint Name.

  • Cached Queries: a list of the cached queries for the table showing: Routine name, Query text, Creation Time, Source, Query Type.

  • Table’s SQL Statements: a list of the SQL Statements generated for this table. Same information as namespace-wide SQL Statements display.

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:

  • View Info: Owner name, Last Compiled timestamp. This timestamp updates when you use the Edit View link and save changes.

    Defined as Read Only and View is Updateable booleans: if view definition included WITH READ ONLY, these are set to 1 and 0 respectively. Otherwise, if the view is defined from a single table they are set to 0 and 1; if the view is defined from joined tables they are set to 0 and 0. You can change this option using the Edit View link.

    Class Name is a unique package.class name derived from the view name by removing punctuation characters, as described in Identifiers and Class Entity Names.

    Check Option is only listed if the view definition included the WITH CHECK OPTION clause. It can be LOCAL or CASCADED. You can change this option using the Edit View link.

    Class Type is VIEW. It provides an Edit View link to edit the view definition.

    View Text is the SELECT statement used to define the view. You can change the view definition using the Edit View link.

    The list of fields includes the Field Name, Data Type, MAXLEN Parameter, MAXVAL Parameter, MINVAL Parameter, BLOB (%Stream.GlobalCharacter or %Stream.GlobalBinary field), Length, Precision, and Scale.

  • View’s SQL Statements: a list of the SQL Statements generated for this view. Same information as namespace-wide SQL Statements display.

Catalog Details for a Stored Procedure

The following Catalog Details options are provided for each procedure:

  • Stored Procedure Info:

    Class Name is a unique package.class name derived from the procedure name by pre-pending a type identifier ( ‘func’, ‘meth’, ‘proc’, or ‘query’) to the class name (for example, the SQL function MyProc becomes funcMyProc) and removing punctuation characters, as described in Identifiers and Class Entity Names. Class Document is a link to the corresponding entry in the InterSystems Class Reference. Procedure Type (for example, function). Method or Query Name the name of the generated class method or class query; this name is generated described in Identifiers and Class Entity Names. The Run Procedure link provides an option to interactively run the procedure.

  • Stored Procedure’s SQL Statements: a list of the SQL Statements generated for this stored procedure. Same information as namespace-wide SQL Statements display.

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.

Wizards

Actions

  • Create View — Displays a page for creating a view. Instructions for using this option are provided in the “Defining and Using Views” chapter of this book.

  • Print Catalog — Allows you to print complete information about a table definition. Clicking Print Catalog displays a print preview. By clicking Indices, Triggers, and/or Constraints on this print preview you can include or exclude this information from the catalog printout.

  • Purge Cached Queries — Provides three options for purging cached queries: purge all cached queries for the current namespace, purge all cached queries for the specified table, or purge only selected cached queries.

  • Tune Table Information — Run the Tune Table facility against the selected table. This calculates the selectivity of each table column against the current data. A selectivity value of 1 indicates a column that defined as unique (and therefore has all unique data values). A selectivity value of 1.0000% indicates a column not defined as unique for which all current data values are unique values. A percentage value greater that 1.0000% indicates the relative number of duplicate values for that column in the current data. By using these selectivity values you can determine what indexes to define and how to use these indexes to optimize performance.

  • Tune all tables in schema — Run the Tune Table facility against all of the tables belonging to a specified schema in the current namespace.

  • Rebuild Table’s Indices — Rebuild all indexes for the specified table.

  • Drop this item — Drop (delete) the specified table definition, view definition, procedure, or cached query. You must have the appropriate privileges to perform this operation. Drop cannot be used on a table created by defining a persistent class, unless the table class definition includes [DdlAllowed]. Otherwise, the operation fails with an SQLCODE -300 error with the %msg DDL not enabled for class 'Schema.tablename'. Drop cannot be used on a table if the corresponding persistent class has a subclass (a derived class); the operation fails with an SQLCODE -300 error with the %msg Class 'Schema.tablename' has derived classes and therefore cannot be dropped via DDL.

    If a class is defined as a linked table, the Drop action drops the linked table on the local system, even if the linked table class is not defined as DdlAllowed. Drop does not drop the actual table this link references that resides on the server.

  • Export All Statements — Exports all SQL Statements in the current namespace. SQL Statements are exported in XML format. You can choose to export to a file, or export to a browser display page.

  • Import Statements — Imports SQL Statements from an XML file into the current namespace.

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.

A column of data type %Stream.GlobalCharacterOpens in a new tab displays the actual data (up to 100 characters) as a string. Additional data beyond the first 100 characters is indicated by an ellipsis (...).

A column of data type %Stream.GlobalBinaryOpens in a new tab displays as <binary>.

Tools

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:

  • SQL Runtime Statistics: user interface to generate SQL Runtime Statistics for a specified query.

  • Index Analyzer: user interface for gathering various types of index analysis for a specified schema.

  • Alternative Show Plans: user interface to generate Alternate Show Plans for a specified query.

  • Generate Report to submit an SQL query performance report to InterSystems WRC (Worldwide Response Center customer support). To use this reporting tool you must first get a WRC tracking number from the WRC.

  • Import Report to import an existing WRC report by file name. For InterSystems use only.

Diagnostic Logs

From the home page of the Management Portal, navigate to System Operation < System Logs < SQL Diagnostics Logs to view a log that details the performance of LOAD DATA commands that have run within the current namespace.

Each command is viewable on a different row. When a row is selected, the details appear on the right. These details include:

  • Start time: The time that the LOAD DATA command started running.

  • User: The user that issued the LOAD DATA command.

  • Status: The current status of the statement.

  • Process ID: The process ID that the LOAD DATA command ran in.

  • SQLCODE: The SQLCODE returned when the LOAD DATA command has finished running.

  • Input record count: The number of input records from the data source.

  • Error count: The number of errors loading specific rows of the data source.

  • Statement: The full statement text of the command that was run.

  • Messages: Any messaged logged as the LOAD DATA statement ran, including errors.

To purge older entries, select the check box for the rows you would like to delete, then click the Delete button at the top of the screen.

Next sectionDefining Tables
Previous sectionUsing the SQL Shell Interface
FeedbackOpens in a new tab