sp_iqwho procedure

Function

Displays information about all current users and connections, or about a particular user or connection.

Syntax

sp_iqwho [ { connhandle | user-name } [, arg-type ] ]

Permissions

None required.

Description

The sp_iqwho stored procedure displays information about all current users and connections, or about a particular user or connection.

Table 10-51: sp_iqwho columns

Column name

Description

ConnHandle

The SA connection handle

IQConnID

The Sybase IQ specific connection ID

Userid

The name of the user that opened the connection “ConnHandle”

BlockedOn

The connection on which a particular connection is blocked; 0 if not blocked on any connection

BlockUserid

The owner of the blocking connection; NULL if there is no blocking connection

ReqType

The type of the request made through the connection; DO_NOTHING if no command is issued

IQCmdType

The type of Sybase IQ command issued from the connection; NONE if no command is issued

IQIdle

The time in seconds since the last Sybase IQ command was issued through the connection; in case of no last Sybase IQ command, the time since ‘01-01-2000’ is displayed

SAIdle

The time in seconds since the last SA request was issued through the connection; in case of no last SA command, the time since ‘01-01-2000’ is displayed

IQCursors

The number of active cursors in the connection; 0 if no cursors

IQThreads

The number of threads with the connection. At least one thread is started as soon as the connection is opened, so the minimum value for IQThreads is 1.

TmpTblSpaceKB

The size of temporary table space in kilobytes; 0 if no temporary table space is used

TmpWrkSpaceKB

The size of temporary workspace in kilobytes; 0 if no temporary workspace is used

Adaptive Server Enterprise compatibility The Sybase IQ sp_iqwho stored procedure incorporates the Sybase IQ equivalents of columns displayed by the Adaptive Server Enterprise sp_who procedure. Some Adaptive Server Enterprise columns are omitted, as they are not applicable to Sybase IQ. Table 10-52 maps the Adaptive Server Enterprise sp_who columns to the columns displayed by sp_iqwho.

Table 10-52: Mapping of sp_who and sp_iqwho columns

sp_who column

sp_iqwho column

fid

Family to which a lock belongs; omitted, as not applicable to Sybase IQ

spid

ConnHandle, IQConnID

status

IQIdle, SAIdle

loginame

Userid

origname

User alias; omitted, as not applicable to Sybase IQ

hostname

Name of the host on which the server is running; can be confusing in a multiplex environment, so currently is not supported

blk_spid

BlockedOn

dbname

Omitted, as there is one server and one database for Sybase IQ and they are the same for every connection

cmd

ReqType, IQCmdType

block_xloid

BlockUserid

Usage

connhandle An integer representing the connection ID. If this parameter is specified, sp_iqwho returns information only about the specified connection. If the specified connection is not open, no rows are displayed in the output.

user-name A char(255) parameter representing a user login name. If this parameter is specified, sp_iqwho returns information only about the specified user. If the specified user has not opened any connections, no rows are displayed in the output. If the specified user name does not exist in the database, sp_iqwho returns the error message ”User user-name does not exist”

arg-type The arg-type parameter is optional and can be specified only when the first parameter has been specified. The only value for arg-type is “user”. If the arg-type value is specified as “user”, sp_iqwho interprets the first parameter as a user name, even if the first parameter is numeric. If any value other than “user” is specified for arg-type, sp_iqwho returns the error

“Invalid parameter.”

Enclose the arg-type value in double quotes.

If no parameters are specified, sp_iqwho displays information about all currently active connections and users.

Either a connection handle or a user name can be specified as the first sp_iqwho parameter. The parameters connhandle and user-name are exclusive and optional. Only one of these parameters can be specified at a time. By default, if the first parameter is numeric, the parameter is assumed to be a connection handle. If the first parameter is not numeric, it is assumed to be a user name.

Sybase IQ allows numeric user names. The arg-type parameter directs sp_iqwho to interpret a numeric value in the first parameter as a user name. For example:

sp_iqwho 1, “user”

When the arg-type “user” is specified, sp_iqwho interprets the first parameter 1as a user name, not as a connection ID. If a user named 1 exists in the database, sp_iqwho displays information about connections opened by user 1.

Table 10-53: sp_iqwho usage examples

Syntax

Output

sp_iqwho

Displays all active connections

sp_iqwho 3

Displays information about connection 3

sp_iqwho “DBA”

Displays connections opened by user DBA

sp_iqwho 3, “user”

Interprets 3 as a user name and displays connections opened by user 3. If user 3 does not exist, returns the error “User 3 does not exist”

sp_iqwho non-existing-user

Returns error “User non-existing-user does not exist”

sp_iqwho 3, “xyz”

Returns the error “Invalid parameter: xyz”

See also

“sp_iqconnection procedure”

“sa_conn_info system procedure”

Example

Example 14

Display all active connections:

ConnHandle IQConnID Userid ReqType      IQCmdType            BlockedOn BlockUserid IQCursors
IQThreads IQIdle SAIdle TmpTblSpaceKB TmpWrkSpaceKB

12         118      DBA    CURSOR_OPEN  IQUTILITYOPENCURSOR  0         (NULL)      0  
1         1      0      0             0
13         119      shweta DO_NOTHING   NONE                 0         (NULL)      0
1    16238757   470     0             0