Reference Manual
   APPENDIX A. Database Performance and Connection Properties     

Connection properties

The following table lists properties available for each connection.

Examples 

  To retrieve the value of a connection property:

1. 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:

1. Use the sa_conn_properties system procedure:

   call sa_conn_properties

   A separate row is displayed for each connection.

Descriptions 

Property	Description
AllowNullsByDefault	ALLOW_NULLS_BY_DEFAULT option
AnsiBlanks	ANSI_BLANKS option
AnsiCloseCursorsOnRollBack	ANSI_CLOSE_CURSORS_ON_ROLLBACK option
AnsiIntegerOverflow	ANSI_INTEGER_OVERFLOW option
AnsiPermissions	ANSI_PERMISSIONS option
AnsiNull	ANSINULL option
Async2Read	The number of rereads. A reread occurs when a read request for a page is received by the database IO subsystem while an asynchronous read IO operation has been posted to the operating system but has not completed.
AsyncRead	The number of pages that have been read asynchronously from disk.
AsyncWrite	The number of pages that have been written asynchronously to disk.
Automatic_timestamp	AUTOMATIC_TIMESTAMP option
Background_priority	BACKGROUND_PRIORITY option
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.
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.
CacheWrite	The number of pages in the cache that have been modified.
Chained	CHAINED option
Checkpoint_time	CHECKPOINT_TIME option
CIS_option	Reserved
Close_on_EndTrans	CLOSE_ON_ENDTRANS option
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.
CommProtocol	Returns CmdSeq for Adaptive Server Anywhere protocol (ODBC and Embedded SQL) or TDS for Open Client and jConnect connections.
Continue_after_raiserror	CONTINUE_AFTER_RAISE_ERROR option
Conversion_error	CONVERSION_ERROR option
Cooperative_commit_timeout	COOPERATIVE_COMMIT_TIMEOUT option
Cooperative_commits	COOPERATIVE_COMMITS option
CurrTaskSwitch	The number of current request context switches.
Cursors	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.
Date_format	DATE_FORMAT option
Date_order	DATE_ORDER option
DBNumber	The ID number of the database.
Default_timestamp_increment	DEFAULT_TIMESTAMP_INCREMENT option
Delayed_commit_timeout	DELAYED_COMMIT_TIMEOUT option
Delayed_commite	DELAYED_COMMITS option
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.
DiskSyncRead	The number of pages that have been read synchronously from disk.
DiskSyncWrite	The number of pages that have been written synchronously to disk. It is the sum of all the other "SyncWrite" counters.
DiskWaitRead	The number of times the server has waited synchronously for the completion of a read IO operation which was originally issued as an asynchronous read. Waitreads often occur due to cache misses on systems that support asynchronous IO.
DiskWaitWrite	The number of times the server has waited synchronously for the completion of a write IO operation which was originally issued as an asynchronous write.
DiskWrite	The number of modified pages that have been written to disk.
Divide_by_zero_error	DIVIDE_BY_ZERO_ERROR option
Escape_character	ESCAPE_CHARACTER option
Fire_triggers	FIRE_TRIGGERS option
Float_as_double	FLOAT_AS_DOUBLE option
FullCompare	The number of comparisons that have been performed beyond the hash value in an index.
HintUsed	The number of page-read operations that have been satisfied immediately from cache thanks to an earlier read hint.
IndAdd	The number of entries that have been added to indexes.
IndLookup	The number of entries that have been looked up in indexes.
Java_heap_size	JAVA_HEAP_SIZE option
Java_namespace_size	JAVA_NAMESPACE_SIZE option
LastIdle	The number of ticks between requests.
LastReqTime	The time at which the last request for the specified connection started.
LockTablePages	The number of pages used to store lock table info.
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
Login_procedure	LOGIN_PROCEDURE option
LogRewrite	The number of pages that were previously written to the transaction log (but were not full) that have been written to the transaction log again (but with more data added).
LogWrite	The number of pages that have been written to the transaction log.
Max_cursor_count	MAX_CURSOR_COUNT option
Max_statement_count	MAX_STATEMENT_COUNT option
Name	The name of the server.
Nearest_century	NEAREST_CENTURY option
NodeAddress	The node for the client in a client/server connection.
Non_keywords	NON_KEYWORDS option
Number	The ID number of the connection.
Percent_as_comment	PERCENT_AS_COMMENT option
Port	An application-specific number for each client machine, identifying the connection port.
Precision	PRECISION option
Prefetch	PREFETCH option
PrepStmt	The number of prepared statements that are currently being maintained by the server.
ProcessTime	The time since the start of the connection.
Query_plan_on_open	QUERY_PLAN_ON_OPEN option
Quoted_identifier	QUOTED_IDENTIFIER option
ReadHint	The number of read hints. A read hint is an asynchronous read operation for a page that the database server is likely to need soon.
Recovery_time	RECOVERY_TIME option
Replicate_all	REPLICATE_ALL option
ReqType	A string for the type of the last request.
RI_Trigger_time	RI_Trigger_time option
Rlbk	The number of Rollback requests that have been handled.
RollbackLogPages	The number of pages in the rollback log
Row_counts	ROW_COUNTS option
Scale	SCALE option
SQL_flagger_error_level	SQL_FLAGGER_ERROR_LEVEL option
SQL_flagger_warning_level	SQL_FLAGGER_WARNING_LEVEL option
String_rtruncation	STRING_RTRUNCATION option
SyncWriteChkpt	The number of pages that have been written synchronously to disk for a checkpoint.
SyncWriteExtend	The number of pages that have been written synchronously to disk while extending a database file.
SyncWriteFreeCurr	The number of pages that have been written synchronously to disk to free a page that cannot remain in the in-memory free list.
SyncWriteFreePush	The number of pages that have been written synchronously to disk to free a page that can remain in the in-memory free list.
SyncWriteLog	The number of pages that have been written synchronously to the transaction log.
SyncWriteRlbk	The number of pages that have been written synchronously to the rollback log.
SyncWriteUnkn	The number of pages that have been written synchronously to disk for a reason not covered by other "SyncWrite" counters.
TaskSwitch	The number of times the current server thread has been changed.
TaskSwitchCheck	The number of times the current server thread has volunteered to give up the CPU to another server thread.
Thread_count	THREAD_COUNT option
Time_format	TIME_FORMAT option
Timestamp_format	TIMESTAMP_FORMAT option
Tsql_hex_constant	TSQL_HEX_CONSTANT option
Tsql_variables	TSQL_VARIABLES option
UncommitedOp	The number of uncommitted operations
Userid	The user ID for the connection.
VoluntaryBlock	The number of server threads that have voluntarily blocked on pending disk IO.
Wait_for_commit	WAIT_FOR_COMMIT option
WaitReadCmp	The number of read requests associated with a full comparison (a comparison beyond the hash value in an index) that must be satisfied by a synchronous read operation.
WaitReadOpt	The number of read requests posted by the optimizer that must be satisfied by a synchronous read operation.
WaitReadSys	The number of read requests posted from the system connection that must be satisfied by a synchronous read operation. The system connection is a special connection, used as the context before a connection is made and for operations performed outside of any client connection.
WaitReadTemp	The number of read requests for a temporary table that must be satisfied by a synchronous read operation.
WaitReadUnkn	The number of read requests from other sources that must be satisfied by a synchronous read operation.

