Inheritance diagram for Table:

Collaboration diagram for Table:

Static Public Member Functions
_.Library.Status	ClearSchemaStats (_.Library.String schema)
	Clear the table stats (selectivity, histogram info, extent size, etc.) for all classes/tables and their properties/fields within a schema. More...

_.Library.Status	ClearTableStats (_.Library.String table)
	Clear the table stats (selectivity, histogram info, extent size, etc.) for a class/table and its properties/fields. More...

_.Library.Status	Export (_.Library.String pFilename, _.Library.String pSchemaFilter, _.Library.String pTableFilter, _.Library.Boolean pDisplay)
	Export extentsize and selectivity for tables/fields to an XML file. More...

_.Library.Status	GatherSchemaStats (_.Library.String schema, _.Library.String logFile)
	Calculate and update extentsize and selectivity for all classes/tables and their properties/fields within a schema. More...

_.Library.Status	GatherTableStats (_.Library.String table, _.Library.String logFile)
	Calculate and update extentsize and selectivity for a class/table and its properties/fields. More...

_.Library.Status	Import (_.Library.String pFilename, _.Library.Boolean pDisplay, _.Library.Boolean pClearCurrentStats)
	Import extentsize, selectivity, blockcount for a table and its fields from a file created by $SYSTEM.SQL.Stats.Table.Export(). More...

_.Library.Status	SetExtentSize (_.Library.String schema, _.Library.String tablename, _.Library.String newextentsize)
	Set the EXTENTSIZE of a table to the value of the given extentsize. More...

_.Library.Status	SetFieldSelectivity (_.Library.String schema, _.Library.String tablename, _.Library.String fieldname, _.Library.String selectivity)
	Set the SELECTIVITY of a field and property to the value of the given selectivity. More...

Static Public Member Functions inherited from Help
_.Library.String	Help (_.Library.String method)
	This is a helper class that is used by the various SYSTEM classes to provide a Help method. More...

Member Function Documentation

◆ ClearSchemaStats()

_.Library.Status ClearSchemaStats ( _.Library.String schema )

static

Clear the table stats (selectivity, histogram info, extent size, etc.) 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.

Examples:

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

◆ ClearTableStats()

_.Library.Status ClearTableStats ( _.Library.String table )

static

Clear the table stats (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 table stats for SQLUser.IscPerson

◆ Export()

_.Library.Status Export	(	_.Library.String	pFilename,
		_.Library.String	pSchemaFilter,
		_.Library.String	pTableFilter,
		_.Library.Boolean	pDisplay
	)

static

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

Examples:

Do $SYSTEM.SQL.Stats.Table.Export("C:\AllStats.xml") // Exports TuneTable Statistics for all schemas/tables in the current namespace
Do $SYSTEM.SQL.Stats.Table.Export("C:\SampleStats.xml","Sample") // Exports TuneTable Statistics for all Sample.* tables in the current namespace
Do $SYSTEM.SQL.Stats.Table.Export("C:\SamplePStats.xml","Sample","P*") // Exports TuneTable Statistics for all Sample.P* in the current namespace
Do $SYSTEM.SQL.Stats.Table.Export("C:\SamplePersonStats.xml","Sample","Person") // Exports TuneTable Statistics for table Sample.Person in the current namespace

◆ GatherSchemaStats()

_.Library.Status GatherSchemaStats	(	_.Library.String	schema,
		_.Library.String	logFile
	)

static

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

◆ GatherTableStats()

_.Library.Status GatherTableStats	(	_.Library.String	table,
		_.Library.String	logFile
	)

static

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

_.Library.Status Import	(	_.Library.String	pFilename,
		_.Library.Boolean	pDisplay,
		_.Library.Boolean	pClearCurrentStats
	)

static

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

◆ SetExtentSize()

_.Library.Status SetExtentSize	(	_.Library.String	schema,
		_.Library.String	tablename,
		_.Library.String	newextentsize
	)

static

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)

◆ SetFieldSelectivity()

_.Library.Status SetFieldSelectivity	(	_.Library.String	schema,
		_.Library.String	tablename,
		_.Library.String	fieldname,
		_.Library.String	selectivity
	)

static

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. <number> - 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%")

Static Public Member Functions

Member Function Documentation

◆ ClearSchemaStats()

◆ ClearTableStats()

◆ Export()

◆ GatherSchemaStats()

◆ GatherTableStats()

◆ Import()

◆ SetExtentSize()

◆ SetFieldSelectivity()