Skip to main content
Previous sectionNext section

SQL Statements

This list of SQL statements provide a record of SQL queries and other operations for each table, including insert, update, and delete. These SQL statements are linked to a query plan, and this link provides the option to freeze this query plan.

The system creates an SQL Statement for each SQL DML operation. This provides a list of SQL operations listed by table, view, or procedure name. If you change the table definition, you can use this SQL Statements list to determine whether the query plan for each SQL operation will be affected by this DDL change and/or whether an SQL operation may need to be modified. You can then:

  • Determine which query plan to use for each SQL operation. You can decide to use a revised query plan that reflects changes made to the table definition. Or you can freeze the current query plan, retaining the query plan generated prior to making changes to the table definition.

  • Determine whether to make code changes to routines that perform SQL operations against that table, based on changes made to the table definition.

Note:

SQL Statements is a listing of SQL routines that may be affected by a change to a table definition. It should not be used as a history of changes to either the table definition or table data.

Operations that Create SQL Statements

The following SQL operations create corresponding SQL Statements:

Data management (DML) operations include queries against the table, and insert, update, and delete operations. Each data management (DML) operation (both Dynamic SQL and Embedded SQL) creates an SQL Statement when the operation is executed.

  • Dynamic SQL SELECT commands create an SQL Statement when the query is prepared. In addition, an entry is created in the Management Portal Cached Queries listing.

  • Embedded SQL cursor-based SELECT commands create an SQL Statement when the OPEN command invokes a DECLARED query. No separate entry is created in the Management Portal Cached Queries listing.

If a query references more than one table, a single SQL Statement is created in the namespace’s SQL Statements listing that lists all of the referenced tables in the Table/View/Procedure Name(s) column, and for each individual referenced table the Table’s SQL Statements listing contains an entry for that query.

Most SQL Statements have an associated Query Plan. When created, this Query Plan is unfrozen; you can subsequently designate this Query Plan as a frozen plan. SQL Statements with a Query Plan include DML commands that involve a SELECT operation. SQL Statements without a Query Plan are listed in the “Plan State” section below.

Note:

SQL Statements only lists the most recent version of an SQL operation. Unless you freeze the SQL Statement, InterSystems IRIS® data platform replaces it with the next version. Thus rewriting and invoking the SQL code in a routine causes the old SQL code to disappear from SQL Statements.

Other SQL Statement Operations

The following SQL commands perform more complex SQL Statement operations:

  • CREATE TRIGGER: No SQL Statement is created in the table in which the trigger is defined, either when the trigger is defined or when it is pulled. However, if the trigger performs a DML operation on another table, defining a trigger creates an SQL Statement in the table modified by the trigger code. The Location specifies the table in which the trigger is defined. The SQL Statement is defined when the trigger is defined; dropping a trigger deletes the SQL Statement. Pulling a trigger does not create an SQL Statement.

  • CREATE VIEW does not create an SQL Statement, because nothing is compiled. It also does not change the Plan Timestamp of the SQL Statements of its source table. However, compiling a DML command for a view creates an SQL Statement for that view.

Listing SQL Statements

This section describes listing SQL Statements in detail using the Management Portal interface. You can also return an index list of SQL Statements using the ^rINDEXSQL global. Note that this listing of SQL Statements can contain stale (no longer valid) listings.

