Statistics Management

The Data Virtuality Server can gather a number of statistical parameters - for more information, please see Data Sources Statistics.

SYSADMIN.gatherStatistics

This procedure gathers statistics for a particular data source synchronously.

SQL

SYSADMIN.gatherStatistics(<IN string datasource>, <IN boolean gatherTableCardinalities>, <IN boolean gatherColumnCardinalities>, <IN boolean gatherNetworkStats>, <IN string excludeTables> <IN boolean gatherForOnlyTablesWithoutStats>)

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	Description
`datasource`	Name of the data source to gather statistics for
`gatherTableCardinalities`	Whether or not the tables' statistics should be gathered
`gatherColumnCardinalities`	Whether or not the columns' statistics should be gathered
`gatherNetworkStats`	Whether the network statistics should be gathered
`excludeTables`	Tables to be excluded from the statistics gathering (regular expression)
`gatherForOnlyTablesWithoutStats`	If set to `TRUE`, only tables without previously gathered statistics will be checked

Example

SQL

CALL "SYSADMIN.gatherStatistics"( 
	"datasource" => 'postgres', 
	"gatherTableCardinalities" => TRUE, 
	"gatherColumnCardinalities" => TRUE, 
	"gatherNetworkStats" => FALSE, 
	"excludeTables" => 'table1', 
	"gatherForOnlyTablesWithoutStats" => FALSE 
);;

SYSADMIN.createGatherStatisticsJob

This procedure creates a job to gather statistics for a particular data source.

SQL

SYSADMIN.createGatherStatisticsJob(IN datasource string, IN gatherTableCardinalities boolean NOT NULL, IN gatherColumnCardinalities boolean NOT NULL, IN gatherNetworkStats boolean NOT NULL, IN excludeTables string, IN boolean gatherForOnlyTablesWithoutStats, IN parallelRunsAllowed integer, IN retryCounter integer, IN retryDelay integer, IN jobName biginteger, IN uuid string )

It has the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	Description
`datasource`	Name of the data source to gather statistics for
`gatherTableCardinalities`	Whether or not the tables' statistics should be gathered
`gatherColumnCardinalities`	Whether or not the columns' statistics should be gathered
`gatherNetworkStats`	Whether the network statistics should be gathered
`excludeTables`	Tables to be excluded from the statistics gathering (regular expression)
`gatherForOnlyTablesWithoutStats`	If set to `TRUE`, only tables without previously gathered statistics will be checked
`parallelRunsAllowed`	Number of parallel runs allowed
`retryCounter`	Number of retry attempts if the job fails
`retryDelay`	Delay in seconds between retry attempts
`runTimeout`	Job timeout in minutes
`jobName`	Name of job
uuid	Custom uuid for the job

Example

SQL

CALL "SYSADMIN.createGatherStatisticsJob"( 
	"datasource" => 'postgres', 
	"gatherTableCardinalities" => TRUE, 
	"gatherColumnCardinalities" => TRUE, 
	"gatherNetworkStats" => FALSE, 
	"excludeTables" => 'table1', 
	"gatherForOnlyTablesWithoutStats" => FALSE, 
	"parallelRunsAllowed" => 3, 
	"retryCounter" => 20, 
	"retryDelay" => 30, 
	"runTimeout" => 40, 
	"jobName" => 'string_jobName1',
	"uuid" => '79259976-63cb-11ee-8c99-0242ac120002' 
);;

uuid parameter is available since v4.1

SYSADMIN.deleteStatistics

This procedure deletes statistics for a particular data source.

SQL

SYSADMIN.deleteStatistics(<IN string elementName>)

Examples

1. Deleting all statistics from the internal database and resetting all the current statistics shown in SYS.Tables and SYS.Columns:

SQL

CALL SYSADMIN.deleteStatistics('*')

2. Deleting all table and column statistics related to the test_tables schema:

SQL

CALL SYSADMIN.deleteStatistics('test_tables')

3. Deleting all table and column statistics related to the test_tables.test_a table:

SQL

CALL SYSADMIN.deleteStatistics('test_tables.test_a')

4. Deleting only column statistics related to the test_tables.test_a.a column:

SQL

CALL SYSADMIN.deleteStatistics('test_tables.test_a.a')

SYSADMIN.setColumnStats

This procedure sets statistics for the given column.

SQL

SYSADMIN.setColumnStats(IN tableName string NOT NULL, IN columnName string NOT NULL, IN distinctCount long, IN nullCount long cardinality, IN max string, IN min string)

All statistics values are nullable. Passing a NULL stat value will leave the corresponding metadata value unchanged.

SYSADMIN.setTableStats

This procedure sets statistics for the given table.

SQL

SYSADMIN.setTableStats(IN tableName string NOT NULL, IN cardinality long NOT NULL)