ASA Database Administration Guide
Database Performance and Connection Properties
Database properties
The following table lists properties available for each database on the server.
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
| Property | Description |
|---|---|
| Alias | The database name. |
| BlankPadding | The status of the blank padding feature. Returns ON if the database has blank padding enabled. Otherwise, it returns OFF. |
| BlobArenas | The status of the BlobArenas feature. Returns ON if the database stores extension (blob) pages separately from table pages for the database. Otherwise, returns OFF. |
| 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. |
| Capabilities | The capability bits enabled for the database. This property is primarily for use by technical support. |
| CaseSensitive | The status of the case sensitivity feature. Returns ON if the database is case sensitive. Otherwise, it returns OFF |
| CharSet | The character set of the database. |
| 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/2000/XP and NetWare platforms, the disk cache does not need to be flushed if unbuffered (direct) I/O is used. |
| CompressedBTrees | Returns ON if Compressed B-tree indexes are supported. Otherwise, returns OFF. |
| Compression | The compression status of the database. Returns either ON (meaning the database is compressed) or OFF. If a write file is created on a compressed database, the write file is NOT compressed. Starting a write file created on a compressed database and selecting db_property('icompression'), returns OFF. |
| ConnCount | The number of connections to the database. |
| CurrentRedoPos | The current offset in the transaction log file where the next database operation is to be logged. |
| CurrIO | The current number of file I/Os 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. |
| DBFileFragments | The number of database file fragments. This property is supported on Windows NT/2000/XP. |
| 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. |
| DriveType | The drive on which the database file is located. Returns CD, FIXED, RAMDISK, REMOTE, REMOVABLE, and UNKNOWN. Returns UNKNOWN on non-Windows platforms. |
| Encryption | The type of encryption applied to the database. Returns None, Simple, AES, or MDSR. |
| ExtendDB | The number of pages by which the database file has been extended. |
| ExtendTempWrite | The number of pages by which temporary files have been extended. |
| File | The file name of the database root file, including path. |
| FileSize dbspace |
When used with db_property, this property returns the file size of the system dbspace, in pages.
When used with db_extended_property, you can specify which dbspace you want the size for. Dbspacecan be either the name of the dbspace, the file_id of the dbspace, or temporary to refer to the temporary dbspace. You can also specify translog to return the size of the log file. Finally, you can specify writefile to refer to the write file. When using a write file, FileSize on a dbspace returns the amount of space in the virtual dbspace, represented by the underlying dbspace plus the modifications to that dbspace that have been stored in the write file. Leaving the dbspace unspecified, or using system, both refer to the system dbspace. If the specified dbspace does not exist, the property function returns null. If the name of a dbspace is specified and an id or name of a database which is not the database of the current connection is also specified, the function also returns null. |
| FileVersion | The version of the database file. This does not correspond to a software release version. |
| FreePages dbspace |
FreePages is only supported on databases created with 8.0.0 or later.
When used with db_property, this property returns the number of free pages in the system dbspace. When used with db_extended_property, you can specify which dbspace you want the number of free pages for. Dbspacecan be either the name of the dbspace, the file_id of the dbspace, or temporary to refer to the temporary dbspace. You can also specify translog to return the size of the log file. Finally, you can specify writefile to refer to the write file. When using a write file, FreePages on a dbspace returns the number of free pages in the virtual dbspace, represented by the underlying dbspace plus the modifications to that dbspace that have been stored in the write file. Leaving the dbspace unspecified, or using system both refer to the system dbspace. If the specified dbspace does not exist, the property function returns null. If the name of a dbspace is specified and an id or name of a database which is not the database of the current connection is also specified, the function also returns null. |
| FreePageBitMaps | Returns ON if free database pages are managed via bitmaps. Otherwise, returns OFF. |
| FullCompare | The number of comparisons that have been performed beyond the hash value in an index. |
| GlobalDBId | The value of the GLOBAL_DATABASE_ID option used to generate unique primary key values in a replication environment. |
| Histograms | Returns ON if optimizer statistics are maintained as histograms. Otherwise, returns OFF. |
| 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 I/O. |
| 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. |
| IOToRecover | The estimated number of I/O operations required to recover the database. |
| IQStore | Reserved. |
| JavaHeapSize | Heap size per Java VM. |
| JavaNSSize | Java VM Namespace size. |
| JDKVersion | The Java runtime library version used by this database. |
| Language | The locale language of the database. |
| LargeProcedureIDs | Returns ON if 32-bit stored procedure IDs are supported for the database. Otherwise, returns OFF. |
| LockTablePages | The number of pages used to store lock information. |
| LogFileFragments | The number of log file fragments. This property is supported on Windows NT/2000/XP. |
| 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"). |
| LogName | The file name of the transaction log, including path. |
| LogWrite | The number of pages that have been written to the transaction log. |
| LTMGeneration | The generation number of the LTM, or Replication Agent. This property is primarily for use by technical support. |
| LTMTrunc | The minimal confirmed log offset for the Replication Agent. |
| MapPages | The number of map pages used for accessing the lock table, frequency table, and table layout. |
| MaxIO | The maximum value that CurrIO has reached. |
| MaxRead | The maximum value that CurrRead has reached. |
| MaxWrite | The maximum value that CurrWrite has reached. |
| MultiByteCharSet | Returns ON if the database uses a multi-byte character set. Otherwise, returns OFF. |
| Name | The database name (identical to alias). |
| PageRelocations | The number of relocatable heap pages that have been read from the temporary file. |
| PageSize | The page size of the database, in bytes. |
| PreserveSource | Returns ON if the database preserves the source for procedures and views. Otherwise, returns OFF. |
| ProcedurePages | The number of relocatable heap pages that have been used for procedures. |
| QueryBypassed | The number of requests optimized by the optimizer bypass. |
| QueryCachePages | The number of pages used to cache execution plans. |
| QueryCachedPlans | The number of cached execution plans across all connections. |
| 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 fully optimized. |
| QueryBypassed | The number of requests reused from the plan cache. |
| ReadOnly | Returns ON if the database is being run in read-only mode. Otherwise, returns OFF. |
| RecoveryUrgency | An estimate of the amount of time required to recover the database. |
| RelocatableHeapPages | The number of pages used for relocatable heaps (cursors, statements, procedures, triggers, views, etc.). |
| RemoteTrunc | The minimal confirmed log offset for the SQL Remote Message Agent. |
| RollbackLogPages | The number of pages in the rollback log. |
| SeparateCheckpointLog | Returns ON if the checkpoint log for the database is maintained at the end of the SYSTEM dbspace. Otherwise, returns OFF. |
| SeparateForeignKeys | Returns ON if primary and foreign keys are stored separately. Otherwise, returns OFF. |
| SyncTrunc | The minimal confirmed log offset for the MobiLink client dbmlsync executable. |
| TableBitMaps | Returns ON if the database supports table bitmaps. Otherwise, returns OFF. |
| TempFileName | The file name of the database temporary file, including path. |
| TempTablePages | The number of pages in the temporary file used for temporary tables. |
| TransactionsSpanLogs | Returns ON if transactions can span multiple log files. Otherwise, returns OFF. |
| TriggerPages | The number of relocatable heap pages used for triggers. |
| VariableHashSize | Returns ON if the hash size can be specified for B-tree indexes. Otherwise, returns OFF. |
| ViewPages | The number of relocatable heap pages used for views. |