1 2 Previous Next 17 Replies Latest reply on Oct 1, 2009 10:01 AM by young so

    Getting error while running ETL script

    Gajanan Patil

      Once i run the ETL script getting below error.

       

      Thu Sep 24 12:47:35 2009: Starting scenario execution
      Thu Sep 24 12:47:40 2009: Executing domain 0_BSARA_ETL
      Thu Sep 24 12:51:15 2009: Completed execution for domain 0_BSARA_ETL
      Thu Sep 24 12:51:15 2009: Call to Update Cognos scenario.
      Thu Sep 24 12:51:30 2009: Completed call.
      Thu Sep 24 12:51:31 2009: Error: 0_BSARA_ETL  domain exited with status  80
      Thu Sep 24 12:51:31 2009:  For more error details, please log in to the Metadata Navigator and navigate to the session for 0_BSARA_ETL  domain.
      Thu Sep 24 12:51:31 2009: Completed succesful execution for domain 4_UPDATE_COGNOS
      Thu Sep 24 12:51:31 2009: Removing exstat files for this run.
      Thu Sep 24 12:51:32 2009: Finished removing exstat files.
      Thu Sep 24 12:51:32 2009: Removing lock file.

       

       

       

      If we check in metadata navigator it show stop icon next to 0_BSARA_ETL, FOUNDATION_FACT and 2_FOUNDATION sessionswith return code 5000

       

      I could able to view only Inventory summary by OS, Inventory by OS.

       

      Please help me out.

        • 1. Re: Getting error while running ETL script
          Bill Robinson

          What's in the etl log file?

          • 2. Re: Getting error while running ETL script
            Gajanan Patil

            Hi Bill,

             

            Thanks for the response.

             

            etl log >>

             

            Wed Sep 23 19:21:37 2009:Starting run_etl.
            Wed Sep 23 19:21:37 2009:Checking for the existence of lock file.
            Wed Sep 23 19:21:37 2009:Creating lock file for this run.
            Wed Sep 23 19:21:37 2009: Properties File Name:  RunETL.properties
            Wed Sep 23 19:21:37 2009: Context Name:  CTX_SITE1
            Wed Sep 23 19:21:37 2009: Log Level:  3
            Wed Sep 23 19:21:37 2009: Level of Parallelism:  20
            Wed Sep 23 19:21:37 2009: Removing orphaned exstat files.
            Wed Sep 23 19:21:38 2009: Finished removing orphaned exstat files.
            Wed Sep 23 19:21:38 2009: Retrieving the scenario list of executable scenarios.
            Wed Sep 23 19:21:38 2009: Starting scenario execution
            Wed Sep 23 19:21:42 2009: Executing domain 0_BSARA_ETL
            Wed Sep 23 19:24:44 2009: Completed execution for domain 0_BSARA_ETL
            Wed Sep 23 19:24:44 2009: Call to Update Cognos scenario.
            Wed Sep 23 19:24:58 2009: Completed call.
            Wed Sep 23 19:24:58 2009: Error: 0_BSARA_ETL  domain exited with status  80
            Wed Sep 23 19:24:58 2009:  For more error details, please log in to the Metadata Navigator and navigate to the session for 0_BSARA_ETL  domain.
            Wed Sep 23 19:24:59 2009: Completed succesful execution for domain 4_UPDATE_COGNOS
            Wed Sep 23 19:24:59 2009: Removing exstat files for this run.
            Wed Sep 23 19:24:59 2009: Finished removing exstat files.
            Wed Sep 23 19:24:59 2009: Removing lock file.

            __________________________________________________________________________________________________________________________

            site_1_20090923192136_0_BSARA_ETL_SCEN.log

             

            as C19_IS_RUN_BY_BATCH_JOB,

            DATEDIFF(SS, JR.START_TIME,JR.END_TIME) as C20_EXECUTION_TIME,

            JOB_RUN_ETL_CDC.etl_seq_id as C21_ETL_SEQ_ID,

            JR.ROLE_ID as C23_ROLE_ID,

            JR.USER_ID as C22_USER_ID

            from bladelogic.dbo.JOB_RUN as JR LEFT JOIN bladelogic.dbo.BATCH_JOB_RUN as BJR ON ((JR.JOB_ID=BJR.JOB_ID) AND JR.JOB_VERSION_ID=BJR.JOB_VERSION_ID) AND JR.JOB_RUN_ID=BJR.JOB_RUN_ID

            , bladelogic.dbo.job_run_etl_cdc as JOB_RUN_ETL_CDC

            where (1=1)

             

            And (JR.JOB_RUN_ID=JOB_RUN_ETL_CDC.job_run_id)

            09/23/2009 07:24:41 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

            09/23/2009 07:24:41 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

            09/23/2009 07:24:42 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

            UPDATE ETL_LAST_RUN

            SET END_TIME = getdate(),

            STATUS = 'ERROR'

            where ETL_LAST_RUN_ID IN

            (select ETL_LAST_RUN_ID from ETL_LAST_RUN

            where BL_SITE_ID = 1.0

            and SRC_SCHEMA_NAME = 'bladelogic'

            and TABLE_NAME IN ('FOUNDATION', 'F_JOB_RUN')

            and STATUS = 'STARTED'

            and END_TIME IS NULL)

            09/23/2009 07:24:42 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

            /*commit*/

            09/23/2009 07:24:42 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

            DECLARE @ErrorMessage VARCHAR(255)

            SELECT @ErrorMessage = 'Scenario failed for Site - ' + '1.0'

            raiserror (@ErrorMessage, 16,1);

            09/23/2009 07:24:42 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

            09/23/2009 07:24:42 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

            09/23/2009 07:24:42 PM(main): Session : 23001 finished with return code : 50000

            09/23/2009 07:24:42 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

            09/23/2009 07:24:42 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

            UPDATE ETL_LAST_RUN

            SET END_TIME = getdate(),

            STATUS = 'ERROR'

            where ETL_LAST_RUN_ID IN

            (select ETL_LAST_RUN_ID from ETL_LAST_RUN

            where BL_SITE_ID = 1.0

            and SRC_SCHEMA_NAME = 'bladelogic'

            and TABLE_NAME IN ('FOUNDATION', 'FACT')

            and STATUS = 'STARTED'

            and END_TIME IS NULL)

            09/23/2009 07:24:42 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

            /*commit*/

            09/23/2009 07:24:42 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

            DECLARE @ErrorMessage VARCHAR(255)

            SELECT @ErrorMessage = 'Scenario failed for Site - ' + '1.0'

            raiserror (@ErrorMessage, 16,1);

            09/23/2009 07:24:42 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

            09/23/2009 07:24:42 PM(main): Session : 19001 finished with return code : 50000

            09/23/2009 07:24:43 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

            09/23/2009 07:24:43 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

            09/23/2009 07:24:44 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

            DECLARE @ErrorMessage VARCHAR(255)

            SELECT @ErrorMessage = 'ETL failed for Site - ' + '1.0'

            raiserror (@ErrorMessage, 16,1);

            09/23/2009 07:24:44 PM(main): com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

            09/23/2009 07:24:44 PM(main): Session : 17001 finished with return code : 50000

            DwgJv.main: Exit. Return code:50000

            • 3. Re: Getting error while running ETL script
              young so

              I've seen this exact error before, this is due to how you created the database and who is the owner of the database.  Are you running MS SQL?  If yes, go into the table and show me a screen shot of that...

               

              See my posting on http://communities.bmc.com/communities/thread/32708?tstart=0 to see if it is same error.

              • 4. Re: Getting error while running ETL script
                Gajanan Patil

                Yes i am using MS SQL 2005, Please find screenshot of db owner and dw tables

                DW Tables.JPGdb owner.JPG

                • 5. Re: Getting error while running ETL script
                  young so

                  no.  Screen shot of the bladelogic tables.

                  • 6. Re: Getting error while running ETL script
                    Gajanan Patil

                    Please check bladelogic tables

                     

                    BL tables.JPG

                    • 7. Re: Getting error while running ETL script
                      young so

                      That look good.  Can you give a screen of the bladelogic user mapping

                      and database properties | files.  Who is the owner?

                      • 9. Re: Getting error while running ETL script
                        young so

                        Ok.  The database look good.  Can you send screen shot of the ETL errors?  and do stop_nsh to see if you get lock file errors.  If yes, what version of BSARA are you running?

                        • 10. Re: Getting error while running ETL script
                          Gajanan Patil

                          I have posted the ETL errors in my 1st 2 posts.

                           

                          I ran the stop_etl.nsh, i can not see lock file errors. I am using BSARA 1.0.03 version

                          • 11. Re: Getting error while running ETL script

                            I would recommend opening a ticket with support for this, if you have not done so already. You will likely get better assistance for individuals who can continually support and track your problems.

                             

                            Adam Bowen

                            Technical Client Lead

                            BMC Software

                            • 12. Re: Getting error while running ETL script
                              young so

                              Try this:


                              1. select * from dbo.etl_last_run where status = 'STARTED'; (from sql server management studio)
                              2. from cmd line on reports server run cd <install>/etl/bin
                              3. "nsh stop_etl.nsh -s 1 -v (OM version here)"
                              4. select * from dbo.etl_last_run where status = 'STARTED'; (from sql server management studio)
                              5. "nsh run_etl.nsh -s 1 -v (OM version here)"

                               

                              After step 3 you should have not status on the .etl_last_run table.  If you have process there, kill any nsh and java stuff from the task manager.  Or find way to disconnection db connection on the BSARA database.  The goal here is to find out you have active connection or not after stopping the ETL.

                              • 13. Re: Getting error while running ETL script
                                Gajanan Patil

                                i tried as per your give solution but i could not found any result after running the sql queries. Result >> (0 row(s) affected)

                                 

                                Also i checked by opening ETL_LAST_RUN table.  In table Status column having Error status for table_name F_JOB_RUN and FOUNDATION.

                                • 14. Re: Getting error while running ETL script
                                  young so

                                  Did you do the post install script after install 1.0.0.3?  Here is the post script you have to run, if you didn't.

                                   

                                  To update BMC BladeLogic for SQL Server database types:
                                  1 Log into the BMC BladeLogic database server as the BMC BladeLogic user.
                                  2 Navigate to the BSARA/Db_Scripts/sqlserver/schema directory.
                                  3 Run the following script:  OM_UPGRADE_FOR_job_run_etl_seq_sqlserver.sql

                                   

                                  I think, you should open a ticket and work with Alan.  Your issue is not the same issue I had.  I resolve it using those procedure.

                                  1 2 Previous Next