sp_iqstatistics Procedure

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

Syntax

sp_iqstatistics [ stat_name ]

Parameter

Privileges

You must have EXECUTE privilege on the system procedure, as well as the MANAGE ANY STATISTICS system privilege.

Remarks

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 SAP Sybase IQ server since last server startup Second
1 CpuUserTime CPU user time in seconds consumed by the SAP Sybase IQ server since last server startup Second
2 CpuSystemTime CPU system time in seconds consumed by the SAP Sybase IQ server since last server startup Second
3 ThreadsFree Number of SAP Sybase IQ threads free N/A
4 ThreadsInUse Number of SAP Sybase IQ threads in use N/A
5 MemoryAllocated Allocated memory in megabytes MB
6 MemoryMaxAllocated Max allocated memory in megabytes MB
7 MainCacheCurrentSize Main buffer cache current size in megabytes MB
8 MainCacheFinds Main buffer cache total number of lookup requests N/A
9 MainCacheHits Main buffer cache total number of hits N/A
10 MainCachePagesPinned Main buffer cache number of pages pinned Page
11 MainCachePagesPinnedPercentage Percentage of main buffer cache pages pinned %
12 MainCachePagesDirtyPercentage Percentage of main buffer cache pages dirtied %
13 MainCachePagesInUsePercentage Percentage of main buffer 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 SAP Sybase IQ resource governor N/A
29 OperationsActive Number of active concurrent operations admitted by SAP Sybase IQ resource governor N/A
30 OperationsActiveLoadTableStatements Number of active LOAD TABLE statements N/A

Example

Displays a single statistic, the total CPU time:

sp_iqstatistics 'CpuTotalTime'

Displays all statistics for MainCache%:

SELECT * from sp_iqstatistics() WHERE stat_name LIKE 'MainCache%'
stat_num stat_name stat_desc stat_value stat_unit
7 MainCacheCurrentSize Cache dbspace current size in megabytes 64 mb
8 MainCacheFinds Cache dbspace total number of lookup requests 95303  
9 MainCacheHits Cache dbspace total number of hits 95283  
10 MainCachePagesPinned Cache dbspace number of pages pinned 0 page
11 MainCachePagesPinnedPercentage Percentage of cache dbspace pages pinned 0 %
12 MainCachePagesDirtyPercentage Percentage of cache dbspace pages dirtied 0.39 %
13 MainCachePagesInUsePercentage Percentage of cache dbspace pages in use 4.44 %
Parent topic: Procedures