IBM DB2 for i Monitoring


IBM DB2 for i - An Overview

DB2 for i is a member of IBM’s family of DB2 databases. It is an integrated relation database management system for the IBM i platform. DB2 for i contains a rich set of security features and services that pertain to the goals of authentication, authorization, integrity, confidentiality, and auditing.

Monitoring IBM DB2 for i - What we do.

Applications Manager aims to help database admins with tuning their DB2 and gather operating system level data to understand performance issues. Let’s take a look at what you need to see to monitor IBM DB2 for i, the performance metrics to gather and how you can ensure that your DB2 database is up and operating as expected with Applications Manager:

  • Monitor Jobs status and health and also optimize your server's resources by minimizing network traffic, disk I/O and CPU time. 
  • Track memory usage details and keep track of JVM metrics to correlate server performance.
  • Get instant notifications when there are performance issues with the components of your DB2.
  • Become aware of performance bottlenecks and take quick remedial actions before your end users experience issues.

Adding a new IBM DB2 for i monitor

Prerequisites for monitoring IBM DB2 for i:
  • To monitor IBM DB2 Version 8 and 9, the minimum user privileges required is the ability to access the SYSPROC procedures.
  • To monitor IBM DB2 Version 10, the minimum user privileges required is the ability to access the SYSIBMADM functions.

To create a IBM DB2 for i database server Monitor, follow the given steps:

  1. Click on New Monitor link.
  2. Select DB2 for i Server.
  3. Enter a Display Name for your monitor.
  4. Enter the IP Address or hostname of the host.
  5. Enter the Subnet Mask of the network.
  6. Enter the port number in which DB2 is running.
  7. Enter the polling interval time in minutes.
  8. If you are adding a new monitor from an Admin Server, select a Managed Server.
  9. Provide the user name and password of user who has permission to access the DB2 database. The user name specified for collecting the data from DB2 Server should have either System Administrator role or the user should be the DB owner for master database.
  10. Specify the Database Name.
  11. Choose the Monitor Group from the combo box with which you want to associate the DB2 database server Monitor (optional). You can choose multiple groups to associate your monitor.
  12. Click Add Monitor(s). This discovers DB2 for i database server from the network and starts monitoring them.

Monitored Parameters

Overview

ATTRIBUTE NAME DESCRIPTION UNITS
CPU DETAILS
Average CPU Utilization The average CPU utilization for all the active processors. %
Average CPU Rate The average CPU rate expressed as a percentage where 100% indicates the processor is running at its nominal frequency. A value above or below 100% indicates how much the processor has been slowed down (throttled) or speeded up (turbo) relative to the nominal frequency for the processor model. For instance, a value of 120% indicates the processor is running 20% faster against its nominal speed. %
Configured CPUs The total number of configured CPUs. -
Current CPU Capacity The current processing capacity specifies the processor units that are being used in the partition. For a partition sharing physical processors, the current processing capacity represents the share of the physical processors in the pool it is running. For a partition using dedicated processors, the current processing capacity represents the number of virtual processors that are currently active in the partition. GHz
SCHEMA DETAILS FOR TOP 10 SCHEMA BY SIZE
Schema Schema Name -
Table Count The number of tables in the schema -
Size The size of the Schema MB
Partition Count The number of Partitions present in the Schema -
Pages The number of 64K pages in all partitions or members of the table. -
>Distinct Indexes The number of distinct indexes built over any partitions or members of the table. This does not include maintained temporary indexes. -
>Insert Operations The number of insert operations of all partitions or members of the table since the last IPL(Initial Program Load). -
Update Operations The number of update operations of all partitions or members of the table since the last IPL. -
Delete Operations The number of delete operations of all partitions or members of the table since the last IPL. -
Logical Reads The number of logical read operations of all partitions or members of the table since the last IPL. -

System Information

ATTRIBUTE NAME DESCRIPTION UNITS
SYSTEM DETAILS
Main Storage Size The amount of main storage in the system. GB
System ASP Storage The amount of ASP storage in the system. GB
System ASP Used The amount of ASP Memory used by the System MB
Partitions Count The number of Partitions in the System -
Active Threads in System The number of Active Threads in the System -
MEMORY POOL INFORMATION
Pool Name

The name of the storage pool. The name may be a number, in which case it is a private pool associated with a subsystem, or one of the following special values.

• MACHINE - The specified pool definition is defined to be the machine pool.
• BASE - The specified pool definition is defined to be the base system pool, which can be shared with other subsystems.
• INTERACT - The specified pool definition is defined to be the shared pool used for the QINTER subsystem.
• SPOOL - The specified pool definition is defined to be the shared pool used for spooled writers.
• SHRPOOLx - The specified pool definition is defined to be a shared pool.

 
Reserved Size The amount of storage in the pool reserved for system use (for example, for save/restore operations). GB
Current Size The amount of main storage in the pool. GB
Maximum Active Threads The maximum number of threads that can be active in the pool at any one time. -
Current Threads The number of threads currently using the pool. -
Elapsed Time The time since the measurement start time. Sec

Jobs and JVM Information

TOP 10 GARBAGE COLLECTIONS AND HEAP TIME USING JVM INFORMATION

ATTRIBUTE NAME DESCRIPTION UNITS
JOB DETAILS
Active Jobs in System The number of jobs active in the system (jobs that have been started, but have not yet ended), including both user and system jobs -
Interactive Jobs in System The percentage of interactive performance assigned to this logical partition. This value is a percentage of the total interactive performance available to the entire physical system. %
Total Jobs in System The total number of user and system jobs that are currently in the system. The total includes:
• All jobs on job queues waiting to be processed.
• All jobs currently active (being processed).
• All jobs that have completed running but still have output on output queues to be produced.
 
Maximum Jobs in System The maximum number of jobs that are allowed on the system. When the number of jobs reaches this maximum, you can no longer submit or start more jobs on the system. The total includes:
• All jobs on job queues waiting to be processed.
• All jobs currently active (being processed).
• All jobs that have completed running but still have output on output queues to be produced.
 
Job Name The name of Job that is running in DB2 Database -
Process ID The process identifier used by the kernel to uniquely identify the process. -
Total GC Time The total time spent performing garbage collection tasks. Min
Current Java Threads The current number of java threads within the JVM job. -
Current Heap Size The amount of memory currently allocated for heap space. MB
In Use Heap Size The amount of memory  currently in use by the heap. MB
Max Heap Size The maximum heap size available to the JVM code, MB

Configuration

ATTRIBUTE NAME DESCRIPTION
Aliases Alias is an alternative name for database objects.
Sequences The number of Sequences
Global Variables The number of Global Variables
Journals The number of Journals
Journal Receivers The number of Journal Receivers
Schemas The number of Schemas
Tables The number of Tables
Partitioned Tables The number of Partitioned Tables
Distributed Tables The number of Distributed Tables
Materialized Tables The number of Materialized Tables
Physical Files The number of Physical Files
Source Files The number of Source Files
Logical Files The number of Logical Files
DDM Files The number of DDM Files
External Triggers The number of External Triggers
SQL Procedures The number of SQL Procedures
External Procedures The number of External Procedures
SQL Procedures The number of SQL Procedures