Using MaxL Data Definition Language

In This Section:

The MaxL DDL Language

The MaxL Shell

The MaxL Perl Module

Also see the MaxL DDL section of the Oracle Essbase Technical Reference.

The MaxL DDL Language

The MaxL data definition language is an interface for making administrative requests to Essbase using statements rather than a series of commands with complicated arguments.

Using MaxL, you can automate administrative operations on Essbase databases. You can write MaxL scripts with variables to make them customizable and reusable.

For Essbase to receive and parse MaxL statements, you must “pass” them to the Essbase Server using either the MaxL Shell (essmsh), Administration Services, or a customized Perl program that uses the MaxL Perl Module, which enables you to embed its statements in Perl programs.

Overview of Statements

All MaxL DDL scripts and interactive sessions comprise a login and a sequence of statements, each terminated by a semicolon and consisting of grammatical sequences of keywords and variables. Statements are similar to English sentences; for example,

create or replace user user-name identified by password;

MaxL statements begin with a verb, such as create or alter, which indicates the type of operation to perform. Then you specify an artifact, such as database or user, which indicates the Essbase elements you are working with. The rest of the statement provides details about the action to perform, using a grammatically correct sequence of statement parts, or tokens.

For an overall picture of grammar requirements and the verb-artifact structure of MaxL statements, see the MaxL DDL section of the Oracle Essbase Technical Reference.

Components of Statements

The MaxL parser recognizes and requires an ordered presentation of tokens, which are the components of statements. A token is a space-delimited sequence of valid characters that is recognized by MaxL as a single readable unit. Tokens can be any of the following:

  • Keywords

  • Names

  • Strings

  • Numbers

Keywords

Keywords are the reserved words that make up the MaxL vocabulary. These include verbs, artifacts, and other words needed to construct statements. Keywords in MaxL are independent of your data; conversely, you must define all other MaxL tokens (names, for example).

The MaxL parser expects to see MaxL keywords and other tokens in their correct grammatical order, as diagrammed in MaxL topics in the Oracle Essbase Technical Reference.

Figure 150, Example of MaxL Syntax Diagram: Alter Filter shows a sample syntax diagram from the Oracle Essbase Technical Reference. Only the lowercase words in the diagram represent keywords. The other elements are placeholders for names or values that you provide.

Figure 150. Example of MaxL Syntax Diagram: Alter Filter

This image shows the syntax diagram for the alter filter MaxL statement.

Note:

Keywords are not case-sensitive; the use of lowercase for keywords is a documentation convention. See “How to Read MaxL Railroad Diagrams” in the Oracle Essbase Technical Reference.

Names

Names in MaxL are used to uniquely identify databases and database artifacts, such as users, applications, or filters.

Rules for Names

Unless you enclose a MaxL name within single quotation marks, a MaxL name is a string that must begin with an alphabetic character or the underscore. Names that are not enclosed in quotation marks may contain only alphabetic characters, numbers, and the underscore.

When enclosed in single quotation marks, a name may contain white space and any of the following special characters:

.
,
;
:
% 
$
"
'
*
+
-
=
<
>
[
]
{
}
(
)
?
!
 /
 \
|
 ~
