2 Configuring the IBM DB2 Database Plug-in

This chapter provides the instructions for the configuration tasks you must complete before adding an IBM DB2 Database target to Oracle Enterprise Manager.

The following configuration tasks are provided:

Configure the Management Agent to Deploy the Plug-In

To configure the Agent, you must first ensure that the user starting the Agent service belongs to the Local Administrators Group. Also, you must set the preferred credentials on all Agents where you want to deploy the plug-in. To do so, follow the instructions given in the following sections:

Assigning Advanced Privileges to User

To assign advanced privileges, do the following:

  1. Locally on the Microsoft Windows node hosting the Agent, check that the user starting the Agent service belongs to the Local Administrators Group. If not, add it.
  2. Open the Local Security Settings Windows Tool and give the following Advanced Privileges to the user starting the Agent service:

    • Act as part of the operating system

    • Adjust memory quotas for a process

    • Logon as batch job

    • Replace a process level token

  3. Restart the Agent service if it is running.
  4. Set the Preferred Credentials for the Host and the Agent in Enterprise Manager. For more information, see Setting and Validating Preferred Credentials.

    • The OS user set in the Preferred Credentials must belong to the Local Administrators Group.

    • This OS user must have the following Advanced Privileges:

      • Act as part of the operating system

      • Adjust memory quotas for a process

      • Log on as batch job

      • Replace a process level token

Setting and Validating Preferred Credentials

To set the preferred credentials on all Agents where you want to deploy the plug-in, do the following:

  1. In Enterprise Manager, from the Setup menu, select Security, then Preferred Credentials.

    The Preferred Credentials page appears, showing a table of targets.

  2. Select Host target type from the table and then click Managed Preferred Credentials.

    The Host Preferred Credentials page appears.

  3. In the Host Preferred Credentials page, in the Target Credentials section, select the host that is running the Management Agent where the plug-in has to be deployed, and click Set.
  4. In the Select Named Credential dialog box, Select Credential as New and specify the user name and password and click Test and Save. If your test runs successfully, your credentials are set correctly.
  5. Run the OS Command job for the Management Agent where the plug-in has to be deployed.

    • Log in to Enterprise Manager.

    • From the Enterprise menu, select Job and then Activity.

    • In the Job Activity page, from the Create Job list, select OS Command, and click Go.

    • Fill up the details required in the following pages, and click Submit to run the job. If the job runs successfully, your credentials are set correctly.

Note:

In order to run jobs from the UI, the target's Agent Host Preferred Credentials must be for a user that can run the db2 command-line utility and has permissions to start, stop, quiesce, and unquiesce the IBM DB2 database.

Using a Suitable Operating System User and Assigning Authorities and Privileges

The IBM DB2 Database plug-in accesses monitoring functions and system views in IBM DB2. For the plug-in to have access to these functions, you have to use a suitable operating system user and assign this new user to a user group. The operating system user must have at least the minimum privileges. In addition, you have to assign the correct authority levels to this user.

Note:

IBM DB2 users must be operating system users. IBM DB2 cannot have its own database users because it relies on the host operating system for security.

If you do not have an operating system user already created, first create one on the host where IBM DB2 is running.

Creating the User and Granting CONNECT

At the db2 command line, connect to the database as an administrator and grant CONNECT privilege to the monitoring user: 

db2 => CONNECT TO <database> USER <admin_user>
db2 => GRANT CONNECT ON DATABASE TO USER <monitoring_user>

To verify, connect to the database with the newly created user:

db2 => CONNECT TO <database> USER <monitoring_user> USING <password>

Note:

These steps can also be performed using any DB2-compatible administration tool, if available.

Assigning SYSMON Authority

SYSMON authority level is required to monitor IBM DB2. This level is required to access the MON_GET_* monitoring table functions and the SYSIBMADM administrative views used by the plug-in.

Also, assign authorities and privileges for the operating system UserGroup. The authorities supported with IBM DB2 are SYSADM, SYSCTRL, SYSMAINT, DBADM, and LOAD. The SYSADM, SYSCTRL, and SYSMAINT authorities cannot be granted using the GRANT SQL statement. These special authorities can only be set from the database manager configuration file. DBADM privilege can only be granted by a user at the SYSADM authorization level.

Follow these steps to set SYSMON authority level to your UserGroup:

  1. At the db2 => prompt, run the following commands:
    db2 => update dbm cfg using sysmon_group USERGROUP
db2 => db2stop
db2 => db2start
  2. To check whether the changes are effective, run the following command:
    db2 => get dbm cfg

    The following will be the output of the previous command:

    Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
