Accessing Netezza Query History Details Using nz_query_history Table

Prerequisites

• Access to a Netezza database and appropriate privileges to query system tables.
• A basic understanding of SQL.

Accessing Query History Details

1. Connect to your Netezza database using a tool that supports running SQL queries, such as pgAdmin, Navicat for Netezza, or sqlcli.
2. Execute the following query to retrieve query history details from the nz_query_history table:

SELECT * FROM nz_query_history WHERE job_id = 'your_job_id';

Replace 'your_job_id' with the actual job ID for the query you want to inspect. If you are interested in all queries executed within a specific timeframe, replace 'your_job_id' with the appropriate conditions (e.g., WHERE start_time >= 'start_date' AND start_time <= 'end_date';).

Example Query Output

Columns Description

• job_id: Unique identifier for each query.
• start_time: Time when the query was started.
• end_time: Time when the query ended (or the current time if it is still running).
• user_name: User who executed the query.
• application_name: Application that was used to execute the query.
• num_rows_affected: Number of rows affected by the query.
• num_bytes_read: Number of bytes read from the database.
• num_bytes_written: Number of bytes written to the database.
• num_bytes_spilled: Number of bytes spilled to disk due to memory pressure.
• num_bytes_cached: Number of bytes cached for future use.
• status: Status of the query execution (e.g., success or error).

Conclusion

By leveraging the nz_query_history table, you can effectively monitor query performance, identify potential issues, and optimize your Netezza database workloads more efficiently. Analyzing the data returned from this system catalog view allows you to make informed decisions to improve overall system performance and optimize your queries for better results.