%SYSTEM.SQL.Stats.Table

Clear the fixed table statistics (selectivity, histogram info, extent size, etc.) for all classes/tables and their properties/fields within a schema.

Parameters:

schema

Name of a schema If the schema name is omitted, the default schema is used.

Examples:

• Do $SYSTEM.SQL.Stats.Table.ClearSchemaStats("MedLab")
• Do $SYSTEM.SQL.Stats.Table.ClearSchemaStats("") ; Clear the fixed table statistics for SQLUser schema

Clear the fixed table statistics (selectivity, histogram info, extent size, etc.) for a class/table and its properties/fields.

Parameter:

table

Name of a table or "*" to Clear the table stats for all tables in the current namespace. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.

Examples:

• Do $SYSTEM.SQL.Stats.Table.ClearTableStats("MedLab.Patient")
• Do $SYSTEM.SQL.Stats.Table.ClearTableStats("""Medical Lab"".Patient")
• Do $SYSTEM.SQL.Stats.Table.ClearTableStats("IscPerson") ; Clear the fixed table statistics for SQLUser.IscPerson table

Export extentsize and selectivity for tables/fields to an XML file. Generated file can be loaded using $SYSTEM.SQL.Stats.Table.Import().

Parameter:

pFilename

Name of the file to output the table(s) tuning statistics to.

pSchemaFilter

Filter to limit the schemas output. The default is "", which means there is no filter applied and all schemas in the namespace are exported. pSchemaFilter uses '_' to signify any single character, '*' to signify 0 through N characters, and ' to signify NOT.

pTableFilter

Filter to limit the tables output. The default is "", which means there is no filter applied and all tables in the specified schemas are exported. pTableFilter uses '_' to signify any single character, '*' to signify 0 through N characters, and ' to signify NOT.

pDisplay

TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is TRUE (1).

pType

Integer flag (1/2/3) to indicate the type of table statistics need to be output. 1 means just export the fixed table statistics in class definition; 2 means just export the latest collected table statistics; 3 means export both the fixed table statistics in class definition and the latest collected table statistics

Examples:

• Do $SYSTEM.SQL.Stats.Table.Export("C:\AllStats.xml") // Exports the fixed table statistics in class definition for all schemas/tables in the current namespace
• Do $SYSTEM.SQL.Stats.Table.Export("C:\SampleStats.xml","Sample") // Exports the fixed table statistics in class definition for all Sample.* tables in the current namespace
• Do $SYSTEM.SQL.Stats.Table.Export("C:\SamplePStats.xml","Sample","P*") // Exports the fixed table statistics in class definition for all Sample.P* in the current namespace
• Do $SYSTEM.SQL.Stats.Table.Export("C:\SamplePersonStats.xml","Sample","Person") // Exports the fixed table statistics in class definition for table Sample.Person in the current namespace
• Do $SYSTEM.SQL.Stats.Table.Export("C:\SamplePersonStats.xml","Sample","Person",,2) // Exports the latest collected table statistics for table Sample.Person in the current namespace
• Do $SYSTEM.SQL.Stats.Table.Export("C:\SamplePersonStats.xml","Sample","Person",,3) // Exports both the fixed table statistics in class definition and the latest collected table statistics for table Sample.Person in the current namespace

Fix table statistics in class definition via copying the latest collected table statistics into the class definition storage within a schema.

Parameters:

schema

Name of a schema.
If the schema name is omitted, the default schema is used.

Examples:

• Do $SYSTEM.SQL.Stats.Table.FixSchemaStats("MedLab")
• Do $SYSTEM.SQL.Stats.Table.FixSchemaStats("") ; Copy the latest collected table statistics into the class definition storage for the default schema

Fix table statistics in class definition via copying the latest collected table statistics into the class definition storage for a given table

Parameter:

table

Name of a table. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.

statsInfoID

ID of table statistic information, default is "" means the latest collected table statistics

Examples:

• Do $SYSTEM.SQL.Stats.Table.FixTableStats("MedLab.Patient"); Copy the latest collected table statistics of MedLab.Patient into the class definition storage
• Do $SYSTEM.SQL.Stats.Table.FixTableStats("MedLab.Patient",3); Copy the collected table statistics of MedLab.Patient with table stats info ID = 3 into the class definition storage
• Do $SYSTEM.SQL.Stats.Table.FixTableStats("""Medical Lab"".Patient")
• Do $SYSTEM.SQL.Stats.Table.FixTableStats("IscPerson") ; Copy the latest collected table statistics of SQLUser.IscPerson into the class definition storage

Calculate and update extentsize and selectivity for all classes/tables and their properties/fields within a schema.

Parameters:

schema

Name of a schema to tune tables. If the schema name is omitted, the default schema is used.

logFile

Optional name of a file to log the output of the TuneTable utility to. If logFile isn't supplied, the output will go to the current device.

Examples:

• Do $SYSTEM.SQL.Stats.Table.GatherSchemaStats("MedLab","TuneLog.txt")
• Do $SYSTEM.SQL.Stats.Table.GatherSchemaStats("""Medical Lab""")
• Do $SYSTEM.SQL.Stats.Table.GatherSchemaStats("") ; Tunes SQLUser schema

Calculate and update extentsize and selectivity for a class/table and its properties/fields.

Parameter:

table

Name of a table or "*" to tune all tables in the current namespace. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.

logFile

Optional name of a file to log the output of the TuneTable utility to. If logFile isn't supplied, the output will go to the current device.

Examples:

• Do $SYSTEM.SQL.Stats.Table.GatherTableStats("MedLab.Patient","Tune.log")
• Do $SYSTEM.SQL.Stats.Table.GatherTableStats("""Medical Lab"".Patient")
• Do $SYSTEM.SQL.Stats.Table.GatherTableStats("IscPerson") ; Tunes SQLUser.IscPerson

Import extentsize, selectivity, blockcount for a table and its fields from a file created by $SYSTEM.SQL.Stats.Table.Export().

Parameter:

pFilename

Name of the file to output the table(s) tuning statistics to.

pDisplay

TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is TRUE (1).

pClearCurrentStats

TRUE(1)/FALSE(0) flag. If TRUE(1), any EXTENTSIZE, SELECTIVITY, BLOCKCOUNT, etc. will be cleared from the existing table prior to importing the stats. This can be used if you want to completely clear stats that are not specified in the import file instead of leaving them defined in class/table. The default is FALSE (0)

Examples:

• Do $SYSTEM.SQL.Stats.Table.Import("C:\AllStats.xml") // Import TuneTable Statistics for all schemas/tables that were exported with the $SYSTEM.SQL.Stats.Table.Export() to the AllStats.xml file

Remove stale collected statistics for a given table, a given schema, or the entire extent.

Parameter:

schema

Name of a schema.

If the schema name is "*", remove for the entire extent.
If the schema name is "", the default schema is used.

table

Name of a table.

If the table name is "*", remove for all the tables within the given schema.

expirationDays

The number of days to keep collected table statistics. Any collected table statistics older than this period will be deleted. For example, if expirationDays is set to 30 (by default), the function will remove any statistics older than 30 days.

If pExpirationDays=0: purge all statistics for a given table, a given schema, or the entire extent.
If pExpirationDays=-1: purge all but the most recent statistics for a given table, a given schema, or the entire extent.

Examples:

• Do $SYSTEM.SQL.Stats.Table.PurgeStaleStats("*") ; Purge any collected statistics older than 30 days for the entire extent
• Do $SYSTEM.SQL.Stats.Table.PurgeStaleStats("Sample","*",10) ; Purge any collected statistics older than 10 days within schema Sample
• Do $SYSTEM.SQL.Stats.Table.PurgeStaleStats("Sample","Person",0) ; Purge all the collected statistics for table Sample.Person
• Do $SYSTEM.SQL.Stats.Table.PurgeStaleStats("Sample","Person",-1) ; Purge all but the most recent statistics for table Sample.Person

Set the EXTENTSIZE of a table to the value of the given extentsize Parameter:

schema

Name of the table's schema. Default is the default schema.

tablename

Name of the table the field is in (required).

newextentsize

New extent size value for the field (required). The extent size of a table is the number of rows. Must be a positive number. Returns:

Status Code

Example:

• Do $SYSTEM.SQL.Stats.Table.SetExtentSize("Sample","Person",135)

Set the SELECTIVITY of a field and property to the value of the given selectivity

Parameter:

schema

Name of the table's schema. Default is the default schema.

tablename

Name of the table the field is in (required).

fieldname

Name of the field to set the SELECTIVITY for (required).

selectivity

New selectivity value for the field (required). The selectivity of a property specifies the approximate frequency of specific values within the entire distribution of values. The Selectivity value for a column is generally the percentage of rows within a table that would be returned as a result of query searching for a typical value of the column. For example, suppose a table contains a Gender column whose value is evenly distributed between "M" and "F". The Selectivity value for the Gender column would be 50%, as a typical query based on Gender would find 50% of the rows within the table. The Selectivity value for a more unique property, such as TaxID, is typically a small percentage that varies according to the size of the table. Examples of values you can specify here are:

"10%" - Means that typical values for this column will return 10% of the rows in the table

"1" - Means this field is unique. For any given value, it will return 1 row from the table.

- A pure number will calculate the selectivity as EXTENTSIZE/selectivity. For example, if EXTENTSIZE is 100000 and selectivity is 1000, this will set the selectivity to 1%.

"NUMROWS" - This is the same as specifying "1", it means the field is unique. This is allowed for legacy support of M/SQL tables that have been converted to class definitions.

NUMROWS/positive_integer - This will calculate the SQL SELECTIVITY as EXTENTSIZE/positive_integer. For example if EXTENTSIZE is 100000 and you specify NUMROWS/5000, this will set the SQL SELECTIVITY to 20, which means for a typical value for the field, 20 rows of the table will be returned. This is allowed for legacy support of M/SQL tables that have been converted to class definitions.

There is no validation of the value you enter for the SELECTIVITY. If you enter something not recognized as a valid SELECTIVITY, such as the string "nonsense", it will be turned into a value of 0. If the SQL query processor sees a SELECTIVITY of 0, it will attempt to come up with a typical SELECTIVITY value for the field based on how many rows are in the table and whether or not the field is a reference column, is part of the IDKEY field, has a VALUELIST specification, etc.

Returns:

Status Code

Example:

• Do $SYSTEM.SQL.Stats.Table.SetFieldSelectivity("MedLab","Patient","Home_Phone","2.5%")

Set a property of table statistics to a given value within a schema. This is a runtime setting that does not affect the class definition.

Parameters:

schema

Name of a schema.
If the schema name is omitted, the default schema is used.

property

Name of the property of table statistic, could be either "IgnoreFixedStats" or "SkipAutomaticStatsCollection".

value

Value of the property of table statistic, could be either 1 or 0.

Examples:

• Do $SYSTEM.SQL.Stats.Table.SetSchemaRuntimeTableStatsProperty("MedLab","IgnoreFixedStats",1)
• Do $SYSTEM.SQL.Stats.Table.SetSchemaRuntimeTableStatsProperty("","SkipAutomaticStatsCollection",1) ; Set table statistic property: SkipAutomaticStatsCollection to 1 for the default schema

Set a property of table statistics to a given value for a given table. This is a runtime setting that does not affect the class definition.

Parameters:

table

Name of a table. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.

property

Name of the property of table statistic, could be either "IgnoreFixedStats" or "SkipAutomaticStatsCollection".

value

Value of the property of table statistic, could be either 1 or 0.

Examples:

• Do $SYSTEM.SQL.Stats.Table.SetTableRuntimeTableStatsProperty("MedLab.Patient","IgnoreFixedStats",1)
• Do $SYSTEM.SQL.Stats.Table.SetTableRuntimeTableStatsProperty("IscPerson","SkipAutomaticStatsCollection",1) ; Set table statistic property: SkipAutomaticStatsCollection to 1 for SQLUser.IscPerson table