SAP IQ 16.1 - Which tools can you recommend to track server KPI

[hrrudnytro111]

SAP IQ 16.1 - Which tools can you recommend to monitor server-side metrics?
SAP IQ Cockpit is not being used now

[ChristianK5e]

May I point to this blog post, where the IQ web service is employed to provide metrics to Prometheus, an open source time series database.

To create fancy dashboards you might use Grafana.

I did it once that way and I used this procedure with more metrics:

create or replace service metrics
  type 'raw'
  authorization off
  secure off
  url elements
  user dba
  as call get_metrics(:url);

create or replace procedure get_metrics(IN @URL long varchar)
  result(item long varchar)
  BEGIN
    declare @TS bigint;
    declare @IQ_cpu_total_seconds_total float;
    declare @IQ_cpu_user_seconds_total float;
    declare @IQ_cpu_system_seconds_total float;
    declare @IQ_threads_free integer;
    declare @IQ_threads_used integer;
    declare @IQ_memory_allocated float;
    declare @IQ_memory_allocated_max float;
    declare @IQ_cache_finds_total__main float;
    declare @IQ_cache_finds_total__temp float;
    declare @IQ_cache_hits_total__main float;
    declare @IQ_cache_hits_total__temp float;
    declare @IQ_cache_pages_dirty_percent__main float;
    declare @IQ_cache_pages_dirty_percent__temp float;
    declare @IQ_cache_pages_in_use_percent__main float;
    declare @IQ_cache_pages_in_use_percent__temp float;
    declare @IQ_connections_active integer;
    declare @IQ_operations_waiting integer;
    declare @IQ_operations_active integer;
    declare @IQ_load_table_active integer;
    declare @IQ_max_connections integer;
    call sa_set_http_header('Content-Type', 'text/plain; version=0.0.4; charset=utf-8');
