Retrieving QHMM logs by using ibm-lh utility

The ibm-lh utility allows you to retrieve the QHMM logs (provide insights into query executions, enabling users to monitor and analyze performance metrics) without running SQL queries. It can be used to easily retrieve the following query information with the help of keywords.

Basic query information
Basic error information of failed queries
Query stats information
Query memory information
Query garbage collection information
Top time taken query

Procedure

Install ibm-lh client package. For information, see Installing ibm-lh-client package.
Use the cert-mgmt utility to add or remove SSL certificates in the ibm-lh-client truststore to establish connection with the Presto engine.
```
./cert-mgmt --op=add --host=<saas_host> --port=<port>
```

Establish connection to watsonx.data using ibm-lh-client package utilities.

./connect-lh --op=add --name=<your_saas_name> --host=<saas_host> --port=<port> --username=<your_username> --password=<your_password>

For more information, see Establishing cconnection.

Configure Presto details by using the following command:

./ibm-lh config add_saas --name <your_saas_username> --crn <your_crn> --host <saas_host> --port <port> --prestohost <presto_host> --prestoport <presto_port> --username <your_username> --password <your_password>

Select the current instance.

./ibm-lh config select --current_instance <your_saas_username>

Use the ibm-lh monitor qhmm utility command to retrieve the QHMM logs. For more information about the syntax and the different keywords, see ibm-lh monitor qhmm.

The syntax is ./ibm-lh monitor qhmm --type <type_name> --start-time <start_time> --end-time <end_time> --user <user_id> --query-id <query_id> --query-state <query_state>

Parameters:

--type <type_name>: Specifies the type of query (e.g., query_failed_info, query_basic_info, query_stats_info, query_memory_info, query_gc_info).
--start-time <start_time>: Starting timestamp in YYYY-MM-DD HH:MM:SS (UTC).
--end-time <end_time>: Ending timestamp (optional).
--user <user_id>: User ID associated with the queries (optional).
--query-id <query_id>: Specific query ID (optional).
--query-state <query_state>: State of the query (e.g., COMPLETED, FAILED) (optional).
--limit <limit_value>: Number of rows to return (optional).
--order: Name of columns for sorting (optional).

Sample query to retrieve basic information about all queries executed:

./ibm-lh monitor qhmm --type query_basic_info --start-time "2023-10-01 00:00:00" --end-time "2023-10-08 23:59:59" --user "user123"

This command fetches basic information for queries executed by user123 between October 1st and October 8th, 2023.