Alter Database Set

The MaxL alter database statement under the set keyword helps you modify Essbase database-wide settings.

Click here for aggregate storage version

Syntax

Description of the illustration altdb_set.gif

Keywords

Use MaxL alter database set to change the following database-wide settings. The minimum application permission required for most of the statements is Database Manager, with exceptions noted.

alter database DBS-NAME 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 20 KB. The minimum size is 2 KB. Increasing the size may improve retrieval performance.

Example:

alter database Sample.Basic set retrieve_buffer_size 20kb;

alter database DBS-NAME 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 20 KB. The minimum size is 2 KB. Increasing the size may improve retrieval performance.

Example:

alter database Sample.Basic set retrieve_sort_buffer_size 20kb;

alter database DBS-NAME set data_cache_size ...

Change the data cache size. The data cache is a buffer in block storage memory that holds uncompressed data blocks. You need at least Application Manager permission to set it. Essbase Server allocates memory to the data cache during data load, calculation, and retrieval operations as needed. The default and minimum size is 3072 KB.

Example:

alter database Sample.Basic set data_cache_size 3072kb;

alter database DBS-NAME set index_cache_size ...

Change the index cache size. The index cache is a buffer in block storage memory that holds index pages. You need at least Application Manager permission to set it. When a data block is requested, Essbase looks at the index pages in the index cache to find its location on disk.

Minimum index cache size is: 1 MB (1,048,576 bytes)
Maximum index cache size is: 256 TB

Default index cache size is: 1 MB (1,048,576 bytes)

Example:

alter database Sample.Basic set index_cache_size 1mb;

alter database DBS-NAME set currency_database ...

Link the cube with a currency cube, enabling conversion of currency values from one currency into another.

Example:

alter database Sample_Currency.Interntl set currency_database Xchgrate;

alter database DBS-NAME 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 main cube.

Example:

alter database Sample_Currency.Interntl set currency_member 'Act xchg';

alter database DBS-NAME set currency_conversion

Specify the method of currency conversion: to multiply, or divide, the exchange rates with the main cube. Indicate using keyword multiplication or division.

Example:

alter database Sample_Currency.Interntl set currency_conversion multiplication;

alter database DBS-NAME 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. This statement is supported for use in EPM Shared Services security mode only.

Example:

alter database Sample.Basic set minimum permission no_access;

alter database DBS-NAME set compression rle

Set the database to use run-length encoding (RLE) compression. Essbase 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, Essbase expands the block to its full size, regardless of the scheme that was used to compress it.

Example:

alter database Sample.Basic set compression rle;

alter database DBS-NAME set compression bitmap

Set the database to use bitmap compression, the default. Essbase stores only non-missing values and uses a bitmapping scheme.

When a compressed data block is brought into the data cache, Essbase expands the block to its full size, regardless of the scheme that was used to compress it.

Example:

alter database Sample.Basic set compression bitmap;

alter database DBS-NAME 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:

Smart View and other grid clients' data-update operations are always in committed mode.

Example:

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.

alter database DBS-NAME set implicit_commit after <number> blocks

When uncommitted access is enabled, set the frequency at which Essbase commits data blocks (after the specified number of blocks has been reached).

The default frequency, if unspecified, is 3000, and may adjust dynamically during a calculation.

If Essbase Server runs on Oracle Exalytics In-Memory machine, for calculation and data load requests, the commit happens at the end of the command or request, and the default interval of 3000 (or any other value you specify) is ignored.

Example:

alter database Sample.Basic set implicit_commit after 3000 blocks;

alter database DBS-NAME set implicit_commit after <number> rows

When uncommitted access is enabled, set the frequency at which Essbase commits data blocks (after the specified number of rows has been reached).

Example:

alter database Sample.Basic set implicit_commit after 50 rows;

alter database DBS-NAME set variable …

Change the value of an existing substitution variable on the database. The value must not exceed 256 bytes. It may contain any character except a leading ampersand (&).

Example:

alter database Sample.Basic set variable CurrMonth 'Oct';

alter database DBS-NAME 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.

Examples:

alter database Sample.Basic set default calculation 'CalcAll';

alter database Sample.Basic set default calculation as calc_string 'CALC ALL;';

alter database DBS-NAME 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.

Example:

alter database Sample.Basic set active alias_table 'Long Names';

To check which alias table is active, use query database.

alter database DBS-NAME 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.

Example:

alter database Sample.Basic set performance statistics enabled;

alter database DBS-NAME 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.

Example:

alter database Sample.Basic set performance statistics disabled;

alter database DBS-NAME 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.

Description of the illustration pstspec.gif

Example:

alter database Sample.Basic set performance statistics mode to default;

alter database DBS-NAME set note

Create an informational note about the database that Smart View or other grid client users can see from the login dialog box. The note can be up to 64 kilobytes long.

Example:

alter database Sample.Basic set note 'Calc in progress';