sp_iqshowpsexe procedure

Function

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

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.

Table 7-54: 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.

NoteThe 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.

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_1\\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

See also

In Chapter 7, “System Procedures”: “sp_iqconnection procedure”, “sp_iqcontext procedure”, and “sa_conn_info system procedure”

“CONNECTION_PROPERTY function [System]”

In Chapter 2, “Database Options” in Reference: Statements and 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 System Administration Guide: Volume 1