Creating a Special User for Monitoring DB2 Server

All the tests that the eG agent runs on a DB2 UDB/DB2 DPF server (version 10.5 and above) should be configured with the credentials of a user who is authorized to access the target database server and collect the required metrics. It is recommended that you create a special user for this purpose. The steps for creating such a user are detailed below:

  1. Create a user for monitoring purpose - say, eguser - at the OS-level on the operating system hosting the target DB2 server. Specify the name of this user in the USER text box while configuring the tests in the test configuration page.

  2. Next, connect to the database as the user (with SECADM or SYSADM privilege) using the following command:

    CONNECT TO <dbname> USER <username> USING <password>;

    For example, if the dbname is DB2master, the username is DBadmin and the password is DBadminpassword, then, the command should be:

    CONNECT TO DB2master USER DBadmin USING DBadminpassword;

  3. Next grant the execute privilege for the newly created user to execute the following functions:

    GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_CONNECTION TO USER eguser;

    GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_ACTIVITY TO USER eguser;

    GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_MEMORY_SET TO USER eguser;

    GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_DATABASE TO USER eguser;

    GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_TABLESPACE TO USER eguser;

    GRANT EXECUTE ON FUNCTION SYSPROC.MON_CURRENT_SQL TO USER eguser;

    GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_TRANSACTION_LOG TO USER eguser;

    GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_HADR TO USER eguser;

    GRANT SELECT ON TABLE SYSIBMADM.MON_CURRENT_SQL TO USER eguser;

    GRANT SELECT ON TABLE SYSIBMADM.DB_HISTORY TO USER eguser;

    GRANT SELECT ON TABLE SYSCAT.TABLES TO USER eguser;

    GRANT SELECT ON TABLE SYSIBMADM.ENV_INST_INFO TO USER eguser;

    GRANT SELECT ON TABLE SYSIBMADM.DBMCFG TO USER eguser;

    Note:

    In case if multiple databases are available within the target DB2 UDB instance, then, the user connecting to the database (in our example, DBadmin), should grant the above-mentioned privileges for each and every database individually.

All the tests that the eG agent runs on a DB2 UDB server (version 8.0 to version 10.1) or on a DB2 UDB/DB2 DPF server, should be configured with the credentials of a user who has any of the following privileges to the monitored DATABASE: SYSADM or SYSCTRL or SYSMAINT or SYSMON.

It is recommended that you create a special user for this purpose. The steps for creating such a user are detailed below:

  1. Create a user group - say, eg_mon_grp - on the operating system hosting the DB2 server.
  2. Create a user - say, eg_user - at the OS-level, and add this new user to the group created in step 1.

  3. Next, you need to grant the SYSADM or SYSCTRL or SYSMAINT or SYSMON privilege to the group created in step 1. For instance, to grant the SYSMON privilege to the eg_mon_grp in our example, do the following:

    • Go to the DB2 prompt.

    • Issue the following command at the prompt:

      UPDATE DBM CFG USING SYSMON_GROUP eg_mon_grp;

    • Next, connect to the database as the user (with SECADM or SYSADM privilege) using the following command:

      CONNECT TO <dbname> USER <username> USING <password>;

      For example, if the dbname is DB2master, the username is DBadmin and the password is DBadminpassword, then, the command should be:

      CONNECT TO DB2master USER DBadmin USING DBadminpassword;

    • Next grant the execute privilege for the newly created user (in our example, it is eg_user) to execute the following functions:

      GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_CONNECTION TO USER eg_user;

      GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_DATABASE TO USER eg_user;

      GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_TABLESPACE TO USER eg_user;

      GRANT SELECT ON TABLE SYSIBMADM.MON_CURRENT_SQL TO USER eg_user;

      GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_TRANSACTION_LOG TO USER eg_user;

      GRANT EXECUTE ON FUNCTION SYSPROC.MON_GET_HADR TO USER eg_user;

      Note:

      In case if multiple databases are available within the target DB2 UDB instance, then, the user connecting to the database (in our example, DBadmin), should grant the above-mentioned privileges for each and every database individually.

    • Finally, restart the DB2 instance by issuing the following commands, one after another:

      db2stop force

      db2start