sp_iqstatistics procedure

Function

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

Syntax

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

Description

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%'