DB2

Configuration

NeoLoad monitors the DB2 server using the JDBC protocol. To monitor a DB2 server, it is necessary to place the driver and license jars in the NeoLoad lib/jdbcDrivers folder. These files can be found in the DB2 Server installation at the following location:

• <DB2 Server>/java/db2jcc.jar
• <DB2 Server>/java/db2jcc4.jar

  or in previous versions:

• <DB2 server>/SQLLIB/java/db2jcc.jar
• <DB2 server>/SQLLIB/java/db2jcc_license_xx.jar

Supported versions

NeoLoad supports most common DB2 Database server versions from 8.1 upwards.

• Warning: NeoLoad does not support the DB2 database versions for AS400.

Connection settings

The DB2 monitor allows monitoring a DB2 database server. The counters are sorted by category: connections, catalog cache, IO requests, command rates, tables memory, pool, joins, and so on.

This monitor executes SQL requests on the database to obtain status data and system variables. To carry out the monitoring, NeoLoad requires an account with authorization to connect to the database and to read and collect global status data and variables. The account must belong to the DB2ADMNS system group (on Windows) and sysmon_group (on Unix and Linux).

For NeoLoad to collect the indicators for the most resource-hungry requests (see Top Statements), the monitoring switches on the database server must be activated. These are the commands required to activate the switches:

• All indicators: this switch allows NeoLoad to retrieve the list of SQL requests.

  db2 update dbm cfg using dft_mon_stmt on

• CPU time indicator: this switch allows NeoLoad to retrieve the CPU time (in milliseconds) for each SQL request.

  db2 update dbm cfg using dft_mon_uow on

• Sort time indicator: this indicator allows NeoLoad to retrieve the sort times for each SQL request.

  db2 update dbm cfg using dft_mon_sort on

The database server must be restarted for these changes to take effect.

NeoLoad monitors the DB2 server using the JDBC protocol:

• Classic edit mode: The JDBC driver is included in the NeoLoad software package. The default connection port set by NeoLoad is 50000, the standard JDBC port.
• Advanced edit mode: A specific JDBC connection URL provides extra connection settings to the JDBC driver, such as timeout, cluster handling, and so on.

  Example of a DB2 JDBC URL: jdbc:db2://myserver:50000/mydatabase;traceLevel=(com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL);

Create a DB2 monitor

NeoLoad makes it possible to create a new monitor either using the monitored machine creation wizard, as described in Create and configure a monitored machine, or from an existing monitored machine, as described in Create and configure a monitor.

The Additional properties area makes it possible to set the number of SQL requests displayed in NeoLoad which use database resources the most—for all the indicators of the Top SQL Statements category.

Available counters

• Connections. 
  • Remote connections. The current number of connections initiated from remote clients to the instance of the database manager that is being monitored.
  • Connections executing requests. The number of remote applications that are currently connected to a database and are currently processing a unit of work within the database manager instance being monitored.

    Warning: This monitor is not available in versions 9.x and higher.

  • Idle agents. The number of Agents in the Agent pool that are currently unassigned to an application and therefore idle.
    This element cannot be used to help set the num_poolagents configuration setting. Having idle Agents available to service requests for Agents can improve performance.
• Catalog Cache. 
  • Catalog cache lookups. The number of times that the catalog cache was referenced to obtain table descriptor information or authorization information.

    This element includes both successful and unsuccessful accesses to the catalog cache. The catalog cache is referenced whenever:

    • a table, view, or alias name is processed during the compilation of an SQL statement.
    • database authorization information is accessed.
    • a routine is processed during the compilation of an SQL statement.
  • Catalog cache inserts. The number of times that the system tried to insert table descriptor or authorization information into the catalog cache.
  • Catalog cache overflows. The number of times that the catalog cache overflowed the bounds of its allocated memory.
    If cat_cache_overflows is high, the catalog cache may be too small for the workload. Enlarging the catalog cache may improve its performance.
• IO Requests. 
  • Direct reads. The number of read operations that do not use the buffer pool.
  • Direct writes. The number of write operations that do not use the buffer pool.
  • Direct read time. The elapsed time (in milliseconds) required to perform the direct reads.
    A high average time may indicate an I/O conflict.
  • Direct write time. The elapsed time (in milliseconds) required to perform the direct writes.
    A high average time may indicate an I/O conflict.
• Command Rates. 
  • Select statements. The number of SQL SELECT statements that were executed. This element can be used to determine the level of database activity.
  • Commit statements. The total number of SQL COMMIT statements that have been attempted.
    A small rate of change in this counter during the Monitor period may indicate that applications are not doing frequent commits, which may lead to problems with logging and data concurrency.
  • Rollback statements. The total number of SQL ROLLBACK statements that have been attempted.
    It is advised to minimize the number of rollbacks, since higher rollback activity results in lower throughput for the database.
  • Update/insert/delete statements. The number of SQL UPDATE, INSERT, and DELETE statements that were executed. This element can be used to determine the level of database activity.
  • Overflow accesses. The number of accesses (reads and writes) to overflowed rows of tables.
    Overflowed rows indicate that data fragmentation has occurred. If this number is high, improving the database performance may be done by reorganizing the database.

    Warning: This Monitor is not available in versions 9.x and higher.

