Db2 Database Manager Test

The database manager includes the database engine and the facilities to access data, such as the command line processor and the application interfaces. This test reports key statistics pertaining to the health of the DB2 database manager.

Target of the test : A DB2 database server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every DB2 database server being monitored

Configurable parameters for the test
  1. TEST PERIOD – How often should the test be executed
  2. Host – The IP address of the DB2 server
  3. Port – The port number through which the DB2 server communicates. The default port is 50000.
  4. user - Specify the name of the user who has any of the following privileges to the specified DATABASE: SYSADM or SYSCTRL or SYSMAINT or SYSMON. You can create a separate user on the OS hosting the DB2 server for this purpose, and assign any of the aforesaid privileges to that user. The steps for the same are detailed in the Creating a Special User for Monitoring DB2.
  5. password - Enter the password of the specified USER in the PASSWORD text box.
  6. confirm password – Confirm the password by retyping it here.
  7. database - Specify the name of the database on the monitored DB2 server to be used by this test
  8. SSL- If the target database server is SSL-enabled, then set the SSL flag to Yes. If not, then set the SSL flag to No.
  9. Trust Store file name- The trust store file contains certificates from trusted Certificate Authorities (CAs). These certificates are used by eG agent to verify the authenticity of servers hosting DB2 UDB and establish a secure connection with the server using SSL. Specify the filename for Trust store file in Trust Store file name text box.
  10. Trust Store Password- The trust store password is the passphrase or key used to encrypt and decrypt the trust store file. This password is required by the eG Agent when it needs to access the trust store file to establish secure connections. Specify the password in Trust Store password text box.
  11. Confirm Password- Confirm the Password by retyping it in the Confirm Password text box.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Sorts post threshold:

Indicates the number of sorts per second that have requested heaps after the sort heap threshold has been exceeded.

Sorts/Sec

Under normal conditions, the database manager will allocate sort heap using the value specified by the SORTHEAP configuration parameter. If the amount of memory allocated to sort heaps exceeds the sort heap threshold (SHEAPTHRES configuration parameter), the database manager will allocate sort heap using a value less than that specified by the SORTHEAP configuration parameter. Each active sort on the system allocates memory, which may result in sorting taking up too much of the system memory available. Sorts that start after the sort heap threshold has been reached may not receive an optimum amount of memory to execute, but, as a result, the entire system may benefit. By modifying the sort heap threshold and sort heap size configuration parameters, sort operation performance and overall system performance can be improved. If this element’s value is high, you can:

  1. Increase the sort heap threshold (SHEAPTHRES), or,
  2. Adjust applications to use fewer or smaller sorts via SQL query changes.

 

Piped Sorts Requested:

A sort is classified as piped sort if the sorted information can return directly without requiring a temporary table to store a final, sorted list of data. This measure reports the number of piped sorts that have been requested per second..

Sorts/Sec

Piped sorts may reduce disk I/O. Allowing more piped sorts therefore, can improve the performance of sort operations and possibly the performance of the overall system.

Piped Sorts Rejected:

Indicates the percentage of piped sort requests that have been rejected.

Percent

When the number of rejected piped sorts are high, you can improve sort performance by adjusting one or both of the following configuration parameters:

  1. SORTHEAP
  2. SHEAPTHRES

If piped sorts are being rejected, you might consider decreasing your sort heap or increasing your sort heap threshold. You should be aware of the possible implications of either of these options. If you increase the sort heap threshold, then there is the possibility that more memory will remain allocated for sorting. This could cause the paging of memory to disk. If you decrease the sort heap, you might require an extra merge phase that could slow down the sort.

Hash Join Post Threshold:

Indicates the total number of times that a hash join heap request was limited due to concurrent use of shared or private sort heap space.

Hits/Sec

If this value is large, the sort heap threshold should be increased.