This topic outlines the process to create an IBM DB2 least-privileged user (LPU) on Linux/UNIX to use as the monitoring user for the management pack.

For the purposes of this management pack, we chose the SYSMON group as noted in this IBM Knowledge Center article because it has the least permissions of the four groups listed that provide access to snapshot monitoring data.

Procedure

  1. Starting as the root user on Linux/UNIX, run the following command:

    groupadd <system-monitoring-group-name>
    useradd -g <system-monitoring-group-name> <monitoring-user-name>
    passwd <monitoring-user-name>

    Example:

    IMPORTANT:
    groupadd sysmong
    useradd -g sysmong db2lpu
    passwd db2lpu
  2. Switch to the instance master user (in the following example db2inst1 for instance 1):

    su - db2inst1

  3. Grant the necessary permissions by setting the OS group as the SYSMON_GROUP:

    db2 update dbm cfg using SYSMON_GROUP <system-monitoring-group-name>

    Example:

    IMPORTANT:

    db2 update dbm cfg using SYSMON_GROUP SYSMONG

  4. Exit from the master user back to root:

    exit

  5. Reboot the server, then log in again as root. Copy the .bashrc from master user to <monitoring-user-name> in order to get the environment variables that make DB2 work:

    Example (using same variables as before):

    cp -f /home/db2inst1/.bashrc /home/db2lpu/

    chown db2lpu:sysmong /home/db2lpu/.bashrc

  6. Test permissions:
    1. Log in as <monitoring-user-name>.
    2. Connect to a database.

      Example (connecting to sample below):

      db2 connect to sample

  7. Enter the DB2 shell:

    db2

  8. Run our queries from the DB2 shell to test that they all return successfully:

    SELECT coalesce((sum(DATA_LOGICAL_READS) - sum(DATA_PHYSICAL_READS)) / nullif(cast(sum(DATA_LOGICAL_READS) as float), 0), 1) * 100 as buffer_pool_data_hit from SYSIBMADM.BP_HITRATIO group by db_name, snapshot_timestamp

    SELECT COUNT(locks_waiting) as applications_waiting_on_locks from SYSIBMADM.SNAPAPPL WHERE LOCKS_WAITING > 0

    SELECT LOG_UTILIZATION_PERCENT AS log_utilization from SYSIBMADM.LOG_UTILIZATION

    SELECT TOTAL_CONS, DEADLOCKS, LOCK_TIMEOUTS, APPLS_IN_DB2 FROM SYSIBMADM.SNAPDB

    SELECT LOCAL_CONS, REM_CONS_IN FROM SYSIBMADM.SNAPDBM

  9. Optional: To test if your user is in the SYSMON_GROUP without queries, you can use the following OS command:

    db2pd

The following are some useful permission queries that may help with debugging:

SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('DB2LPU', 'U') ) AS T ORDER BY AUTHORITY

SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE >FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('SYSMONG', 'G') ) AS T ORDER BY AUTHORITY

What to do next

Installing the Management Pack (IBM DB2)