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
Parameter	Description
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 is authorized to access the target database server and collect the required metrics in this text box. You can create a separate user on the OS hosting the DB2 server for this purpose. The steps for the same are detailed in the Creating a Special User for Monitoring DB2 Server topic.
Password	Enter the password of the specified USER in the PASSWORD text box.
Confirm Password	Confirm the Password by retyping it in the Confirm Password text box.
Database	Specify the name of the database on the monitored DB2 server to be used by this test.
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.
Trust Store File Name	This parameter is applicable only if the target DB2 UDB database is SSL-enabled, if not, set this parameter to none. Specify the file name of the client-side SSL truststore that contains the server certificate required for establishing an SSL connection. The truststore is used to verify the identity of the server and enable a secure communication channel. By default, the truststore file should be placed in:<EG_INSTALL_DIR>/jre/lib/security/mytruststore.jks Here, mytruststore.jks is the Truststore file name. You may change this to any valid file name. By default, none is specified against this text box.
Trust Store Password	This parameter is applicable only if the target DB2 UDB database is SSL-enabled, if not, set this parameter to none. If a Truststore File name is provided, then, in this text box, provide the password that is used to obtain the associated certificate details from the Truststore File. By default, this parameter is set to none.
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: 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.