Knowledge Base >> DB2 Permissions for Ignite Monitoring

DB2 Permissions for Ignite Monitoring - Confio Article 1548

Confio Article: 1548 Published: May 11 2009 12:24 PM
Version: >= 4.3 Topic(s): Install and Setup Ignite Management
Product(s): Ignite
Database(s): DB2

DB2 Permissions for Ignite Monitoring

 

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

Ignite also 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, Ignite requires the DBADM authority for each monitored database. This allows Ignite to explain the data access path for each specific sql statement collected.

You can optionally turn the DFT_MON_BUFFERPOOL parameter on to allow Ignite 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 ignite 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 as using groups MAY cause unexpected behavior for other users.

 

Tags: monitoring DB2 authority privilege security dft_mon_stmt dft_mon_bufferpool dbadm sysmon