IDUG Content Committee

Continuous Diagnostic Data Collection and Archival – Introducing db2histmon Historical Monitoring

- David Sciaraffa, Software Engineering Manager – IBM Db2

When a problem arises in your Db2 stack, the availability of diagnostic information before, during, and after the time-frame of the problematic event is often paramount to successful diagnosis. When the diagnostic messages in the db2diag.log or notify log are insufficient, it is not uncommon for IBM support to provide instructions to enable a specific set of diagnostic data collection, and wait for a re-occurrence of the problem, in order to begin narrowing the root cause. 

The new Db2 Historical Monitoring (db2histmon) scripts (available here) are a successor to the older Db2 Persistent Diagnostic Data Collection scripts, and similarly collect a broad range of Db2 and Operating System diagnostic data, and retain this data for some period of time, allowing for the initial triage of many types of issues.

Diagnostic data is collected at various intervals (ranging from 1min to 1hour, depending on the kind of data), and can be easily customized and added to. The scripts will collect data only while the database is activated, which can help to reduce the generation of unnecessary data.

By enabling the collection of Db2 Historical Monitoring (db2histmon), you can improve the odds that helpful diagnostic information is available after the first occurrence of a problem.

This blog provides an overview of how to enable and customize the new Db2 Historical Monitoring (db2histmon) data collection, and also provides an overview of a basic report generation script.

 
What Data is Collected?

Later in this blog, I’ll describe how to customize the data collection by adding or removing whatever diagnostic data your prefer.
By default the following data (as defined in the task_details.json file, described later) is collected:

Downloading the Db2 Historical Monitoring (db2histmon) scripts:

The scripts are available and can be downloaded from the open-source Github repo: https://github.com/IBM/db2histmon

Pre-requisites:

• The scripts can be deployed in any Unix or Windows environment where Db2 is supported.
• Python2or higher, as well as the python ibm_db module are required to execute the scripts.
• The scripts can be deployed on any relatively modern version of Db2 (Version 10.1, 10.5, 11.1, 11.5). 
• Db2 DBADM authority is required on the database where the historical monitoring will be deployed.
• A C++ compiler (for example g++ on Unix or Visual Studio on Windows) is required (in order for the scripts to use bldrtn to compile the external C++ UDFs which are used by the historical monitoring framework).

Overview of the Db2 Historical Monitoring framework:

The historical monitoring scripts will utilize a set of control tables, procedures and external UDFs to operate, with the DB2 Admin Task Scheduler executing the data collection tasks at the desired frequency (minimum 1min), and will generate diagnostic/monitoring output into the active data collection folder first, and then archived into the data archival folder every hour.

Consider the following high-level process flow diagram:

Setup Folder:

After downloading the scripts to your local database server system, you'll notice three folders, 1_setup, 2_loader, 3_quickparser, and 4_report.

     (db2inst1@hotellnx112) /home/db2inst1/db2histmon-master/

     $ ls

     1_setup/  
     2_loader/   
     3_quickparser/   
     4_report/

Let’s focus on the 1_setup/ folder (which is used to setup and deploy the historical monitoring framework and begin data collection). In a subsequent blog I will provide an overview of the 2_loader script (which is used to easily load the data collections into a database so it can be queried), and the 3_quickparser script (which is used to display the data collections to a terminal window in a columnar format for easier viewing).  Later in this blog I’ll provide an overview of the 4_report script (which is used to generate a summary report of key metrics within a data collection set).

Within the 1_setup/ folder, we find three files, and sub-folder named sql/

• • The mdfile contains a technical description of the setup scripts, input options, as well as a brief architectural overview of how the scripts work. The purpose of this blog is to expand on this information with some visual examples.
  • The pyfile is the main setup script, described in detail below.
  • The jsonfile, described in detail below, contains the definition of each data collection task, and is used by the setup.py script to deploy each task.

Within the 1_setup/sql/ folder, we find the definition of the control tables, procedures, and user-defined functions which the setup.py script will create in the specified database. The details of these files would mostly be useful to developers or administrators who wish to explore and expand on the framework. I do not explore them in detail in this blog.

     (db2inst1@hotellnx112) /home/db2inst1/db2histmon-master/1_setup/sql

     $ ls

 

Overview of the task_details.json file:

The task_details.json file defines each data collection task, and is used by the setup.py script to deploy each data collection task into the Db2 Admin Task Scheduler.

