Alter Database Set

Click here for aggregate storage version

Permission required: create_application.

Syntax

Description of the illustration altdb_set.gif

Use alter database set to change the following database-wide settings:

Keywords

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.

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.

data_cache_size

Change the data cache size. The data cache is a buffer in memory that holds uncompressed data blocks. 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.

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, Essbase looks at the index pages in the index cache to find its location on disk.

Minimum value: 1 MB (1,048,576 bytes)
Maximum value: 256 TB

Default value for buffered I/O: 1 MB (1,048,576 bytes)

Buffered I/O is the default for this release.

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.

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.

currency_conversion

Specify whether during currency conversion, the calculation method multiplies the currency database exchange rates with the main database values, or that the currency database exchange rates are divided by the main database values.

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.

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.

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.

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.

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.

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).

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 (&).

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.

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.

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.

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.

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

note

Create an informational note about the database that Smart View or other grid client 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.

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.