sa_conn_info system procedure

sa_conn_info( [ connidparm ] )

connidparm This optional INTEGER parameter specifies the connection ID number.

Column name	Data type	Description
Number	INTEGER	Renvoie l'identificateur de la connexion.
Name	VARCHAR(255)	Renvoie l'identificateur de la connexion. Temporary connection names have INT: prepended to the connection name.
Userid	VARCHAR(255)	Renvoie l'ID utilisateur de la connexion.
DBNumber	INTEGER	Renvoie l'identificateur de la base de données.
LastReqTime	VARCHAR(255)	Renvoie l'heure de lancement de la dernière demande associée à la connexion spécifiée. Cette propriété peut renvoyer une chaîne vide pour les connexions internes, comme les événements.
ReqType	VARCHAR(255)	Renvoie le type de la dernière demande. Si une connexion se trouve dans le cache à la suite d'une mise en pool, sa valeur ReqType est CONNECT_POOL_CACHE.
CommLink	VARCHAR(255)	Renvoie le lien de communication de la connexion. Il s'agit de l'un des protocoles réseau supportés par SQL Anywhere ou de la valeur "local" pour une communication entre homologues.
NodeAddr	VARCHAR(255)	Returns the address of the client in a client/server connection.
ClientPort	INTEGER	Renvoie le numéro de port TCP/IP du client ou la valeur 0 s'il ne s'agit pas d'une connexion TCP/IP.
ServerPort	INTEGER	Renvoie le numéro de port TCP/IP du serveur de base de données ou la valeur 0.
BlockedOn	INTEGER	Renvoie la valeur zéro si la connexion en cours n'est pas bloquée ou, si elle est bloquée, le numéro de la connexion à l'origine du blocage suite à un conflit de verrouillage.
LockRowID	UNSIGNED BIGINT	Renvoie l'identificateur de la ligne verrouillée. 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).
LockIndexID	INTEGER	Renvoie l'identificateur de l'index verrouillé. LockIndexID is -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).
LockTable	VARCHAR(255)	Returns the name of the table associated with a lock if the connection is currently waiting for a lock. Otherwise, LockTable returns an empty string.
UncommitOps	INTEGER	Returns the number of uncommitted operations.
ParentConnection	INTEGER	Renvoie l'identificateur de la connexion qui a créé une connexion temporaire pour exécuter une opération de base de données (sauvegarde ou création, par exemple). Pour les autres types de connexion, cette propriété renvoie la valeur NULL.

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 a connection is waiting on a lock.

Cloud note: Because of tenant database isolation rules, when this system procedure is run in the cloud it returns only information about the current tenant database.

None

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( );

Number	Name	Userid	DBNumber	...
79		DBA	0	...
46	Sybase Central 1	DBA	0	...
...	...	...	...	...

The following example uses the sa_conn_info system procedure to return a result set showing which connection created a temporary connection.

SELECT Number, Name, ParentConnection FROM sa_conn_info();

Connection 8 created the temporary connection that executed a CREATE DATABASE statement.

Number      Name                ParentConnection
------------------------------------------------
1000000048  INT: CreateDB       8
9           SQL_DBC_14675af8    (NULL)
8           SQL_DBA_152d5ac0    (NULL)

sa_conn_info system procedure

Syntax

Arguments

Result set

Remarks

Permissions

Side effects

See also

Examples