Query status and history

You can use the system views, _v_qrystat and _v_qryhist, to view the status of queries that are running and the recent query history.

Note: This version of the query history and status feature is provided for compatibility with earlier releases and will be deprecated in a future release.

You do not need to be the administrator to view these views, but you must have been granted List permission for the users who ran the queries and the database objects to see the history records.

For example, to grant admin1 permission to view bob’s queries on database emp, use the following SQL commands:

GRANT LIST ON bob TO admin1; 
GRANT LIST ON emp TO admin1;

You can also use the nzstats command to view the Query Table and Query History Table. For more information, see Table 1 and Table 1.

The following table lists the _v_qrystat view, which lists active queries.

Table 1. The _v_qrystat view
Columns	Description
Session Id	The ID of the session that initiated this query.
Plan Id	The internal ID of the plan that is associated with this query.
Client Id	The internal client ID associated with this query.
Client IP address	The client IP address.
SQL statement	The SQL statement. (Note that the statement is not truncated as it is when you view a statement using the nzstats command.)
State	The state number.
Submit date	The date and time the query was submitted.
Start date	The date and time the query started running.
Priority	The priority number.
Priority text	The priority of the queue when submitted (normal or high).
Estimated cost	The estimated cost, as determined by the optimizer. The units are thousandths of a second, that is, 1000 equals one second.
Estimated disk	The estimated disk usage, as determined by the optimizer.
Estimated mem	The estimated memory usage, as determined by the optimizer.
Snippets	The number of snippets in the plan for this query.
Current Snippet	The current snippet the system is processing.
Result rows	The number of rows in the result.
Result bytes	The number of bytes in the result.

The following table describes the _v_qryhist view, which lists recent queries.

Table 2. The _v_qryhist view
Columns	Description
Session Id	The ID of the session that initiated this query.
Plan Id	The internal ID of the plan that is associated with this query.
Client Id	The internal client ID associated with this query.
Client IP address	The client IP address.
DB name	The name of the database the query ran on.
User	The user name.
SQL statement	The SQL statement. (Note that the statement is not truncated as it is when you view a statement using the nzstats command.)
Submit date	The date and time the query was submitted.
Start date	The date and time the query started running.
End date	The date and time that the query ended.
Priority	The priority number.
Priority text	The priority of the queue when submitted (normal or high).
Estimated cost	The estimated cost, as determined by the optimizer.
Estimated disk	The estimated disk usage, as determined by the optimizer.
Estimated mem	The estimated memory usage, as determined by the optimizer.
Snippets	The number of snippets in the plan for this query.
Snippet done	The number of snippets that have completed.
Result rows	The number of rows in the result.
Result bytes	The number of bytes in the result.