SQL administration authority (SQLADM)

SQLADM authority is the authority required to monitor and tune SQL statements.

SQLADM authority can be granted by the security administrator (who holds SECADM authority) or a user who possesses ACCESSCTRL authority. SQLADM authority can be granted to a user, a group, a role, or to PUBLIC. SQLADM authority gives a user the ability to perform the following functions:
  • Execution of the following SQL statements:
    • CREATE EVENT MONITOR
    • DROP EVENT MONITOR
    • EXPLAIN
    • FLUSH EVENT MONITOR
    • FLUSH OPTIMIZATION PROFILE CACHE
    • FLUSH PACKAGE CACHE
    • PREPARE
    • REORG INDEXES/TABLE
    • RUNSTATS
    • SET EVENT MONITOR STATE
    • ALTER MODEL
    • DROP MODEL
    Note: If the DB2AUTH registry variable is set to SQLADM_NO_RUNSTATS_REORG, users with SQLADM authority will not be able to perform reorg or runstats operations.
  • Execution of certain clauses of the following workload manager SQL statements:
    • The following clauses of the ALTER SERVICE CLASS statement:
      • COLLECT AGGREGATE ACTIVITY DATA
      • COLLECT AGGREGATE REQUEST DATA
      • COLLECT REQUEST METRICS
    • The following clause of the ALTER THRESHOLD statement
      • WHEN EXCEEDED COLLECT ACTIVITY DATA
      .
    • The following clauses of the ALTER WORK ACTION SET statement that allow you to alter a work action:
      • ALTER WORK ACTION ... COLLECT ACTIVITY DATA
      • ALTER WORK ACTION ... COLLECT AGGREGATE ACTIVITY DATA
      • ALTER WORK ACTION ... WHEN EXCEEDED COLLECT ACTIVITY DATA
    • The following clauses of the ALTER WORKLOAD statement:
      • COLLECT ACTIVITY METRICS
      • COLLECT AGGREGATE ACTIVITY DATA
      • COLLECT LOCK TIMEOUT DATA
      • COLLECT LOCK WAIT DATA
      • COLLECT UNIT OF WORK DATA
  • SELECT privilege on the system catalog tables and views
  • EXECUTE privilege on all built-in Db2® routines (except audit routines and the SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY procedure).
    Note: With the release of the Db2 11.5.8 security special build 29133, the SQLADM authority no longer has the EXECUTE privilege on the SYSIBMADM.UTL_DIR module, by default. The EXECUTE privilege is available to the SQLADM authority only when the DB2_ALTERNATE_AUTHZ_BEHAVIOUR is set to UTL_DIR_SQLADM_GRANT during a CREATE DATABASE operation, a database migration, or a db2updv operation.

SQLADM authority is a subset of the database administrator (DBADM) authority.

EXPLAIN authority is a subset of the SQLADM authority.