Skip to main content

%SYSTEM.SQL.Statement

Class %SYSTEM.SQL.Statement Extends %SYSTEM.Help [ Abstract, Not ProcedureBlock, System = 4 ]

Methods​

Clean​

ClassMethod Clean()

Cleanup any SQL statement index entries that no longer have any routines/classes associated with them

ClearAll​

ClassMethod ClearAll() As %Status [ ProcedureBlock = 1 ]

Clear SQL statistics for all query plans in this namespace Example: set st=$SYSTEM.SQL.Statement.ClearAll()

ClearRelation​

ClassMethod ClearRelation(pRelation As %String) As %Status [ ProcedureBlock = 1 ]

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

ClearSchema​

ClassMethod ClearSchema(pSchema As %String) As %Status [ ProcedureBlock = 1 ]

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

ClearStatement​

ClassMethod ClearStatement(pStatementHash As %String) As %Status [ ProcedureBlock = 1 ]

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=")

ExportAllFrozenPlans​

ClassMethod ExportAllFrozenPlans(Filename As %Library.String = "") As %Library.Status [ ProcedureBlock = 1 ]

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

ExportFrozenPlans​

ClassMethod ExportFrozenPlans(Filename As %Library.String = "", Hash As %Library.String = "") As %Library.Status [ ProcedureBlock = 1 ]

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='

FreezeAll​

ClassMethod FreezeAll() As %Status [ ProcedureBlock = 1 ]

Freeze all query plans in this namespace Example: set st=$SYSTEM.SQL.Statement.FreezeAll()

FreezeSchema​

ClassMethod FreezeSchema(schema As %String = "") As %Status [ ProcedureBlock = 1 ]

Freeze all query plans for the schema Example: set st=$SYSTEM.SQL.Statement.FreezeSchema("XLT")

FreezeRelation​

ClassMethod FreezeRelation(relation As %String = "") As %Status [ ProcedureBlock = 1 ]

Freeze all query plans for the relation Example: set st=$SYSTEM.SQL.Statement.FreezeRelation("XLT.Person")

FreezeStatement​

ClassMethod FreezeStatement(hash As %String = "") As %Status [ ProcedureBlock = 1 ]

Freeze the query plan with the statement hash provided in hash Example: set st=$SYSTEM.SQL.Statement.FreezeStatement("3DgIqc72NS+Np6nybddb719NKb8=")

UnfreezeAll​

ClassMethod UnfreezeAll(upgradeOnly As %Boolean = 0) As %Status [ ProcedureBlock = 1 ]

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

UnfreezeSchema​

ClassMethod UnfreezeSchema(schema As %String = "", upgradeOnly As %Boolean = 0) As %Status [ ProcedureBlock = 1 ]

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

UnfreezeRelation​

ClassMethod UnfreezeRelation(relation As %String = "", upgradeOnly As %Boolean = 0) As %Status [ ProcedureBlock = 1 ]

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

UnfreezeStatement​

ClassMethod UnfreezeStatement(hash As %String = "", upgradeOnly As %Boolean = 0) As %Status [ ProcedureBlock = 1 ]

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=")

ImportFrozenPlans​

ClassMethod ImportFrozenPlans(Filename As %Library.String = "", Display As %Library.Boolean = 1) As %Library.Status [ ProcedureBlock = 1 ]

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