ICC-Sensors of the Admin-Scout!

In addition to the installed Openadmin tool, the Admin-Scout contains a plug-in from CURSOR Software AG. The plugin can be found in the ICC menu under the CURSOR-Reports menu item.

The installation of the sensors can be found in the "Install Sensors" menu item. The following sensors are available:

The module determines when a sensor is installed and with which version number. It suggests that you install the sensor, make an update, or do nothing. By moving the hooks, you can change the suggested action. If you want to delete a sensor, you must also remove the 'do nothing' checkbox in addition to 'delete'. Pressing the 'Submit' button performs the corresponding marked function for all sensors. You will get an overview of the actions and their results at the end of the page.

ATTENTION! Deleting a sensor also deletes the Table of results and thus all collected data of the sensor! An update is a delete followed by an install. This also deletes all data from a sensor!

For all sensors, the threshold values can be adapted in the ICC in the "Menu point: 'Task-Scheduler-> Scheduler' for the respective sensor.

To avoid confusion with own or Informix sensors and tasks, all sensor and task names and all monitoring tables begin with the abbreviation 'cas_'.

Alerts for DBSPACE Full

The sensor cas_mon_dbspace_full checks the level of all instances of the instance. It generates an alarm if the percendays value of the free memory of a dbspace exceeds the thresholds red or yellow.

Parallel to this sensor there is also a task cas_check_dbspace_full. While the sensor writes the values to the monitoring table once a day, the task checks the thresholds in the 5-minute cycle and generates an alarm when the thresholds are exceeded. This ensures that an overshoot is reported promptly. To ensure that you do not receive emails in the 5-minute rhythm when the thresholds are exceeded, you can use the setting 'alarm repeat time' to determine when the alarm should be repeated on the day. An alarm is repeated if the previous message is more than 24 hours past the set time.

Sensor-configuration (defaults)
Configuration Value
Name of the monitoring tabelle sysadmin:cas_mon_dbspace_full
Related procedure check_dbspace_full
Storage time 93 days
Start time 9:00 Uhr
Frequence 1 x daily
Type DISK
Threshold red 5%
Threshold yellow 10%
use_dbspace_check_params 0
alarm repeat time (only Task) 11:00

A special "threshold" is the dbspace ignore list. There are dbspaces that are usually filled to 100%. This is e.g. A special dbspace of the pys. or log. Logfile. Such a dbspace is always full. In order not to receive any warnings by mail, enter this dbspace into the ignore list. Here you can enter a comma-separated list of DBSpaces for which you do not want any warnings.

If the parameter 'use_dbspace_check_params' is set to 1, the set threshold values are only valid for the DBSpaces, for which no values were specified in the 'DBSpace Check Parameters' menu item. Here you can specify individual threshold values in percent or MegaByte for each dbspace, which are checked.

It is useful to keep the settings for the sensor and the task the same.

Table: cas_mon_dbspace_full
Column Datatype Content
id int Run-id
dbsname varchar(128) Name of the DBSpace
size_kb int Size of the DBSpaces in KiloByte
nfree_kb int Free space of the DBSpaces in KiloByte
perc_free int Free space in percendays overall
alert_color varchar(15) Color of the alarm (green - no mail)

 

Collector for Virtual Processor time

The cas_mon_vp_color sensor collects information about the distribution of CPU resources to the running virtual processors. In contrast to the standard sensors, the difference time to the last run is also noted here. An alarm is not generated when a CPU VP is high in time, but if more than the set number of threads are in the ready queue of the VP. This is an indication that the CPU load of the instance is higher than the resources allocated to it. If you get these alarms, we recommend assigning additional CPU VPs to the instance.

Sensor configuration (defaults)
Configuration Value
Name of monitoring table sysadmin:cas_mon_vp_color
Related procedure check_vp
Period of storage 93 days
Start time 0:01 Uhr
Frequency every 15 minutes
Type CPU
Threshold red 10 threads in the ready-queue
threshold yellow 5 threads in the ready-queue

 

Table of results: cas_mon_vp_color
Column name Datatype Content
id int Runid
vpid int ID of the virt proc
class varchar(15) Clas of virt proc
pid int Processid of virt proc
usecs_user_total decimal(16,2) accumulated value of time recording in the user-mode since start of the instance or onstat -z.
usecs_sys_total decimal(16,2) accumulated value of time recording in system-mode since start of the instance or onstat -z.
usecs_user decimal(8,2) accumulated value of time recording in user-mode since the last run of the related sensor.
usecs_sys decimal(8,2) accumulated value of time recording in system-mode since the last run of the related sensor.
alert_color varchar(15) Color of the generated alarm (green - no e-mail).

 