From the Management Portal SQL interface you can list SQL Statements as follows:

  • SQL Statements tab: this lists all SQL Statements in the namespace, in collation sequence by schema then by table/view name within each schema. This listing only includes those tables/views for which the current user has privileges. If a SQL Statement references more than one table, the Table/View/Procedure Name(s) column lists all the referenced tables in alphabetical order.

    By clicking a column heading you can sort the list of SQL Statements by Table/View/Procedure Name(s), Plan State, Location(s), SQL Statement Text, or any other column of the list. These sortable columns enable you to quickly find, for example, all frozen plans (Plan State), all cached queries (Location(s)), or the slowest queries (Average time).

    You can use the Filter option provided with this tab to narrow the listed SQL Statements to a specified subset. A specified filter string filters on all data in the SQL Statements listing, most usefully on schema or schema.table name, routine location, or a substring found in the SQL Statement texts. A filter string is not case-sensitive, but must follow statement text punctuation whitespace (name , age, not name,age). If a query references more than one table, the Filter includes the SQL Statement if it selects for any referenced table in the Table/View/Procedure Name(s) column. The Filter option is user customized.

    The Max rows option defaults to 1,000. The maximum value is 10,000. the minimum value is 10. To list more than 10,000 SQL Statements, use INFORMATION_SCHEMA.STATEMENTS. The Page size and Max rows options are user customized.

  • Catalog Details tab: select a table and display its catalog details. This tab provides an Table’s SQL Statements button to display the SQL Statements associated with that table. Note that if a SQL Statement references more than one table, it will be listed in the Table’s SQL Statements listing for each referenced table, but only the currently selected table is listed in the Table Name column.

    By clicking a column heading you can sort the list of the table’s SQL Statements by any column of the list.

You can use the SQLTableStatements() catalog query or INFORMATION_SCHEMA.STATEMENTS to list SQL Statements selected by various criteria, as described in Querying the SQL Statements, below.

Listing Columns

The SQL Statements tab lists all SQL statements in the namespace. The Catalog Details tab Table’s SQL Statements button lists the SQL Statements for the selected table. Both listing contain the following column headings:

Plan State

The Plan State lists one of the following:

  • Unfrozen: not frozen, can be frozen.

  • Unfrozen/Parallel: not frozen, cannot be frozen.

  • Frozen/Explicit: frozen by user action, can be unfrozen.

  • Frozen/Upgrade: frozen by InterSystems IRIS version upgrade, can be unfrozen.

  • blank: no associated Query Plan:

    • An INSERT... VALUES() command creates an SQL Statement that does not have an associated Query Plan, and therefore cannot be unfrozen or frozen (the Plan State column is blank). Even though this SQL command does not produce a Query Plan, its listing in SQL Statements still is useful, because it allows you to quickly locate all the SQL operations against this table. For example, if you add a column to a table, you may want to find out where all of the SQL INSERTs are for that table so you can update these commands to include this new column.

    • A cursor-based UPDATE or DELETE command does not have an associated Query Plan, and therefore cannot be unfrozen or frozen (the Plan State column is blank). Executing the OPEN command for a declared CURSOR generates an SQL Statement with an associated Query Plan. Embedded SQL statements that use that cursor (FETCH cursor, UPDATE...WHERE CURRENT OF cursor, DELETE...WHERE CURRENT OF cursor, and CLOSE cursor) do not generate separate SQL Statements. Even though a cursor-based UPDATE or DELETE does not produce a Query Plan, its listing in SQL Statements is still useful, because it allows you to quickly locate all the SQL operations against this table.

SQL Statement Text

The SQL Statement text commonly differs from the SQL command because SQL statement generation normalizes lettercase and whitespace. Other differences are as follows:

If you issue a query from the Management Portal interface or the SQL Shell interface, the resulting SQL Statement differs from the query by preceding the SELECT statement with DECLARE QRS CURSOR FOR (where “QRS” can be a variety of generated cursor names). This allows the statement text to match that of the Dynamic SQL cached query.

If the SQL command specifies an unqualified table or view name, the resulting SQL Statement provides the schema by using either a schema search path (for DML, if provided) or the default schema name.

SQL Statement Text is truncated after 1024 characters. To view the complete SQL Statement Text, display the SQL Statement Details.

A single SQL command may result in more than one SQL Statement. For example, if a query references a view, SQL Statements displays two statement texts, one listed under the view name, the other listed under the underlying table name. Freezing either statement results in Plan State of Frozen for both statements.

