Skip to main content

SQL Statements

This list of SQL statements provide a record of SQL queries and other operations for each table, including table and index definition, 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 compiled SQL operation. This provides a list of SQL DDL and DML 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 definition (DDL) operations that compile an underlying Persistent Class always create (or update and re-compile) one or more SQL Statements. Multiple statements are defined if constraints such as UNIQUE and PRIMARY KEY are defined.

Data management (DML) operations include queries against the table, and insert, update, and delete operations. Each Embedded SQL data management (DML) operation creates an SQL Statement because Embedded SQL is compiled (by default) when the routine containing it is compiled. Dynamic SQL SELECT commands create an SQL Statement when the query is prepared and is preserved as a cached query. This creates a list of the most-recently compiled versions of all SQL commands. If a query references more than one table, a single SQL Statement is created that lists all of the referenced tables in the Table/View/Procedure Name(s) column.

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 DDL commands and 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 recently compiled version of an SQL operation. Unless you freeze the SQL Statement, Caché replaces it with the next version. Thus rewriting 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:

  • ALTER TABLE: Adding or removing a column does not create an SQL Statement. Instead, the existing DDL SQL Statements are recompiled, incorporating the changes to the table definition. Adding a column constraint, such as UNIQUE, does create additional SQL Statements, and recompiles existing DDL statements.

  • 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:

A system task is automatically run once per hour in all namespaces to clean up any SQL Statement Index entries that might be stale or have stale routine references. Therefore, the SQL Statements listings may not reflect all changes made within the hour. 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.

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. 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.

  • 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.

