Database-level properties

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

Examples

To retrieve the value of a database property

  • 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

  • Use the sa_db_properties system procedure:

    CALL sa_db_properties;
See also
Descriptions
Property Description
AccentSensitive Returns the status of the accent sensitivity feature. Returns Yes if the database is accent sensitive, No if it is not, or FRENCH if it is using French sensitivity rules.
Alias Returns the database name.
AlternateMirrorServerName Returns the alternate mirror server name associated with the database if one was specified. See -sm database option.
AlternateServerName Returns the alternate server name associated with the database if one was specified. See -sn database option.
ArbiterState

Returns one of the following values:

  • NULL   You are connected to a database that is not mirrored.

  • connected   The arbiter server is connected to the primary server.

  • disconnected   The arbiter server is not connected to the primary server.

See Introduction to database mirroring.

AuditingTypes Returns the types of auditing currently enabled. See auditing option [database]
BlankPadding Returns On if the database has blank padding enabled. Otherwise, it returns Off.
CacheHits Returns 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 Returns the number of index internal-node pages that have been read from the cache.
CacheReadIndLeaf Returns the number of index leaf pages that have been read from the cache.
CacheReadTable Returns the number of table pages that have been read from the cache.
CacheReadWorkTable Returns the number of cache work table reads.
Capabilities Returns the capability bits enabled for the database. This property is primarily for use by technical support.
CaseSensitive Returns the status of the case sensitivity feature. Returns On if the database is case sensitive. Otherwise, it returns Off. In case sensitive databases, data comparisons are case sensitive. This setting does not affect the case sensitivity of identifiers. Passwords are always case sensitive. See Case sensitivity.
CatalogCollation Returns the identifier for the collation used for the catalog. This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].
CharSet

Returns the CHAR character set of the database.

This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].

CheckpointLogBitmapPagesWritten Returns the number of writes to the checkpoint log bitmap.
CheckpointLogBitmapSize Returns the checkpoint log bitmap size.
CheckpointLogCommitToDisk Returns the number of checkpoint log commits to disk.
CheckpointLogPageInUse Returns the number of checkpoint log pages in use.
CheckpointLogPagesRelocated Returns the number of relocated checkpoint log pages.
CheckpointLogPagesWritten Returns the number of checkpoint log pages that have been written.
CheckpointLogSavePreimage Returns the number of pre-images of database pages that are being added to the checkpoint log.
CheckpointLogSize Returns the size of the checkpoint log, in pages.
CheckpointLogWrites Returns the number of writes to the checkpoint log.
CheckpointUrgency Returns the time that has elapsed since the last checkpoint, as a percentage of the checkpoint time setting of the database.
Checksum Returns On if database page checksums are enabled for the database. Otherwise, returns Off. Checksums are always present for critical pages.
Chkpt Returns the number of checkpoints that have been performed.
ChkptFlush Returns the number of ranges of adjacent pages written out during a checkpoint.
ChkptPage Returns the number of transaction log checkpoints.
CleanablePagesAdded Returns the number of pages marked to be cleaned since database server startup.
CleanablePagesCleaned Returns the number of database pages cleaned since database server startup.
CleanableRowsAdded Returns the number of rows marked to be deleted since database server startup.
CleanableRowsCleaned Returns the number of shadow table rows deleted since database server startup.
Collation

Returns the collation used by the database. For a list of available collations, see Supported and alternate collations.

This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].

CommitFile Returns the number of times the server has forced a flush of the disk cache. On Windows, the disk cache doesn't need to be flushed if unbuffered (direct) I/O is used.
ConnCount

Returns the number of connections to the database. The property value does not include connections used for firing events or other internal operations, but it does include connections used for external environment support. If you want to obtain an accurate count of the number of licensed connections in use, you can execute the following statement: 

SELECT COUNT( * ) FROM sa_conn_info( )
ConnsDisabled Returns On if connections to the current database are disabled, otherwise, returns Off.
CurrentRedoPos Returns the current offset in the transaction log file where the next database operation is to be logged.
CurrIO Returns the current number of file I/Os that were issued by the server but haven't yet completed.
CurrRead Returns the current number of file reads that were issued by the server, but haven't yet completed.
CurrWrite Returns the current number of file writes that were issued by the server, but haven't yet completed.
DatabaseCleaner Returns On or Off to indicate whether the database cleaner is enabled.
DBFileFragments Returns the number of database file fragments. This property is supported on Windows.
DiskRead Returns the number of pages that have been read from disk.
DiskReadHint Returns the number of disk read hints.
DiskReadHintPages Returns the number of disk read hint pages.
DiskReadIndInt Returns the number of index internal-node pages that have been read from disk.
DiskReadIndLeaf Returns the number of index leaf pages that have been read from disk.
DiskReadRetry Returns the number of disk read retries.
DiskReadTable Returns the number of table pages that have been read from disk.
DiskReadWorkTable Returns the number of disk work table reads.
DiskRetryReadScatter Returns the number of disk read retries for scattered reads.
DiskWaitRead Returns the number of times the database server waited for an asynchronous read.
DiskWaitWrite Returns the number of times the database server waited for an asynchronous write.
DiskWrite Returns the number of modified pages that have been written to disk.
DiskWriteHint Returns the number of disk write hints.
DiskWriteHintPages Returns the number of disk gather write hints.
DiskSyncRead Returns the number of disk reads issued synchronously.
DiskSyncWrite Returns the number of writes issued synchronously.
DriveType

