Alter Database

Click here for aggregate storage version

Change database-wide settings. Permission required: Database Manager.

Syntax

 

DISK VOLUME

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.

Examples

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.

Descriptions

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, "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. 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 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 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 essbase.cfg.

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:

  • Commits any modified data to disk.
  • Switches the database to read-only mode. The read-only state persists, even after the current session ends, until it is changed back to read-write using end archive.
  • Reopens the database files in shared, read-only mode.
  • Creates a file containing a list of files that need to be backed up. Unless a different path is specified, the file is stored in the database directory.

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.
This statement requires the database to be started.

End-archive achieves the following outcomes:

  • Returns the database to read-write mode.
  • Re-opens database files in exclusive, read-write mode.
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 (on 32-bit platforms), and 20KB (on 64-bit platforms). 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 Analytic Services Query Designer use the retrieval sort buffer. The default size is 10 KB (on 32-bit platforms), and 20KB (on 64-bit platforms). 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, AnalyticServices.

The level option controls the amount of information written to essha.log. Level high should be used only for debugging purposes and should be rarely used because it can quickly fill up the log file. Level low is recommended.

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:

  1. Disable all logins.
  2. Forcibly log off all users.
  3. Run the MaxL statement to get invalid block header information.
  4. Repair invalid block headers, if applicable.

For example,

alter application sample disable connects;
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: AnalyticServices\APP\sample\basic\VALIDATE.LST.

The validate utility verifies the following:
  • That blocks, sections, block type, block length, and floating-point numbers are valid.
  • That the index contains an entry for every data block.
  • That keys in the index page are matched with keys in the corresponding data blocks. Keys out of order indicate corruption.
  • Structural integrity of index freespace information.
  • Structural integrity of the LRO catalog.
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:
  • Must be correctly formatted.
  • Must be located on the Analytic Server computer, not on a client computer.
  • FILE-NAME must include the full path.

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.

If substitution variables with the same name exist at server, application, and database levels, the order of precedence for the variables is as follows: a database level substitution variable supersedes an application level variable, which supersedes a server level variable.

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.
appname.dbname appname.dbname positive integer