Submit a ticketCall us

Announcing NCM 7.7
With NCM 7.7, you can examine the rules that make up an access control list for a Cisco ASA device. Then you can apply filters to display only rules that meet the specified criteria, order the rules by line number or by the hit count, and much more.
See new features and improvements.

Home > Success Center > Database Performance Analyzer (DPA) > Required DB2 permissions needed by DPA for monitoring

Required DB2 permissions needed by DPA for monitoring

Overview

This article details DB2 permissions for DPA monitoring.

Environment

  • All versions of DPA

Details

DPA requires an operating system user to have SYSMON authority and connect privileges in order to monitor a DB2 instance.  DPA uses this authority to view the snapshot tables: table(snapshot_statement) and table(snapshot_appl_info).  Then, DPA records the statement operations or wait events for each active session in the monitored database into a performance data warehouse.

DPA requires that the instance wide parameter, DFT_MON_STMT, be turned on in order to monitor the instance. 

 

Note: Turning on DFT_MON_STMT requires an instance restart before it will take effect.

To obtain Access Plans, DPA requires the DBADM authority for each monitored database. This allows DPA to explain the data access path for each specific SQL statement collected.

You can optionally turn on the DFT_MON_BUFPOOL parameter to allow DPA to collect the following resource metrics:

  • DB Physical I/O Rate
  • DB Physical I/O Rate

SYSMON authority defined

The SYSMON_GROUP Users, having SYSMON authority at the instance level, have the ability to take database system monitor snapshots of a database manager instance or its databases. SYSMON authority includes the ability to use the following commands:

  • GET DATABASE MANAGER MONITOR SWITCHES
  • GET MONITOR SWITCHES
  • GET SNAPSHOT
  • LIST ACTIVE DATABASES
  • LIST APPLICATIONS
  • LIST DCS APPLICATIONS
  • RESET MONITOR
  • UPDATE MONITOR SWITCHES

Users with SYSADM, SYSCTRL, or SYSMAINT authority automatically have the ability to take database system monitor snapshots and to use these commands.  DBADM does not.

 

Notes:

  • If using anything but DBADM or DB2ADM groups, explain plans will not function properly unless the DPA user is granted SELECT privilege on every table being accessed in the SQL as well as has the necessary explain_* tables.
  • Do not set up DB2 GROUPs if you are not familiar with DB2 permissions. Using groups may cause unexpected behavior for other users.
Last modified
10:21, 28 Jun 2016

Tags

Classifications

Public