Displays information about the settings of database options that control the priority of tasks and resource usage for connections.
sp_iqshowpsexe [ connection-id ]
None required.
connection-id An integer representing the connection ID.
If connection-id is specified, sp_iqshowpsexe returns information only about the specified connection. If connection-id is not specified, sp_iqshowpsexe returns information about all connections.
If the specified connection-id does not exist, sp_iqshowpsexe returns no rows.
In Chapter 10, “System Procedures”: “sp_iqconnection procedure”, “sp_iqcontext procedure”, and “sa_conn_info system procedure”
“CONNECTION_PROPERTY function [System]”
In Chapter 2, “Database Options”: “IQGOVERN_MAX_PRIORITY option”, “IQGOVERN_PRIORITY option”, “IQGOVERN_PRIORITY_TIME option”, “MAX_QUERY_TIME option”, “QUERY_ROWS_RETURNED_LIMIT option”, “QUERY_TEMP_SPACE_LIMIT option”, “MAX_CURSOR_COUNT option”, and “MAX_STATEMENT_COUNT option”
“AppInfo connection parameter [App]” in Chapter 4, “Connection and Communication Parameters” in the Sybase IQ System Administration Guide
The sp_iqshowpsexe stored procedure displays information about the settings of database options that control the priority of tasks and resource usage for connections, which is useful to database administrators for performance tuning.
Column name |
Description |
|---|---|
connection_id |
The connection ID |
application |
Information about the client application that opened the connection. Includes the AppInfo connection property information: HOST: the host name of the client machine EXE: the name of the client executable (Windows only) APPINFO: the APPINFO in the client connection string, if specified |
userid |
Login name of the user that opened the connection |
iqgovern_priority |
Value of the database option IQGOVERN_PRIORITY that assigns a priority to each query waiting in the -iqgovern queue. By default, this option has a value of 2 (MEDIUM). The values 1, 2, and 3 are shown as HIGH, MEDIUM, and LOW, respectively. |
max_query_time |
Value of the database option MAX_QUERY_TIME that sets a limit, so that the optimizer can disallow very long queries. By default, this option is disabled and has a value of 0. |
query_row_limit |
Value if the database option QUERY_ROWS_RETURNED_LIMIT that sets the row threshold for rejecting queries based on the estimated size of the result set. The default is 0, which means there is no limit. |
query_temp_space_limit |
Value of the database option QUERY_TEMP_SPACE_LIMIT (in MB) that constrains the use of temporary IQ dbspace by user queries. The default value is 2000MB. |
max_cursors |
Value of the database option MAX_CURSOR_COUNT that specifies a resource governor to limit the maximum number of cursors a connection can use at once. The default value is 50. A value of 0 implies no limit. |
max_statements |
Value of the database option MAX_STATEMENT_COUNT that specifies a resource governor to limit the maximum number of prepared statements that a connection can use at once. The default value is 100. A value of 0 implies no limit. |
The AppInfo property may not be available
from Open Client or jConnect applications such as the Java version
of Interactive SQL (dbisql) or Sybase Central.
If the AppInfo property is not available, the application column
is blank.
Display information about the settings of database options that control the priority of tasks and resource usage for connection ID 2:
sp_iqshowpsexe 2 connectionid application 2 HOST=GOODGUY-XP;EXE=C:\\Program Files\\Sybase\\ ASIQ-12_7\\win32\\dbisqlg.exe; userid iqgovern_priority max_query_time query_row_limit DBA MEDIUM 0 0 query_temp_space_limit max_statements max_cursors 2000 50 100
