Monitoring Db2 on i

Supported versions

Supported IBM i platforms are: 7.2, 7.3, 7.4.

Note: Currently, we are supporting only remote monitoring of Db2 on i.

Configuration

The monitoring of Db2 on i will be automatically started once the IBM i Series configuration is in place and Collections Services is started. Please follow the IBM i Series configuration instructions.

The Db2 on i will be presented as a separate entity within the IBM i Series dashboard.

Collection Services configuring and startup

Collection Services Configuration

In order to configure Collection Services, Configure Performance Collection (CFGPFRCOL) command can be used. Configure Performance Collection command changes certain collection attributes and determines how Collection Services will manage data collection. The following example shows how CFGPFRCOL command can be used in order to configure Collection Services to collect data relevant for Instana monitoring:

CFGPFRCOL 
    INTERVAL(00.25)
    LIB(QPFRDATA)
    DFTCOLPRF(*STANDARD) 
    CYCTIME(000000)
    CYCITV(01)
    RETPERIOD(00001 *HOURS)
    ENBSYSMON(*YES)
    CRTDBF(*YES)
    CRTPFRSUM(*NONE)
    SYSMONCGY(*SYSMONDFT)

Parameters

Parameter Name Description Value
INTERVAL Default polling interval 15 seconds
LIB Collection library name QPFRDATA
DFTCOLPRF Default collection profile STANDARD
CYCTIME Begin collecting data in a new management collection object (*MGTCOL object) at 00:00
CYCITV Cycle collecting data in a new management collection object (*MGTCOL object) 24 hours
RETPERIOD Management collection object (*MGTCOL object) retention period 1 hour
ENBSYSMON If Collection Services provide support for system monitoring YES
CRTDBF Submit the Create Performance Data (CRTPFRDTA) job YES
CRTPFRSUM Create additional summary type data for the standard database collection NONE
SYSMONCGY System monitoring category SYSMONDFT

More detailed information can be found here.

Collection Services startup

In order to configure Collection Services, Start Performance Collection (STRPFRCOL) command can be used in the following way:

STRPFRCOL 
    COLPRF(*CFG)
    CYCCOL(*YES)

Parameters

Parameter Name Description Value
COLPRF Collection profile name (*STANDARDP) CFG
CYCCOL Cycle Collection Services data collection YES

More detailed information can be found here.

Metrics collection

Configuration data

  • Collection Services
  • Collection Services Library name

Performance metrics

Metric Description Granularity
Active queries Total number of queries currently active. 15 seconds
Logical database reads Total number of times the database module was called by all jobs within the interval. 15 seconds
Logical database writes Total number of times the internal database write function was called by all jobs within the interval. 15 seconds
Asynchronous database reads TTotal number of asynchronous physical database read operations for database functions for all jobs within the interval. 15 seconds
Asynchronous database writes Total number of asynchronous physical database write operations for database functions for all jobs within the interval. 15 seconds
Synchronous database reads Total number of physical synchronous database read operations for database functions for all jobs within the interval. 15 seconds
Synchronous database writes Total number of physical synchronous database write operations for database functions for all jobs within the interval. 15 seconds
Database commit operations Total number of database commit operations performed by all jobs within the interval. 15 seconds
Database rollback operations Total number of database rollback operations performed by all jobs within the interval. 15 seconds
Miscellaneous logical database operations The number of miscellaneous logical database operations (updates, deletes, force-end-of-data, opens, closes, and releases) performed by all jobs within the interval. 15 seconds
Plans in SQL plan cache Total number of plans currently in the SQL plan cache. 15 seconds
Plan cache size The current size (in megabytes) of the SQL plan cache. 15 seconds
Plan cache size limit The maximum size (in megabytes) that the SQL plan cache is allowed to be. 15 seconds
SQL cursor count Total cumulative number of SQL cursors that were full opened by all jobs within the interval. 15 seconds
SQL cursor reuse Total cumulative number of SQL cursors that were pseudo-opened by all jobs within the interval. 15 seconds