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.
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>
groupadd sysmong useradd -g sysmong db2lpu passwd db2lpu
Switch to the instance master user (in the following example db2inst1 for instance 1):
su - db2inst1
Grant the necessary permissions by setting the OS group as the SYSMON_GROUP:
db2 update dbm cfg using SYSMON_GROUP <system-monitoring-group-name>
db2 update dbm cfg using SYSMON_GROUP SYSMONG
Exit from the master user back to root:
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
- Test permissions:
- Log in as <monitoring-user-name>.
Connect to a database.
Example (connecting to sample below):
db2 connect to sample
Enter the DB2 shell:
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
Optional: To test if your user is in the SYSMON_GROUP without queries, you can use the following OS command:
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