Introduction
In “No Exit”, that French existentialist philosopher had it wrong, or he would have had he confined his thoughts to the awesomeness that is Essbase’s MaxL. Or, to paraphrase that other French philosopher, had he cultivated a MaxL garden, life might not have seemed so bleak.Surely when Sartre wrote “Hell is other people” he was just frustrated with the way MaxL (we know that all philosophers are at heart Essbase hackers, and all Essbase hackers are at heart philosophers – I’m not reaching too much, am I?) handles exiting out of a script, especially when there is an error.
Error handling in MaxL pre 11.1.2
The iferror and define label keywords have been in MaxL since almost the very beginning (I think they came in in Essbase 7, although MaxL got its first start in Essbase 6.5 – anyone who knows differently please write in care of this address).iferror
To quote My Very Favorite Bit of Essbase Documentation (MVFBED) aka the Tech Ref, iferror can trap:- Errors in MaxL statement execution
- Errors in MaxL Shell command execution, including:
- Errors in spool on/off, such as permission errors
- Errors in set column_width, such as invalid widths
- Errors in script nesting, such as permission errors or nonexistent include files
Note that MaxL is not going to trap data or dimension load errors. ‘Twould be nice if it could, but it doesn’t. So it goes.
define label
iferror branches to a label as defined, logically enough, by the define label keywords.Two great tastes that taste great together
Use iferror to branch to a label and then immediately quit the script; those labels are created with define label. Simple, right?A practical example
When I write MaxL code, I want the script to stop if it hits some kind of monumental error as continuing a process that has badly failed will likely only make things worse.The iferror statement only tests the precedent statement for error, so unlike more sophisticated languages MaxL can’t just declare error handling on and a single place to catch the errors. Remember, an statement that results in error followed by a successfully executed statement clears the error state.
What I end up doing is literally sticking an iferror after every statement. Yes, every statement. It looks ugly but it works (sort of the story of my coding life). Here’s an example:
/*
Purpose: Run calcs
Written by: Cameron Lackpour
Modified: A long time ago
Notes:
*/
/* Log in to Essbase */
login admin password on localhost ;
iferror "ErrorHandler" ;
/* Define STDOUT and STDERR outputs */
spool stdout on to "c:\\Automation\\Sample\\MaxLLog\\mshSample.log" ;
iferror “ErrorHandler” ;
spool stderr on to "c:\\Automation\\Sample\\MaxLError\\mshSample.err" ;
iferror "ErrorHandler" ;
/* only supervisors and developers may connect */
alter application Sample disable connects ;
iferror "ErrorHandler" ;
/* Run calculations */
execute calculation Sample.Basic.AdminAgg ;
iferror "ErrorHandler" ;
execute calculation Sample.Basic.OrgAgg ;
iferror "ErrorHandler" ;
/* Allow all users to connect */
alter application Sample enable connects ;
iferror "ErrorHandler" ;
/* Error handler label */
define label "ErrorHandler" ;
/* Leave the MaxL shell */
logout ;
exit ;
A better way in 11.1.2.x
There’s nothing new with iferror and define label as they work the same way as before.However, the exit statement now has a new piece of functionality that it didn’t have before 11.1.2 – the ability to pass a condition code on exit.
exit
Per MVFBED, “You can optionally set the exit status variable to a non zero argument to return an exit status to the parent shell.”The impact
What does that mean? Your calling script language is (hopefully) more sophisticated than MaxL in its error handling and branching (I’m looking at you VBScript, PowerShell, and Perl) and can react to the error code(s) you define. Wouldn’t it be nice if your calling code tried to fix things, or at least told you where (and maybe why) everything went pear-shaped instead of just saying, “It don’t work”?Some easy examples first
iferror and define label but no defined exit code
Here’s an example that tries to connect with a bad password (an extra s in the word “password”):/* MaxL with non-specific error codes */
/* Login with bad password */
login admin passsword on localhost ;
iferror 'ErrorHandler' ;
/* Create label */
define label 'ErrorHandler'
/* Quit MaxL */
exit ;
Run and response:
Essbase MaxL Shell 64-bit - Release 11.1.2 (ESB11.1.2.0.0B444)
Copyright (c) 2000, 2009, Oracle and/or its affiliates.
All rights reserved.
MAXL> login admin password on localhost ;
ERROR - 103 - Unexpected Essbase error 1051293.
ERROR - 1051293 - Login fails due to invalid login credentials.
MaxL Shell completed with error
When I interactively test the Windows OS errorlevel variable, I get a value of 2.
E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin>echo %erro
rlevel%
2
Successful MaxL executions result in 0 errorlevels, failures a 2. So yes, I know something went bad, but I have no idea what that is just based on the error code.
iferror and define label with a defined exit code
Failure example
Two new things are in this code:- There are two error handlers – one for bad logins, one for everything else.
- The error handler that ties to a bad login gets an error code of 10.
Note that if there is no error of any kind, the code will exit with no defined error code.
/* MaxL with specific error codes */
/* Login with bad password */
login admin passsword on localhost ;
iferror 'BadLogin' ;
/* Create label for generic error handler */
define label 'GenericErrorHandler';
/* Quit MaxL */
exit ;
/* Create label just for login errors */
define label 'BadLogin' ;
/* Quit MaxL with a 10 error code */
exit 10 ;
Run and response:
E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin>essmsh e:\
tempdir\errortest.msh
Essbase MaxL Shell 64-bit - Release 11.1.2 (ESB11.1.2.0.0B444)
Copyright (c) 2000, 2009, Oracle and/or its affiliates.
All rights reserved.
MAXL> login admin passsword on localhost ;
ERROR - 103 - Unexpected Essbase error 1051293.
ERROR - 1051293 - Login fails due to invalid login credentials.
MaxL Shell completed with error
When I interactively test the Windows OS errorlevel variable, I get a value of 10.
E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin>echo %erro
rlevel%
10
Success example
/* MaxL with specific error codes *//* Login with bad password */
login admin password on localhost ;
iferror 'BadLogin' ;
/* Create label for generic error handler */
define label 'GenericErrorHandler';
/* Quit MaxL without a predefined error code */
exit ;
/* Create label just for login errors */
define label 'BadLogin' ;
/* Quit MaxL with a 10 error code */
exit 10 ;
Run and response:
E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin>essmsh e:\
tempdir\errortest.msh
Essbase MaxL Shell 64-bit - Release 11.1.2 (ESB11.1.2.0.0B444)
Copyright (c) 2000, 2009, Oracle and/or its affiliates.
All rights reserved.
MAXL> login admin kscope2011 on localhost ;
OK/INFO - 1051034 - Logging in user [admin@Native Directory].
OK/INFO - 1241001 - Logged in to Essbase.
MaxL Shell completed
When I interactively test the Windows OS errorlevel variable, I get a value of 0.
E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin>echo %erro
rlevel%
0
Putting it all together
I will concede in advance that perhaps the below level of error trapping and surfacing of where the error occurred is a bit over the top, but regardless, it certainly traps everything. Yes, this might be a good time to invest effort in MaxL + Perl as an alternate way to do error trapping. However, this is a MaxL post, not a MaxL Perl module post, so onwards, ever onwards./*
Purpose: Connect, kick non-admins out, and surface errors based
on action.
Written by: Cameron Lackpour
Modified: Right now
Notes: * Illustrate use of exit
*/
/* Log in to Essbase */
login admin password on localhost ;
iferror "BadLogin" ;
/* Define STDOUT and STDERR outputs */
spool stdout on to "c:\\Automation\\Sample\\MaxLLog\\mshSample.log" ;
iferror "BadLogFile" ;
spool stderr on to "c:\\Automation\\Sample\\MaxLError\\mshSample.err" ;
iferror "BadErrorFile" ;
/* only supervisors and developers may connect */
alter application Sample disable connects ;
iferror "CouldNotDisable" ;
/* Run calculations */
execute calculation Sample.Basic.AdminAgg ;
iferror "AdminAggError" ;
execute calculation Sample.Basic.OrgAgg ;
iferror "OrgAggError" ;
/* Allow all users to connect */
alter application Sample enable connects ;
iferror "CouldNotEnable" ;
/* Leave the MaxL shell */
/* This is where the script exits if there is no error. */
logout ;
exit 0 ;
/* Create label for login errors */
define label "BadLogin" ;
/* Quit MaxL with a 10 error code */
exit 10 ;
/* Create label for log file errors */
define label "BadLogFile" ;
/* Quit MaxL with a 20 error code */
exit 20 ;
/* Create label for error file errors */
define label "BadErrorFile" ;
/* Quit MaxL with a 30 error code */
exit 30 ;
/* Create label for disable connects errors */
define label "CouldNotDisable" ;
/* Quit MaxL with a 40 error code */
exit 40 ;
/* Create label for AdminAgg calc script errors */
define label "AdminAggError" ;
/* Make sure that connections are enabled */
alter application Sample enable connects ;
/* Quit MaxL with a 50 error code */
exit 50 ;
/* Create label for OrgAgg calc script errors */
define label "AdminAggError" ;
/* Make sure that connections are enabled */
alter application Sample enable connects ;
/* Quit MaxL with a 60 error code */
exit 60 ;
/* Create label for enable connects errors */
define label "CouldNotEnable" ;
/* Quit MaxL with a 70 error code */
exit 70 ;
Example of a bad directory
Run and response:E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin>essmsh e:\tempdir\maxlwithexit.msh
Essbase MaxL Shell 64-bit - Release 11.1.2 (ESB11.1.2.0.0B444)
Copyright (c) 2000, 2009, Oracle and/or its affiliates.
All rights reserved.
MAXL> login admin password on localhost ;
OK/INFO - 1051034 - Logging in user [admin@Native Directory].
OK/INFO - 1241001 - Logged in to Essbase.
essmsh error: Unable to open log file c:\\Automation\\Sample\\MaxLLog\\ms
Sample.log
MaxL Shell completed with error
When I interactively test the Windows OS errorlevel variable, the OS returns 20.
E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin>echo %
rrorlevel%
20
Fix the directory
I changed the directories that didn’t exist to ones that did. So this:spool stdout on to "c:\\Automation\\Sample\\MaxLLog\\mshSample.log" ;
iferror "BadLogFile" ;
spool stderr on to "c:\\Automation\\Sample\\MaxLError\\mshSample.err" ;
iferror "BadErrorFile" ;
became this:
spool stdout on to "e:\\TempDir\\mshSample.log" ;
iferror "BadLogFile" ;
spool stderr on to "e:\\TempDir\\mshSample.err" ;
iferror "BadErrorFile" ;
Good directory, bad calc script, actually managed to turn connections back on
I’m not going to give every example of what might happen, but this time the code has managed to log in, create log files for STDOUT and STDERR, and disable connections.However, when it came to run the calc AdminAgg, the script failed because the calc script doesn’t exist.
NB – Based on the error branch, the code can do specific things on error. In the code, I make sure that errors in running calcs always re-enable connections to the Sample application. Without these specific error codes, it would be impossible to know that connections need to be enabled.
Run and response:
E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin>essmsh e:\tempdir\maxlwithexit.msh
Essbase MaxL Shell 64-bit - Release 11.1.2 (ESB11.1.2.0.0B444)
Copyright (c) 2000, 2009, Oracle and/or its affiliates.
All rights reserved.
MAXL> login admin kscope2011 on localhost ;
OK/INFO - 1051034 - Logging in user [admin@Native Directory].
OK/INFO - 1241001 - Logged in to Essbase.
MAXL> alter application Sample disable connects ;
OK/INFO - 1054014 - Database Basic loaded.
OK/INFO - 1054014 - Database Xchgrate loaded.
OK/INFO - 1054014 - Database Interntl loaded.
OK/INFO - 1054014 - Database Sparse loaded.
OK/INFO - 1051061 - Application Sample loaded - connection established.
OK/INFO - 1054027 - Application [Sample] started with process id [7796].
OK/INFO - 1056013 - Application Sample altered.
MAXL> execute calculation Sample.Basic.AdminAgg ;
ERROR - 1012500 - The requested calc script [AdminAgg] not found.
MAXL> alter application Sample enable connects ;
OK/INFO - 1056013 - Application Sample altered.
MaxL Shell completed with error
When I interactively test the Windows OS errorlevel variable, the OS returns 50.
E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient-32\bin>echo %e
rrorlevel%
50
Were I automating this code for real I would use the calling code to query %errorlevel% and then send specific emails based on the error code. In this case, I could quickly be informed that there was something wrong with the calc script AdminAgg instead of having to wade through tedious log files trying to figure out what went wrong.