Returns the type of drive on which the database file is located. The value is one of the following: CD, FIXED, RAMDISK, REMOTE, REMOVABLE, or UNKNOWN.

On Unix, depending on the version of Unix and the type of drive, it may not be possible to determine the drive type. In these cases UNKNOWN is returned.

This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].

Encryption Returns the type of encryption used for database or table encryption, one of None, Simple, AES, AES256, AES_FIPS, or AES256_FIPS.
EncryptionScope

Returns the part of the database, if any, that can be encrypted. The value is one of the following: TABLE, DATABASE, or NONE.

TABLE indicates that table encryption is enabled. DATABASE indicates that the whole database is encrypted. NONE indicates that table encryption is not enabled, and the database is not encrypted.

ExprCacheAbandons Returns the number of time that the expression cache was completely abandoned because the hit rate was too low.
ExprCacheDropsToReadOnly Returns the number of times that the expression cache dropped to read-only status because the hit rate was low.
ExprCacheEvicts Returns the number of evictions from the expression cache.
ExprCacheHits Returns the number of hits in the expression cache.
ExprCacheInserts Returns the number of values inserted into the expression cache.
ExprCacheLookups Returns the number of lookups performed in the expression cache.
ExprCacheResumesOfReadWrite Returns the number of times that the expression cache resumed read-write status because the hit rate increased.
ExprCacheStarts Returns the number of times the expression cache was started.
ExtendDB Returns the number of pages by which the database file has been extended.
ExtendTempWrite Returns the number of pages by which temporary files have been extended.
File Returns the file name of the database root file, including path. This property has extensions that you can specify when querying for property value. See DB_EXTENDED_PROPERTY function [System].
FileSize Returns the file size of the system dbspace, in pages. This property has extensions that you can specify when querying for property value. See DB_EXTENDED_PROPERTY function [System].
FreePages

Returns the number of free pages in the system dbspace. The FreePages property is only supported on databases created with version 8.0.0 or later.

This property has extensions that you can specify when querying for property value. See DB_EXTENDED_PROPERTY function [System].

FullCompare Returns the number of comparisons that have been performed beyond the hash value in an index.
GetData Returns the number of GETDATA requests.
GlobalDBID Returns the value of the global_database_id option used to generate unique primary key values in a replication environment.
HashForcedPartitions Returns the number of times that a hash operator was forced to partition because of competition for memory.
HasCollationTailoring Returns a value indicating whether collation tailoring was specified when the database was created. Possible values are On or Off.
HasEndianSwapFix Returns a value indicating whether the database supports both big-endian and little endian UTF-16 encoding on all platforms, regardless of the endianness of the platform. Possible values are On or Off.
HashRowsFiltered Returns the number of probe rows rejected by bit-vector filters.
HashRowsPartitioned Returns the number of rows written to hash work tables.
HashWorkTables Returns the number of work tables created for hash-based operations.
HasNCHARLegacyCollationFix

Returns one of the following values:

  • NULL   For database servers without the legacy collation fix installed.

  • ON   For databases created after version 10 and 10.0.x databases created by a database server with the legacy collation fix and a legacy NCHAR collation.

  • OFF   For databases created after version 10.

IdentitySignature Reserved.
IdleCheck Returns the number of times that the server's idle thread has become active to do idle writes, idle checkpoints, and so on.
IdleChkpt Returns 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 Returns the number of hundredths of a second spent checkpointing during idle I/O.
IdleWrite Returns the number of disk writes that have been issued by the server's idle thread.
IndAdd Returns the number of entries that have been added to indexes.
IndLookup Returns the number of entries that have been looked up in indexes.
IOParallelism

Returns the estimated number of simultaneous I/O operations supported by the dbspace. This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].

IOToRecover Returns the estimated number of I/O operations required to recover the database.
JavaVM Returns the Java VM the database server uses to execute Java in the database.
Language Returns a comma-separated list of languages known to be supported by the database collation. The languages are in two-letter ISO format. If the language isn't known, the return value is NULL. For a list of the two-letter ISO format language names and the language they correspond to, see Understanding the locale language.
LockCount Returns the number of locks held by the database.
LockTablePages Returns the number of pages used to store lock information.
LogFileFragments Returns the number of log file fragments. This property is supported on Windows.
LogFreeCommit Returns 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).
LogMirrorName Returns the file name of the transaction log mirror, including path.
LogName Returns the file name of the transaction log, including path.
LogWrite Returns the number of pages that have been written to the transaction log.
LTMGeneration Returns the generation number of the LTM or Replication Agent. This property is primarily for use by technical support.
LTMTrunc Returns the minimal confirmed log offset for the Replication Agent.
MaxIO Returns the maximum value that CurrIO has reached.
MaxRead Returns the maximum value that CurrRead has reached.
MaxWrite Returns the maximum value that CurrWrite has reached.
MirrorMode Returns NULL if database mirroring is not in use, synchronous if the mirroring mode specified with the -xp command line option is synchronous, and asynchronous otherwise.
MirrorState