Both listing interfaces specify the qualified table (or view) name, the plan state, the location of the routine that defined this statement (truncated to 128 characters), and the SQL Statement text. The SQL Statement text is in normalized format, which may differ from the command text, as specified below.

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 Caché 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). The DECLARE CURSOR command does generate an SQL Statement with an associated Query Plan. Embedded SQL statements that use that cursor (OPEN cursor, FETCH cursor, 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 Caché 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 Q1 CURSOR FOR (where “Q1” 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 system-wide default schema name (for DDL and DML).

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.

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 DECLARE CURSOR FOR SELECT. You can use Dynamic SQL or Embedded SQL to compile (or prepare) a DML command. A DML command can be compiled for a table or a view, and Caché creates a corresponding SQL Statement.

Note:

The system creates an SQL Statement when Dynamic SQL is prepared or when Embedded SQL is compiled, not when the SQL is executed. The SQL Statement timestamp records when the SQL code was prepared or compiled, not when (or if) it was executed. Thus an SQL Statement may represent a change to the table 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 deletes the corresponding SQL Statement. Purging a frozen cached query removes the Location value for the corresponding SQL Statement; the SQL Statement is deleted when it is unfrozen.

Compiling 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.

Compiling a cursor-based Embedded SQL Data Management Language (DML) command creates an SQL Statement for DECLARE CURSOR with a Query Plan. Associated Embedded SQL statements (OPEN cursor, 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.

Modifying a Routine Containing Embedded SQL

When you compile a routine containing Embedded SQL, each SQL command is recorded as an entry in a statement dictionary for that routine. If you change and re-compile the routine, Caché removes all of the previous statement dictionary entries for that routine from all tables, then creates new entries corresponding to the current Embedded SQL content. If the re-compiled routine contains no Embedded SQL, the prior SQL Statements are removed and no new SQL Statements are added. Thus, by default, only the most recently compiled version of the Embedded SQL in a routine is preserved as an SQL Statement.

You can prevent a re-compile from deleting/replacing an SQL Statement by designating it as a Frozen Plan. This allows you to retain the query plan for that statement. Subsequent changes to the routine’s SQL code have no effect on frozen SQL Statements.

SELECT Commands

Compiling (or Preparing) 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

    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

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 this SQL Statement Details display to view the Query Plan and to freeze or unfreeze the query plan.

In addition to the buttons to Freeze or Unfreeze the query plan, this box contains the following sections:

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).

  • 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 Caché version under which the plan was created. If the Plan State is Frozen/Upgrade, this is an earlier version of Caché. When you unfreeze a query plan, the Plan State is changed to Unfrozen and the Plan Version is changed to the current Caché 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 a Caché 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.

  • Unfrozen plan different (not displayed unless the plan is frozen): if you freeze the query plan, a Check Frozen button is displayed. If you select this button, this additional field is displayed, displaying whether the frozen plan is different from the unfrozen plan.

  • This section also includes six 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.

Caché 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.

Caché 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.STATEMENTSOpens in a new tab 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 DDL commands, this is always Runtime. 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 system-wide default schema name that were set when the statement was compiled. For DDL commands this is the schema actually used to resolve unqualified names. For DML commands this is the system-wide 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 this system-wide default schema. 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. For DDL commands this is always blank. For DML commands 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.

  • Plan Error: This field only appears when an error occurs when using a frozen plan. For example, if a query plan uses an index, the query plan is frozen, and then the index is dropped from the table, a Plan Error occurs such as the following: Map 'NameIDX' not defined in table 'Sample.Person', but it was specified in the frozen plan for the query. Dropping or adding an index causes a recompile of the table, changing the Last Compile Time value. The Clear Error button can be used to clear the Plan Error field once the condition that caused the error has been corrected — for example, by re-creating the missing index. Using the Clear Error button after the error condition has been corrected causes both the Plan Error field and the Clear Error button to disappear. For further details, refer to Frozen Plan in Error.

Routines and Relations Sections

Statement is defined in the following routines section:

  • Routine: the class name associated with the table (for DDL), 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 DDL was last compiled.

  • Classname: the classname associated with the table.

This section includes an 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.

Data Definition (DDL) SQL Statements

Creating a table compiles a corresponding Persistent Class, and therefore creates one or more SQL Statements. Creating a view does not create a persistent class, so no SQL Statements are created.

You can create an SQL table by defining it as a persistent class, or defining it using the SQL CREATE TABLE command from either Embedded SQL or Dynamic SQL. Regardless of how you create a table, the system creates one or more corresponding SQL Statements. The Location specifies the class name associated with the table definition.

When you create a table, Caché SQL defines a bitmap extent index. In doing so, it creates a corresponding SQL Statement, such as the following:

DECLARE QEXTENT CURSOR FOR SELECT ID FROM SAMPLE . SQLTEST

Altering a table definition recompiles the existing create table SQL Statements.

When you create an index, either by modifying the table class definition or by issuing an SQL CREATE INDEX command, Caché stores a corresponding SQL Statement, such as the following, for each created index. The SQL Statement is the same regardless of the type of index created. Creating more than one type of index for a field does not store additional SQL statements:

SELECT %ID INTO :id FROM SAMPLE . SQLTEST WHERE ( :K1 IS NOT NULL AND LASTNAME = :K1 ) OR ( :K1 IS NULL AND LASTNAME IS NULL )

Adding an index also causes all of the create table SQL Statements to be re-compiled, updating the Plan Timestamp for all of them, including SQL Statements for other indices. A DROP INDEX removes this SQL Statement and causes all of the remaining DDL SQL Statements to be re-compiled, updating the Plan Timestamp.

If you define a Primary Key or Unique constraint, Cache defines a primary key index, and therefore creates an SQL Statement such as the one above. In addition to the two SQL Statements described above, Caché adds the following four statements (in this example the LastName field):

SELECT 1 AS _PASSFAIL FROM SAMPLE . SQLTEST WHERE LASTNAME = :pValue(1) AND %ID <> :id 
SELECT LASTNAME INTO :tCol1 FROM SAMPLE . SQLTEST WHERE %ID = :pID  
DECLARE EXT CURSOR FOR SELECT %ID INTO :tID FROM SAMPLE . SQLTEST 
SELECT %ID INTO :id FROM SAMPLE . SQLTEST WHERE LASTNAME = :%d(2) 

Each additional UNIQUE field adds three more SQL statements like these: specifying in the WHERE clause the UNIQUE field (In this example the SSN field).

SELECT %ID INTO :id FROM SAMPLE . SQLTEST WHERE ( :K1 IS NOT NULL AND SSN = :K1 ) OR ( :K1 IS NULL AND SSN IS NULL )
SELECT 1 AS _PASSFAIL FROM SAMPLE . SQLTEST WHERE SSN = :pValue(1) AND %ID <> :id
SELECT %ID INTO :id FROM SAMPLE . SQLTEST WHERE SSN = :%d(3)

It also modifies the following statement to include the additional UNIQUE field, such as the following:

SELECT SSN , LASTNAME INTO :tCol2 , :tCol1 FROM SAMPLE . SQLTEST WHERE %ID = :pID

Delete Table and SQL Statements

When a table is deleted, all non-frozen SQL Statements (Plan State Unfrozen) are deleted. Frozen statements (Plan State Frozen/Explicit) are not deleted, but the Table/View/Procedure Name(s) column is modified, as in the following example: SAMPLE.MYTESTTABLE - Deleted?? Sample.Person; the name of the deleted table is converted to all uppercase letters and is flagged as “Deleted??”. The Location column is blank for DDL statements because the table has been deleted. The Location column is blank for Dynamic SQL DML statements because all cached queries associated with the table have been automatically purged.

Querying the SQL Statements

You can use the SQLTableStatements()Opens in a new tab 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()

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

  • INFORMATION_SCHEMA.STATEMENTS: Contains SQL Statement Index entries that can be accessed by the current user in the current namespace.

  • INFORMATION_SCHEMA.STATEMENT_LOCATIONS: Contains each routine location from which an SQL statement is invoked: the persistent class name or the cached query name.

  • INFORMATION_SCHEMA.STATEMENT_RELATIONS: Contains each table or view entry use by an SQL statement.

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

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

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'

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

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 ( * ) '

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

Importing SQL Statements

Import an SQL Statement or multiple SQL Statements from a previously-exported file: Use the ImportSQLStatement()Opens in a new tab method.

FeedbackOpens in a new tab