Alter Database (Aggregate Storage)

Click here for non-aggregate storage version

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

Syntax

 

Examples

alter database AsoSamp.Sample clear aggregates;

Deletes all aggregate views of AsoSamp.Sample.

alter database AsoSamp.Sample initialize load_buffer with buffer_id 1;

See Loading Data Using Buffers for more information.

alter database AsoSamp.Sample disable query_tracking;

Turns off the harvesting of query data for database AsoSamp.Sample.

Descriptions

You can change the following database-wide settings using alter database.

Key Phrase Explanation
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 query_tracking Begin collecting query data for this database, to be used for query-based view optimization.

To utilize the results of query tracking, use the optional based on query_data clause found in either of the following statements:

Query tracking is disabled by default.

disable query_tracking Stop collecting query data for query-based view optimization. Query tracking is disabled by default.
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 Analytic Services 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 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 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 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.
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.
clear aggregates Delete all aggregate views.
compact outline Compact the outline file to remove the records of members that have been deleted. Compaction helps keeps the outline file at an optimal size.
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.
initialize|destroy load_buffer Create or destroy a temporary buffer in memory for loading data.
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.