Returns one of the following values:

  • null   You are connected to a database that is not mirrored.

  • synchronizing   The mirror server is not connected or has not yet read all of the primary’s log pages. This value is also returned if the synchronization mode is asynchronous.

  • synchronized   The mirror server is connected and has all changes that have been committed on the primary server.

See Introduction to database mirroring.

MultiByteCharSet Returns On if the database uses a multibyte character set. Otherwise, returns Off.
Name Returns the database name (identical to Alias).
NcharCharSet Returns the NCHAR character set of the database.
NcharCollation Returns the name of the collation used for NCHAR data. This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].
NextScheduleTime Returns the next scheduled execution time for a specified event; query this property using the DB_EXTENDED_PROPERTY function. See DB_EXTENDED_PROPERTY function [System].
OptionWatchAction Returns the action that is taken when an attempt is made to set a database option that is included in the OptionWatchList property. See sa_server_option system procedure.
OptionWatchList Returns the list of database options being monitored by the database server. See sa_server_option system procedure.
PageRelocations Returns the number of relocatable heap pages that have been read from the temporary file.
PageSize Returns the page size of the database, in bytes.
PartnerState

Returns one of the following values:

  • NULL   You are connected to a database that is not mirrored.

  • connected   The mirror server is connected to the primary server.

  • disconnected   The mirror server is not connected to the primary server.

See Introduction to database mirroring.

ProcedurePages Returns the number of relocatable heap pages that have been used for procedures.
ProcedureProfiling Returns On if procedure profiling is turned on for the database. Otherwise, returns Off.
QueryBypassed Returns the number of requests reused from the plan cache.
QueryCachedPlans Returns the number of cached execution plans across all connections.
QueryCachePages Returns the number of pages used to cache execution plans.
QueryJHToJNLOptUsed Returns the number of times a hash join was converted to a nested loops join.
QueryLowMemoryStrategy Returns 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 Returns the number of requests fully optimized.
QueryReused Returns the number of reused query plans.
QueryRowsBufferFetch Returns the number of rows fetched using buffering.
QueryRowsMaterialized Returns the number of rows written to work tables during query processing.
ReadOnly Returns On if the database is being run in read-only mode. Otherwise, returns Off.
ReceivingTracingFrom Returns the name of the database from which the tracing data is coming. Returns a blank string if tracing is not attached.
RecoveryUrgency Returns an estimate of the amount of time required to recover the database as a percentage of the recovery time setting of the database. See -gr server option and How the database server decides when to checkpoint.
RecursiveIterations Returns the number of iterations for recursive unions.
RecursiveIterationsHash Returns the number of times recursive hash join used a hash strategy.
RecursiveIterationsNested Returns the number of times recursive hash join used a nested loops strategy.
RecursiveJNLMisses Returns the number of index probe cache misses for recursive hash join.
RecursiveJNLProbes Returns the number of times recursive hash join attempted an index probe.
RelocatableHeapPages Returns the number of pages used for relocatable heaps (cursors, statements, procedures, triggers, views, and so on.).
RemoteTrunc Returns the minimal confirmed log offset for the SQL Remote Message Agent.
RollbackLogPages Returns the number of pages in the rollback log.
SendingTracingTo Returns the connection string where the tracing data is being sent. Returns a blank string if tracing is not attached.
SnapshotCount Returns the number of snapshots associated with the database.
SnapshotIsolationState

Returns one of the following values:

  • On   snapshot isolation is enabled for the database.

  • Off   snapshot isolation is disabled for the database.

  • in_transition_to_on   snapshot isolation will be enabled once the current transactions complete.

  • in_transition_to_off   snapshot isolation will be disabled once the current transactions complete.

See allow_snapshot_isolation option [database].

SortMergePasses Returns the number of merge passes used during sorting.
SortRowsMaterialized Returns the number of rows written to sort work tables.
SortRunsWritten Returns the number of sorted runs written during sorting.
SortSortedRuns Returns the number of sorted runs created during run formation.
SortWorkTables Returns the number of work tables created for sorting.
SyncTrunc Returns the minimal confirmed log offset for the MobiLink client dbmlsync executable.
TempFileName Returns the file name of the database temporary file, including path.
TempTablePages Returns the number of pages in the temporary file used for temporary tables.
TriggerPages Returns the number of relocatable heap pages used for triggers.
VersionStorePages Returns the number of pages in the temporary file that are being used for the row version store when snapshot isolation is enabled.
ViewPages Returns the number of relocatable heap pages used for views.
XPathCompiles Returns the number of times any XPath query (using the openxml procedure) was compiled by the database server since database server startup.
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0