The following table lists properties available for each database on the database server.
You can use the DB_PROPERTY system function to retrieve the value for an individual property, or you can use the sa_db_properties system procedure to retrieve the values of all database properties. Property names are case insensitive.
Examples To retrieve the value of a database propertyUse 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 propertiesUse the sa_db_properties system procedure:
CALL sa_db_properties; |
See also Descriptions| Property name | 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 dbsrv12 database option (deprecated). | ||
| AlternateServerName | Returns the alternate server name associated with the database if one was specified. See -sn dbsrv12 database option. | ||
| ArbiterState |
Returns one of the following values:
|
||
| AuditingTypes | Returns the types of auditing currently enabled. See auditing option | ||
| Authenticated | Returns Yes if the database has been authenticated, or No if the database has not been authenticated. | ||
| 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]. |
||
| CheckpointLogCommitToDisk | Returns the number of checkpoint log commits to disk. | ||
| CheckpointLogPagesInUse | 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 preimages 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 global checksums are enabled for the database (a checksum is created for each database page). 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 firing events and 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:
|
||
| ConnPoolCachedCount | Returns the number of connections disconnected by the application but cached for connection pooling. | ||
| ConnPoolHits | Returns the number of reused pooled connections. | ||
| ConnPoolMisses | Returns the number of pooled connections which were cached but could not be reused. | ||
| 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. | ||
| 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. | ||
| DiskSyncRead | Returns the number of disk reads issued synchronously. | ||
| DiskSyncWrite | Returns the number of writes issued synchronously. | ||
| 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. | ||
| DriveBus |
Returns the type of bus to which the database file is connected. The value is The value is one of the following:
If the bus type cannot be determined, this property returns NULL. The value of this property for the primary dbspace is recorded in the SYSHISTORY view. See SYSHISTORY system view. |
||
| DriveModel |
Returns the model number of the drive on which the database is located. The value of this property for the primary dbspace is recorded in the SYSHISTORY view. See SYSHISTORY system view. If the drive model cannot be determined, this property returns NULL. |
||
| DriveType |
Returns the type of drive on which the database file is located. The value is one of the following:
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. | ||
| 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. | ||
| HashForcedPartitions | Returns the number of times that a hash operator was forced to partition because of competition for memory. | ||
| 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:
|
||
| HasTornWriteFix | Returns a value that indicates whether the database has a fixed file format to allow recovery from partial writes. | ||
| HttpConnPoolCachedCount | Returns the absolute count of cached database connections within all pools. | ||
| HttpConnPoolHits | Returns the rate of connections reused by the same service. | ||
| HttpConnPoolMisses | Returns the rate of new connections that were allocated when a connection could not be accessed from a pool. This property only counts HTTP requests to services that use connection pooling. At the time of access, a connection may not have been available due to a small pool size whose oldest connection did not fit the steal criteria. | ||
| HttpConnPoolSteals |
Returns the rate of connections taken by services where the connection belonged to another service. The service steals a connection from another service if the criteria is met for an HTTP request for a service not having any direct reusable connections and the pool size and age of the least used connection. A connection is allocated for the service and Http_Conn_Pool_Misses is incremented instead if the pool criteria is not met. |
||
| IdentitySignature | This property is reserved for system use. Do not change the setting of this property. | ||
| 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. | ||
| IQStore | This property is reserved for system use. Do not change the setting of this property. | ||
| 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. | ||
| LastCheckpointTime | Returns the date and time, in millisecond, of the most recent checkpoint. | ||
| 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 | This property is for internal use only. | ||
| LTMTrunc | This property is for internal use only. | ||
| 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 option is synchronous, and asynchronous otherwise. | ||
| MirrorRole |
Returns one of the following values:
|
||
| MirrorServerState |
Returns one of the following values:
|
||
| MirrorServerWaits | Returns the number of times the database server waited more than 500 milliseconds when sending log pages to copy servers. | ||
| MirrorState |
Returns one of the following values:
|
||
| MultiByteCharSet | Returns On if the database uses a multibyte character set, such as UTF-8. Otherwise, returns Off. For more information, see Multibyte character sets. | ||
| Name |
Returns the database name (identical to Alias database property). |
||
| 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:
|
||
| Prepares | Returns the number of statement preparations performed for the database. | ||
| 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. | ||
| QueryBypassedCosted | Returns the number of requests processed by the optimizer bypass using costing. | ||
| QueryBypassedHeuristic | Returns the number of requests processed by the optimizer bypass using heuristics. | ||
| QueryBypassedOptimized | Returns the number of requests initially processed by the optimizer bypass and subsequently fully optimized by the SQL Anywhere optimizer. | ||
| QueryCachedPlans | Returns the number of cached execution plans across all connections. | ||
| QueryCachePages | Returns the number of pages used to cache execution plans. | ||
| QueryDescribedBypass | Returns the number of describe requests processed by the optimizer bypass. | ||
| QueryDescribedOptimizer | Returns the number of describe requests processed by the optimizer. | ||
| 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. | ||
| QueryOpened | Returns the number of OPEN requests for execution. | ||
| QueryOptimized | Returns the number of requests fully optimized. | ||
| QueryReused | Returns the number of reused query plans. | ||
| QueryRowsFetched | Returns the number of rows that have been read from base tables, either by a sequential scan or an index scan, for this database. | ||
| 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 dbeng12/dbsrv12 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:
|
||
| 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. | ||
| StatementDescribes | Returns the total number of statements processed by DESCRIBE requests. | ||
| StatementPostAnnotates | Returns the number of statements processed by the semantic query transformation phase. | ||
| StatementPostAnnotatesSimple | Returns the number of statements processed by the semantic query transformation phase, but which skipped some of the semantic transformations. | ||
| StatementPostAnnotatesSkipped | Returns the number of statements that have completely skipped the semantic query transformation phase. | ||
| SynchronizationSchemaChangeActive | Returns On if an active connection issued a START SYNCHRONIZATION SCHEMA CHANGE but has not issued a STOP SYNCHRONIZATION SCHEMA CHANGE. See START SYNCHRONIZATION SCHEMA CHANGE statement [MobiLink] and STOP SYNCHRONIZATION SCHEMA CHANGE statement [MobiLink]. | ||
| 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. | ||
| WriteChecksum | Returns On if the database server adds checksums to pages before they are written out; otherwise, returns Off. See Using checksums to detect corruption. | ||
| XPathCompiles | Returns the number of times any XPath query (using the openxml procedure) was compiled by the database server since database server startup. See openxml system procedure. |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |