IFERROR MaxL – Surprises in Essbase Part V

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;