• Tables. 
  • Rows read. This is the number of rows read from the tables.
  • Rows inserted. This is the number of row insertions attempted. This element can be used to gain insight into the current level of activity within the database.
  • Rows deleted. This is the number of row deletions attempted. This element can be used to gain insight into the current level of activity within the database.
  • Rows updated. This is the number of row updates attempted. This element can be used to gain insight into the current level of activity within the database.
• Memory. 
  • Committed private memory. The amount of private memory that the instance of the database manager has currently committed.
    This element can be used to help set the min_priv_mem configuration parameter to ensure enough private memory is available.
  • Total log space used. The total amount of active log space (in bytes) currently used in the database.

    This element must be used in conjunction with Log space available to determine whether to adjust the following configuration parameters to avoid running out of log space:

    • logfilsiz
    • logprimary
    • logsecond
  • Log space available. The amount of active log space in the database that is not being used by uncommitted transactions.
  • Log pages read. The number of log pages read from disk by the logger.
    This element can be used with an operating system Monitor to quantify the amount of I/O on a device that is attributable to database activity.
  • Log pages write. The number of log pages written to disk by the logger.
    This element can be used with an operating system Monitor to quantify the amount of I/O on a device that is attributable to database activity.
  • Secondary logs allocated. The total number of secondary log files that are currently being used for the database.
    If this value is consistently high, it may be necessary to have larger log files, or more primary log files, or more frequent COMMIT statements within the application.
• Pool. 
  • Pool data logical reads. Indicates the number of data pages which have been requested from the buffer pool (logical) for regular and large table spaces.
  • % Pool data logical reads. Percentage of data pages which have been requested from the buffer pool (logical) for regular and large table spaces compared to data pages read in from the table space Containers (physical).
  • Pool data physical reads. Indicates the number of data pages read in from the table space Containers (physical) for regular and large table spaces.
  • Pool index logical reads. Indicates the number of index pages which have been requested from the buffer pool (logical) for regular and large table spaces.
  • % Pool index logical reads. Percentage of index pages which have been requested from the buffer pool (logical) for regular and large table spaces compared to index pages read in from the table space Containers (physical).
  • Pool index physical reads. Indicates the number of index pages read in from the table space Containers (physical) for regular and large table spaces.
  • Pool index writes. Indicates the number of times a buffer pool index page was physically written to disk.
  • Pool read time. Indicates the total amount of time spent reading in data and index pages from the table space Containers (physical) for all types of table spaces. This value is given in microseconds.
  • Pool write time. Provides the total amount of time spent physically writing data or index pages from the buffer pool to disk. Elapsed time is given in microseconds.
• Hash Joins. 
  • Total hash joins. The total number of hash joins executed.
    Use this value in conjunction with hash_join_overflows to determine if a significant percentage of hash joins would benefit from modest increases in the sort heap size.
  • Hash joins overflows. The number of times that hash join data exceeded the available sort heap space.
  • % Hash joins overflows. Percentage of hash join data exceeded the available sort heap space.
• Miscellaneous. 
  • Locks waiting. Indicates the number of Agents waiting on a lock.
    If this number is high, the applications may have concurrency problems; it is advised to identify applications that are holding locks or exclusive locks for long periods of time.
  • Locks held. The number of locks currently held. This is the total number of locks currently held by all applications in the database.
  • Deadlocks. The total number of deadlocks that have occurred.

    This element can indicate that applications are experiencing contention problems. These problems could be caused by the following situations:

    • Lock escalations are occurring for the database.
    • An application may be locking tables explicitly when system-generated row locks may be sufficient.
    • An application may be using an inappropriate isolation level when binding.
    • Catalog tables are locked for repeatable read.
    • Applications are getting the same locks in different orders, resulting in deadlock.

    Resolving the problem can be done by determining in which applications (or application processes) the deadlocks are occurring. Modifying the application may be available to better enable it to execute concurrently.

  • Active sorts. The number of sorts in the database that currently have a sort heap allocated.
  • Total sorts. The total number of sorts that have been executed. This value includes heaps for sorts of temporary tables that were created during relational operations.
  • Sorts overflows. The total number of sorts that ran out of sort heap and may have required disk space for temporary storage.
    When a sort overflows, additional overhead will be incurred because the sort will require a merge phase and can potentially require more I/O, if data needs to be written to disk.
  • % Sorts overflows. Percentage of sorts that ran out of sort heap and may have required disk space for temporary storage.
    When a sort overflows, additional overhead will be incurred because the sort will require a merge phase and can potentially require more I/O, if data needs to be written to disk.
  • Version. DB2 database server version.
• Top Statements. 
  • CPU. The requests having consumed the most processor time.
  • Rows read. The requests having read the most rows from the database.
  • Rows written. The requests having written the most rows to the database.
  • Sort time. The requests with the longest sort times.
  • Sort overflows. The requests with the highest number of sort overflows.

Home