DB2 local connection setup

After you have installed the PATROL for IBM DB2, you must configure a monitoring policy. You can use the information in this topic to configure a DB2 local connection setup with the TrueSight console.

Before you begin

You must download and install the PATROL for IBM DB2.

Parameters for the DB2 Local monitor type

In the Add Monitoring Configuration panel, select the following parameters for the DB2 local connection setup monitor type:

Parameter	Selection
Monitoring Solution	IBM DB2
Monitoring Profile	IBM DB2
Monitor Type	DB2 Local connection setup

To add an environment configuration

Click Add and enter the Environment Configuration details.

Field	Description
Environment Name	Enter a unique environment name for monitoring the DB2 databases. A container will be created with the environment name, and this environment will hold all the DB2 instances. The name should not contain blank spaces or any of the following special characters: $\|?\"[]+=&*();!^<>:/
Local IBM DB2 details
IBM DB2 installation home	Enter the path to the installation base directory or the environment variable that contains the installation base directory. Files are installed to the following directories under the installation base directory, as depicted in the following example: Windows: C:\Program Files\IBM\SQLLIB UNIX: /opt/ibm/db2/V10.5 Important: Starting the 9.6.21 version of PATROL for IBM DB2, if you enter the environment variable, ensure that the variable name begins with DB2PATH, for example, DB2PATH, DB2PATH3. Also, ensure that the OS JVM user has access to the DB2PATH environment variable.
IBM DB2 instance List	Specify the list of DB2 instances for monitoring. If the field is left blank, all the instances get monitored.
IBM DB2 database authentication type	Select one of the following authentication types to establish database connection: DEFAULT: If encrypted authentication is disabled in your database, select DEFAULT. DATA_ENCRYPT: If the connection to IBM DB2 databases is established by using the data encrypt security mechanism, select DATA_ENCRYPT. KERBEROS: If the connection to IBM DB2 databases is established by using the Kerberos authentication mechanism, select KERBEROS. Setting up the database connection by using Kerberos authentication Mandatory arguments: Enter the path to the JAAS configuration file for Java authentication settings: -Djava.security.auth.login.config=/db2home/db2inst1/jaas.conf For example, refer the following content for creating jaas.conf file: Oracle Java: com.sun.security.jgss.initiate { com.sun.security.auth.module.Krb5LoginModule required useCcache="FILE:/folder1/folder2/mycachefile" keytab="THE/PATH/TO/YOUR/KEYTAB" useTicketCache=true principal="USERNAME@HOST.DOMAIN.COM" debug=true; }; IBM Java: com.ibm.security.jgss.krb5.initiate { com.ibm.security.auth.module.Krb5LoginModule required debug=true useCcache="FILE:/folder1/folder2/mycachefile" principal="kerberos-principal-domain" useKeytab="/folder3/folder4/mykeytabfile"; }; Enter the path to the Kerberos configuration file for Java: -Djava.security.krb5.conf=/etc/krb5/krb.conf Optional arguments: Enable detailed debugging output for IBM Java Generic Security Services (JGSS): -Dcom.ibm.security.jgss.debug=all Enable detailed debugging output for Kerberos operations in IBM Java: -Dcom.ibm.security.krb5.Krb5Debug=all Set the Kerberos realm to bmc.com for Java applications: -Djava.security.krb5.realm=BMC.COM Enter the Kerberos KDC server and port: -Djava.security.krb5.kdc=xyz.bmc.com:88
IBM DB2 database credentials
User name	Enter the name of the user with permissions to connect and run SQL queries on all DB2 databases. The user should have minimum SYSMON authority with DATAACCESS privileges.
Password Confirm password	Confirm the password of the DB2 user.
Java collector settings
Java path	Enter the full path (do not use the short path format) to the JRE directory on the PATROL Agent host. The Java version must be 8 or later. For example: Windows: C:\Program Files (x86)\Java\jre8 Unix/LINUX: /usr/java8_64 Alternatively, you can also use the $JAVA_HOME environment variable in this field. If you use the $JAVA_HOME variable, the Java location depends on the $JAVA_HOME that you declare in you PATROL agent host. If this field is left blank, the KM looks for the Java executable in the Patrol3\openjdk and Patrol3\jre64 locations.
JVM arguments	Enter the optional Java Virtual Machine (JVM) arguments for the DB2 Java collector.
SSL
Enable SSL	Select the check box to enable SSL connection with the DB2 server. Ensure that the following configurations are set while configuring SSL on DB2 server. Configurations for SSL on DB2 server Set the following SSL properties on the DB2 instance for SSL communication. [db2inst1@<device> ~]$ db2 get dbm cfg \| grep SSL SSL server keydb file (SSL_SVR_KEYDB) = /home/db2inst1/keys/mydbserver.kdb SSL server stash file (SSL_SVR_STASH) = /home/db2inst1/keys/mydbserver.sth SSL server certificate label (SSL_SVR_LABEL) = SSL_Cert_Label SSL service name (SSL_SVCENAME) = 60001 <SSL port number> Set the DB2COMM registry variable to SSL. db2set -i db2inst1 DB2COMM=SSL Run the following command to verify that the KM is able to communicate with DB2 server in SSL mode: [db2inst1@<device> ~]$ netstat -an \| grep <port number> For example: [db2inst1@abc-xyz ~]$ netstat -an \| grep 60001 tcp 0 0 0.0.0.0:60001 0.0.0.0:* LISTEN tcp 0 0 10.135.62.213:60001 10.135.61.165:55950 ESTABLISHED [db2inst1@abc-xyz ~]$ The connection is established on 60001 port.
Trust store file path	Enter the file path of the trust store.
Trust store file password	Enter the password for the trust store.
Java OS credentials
User Name	Enter the name of the OS JVM user (this user must exist where the PATROL Agent and DB2 KM are installed) with permission to run the Java process. Important: You can use any of the following usernames: Empty user - In this case, the KM uses IBM DB2 database credentials. A username that you enter - In this case, the KM uses the entered username. $PATROL_USER - In this case, the KM uses the PATROL default account user. To start the KM backend Java collector by using the PATROL user, specify $PATROL_USER. The KM must use a user that has permissions to run: DB2 CLP commands "df" OS command on the following DB2 files: logpath file: Received by running the query - select NAME,VALUE from SYSIBMADM.DBCFG where (DBPARTITIONNUM=<partition number>) DB_PATH file: Received by running the query - select DB_PATH from SYSIBMADM.SNAPDB where (DBPARTITIONNUM=<partition number>)
Password Confirm password	The password for the OS JVM user. Important: If you use $PATROL_USER as username, you must enter a dummy password. Otherwise, the policy does not save the $PATROL_USER value.
Monitoring data details
Monitoring level	Select the monitoring collection level for the IBM DB2 system. Availability - Select this option to check the availability of instances, connections, and databases. Full data monitoring - Select this option to collect all the monitored groups. Custom data monitoring - Select this option to collect only the monitored groups selected from the custom data module types.
Monitoring categories	Select monitor types for monitoring. Applications - This group is responsible for collecting applications instances and parameters and displaying them under each partition of a database. Buffer Pools - This group is responsible for collecting buffer pools instances and parameters and displaying them under each database’s partition. Capacity - This group is responsible for collecting capacity parameters and displaying them under each partition of a database. HADR - This group is responsible for collecting HADR parameters and displaying them under each partition of HADR standby and primary databases. Instance Attributes - This group is responsible for collecting instance attributes and displaying them under the partition's container node of the instance. Instance Partition Attributes - This group is responsible for collecting instance's partition's attributes and displaying them under each instance’s partition’s node. Locks - This group is responsible for collecting lock parameters and displaying them under each partition of a database. Logs - This group is responsible for collecting log parameters and displaying them under each partition of a database. Performance - This group is responsible for collecting performance parameters and displaying them under each partition of a database. Recovery - This group is responsible for collecting recovery parameters and displaying them under each partition of a database. SQL - This group is responsible for collecting SQL parameters. Tablespaces - This group is responsible for collecting table spaces instances and parameters and displaying them under each partition of a database.

Filter configuration by monitor types

Click Add and filter the monitor types for monitoring. For more information, see Using filter configuration by monitor types.

Tip

You can filter the monitor types at any instance level within the DB2 hierarchy.

Field	Description
IBM DB2 instance name	Enter IBM DB2 instance name. Use comma (,) only to separate the instance names. This field is regex enabled. Important: All the defined filters are applicable for the specified DB2 instances.
IBM DB2 database name	Enter IBM DB2 database name. Use comma (,) only to separate the database names. This field is regex enabled. Important: All the defined filters are applicable for the specified databases.
Filters by monitor types list
Filtering monitor types	Select the IBM DB2 monitor type for monitoring: DB2 Instance DB2 Diag Log DB2 Databases Container DB2 Database DB2 HADR Database DB2 Buffer Pool Container DB2 Buffer Pool DB2 Database Lock DB2 Database Capacity DB2 Partitions Container DB2 Partitions DB2 DB Partition DB2 Applications Container DB2 Application DB2 Application's Agent DB2 Table Spaces Container DB2 Tablespace DB2 Database Log DB2 Database Sql DB2 Database Recovery DB2 Database Performance DB2 Instance Partition Parameters DB2 Instance Parameters DB2 Database Hadr Custom SQL Queries SQL Query
Filtering options	Based on the monitor types selected in the above field, select the filtering option: None Include Exclude
Filtering instances from the selected monitor type	Based on the selection in the filtering options, specify the instances from the selected monitor type for monitoring. This field is regex enabled. Use comma (,) to define multiple instances for the specified monitor type.

Custom SQL queries configuration list

Click Add and configure the custom SQL queries section.

