Defines database settings by number, providing more options than SETDBSTATE.
It is most efficient to load the required database before you run the SETDBSTATE command, then stop and restart the database for the command to take effect.
Note: | When changing sizes, valid size-entry units in ESSCMD are bytes (b), kilobytes (k), metabytes (m), gigabytes (g), or terabytes (t). Example: 8192b, 8k, 1m, ng, nt. If no size unit is given, the default unit is bytes (b). |
Syntax
SETDBSTATEITEM [optionNumber] ["appName"] ["dbName"] ["values"]
| Parameter | Description |
|---|---|
optionNumber | An integer between 0 and 27, inclusive. This number corresponds to the options listed below. Enter 99 to be prompted for all options (in interactive mode). |
appName | Name of the application. Omit if the application is already selected using the SELECT command. |
dbName | Name of the database; required if appName is specified. |
values | Acceptable value or values; these vary from option to option. See Values for Values Parameter |
Notes
Using the semicolon statement terminator (;) is optional in ESSCMD scripts. ; however, it is good practice to use it to signal the end of the parameter list. This is especially important if you omit some of the parameters, accepting their default values. If not all parameters are present, and the ; is omitted, ESSCMD looks for the remaining values in the next line, leading to unpredictable results.
Items 14 and 15 (Data Compression and Data Compression Type) are effective as soon as Essbase writes blocks to disk. This command has no effect on blocks already on disk until the next time Essbase writes them.
Items 18, 19, and 20 (Isolation level, Pre-Image Access, and Time Out) are effective the next time there are no active transactions in the database.
Items 5, 12, 21, and 22 (Data Cache Size, Index Cache Size, Blocks Modified Before Internal Commits, and Rows to Data Load Before Internal Commit) are effective when the database is stopped and re-started.
Item 13, Index Page Size, is no longer changeable. Input for this setting is ignored.
0. Abort—Returns you to the ESSCMD command line. Use only in interactive mode.
1. Description—Text string describing the database.
2. Allow Database to Start?—Sets whether the database is loadable. Values: Y/N.
3. Start Database with Application?—Sets autoload on or off. Values: Y/N.
4. Access Level—Values:
0 - None.
1 - Read.
2 - Write.
3 - Calculate.
4 - Database Manager.
5. Data Cache Size—The maximum size of a buffer in memory that holds data blocks for the current operation. Default and minimum: 3145728B (3 megabytes).
6. Aggregate Missing Values?—Sets whether to aggregate missing values. Values: Y/N.
7. Two Pass Calc When [CALC ALL]?— Sets whether or not to perform a second calculation on formulas tagged as "Two Pass" as part of the default calculation. Values: Y/N.
8. Create Blocks on Equation?—Sets whether to create blocks on equations. Values: Y/N.
9. Currency Database Name—Links a currency database that you specify.
10. Currency Conversion Type Member —Specifies the default currency conversion type member.
11. Currency Conversion Type—Sets the conversion method. Values:
0 - Division.
1 - Multiplication.
12. Index Cache Size—Maximum size of a memory buffer that holds index pages for the current operation. Default: 1048576 bytes (1 megabyte).
13. Index Page Size—This setting is no longer changeable.
14. Data Compression on Disk?—Enables (Y) or disables (N) data compression on disk.
15. Data Compression Type—Values:
1 - Run-Length Encoding.
2 - Bitmap (the default).
16. Retrieval Buffer Size—Specifies the size of the internal sorting buffer that holds extracted row data cells before they are evaluated by the RESTRICT or TOP/BOTTOM Report Writer command. Default: 10K (on 32-bit platforms), and 20K (on 64-bit platforms).
17. Retrieval Sort Buffer Size—Specifies the size of the internal data sorting buffer. Default: 10K (on 32-bit platforms), and 20K (on 64-bit platforms).
18. Isolation Level—Choose committed or uncommitted access to your database. Committed access provides better data integrity. Uncommitted access provides consistency with Release 4. See the Oracle Essbase Database Administrator's Guide for information about isolation levels. Values:
1 - Committed access
2 - Uncommitted access (the default)
Depending on which type of access you specify, ESSCMD prompts you for other parameters (or you can supply the values on the command line).
If you choose 1 (committed access), ESSCMD prompts for:
Pre-image access (see item 19).
Time Out (see item 20).
If you choose 2 (uncommitted access), ESSCMD prompts for:
Number of blocks modified before internal commit (Default: 3000).
A value of 0 means no implicit commit; Essbase commits blocks at the end of the transaction.
Number of rows to data load before internal commit. (Default: 0, no implicit commit; Essbase commits blocks at the end of the transaction).
19. Pre Image Access?—Valid for Committed access only. Provides users Read-only access to data blocks that are locked for the duration of another transaction. Users see the last committed data values for those data blocks. If you choose N (No), your transaction waits for the blocks to become available, or Essbase issues a time-out error. Values: Y/N. Default: N (No).
20. Time Out—The length of time, in seconds, to wait to acquire a lock on data blocks that are locked by another transaction. Acceptable values are:
-1 - Indefinite wait.
0 - Immediate access, or no wait.
n - A number of seconds that you specify.
21. Number of blocks modified before internal commit—Default: 3000. See item 18.
22. Number of rows to data load before internal commit—Default: 0. See item 18.
23. Add Disk Volume Definitions—Use if you want to allocate storage across multiple volumes, or restrict space used on a volume. For information on disk volumes, see the Oracle Essbase Database Administrator's Guide.
ESSCMD prompts you for the following values, unless you supply them on the command line:
The number of new disk volumes you want to add.
Then, for each volume:
Volume name or drive letter (required).
Volume size (maximum space to use on that volume). Default: Unlimited (0). Minimum: 8 megabytes. You can specify this value in bytes (B), kilobytes (K), megabytes (M, the default), or gigabytes (G).
File types to be stored on this volume:
1 - Index files only.
2 - Data files only.
3 - Index and data files (the default).
File size: the maximum size that each index or data file can attain before Essbase creates a new file. Default: 2G. Minimum: 8 megabytes. You can specify this value in bytes (B), kilobytes (K), megabytes (M, the default), or gigabytes (G).
24. Modify Disk Volume Information—Change the disk volume settings on an allocated volume. This command prompts you for the number assigned to the disk volume you want to change and then prompts you for each value for the chosen disk volume. See item 23. Use GETDBSTATE to see a list of the currently defined disk volumes, and the number assigned to each volume.
25. Delete Disk Volume Definition—Stop Essbase from storing additional files on an allocated volume. This command prompts you for: Volume Definition (n), where n is the number corresponding to the disk volume definition you want to remove.
For example, suppose you defined three volumes: first, C; then, E; then, D. Essbase considers D the third volume - definition number 3.
Note: | If you delete an application or database, Essbase does not remove the directory containing the application or database on a disk volume. The computer's operating system still shows the folder and file labels on the disk. However, you can reuse the same name of the application or database that you had removed on the disk volume. |
26. Cache Memory Locking—Enable or disable Cache Memory Locking. When enabled, this setting locks the memory used for the index cache, data file cache, and data cache into physical memory, improving database performance.
Values: Y/N Default: No
27. Data File Cache Size—
Specify the size, in bytes, for the Data File Cache. Minimum: 8388608 bytes. Default: 33554432 bytes. Recommended: Combined size of all ESS*.PAG files if possible; as large as possible otherwise.
99. All Items—Prompts for each option in turn. Use only in interactive mode.
Example
The following example enables Committed access and Pre-image access, and specifies indefinite wait time:
SETDBSTATEITEM 18 "JTEMP" "JTEMPDB" "1" "Y" "1";
The following example allocates up to 4 GB on Volume E, sets a maximum file size of 1 GB, and specifies that data files should be stored only on E:
SETDBSTATEITEM 23 "SAMPLE" "BASIC" "1" "E" "4G" "2" "1G"
The following examples set the data cache value to 45000000 bytes. In the first example, the SELECT command was used to select the application and database. In the second example, the application and database are specified in the SETDBSTATEITEM command line instead.
LOGIN "machinename" "admin" "password"; SELECT "Sample" "Basic"; SETDBSTATEITEM 5 45000000; LOGOUT; EXIT;
LOGIN "machinename" "admin" "password"; SETDBSTATEITEM 5 sample basic 45000000; LOGOUT; EXIT;
See Also