Want to know The Truth About CPM?

10 June 2011

No Exit (but not in MaxL)


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:
  1. There are two error handlers – one for bad logins, one for everything else.
  2. 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.

Conclusion

Adding the ability to pass on an error code to the exit keyword seems trivial, but it can provide a lot of information to automation code and make debugging production issues that much easier.  Why aren’t you using it?


18 comments:

srx said...

Great post Cameron, as far as I can see you're preparing yourself and your French for your next book review (http://bit.ly/lynbuZ) btw it could have been named "Born a four of July"...

Cameron Lackpour said...

Sebastien,

I'm glad you liked it.

I'm a big fan of Rififi. Getting Jules Dassin into a post would be hard, even for me, so I used Sartre. :)

I know these references are silly, but the subjects can be so dry. And sometimes people even know what I'm talking about. :)

Regards,

Cameron Lackpour

P.S. Interesting looking book -- pity my French never went beyond four years.

Anonymous said...

Cameron,

Very informative post! Thanks!

TimG said...

Thanks Cameron, very clear. Will have plenty of opportunity to use this.

I was wondering why they didn't they implement...

IFERROR EXIT 10;

...until I got to your remarks about reenabling connects etc. Good stuff!

Cameron Lackpour said...

Tim and Jared,

Thanks for your kind words.

Without getting embarrassingly mushy, let me just say that to hear praise from guys like you is really, really gratifying.

Regards,

Cameron Lackpour

Anonymous said...

Hi,

can you help me?

I got this error on essbase:

OK/INFO - 1021013 - ODBC Layer Error: [S1000] ==> [[DataDirect][ODBC Oracle Wire Protocol driver]TNS-12154: TNS:could not resolve the connect identifier specified].
OK/INFO - 1021014 - ODBC Layer Error: Native Error code [0] .
ERROR - 1021001 - Failed to Establish Connection With SQL Database Server. See log file for more information.
ERROR - 1241101 - Unexpected Essbase error 1021001.

thanks.

Ana Maria

Cameron Lackpour said...

Ana,

I was a little confused by your question as it doesn't relate to this post, but I'll try to answer regardless.

It *looks* like your MaxL (I am guessing MaxL, it could be directly from EAS) is failing because you are passing bad SQL authentication information, i.e., your id or password is bad.

HTH,

Cameron Lackpour

Anonymous said...

Essmsh can trap data and dimension load errors. You just need to add "on error abort". You should be able to trap that an error file exists using shell commands too.

Cameron Lackpour said...

Dear Anon,

When you use on error abort, MaxL will not write to an error file, so yes, you will know it failed, but not *what* failed. So then you would have to run the import interactively whilst piping the errors to a log file and *then* know what failed. It's a right pain.


Warning -- two shameless plugs follow.

What I did in my chapter of Developing Essbase Applications is use ODI to interrogate file length/existence and then decide what to do about the error. Come see me at KScope12 when I talk about this. I'm also going to have an Essbase Beginner's session called (I hope the name has been changed) "Script or Die!".

Regards,

Cameron Lackpour

Anonymous said...

You can write to the log without the abort and use the shell commAnd to check for the existence of the error file -- very easily done in 11.1.2.

Cameron Lackpour said...

>>You can write to the log without the abort and use the shell commAnd to check for the existence of the error file -- very easily done in 11.1.2.
^^^A *different* Anonymous? Who can tell?

Assuming I'm continuing this conversation you're quite right and in fact this has been available as long as there has been a MaxL.

However, once you write to an error file, you cannot trap it with iferror as I wrote in the blog. It sure would be nice if you could because otherwise, as you note, you must write a trapping routine in your calling code. That's not impossible (it's actually fairly easy) but it is Yet Another Step.

Again, come to KScope12 and see me do just the above in ODI (you could do it anywhere).

Regards,

Cameron Lackpour

TimF said...

Sorry -- I was on my phone -- Anon is Tim Faitsch. What I'm trying to say is that you can do the dataload or dim build -- you still probably want to do an iferror in case it can't find the file or can't connect to sql source. Once the import command is done, you do a shell command like so:

----
Shell("cat filename.txt")
iferror "this_actually_means_everything_is_okay"
goto "this_means_there_were_rejects"
----

In 11.1.2 you can catch the error code from a shell command. Just check to see if the file exists, if it doesn't exist you're good, if it does exist you can handle it. I know it sounds backward but I'm not sure how to trigger an error looking for a file that does exist. Anyway, this functionality doesn't work in, say, 11.1.1.3 but it does in 11.1.2. If you notice the tech ref, it's much more robust on the subject in 11.1.2 than 11.1.1.3.

Cameron Lackpour said...

That is f***ing awesome! I had no idea that was possible.

Just to translate that, if you do in Windoze:
import blahblahblah on error write to "c:\\\\bozo.txt" ;
shell("dir c:\bozo.txt") ;
iferror "NotReallyAnErrorAtAll" ;
goto "OhYesThereWasAnError" ;

This works because Windows returns %errorlevel% as 1 if the file doesn't exist, and 0 if it does. So an error isn't really an error at all.

I am stealing this for my KScope12 "Script or Die!" presentation and giving you all the credit.

That technique totally made my day. Thanks for posting it.

Regards,

Cameron Lackpour

Anonymous said...

Hi Cameron,

I'm using Linux scripting to call MAXL scripts. In Maxl scripts, I'm trapping errors at every level of the code (login, CALC,...) with different error codes. Is there a way to send the trapped error to the linux script ? If yes , how this can be accomplished. I know from windows side, interactively, we can intorrogate the variable %errorlevel% but I don't know if there is a such equivalent in Linux and if there is something that can be used in Linux scripting to do such verification.

Thx for your help.

Issam

Cameron Lackpour said...

Issam,

I am guessing you searched this, but the Linux equivalent appears to be $?

Regards,

Cameron Lackpour

Anonymous said...

Will the iferror function generate an error when a calc script exists, but fails when it is run? I'm looking to pass an exit status to a parent shell script if a calc script fails when the MaxL calls it.

Ronald said...

Hi,

I am using $? to capture the return value of MAXL.
But even though my MAXL is failing in the fist step(login $1 $2 on $3;) , I am getting $?=0.

Please suggest.

My code
--------

login $1 $2 on $3;
IFERROR 'errLogin';
spool stderr off;
EXIT 0

define label 'errLogin';
echo "*******";
logout;

Ronald said...

Hi,

I am using $? to capture MAXL error in shell. But $? returns value 'zero' even though it fails at the first step(Login $1 $2 on $3;)

Please suggest.

login $1 $2 on $3;
IFERROR 'errLogin';
spool stderr off;
EXIT 0

define label 'errLogin';
echo "*******";
logout;