Documentation forDatabase Performance Analyzer

Register a Db2 database instance

Complete the following steps to register an individual Db2 database instance for monitoring with DPA.

You can also use mass registration to registrations to register multiple database instances, or you can register database instances using scripts that call the DPA API.

If you register a database instance within the 14-day trial period, DPA begins monitoring the instance immediately. After the trial period, you must activate a license to monitor the database instance.

Create the DPA monitoring user

The DPA monitoring user enables DPA to monitor the database instance. The monitoring user must have the required permissions.

  1. Create a user account and grant the following permissions:

    • SYSMON authority and connect privileges (required to monitor the Db2 instance)

    • DBADM authority (allows DPA to obtain access plans, and also allows DPA to explain the data access path for each specific SQL statement collected)

  2. Run the following commands to grant that user EXECUTE privileges on the required functions:

    grant execute on function SYSPROC.MON_GET_DATABASE to userName;
    grant execute on function SYSPROC.MON_SAMPLE_WORKLOAD_METRICS to userName;
    grant execute on function SYSPROC.MON_GET_ACTIVITY to userName;
    grant execute on function SYSPROC.MON_GET_BUFFERPOOL to userName;
    grant execute on function SYSPROC.MON_GET_TABLESPACE to userName;
    grant execute on function SYSPROC.MON_GET_TRANSACTION_LOG to userName;
    grant execute on function SYSPROC.MON_GET_APPL_LOCKWAIT to userName;
    grant execute on function SYSPROC.MON_GET_LOCKS to userName;
    grant execute on function SYSPROC.MON_FORMAT_LOCK_NAME to userName;
    grant execute on function SYSPROC.MON_GET_UNIT_OF_WORK to userName;
    grant execute on function SYSPROC.MON_GET_AGENT to userName;
    grant execute on function SYSPROC.MON_GET_PKG_CACHE_STMT to userName;
    grant execute on function SYSPROC.MON_GET_CONNECTION to userName;
  3. To verify that the permissions were applied correctly, run the following command:

    select substr(authid,1,20) as authid
        , authidtype
        , privilege
        , grantable
        , substr(objectschema,1,12) as objectschema
        , substr(objectname,1,30) as objectname
        , objecttype
    from sysibmadm.privileges
    where objectschema ='SYSPROC' AND AUTHID='userName';

Run the registration wizard

  1. On the DPA home page, click Register DB Instance for Monitoring.
  2. Under Self-managed, click IBM Db2. Then click Next.
  3. Confirm the Db2 configuration settings.

    DPA requires the Db2 instance-wide parameter {DFT_MON_STMT} to be turned on to collect monitoring data. Follow the on-screen instructions to check and set the parameter.

    If {DFT_MON_STMT} is set to OFF, you can still use DPA to register the database instance. Later, you can set it to ON and restart the database instance during an approved maintenance window. In the meantime, the database shows a status of Idle.

  4. Enter the following connection information:
    1. Enter the host name or IP address and port of the Db2 server.

    2. Specify a database that DPA can use to register the instance. DPA monitors all databases in the Db2 instance.

      For more information about monitoring a single database or all databases in the instance, see Switch to Db2 instance-wide monitoring.

    3. Enter the credentials for the DPA monitoring user account that you created earlier.

  5. If your repository database is Oracle, choose the tablespace in the repository database to store DPA performance data for this monitored instance. Then click Next.

    By default, the performance data is stored in the default tablespace of the repository user. However, data for monitored instances can be stored in separate tablespaces.

    If your repository database is not Oracle, the wizard skips this step.

  6. (Optional) Select the alert groups you want the new database instance to join. Then click Next.

    • If no alert groups exist, or the existing groups do not match the database type of this instance, the wizard skips this step.

    • Group membership can be changed after registration

  7. Review the information and click Register Database Instance.

  8. When the registration is complete, click Finish to return to the DPA home page.

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.