IFERROR MaxL – Surprises in Essbase Part V


GlennS calls them as issues Cameron Lackpour calls them as stupid tricks and I chose surprises. 🙂

I had to experiment on this because I had to prove that I was not wrong 🙂 and also to explain why it behaves this way.
People who are not aware of IFERROR (I don’t think you are one among them)

According to the definition it skips the subsequent statements and moves to the ones which are defined under a label.

I’ve used it always and it worked too (or I thought it did)

I’ve a MaxL script which loads data from Oracle tables

/* Spool the results of Load Data process to a log file */
spool on to "$MAXLLOG_DIR\$1_Data_Load.log";
set timestamp on;
import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD1 to load_buffer with buffer_id 1 on error write to $5;  
 iferror 'Error';  
 define label 'Error';    
 spool off;
 logout;  
 exit;  

On a fine (?) day a two more load rules were added and that required more error file locations.

/* Spool the results of Load Data process to a log file */
spool on to "$MAXLLOG_DIR\$1_Data_Load.log";
set timestamp on;
import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD1 to load_buffer with buffer_id 1 on error write to $5;  
 iferror 'Error'; 
import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD2 to load_buffer with buffer_id 1 on error write to $6;  
 iferror 'Error';  
import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD3 to load_buffer with buffer_id 1 on error write to $7;  
 iferror 'Error';  
 define label 'Error';  
 spool off;  
 logout;
 exit;  

The MaxL was updated however the batch file was not (it was still having 5 parameters instead of 7)

It stopped saying that there is an error in data load, which was expected. However I didn’t expect what followed.

The log file is showing the second and third data import line!!!!!

So did it load LD2 & LD3 data? I checked the log files and there is no record of LD2 & LD3 sql execution.

Then why is it spooling the second data load statement recorded into the log file??? (The first command under the label ‘Error’ is spool off!!!!!!)

Since I’m on 11.1.2.2 I tried adding different exit codes to check whether IFERROR is working or not.

Read about adding different exit codes here. So now my MaxL is

/* Spool the results of Load Data process to a log file */
spool on to "$MAXLLOG_DIR\$1_Data_Load.log";
set timestamp on;
import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD1 to load_buffer with buffer_id 1 on error write to $5;  
 iferror '1st_Error'; 
import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD2 to load_buffer with buffer_id 1 on error write to $6;  
 iferror '2nd_Error';  
import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD3 to load_buffer with buffer_id 1 on error write to $7;  
 iferror '3rd_Error';  

define label '1st_Error';
spool off;
logout;
exit 10;

define label '2nd_Error';
spool off;
logout;
exit 20;

define label '3rd_Error';
spool off;
logout;
exit 30;

I didn’t give the 6th and 7th parameter and as expected the process aborted. I check the ERRORLEVEL and found that it is 20 which proves that it did break just after 1st data import statement.

Or did it? The log file still shows the 2nd and 3rd data load statement.

Just to prove that it works (an I’m sane) I gave a non-existent log file (non-existent path) as 6th parameter and that did the trick!!!!

The spool stopped just after 1st import statement. No record of second import statement in log file.

I think IFERROR doesn’t like the fact that a parameter is missing and spools that statement into the log file.

Why it is so? (I don’t have an answer to that but have an answer to why it did so 🙂 )

Update:
Added the log file so that it’ll help

