Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » System Procedures » System procedures

sa_conn_compression_info system procedure Next Page

sa_conn_info system procedure


Reports connection property information.

Syntax

sa_conn_info( [ connidparm ] )

Arguments
Result set
Column nameData typeDescription
NumberINTEGERThe ID number of the connection.
NameVARCHAR(255)The name of the connection.
UseridVARCHAR(255)The user ID for the connection.
DBNumberINTEGERThe ID number of the database.
LastReqTimeVARCHAR(255)The time at which the last request for the specified connection started.
ReqTypeVARCHAR(255)A string for the type of the last request.
CommLinkVARCHAR(255)The communication link for the connection. This is one of the network protocols supported by SQL Anywhere, or local for a same-computer connection.
NodeAddrVARCHAR(255)The address of the client in a client/server connection.
ClientPortINTEGERThe port number on which the client application communicates using TCP/IP.
ServerPortINTEGERThe port number on which the server communicates using TCP/IP.
BlockedOnINTEGERIf the current connection is not blocked, this is zero. If it is blocked, the connection number on which the connection is blocked because of a locking conflict.
LockTableVARCHAR(255)If the connection is currently waiting for a lock, LockTable will be the name of the table associated with that lock. Otherwise, LockTable will be the empty string.
UncommitOpsINTEGERThe number of uncommitted operations.
LockRowIDUNSIGNED BIGINTIf the connection is waiting on a lock that is associated with a particular row identifier, LockRowID contains that row identifier. LockRowID is NULL if the connection is not waiting on a lock associated with a row (that is, it is not waiting on a lock, or it is waiting on a lock that has no associated row).
LockIndexIDINTEGER If the connection is waiting on a lock that is associated with a particular index, LockIndexID contains the identifier of that index (or -1 if the lock is associated with all indexes on the table in LockTable). LockIndexID is NULL if the connection is not waiting on a lock associated with an index (that is, it is not waiting on a lock, or it is waiting on a lock that has no associated index).
Remarks

If you specify the connection ID number, the sa_conn_info system procedure returns a result set consisting of connection properties for the supplied connection. If no connidparm is supplied, this system procedure returns information for all current connections to databases on the server. If connidparm is less than zero, option values for the current connection are returned.

In a block situation, the BlockedOn value returned by this procedure allows you to check which users are blocked, and who they are blocked on. The sa_locks system procedure can be used to display the locks held by the blocking connection.

For more information based on any of these properties, you can execute something similar to the following:

SELECT *, DB_NAME( DBNumber ), 
   CONNECTION_PROPERTY( 'LastStatement', Number ) 
   FROM sa_conn_info();

The value of LockRowID can be used to look up a lock in the output of the sa_locks procedure.

The value in LockIndexID can be used to look up a lock in the output of the sa_locks procedure. Also, the value in LockIndexID corresponds to the primary key of the ISYSIDX system table, which can be viewed using the SYSIDX system view.

Every lock has an associated table, so the value of LockTable can be used to unambiguously determine whether or not a connection is waiting on a lock.

Permissions

None

Side effects

None

See also
Example

The following example uses the sa_conn_info system procedure to return a result set summarizing connection properties for all connections to the server.

CALL sa_conn_info( );
NumberNameUseridDBNumber...
79DBA0 ...
46Sybase Central 1DBA0...
...............