sa_conn_properties system procedure

Reports connection property information.

Syntax

sa_conn_properties( [ connidparm ] )

Parameters

connidparm
Use this optional INTEGER parameter to specify the connection ID number. The default is NULL.

Result set

Column name	Data type	Description
Number	INTEGER	Returns the connection ID (a number) for the current connection.
PropNum	INTEGER	Returns the connection property number.
PropName	VARCHAR(255)	Returns the connection property name.
PropDescription	VARCHAR(255)	Returns the connection property description.
Value	LONG VARCHAR	Returns the connection property value.

Remarks

Returns the connection ID as Number, and the PropNum, PropName, PropDescription, and Value for each available connection property. Values are returned for all connection properties, database option settings related to connections, and statistics related to connections. Valid properties with NULL values are also returned.

If connidparm is less than zero, then property values for the current connection are returned. If connidparm is not supplied or is NULL, then property values are returned for all connections to the current database.

Privileges

You must have EXECUTE privilege on the system procedure.

To obtain a list of all connection IDs, you must also have either the SERVER OPERATOR, MONITOR, or DROP CONNECTION system privilege.

Side effects

None

Example

The following example uses the sa_conn_properties system procedure to return a result set summarizing connection property information for all connections.

CALL sa_conn_properties( );

Number	PropNum	PropName	...
79	37	ClientStmtCacheHits	...
79	38	ClientStmtCacheMisses	...
...	...	...	...

This example uses the sa_conn_properties system procedure to return a list of all connections, in decreasing order by CPU time*:

SELECT Number AS connection_number,
    CONNECTION_PROPERTY ( 'Name', Number ) AS connection_name,
    CONNECTION_PROPERTY ( 'Userid', Number ) AS user_id,
  CAST ( Value AS NUMERIC ( 30, 2 ) ) AS approx_cpu_time
  FROM sa_conn_properties( )
  WHERE PropName = 'ApproximateCPUTime'
  ORDER BY approx_cpu_time DESC;