Click here for aggregate storage version
Change database-wide settings.
Permission required: create_application.
Syntax
Use alter database to change the following database-wide settings:
Keyword | Description | ||
---|---|---|---|
enable two_pass_calc | Recalculate (after a default calculation) database outline members tagged as Two Pass, so they will be recalculated after other database members have been consolidated. This setting is enabled by default. Members that usually require a two-pass calculation are those members of the Accounts dimension that are calculated by a formula rather than by hierarchical consolidation. These members are typically ratios, such as "Profit % Sales" (profit percentage of sales), which has a member formula. This setting is ignored during a calculation script; it is used only during a default calculation. To use two-pass calculation in a non-default calculation, use the CALC TWOPASS command in the calculation script. | ||
disable two_pass_calc | Do not recalculate database outline members tagged as Two Pass after a default calculation. Two-pass calculation is enabled by default. | ||
enable aggregate_missing | Consolidate #MISSING values along with the regular database consolidation. If you never load data at parent levels, aggregating #MISSING values can improve calculation performance, depending on the ratio between upper level blocks and input blocks in the database. If this setting is enabled and you load values directly at the parent level, these parent-level values will be replaced by the results of the consolidation, even if the results are #MISSING values. The aggregate missing setting is disabled by default. | ||
disable aggregate_missing | Do not consolidate #MISSING values. This is the default. Data that is loaded at parent levels is not overwritten by #MISSING values of children below it. However, if any of the child data values are not #MISSING, these values are consolidated and overwrite the parent values. | ||
enable startup | Enable users to start the database directly or as a result of requests requiring the database to be started. Startup is enabled by default. | ||
disable startup | Prevent all users from starting the database directly or as a result of requests that would start the database. Startup is enabled by default. | ||
enable autostartup | Automatically start the database when the application to which it belongs starts. Autostartup is enabled by default. This setting is applicable only when startup is enabled. | ||
disable autostartup | Prevent automatic starting of the database when the application to which it belongs starts. Autostartup is enabled by default. | ||
enable compression | Enable data compression. By default, Bitmap compression is enabled. To switch to a different compression type, use alter database set compression. | ||
disable compression | Disable data compression. By default, Bitmap compression is enabled. | ||
enable create_blocks | Allow Essbase to create a data block when you assign a non-constant value to a member combination for which a data block does not already exist. Block creation on equation is disabled by default, because it can result in a very large database. When you assign a constant to a member on a sparse dimension, you do not need to enable Create Blocks on Equation, because Essbase would create a data block anyway. For example, "West = 5;" would result in the creation of data blocks, with or without the Create Blocks on Equation setting enabled. You do need to check this option if you want blocks created when you assign anything other than a constant to a member on a sparse dimension for which a data block does not already exist. For example, if no data exists for Actuals, a member of a sparse Scenario dimension, then you need to enable Create Blocks on Equation in order to perform the following allocation: 2002Forecast = Actuals * 1.05;. | ||
disable create_blocks | Turn off the Create Blocks on Equation setting. The setting is disabled by default. | ||
enable committed_mode | Set the database isolation level to committed access, meaning that only one transaction at a time can update data blocks. Essbase holds read/write locks on all data blocks until the transaction and the commit operations are performed. If pre-image access is enabled, users (or transactions) can still have read-only access to data at its last commit point. For more information, see the enable pre_image_access setting. The default isolation-level mode is Uncommitted. | ||
disable committed_mode | Turn off the Committed Mode setting, reverting to the default isolation level of Uncommitted for the database.
In uncommitted mode, Essbase allows transactions to hold read/write locks on a block-by-block basis. Essbase releases a block after it is updated, but does not commit blocks until the transaction is completed, or until a specified number of blocks or rows (a "synchronization point") has been reached. You can set this limit using the implicit_commit settings. | ||
enable pre_image_access | Allow users (or other transactions) read-only access to data at its last commit point, when the database is in committed mode (meaning that data blocks may be locked for the duration of a concurrent transaction). Pre-image access is enabled by default when the database is in committed mode. See also the enable committed_mode setting. | ||
disable pre_image_access | Disable pre-image access, disallowing read-only access to locked blocks of data at their last commit point (this setting is only applicable while the database is in committed mode). Pre-image access is enabled by default when the database is in committed mode. | ||
enable cache_pinning | Enable cache memory locking, which locks the memory used for the index cache, data file cache, and data cache into physical memory, giving the Essbase Server kernel priority use of system RAM. Cache memory locking improves performance for a database because the system memory manager does not need to swap the memory used by the caches when swapping the memory used by the Essbase Server. The setting takes effect after you restart the database. By default, cache memory locking is disabled. To use cache memory locking, you must be using direct I/O (buffered I/O is the default). For more information, see the Oracle Essbase Technical Reference documentation for the DIRECTIO setting for essbase.cfg. | ||
disable cache_pinning | Disable cache memory locking, reverting to the default. |
Example
alter database Sample.Basic enable cache_pinning;
Locks database cache pages in physical memory so that the operating system will not page them out while the database is still using them.
alter database Sample.Basic disable two_pass_calc;
Prevents recalculation (after a default calculation) of members tagged as Two Pass.