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.

This topic includes the following information:

Before you begin

You must download and install the PATROL for IBM DB2.

To configure the 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. 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 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

Note

Starting the 9.6.21 version of PATROL for IBM DB2, if you enter 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 gets monitored.

Data encryption authentication

Select this option to enable the IBM DB2 authenticated connection. The connection to IBM DB2 databases is established using data encrypt (DATA_ENCRYPT) security mechanism.

Note

Use this option when the IBM DB2 databases are configured as data encrypt (DATA_ENCRYPT).

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

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 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.

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.

Notes:

  • You can use any of the following user names:
    • Empty user - In this case, the KM uses IBM DB2 database credentials.
    • A user name that you enter - In this case, the KM uses the entered user name.
    • $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.

Note: If you use $PATROL_USER as user name, 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 partitions 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.

Note

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.

Note

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.

Note

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 configuation list

SQL query name

Enter the name of the SQL query. This is an user-defined field.

Note

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.

Note

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.

Note

  • 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

Click Add and configure the db2diag log search configuration section.

Note

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.


Field

Description

IBM DB2 Instance name

Enter the IBM DB2 instance name. It supports regex for monitoring multiple instances. Use comma to separate instances.

Note

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

Note

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.

Note

Once 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.

Note

Once you remove the disable monitoring file from the monitoring system, the monitoring will be enabled.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*