iAnywhere.com - Connection-level properties

Connection-level properties

The following table lists properties available for each connection.

Examples

To retrieve the value of a connection property

Use the connection_property system function. The following statement returns the number of pages that have been read from file by the current connection.
```
SELECT connection_property ( 'DiskRead' )
```

To retrieve the values of all connection properties

Use the sa_conn_properties system procedure:
```
CALL sa_conn_properties
```
A separate row appears for each connection.

Descriptions

Property	Description
Allow_nulls_by_default	ALLOW_NULLS_BY_DEFAULT option [compatibility]
Ansi_blanks	ANSI_BLANKS option [compatibility]
Ansi_close_cursors_on_rollback	ANSI_CLOSE_CURSORS_ON_ROLLBACK option [compatibility]
Ansi_integer_overflow	ANSI_INTEGER_OVERFLOW option [compatibility]
Ansi_permissions	ANSI_PERMISSIONS option [compatibility]
Ansi_update_constraints	ANSI_UPDATE_CONSTRAINTS option [compatibility]
Ansinull	ANSINULL option [compatibility]
AppInfo	Returns information about the client that made the connection. For HTTP connections, this includes information about the browser. For connections using older versions of jConnect or Open Client, the information may be incomplete. The API value can be DBLIB, ODBC, OLEDB, or ADO.NET. For more information about the values returned for other types of connections, see AppInfo connection parameter [APP].
Auditing	AUDITING option [database]
AuditingTypes	The types of auditing currently enabled. AUDITING option [database]
Automatic_timestamp	AUTOMATIC_TIMESTAMP option [compatibility]
Background_priority	BACKGROUND_PRIORITY option [database]
BlockedOn	If the current connection is not blocked, this is zero. If it is blocked, the connection number on which the connection is blocked due to a locking conflict.
Blocking	BLOCKING option [database]
Blocking_timeout	BLOCKING_TIMEOUT option [database]
BytesReceived	The number of bytes received during client/server communications.
BytesReceivedUncomp	The number of bytes that would have been received during client/server communications if compression was disabled. (This value is the same as the value for BytesReceived if compression is disabled.)
BytesSent	The number of bytes sent during client/server communications.
BytesSentUncomp	The number of bytes that would have been sent during client/server communications if compression was disabled. (This value is the same as the value for BytesSent if compression is disabled.)
CacheHits	The number of successful reads of the cache.
CacheRead	The number of database pages that have been looked up in the cache.
CacheReadIndInt	The number of index internal-node pages that have been read from the cache.
CacheReadIndLeaf	The number of index leaf pages that have been read from the cache.
CacheReadTable	The number of table pages that have been read from the cache.
Chained	CHAINED option [compatibility]
CharSet	The character set used by the connection.
Checkpoint_time	CHECKPOINT_TIME option [database]
Cis_option	CIS_OPTION option [database]
Cis_rowset_size	Reserved
ClientLibrary	ReturnsjConnect for jConnect connections; CT_Library for Open Client connections; None for HTTP connections, and CmdSeq for ODBC, embedded SQL, OLE DB, ADO.NET, and iAnywhere JDBC driver connections.
ClientPort	Returns the client's TCP/IP port number or 0 if the connection is not a TCP/IP connection.
Close_on_EndTrans	CLOSE_ON_ENDTRANS option [compatibility]
Commit	The number of Commit requests that have been handled.
CommLink	The communication link for the connection. This is one of the network protocols supported by Adaptive Server Anywhere, or local for a same-machine connection.
CommNetworkLink	The communication link for the connection. This is one of the network protocols supported by Adaptive Server Anywhere. Values can include SharedMemory, TCPIP, SPX, or NamedPipes. The CommLinkNetwork property always returns the name of the link, regardless of whether it is same-machine or not.
CommProtocol	Returns TDS for Open Client and jConnect connections, HTTP for HTTP connections, and CmdSeq for OLE DB, ADO.NET and iAnywhere JDBC driver connections.
Compression	Returns ON or OFF to indicate whether communication compression is enabled on the connection.
Connection_authentication	A string used to authenticate the client. Authentication is required before the database can be modified.
Conversion_error	CONVERSION_ERROR option [compatibility]
Cooperative_commit_timeout	COOPERATIVE_COMMIT_TIMEOUT option [database]
Cooperative_commits	COOPERATIVE_COMMITS option [database]
Cursor	The number of declared cursors that are currently being maintained by the server.
CursorOpen	The number of open cursors that are currently being maintained by the server.
Database_authentication	A string used to authenticate the database. Authentication is required before the database can be modified.
Date_format	DATE_FORMAT option [compatibility]
Date_order	DATE_ORDER option [compatibility]
DBNumber	The ID number of the database.
Debug_messages	DEBUG_MESSAGES option [database]
Dedicated_task	DEDICATED_TASK option [database]
Default_timestamp_increment	DEFAULT_TIMESTAMP_INCREMENT option [database]
Delayed_commit_timeout	DELAYED_COMMIT_TIMEOUT option [database]
Delayed_commits	DELAYED_COMMITS option [database]
DiskRead	The number of pages that have been read from disk.
DiskReadIndInt	The number of index internal-node pages that have been read from disk.
DiskReadIndLeaf	The number of index leaf pages that have been read from disk.
DiskReadTable	The number of table pages that have been read from disk.
DiskWrite	The number of modified pages that have been written to disk.
Divide_by_zero_error	DIVIDE_BY_ZERO_ERROR option [compatibility]
Encryption	Encryption connection parameter [ENC]
Escape_character	ESCAPE_CHARACTER option [compatibility]
EventName	The name of the associated event if the connection is running an event handler. Otherwise, the result is NULL.
Exclude_operators	EXCLUDE_OPERATORS option [database]
Extended_join_syntax	EXTENDED_JOIN_SYNTAX option [database]
Fire_triggers	FIRE_TRIGGERS option [compatibility]
First_day_of_week	FIRST_DAY_OF_WEEK option [database]
Float_as_double	FLOAT_AS_DOUBLE option [compatibility]
For_xml_null_treatment	FOR_XML_NULL_TREATMENT option [database]
Force_view_creation	FORCE_VIEW_CREATION option [database]
FullCompare	The number of comparisons that have been performed beyond the hash value in an index.
Global_database_id	GLOBAL_DATABASE_ID option [database]
IdleTimeout	The idle timeout value of the connection. For more information, see Idle connection parameter [IDLE].
IndAdd	The number of entries that have been added to indexes.
IndLookup	The number of entries that have been looked up in indexes.
Integrated_server_name	INTEGRATED_SERVER_NAME option [database]
Isolation_level	ISOLATION_LEVEL option [compatibility]
Java_heap_size	JAVA_HEAP_SIZE option [database]
Java_input_output	JAVA_INPUT_OUTPUT option [database]
Java_namespace_size	JAVA_NAMESPACE_SIZE option [database]
Java_page_buffer_size	The page buffer size used by the Java VM.
JavaHeapSize	The heap size per Java VM.
Language	The locale language.
LastIdle	The number of ticks between requests.
LastReqTime	The time at which the last request for the specified connection started.
LastStatement	The most recently prepared SQL statement for the current connection. For more information, see -zl server option.
LivenessTimeout	The liveness timeout period for the current connection. For more information, see LivenessTimeout connection parameter [LTO].
Lock_rejected_rows	Reserved
LockName	A 64-bit unsigned integer value representing the lock for which a connection is waiting.
Log_deadlocks	LOG_DEADLOCKS option [database]
LogFreeCommit	The number of Redo Free Commits. A Redo Free Commit occurs when a commit of the transaction log is requested but the log has already been written (so the commit was done for free.)
Login_mode	LOGIN_MODE option [database]
Login_procedure	LOGIN_PROCEDURE option [database]
LoginTime	The date and time the connection was established.
LogWrite	The number of pages that have been written to the transaction log.
Max_cursor_count	MAX_CURSOR_COUNT option [database]
Max_plans_cached	MAX_PLANS_CACHED option [database]
Max_recursive_iterations	MAX_RECURSIVE_ITERATIONS option [database]
Max_statement_count	MAX_STATEMENT_COUNT option [database]
MessageReceived	The string that was generated by the MESSAGE statement that caused the WAITFOR statement to be interrupted. Otherwise, an empty string is returned.
Min_password_length	MIN_PASSWORD_LENGTH option [database]
Name	The name of the current connection.
Nearest_century	NEAREST_CENTURY option [compatibility]
NodeAddress	The node for the client in a client/server connection. When the client and server are both on the same machine, an empty string is returned.
Non_keywords	NON_KEYWORDS option [compatibility]
Number	The ID number of the connection.
ODBC_describe_binary_as_varbinary	ODBC_DESCRIBE_BINARY_AS_VARBINARY [database]
ODBC_distinguish_char_and_varchar	ODBC_DISTINGUISH_CHAR_AND_VARCHAR option [database]
On_charset_conversion_failure	ON_CHARSET_CONVERSION_FAILURE option [database]
On_tsql_error	ON_TSQL_ERROR option [compatibility]
Optimistic_wait_for_commit	OPTIMISTIC_WAIT_FOR_COMMIT option [compatibility]
Optimization_goal	OPTIMIZATION_GOAL option [database]
Optimization_level	Reserved
Optimization_workload	OPTIMIZATION_WORKLOAD option [database]
PacketSize	The packet size used by the connection, in bytes.
PacketsReceived	The number of client/server communication packets received.
PacketsReceivedUncomp	The number of packets that would have been received during client/server communications if compression was disabled. (This value is the same as the value for PacketsReceived if compression is disabled.)
PacketsSent	The number of client/server communication packets sent.
PacketsSentUncomp	The number of packets that would have been sent during client/server communications if compression was disabled. (This value is the same as the value for PacketsSent if compression is disabled.)
Percent_as_comment	PERCENT_AS_COMMENT option [compatibility]
Pinned_cursor_percent_of_cache	PINNED_CURSOR_PERCENT_OF_CACHE option [database]
Precision	PRECISION option [database]
Prefetch	PREFETCH option [database]
Prepares	The number of statement preparations carried out.
PrepStmt	The number of prepared statements currently being maintained by the server.
Preserve_source_format	PRESERVE_SOURCE_FORMAT option [database]
Prevent_article_pkey_update	PREVENT_ARTICLE_PKEY_UPDATE option [database]
Query_plan_on_open	QUERY_PLAN_ON_OPEN option [compatibility]
QueryBypassed	The number of requests optimized by the optimizer bypass.
QueryCachedPlans	The number of query execution plans currently cached for the connection.
QueryCachePages	The number of pages used to cache execution plans.
QueryLowMemoryStrategy	The number of times the server changed its execution plan during execution as a result of low memory conditions. The strategy can change because less memory is available than the optimizer estimated, or because the execution plan required more memory than the optimizer estimated.
QueryOptimized	The number of requests that have been fully optimized.
QueryReused	The number of requests that have been reused from the plan cache.
Quoted_identifier	QUOTED_IDENTIFIER option [compatibility]
Read_past_deleted	READ_PAST_DELETED option [database]
Recovery_time	RECOVERY_TIME option [database]
Remote_idle_timeout	REMOTE_IDLE_TIMEOUT option [database]
Replicate_all	REPLICATE_ALL option [replication]
ReqType	A string for the type of the last request.
Return_date_time_as_string	RETURN_DATE_TIME_AS_STRING option [database]
RI_trigger_time	RI_TRIGGER_TIME option [compatibility]
Rlbk	The number of Rollback requests that have been handled.
Rollback_on_deadlock	ROLLBACK_ON_DEADLOCK [database]
RollbackLogPages	The number of pages in the rollback log.
Row_counts	ROW_COUNTS option [database]
Scale	SCALE option [database]
ServerPort	Returns the server's TCP/IP port number or 0.
Sort_collation	SORT_COLLATION option [database]
SQL_flagger_error_level	SQL_FLAGGER_ERROR_LEVEL option [compatibility]
SQL_flagger_warning_level	SQL_FLAGGER_WARNING_LEVEL option [compatibility]
String_rtruncation	STRING_RTRUNCATION option [compatibility]
Subsume_row_locks	SUBSUME_ROW_LOCKS option [database]
Suppress_TDS_debugging	SUPPRESS_TDS_DEBUGGING option [database]
TDS_empty_string_is_null	TDS_EMPTY_STRING_IS_NULL option [database]
Temp_space_limit_check	TEMP_SPACE_LIMIT_CHECK option [database]
TempTablePages	The number of pages in the temporary file used for temporary tables.
Time_format	TIME_FORMAT option [compatibility]
Timestamp_format	TIMESTAMP_FORMAT option [compatibility]
TimeZoneAdjustment	The number of minutes that must be added to the Coordinated Universal Time (UTC) to display time local to the connection. By default, the value is set according to the client's time zone.
TransactionStartTime	A string containing the time the database was first modified after a COMMIT or ROLLBACK, or an empty string if no modifications have been made to the database since the last COMMIT or ROLLBACK.
Truncate_date_values	TRUNCATE_DATE_VALUES option [database] (deprecated)
Truncate_timestamp_values	TRUNCATE_TIMESTAMP_VALUES option [database]
Truncate_with_autocommit	TRUNCATE_WITH_AUTO_COMMIT option [database]
Tsql_hex_constant	TSQL_HEX_CONSTANT option [compatibility]
Tsql_variables	TSQL_VARIABLES option [compatibility]
UncommitOp	The number of uncommitted operations.
User_estimates	USER_ESTIMATES option [database]
UserAppInfo	The string specified by the AppInfo connection parameter in a connection string. For more information, see AppInfo connection parameter [APP].
Userid	The user ID for the connection.
UtilCmdsPermitted	Returns ON or OFF to indicate whether utility commands such as CREATE DATABASE, DROP DATABASE, and RESTORE DATABASE are permitted for the connection. For more information, see -gu server option.
Wait_for_commit	WAIT_FOR_COMMIT option [database]