MAXL> import database AppName.DBName DATA connect as SQLUser identified by SQLpassword using server rules_file LD1 to load_buffer with buffer_id 1 on error write to “C:Hyperion_LogsLD1_Error.txt”;

 OK/INFO – 1019061 – Reading Rule SQL Information For Database [DBName].
 OK/INFO – 1019025 – Reading Rules From Rule Object For Database [DBName].
 OK/INFO – 1021004 – Connection String is generated.
 OK/INFO – 1021041 – Connection String is [DSN=ODS;UID=…;PWD=…;].
 OK/INFO – 1021043 – Connection has been established.
 OK/INFO – 1021044 – Starting to execute query.
 OK/INFO – 1021045 – Finished executing query, and started to fetch records.
 OK/INFO – 1021000 – Connection With SQL Database Server is Established.
 OK/INFO – 1003040 – Parallel dataload enabled: [2] block prepare threads, [2] block write threads..
 OK/INFO – 1021047 – Finished fetching data.
 OK/INFO – 1021002 – SQL Connection is Freed.
 OK/INFO – 1003051 – Data Load Elapsed Time for [SQL] with [LD1.rul] : [7.114] seconds.
 OK/INFO – 1241113 – Database import completed [‘AppName’.’DBName’].

      essmsh timestamp: Fri Jul 12 15:39:37 2013

MAXL> import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD2 to load_buffer with buffer_id 1 on error write to $6;

      essmsh error: Variable doesn’t exist – $6

      essmsh timestamp: Fri Jul 12 15:39:37 2013

MAXL> import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD3 to load_buffer with buffer_id 1 on error write to $7;

When more load rules are added without log file.

MAXL> import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD2 to load_buffer with buffer_id 1 on error write to $6;

      essmsh error: Variable doesn’t exist – $6

      essmsh timestamp: Fri Jul 12 15:39:37 2013


MAXL> import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD3 to load_buffer with buffer_id 1 on error write to $7;MAXL> import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD4 to load_buffer with buffer_id 1 on error write to $8;


About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 thoughts on “IFERROR MaxL – Surprises in Essbase Part V

  • Harsh Warikoo

    Hi Celvin,

    I too had seen such situations many times on an older Hyperion installation (Version 9.3.1) a while back. I was working on writing end-to-end automated MAXL scripts for the entire daily load processes and it was becoming pretty complex (there were many situations where i was invoking a VBS/MS-DOS batch shell command within another VBS shell running inside the MAXL shell – three layers of shell commands invoked within a batch) and on numerous occasions while developing and testing the scripts, i would inadvertently face such an issue whenever there was something wrong in passing arguments (or syntax issues etc). Based on what i experienced, my understanding gathered from the issue was as follows:

    If you look at your example, what has happened is that when the MAXL SHELL was INTERPRETING the problematic statement – while attempting to replace the variables ($1, $2, $3, $4, $5 etc) with values received at RUN TIME, it faced an issue. Now since the MAXL SHELL has already made an attempt at interpreting that statement, that must be the reason why it has outputted it to the log file (Also i guess there must be a back end process or something similar in MAXL wherein each MAXL command first goes through a INTERPRETER and after that, gets executed at the COMMAND LINE/SHELL – so the statement that got outputted to the log file was the failure at INTERPRETATION, not execution).

    I could not see any MAXL log file output of this issue displayed in the blog post by you, but i'm pretty sure if what i have understood (and remember) is right, the problematic statement that you reportedly found in your log file would not have had the variables substituted with run time values, it would have been just like the originally written MAXL statement as follows:

    import database $1.$2 DATA connect as $3 identified by $4 using server rules_file LD2 to load_buffer with buffer_id 1 on error write to $6;

    Normally a MAXL statement that gets written in the log file would have the variables replaced with the run time values, but in your case i bet the problematic log statement (the 2nd data load statement) found in the file would have variables as $1, $2, $3 (or maybe just $6 – the problematic variable that MAXL was unable to INTERPRET) etc

    This was just something i understood in my practical implementations on Version 9.3.1. I guess the same would also hold true of 11.1.2.2 in this case.

    Hope this explanation helps.

    Thanks and regards,
    Harsh Warikoo

  • Celvin Kattookaran

    @Harsh yes you understanding is correct. I ran it again and posted the log file.

    Yes that's why i said it didn't like the fact that there were missing variables and added that to the log file. To test it i added more statements all got spooled. Your explanation of interpreter could be very well valid.

  • Harsh Warikoo

    Even i did not like it the first time when i experienced this issue. But on further testing and attempting to understand the behavior, I could see no further issues. As the MAXL shell has not actually executed the problematic statement.