Collector of number of backuped logs in time

The cas_mon_log_backup sensor counts the log which are not backed-up. Log files and generates an alarm if the number exceeds the red or yellow limit.

Sensor configuration (defaults)
Configuration Value
Name of the monitoring table sysadmin:cas_mon_log_backup
Related procedure check_log
Period of storage 93 days
Start time 0:05 Uhr
Frequency 1 x per hour
Typ BACKUP
Threshold red 20 logfiles
Threshold yellow 10 logfiles

 

Table of results: cas_mon_log_backup
Column name Data type Content
id int Runid
firstlog datetime year to second Timestamp at the beginning of logfile usage.
lastlog datetime year to second Timestamp at time of running this report.
log_count int Number of logfiles filled in this period.
alert_color varchar(15) Color of the generated alarm (green - no e-mail).

 

Collector of number of logs written in interval

The sensor cas_mon_log_count counts the filled log. Log files in a specified time interval and generates an alarm if the number exceeds the red or yellow limit.

Sensor configuration (defaults)
Configuration Value
Name of monitoring table sysadmin:cas_mon_log_count
Related procedure check_log
Period of storage 93 days
Start time 0:04 Uhr
Frequency 1 x per hour
Typ BACKUP
Standard interval 7200 seconds (2 hour)
Threshold red 200 logfiles
Threshold yellow 150 logfiles

 

Table of results: cas_mon_log_count
Column name Datatype Value
id int Runid
firstlog datetime year to second Timestand when the usage of the logfile began.
lastlog datetime year to second the actual timestamp.
log_count int Number of logfiles filled in this period.
alert_color varchar(15) Color of the generated alarm (green - no e-mail).

 

Collector of number of logs in one transaction

The sensor cas_mon_log_trans counts the filled log. logfiles that are within a transaction and generate an alarm when the number exceeds the red or yellow limit.

This alarm is an indication of transactions that are too long in the instance. If this alarm occurs, check how long transactions are made and whether the number and size of the log files is sufficient to obtain the necessary data. If this is not the case, a long transaction abort threatens.

Sensor configuration (defaults)
Configuration Value
Name of the monitoring table sysadmin:cas_mon_log_trans
Related procedure check_log
Period of storage 93 days
Start time 0:02 a.m
Frequency 1 x per hour
Type BACKUP
Threshold red 20 logfiles
Threshold yellow 10 logfiles

 

Table of results: cas_mon_log_trans
Column name Datatype Value
id int Runid
firstlog datetime year to second Timestamp of the first usage of the 1st logfile of the transaction.
lastlog datetime year to second Actual timestamp.
log_count int Number of logfiles filled in this period.
alert_color varchar(15) Color of the generated alarm (green - no e-mail).

 

Collector for table extents

The cas_mon_tabextents sensor checks the number of extents of all tables and generates an alarm if the percentage of available extents for this table falls below a threshold.

