InterSystems IRIS Data Platform 2020.4 > Class Reference > %SYS namespace > %SYSTEM.SQL.Statement

%SYSTEM.SQL.Statement

abstract class %SYSTEM.SQL.Statement extends %SYSTEM.Help

Method Inventory

Methods

classmethod Clean() [ Language = objectscript ]
Cleanup any SQL statement index entries that no longer have any routines/classes associated with them
classmethod ClearAll() as %Status [ Language = objectscript ]
Clear SQL statistics for all query plans in this namespace Example: set st=$SYSTEM.SQL.Statement.ClearAll()
classmethod ClearRelation(pRelation As %String) as %Status [ Language = objectscript ]
Clear SQL statistics for all query plans for the relation provided in pRelation

Parameter:
pRelation must be in internal qualified format, which is typically simply Schema.Tablename, but if tablename itself contains any "." characters, it needs to be in Schema_"."_$translate(Tablename,".",$Char(2)) format Example: Clear SQL statistics for all statements for the current namespace for each statement that references the XLT.Person table: set st=$SYSTEM.SQL.Statement.ClearRelation("XLT.Person")
classmethod ClearSchema(pSchema As %String) as %Status [ Language = objectscript ]
Clear SQL statistics for all query plans for the schema name provided in pSchema Example: Clear SQL statistics for all statements for the current namespace for each statement that references a relation in the XLT schema: set st=$SYSTEM.SQL.Statement.ClearSchema("XLT")
classmethod ClearStatement(pStatementHash As %String) as %Status [ Language = objectscript ]
Clear SQL statistics for all query plans with the statement hash id provided in pStatementHash Example: Clear SQL statistics for statement identified by hash "3DgIqc72NS+Np6nybddb719NKb8=": set st=$SYSTEM.SQL.Statement.ClearStatement("3DgIqc72NS+Np6nybddb719NKb8=")
classmethod ExportAllFrozenPlans(Filename As %Library.String = "") as %Library.Status [ Language = objectscript ]
Exports all SQL Statement Index entries in this namespace, including query plans. Generated file can be loaded using $SYSTEM.SQL.Statement.ImportFrozenPlans().
The main reason to export/import an SQL Statement entry is to move a frozen plan from one location to another. Other than moving a frozen plan, there is not much benefit of moving an SQL Statement definition from one location to another. When exporting SQL Statement definitions, the Locations (routines) the SQL Statement is defined in is not exported. This is because it is possible the Locations are different or don't exist on the target system. Recompiling the class/routines that contain the SQL Statement on the target system will redefine the Location entries for the SQL Statement definition.
Any SQL Statements the current user does not have privilege to run will not be exported.

Parameter:
Filename
Name of the file to output the SQL Statement definitions to.

Examples:

  • set st=$SYSTEM.SQL.Statement.ExportAllFrozenPlans("C:\MyAllStatements.xml") // Exports all SQL Statements
classmethod ExportFrozenPlans(Filename As %Library.String = "", Hash As %Library.String = "") as %Library.Status [ Language = objectscript ]
Export an SQL Statement Index entry, including query plan. Generated file can be loaded using $SYSTEM.SQL.Statement.ImportFrozenPlans().
The main reason to export/import an SQL Statement entry is to move a frozen plan from one location to another. Other than moving a frozen plan, there is not much benefit of moving an SQL Statement definition from one location to another. When exporting SQL Statement definitions, the Locations (routines) the SQL Statement is defined in is not exported. This is because it is possible the Locations are different or don't exist on the target system. Recompiling the class/routines that contain the SQL Statement on the target system will redefine the Location entries for the SQL Statement definition.
If the current user is not privileged to execute this SQL Statement, a privilege error will be returned and the export will not occur.

Parameter:
Filename
Name of the file to output the SQL Statement definition to.
Hash
SQL Statement definition hash, used as the ID of the SQL Statement Definition index entry

Examples:

  • set st=$SYSTEM.SQL.Statement.ExportFrozenPlans("C:\MyStatement.xml","Kod99B0VFOn/aXQ9PyRGfb64q04=") // Exports SQL Statement with ID hash of 'Kod99B0VFOn/aXQ9PyRGfb64q04='
