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.
  • Memory usage details of queries.
  • Information after joining the two tables.
  • Information containing all the columns of a table.
  • Information about the errors in the query.
  • Count of all error codes.
  • Count of all failure messages.
  • Count of all failure types.

watsonx.data on IBM Software Hub

Procedure

  1. Install ibm-lh client package. For information, see Installing ibm-lh-client package.
  2. Use the cert-mgmt utility to add or remove SSL certificates in the ibm-lh-client truststore to establish connection with the Presto engine. For more information, see cert-mgmt.
  3. Establish connection to watsonx.data using ibm-lh-client package utilities. 
    ./connect-lh --op=add --name=<your_cpd_name> --host=<cpd_host> --port=<port> --username=<your_username> --password=<your_password>
    For more information, see Establishing connection.
  4. Configure Presto details by using the following command: 
    ./ibm-lh config add_cpd \
--name <your_wxd_name> \
--host <wxd_host> --port <port> \
--prestohost <presto_host> --prestoport <presto_port> --username <your_username> --password <your_password>
  5. Select the current instance. 
    ./ibm-lh config select --current_instance <your_wxd_name>
  6. 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.