Managing diagnostic data from user interface

The Query history monitoring and management page in IBM® watsonx.data provides information for fetching the history data and analyzing the queries that are run. You can retrieve the history files to analyze, debug or monitor the queries from the Query workspace page.

watsonx.data on IBM Software Hub

Procedure

Add storage to store the QHMM data.
Add a storage to store the QHMM data. Supported storage options are, IBM Cloud Object Storage, Amazon S3, or MinIO. To create the storage, see Adding a storage-catalog pair..
Important: QHMM supports only the Apache Hive catalog.
Associate the storage with a query engine (Presto (Java) or Presto (C++)). For details, see Associating a catalog with an engine.
Configure Query monitoring. For details, see Configuring Query monitoring.
1. Enable QHMM. For details, see Configuring Query monitoring.
2. Select the storage bucket to store QHMM data. To do that, see Configuring Query monitoring. From the Infrastructure manager , select the storage bucket (for QHMM data) . An information message is displayed on the top of the page "This bucket cannot be deleted or disabled because it is being used for storing query history data."
3. Click Save to save the details. The queries history and query event-related information of the Presto engine is available in the storage bucket.
Retrieve query information.
1. From the navigation menu, select Query workspace.
2. Select the engine from Engine list and identify the catalog that you created to store the QHMM data.
3. Run queries to analyze the data from the tables within the catalog.
  Examples:
  Raw query event table - Stores raw query event data.
```
SELECT * FROM <catalog>.<schema>.query_event_raw;
```
  Query history table - Stores query history data.
```
SELECT * FROM <catalog>.<schema>.query_history;
```
  Query history view - Extracts relevant information from query history data.
```
SELECT * FROM <catalog>.<schema>.query_history_view;
```
  Query optimizer event view - Extracts relevant information from query optimizer event data.
```
SELECT * FROM <catalog>.<schema>.query_optimizer_event_view;
```
  Query event view - Extracts relevant information from raw query event data.
```
SELECT * FROM <catalog>.<schema>.query_event_view;
```
  Query completed events view - Collects all the query completed events from query events.
```
SELECT * FROM <catalog>.<schema>.query_completed_event_view;
```
  Full Garbage Collection (GC) task details view - Aggregates full GC task statistics for query events.
```
SELECT * FROM <catalog>.<schema>.fullGC_TaskDetails;
```
  Table stats information memory view - Analyzes table statistics and memory usage.
```
SELECT * FROM <catalog>.<schema>.table_stats_information_memory;
```
Deleting QHMM schemas and tables
Administrators can permanently delete the QHMM schemas and tables present in the QHMM enabled catalog by using the following CURL command :
curl --location --request DELETE '<instance_url>/lakehouse/api/v3/configuration/qhmm/table?catalog_name=<qhmm_catalog>&schema_id=<qhmm_schema>&engine_id=<qhmm_engine>`
Important: The QHMM enabled catalog must be associated to a Presto engine. If the engine is deleted or disassociated, then the catalog must be associated to next available Presto engine.
Parameter values:
<instance_url> : The watsonx.data access URL.

<qhmm_engine> : The unique ID of the engine for which the schema and table information needs to be removed.

<qhmm_catalog> : The QHMM catalog for which the schema and table information needs to be removed.

<qhmm_schema> : The unique ID of the schema for which the table information needs to be removed.