Return
-
Title
Top SQL not collected with the DB2 agent -
Description
Top SQL data is missing from certain DB2 databases. Looking at the release notes, the following is written:
"The required user database privilege is SYSMON authorization on the monitored instance."
However, this has been granted on the user monitoring the DB2 database.
-
Cause
Beginning with the 5.6.6.1 DB2 cartridge, additional privileges and monitoring switch settings are required for full monitoring:
Permissions: The following permissions are now required:EXECUTE privilege on the PD_GET_DIAG_HIST table function (version 10 and above)EXECUTE privilege on the MON_GET_SERVICE_SUBCLASS_DETAILS table function (version 9.7.0.1 and above)EXECUTE privilege on the MON_GET_PKG_CACHE_STMT table function (version 9.7.0.1 and above)In addition, ensure that the following monitoring configuration parameters are set to base level (at least): MON_REQ_METRICS and MON_ACT_METRICS. These parameters can be set by updating the database manager configuration.As well, the required user database privilege is SYSMON authorization on the monitored instance. -
Resolution
The commands to activate the monitor switches in DB2 are:UPDATE db cfg for @replace_db_name using MON_REQ_METRICS BASEUPDATE db cfg for @replace_db_name using MON_ACT_METRICS BASEThe commands to include the additional privileges for DB2 are:grant execute on function SYSPROC.MON_GET_PKG_CACHE_STMT TO USER foglightgrant execute on function SYSPROC.MON_GET_SERVICE_SUBCLASS_DETAILS TO USER foglightgrant execute on function SYSPROC.PD_GET_DIAG_HIST TO USER foglight