It seems that BSARA 1.0.03 ( and maybe other versions) has a problem if BSARA is running on a server with the regional settings set to something other than English (it may also cause problems in Canada).
Essentially the run_etl.nsh job interprets the NLS_NUMERIC_CHARACTERS as ",." in countries that use a comma as the decimal separator and one of the stored procedures in the BSARA DW database fails with these settings.
The full, gory details are below together with a workaround that means you don't have to change the regional settings on your BSARA server:
We have recently installed BSARA 1.0.03 and the installation went without problems. However, when we run the run_etl.nsh script, it failed with a scenario failed to complete error message. We then looked into the etl log files and found the following in the .err file
java.sql.SQLException: ORA-01722: invalid number
ORA-02063: preceding line from DB_SITE1_LINK<br>ORA-06512: at "BSARA_DW.LOAD_D_TEMPLATE_PART", line 242
ORA-06512: at line 1
and this in the .log file (with the debug level increased):
09/03/2009 02:51:44 PM(main): SnpsQuery.updateExecStatement : prepare request :
BEGIN LOAD_D_JOB(1.0, 'db_site1_link', 'bladelogic'); LOAD_D_TEMPLATE(1.0, 'db_site1_link', 'bladelogic'); LOAD_D_TEMPLATE_PART(1.0, 'db_site1_link', 'bladelogic'); LOAD_D_COMPONENT(1.0, 'db_site1_link', 'bladelogic'); END;
09/03/2009 02:51:45 PM(main): SnpSessTask.treatSqlException : 1722 : 42000 : java.sql.SQLException: ORA-01722: invalid number<br>ORA-02063: preceding line from DB_SITE1_LINK
ORA-06512: at "BSARA_DW.LOAD_D_TEMPLATE_PART", line 242
ORA-06512: at line 1
We then took a look at the LOAD_D_TEMPLATE_PART stored procedure in the DW database and found that it checks the Bladelogic DB version as follows:
v_select_sql := 'select CAST(substr(value,1,3) AS float) from system_property@' || p_db_link || ' where name = ''DatabaseVersion''
and value not in (''7.4.1'', ''7.4.2'')';
EXECUTE IMMEDIATE v_select_sql
This was failing because the version (7.6 in our case) could not be cast to the local numeric values where , (comma) is the numeric decimal character here (in Belgium and most of continental Europe), i.e. it was expecting 7,6 as a decimal number.
Checking the NLS settings of the database showed that these were set to English US with a NLS_NUMERIC_CHARACTERS value of "." , which is fine.
It transpires that Java modifies the session NLS parameters based on the local machine settings. The local machine language settings were set to be_NL for Belgium and the Java program that does the ETL run was reading these and setting the NLS_NUMERIC_CHARACTERS value to ",." , which then caused the ETL export to fail!
As a workaround to fix this, we have modified the odiparams.bat file (located in the ETL/bin folder) to change the following line :
set ODI_ADDITIONAL_JAVA_OPTIONS=-Duser.language=en -Duser.region=US<br>
This forces the Java job that runs the extract to use an en_US locale setting and hence the US style numeric values.
This seems to fix the issues as the ETL runs through without errors now.