sp_iqstatistics procedure

Function

Returns serial number, name, description, value, and unit specifier for each available statistic, or a specified statistic.

Syntax

sp_iqstatistics [ stat_name ]

Parameters

stat_name (Optional) VARCHAR parameter specifying the name of a statistic.

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

When stat_name is provided, sp_iqstatistics returns one row for the given statistic, or zero rows if the name is invalid. When invoked without any parameter, sp_iqstatistics returns all statistics.

Result set

Column name	Data type	Description
stat_num	UNSIGNED INTEGER	Serial number of a statistic
stat_name	VARCHAR(255)	Name of statistic
stat_desc	VARCHAR(255)	Description of statistic
stat_value	LONG VARCHAR	Value of statistic
stat_unit	VARCHAR(128)	Unit specifier

The following statistics may be returned:

stat_num	stat_name	stat_desc	stat_unit
0	CpuTotalTime	Total CPU time in seconds consumed by the IQ server since last server startup	Second
1	CpuUserTime	CPU user time in seconds consumed by the IQ server since last server startup	Second
2	CpuSystemTime	CPU system time in seconds consumed by the IQ server since last server startup	Second
3	ThreadsFree	Number of IQ threads free	N/A
4	ThreadsInUse	Number of IQ threads in use	N/A
5	MemoryAllocated	Allocated memory in megabytes	MB
6	MemoryMaxAllocated	Max allocated memory in megabytes	MB
7	MainCacheCurrentSize	Main cache current size in megabytes	MB
8	MainCacheFinds	Main cache total number of lookup requests	N/A
9	MainCacheHits	Main cache total number of hits	N/A
10	MainCachePagesPinned	Main cache number of pages pinned	Page
11	MainCachePagesPinnedPercentage	Percentage of main cache pages pinned	%
12	MainCachePagesDirtyPercentage	Percentage of main cache pages dirtied	%
13	MainCachePagesInUsePercentage	Percentage of main cache pages in use	%
14	TempCacheCurrentSize	Temporary cache current size in megabytes	MB
15	TempCacheFinds	Temporary cache total number of lookup requests	N/A
16	TempCacheHits	Temporary cache total number of hits	N/A
17	TempCachePagesPinned	Temporary cache number of pages pinned	Page
18	TempCachePagesPinnedPercentage	Percentage of temporary cache pages pinned	%
19	TempCachePagesDirtyPercentage	Percentage of temporary cache pages dirtied	%
20	TempCachePagesInUsePercentage	Percentage of temporary cache pages in use	%
21	MainStoreDiskReads	Number of kilobytes read from main store	KB
22	MainStoreDiskWrites	Number of kilobytes written to main store	KB
23	TempStoreDiskReads	Number of kilobytes read from main store	KB
24	TempStoreDiskWrites	Number of kilobytes written to main store	KB
25	ConnectionsTotalConnections	Total number of connections since server startup	N/A
26	ConnectionsTotalDisonnections	Total number of disconnections since server startup	N/A
27	ConnectionsActive	Number of active connections	N/A
28	OperationsWaiting	Number of operations waiting for IQ resource governor	N/A
29	OperationsActive	Number of active concurrent operations admitted by IQ resource governor	N/A
30	OperationsActiveLoadTableStatements	Number of active LOAD TABLE statements	N/A

Examples

Example 1 Displays a single statistic, the total CPU time:

sp_iqstatistics 'CPUTotalTime'

Example 2 Displays all statistics for MainCache%:

SELECT * from sp_iqstatistics() WHERE stat_name LIKE 'MainCache%'