The default task_details.json file that is included with the scripts looks like so:

 

• • The "collection_name" defines the name of the collection task (this name will be used later to generate the filename into which this data collection will be output, so avoid using spaces or special characters).
  • The "collection_class"can be either "SYS" or "SQL".  If this task is defined as "SYS" then the collection_command is invoked as a system call to the operating system. If this task is defined as "SQL", then the collection_command is invoked as an sql query against the database.
  • The "collection_command"is the operating system or sql query which generates the desired monitoring or diagnostic information.
  • The "collection_freq" defines the frequency of data collection, and this is specified in cron format. The maximum frequency is every 1 minute. 
  • The "collection_level"can be either "1" or "2".  When the setup.py script is executed to deploy these data collection tasks to the database, the administrator has the option of deploying only a small set of basic data collection (which are those tasks defined with a collection_level value of 1), or an expanded set of data collection (which are those tasks defined with a collection_level value of 2).
  • The "collection_condition"is used to define the environmental condition under which this data collection task should run. As of the time of this writing, the possible values are "HADR", "PURESCALE", "UNIX", "WINDOWS", or nothing. For example, if a task is defined with a collection_condition value of "HADR", then the task is only executed if an db2 HADR environment is detected. If the collection_condition is nothing, then the task is always executed.
  • The "loader_join_columns", "loader_diff_exempt_columns", and "quickparse_summary_columns"will be the focus of a subsequent blog.

setup.py usage and arguments:
Use the setup.py --help option to display usage:

• • The [database] name parameter is mandatory and specifies the local database against which the historical monitoring setup will be deployed.  If you have multiple databases in your database instance, and wish to deploy historical monitoring on these other databases, you must perform the setup.py script on each one separately.
  • The--username and --password arguments are used to establish the database connection. These are required if your database is configured to disallow local user IPC connections without a password. (If unspecified, a connection to the database using the current user is used).
  • The --cleanup option will perform a full removal of all previously created IBMHIST control tables, procedures, and external UDFs, and data collection tasks. Any previous data collection files or archived sub-folders will not be removed.
  • The --update_config_onlyoption will perform only the creation (or re-creation) of the IBMHIST control tables, procedures, and external UDFs, and configure (or re-configure) any options/arguments that are specified. Note that default values are used for any options/arguments which are not specified, even if these arguments were specified during a previous invocation of the script.
  • The --update_tasks_onlyoption will perform only the removal of any previously deployed data collection tasks, and will parse of the task_details.json file and (re)deploy all data collections tasks.
    (Note, when neither the --update_config_only or --update_tasks_only options are specified, then both operations are always performed).
  • The --bldrtn_pathoptional argument specifies the fully qualified path to the sqllib/samples/cpp/bldrtn This is required to compile the external C++ UDFs which perform the collection of operating system 'SYS' data collection tasks. (If unspecified, the default path /home/<current_user>/sqllib/samples/cpp/bldrtn is used).
  • The --coll_pathoptional argument specifies the fully qualified path where a data collection sub-folder will be created (every hour) and the monitoring/diagnostic data will be placed into. (If unspecified, the default path /home/<current_user>/sqllib/db2dump/ is used).
  • The --arch_path optional argument specifies the fully qualified path to the folder where the data collection folder (every hour) will be moved after it is removed from the active coll_path. (If unspecified, the default path /home/<current_user>/sqllib/db2dump/is used).
  • The --max_sizeoptional argument specifies the maximum size (in bytes) of archival data to retain within the arch_path. When this limit is breached, hourly data collection folders (starting with the oldest) will be deleted. (If unspecified, the default value 1073741824 bytes (1GB) is used).
  • The --arch_cmdoptional argument specifies the operating system command that is used (every hour) to tar/zip/compress the active data collection folder into the archive folder. A filename (in the format '<dbname>_<yyyy><mm><dd><hh>') is automatically generated and should not be specified. Tokens '_src_' and '_dest_' are used as placeholders in place of the coll_path and arch_path  (If unspecified, the default command 'tar -caf _dest_ _src_' is assumed). For example, if one wanted to use the star unix command, instead of the default tar command, one might specify 'star -c -f=_dest_ _src_'.
  • The --arch_extoptional argument specifies the extension to tack onto the archived folder's file after the arch_cmd is completed. (If unspecified, the default extension ".tar.gz" is used).
  • The --coll_leveloptional argument specifies the level of data collections. If "1" is specified, only the data collection tasks in the task_details.json file with a "collection_level" value of 1 are deployed.  If "2" is specified, any data collection tasks with a "collection_level" value of 1 or 2 are deployed.

