In this article, I would like to give you an overview of watonx.data QHMM, explanation of the wonderful tables and views that QHMM provides, and how to change QHMM configuration. 

QHMM Overview

Query History Monitoring and Management (QHMM) is a service that stores and manages the diagnostic data such as queries history and query event-related information of the Presto(Java) and Presto(C++) engine in a Hive storage bucket.
QHMM is primarily aimed at solving the problem of data persistence of diagnostic data. Diagnostic data for query contains information about currently and recently running queries on the Presto cluster. It means such data is not available after a while. If the engine is restarted or goes offline, valuable diagnostic data can be lost. QHMM solves this problem by storing such data in storage.

You can retrieve the stored history files for analysis, debugging and monitoring purposes.

In watsonx.data, when you create a Presto engine, QHMM is automatically available, and the diagnostic data is stored in a storage called wxd-system. watsonx.data official document "QHMM overview - Data organization " shows the structure that QHMM store data in detail.

Using stored diagnostic data, QHMM provides tables and views for analysis, debugging and monitoring purpose. You can check the tables and views provided by QHMM in the Data manager.
You will find wxd_system_data catalog. When you expand it, you find a schema is created for each Presto engine.

• For C++, wxd_system_data_diag_prestissimoXXX
• For Java, wxd_system_data_diag_prestoXXX

8 tables and views are created for each schema and can be accessed with SQL. Because the official documentation has little explanation of the tables and views provided by QHMM, I will explain them based on my experience.

Next diagram illustrates

• the relationship between source data and QHMM tables and views
• how to access QHMM data

Top of the diagram, Information generated by the Presto engine shows the source data of QHMM. 

• system.runtime.queries : See also my blog   Presto: Observation "system.runtime" schema provided by System Connecter which shows system.runtime.queries table structure and example output.
• JSON-formatted query event related data : See also my blog Presto Console - QUERY DETAILS. 

Middle of the diagram show the relationship of tables and views provided by QHMM.

• Left side of the diagram.
  • The SELECT result of system.runtime.queries is stored in Storage, and  query_history table is provided.
  • query_history_view is a view that converts the date and time column of the query_history to the timestamp type.　 

• Right side, JSON-formatted query event related data are also stored in Storage.　
  • query_event_raw table is a table based on Query event related data in JSON format stored in storage.
  • query_event_view : Since the JSON for each query event-related information is too complex and the SQL to extract the required values is also complicated, a tabular query_event_view is provided.
  • query_completed_event_view  : Only 'FINISHED' and 'FAILED' are extracted from query_event_view. 
    • query_completed_event_view  is my recommended view. See my blog "watsonx.data QHMM query_completed_event_view"  to check how valuable data we can refer using it. 
  • table_stats_information_memory provides table statistics and memory-related information. (As of Aug 2025, you may see some cast error and it will be fixed soon. Wait a new release-!)
  • fullgc_taskdetails provides GC information for each query.　
  • query_optimizer_event_view is an extract of query optimizer events supported by Presto (C++).

Each table and view can be accessed with SQL Query, but "ibm-lh monitor qhmm", which is included in the ibm-lh client tool, has multiple templates that can be easily retrieved without executing SQL.
Since the ibm-lh client will be removed in 2.2.2, watsonx.data 2.2 will include qhmm-related functionality in cpdctl wx-data.

On watsonx.data 2.1.3, data is retrieved from each Presto engine and stored every 15 min. I observed 2 procedures are called every 15 min. 

• (*1) CALL system.sync_partition_metadata('wxd_system_data_diag_prestXXXXX', 'query_history', 'FULL') 
• (*2) CALL system.sync_partition_metadata('wxd_system_data_diag_prestXXXXX', 'query_event_raw', 'FULL') 

QHMM configuration change

You can change QHMM configuration using watsonx.data console.

• Reference : watsonx.data 2.2 Configuring Query monitoring

Using navigation bar on the left of watsonx.data console select "Configurations" and click " Query monitoring" tile. Then you can see the current configuration of QHMM (Left image). Click "Edit" , you can change QHMM configuration.

Conclusion　

Here I introduced the overview of QHMM and its configuration change.

My next post "watsonx.data QHMM query_completed_event_view" will show you how valuable data we can refer. 

Environment

The example in this topic introduced is run in the following environment.

• OCP Version : 4.16
• CP4D 5.1.3 , watsonx.data 2.1.3
• Presto engines
  • P01(Presto Java) Type : Presto (Java) v0.286 / Size : Starter
  • PCpp (Presto(C++)) Type : Presto (C++) v0.286 / Size : Starter

#watsonx.data

#PrestoEngine