Compatibility 

The following table shows the Adaptive Server Enterprise system functions and their status in Adaptive Server Anywhere:

Function	Status
Col_length	Implemented
Col_name	Implemented
Db_id	Implemented
Db_name	Implemented
Index_col	Implemented
Object_id	Implemented
Object_name	Implemented
Proc_role	Always returns 0
Show_role	Always returns NULL
Tsequal	Implemented
User_id	Implemented
User_name	Implemented
Suser_id	Implemented
Suser_name	Implemented
Datalength	Implemented
Curunreservedpgs	Not implemented
Data_pgs	Not implemented
Host_id	Not implemented
Host_name	Not implemented
Lct_admin	Not implemented
Reserved_pgs	Not implemented
Rowcnt	Not implemented
Used_pgs	Not implemented
Valid_name	Not implemented
Valid_user	Not implemented

Notes 

• Some of the system functions are implemented in Adaptive Server Anywhere as stored procedures.

• The db_id, db_name, and datalength functions are implemented as built-in functions.

The implemented system functions are described in the following table.

System function	Description
Col_length( table-name, column-name )	Returns the defined length of column
Col_name( table-id, column-id [, database-id] )	Returns the column name
Db_id( [ database-name ] )	Returns the database ID number
Db_name( [ database-id ] )	Returns the database name
Index_col (table-name, index-id, key_# [, userid] )	Returns the name of the indexed column
Object_id (object-name )	Returns the object ID
Object_name ( object-id [, database-id ] )	Returns the object name
Tsequal( timestamp, timestamp2 )	Compares timestamp values to prevent update on a row that has been modified since it was selected
User_id( [ user-name] )	Returns an integer user identification number. This does not return the Adaptive Server Anywhere user ID
User_name( [user-id] )	Returns the user ID (user name in Adaptive Server Enterprise)
Suser_id( [user-name] )	Returns an integer user identification number
Suser_name( [user-id] )	Returns the user ID (server user name in Adaptive Server Enterprise)
Datalength( expression )	Returns the length of the expression, in bytes

  Properties available for the server

The following table lists properties that apply across the server as a whole.

Examples 

  To retrieve the value of a server property:

1. Use the property system function: For example: the following statement returns the number of cache pages being used to hold the main heap:

   select property ( 'MainHeapPages')

  To retrieve the values of all server properties:

1. Use the sa_eng_properties system procedure:

   call sa_eng_properties

Descriptions 

Property	Description
ActiveReq	The number of server threads that are currently handling a request.
Async2Read	The number of rereads. A reread occurs when a read request for a page is received by the database IO subsystem while an asynchronous read IO operation has been posted to the operating system but has not completed.
AsyncRead	The number of pages that have been read asynchronously from disk.
AsyncWrite	The number of pages that have been written asynchronously to disk.
CacheHits	The number of database page lookups satisfied by finding the page in 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.
CacheWrite	The number of pages in the cache that have been modified.
CheckpointUrgency	The time that has elapsed since the last checkpoint, as a percentage of the checkpoint time setting of the database.
Chkpt	The number of checkpoints that have been performed.
ChkptFlush	The number of ranges of adjacent pages written out during a checkpoint.
ChkptPage	The number of transaction log checkpoints.
CommitFile	The number of times the server has forced a flush of the disk cache. On Windows NT and NetWare platforms, the disk cache does not need to be flushed if unbuffered (direct) IO is used.
CompanyName	The name of the company owning this software.
ConnsDisabled	The number of connections disabled
ContReq	The number of "CONTINUE" requests issued to the server.
CurrIO	The current number of file IOs that were issued by the server but have not yet completed.
CurrRead	The current number of file reads that were issued by the server but have not yet completed.
CurrWrite	The current number of file writes that were issued by the server but have not yet completed.
DirtyPages	The number of dirty pages in cache
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.
DiskSyncRead	The number of pages that have been read synchronously from disk.
DiskSyncWrite	The number of pages that have been written synchronously to disk. It is the sum of all the "SyncWrite" counters.
DiskWaitRead	The number of times the server has waited synchronously for the completion of a read IO operation which was originally issued as an asynchronous read. Waitreads often occur due to cache misses on systems that support asynchronous IO.
DiskWaitWrite	The number of times the server has waited synchronously for the completion of a write IO operation which was originally issued as an asynchronous write.
DiskWrite	The number of modified pages that have been written to disk.
ExtendDBWrite	The number of pages by which the database file has been extended.
ExtendTempWrite	The number of pages by which temporary files have been extended.
FreeWriteCurr	The number of pages freed, of those that cannot remain in the in-memory free list.
FreeWritePush	The number of pages freed, of those that can remain in the in-memory free list.
FullCompare	The number of comparisons that have been performed beyond the hash value in an index.
HintUsed	The number of page-read operations that have been satisfied immediately from cache thanks to an earlier read hint.
IdleCheck	The number of times that the server's idle thread has become active to do idle writes, idle checkpoints, and so on.
IdleChkpt	The number of checkpoints completed by the server's idle thread. An idle checkpoint occurs whenever the idle thread writes out the last dirty page in the cache.
IdleChkTime	The number of 100ths of a second spent checkpointing during idle IO.
IdleWrite	The number of disk writes that have been issued by the server's idle thread.
IndAdd	The number of entries that have been added to indexes.
IndLookup	The number of entries that have been looked up in indexes.
LegalCopyright	The copyright string for the software.
LegalTrademarks	Trademark information for the software.
LockTablePages	The number of pages used to store lock information
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").
LogRewrite	The number of pages that were previously written to the transaction log (but were not full) that have been written to the transaction log again (but with more data added).
LogWrite	The number of pages that have been written to the transaction log.
MainHeapPages	The number of pages used for global server data structures.
MapPages	The number of map pages used for accessing the lock table, frequency table, and table layout.
MaxIO	The maximum value that Curr IO has reached.
MaxRead	The maximum value that Curr Read has reached.
MaxWrite	The maximum value that Curr Write has reached.
Name	The name of the server.
PageRelocations	The number of relocatable heap pages that have been read from the temporary file.
PendingReq	The number of new requests detected by the server.
Platform	The operating system on which the software is running.
ProcedurePages	The number of relocatable heap pages that have been used for procedures.
ProductName	The name of the software.
ProductVersion	The version of the software being run.
ReadHint	The number of read hints. A read hint is an asynchronous read operation for a page that the database server is likely to need soon.
RelocatableHeapPages	The number of pages used for relocatable heaps (cursors, statements, procedures, triggers, views, etc.).
Req	The number of times the server has been entered to allow it to handle a new request or continue processing an existing request.
RollbackLogPages	The number of pages in the rollback log.
SyncWriteChkpt	The number of pages that have been written synchronously to disk for a checkpoint.
SyncWriteExtend	The number of pages that have been written synchronously to disk while extending a database file.
SyncWriteFreeCurr	The number of pages that have been written synchronously to disk to free a page that cannot remain in the in-memory free list.
SyncWriteFreePush	The number of pages that have been written synchronously to disk to free a page that can remain in the in-memory free list.
SyncWriteLog	The number of pages that have been written synchronously to the transaction log.
SyncWriteRlbk	The number of pages that have been written synchronously to the rollback log.
SyncWriteUnkn	The number of pages that have been written synchronously to disk for a reason not covered by other "SyncWrite" counters.
TriggerPages	The number of relocatable heap pages used for triggers.
UnschReq	The number of requests that are currently queued up waiting for an available server thread.
ViewPages	The number of relocatable heap pages used for views.
VoluntaryBlock	The number of server threads that have voluntarily blocked on pending disk IO.
WaitReadCmp	The number of read requests associated with a full comparison (a comparison beyond the hash value in an index) that must be satisfied by a synchronous read operation.
WaitReadOpt	The number of read requests posted by the optimizer that must be satisfied by a synchronous read operation.
WaitReadSys	The number of read requests posted from the system connection that must be satisfied by a synchronous read operation. The system connection is a special connection, used as the context before a connection is made and for operations performed outside of any client connection.
WaitReadTemp	The number of read requests for a temporary table that must be satisfied by a synchronous read operation.
WaitReadUnkn	The number of read requests from other sources that must be satisfied by a synchronous read operation.

  Properties available for each database

The following table lists properties available for each database on the server.

Examples 

  To retrieve the value of a database property:

1. Use the db_property system function: For example, the following statement returns the page size of the current database:

   select db_property ( 'PageSize')

  To retrieve the values of all database properties:

1. Use the sa_db_properties system procedure:

   call sa_db_properties

Descriptions 

Property	Description
Alias	The database name.
CheckpointUrgency	The time that has elapsed since the last checkpoint, as a percentage of the checkpoint time setting of the database.
ConnCount	The number of connections to the database.
File	The file name of the database root file, including path.
FileVersion	The version of the database file.
LogName	The file name of the transaction log, including path.
Name	The database name (identical to alias).
PageSize	The page size of the database, in bytes.
RecoveryUrgency	An estimate of the amount of time required to recover the database.