1 Reply Latest reply on Sep 25, 2009 1:51 AM by Paul Seager-Smith

    BSARA on servers with non-English locales

    Paul Seager-Smith

      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
      INTO v_db_version;


      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.