When SQL statements are prepared via xDBC, SQL statement generation appends SQL Comment Options (#OPTIONS) to the statement text if the options are needed to generate the statement index hash. This is shown in the following example:

DECLARE C CURSOR FOR SELECT * INTO :%col(1) , :%col(2) , :%col(3) , :%col(4) , :%col(5) FROM SAMPLE . COMPANY /*#OPTIONS {"xDBCIsoLevel":0} */ 
Copy code to clipboard

Stale SQL Statements

When a routine or class associated with an SQL Statement is deleted, the SQL Statement listing is not automatically deleted. This type of SQL Statement listing is referred to as Stale. Because it is often useful to have access to this historic information and the performance statistics associated with the SQL Statement, these stale entries are preserved in the Management Portal SQL Statement listing.

You can remove these stale entries by using the Clean Stale button. Clean Stale removes all non-frozen SQL Statements for which the associated routine or class (table) is no longer present or no longer contains the SQL Statement query. Clean Stale does not remove frozen SQL Statements.

You can perform the same clean stale operation using the CleanStaleStatements() method.

If you delete a table (persistent class) associated with an SQL Statement, the Table/View/Procedure Name(s) column is modified, as in the following example: SAMPLE.MYTESTTABLE - Deleted??; the name of the deleted table is converted to all uppercase letters and is flagged as “Deleted??”. Or, if the SQL Statement referenced more than one table: SAMPLE.MYTESTTABLE - Deleted?? Sample.Person.

  • For a Dynamic SQL query, when you delete the table the Location(s) column is blank because all cached queries associated with the table have been automatically purged. Clean Stale removes the SQL Statement.

  • For an Embedded SQL query, the Location(s) column contains the name of the routine used to execute the query. When you change the routine so that it no longer executes the original query, the Location(s) column is blank. Clean Stale removes the SQL Statement. When you delete a table used by the query, the table is flagged as “Deleted??”; Clean Stale does not remove the SQL Statement.

Note:

A system task is automatically run once per hour in all namespaces to clean up indices for any SQL Statements that might be stale or have stale routine references. This operation is performed to maintain system performance. This internal clean-up is not reflected in the Management Portal SQL Statements listings. You can use the Management Portal to monitor this hourly cleanup or to force it to occur immediately. To view when this task was last finished and next scheduled, select System Operation, Task Manager, Task Schedule and view the Cleanup SQL Statement Index task. You can click on the task name for task details. From the Task Details display you can use the Run button to force the task to be performed immediately. Note that these operations do not change the SQL Statements listings; you must use Clean Stale to update the SQL Statements listings.

Data Management (DML) SQL Statements

The Data Management Language (DML) commands that create an SQL Statement are: INSERT, UPDATE, INSERT OR UPDATE, DELETE, TRUNCATE TABLE, SELECT, and OPEN cursor for a declared cursor-based SELECT. You can use Dynamic SQL or Embedded SQL to invoke a DML command. A DML command can be invoked for a table or a view, and InterSystems IRIS creates a corresponding SQL Statement.

Note:

The system creates an SQL Statement when Dynamic SQL is prepared or when an Embedded SQL cursor is opened, not when the DML command is executed. The SQL Statement timestamp records when this SQL code invocation occurred, not when (or if) the query was executed. Thus an SQL Statement may represent a change to table data that was never actually performed.

Preparing a Dynamic SQL DML command creates a corresponding SQL Statement. The Location associated with this SQL Statement is a cached query. Dynamic SQL is prepared when SQL is executed from the Management Portal SQL interface, from the SQL Shell interface, or imported from a .txt file. Purging an unfrozen cached query flags the corresponding SQL Statement for Clean Stale deletion. Purging a frozen cached query removes the Location value for the corresponding SQL Statement. Unfreezing the SQL Statement flags it for Clean Stale deletion.

Executing a non-cursor Embedded SQL Data Management Language (DML) command creates a corresponding SQL Statement. Each Embedded SQL DML command creates a corresponding SQL Statement. If a routine contains multiple Embedded SQL commands, each Embedded SQL command creates a separate SQL Statement. (Some Embedded SQL commands create multiple SQL Statements.) The Location column of the SQL Statement listing specifies the routine that contains the Embedded SQL. In this way, SQL Statements maintains a record of each Embedded SQL DML command.

Opening a cursor-based Embedded SQL Data Management Language (DML) routine creates an SQL Statement with a Query Plan. Associated Embedded SQL statements (FETCH cursor, CLOSE cursor) do not generate separate SQL Statements. Following a FETCH cursor, an associated UPDATE table WHERE CURRENT OF cursor or DELETE FROM table WHERE CURRENT OF cursor does generate a separate SQL Statement, but no separate Query Plan.

An INSERT command that inserts literal values creates a SQL Statement with the Plan State column blank. Because this command does not create a Query Plan, the SQL Statement cannot be frozen.

SELECT Commands

Invoking a query creates a corresponding SQL Statement. It can be a simple SELECT, or a CURSOR-based SELECT/FETCH operation. The query can be issued against a table or a view.

  • A query containing a JOIN creates an identical SQL Statement for each table. The Location is the same stored query in the listing for each table. The Statement uses the following relations lists all of the tables, as described in the SQL Statement Details Routines and Relations Sections.

  • A query containing a select-item subquery creates an identical SQL Statement for each table. The Location is the same stored query in the listing for each table. The Statement uses the following relations lists all of the tables, as described in the SQL Statement Details Routines and Relations Sections.

  • A query that references an external (linked) table cannot be frozen.

  • A query containing the FROM clause %PARALLEL keyword may create more than one SQL Statement. You can display these generated SQL Statements by invoking:

    SELECT * FROM INFORMATION_SCHEMA.STATEMENT_CHILDREN
    Copy code to clipboard

    This displays the Statement column containing the statement hash of the original query and the ParentHash column containing the statement hash of a generated version of the query.

    SQL Statements for a %PARALLEL query have a Plan State of Unfrozen/Parallel, and cannot be frozen.

  • A query containing no FROM clause, and therefore not referencing any table, still creates an SQL Statement. For example: SELECT $LENGTH('this string') creates a SQL Statement with the Table column value %TSQL_sys.snf.

SQL Statement Details

There are two ways to display the SQL Statement Details:

  • From the SQL Statements tab, select an SQL Statement by clicking the Table/View/Procedure Name(s) link in the left-hand column. This displays the SQL Statement Details in a separate tab. This interface allows you to open multiple tabs for comparison. It also provides a Query Test button that displays the SQL Runtime Statistics page.

  • From the table’s Catalog Details tab (or the SQL Statements tab), select an SQL Statement by clicking the Statement Text link in the right-hand column. This displays the SQL Statement Details in a pop-up window.

You can use either SQL Statement Details display to view the Query Plan and to freeze or unfreeze the query plan.

SQL Statement Details provides buttons to Freeze or Unfreeze the query plan. It also provides a Clear SQL Statistics button to clear the Performance Statistics, an Export button to export one or more SQL Statements to a file, as well as a buttons to Refresh and to Close the page.

The SQL Statement Details display contains the following sections. Each of these sections can be expanded or collapsed by selecting the arrow icon next to the section title:

Statement Details Section

Statement Details section:

  • Statement hash: an internal hash representation of the statement definition that is used as the key of the SQL Statement Index (for internal use only). Occasionally, what appear to be identical SQL statements may have different statement hash entries. Any difference in settings/options that require different code generation of the SQL statement result in a different statement hash. This may occur with different client versions or different platforms that support different internal optimizations.

  • Timestamp: Initially, the timestamp when the plan was created. This timestamp is updated following a freeze / unfreeze to record the time the plan was unfrozen, not the time the plan was re-compiled. You may have to click the Refresh Page button to display the unfreeze timestamp. Comparing the Plan Timestamp with the datetime value of the routine/class that contain the statement will let you know if the routine/class is not using the same query plan if it was recompiled again.

  • Version: the InterSystems IRIS version under which the plan was created. If the Plan state is Frozen/Upgrade, this is an earlier version of InterSystems IRIS. When you unfreeze a query plan, the Plan state is changed to Unfrozen and the Version is changed to the current InterSystems IRIS version.

  • Plan state: Frozen/Explicit, Frozen/Upgrade, Unfrozen, or Unfrozen/Parallel. Frozen/Explicit means that this statement's plan has been frozen by an explicit user action and this frozen plan is the query plan that will be used, regardless of changes to the code that generated this SQL Statement. Frozen/Upgrade means that this statement's plan has been automatically frozen by an InterSystems IRIS version upgrade. Unfrozen means that the plan is currently unfrozen and may be frozen. Unfrozen/Parallel mean that the plan is unfrozen and uses %PARALLEL processing, and therefore cannot be frozen. A NULL (blank) plan state means that there is no associated query plan.

  • Natural query: a boolean flag indicating whether the query is a “natural query.” If checked, the query is a natural query, and no query performance statistics are recorded. If not checked, performance statistics may be recorded; other factors determine whether statistics actually are recorded. A natural query is defined as an Embedded SQL query that is so simple that the overhead of recording statistics would affect query performance. There is no advantage to keeping statistics on a natural query, as the query is already very simple. A good example of a natural query is SELECT Name INTO :n FROM Table WHERE %ID=?. The WHERE clause of this query is an equality condition. This query does not involve any looping or any index references. A Dynamic SQL query (cached query) is never flagged as a natural query; statistics may or may not be recorded for a cached query.

  • Frozen plan different: if you freeze the plan, this additional field is displayed, displaying whether the frozen plan is different from the unfrozen plan. When you freeze the plan, the Statement Text and Query Plan displays the frozen plan and the unfrozen plan side-by-side for easier comparison.

  • This section also includes five query performance statistics fields which are described in the following section.

Performance Statistics

Executing a query adds performance statistics to the corresponding SQL Statement. This information can be used to determine which queries are the slowest and which queries are executed the most. By using this information you can determine which queries would provide significant benefits by being optimized.

In addition to the SQL Statement name, Plan state, location, and text, the following additional information is provided for cached queries:

  • Count: an integer count of the number of times this query has been run. A change that results in a different Query Plan for this query (such as adding an index to a table) will reset this count.

  • Average count: the average number of times this query is run per day.

  • Total time: the amount of time (in seconds) that running this query has taken.

  • Average time: the average amount of time (in seconds) that running this query has taken. If the query is a cached query, the first execution of the query likely took significantly more time than subsequent executions of the optimized query from the query cache.

  • Standard deviation: the standard deviation of the total time and the average time. A query that is only run once has a standard deviation of 0. Queries that are run many times commonly have a lower standard deviation than those that are run only a few times.

  • Date first seen: the date the query was first run (executed). This may differ from the Last Compile Time, which is when the query was prepared.

Query performance statistics are periodically updated for completed query executions by the UpdateSQLStats task. This minimizes the overhead involved in maintaining these statistics. As a consequence, currently running queries do not appear in the query performance statistics. Recently-completed queries (roughly, within the last hour) may not immediately appear in the query performance statistics.

You can use the Clear SQL Statistics button to clear the values of these six fields.

InterSystems IRIS does not separately record performance statistics for %PARALLEL subqueries. %PARALLEL subquery statistics are summed with the statistics for the outer query. Queries generated by the implementation to run in parallel do not have their performance statistics tracked individually.

InterSystems IRIS does not record performance statistics for “natural” queries. If the system collected statistics it would slow the query performance, and a natural query is already optimal, so there is no potential for optimization.

You can view these query performance statistics for multiple SQL statements in the SQL Statements tab display. You can sort the SQL Statements tab listing by any column. This makes it easy to determine, for example, which queries have the largest average time.

You can also access these query performance statistics by querying the INFORMATION.SCHEMA.STATEMENTS class properties, as described in Querying the SQL Statements.

Compile Settings Section

Compile Settings section:

  • Select mode: the SelectMode the statement was compiled with. For DML commands this can be set using #SQLCompile Select; the default is Logical. If #SQLCompile Select=Runtime, a call to $SYSTEM.SQL.SetSelectMode() can change the query result set display, but does not change the Select Mode value, which remains Runtime.

  • Default schema(s): the default schema name that were set when the statement was compiled. This is commonly the default schema in effect when the command was issued, though SQL may have resolved the schema for unqualified names using a schema search path (if provided) rather than the default schema name. However, if the statement is a DML command in Embedded SQL using one or more #Import macro directives, the schemas specified by #Import directives are listed here.

  • Schema path: the schema path defined when the statement was compiled. This is the schema search path, if specified. If no schema search path is specified, this setting is blank. However, for a DML Embedded SQL command with a search path specified in an #Import macro directive, the #Import search path is shown in the Default schema(s) setting and this Schema path setting is blank.

Routines and Relations Sections

Statement is Defined in the Following Routines section:

  • Routine: the class name associated with the cached query (for Dynamic SQL DML), or the routine name (for Embedded SQL DML).

  • Type: Class Method or MAC Routine (for Embedded SQL DML).

  • Last Compile Time: the last compile time or prepare time for the routine. If the SQL Statement is Unfrozen, recompiling a MAC routine updates both this timestamp and the Plan Timestamp. If the SQL Statement is Frozen, recompiling a MAC routine updates only this timestamp; the Plan Timestamp is unchanged until you unfreeze the plan; the Plan Timestamp then shows the time the plan was unfrozen.

Statement Uses the Following Relations section lists one or more defined tables used to create the query plan. For an INSERT that uses a query to extract values from another table, or an UPDATE or DELETE that uses a FROM clause to reference another table, both tables are listed here. For each table the following values are listed:

  • Table or View Name: the qualified name of the table or view.

  • Type: Table or View.

  • Last Compile Time: The time the table (persistent class) was last compiled.

  • Classname: the classname associated with the table.

This section includes a Compile Class option to re-compile the class. If you re-compile an unfrozen plan, all three time fields are updated. If you re-compile a frozen plan, the two Last Compile Time fields are updated, but the Plan Timestamp is not. When you unfreeze the plan and click the Refresh Page button, the Plan Timestamp updates to the time the plan was unfrozen.

Querying the SQL Statements

You can use the SQLTableStatements() stored query to return the SQL Statements for a specified table. This is shown in the following example:

  SET mycall = "CALL %Library.SQLTableStatements('Sample','Person')"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus=tStatement.%Prepare(mycall)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset=tStatement.%Execute()
  IF rset.%SQLCODE '= 0 {WRITE "SQL error=",rset.%SQLCODE QUIT}
  DO rset.%Display()
Copy code to clipboard

You can use the INFORMATION_SCHEMA package tables to query the list of SQL Statements. InterSystems IRIS supports the following classes:

The following are some example queries using these classes:

The following example returns all of the SQL Statements in the namespace, listing the hash value (a computed Id that uniquely identifies the normalized SQL statement), the frozen status flag (values 0 through 3), the local timestamp when the statement was prepared and the plan saved, and the statement text itself:

SELECT Hash,Frozen,Timestamp,Statement FROM INFORMATION_SCHEMA.STATEMENTS
Copy code to clipboard

The following example returns the SQL Statements for all frozen plans, indicating whether the frozen plan is different from what the plan would be if not frozen. Note that an unfrozen statement may be Frozen=0 or Frozen=3. A statement such as a single row INSERT, that cannot be frozen, displays NULL in the Frozen column:

SELECT Frozen,FrozenDifferent,Timestamp,Statement FROM INFORMATION_SCHEMA.STATEMENTS
WHERE Frozen=1 OR Frozen=2
Copy code to clipboard

The following example returns all the SQL Statements and the routines the statements are located in for a given SQL table. (Note that the table name (SAMPLE.PERSON) must be specified with the same letter case used in the SQL Statement text: all uppercase letters):

SELECT Statement,Frozen,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE STATEMENT_RELATIONS->Relation='SAMPLE.PERSON'
Copy code to clipboard

The following example returns all the SQL Statements in the current namespace that have frozen plans:

SELECT Statement,Frozen,Frozen_Different,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE Frozen=1 OR Frozen=2
Copy code to clipboard

The following example returns all the SQL Statements in the current namespace that contain a COUNT(*) aggregate function. (Note that the statement text (COUNT ( * )) must be specified with the same whitespace used in the SQL Statement text):

SELECT Statement,Frozen,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE Statement [ ' COUNT ( * ) '
Copy code to clipboard

Exporting and Importing SQL Statements

You can export or import SQL Statements as an XML-formatted text file. This enables you to move a frozen plan from one location to another. SQL Statement exports and imports include the associated query plan.

You can export a single SQL Statement or export all of the SQL Statements in the namespace.

You can import a previously-exported XML file containing one or more SQL Statements.

Note:

This import of SQL Statements as XML should not be confused with the import and execution of SQL DDL code from a text file.

Exporting SQL Statements

Export a single SQL Statement:

  • Use the SQL Statement Details page Export button. From the Management Portal System Explorer SQL interface, select the SQL Statements tab and click on a statement to open up the SQL Statement Details page. Select the Export button. This opens a dialog box, allowing you to select to export the file to Server (a data file) or Browser.

    • Server (the default): Enter the full path name of the export xml file. The first time you export, this file has a default name of statementexport.xml. You can, of course, specify a different path and file name. After you have successfully exported an SQL Statement file, the last used file name becomes the default.

      The Run export in the background check box is not selected by default.

    • Browser: Exports the file statementexport.xml to a new page in the user’s default browser. You can specify another name for the browser export file, or specify a different software display option.

  • Use the ExportSQLStatement() method.

Export all SQL Statements in the namespace:

  • Use the Export All Statements Action from the Management Portal. From the Management Portal System Explorer SQL interface, select the Actions drop-down list. From that list select Export All Statements. This opens a dialog box, allowing you to export all SQL Statements in the namespace to Server (a data file) or Browser.

    • Server (the default): Enter the full path name of the export xml file. The first time you export, this file has a default name of statementexport.xml. You can, of course, specify a different path and file name. After you have successfully exported an SQL Statement file, the last used file name becomes the default.

      The Run export in the background check box is selected by default. This is the recommended setting when exporting all SQL Statements. When Run export in the background is checked, you are provided with a link to view the background list page where you can see the background job status.

    • Browser: Exports the file statementexport.xml to a new page in the user’s default browser. You can specify another name for the browser export file, or specify a different software display option.

  • Use the ExportAllSQLStatements() method.

Importing SQL Statements

Import an SQL Statement or multiple SQL Statements from a previously-exported file:

  • Use the Import Statements Action from the Management Portal. From the Management Portal System Explorer SQL interface, select the Actions drop-down list. From that list select Import Statements. This opens a dialog box, allowing you to specify the full path name of the import XML file.

    The Run import in the background check box is selected by default. This is the recommended setting when importing a file of SQL Statements. When Run import in the background is checked, you are provided with a link to view the background list page where you can see the background job status.

  • Use the ImportSQLStatement() method.

Viewing and Purging Background Tasks

From the Management Portal System Operation option, select Background Tasks to view the log of export and import background tasks. You can use the Purge Log button to clear this log.