classmethod FreezeAll() as %Status [ Language = objectscript ]
Freeze all query plans in this namespace Example: set st=$SYSTEM.SQL.Statement.FreezeAll()
classmethod FreezeRelation(relation As %String = "") as %Status [ Language = objectscript ]
Freeze all query plans for the relation Example: set st=$SYSTEM.SQL.Statement.FreezeRelation("XLT.Person")
classmethod FreezeSchema(schema As %String = "") as %Status [ Language = objectscript ]
Freeze all query plans for the schema Example: set st=$SYSTEM.SQL.Statement.FreezeSchema("XLT")
classmethod FreezeStatement(hash As %String = "") as %Status [ Language = objectscript ]
Freeze the query plan with the statement hash provided in hash Example: set st=$SYSTEM.SQL.Statement.FreezeStatement("3DgIqc72NS+Np6nybddb719NKb8=")
classmethod ImportFrozenPlans(Filename As %Library.String = "", Display As %Library.Boolean = 1) as %Library.Status [ Language = objectscript ]
Import a file containing SQL Statement Index entries. Files can be loaded that were generated with $SYSTEM.SQL.Statement.ExportStatement() or $SYSTEM.SQL.Statement.ExportAllStatements().
The main reason to export/import an SQL Statement entry is to move a frozen plan from one location to another. Other than moving a frozen plan, there is not much benefit of moving an SQL Statement definition from one location to another. When exporting SQL Statement definitions, the Locations (routines) the SQL Statement is defined in is not exported. This is because it is possible the Locations are different or don't exist on the target system. Recompiling the class/routines that contain the SQL Statement on the target system will redefine the Location entries for the SQL Statement definition.

Parameter:
Filename
Name of the file to output the SQL Statement definition to.
Display
1/0 Flag. If 1, display import progress on the screen. Default is 1.

Examples:

  • set st=$SYSTEM.SQL.Statement.ImportFrozenPlans("C:\MyStatements.xml",1) // Imports SQL Statement(s) defined in the file
classmethod UnfreezeAll(upgradeOnly As %Boolean = 0) as %Status [ Language = objectscript ]
If upgradeOnly is false, unfreeze all query plans that are marked Frozen/Explicit or Frozen/Upgrade. This is the default. If upgradeOnly is true, unfreeze all query plans that are marked Frozen/Upgrade Example: set st=$SYSTEM.SQL.Statement.UnfreezeAll()
classmethod UnfreezeRelation(relation As %String = "", upgradeOnly As %Boolean = 0) as %Status [ Language = objectscript ]
If upgradeOnly is false, unfreeze all query plans for the relation that are marked Frozen/Explicit or Frozen/Upgrade. This is the default. If upgradeOnly is true, unfreeze all query plans for the relation that are marked Frozen/Upgrade Example: set st=$SYSTEM.SQL.Statement.UnfreezeRelation("XLT.Person")
classmethod UnfreezeSchema(schema As %String = "", upgradeOnly As %Boolean = 0) as %Status [ Language = objectscript ]
If upgradeOnly is false, unfreeze all query plans for the schema that are marked Frozen/Explicit or Frozen/Upgrade. This is the default. If upgradeOnly is true, unfreeze all query plans for the schema that are marked Frozen/Upgrade Example: set st=$SYSTEM.SQL.Statement.UnfreezeSchema("XLT")
classmethod UnfreezeStatement(hash As %String = "", upgradeOnly As %Boolean = 0) as %Status [ Language = objectscript ]
If upgradeOnly is false, unfreeze the query plan with the statement hash provided in hash if it's marked Frozen/Explicit or Frozen/Upgrade. This is the default. If upgradeOnly is true, unfreeze the query plan with the statement hash provided in hash if it's marked Frozen/Upgrade Example: set st=$SYSTEM.SQL.Statement.UnfreezeStatement("3DgIqc72NS+Np6nybddb719NKb8=")

Inherited Members

Inherited Methods

Feedback