Field	Description
IBM DB2 instance name	Enter the IBM DB2 instance name. It supports regex for monitoring multiple instances. Use comma to separate entries. By default, .* is used which monitors all the instances.
IBM DB2 database name	Enter the IBM DB2 database name. It supports regex for monitoring multiple databases. Use comma to separate entries. By default, .* is used which monitors all the databases.
Custom SQL query configuration list
SQL query name	Enter the name of the SQL query. This is a user-defined field. Important: The name cannot include blank spaces or any of the following special characters: $\|?\"[]+=&*();!^<>:/ After you save the environment configuration, you cannot change the name without losing the environment's historical data. You can change the environment name by making a copy of the monitoring solution and then changing the environment name.
SQL query	Enter the SQL query that you want to execute in the database. Important: The SQL query must not contain the semicolon (;). For example: select TOTAL_CPUS from TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO
Collection time (min)	Enter the SQL query collection time in minutes. The default collection cycle is 10 minutes.
Enable number of records annotation	Select this option to display the query result as an annotation on DB2 custom SQL query number of records metric.

Important

All the defined Custom SQL query configuration lists are applicable on the instances and databases specified above.
If you configure multiple lists of DB2 instances and DB2 databases with same SQL query name and matches the same DB2 instance and DB2 database, the last entered list will be displayed. You will get an annotation about the same on Configuration status attribute.

IBM db2diag log search configuration list

You can define multiple search configurations for same instance. In case, if there are multiple search results, the DiagLogOK parameter value will set the value with high priority/severity and displays a detailed annotation for the search commands and their results.

Click Add and configure the db2diag log search configuration section.

Field	Description
IBM DB2 Instance name	Enter the IBM DB2 instance name. It supports regex for monitoring multiple instances. Use comma to separate instances. Important: The defined searches are applicable for the specified DB2 instance.
IBM db2diag log search criteria list
DiagLogOK attribute states	Select the attribute state for monitoring: PRUNE INFORMATION WARN ALARM
IBM db2diag tool search command	Specify a command that is supported by the IBM db2diag tool. For example: -lastlines 100 -l Warning Important: Be cautious while using this feature, it might affect the performance of the environment monitoring in case if the IBM db2diag tool retrieves a huge chunk of data.

Administration

Click Add and configure the administration details.

Field	Description
Logging	Select this option to enable debugging operations for configured environment. This enables the PSL and Java logging of the specified environment. The PSL debug file of the environment is located under: Patrol3/log/PSO_<DB type>^<environment name>-<Agent host>-<Agent port>.kmlog For example: Patrol3/log/PSO_Local^DB2Env-IL-hhomndf-W1-3181.kmlog The Java debug file of the environment is located under: Patrol3/pso/logs/java/<DB type>^<environment>_<Agent port>_JavaCollector.log For example: Patrol3/pso/logs/java/Local^DB2Env_3181_JavaCollector.log.0
Availability collection interval (min)	Enter the availability collection time in minutes for the DB2 JAVA collector. The default collection time is 1 minutes.
Data collection interval (min)	Enter the data collection time in minutes for the DB2 JAVA collector. The default collection time is 10 minutes.
IBM db2diag log collection interval (min)	Enter the IBM db2diag log collection time in minutes for the DB2 JAVA collector. The default collection time is 5 minutes.
Long running data collection queries threshold (sec)	Enter the threshold to monitor the data collection SQL queries that run longer than the number of seconds entered.
Long running data collection commands threshold (sec)	Enter the threshold to monitor the data collection CLP or OS commands that run longer than the number of seconds entered.
Device mapping	Select this option to enable device mapping for all hosts in this environment. If enabled, all the monitors gets created within a device according to the discovered host name of the monitored system. If disabled, all the monitors gets discovered under the PATROL Agent host device.
Disable monitoring for IBM DB2 remote databases	Select this option to disable the discovery of IBM DB2 remote databases.

Disable monitoring configuration

Click Add and configure the disable monitoring details.

Field	Description
Disable monitoring environment	Select this option to disable the KM from discovering and monitoring the configured environment.
IBM DB2 instance disable monitoring list
Instance name	Enter the IBM DB2 instance name that you want to disable from monitoring. The disable monitoring file location configuration will be applied to this IBM DB2 instance. Regex is not supported
Disable monitoring file location	Enter the absolute location of the disable monitoring file. The disable monitoring file should be configured on the IBM DB2 host. Important: After you remove the disable monitoring file from the monitoring system, monitoring will be enabled.

Global monitoring setting

Field	Description
Logging	Select this option to enable main PSL debug logging for global configuration. The log file path:Patrol3/log/PSO_Local_main-<Agent host>-<Agent port>.kmlog
Disable environments monitoring file location	Enter the absolute location of the disable monitoring file. The disable monitoring file should be configured on the PATROL Agent host. Important: After you remove the disable monitoring file from the monitoring system, the monitoring will be enabled.