%SYSTEM.SQL.Statement

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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