This chapter describes how to perform SQL operations from the Caché 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.
Caché allows you to examine and manipulate data using SQL tools from the Caché Management Portal
. The starting point for this is the Management Portal System Explorer
option. From there you select the SQL
option ([Home] > [SQL]
). This displays the SQL interface, which allow you to:
Execute SQL Statements
write and run SQL statements against existing table definitions and 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
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.
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.
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
([Home] > [Security Management] > [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 dropdown list. Click
From the Management Portal
select System Explorer
, then SQL
([Home] > [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:
: write and execute an SQL statement. The SQL statement can be a SELECT
query, or it can be a Caché SQL DDL or DML statement; the statement is validated on the Caché server when it executes.
: 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.
: 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
text box allows you to write not only SELECT
queries, but most SQL statements, including DDL statements such as CREATE TABLE
, and DML statements such as INSERT
, and DELETE
Query execution supports the execution of queries that return multiple result sets. It also support execution of the CALL
The SQL code area supports whitespace characters: 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.
The SQL code area supports single-line and multiline comments
. Comments are retained and shown in the Show History
and Show Plan displays, but not in Cached Queries.
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 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
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
You can also drag and drop a procedure name from the Procedures list on the left side of the screen.
The SQL execution interface has the following options:
The Display 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 Logical Mode (the default), ODBC Mode, and Display Mode. For further details on these options, refer to Data Display Options
in the Caché SQL Basics
chapter of this book.
The Display Mode
drop-down list with an INSERT
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 Display Mode
drop-down list must be set to LOGICAL (the default). For further details, refer to the INSERT
statement in the Caché SQL Reference
is meaningful for data types whose Logical storage format differs from the desired display format, such as Caché dates and times and Caché %List structured data.
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
button displays the Query 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
interface. You do not have to execute a query to show its query plan.
displays an SQLCODE when invoked for an invalid query.
If you click the
option, the SQL execution interface displays the following additional options:
: 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 either the RowID
or the %VID
. The row number column header name is #. The default is to display row numbers.
After writing SQL code in the
text box, you can execute the code by clicking the
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.
If successful, it 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. It also provides a
link to print the query text and/or the query results.
: For a DDL statement such as CREATE TABLE, displays Row count: 0
if the operation was successful; displays no value for Row count
if the operation failed. 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.
: measured in elapsed time (in fractional seconds), total number of global references, total number of lines executed, and disk read latency (in milliseconds).
: the automatically generated cached query
routine name. For example, %sqlcq.SAMPLES.cls2
indicating the second cached query in the SAMPLES namespace. Each new query is assigned a new cached query routine 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.
Closing the Management Portal or stopping Caché does not delete cached queries or reset cached query routine numbering. To purge cached queries from the current namespace, invoke the %SYSTEM.SQL.Purge()
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 Routine name. However, this cached query routine name is created then immediately deleted; the next SQL statement (query or non-query) reuses the same routine name.
: 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.
If unsuccessful, it displays an error message. You can click the
button to display the corresponding SQLCODE error value and message.
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 identified by the word Aggregate_
, or Literal_
followed by the SELECT item sequence number (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.
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
listing updates its Execution Time (local date and time stamp), and increments its Count (number of times executed).
You can filter the
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
by selecting the statement, which causes it to be displayed in the
text box. In
you can modify the SQL code and then click
. Making any change to an SQL statement retrieved from
causes it to be stored in
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
, but it is preserved when an SQL statement is retrieved from
You can execute (re-run) an unmodified SQL statement directly from the
list by clicking the
button found to the right of the SQL statement in the Show History listing.
Note that the
listing is not the same as the list of cached queries
lists all invoked SQL statements from the current session, including those that failed during execution.
Caché supports numerous other ways to write and execute SQL code, as described in other chapters of this manual. These include:
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).
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.
Apply a Filter
or select a schema from the Schema
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.
search pattern remains in effect until you explicitly change it. The x button to the right of the Filter
field clears the search pattern.
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
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
. Those categories not specified in applies to continue to list all of the items of that category type in the namespace.
Optionally, click the System
check box to include system items (items whose names begin with %). The default is to not include system items.
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.
Click on an item in an expanded list to display its Catalog Details
on the right side of the SQL interface.
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
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.
: 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 %CacheStorage
default storage class, and whether it Supports Bitmap Indices
is a link to the corresponding entry in the InterSystems Class Reference
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.
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.
: a list of the fields in the table showing: Field Name
, Column #
, Required, Unique, Collation
, MaxLen, MaxVal, MinVal, Stream
, Container, xDBC Type
, Reference To, Version Column, Selectivity, Outlier Selectivity
, Outlier Value
, and Average Field Size
: a list of the indices defined for the table showing: Index Name, SQL Map Name, Columns, Type, Block Count, Map Inherited. A generated SQL Map Name
is the same as the Constraint Name, and follows the same naming conventions (described below). The corresponding generated Index Name
does not contain the underscore character. 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.
This option also provides a link for each index to rebuild the index.
: a list of the triggers defined for the table showing: Trigger Name, Time Event, Order
: 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'
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
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.
: a list of the cached queries for the table showing: Routine name, Query text, Creation Time, Source, Query Type.
SQL interface also provides Catalog Details
for views, procedures, and cached queries:
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.
Allows you to print complete information about a table definition. Clicking
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.
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.
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.
Run the Tune Table facility
against all of the tables belonging to a specified schema in the current namespace.
Rebuild all indexes for the specified table.
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.