Using MaxL Data Definition Language

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

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

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

Orange
Orange22
_Orange

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

Sample.Basic

sample.basic.filter3

create user Fiona identified by 'password';

alter user Fiona add to group Managers;

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

create application '&New App';

display database '&New App'.testdb;

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

display filter row sample.basic.filter1;

drop function sample.'@COVARIANCE';

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

drop location alias Main.Sales.EasternDB;

grant designer on database Sample.basic to Fiona;

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

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 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:

create user Fiona identified by sunflower;

alter group Financial comment 'Reports due July 31';

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)';

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

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

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

Figure 151. MaxL statement to change data file cache size

Example:

alter database Sample.Basic set data_file_cache_size 32768KB;

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

Example:

grant designer on application Sample to Fiona;

Figure 153. MaxL statement to create a stored calculation

Example:

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

For example:

essmsh

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

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.

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

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.

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.

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

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

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.

    msh <scriptfile>;

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

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.

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.

shell date;

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

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.