Click here for aggregate storage version
Change database-wide settings. Permission required: Database designer.
Add a disk volume definition if you want to allocate storage across multiple volumes, or restrict space used on a volume. You can allocate storage for index files, data files, or both.
If no disk volume is defined, data and index files are stored in the database directory (for example, $ARBORPATH/app/sample/basic
).
File_size is the maximum size an index or data file may attain. Default = 2G; minimum = 8192K (8M).
Partition_size is the maximum amount of disk space allocated to the volume. Default = unlimited.
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.
alter database Sample.Basic set disk volume c file_type index;
Changes the storage settings for Sample Basic so that the alternate disk volume specified as the C: drive stores only index files.
alter database Sample.Basic set lock_timeout after 120;
Changes the number of seconds to wait for blocks to be unlocked. If a transaction request is made which cannot be granted in 120 seconds, the transaction is rolled back until a lock can be granted.
Use alter database to change the following database-wide settings:
Key Phrase | Explanation | |
---|---|---|
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 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 Analytic Services 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 Analytic Services would create a data block anyway. For example, 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: |
|
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.
Analytic Services 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. Note: Spreadsheet Add-in lock and send operations are always in committed mode.
In uncommitted mode, Analytic Services allows transactions to hold read/write locks on a
block-by-block basis. Analytic Services 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 |
|
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 Analytic 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 Analytic 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 Technical Reference documentation for the DIRECTIO setting for |
|
disable cache_pinning |
Disable cache memory locking, reverting to the default. | |
begin archive to file |
Prepare the database for backup by an archiving program, and prevent writing to the files during backup. This statement requires the database to be started. Begin-archive achieves the following outcomes:
Begin-archive and end-archive do not perform the backup; they simply protect the database during the backup process. |
|
end archive | Return the database to read-write mode after backing up the database files. End-archive achieves the following outcomes:
|
|
set retrieve_buffer_size |
Change the database retrieval buffer size. This buffer holds extracted row data cells before they are evaluated by the RESTRICT or TOP/BOTTOM Report Writer commands. The default size is 10 KB. The minimum size is 2 KB. Increasing the size may improve retrieval performance. | |
set retrieve_sort _buffer_size |
Change the database retrieval sort buffer size. This buffer holds data until it is sorted. The Report Writer and Essbase Query Designer use the retrieval sort buffer. The default size is 10 KB. The minimum size is 2 KB. Increasing the size may improve retrieval performance. | |
set data_cache_size |
Change the data cache size. The data cache is a buffer in memory that holds uncompressed data blocks. Analytic Server allocates memory to the data cache during data load, calculation, and retrieval operations as needed. The default and minimum size is 3072 KB. | |
set data_file _cache_size |
Change the data file cache size. The data file cache is a buffer in memory that holds compressed data files (.PAG files). Analytic Server allocates memory to the data file cache during data load, calculation, and retrieval operations as needed. The data file cache is not used when buffered I/O is used; you must use direct i/o to use the data file cache. The default size is 32 MB. | |
set index _cache_size |
Change the index cache size. The index cache is a buffer in memory that holds index pages. When a data block is requested, Analytic Services looks at the index pages in the index cache to find its location on disk. The default size is 1 MB when buffered I/O is used, and 10 MB when direct I/O is used. Buffered I/O is the default for this release. | |
set currency _database |
Link the database with a currency database. A currency database enables you to convert currency values in a database from one currency into another currency. | |
set currency _member |
Specify the member to use as a default value in currency conversions. You can specify any valid member of the dimension defined as "Currency Type" in the currency database. | |
set currency _conversion |
Specify whether during currency conversion, the calculation method muliplies the currency database exchange rates with the main database values, or that the currency database exchange rates are divided by the main database values. | |
set minimum permission |
Set a level of permission that all users or groups can have to the database. Users or groups with higher granted permissions than the minimum permission are not affected. | |
set compression rle |
Set the database to use run-length encoding (RLE) compression. Analytic Services compresses repetitive, consecutive values, including zeros and #MISSING values. The default compression type is bitmap. When a compressed data block is brought into the data cache, Analytic Services expands the block to its full size, regardless of the scheme that was used to compress it. |
|
set compression bitmap |
Set the database to use bitmap compression, the default. Analytic Services stores only non-missing values and uses a bitmapping scheme. When a compressed data block is brought into the data cache, Analytic Services expands the block to its full size, regardless of the scheme that was used to compress it. |
|
set compression zlib |
Set the database to use ZLIB compression. When a compressed data block is brought into the data cache, Analytic Services expands the block to its full size, regardless of the scheme that was used to compress it. If your database allows or requires "Aggregate Missing Values" setting set to YES, then you may want to consider using ZLIB as the compression scheme. ZLIB particularly works well on such databases compared to other compression schemes. However, changing the aggregate missing values setting may have an impact on calculation results - see the Database Administrator's Guide. Consider using ZLIB only if you have already determined that the setting should be YES for other reasons. |
|
set lock_timeout | Change the interval to wait for blocks to be unlocked when the database is in committed mode. If a transaction request is made that cannot be granted in the allotted time, the transaction is rolled back until a lock can be granted. Note: Spreadsheet Add-in lock and send operations are always in committed mode. | |
set implicit_commit after <number> blocks |
When uncommitted access is enabled, set the frequency at which Analytic Services commits data blocks (after the specified number of blocks has been reached). | |
set implicit_commit after <number> rows |
When uncommitted access is enabled, set the frequency at which Analytic Services commits data blocks (after the specified number of rows has been reached). | |
set io_access_mode |
Change the input/output setting you wish to use for the database. The change takes effect the next time the database is started. Buffered I/O uses the file system's buffer cache, and is the default. Direct I/O bypasses the file system's buffer cache, and is able to perform asynchronous, overlapped I/Os, providing faster response time and more potential to optimize cache sizes for databases. If you set a database to use direct I/O, Analytic Services will attempt to use direct I/O each time the database is started. If direct I/O is not available on your platform at the time the database is started, Analytic Services will use buffered I/O, which is the default. For important information about how I/O settings affect database migration and cache sizes, please see the Installation Guide. |
|
set variable | Change the value of an existing subsitution variable on the database. The value must not exceed 256 bytes. It may contain any character except a leading ampersand (&). | |
set default calculation |
Change the default calculation (which, by default, is CALC ALL; ) to
the stored calculation script you specify, or to an anonymous (unstored) calculation
string. |
|
set active alias_table | Set an alias table as the primary table for reporting and any additional alias requests. Only one alias table can be used at a time. This setting is user-specific; it only sets the active alias table for the user issuing the statement. | |
set ha_trace level |
Enable logging of queries generated by Hybrid Analysis operations, such as
running a report involving relationally stored members, or drilling into a spreadsheet
containing relationally stored members. The queries are logged into the file essha.log , which is found in
the root Analytic Services installation directory, essbase .
The level option controls the amount of information written to |
|
set ha_trace off | Turn off logging of queries generated by Hybrid Analysis operations. | |
set performance statistics enabled | Turn on performance-statistics gathering. You might do this when you want to tune the system, change hardware configuration, or monitor I/O. The measurement begins for current processes as soon as you enable it. Any subsequent queries for statistics return measurements spanning from the time of enablement to the time of the query. Performance statistics can be retrieved using query database. | |
set performance statistics disabled | Turn off performance-statistics gathering. This halts the collection of statistics; it does not prevent anyone from retrieving old statistics using query database. | |
set performance statistics mode to <PST-SPEC>
|
Reset performance statistics gathering for a specified
persistence and scope. Each of the statistics tables available using query
database has a pre-defined persistence and scope.
When you use set performance statistics mode , you select the persistence and
scope to reset, and the collecting of measurements starts over for the applicable tables.![]() |
|
set note | Create an informational note about the database that Spreadsheet Add-in users can see from
the login dialog box. For example, 'Calc in progress: do not update.' Database notes can be up to 64 kilobytes long.
|
|
reset | Clear all data and linked-reporting objects from the database, but preserve the outline. | |
reset all | Clear all data, linked reporting objects, and the outline. | |
reset data | Same as using reset .
|
|
validate data to local logfile... |
Create a local log file with all index combinations for which blocks contain invalid block headers.
Before using this MaxL statement, be sure that the server is not performing other operations, such as calculations or dataloads; otherwise, an exception error may occur. The recommended procedure is:
For example, alter application sample disable commands; alter system logout session on database sample.basic; alter database sample.basic validate data to local logfile 'invalid_blocks'; alter database sample.basic repair invalid_block_headers; |
|
validate using... |
Check the database for data and structural integrity. A file is created containing error
messages if there are problems. The default error file is VALIDATE.LST in the
application\database directory. For example:
ESSBASE\APP\sample\basic\VALIDATE.LST .The validate utility verifies the following:
|
|
repair invalid_block_headers |
Delete all blocks that have invalid headers. Before using this statement, see validate data to local logfile... | |
recover freespace | Explicitly recover database freespace in the event of a crash or abnormal shutdown. Beginning with Release 7.0, freespace recovery only occurs if you explicitly request it. | |
force restructure | Explicitly restructure the database to eliminate or reduce fragmentation. | |
load alias_table |
Load an alias table from a file to the current database. The feeder file (FILE-NAME)
must follow these rules:
Sample contents of a feeder file for loading an alias table: $ALT_NAME "400-10" Guava "400-20" Tangerine "400-30" Mango $END |
|
unload alias_table | Delete the specified alias table. | |
add variable |
Create a database-level substitution variable by name, and optionally assign a string value for the variable to represent. You can assign or change the value later using set variable . A substitution variable acts as a global placeholder for information that changes regularly. Substitution variables may be referenced by calculations and report scripts.
|
|
drop variable | Remove a substitution variable and its corresponding value from the database. | |
add disk volume |
Add a disk volume definition if you want to allocate storage across multiple volumes, or restrict space used on a volume. After adding a disk volume definition, use set disk volume to place restrictions on files stored on the disk volume. |
|
drop disk volume |
Remove a disk volume definition. If no disk volume is defined, data and index files are stored in the database directory (for example, $ARBORPATH/app/sample/basic ). |
|
set disk volume |
Specify what types of files should be stored on the disk volume. You can allocate storage for index files, data files, or both. You can specify the maximum file size and partition size allowed on the disk volume. | |
delete lro | Delete linked reporting objects linked to the active database for a given user name or modification date. | |
unlock all objects | Unlock all objects on the database that are in use by a user or process. | |
rename to | Rename the database. When you rename a database, the database directory is also renamed. | |
comment | Create a description of the database. The maximum number of characters is 80. This description is available to database administrators. To annotate the database for Spreadsheet Add-in users, use set note . |
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |