Database Agent configuration - DB2

DB2, 10.1, 10.5, 11.1 configuration

DB2 9.8 is not supported.

DB2 monitoring requires either:

  • The SQLADM authority to be assigned to the DB Agent user.
  • All of the per-object permissions listed in the SQLADM authority replacement section.

The SQLADM/per-object permissions are validated at agent creation and agent restart. Not having them causes agent startup to fail.

Explain plan retrieval requires:

  • Explain plan objects to be created and accessible to the DB Agent user (see below).
  • The EXPLAIN authority if the SQLADM authority was not granted. The explain permissions are optional. Not granting them only prevents explain plans from being retrieved.

The SQLADM/EXPLAIN plans are retrieved only if <dtagent> has permissions to execute the query being explained. SQLADM/EXPLAIN authorities enable any query to be explained, irrelevant of permissions to the underlying objects.

SQLADM authority

The following script assigns SQLADM authority to <dtagent> user:

    grant sqladm on database to <dtagent>

SQLADM authority replacement

The following per-object permissions may be granted to the DB Agent user instead of the SQLADM authority.

	grant execute on function SYSPROC.COMPILATION_ENV to <dtagent>
	grant execute on function SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID to <dtagent>
	grant execute on function SYSPROC.MON_FORMAT_LOCK_NAME to <dtagent>
	grant execute on function SYSPROC.MON_GET_CONNECTION to <dtagent>
	grant execute on function SYSPROC.MON_GET_CONTAINER to <dtagent>
	grant execute on function SYSPROC.MON_GET_PKG_CACHE_STMT to <dtagent>
	grant execute on function SYSPROC.MON_GET_SERVICE_SUBCLASS to <dtagent>
	grant execute on function SYSPROC.MON_GET_TABLESPACE to <dtagent>
	grant execute on function SYSPROC.MON_GET_WORKLOAD to <dtagent>
	grant execute on function SYSPROC.MON_GET_LOCKS to <dtagent>

	grant select on SYSIBMADM.ENV_INST_INFO to <dtagent>
	grant select on SYSIBMADM.ENV_SYS_INFO to <dtagent>
	grant select on SYSIBMADM.ENV_SYS_RESOURCES to <dtagent>
	grant select on SYSIBMADM.TBSP_UTILIZATION to <dtagent>

-- execute in addition to the previous for DB2 10.5 and up:

	grant execute on function SYSPROC.MON_GET_INSTANCE to <dtagent>
	grant execute on function SYSPROC.MON_GET_DATABASE to <dtagent>

Explain plan retrieval requirements

Explain plan retrieval requires creation of explain-related objects in the database. These can be created by invoking the following:


The SYSPROC.SYSINSTALLOBJECTS invocation creates explain plan tables, which need to exist for the explain plan functionality to work.

This call creates the explain tables under the SYSTOOLS schema, which is the location assumed by Dynatrace AppMon. See the Explain tables IBM documentation for list of created objects.

If the SQLADM authority was not granted, granting the EXPLAIN authority is needed as shown in the following example to explain queries to which <dtagent> does not have access to:

    GRANT explain ON DATABASE TO <dtagent>;

(SQLADM IBM documentation)
(Explain tables IBM documentation)