8 Replies Latest reply on Nov 20, 2012 3:35 AM by Rupesh Gaikwad

    ETL Job in BBDSSA stopped working

      The following is in the log

       

      OracleDI: Starting scenario 0_BSARA_ETL 80 in context CTX_SITE1 ...
      03/23/2011 04:08:35 PM(main): Creating session for scenario:0_BSARA_ETL - 80
      03/23/2011 04:08:35 PM(main): Session : 1149001 is running
      03/23/2011 04:08:40 PM(main): SnpSessTaskSql.execSrcOrders : snps std order :
      declare @var_ver_str VARCHAR(255);
          declare @var_ver_float float;
          declare @var_scen_version float;
          declare @v_select_sql nvarchar(max);
          declare @ErrorMessage VARCHAR(255);
          declare @v_Error_Number float;
          declare @v_Err_Msg varchar(2000);

      BEGIN TRY
            SET @var_scen_version = 80;

              SET @v_select_sql = 'select @var_ver_str = value from system_property where name = ''DatabaseVersion''';
              execute sp_executesql @v_select_sql, N'@var_ver_str varchar(255) OUTPUT', @var_ver_str OUTPUT
              SET @var_ver_float = cast(replace(@var_ver_str, '.','') AS float);


              --Check If OM is supported
              IF @var_ver_float >= 800
                      BEGIN
                      --Check if Scenario Version and OM Version match
                      SET @var_ver_float = cast(substring(replace(@var_ver_str, '.',''),1,2) AS float);
                      IF @var_scen_version != @var_ver_float
                              BEGIN
                                      SELECT @ErrorMessage = 'Mismatch between Site Id and OM Version provided as input parameters. Please recheck the OM Version.'
                                      raiserror (@ErrorMessage, 16,1);
                              END
                      END
              ELSE
                      BEGIN
                      SELECT @ErrorMessage = 'Mismatch between Site Id and OM Version provided as input parameters. Please recheck the OM Version.'
                      raiserror (@ErrorMessage, 16,1);
                      END

       


      END TRY

      BEGIN CATCH
              SELECT @v_Error_Number=ERROR_NUMBER();
              SELECT @v_Err_Msg=ERROR_MESSAGE();

              if @v_Error_Number = 50000
              begin
                 SELECT @ErrorMessage = 'Mismatch between Site Id and OM Version provided as input parameters. Please recheck the OM Version. '
                 raiserror (@ErrorMessage, 16,1);
              end
              if @v_Error_Number = 208
              begin
                SELECT @ErrorMessage = 'OM Schema is incorrect. SQLServer Error Number: ' + cast(@v_Error_Number as varchar)+ '. SQLServer Error Message: ' + @v_Err_Msg
                raiserror (@ErrorMessage, 16,1);
              end
              else
              begin
                SELECT @ErrorMessage = 'OM Connectivity Problem. SQLServer Error Number: ' + cast(@v_Error_Number as varchar)+ '. SQLServer Error Message: ' + @v_Err_Msg
                raiserror (@ErrorMessage, 16,1);
              end
      END CATCH

      03/23/2011 04:08:40 PM(main): SnpSessTaskSql.execStdOrders : snps std order :
      declare @v_db_version float;
          declare @v_site_id numeric;
          declare @v_select_sql nvarchar(max);
          declare @ErrorMessage VARCHAR(255);
          declare @v_Error_Number float;
          declare @v_Err_Msg varchar(2000);


      BEGIN TRY

              BEGIN
                SET @v_select_sql = 'select @v_site_id=1 where exists (select bl_site_id from bl_site)';
                execute sp_executesql @v_select_sql, N'@v_site_id numeric OUTPUT', @v_site_id OUTPUT
              END


      END TRY

      BEGIN CATCH
              SELECT @v_Error_Number=ERROR_NUMBER();
              SELECT @v_Err_Msg=ERROR_MESSAGE();

              if @v_Error_Number = 208
              begin
                SELECT @ErrorMessage = 'DW Schema is incorrect. SQLServer Error Number: ' + cast(@v_Error_Number as varchar) + '. SQLServer Error Message: ' + @v_Err_Msg
                raiserror (@ErrorMessage, 16,1);
              end
              else
              begin
                SELECT @ErrorMessage = 'DW Connectivity Problem. SQLServer Error Number: ' + cast(@v_Error_Number as varchar) + '. SQLServer Error Message: ' + @v_Err_Msg
                raiserror (@ErrorMessage, 16,1);
              end
      END CATCH

      03/23/2011 04:08:40 PM(main): SnpSessTaskSql.execStdOrders : snps std order :
      declare @v_db_version float;
          declare @v_site_id numeric;
          declare @v_num numeric;
          declare @v_select_sql nvarchar(max);
          declare @ErrorMessage VARCHAR(500);
          declare @v_Error_Number float;
          declare @v_Err_Msg varchar(2000);


      BEGIN TRY

              SET @v_select_sql = 'select @v_num = count(1) from [db_site1_link].Bladelogic1.dbo.system_property';
              execute sp_executesql @v_select_sql, N'@v_num numeric OUTPUT', @v_num OUTPUT

       

      END TRY

      BEGIN CATCH
              SELECT @v_Error_Number=ERROR_NUMBER();
              SELECT @v_Err_Msg=ERROR_MESSAGE();

              SELECT @ErrorMessage = 'DB Link ' + 'db_site1_link' + ' between DW and OM is not valid. SQLServer Error Number: ' + cast(@v_Error_Number as varchar) + '. SQLServer Error Message: ' + @v_Err_Msg
              raiserror (@ErrorMessage, 16,1);
      END CATCH

      03/23/2011 04:08:40 PM(main): SnpSessTaskSql.execStdOrders : snps std order :
      declare @v_db_version float;
          declare @v_site_id numeric;
          declare @v_collation_om varchar(2000);
          declare @v_collation_dw varchar(2000);
          declare @ErrorMessage VARCHAR(255);
          declare @v_Error_Number float;
          declare @v_Err_Msg varchar(2000);

      BEGIN TRY

            
      SELECT @v_collation_om = isnull (
      min(collation_name), 'OM')
      from [db_site1_link].Bladelogic1.sys.databases
      where name='Bladelogic1';

      SELECT @v_collation_dw = isnull (
      min(
      CONVERT(char,
      DATABASEPROPERTYEX('BSARA_DW_DB',
      'collation'))
      ), 'DW')
      from bl_site;

      IF @v_collation_om != @v_collation_dw
      BEGIN
          SELECT @ErrorMessage = 'Collation sequence mismatch between DW database BSARA_DW_DB and OM database Bladelogic1 Please recheck';
          raiserror (@ErrorMessage, 16,1);
      END

      END TRY

      BEGIN CATCH
                raiserror (@ErrorMessage, 16,1);

      END CATCH

      03/23/2011 04:08:40 PM(main): SnpSessTaskSql.execSrcOrders : snps std order :
      if not exists(select name from sysobjects
                where upper(name) = 'JOB_RUN_ETL_CDC'
                and type = 'U')
          create table job_run_etl_cdc (
          etl_seq_id integer not null identity,
          job_run_id integer not null,
          constraint XPKjob_run_etl_seq primary key (etl_seq_id, job_run_id) );


      IF not exists(select name from sysobjects
                where upper(name) = 'JOB_RUN_UTRG'
                and type = 'TR')
          execute ('create trigger job_run_utrg
          on job_run
          after update as
          begin
            insert into job_run_etl_cdc (job_run_id)
            select d.job_run_id
            from deleted d
              join inserted i on
               (d.job_run_id = i.job_run_id)
            where  (d.progress_status_id IS NULL or d.progress_status_id <> 4)
            and i.progress_status_id = 4;
          end;');


      IF not exists(select name from sysobjects
               where name = 'V_JOB_RUN_ETL_CDC'
               and type = 'V')
          execute ('create view v_job_run_etl_cdc as
              select s.etl_seq_id, s.job_run_id
              from
                (select etl_seq_id, job_run_id,
                 row_number() over(partition by job_run_id  order by etl_seq_id desc) rn
                 from job_run_etl_cdc
                ) s
              where rn = 1');

      03/23/2011 04:08:40 PM(main): SnpSessTaskSql.execStdOrders : snps std order :
      DECLARE  @v_job_run_count FLOAT;
      DECLARE  @v_sql NVARCHAR(MAX);
      DECLARE  @p_linked_svr VARCHAR(MAX);
      SET @p_linked_svr =  '[db_site1_link].Bladelogic1.dbo.'
      SET @v_sql = '''';
      SET @v_sql = ' insert into ' +@p_linked_svr+ 'job_run_etl_cdc (job_run_id) 
      select job_run_id        
      from ' +@p_linked_svr+ 'job_run       
      where progress_status_id = 4
              and job_run_id NOT IN (select job_run_id from ' +@p_linked_svr+ 'job_run_etl_cdc)      
      order by end_time';

      IF '0_BSARA_ETL' = '0_BSARA_ETL'
         BEGIN
           SET @v_job_run_count = 0;
           SELECT @v_job_run_count = COUNT(1) FROM F_JOB_RUN WHERE bl_site_id = 1.0;
           IF  @v_job_run_count = 0
           BEGIN
               EXECUTE sp_executesql @v_sql;
           END
         END
      ELSE
           BEGIN
               EXECUTE sp_executesql @v_sql;
           END

      SET @v_job_run_count = 0;
      SET @v_sql = 'select @v_job_run_count=count(1) from (select vj.etl_seq_id from ' +@p_linked_svr+ 'job_run jr
                                left outer join ' +@p_linked_svr+ 'v_job_run_etl_cdc vj
                                on jr.job_run_id = vj.job_run_id
            where jr.is_deleted = 0
            AND jr.progress_status_id = 4) temp
            where temp.etl_seq_id is null '

      EXECUTE sp_executesql @v_sql, N'@v_job_run_count float OUTPUT',@v_job_run_count OUTPUT
      IF @v_job_run_count > 0
          BEGIN
            RAISERROR ('Invalid data condition in OM (core) database. Some job_run rows with progress_status=4 do not have corresponding rows in job_run_etl_cdc table. Contact BMC support', 16,1);
          END

      03/23/2011 04:08:40 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".
      03/23/2011 04:08:40 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".
      03/23/2011 04:08:41 PM(main): Session : 1149001 finished with return code : 50000
      DwgJv.main: Exit. Return code:50000