.....
   SYSADM group name          (SYSADM_GROUP)   =
   SYSCTRL group name         (SYSCTRL_GROUP)  =
   SYSMAINT group name        (SYSMAINT_GROUP) =
   SYSMON group name          (SYSMON_GROUP)   = USERGROUP
......

Additional Permissions

In addition to SYSMON authority, the monitoring user requires the following permissions:

  • EXECUTE privilege on the following SYSPROC functions:
    • SYSPROC.ENV_GET_SYS_INFO
    • SYSPROC.ENV_GET_PROD_INFO
    • SYSPROC.ENV_GET_INST_INFO
    • SYSPROC.ENV_GET_REG_VARIABLES
    • SYSPROC.DB_PARTITIONS

    Grant these using: 

    db2 => GRANT EXECUTE ON FUNCTION SYSPROC.ENV_GET_SYS_INFO TO USER <monitoring_user>
db2 => GRANT EXECUTE ON FUNCTION SYSPROC.ENV_GET_PROD_INFO TO USER <monitoring_user>
db2 => GRANT EXECUTE ON FUNCTION SYSPROC.ENV_GET_INST_INFO TO USER <monitoring_user>
db2 => GRANT EXECUTE ON FUNCTION SYSPROC.ENV_GET_REG_VARIABLES TO USER <monitoring_user>
db2 => GRANT EXECUTE ON FUNCTION SYSPROC.DB_PARTITIONS TO USER <monitoring_user>
  • SELECT privilege on SYSTOOLS.STMG_DBSIZE_INFO. Ensure that the SYSTOOLS.STMG_DBSIZE_INFO table is created first. For information, see Configurations Required for Avoiding Metric Collection Errors for Database Monitoring Metrics.
    db2 => GRANT SELECT ON TABLE SYSTOOLS.STMG_DBSIZE_INFO TO USER <monitoring_user>
  • SELECT privilege on SYSIBM.SYSTABLES:
    db2 => GRANT SELECT ON TABLE SYSIBM.SYSTABLES TO USER <monitoring_user>
  • For diagnostic log file monitoring (local monitoring only): the operating system user running the Management Agent must have read access to the db2diag.log file located at the DIAGPATH directory.

Consolidated Permission Checklist

Permission Scope Purpose
CONNECT Database JDBC connectivity
SYSMON authority Instance Access to MON_GET_* functions and SYSIBMADM views
EXECUTE on SYSPROC functions Instance ECM configuration collection
SELECT on SYSTOOLS.STMG_DBSIZE_INFO Database Database size monitoring
SELECT on SYSIBM.SYSTABLES Database Lock waits by table monitoring
OS read access to db2diag.log Host filesystem Diagnostic log monitoring (local only)

Note:

To understand how authorities and privileges are implemented in IBM DB2, access the IBM website.

Configuring IBM DB2 for Database Monitoring Metrics

The following sections explain the post-installation configuration steps you need to perform on IBM DB2:

Configurations Required for Avoiding Metric Collection Errors for Database Monitoring Metrics

To avoid metric collection errors for the "Database Monitoring" metrics, make a call to the GET_DBSIZE_INFO package so that the STMG_DBSIZE_INFO table gets created and populated with the required data.

The GET_DBSIZE_INFO procedure calculates the database size and maximum capacity. The calculated values are returned as procedure output parameters and cached in the SYSTOOLS.STMG_DBSIZE_INFO table. The procedure caches these values because the calculations are costly.

The SYSTOOLS.STMG_DBSIZE_INFO table is created automatically the first time the procedure runs. If there are values cached in the SYSTOOLS.STMG_DBSIZE_INFO table and they are current enough, as determined by the snapshot-timestamp and refresh-window values, then these cached values are returned.

If the cached values are not current enough, new cached values are calculated, inserted into the SYSTOOLS.STMG_DBSIZE_INFO table and returned, and the snapshot-timestamp value is updated. The last parameter in the GET_DBSIZE_INFO call is refresh window.

Default value refresh window (time difference between successive calls) is 30 minutes. If your database is growing at a faster rate, then you can set a lower value.

To make a call to GET_DBSIZE_INFO by CLP, run the following command: 

db2==>CALL GET_DBSIZE_INFO(?, ?, ?, -1)

In this case, the refresh window is 30 minutes.

Configurations Required for Lock and Lock Waits Monitoring

To collect lock and lock waits information in the Analysis page, the monitoring user must have SYSMON authority on the database instance. This authority provides access to the SYSIBMADM.MON_LOCKWAITS administrative view.

If you have already configured SYSMON authority as described in Using a Suitable Operating System User and Assigning Authorities and Privileges, no additional configuration is needed for lock and lock waits monitoring.