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.

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.