Example of running the setup.py script:
Here, we execute the setup.py script against database TESTDB1, using all default arguments:
 First, we see the creation of the SYSTOOLSPACE table space (if not already existing), and the determination of environment conditions (UNIX/WINDOWS, HADR, and/or PURESCALE):
   
Next we see the unscheduling of any previously scheduled tasks:

Next we see the creation (or recreation) of the IBMHIST schema, the creation of the IBMHIST control tables, procedures, and compilation of the external UDFs.

Next we see the configuration of settings based on setup.py input arguments (or default values as in this example):
 
 Next we see some validation tests and sanity checks:
 
Next we see the parsing of the task_details.json file, and the registration of data collection tasks to the Db2 Admin Task Scheduler:
 
Finally, the setup script is complete. Within about 5mins the Db2 Admin Task Scheduler should begin executing the data collection tasks, at their defined frequency.
 

The Data Collection and Data Archive folders:

Approximately 5mins after executing the setup script, the Db2 Admin Task Scheduler should begin executing the data collection tasks (while the database remains activated) at their defined collection frequency.

 

Within the data collection folder, we can see that an hourly data collection sub-folder has been created:

TESTDB1_2020062714/

And within this hourly data collection sub-folder, we see the output of each of the data collection tasks, at their defined frequencies.  In this example, I listed the directory contents approximately 9mins after the Db2 Admin Task Scheduler began executing the tasks:

After a few hours, I can see that each hours data collection folders has been tar+gzipped and moved to the archive folder:

The IBMHIST Control Tables

As mentioned earlier, the historical monitoring framework uses a set of control tables in order to operate. We can see the tables here:  

 

• The IBMHIST.TAB_CONFIG table contains information about the configuration parameters/arguments that were passed into the setup.py script (or the default value, if they were not specified).

        
 

• The IBMHIST.TAB_DIRS table contains information about the current (and any previously specified) data collection paths (coll_path), and a history of each data archive path (arch_path), including the size of the archived file.

          For example, we can see the current hourly data collection path as so:
 
       
 
        Or we can see a list of all the previously archived hourly archived folders as so:
 
      
 
 

• The IBMHIST.TAB_TASKS table contains information about each data collection task, as was defined in the task_detail.json file when the full setup.py was last performed (or refreshed).

          For example, we can list each active data collection task including it's operating system command or sql query:
 
        
   
            
 
 

• The IBMHIST.TAB_ERRS will contain a history of any execution errors that occurred by the historical monitoring framework:

      
Example of modifying a setup parameter (changing the data collection path):
 
First we create a new folder to host the active data collection:
 
 
Since the data collections are performed by external UDFs under a fenced userid (not the db2-instance-owner userid), our new folder must be accessible to the fenced userid, for simplicity I'm giving the folder permission to everyone:
 
 
Next I execute the setup script, specifying the --coll_path option with our new path. I also specify the --update_config_only option, since I did not modify the task_details.json file and  do not need to update all the data collection tasks.
     
Now all subsequent data collection tasks will be put into the new data collection path.

Example of Adding a new data collection task (a new operating system metric):

In this example, I want to add a new task to collect "ps -elf" output from the operating system, every minute.

 

First, we edit our task_details.json file to add a new entry to the bottom for our new task called 'PS_ELF' as so:

(note, I can probably just leave the collection_condition field blank, since I do not intend to run this task_details.json file in a Windows environment, and thus always executing this data collection task would be fine, but for completeness I'll add UNIX as a collection_condition, since 'ps -elf' makes no sense in Windows).

 

Next, I executed the setup.py script.  Since I'm only adding a new task, I'll just use the --update_tasks_only option.  We can see our new PS_ELF task included:

Done

If we examine the IBMHIST.TAB_TASKS table, we can also see the new PS_ELF data collection task:

 <db2inst1@hotellnx112> /home/db2inst1/db2histmon-master/1_setup/

If we examine the  SYSTOOLS.ADMIN_TASK_LIST table, we can see that our PS_ELF task has been added to the admin task scheduler:

Lastly, if we wait approximately 5mins for the Db2 Admin Task Scheduler to process the new task, we can then begin seeing the output of our new PS_ELF task within the hourly data collection folder:

Example of Adding a new data collection task  (a new sql query):

In this example, I want to add a new task to collect MON_GET_FCM() output every 10mins.
First, I'll edit the task_details.json file to add a new entry to the bottom for our new task called 'MON_GET_FCM', as so:

Next, I'll invoke the setup.py script. Here I use the --update_tasks_only option.  We can see the new MON_GET_FCM task added:

We can see our new task has been added to the IBMHIST.TAB_TASKS table:

Lastly, if we wait approximately 5mins for the Db2 Admin Task Scheduler to process the new task, we can then begin seeing the output of our new MON_GET_FCM task within the hourly data collection folder:

Example of Removing an data collection task:

The cleanest way to remove a data collection task is to edit the task_details.json file, remove the desired task entry, and then run the setup.py script again, using the --update_tasks_only option.
For example, if I wanted to remove the iostat data collection task, I would remove this entry from the task_details.json file:

And the re-run the setup.py script using the --update_tasks_only option: 

Example of Stopping all data collection tasks:

The easiest way to stop all data collection tasks is to simply disable and remove the historical monitoring framework from the database, using the setup.py --cleanup argument:

You can validate that no data collection tasks are scheduled within the Admin Task Scheduler by:

Generating Reports from Db2 historical monitoring (db2histmon) data

The report generation script can be used to scrape previously generated db2histmon data collections and produce summary reports in multiple flavours, similar to the reports produced by the MONREPORT module. The reports are useful for analyzing various metrics, and highlighting statistical outliers, from previous time periods.

The report.py script itself is located in the 4_report/ sub-folder:

Pre-requisites:

• The scripts can be deployed in any Unix or Windows environment where Db2 is supported.
• Python2 or higher.
• The python pandas, argparse, and numpy modules are also required to execute the report.py script.

Using pip to install the pandas, argparse, and numpy modules:

Types of reports:

The report.py script is currently capable of generating three unique types of reports:

 

A report will display various metrics and data points for every data collection time period (or limited only to the time periods specified in the arguments), as described later.

Historical data which is used to generate the report:

The report.py script will scrape historical monitoring data that was previously collected by the Db2 Historical Monitoring (db2histmon) framework. 
For reference, I'm including a screen-shot showing the contents of the db2histmon data collection folder:

Usage and options:

The report.py script has the following usage options:

Use the --report option to specify the type of report to generate, either DBSUMMARY, CONNECTION, or PKGCACHE.
The last argument specifies the folder path where the db2histmon historical monitoring data resides.
I'll explain the other options within the examples further below.

Example 1. Generate a DBSUMMARY report (on all available historical monitoring data)

        

Example 2. Generate a report on a subset of time-intervals, by using the START_TIME and END_TIME options

 

In some cases, the quantity of db2histmon historical monitoring data collections available or archived is very large and may make the reports too large or unreadable.
We can use the --START_TIME and --END_TIME options to limit the data collection time-intervals that we wish to generate a report on.  

In this example, we wish to include all data collections that were generated after 2020-09-29 02:20 but before 2020-09-29 04:30:

        

Example 3. Generate a report with time-intervals condensed into larger time PERIODs.

 

We can also use the --PERIOD option to combine time-intervals from the data collection set into larger sets, such that fewer (but longer duration) time-intervals are displayed in the report.

In this example, many of our original db2histmon historical monitoring data collections are approximately 5minutes apart (as an be seen in the previous report's example above). By specifying the --PERIOD 3 option, we aggregate the data which would normally be reported in three separate time-periods into one time-period: 

Example 4. Generate a report showing MIN, MAX, MEAN statistical values.

Understanding the statistical values associated with a particular metric can be useful when looking for anomalies in the data set.  In this example, I specify the --stats option to include the statistics for each reported value:

(db2inst1@myhost1) /home/db2inst1/db2histmon-master/4_report/
$ python3 report.py -r CONNECTION --period 3 /path/to/db2histmon/data_files/ --stats

Stay tuned for new blogs expanding on the Db2 Historical Monitoring (db2histmon) suite, coming soon.

In a future blog I hope to showcase some examples of how the diagnostic data can be analyzed to narrow down a specific problem type. In the mean time, you may find the following article by Ani Kiourktchian about analyzing the older Persistent Diagnostic Data useful, as many of the data points are the same: https://www.idug.org/communities/community-home/librarydocuments/viewdocument?documentkey=f9abdf15-69d2-4f56-bb01-ec3735da1a9d.