ASA Database Administration Guide
Database Performance and Connection Properties
Database properties
The following table lists properties available for each connection.
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.
| 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 | AppInfo connection parameter [APP] |
| 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] |
| 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 | Turns on debugging for remote data access. |
| Cis_rowset_size | Reserved |
| ClientLibrary | jConnect for jConnect connections; CT_Library for Open Client connections; CmdSeq for ODBC and Embedded SQL connections; |
| 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 is 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 CmdSeq for Adaptive Server Anywhere protocol (ODBC and Embedded SQL) or TDS for Open Client and jConnect 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. |
| 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. |
| Extended_join_syntax | EXTENDED_JOIN_SYNTAX option [database] |
| Fire_triggers | FIRE_TRIGGERS option [compatibility] |
| Float_as_double | FLOAT_AS_DOUBLE option [compatibility] |
| FullCompare | The number of comparisons that have been performed beyond the hash value in an index. |
| 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. |
| 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. |
| 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] |
| 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_statement_count | MAX_STATEMENT_COUNT option [database] |
| Min_password_length | MIN_PASSWORD_LENGTH option [database] |
| Min_table_size_for_histogram | MIN_TABLE_SIZE_FOR_HISTOGRAM 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. |
| Non_keywords | NON_KEYWORDS option [compatibility] |
| Number | The ID number of the connection. |
| On_tsql_error | ON_TSQL_ERROR option [compatibility] |
| Optimization_goal | OPTIMIZATION_GOAL option [database] |
| Optimization_level | Reserved |
| 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.) |
| PacketSize | The packet size used by the connection, in bytes. |
| Percent_as_comment | PERCENT_AS_COMMENT option [compatibility] |
| 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. |
| Query_plan_on_open | QUERY_PLAN_ON_OPEN option [compatibility] |
| QueryBypassed | The number of requests optimized by the optimizer bypass. |
| QueryCachePages | The number of pages used to cache execution plans. |
| QueryCachedPlans | The number of query execution plans currently cached for the connection. |
| 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] |
| Recovery_time | RECOVERY_TIME option [database] |
| Replicate_all | REPLICATE_ALL option [replication] |
| ReqType | A string for the type of the last request. |
| RI_trigger_time | RI_TRIGGER_TIME option [compatibility] |
| Rlbk | The number of Rollback requests that have been handled. |
| RollbackLogPages | The number of pages in the rollback log. |
| Row_counts | ROW_COUNTS option [database] |
| Scale | SCALE 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] |
| Suppress_TDS_debugging | SUPPRESS_TDS_DEBUGGING option [database] |
| TDS_empty_string_is_null | TDS_EMPTY_STRING_IS_NULL 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. |
| 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] |
| 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] |