select
datediff(millisecond, '1970-01-01', current utc timestamp)
, max(case when stat_name = 'CpuTotalTime' then stat_value else NULL end)
, max(case when stat_name = 'CpuUserTime' then stat_value else NULL end)
, max(case when stat_name = 'CpuSystemTime' then stat_value else NULL end)
, max(case when stat_name = 'ThreadsFree' then stat_value else NULL end)
, max(case when stat_name = 'ThreadsInUse' then stat_value else NULL end)
, max(case when stat_name = 'MemoryAllocated' then stat_value * 1048576 else NULL end)
, max(case when stat_name = 'MemoryMaxAllocated' then stat_value * 1048576 else NULL end)
, max(case when stat_name = 'MainCacheFinds' then stat_value else NULL end)
, max(case when stat_name = 'MainCacheHits' then stat_value else NULL end)
, max(case when stat_name = 'MainCachePagesDirtyPercentage' then stat_value else NULL end)
, max(case when stat_name = 'MainCachePagesInUsePercentage' then stat_value else NULL end)
, max(case when stat_name = 'TempCacheFinds' then stat_value else NULL end)
, max(case when stat_name = 'TempCacheHits' then stat_value else NULL end)
, max(case when stat_name = 'TempCachePagesDirtyPercentage' then stat_value else NULL end)
, max(case when stat_name = 'TempCachePagesInUsePercentage' then stat_value else NULL end)
, max(case when stat_name = 'ConnectionsActive' then stat_value else NULL end)
, max(case when stat_name = 'OperationsWaiting' then stat_value else NULL end)
, max(case when stat_name = 'OperationsActive' then stat_value else NULL end)
, max(case when stat_name = 'OperationsActiveLoadTableStatements' then stat_value else NULL end)
, property('MaxConnections')
into
@ts,
@iq_cpu_total_seconds_total,
@iq_cpu_user_seconds_total,
@iq_cpu_system_seconds_total,
@iq_threads_free,
@iq_threads_used,
@iq_memory_allocated,
@iq_memory_allocated_max,
@iq_cache_finds_total__main,
@iq_cache_hits_total__main,
@iq_cache_pages_dirty_percent__main,
@iq_cache_pages_in_use_percent__main,
@iq_cache_finds_total__temp,
@iq_cache_hits_total__temp,
@iq_cache_pages_dirty_percent__temp,
@iq_cache_pages_in_use_percent__temp,
@iq_connections_active,
@iq_operations_waiting,
@iq_operations_active,
@iq_load_table_active,
@iq_max_connections
from sp_iqstatistics();
select 
'# HELP iq_cpu_total_seconds_total Total CPU time spent in seconds' || '\n' ||
'# TYPE iq_cpu_total_seconds_total counter' || '\n' ||
'iq_cpu_total_seconds_total ' || @IQ_cpu_total_seconds_total || ' ' || @TS || '\n' ||
'# HELP iq_cpu_system_seconds_total System CPU time spent in seconds' || '\n' ||
'# TYPE iq_cpu_system_seconds_total counter' || '\n' ||
'iq_cpu_system_seconds_total ' || @IQ_cpu_system_seconds_total || ' ' || @TS || '\n' ||
'# HELP iq_cpu_user_seconds_total User CPU time spent in seconds' || '\n' ||
'# TYPE iq_cpu_user_seconds_total counter' || '\n' ||
'iq_cpu_user_seconds_total ' || @IQ_cpu_user_seconds_total || ' ' || @TS || '\n' ||
'# HELP iq_threads_used Number of IQ threads in use' || '\n' ||
'# TYPE iq_threads_used gauge' || '\n' ||
'iq_threads_used ' || @IQ_threads_used || ' ' || @TS || '\n' ||
'# HELP iq_threads_free Number of IQ threads free' || '\n' ||
'# TYPE iq_threads_free gauge' || '\n' ||
'iq_threads_free ' || @IQ_threads_free || ' ' || @TS || '\n' ||
'# HELP iq_memory_allocated Currently allocated memory' || '\n' ||
'# TYPE iq_memory_allocated gauge' || '\n' ||
'iq_memory_allocated ' || @IQ_memory_allocated || ' ' || @TS || '\n' ||
'# HELP iq_memory_allocated_max Maximum amount of allocated memory' || '\n' ||
'# TYPE iq_memory_allocated_max gauge' || '\n' ||
'iq_memory_allocated_max ' || @IQ_memory_allocated_max || ' ' || @TS || '\n' ||
'# HELP iq_cache_finds_total Cache total number of lookup requests' || '\n' ||
'# TYPE iq_cache_finds_total counter' || '\n' ||
'iq_cache_finds_total{cache="temp"} ' || @IQ_cache_finds_total__temp || ' ' || @TS || '\n' ||
'iq_cache_finds_total{cache="main"} ' || @IQ_cache_finds_total__main || ' ' || @TS || '\n' ||
'# HELP iq_cache_hits_total Cache total number of hits' || '\n' ||
'# TYPE iq_cache_hits_total counter' || '\n' ||
'iq_cache_hits_total{cache="temp"} ' || @IQ_cache_hits_total__temp || ' ' || @TS || '\n' ||
'iq_cache_hits_total{cache="main"} ' || @IQ_cache_hits_total__main || ' ' || @TS || '\n' ||
'# HELP iq_cache_pages_in_use_percent Percentage of cache pages in use' || '\n' ||
'# TYPE iq_cache_pages_in_use_percent gauge' || '\n' ||
'iq_cache_pages_in_use_percent{cache="temp"} ' || @IQ_cache_pages_in_use_percent__temp || ' ' || @TS || '\n' ||
'iq_cache_pages_in_use_percent{cache="main"} ' || @IQ_cache_pages_in_use_percent__main || ' ' || @TS || '\n' ||
'# HELP iq_cache_pages_dirty_percent Percentage of cache pages dirtied' || '\n' ||
'# TYPE iq_cache_pages_dirty_percent gauge' || '\n' ||
'iq_cache_pages_dirty_percent{cache="temp"} ' || @IQ_cache_pages_dirty_percent__temp || ' ' || @TS || '\n' ||
'iq_cache_pages_dirty_percent{cache="main"} ' || @IQ_cache_pages_dirty_percent__main || ' ' || @TS || '\n' ||
'# HELP iq_connections_active Number of active connections' || '\n' ||
'# TYPE iq_connections_active gauge' || '\n' ||
'iq_connections_active ' || @IQ_connections_active || ' ' || @TS || '\n' ||
'# HELP iq_operations_waiting Number of operations waiting for IQ resource governor' || '\n' ||
'# TYPE iq_operations_waiting gauge' || '\n' ||
'iq_operations_waiting ' || @IQ_operations_waiting || ' ' || @TS || '\n' ||
'# HELP iq_operations_active Number of active concurrent operations' || '\n' ||
'# TYPE iq_operations_active gauge' || '\n' ||
'iq_operations_active ' || @IQ_operations_active || ' ' || @TS || '\n' ||
'# HELP iq_load_table_active Number of active LOAD TABLE statements' || '\n' ||
'# TYPE iq_load_table_active gauge' || '\n' ||
'iq_load_table_active ' || @IQ_load_table_active || ' ' || @TS || '\n' ||
'# HELP iq_max_connections Max number connections possible' || '\n' ||
'# TYPE iq_max_connections gauge' || '\n' ||
'iq_max_connections ' || @IQ_max_connections || ' ' || @TS || '\n';
END;

(The "# HELP" lines may be omitted, AFAIK)

I also experimented with a node.js based data collector that used IQ as the storage for the metrics. But that might be stuff for a separate blog post.

And then, there is the EarlyWatch Alert also for SAP IQ. See SAP Note 2378876.

[hrrudnytro]

Thanks Christian!

[hrrudnytro111]

Are there any solution how can we send critical parameters to email?

[ChristianK5e]

Grafana can send alerts by e-mail: Grafana: Configure email for Alerting