Query Database

The MaxL query database statement helps you retrieve advanced information about the current state of an Essbase database that is running.

Click here for aggregate storage version

This statement requires the database to be started.

Syntax

Description of the illustration qrydb.gif

Keywords

You can query for database information in the following ways using MaxL query database. The minimum application permission required for most query database actions is Database Access, with exceptions noted.

query database DBS-NAME get active alias_table

Display the active alias table for the user issuing the statement.

Example:

query database Sample.Basic get active alias_table;

query database DBS-NAME get attribute_info MEMBER-NAME

Get attribute member, dimension, and name information for the specified attribute member.

Example:

query database Sample.Basic get attribute_info 'Ounces';

query database DBS-NAME get attribute_spec

Display the current attribute specifications for the database. These specifications include attribute member name format, Attribute Calculation dimension member names, Boolean and date member names, and numeric range specifications.

Example:

query database Sample.Basic get attribute_spec;

query database DBS-NAME get currency_rate

Display the currency rate for every currency partition. The database must be started.

Example:

query database Sample_Currency.Interntl get currency_rate;

query database DBS-NAME get dbstats dimension

Get information about dimensions.

Output

The index_type field values are numeric, and translate as follows:

0   Dense
1               Sparse
3               None (database is aggregate storage)

query database DBS-NAME get dbstats data_block

Get information about data blocks. The information returned has little relevance to aggregate storage databases.

Output

The type field values are numeric, and translate as follows:

0               Array
1               AVL (or "B+ Tree")

query database DBS-NAME get default calculation

View the contents of the calculation designated as default for the database. The default calculation refers to either the relations defined in the database outline (CALC ALL) or to the set of calculation strings defined as the default database calculation.

Example:

query database Sample.Basic get default calculation;

query database DBS-NAME get member_info MEMBER-NAME

Get information on a specific member.

Example:

query database Sample_Dynamic.Basic get member_info 'Profit per Ounce';

Output Fields for Member Info Query in MaxL

The unary_type field values are numeric, and translate as follows:

0               Add
1               Subtract
2               Multiply
3               Divide
4               Percent
5               NoRollUp

The member_tag_type field values translate as follows:

0               SkipNone
16384           SkipMissing
32768           SkipZero
49152           SkipBoth
1               BalFirst
2               BalLast
4               TwoPass
8               Average
64              Expense

Variations are possible. The field value consists of one of the first four "skip" values plus any/all/none of the last five values. Some examples:

0               SkipNone
77              SkipNone, BalFirst, TwoPass, Average, Expense
16385           SkipMissing and BalFirst

The first four "skip" values are base values, and added to them are combinations of 1, 2, 4, 8, and 64.

The status field values are hexadecimal, and translate as follows:

0               Normal
1               Never Share
2               Label
4               Refer Share
8               Refer Share (with different name)
16              Implicit share
32              Virtual Member (stored)
64              Virtual Member (not stored)
2048            Attribute
32768           Referred

query database DBS-NAME get member_calculation MEMBER-NAME

View the formula associated with the selected member.

Example:

query database sample.basic get member_calculation 'Profit per Ounce';

Displays the formula associated with the 'Profit per Ounce' member.

query database DBS-NAME get estimated size

Get an estimate of the cube size; that is, display an estimate of the number of blocks a database will create after full calculation (CALC ALL). The estimate is based on the number of blocks that exist before calculation. The database can have all data loaded, or it can have a random sampling of data loaded. Outlines that contain sparse formulas of any type, or top-down formulas are not supported (results of the estimation on such databases may be invalid).

Example:

query database Sample.Basic get estimated size;

query database DBS-NAME get performance statistics TABLE-TYPE table

Display one of several choices of performance statistics tables returning detailed information about the block storage cube statistics. Before you can use this statement, you must enable performance statistics gathering, using alter database DBS-NAME set performance statistics enabled.

Example:

alter database Sample.Basic set performance statistics enabled;

query database Sample.Basic get performance statistics dynamic_calc table;

query database DBS-NAME list alias_table

Get a list of alias tables that are defined for the database.

Example:

query database Sample.Basic list alias_table;

query database DBS-NAME list alias_names in alias_table …

List the alias names defined in an alias table. Alias tables contain sets of aliases for member names and are stored in the database outline. Use this grammar to see a list of alias names defined in the specified table.

Example:

query database Sample.Basic list alias_names in alias_table 'Long Names';

query database DBS-NAME list lro

Get information about linked objects, including the object type, name, and description, based on criteria you specify. If you specify both a user name and modification date, objects matching both criteria are listed. If you specify no user name or date, a list of all linked objects in the database is displayed.

Example:

query database sample.basic list lro before '06_16_2008';

Displays information about linked objects, in the Sample.Basic database, that were modified before the specified time.

query database DBS-NAME list <all | data | index> file information

Get accurate index and data file information. Provides index and data file names, counts, sizes, and totals, and indicates whether or not each file is presently opened by Essbase. The file size information is accurate. Note that the file size information provided by the Windows operating system for index and data files that reside on NTFS volumes may not be accurate.

Example:

query database Sample.Basic list all file information;

query database DBS-NAME list transactions

Display, in the MaxL Shell window, database transactions that were logged after the time when the last replay request was originally executed or after the last restored backup's time (which ever occurred later).

Example:

query database Sample.Basic list transactions;

To use this statement, you must have enabled transaction logging using alter database.

Note:

Transaction logging and replay is available only for backward compatibility support in Essbase 21c. Features added after Essbase 11g On-Premise are not supported for transaction logging and replay; including (but not limited to):

Batch outline editing
Application workbooks and Cube Designer activity
Scenario management
External data load using a Datasource
REST API data loads and other updates
Federated partitions
MDX inserts
Drill through transactions

query database DBS-NAME list transactions after LOG-TIME

Display database transactions that were logged after the specified time. Enclose the TIME value in quotation marks; for example: '11_20_2007:12:20:00'

query database DBS-NAME list transactions after LOG-TIME write to file PATHNAME_FILENAME

Write the list of database transactions to the specified file. The list output is written to a comma-separated file on the Essbase Server computer.

Provide the full pathname to an existing directory and the name of the output file. If only the output file name is provided, Essbase writes the file to the application directory.

When writing to an output file that already exists, you must use the force grammar to overwrite the file.

Example:

query database Sample.Basic list transactions after '11_20_2007:12:20:00'
write to file 'C:\\Hyperion\\products\\Essbase\\EssbaseServer\\app\\Sample\\Basic\\listoutput.csv';

query database DBS-NAME list transactions force write to file PATHNAME_FILENAME

Overwrite the contents of an existing output file.

query database DBS-NAME list transactions after TIME...write to file PATHNAME_FILENAME

Write the list of database transactions that were logged after the specified time to the specified file.