This alarm is an indication that in the near future the maximum number of extents for the table will be reached. The table should be reorganized in the near future. In the 12.10 version, this is done simply via the built-in defragmenter (SQL-Admin-API task ('defragment', '<database>: <table>'). There should be enough space in the dbspace where the table is located. Ideal is at least once again the place the table occupies.

Sensor configuration (defaults)
Configuration Value
Name of the monitoring table sysadmin:cas_mon_tabextents
Related procedure check_tabextents
Period of storage 93 days
Start time 6:00 Uhr
Frequency every 6 hour
Type TABLES
Threshold red 5 % free
Threshold yellow 10 % free

 

Table of results: cas_mon_tabextents
Column name Datatype Value
id int Runid
database_name varchar(128) Name of the related database.
tabname varchar(128) Name of the table.
dbspace_name varchar(128) Name of the related DBSpace which contains the table.
extents_act int Number of extents of the table.
extents_max int Max number of extents the table could have.
percentfree decimal Percentage of free extents.
alert_color varchar(15) Color of the generated alarm (green - no e-mail).

 

Collector for performance parameter

The cas_mon_performance sensor collects various performance parameters. It reads from the sysshmvals all parameters starting with pf_. Therefore, the number of parameters within the Informix versions may also vary.

Since not all of these parameters need to be monitored, there are no threshold values for all parameters. Parameters without threshold values do not generate an alarm and can be ignored.

Since the thresholds for these parameters are very much dependent on the size and the workload of the instance, we can not set any really good default values for these parameters. After this sensor has been activated it is necessary to set the thresholds for the alarms according to the values measured in this time after a running time of 1 to 2 weeks.

Sensor configuration (defaults)
Configuration Value
Name of the monitoring table sysadmin:cas_mon_perf
Related procedure check_perf
Period of storage 93 days
Start time 1:00 Uhr
Frequency every 10 minutes
Type PERFORMANCE
pf_buffwts (buffer waits) threshold red 20000
pf_buffwts (buffer waits) threshold yellow 10000
pf_deadlks (deadlocks) threshold red 20
pf_deadlks (deadlocks) threshold yellow 10
pf_dskreads (disk reads) threshold red 2000000
pf_dskreads (disk reads) threshold yellow 1000000
pf_dskwrites (disk writes) threshold red 2000000
pf_dskwrites (disk writes) threshold yellow 1000000
pf_fgwrites (forground writes) threshold red 2000
pf_fgwrites (forground writes) threshold yellow 1000
pf_latchwts (latch waits) threshold red 20000
pf_latchwts (latch waits) threshold yellow 10000
pf_lockwts (lock waits) threshold red 20000
pf_lockwts (lock waits) threshold yellow 10000
pf_lockreqs (lock requests) threshold red 200000
pf_lockreqs (lock requests) threshold yellow 100000
pf_lruwrites (lru waits) threshold red 200000
pf_lruwrites (lru waits) threshold yellow 100000
pf_ovlock (over locks) threshold red 20
pf_ovlock (over locks) threshold yellow 10
pf_ovtrans (over transactions) threshold red 20
pf_ovtrans (over transactions) threshold yellow 10
pf_ovuser (over users) threshold red 20
pf_ovuser (over users) threshold yellow 10
pf_seqscans (sequential scans) threshold red 200000
pf_seqscans (sequential scans) threshold yellow 100000

 

Table of results: cas_mon_perf
Column name Datatype Value
id int Runid
name varchar(15) Name of the parameter
total_value int8 Accumulated value since start of the instance or onstat -z.
value int8 Value sind the last run of the sensor.
alert_color varchar(15) Color of the generated alarm (green - no e-mail).

 

Checks wait time of user threads

The sensor cas_mon_wait checks the number of waits for a reason in the system table systwaits. The cumulated waiting time against the yellow or red threshold value is checked for each reason. If the waiting time is above one of the threshold values, an alarm is generated.

In normal cases, there are no values in this table. Only if there are longer waiting times due to high system load are values displayed here.

Sensor configuration (defaults)
Configuration
Name of the monitoring table sysadmin:cas_mon_wait
Related procedure check_wait
Period of storage 93 days
Start time 1:00 Uhr
Frequency every 5 minutes
Type PERFORMANCE
Threshold red 50
Threshold yellow 1

 

Table of results: cas_mon_wait
Column name Datatype Value
id int Runid
reason varchar(15) Reason why the thread needed to wait.
num int Number of waiting threads.
cumtime decimal(16,2) Accumulated wait time.
maxtime int Max wait time.
alert_color varchar(15) Color of the generated alarm (green - no e-mail).

 

Collector for table growth history

The sensor cas_mon_table_growth collects information about the growth of tables. For each table, a set with the number rows and the difference to the previous run is created. This allows you to quickly find fast growing tables.

This sensor does not generate any alarms and has no thresholds.

Sensor configuration (defaults)
Configuration
Name of the monitoring table sysadmin:cas_mon_table_growth
Related procedure check_table_growth
Period of storage 93 days
Start time 0:05 Uhr
Frequency einmal täglich
Type TABLE

 

Table of results: cas_mon_table_growth
Column name Datatype Value
id int Runid
database_name varchar(128) Database of the table.
table_name varchar(128) Name of the table.
date_of_record date Day of recording the measured value.
nrows bigint Number of rows of the table.
nrows_diff bigint Growth in rows since last runtime.
pct_diff smallint Percentage of growth.

 

Collector for table data

The sensor cas_mon_tabinfo checks the number of pages occupied by a table. The number of pages of a fragment is limited to 16.7 millons. If a table reaches this value, an insert is no longer possible. This is the only way to create a new fragment (only allowed in the Enterprise / Advanced Enterprise Edition) or to convert such a table into a dbspace with larger pagesize.

There are 3 limit values for this sensor. The 1st limit is the number of extents of a table that it must have reached at least to be tested. If you have large tables created with large first and next extent sizes, leave this value set to 0 (all tables). If they are sure that the tables are not affected with a few extents, they can make this value larger.

The other two thresholds are the thresholds for the red or yellow alarm. The upper limit pages per fragment are 16.7 million. The default values leave them enough time to find a solution for normal table growth.

Sensor configuration (defaults)
Configuration
Name of the monitoring table sysadmin:cas_mon_tabinfo
Related procedure check_tabinfo
Period of storage 93 days
Start time 0:09 Uhr
Frequency einmal am Tag
Type TABLES
Threshold red 15.500.000
Threshold yellow 14.000.000

 

Table of results: cas_mon_tabinfo
Column name Datatype Value
id int Runid
dbname char(128) Name of the database
tablename char(128) Name of the table
dbspace char(128) Name of the DBSpace which contains the fragment.
part_flags int Partitions-Flags of the fragment
part_partnum int Partition number of the fragment
part_lockid int Lockid of the fragments. If the partition number == der Lockid, then it is the 1st fragment of the table. If the partition number > lockid, then it is an index. If the partition number < Lockid, then it is an addition fragment.
rowsize int Row length of the table in byte
nrows int8 Number of rows in the table
pagesize_k smallint Pagesize of the fragment
num_extends smallint Number of allocated extents
used_pg int8 Number of used pages
allocated_pg int8 Number of in extents allocated pages. The difference to used_pg is the free space within the allocated extents.
first_extend_size int First-Extent-Size of the table / index
next_extend_size int Next-Extent-Size of the table / index
alert_color char(6) Color of the generated alarm (green - no e-mail).
 

Collector for sessions with many locks

This sensor, cas_mon_locks, checks the sample-by-probe whether there are sessions at the time the sensor is running, whose number of block locks is above the threshold value. If such sessions are found, they are entered into the monitoring table. An alarm is not generated.

Sensor configuration (defaults)
Configuration
Name of the monitoring table sysadmin:cas_mon_locks
Related procedure check_locks
Period of storage 93 days
Start time 1:10 Uhr
Frequency every 5 minutes
Type USER
Threshold 10

 

Table of results: cas_mon_locks
Column name Datatype Value
id int Runid
username char(128) Name of the users of the session.
sid int Session ID
hostname char(128) Name of the hosts connected.
nlocks int8 Number of locks of the session
part_partnum int Partitionsnummer of the fragment
part_lockid int Lockid of the fragment. If partition number == Lockid, then it is the 1st fragment of the table. If partition number > Lockid, then it is an index. If partition number < Lockid, then it is an additional fragment.
nlocks int8 Number of locks held in the moment of running the report.
locksheld int8 Number of locks held in the moment of running the report (different check).
isreads int8 Number of reads of the session.
iswrites int8 Number of writes of the session.
longtx int8 Number of long transactions of the session.
seqscans int8 Number of sequential scans of the session. (on table with less number of rows it is normal).
pagereads int8 Number of reads of the session (disk).
pagewrites int8 Number of writes of the session (disk).
dsksorts int8 Number of sorts of the session (disk).
 

slow SQL-Statements

This sensor, cas_mon_slow_stmt, checks the sample-by-case if there are SQL statements at the time of the run whose runtime is above the threshold value. If such statements are found, they are entered into the monitoring table. An alarm is not generated.

Sensor configuration (defaults)
Configuration
Name of the monitoring table sysadmin:cas_mon_slow_stmt
Related procedure check_slow_stmt
Period of storage 93 days
Start time 1:10 Uhr
Frequency every 2 minutes
Type PERFORMANCE
Schwellwert 2 Sekunden

 

Table of results: cas_mon_slow_stmt
Column name Datatype Value
id int Runid
username char(128) Name of the user running the session.
sid int Die Session ID
pid int Prozess-ID of the client (if known).
hostname char(128) Name of the host where the session is connected.
tty char(16) Terminal of the client (if known)
connected datetime year to second Time the connection has established.
feprogram char(256) Name of the frontend program.
cumtime float Accumulated time used for running sql statements.
executions int Number of running sql statement.
bufreads int Number of read operations of the session (cache).
pagreads int Number of read operations of the session (disk).
bufwrites int Number of write operations of the session (cache).
pagewrites int Number of write operations of the session (disk).
totsorts int Number of sorts in total.
dsksorts int Number of sort operations on disk.
estcosts int Calculated optimizer costs.
estrows int Calculated optimizer rows returned.
seqscans int Number of squential scans of the session. On tables with less number of rows it is normal.
statement lvarchar(2048) Statement-text limited on 2048 Byte.
 

back