sp_iqshowpsexe Procedure

Displays information about the settings of database options that control the priority of tasks and resource usage for connections.

Syntax

sp_iqshowpsexe [ connection-id ]

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Usage

Parameters
Parameter	Description
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.

Description

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.

sp_iqshowpsexe columns
Column name	Description
connectionid	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.

For information about the database options referenced in the columns, see Reference: Statements and Options.

Note: The AppInfo property may not be available from Open Client or jConnect applications such as Interactive SQL or Sybase Central. If the AppInfo property is not available, the application column is blank.

Example

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\\
                IQ-15_4\\bin32\\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