`
#
&
@
^

Note:

Any name that is also a MaxL keyword must be enclosed in single quotation marks. For a list of keywords, see the Oracle Essbase Technical Reference.

Examples:

The following application names do not require single quotation marks:

Orange
Orange22
_Orange

The following application names do require single quotation marks:

Orange County(because the name contains a space)
22Orange (because the name begins with a number)
variable (because the name is a MaxL keyword)
Types of Names

Some Essbase artifacts have single names, and some require compound names known as doubles and triples, which express the nesting of namespaces.

A singleton name can be meaningful in a system-wide context—the artifact to which it refers may be global to Essbase—or it needs no specified application or database context. For example, an application has a singleton name because it need not be considered in the context of another application or database.

A double is two names connected by a period, and a triple is three names connected by two periods. Doubles and triples show the inherited namespace of the named entity. For example, a database usually is identified using two names. The first identifies the application in which the database resides, and the second is the database name; for example:

Sample.Basic

Database artifacts, such as filters, usually are identified using triple names: the first two names identify the application and database, and the third is the artifact name. Therefore, a filter name could look like this:

sample.basic.filter3

Table 162 shows the type of name required for the most common artifacts and provides an example of the name used in a statement.

Table 162. Name Requirements for Artifacts

Artifact

Name

Example

User

singleton

create user Fiona identified by 'password';

Group

singleton

alter user Fiona add to group Managers;

Host

singleton

drop replicated partition Samppart.Company from Sampeast.East at EastHost;

Application

singleton

create application '&New App';

Database

double

display database '&New App'.testdb;

Calculation

triple

drop calculation Sample.basic.'alloc.csc';

Filter

triple

display filter row sample.basic.filter1;

Function (local)

double

drop function sample.'@COVARIANCE';

Function (global)

singleton

create function '@JSUM' as 'CalcFnc.sum'; 

Location alias

triple

drop location alias Main.Sales.EasternDB;

Role

singleton

grant designer on database Sample.basic to Fiona;

Substitution variable

singleton

alter system add variable Current_month; 
alter system set variable Current_month July;

Disk volume

singleton to define, triple to display

alter database AP.main1 add disk volume G;
alter database AP.main1 set disk volume G partition_size 200mb;
display disk volume sample.basic.C;

Strings

Strings are used in MaxL statements to represent the text of comments, member expressions, calculation scripts, and file references. Strings can begin with any valid character. As with names, strings containing white space or special characters must be enclosed in single quotation marks.

See Rules for Names for a list of valid special characters.

Table 163 shows examples of statement elements that are strings:

Table 163. Examples of String in MaxL Statements

Type of String

Example

Password

create user Fiona identified by sunflower;

Comment

alter group Financial comment 'Reports due July 31';

Member expression

create filter sample.basic.filt1 read on 'Sales,@ATTRIBUTE(Bottle)'; 
create or replace replicated partition sampeast.east area '@IDESC(East), @IDESC(Qtr1)' to samppart.company mapped globally '("Eastern Region")' to '(East)';

Body of a calculation

execute calculation
 '"Variance"=@VAR(Actual, Budget);
  "Variance %"=@VARPER(Actual, Budget);'
on Sample.basic;

File reference

spool on to '/homes/fiona/out.txt';

Numbers

You use numbers in MaxL statements to change certain database settings in Essbase. For example, you can change cache and buffer sizes or set system-wide intervals such as the number of days elapsing before users are required to change their passwords. To change numeric settings, you can use positive integers, positive real numbers, and zero. Decimals and scientific notation are permitted.

Examples:

1000 
2.2 
645e-2

For size settings, units must follow the number. Spaces between numbers and units are optional. Units are case-insensitive and may include the following: B/b (bytes), KB/kb (kilobytes), MB/mb (megabytes), GB/gb (gigabytes), and TB/tb (terabytes). If no units are given, bytes are assumed.

Examples:

1000 b 
5.5GB 
645e-2 mb 
145 KB 
2,000e-3TB

This section helps you review what you have learned about statements by illustrating MaxL statements and their components, in template form. In the diagrams, lowercase words are keywords, and uppercase words are intended to be replaced with the appropriate values, as shown in the example following each illustration.

Altering a Database

Figure 151. MaxL statement to change data file cache size

This image shows a MaxL statement for changing the size of the data file cache (alter database with the set_data_file_cache_size grammar).

Example:

alter database Sample.Basic set data_file_cache_size 32768KB;

Granting a Permission

Figure 152. MaxL statement to grant application permissions to a user

This image shows a MaxL statement for granting applications permission to a user (grant with on application grammar).

Example:

grant designer on application Sample to Fiona;

Creating a Calculation

Figure 153. MaxL statement to create a stored calculation

This image shows a MaxL statement for creating a stored calculation (create calculation).

Example:

create calculation sample.basic.varcalc
'"Variance"=@VAR(Actual, Budget);
"Variance %"=@VARPER(Actual, Budget);'
;

This section shows you how to get started using most of the features of the MaxL Shell. Also see the Oracle Essbase Technical Reference.

This section does not discuss using the Administration Services MaxL Script Editor. See the Oracle Essbase Administration Services Online Help.

Starting the MaxL Shell

The MaxL Shell can be invoked to take input in these ways:

  • Interactively, from the keyboard

  • From a MaxL script file (statements are read from the file specified on the command line)

  • From standard input that is piped to the MaxL Shell from the output of another program

The MaxL Shell also accepts command-line arguments at invocation time. These can be used with positional parameters to represent any name, or a password.

Starting the Shell for Interactive Input

  To enter MaxL statements interactively at the command line, invoke the shell at your operating-system prompt.

For example:

essmsh

  To enter MaxL statements interactively after logging in at invocation time, use the -l flag.

For example:

essmsh -l Admin password
...
49 - User logged in: [Admin].

  To enter MaxL statements interactively and supply command-line arguments to represent variables that you will use in your interactive session, use the -a flag.

For example:

essmsh -a Admin password Sample.Basic 
...
login $1 $2;

49 - User logged in: [admin].

alter database $3.$4 enable aggregate_missing;

72 - Database altered: ['sample'.'basic'].

In the above example, $1, $2, $3, and $4 are positional parameter variables representing arguments entered after essmsh at invocation time, in the order in which they were entered.

Starting the Shell for File Input

  To invoke the MaxL Shell to take input from a MaxL script file, type essmsh followed by the name of a MaxL script in the current directory, or, the full path and file name of a MaxL script in another directory.

If you provide only a file name, the MaxL Shell assumes that the file is in the current directory (the directory the operating-system command prompt was in when essmsh was invoked). In the following invocation example, the file maxlscript.msh must be in C:\.

C:\> essmsh maxlscript.msh

If the MaxL script is not in the current directory, provide a path to the MaxL script. You can use absolute paths or relative paths.

For example:

$ essmsh ../Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer/test.msh

Note:

MaxL scripts do not require a particular—or any—file extension. This document uses.msh.

In UNIX shells, place single quotation marks around the path to avoid file-reading errors.

In a Windows command prompt, if the path to the script contains a space, you may have to use double quotation marks around the entire path and file name to avoid file-reading errors.

Starting the Shell for Programmatic Input

  To invoke the MaxL Shell to take input from the standard output of another program or process, use the -i flag.

For example:

program.sh | essmsh -i

The shell script program.sh may generate MaxL statements as output. The shell script output is piped to essmsh -i, which uses that output as its input. This allows for efficient co-execution of scripts.

Windows Example Using -i Invocation

The following Windows batch script generates a login statement and a MaxL display statement as its output. The -i flag enables that output to be used by essmsh, the MaxL Shell, as input.

echo login admin password on localhost; display privilege user;|essmsh -i

User Admin is logged in, all user privileges are displayed, and the MaxL session is terminated.

UNIX Example Using -i Invocation

The following portion of a shell script ensures that there are no applications on the system by testing whether the display application statement returns zero applications.

if [ $(echo "display application;" | 
essmsh -l admin password -i 2>&1 | 
awk '/Records returned/ {print $7}' ) != "[0]." ]
then
   print "This test requires that there be no applications."
   print "Quitting."
   exit 2
fi

Explanation:

  1. MaxL grammar is piped to a MaxL Shell invocation and login, as the output of the UNIX echo command.

  2. The results of the MaxL session are tested by awk for pattern-matching with the MaxL status message you would get if you entered display application on an empty system: Records returned: [0] .

  3. Awk matches the string 'Records returned: ', and then checks to see if that is equal to '[0].'

  4. If $7 (a variable representing the fifth token awk finds in the status string) is equal to '[0].', there are no applications on the system; otherwise, $7 would equal '[1].' or whatever number of applications exist on the system.

For more information and examples on invocation options, see the Oracle Essbase Technical Reference. Invocation information is also contained in the essmsh “man page”. To view the man page, enter essmsh -h | more at the operating-system command prompt.

Logging In to Essbase

The MaxL language interpreter requires a connection to an Essbase session before it can begin parsing MaxL statements. Use the MaxL Shell to establish the connection to Essbase.

  To log on to Essbase after the command shell has been started, use the shell login grammar.

For example:

essmsh
   
MAXL>login Fiona identified by sunflower on hostname;

If a host name is not specified, localhost is assumed.

  To log on when you invoke the shell, use the -l option. To log on to a server besides localhost at invocation time, use the -s option. To set a message level, use -m.

For example:

essmsh -l fiona sunflower -s myHost -m error

Note:

You can log out and change users without quitting the shell.

When a user logs in to Essbase through a MaxL Shell session and Essbase is restarted while the user is still logged in, the next login through the same MaxL Shell session is delayed by four seconds.

For more information about MaxL Shell invocation options, see the Oracle Essbase Technical Reference.

Command Shell Features

The MaxL Shell includes command-line argument processing, environment variable processing, nesting of MaxL scripts, and shell escapes. These features offer the flexibility needed to create a highly automated Essbase production environment.

See the Oracle Essbase Technical Reference.

Nesting MaxL Scripts

As a DBA, you may want to save your separate automated tasks in several MaxL scripts, rather than executing many operations from a single script. Putting the pieces together is simple if you know how to reference one MaxL script from another.

  To reference or include other MaxL scripts within the current MaxL session, use the following MaxL Shell syntax:

    msh <scriptfile>;

Note:

Because msh is a shell command, it is limited to the originating session. Therefore, you should not reference MaxL scripts that contain new login statements.

Spooling Output to a File

You can create a log file of all or part of a MaxL session and its associated messages by spooling output to a file.

  To record a MaxL session:

  1. log on to Essbase. For example,

    login fiona sunflower;
  2. Begin spooling output, using spool on to <filename>. For example:

    spool on to 'C:\\output\\display.txt';
  3. Enter as many MaxL statements as you want recorded.

  4. Stop spooling output, using spool off;.

MaxL statements and their output are logged to the output file when you issue the spool command, either in an interactive session or in a script. However, MaxL Shell commands and output are logged only if you spool during an interactive session. MaxL Shell commands and output are ignored in log files created from script sessions. Additionally, output from any operating-system commands you may have included is ignored in the log files of both interactive and script sessions.

Including Operating-System Commands

You can issue operating-system commands directly from a MaxL session. The operating-system output becomes part of the MaxL Shell output. When the operating system finishes executing commands, it returns control to essmsh.

  To escape to the operating system from within a MaxL session, use shell.

For example, to run the UNIX date command from a MaxL script:

shell date;

  To escape to ESSCMD from within a MaxL session use single quotes:

For example:

shell esscmd ‘../scripts/test.scr’;

Using Variables to Customize MaxL Scripts

In the MaxL Shell, you can use variables as placeholders for data that is subject to change or that you refer to often; for example, the name of a computer, user names, or passwords. You can use variables in MaxL scripts and during interactive sessions. Using variables in MaxL scripts eliminates the need to create customized scripts for each user, database, or host. Variables can be environment variables (for example, $ESSBASEPATH, which references the Essbase installation directory), positional parameters (for example, $1, $2, and so on), and locally defined shell variables. A variable always begins with a $ (dollar sign) when you reference it.

For more information about using variables in the MaxL Shell, see the Oracle Essbase Technical Reference.

Stopping the MaxL Shell

You can log out of a MaxL session or log on as another user without quitting the shell. You should include a logout statement at the end of MaxL scripts. You need not exit at the end of MaxL script files, or after a session using stream-oriented input from another program’s output.

  To log out without exiting the MaxL Shell, enter:

    logout;

  To exit from the MaxL Shell after using interactive mode, enter:

    exit;

The MaxL Perl Module

With the MaxL Perl Module (Essbase.pm), you can embed the MaxL language within Perl programs, offering more programmatic control than is available in the shell.

Essbase.pm, in the Perlmod directory, enables Perl programmers to wrap MaxL statements in Perl scripts. Database administration with MaxL becomes as efficient and flexible as your Perl programs.

While you administer Essbase databases, Perl with MaxL enables you to take advantage of these and other programmatic features:

  • Conditional testing

  • Interprocess communication

  • Message handling

  • E-mail notification

  • Web scripting

Essbase.pm contains methods that enable passing MaxL statements by means of Perl:

  • connect () establishes a connection to Essbase

  • do () tells Perl to execute the enclosed MaxL statement

  • pop_msg () navigates through the stack of returned MaxL messages

  • fetch_col (), fetch_desc (), and fetch_row () retrieve information from MaxL display output tables

  • disconnect () terminates the connection to Essbase

To make the Perl methods available to Essbase, include a reference to Essbase.pm in the Perl program. Place the following line at the top of each Perl script:

use Essbase;

Perl is not difficult to learn, especially if you have knowledge of UNIX shells or other programming languages. To download Perl and learn more about it, visit the Comprehensive Perl Archive Network Web site at http://www.cpan.org/.

See the MaxL DDL section of the Oracle Essbase Technical Reference and the Readme file in the PERLMOD directory of your Essbase installation.