Db2 DPF 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 with DPF enabled

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
TEST PERIOD – How often should the test be executed Host – The IP address of the DB2 server Port – The port number through which the DB2 server communicates. The default port is 50000. 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. password - Enter the password of the specified USER in the PASSWORD text box. confirm password – Confirm the password by retyping it here. database - Specify the name of the database on the monitored DB2 server to be used by this test

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: Increase the sort heap threshold (SHEAPTHRES), or, 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: SORTHEAP 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.