ASA SQL User's Guide
  Monitoring and Improving Performance
    Monitoring database performance

Obtaining database statistics from a client application

Adaptive Server Anywhere provides a set of system functions that can access information on a per-connection, per-database, or server-wide basis. The kind of information available ranges from static information (such as the server name) to detailed performance-related statistics (such as disk and memory usage).

Functions that retrieve system information 

The following functions retrieve system information:

• property function    Provides the value of a given property on an engine-wide basis.

• connection_property function    Provides the value of a given property for a given connection, or by default, for the current connection.

• db_property function    Provides the value of a given property for a given database, or by default, for the current database.

Supply as an argument only the name of the property you wish to retrieve. The functions return the value for the current server, connection, or database.

For more information, see PROPERTY function [System], CONNECTION_PROPERTY function [System], and DB_PROPERTY function [System].

For a complete list of the properties available from the system functions, see System functions.

Examples 

The following statement sets a variable named server_name to the name of the current server:

SET server_name = property( 'name' )

The following query returns the user ID for the current connection:

SELECT connection_property( 'userid' )

The following query returns the filename for the root file of the current database:

SELECT db_property( 'file' )

Improving query efficiency 

For better performance, a client application monitoring database activity should use the property_number function to identify a named property, and then use the number to repeatedly retrieve the statistic. The following set of statements illustrates the process from Interactive SQL:

CREATE VARIABLE propnum INT ;
CREATE VARIABLE propval INT ;
SET propnum = property_number( 'cacheread' );
SET propval = property( propnum )

Property names obtained in this way are available for many different database statistics, from the number of transaction log page write operations and the number of checkpoints carried out, to the number of reads of index leaf pages from the memory cache.

You can view many of these statistics in graph form from the Sybase Central database management tool.