4 Replies Latest reply: Nov 7, 2012 4:24 PM by Voltaire RSS

    Row into Run Table Task Failed

    Saro
      Hi friends,

      Im starting up to load SCM Analytics using DAC and it is emerged like total of 360 task and out of that, entire 360 task
      is stopped due to the first task failure.


      The failure task name is "Row into Run Table"

      My DAC status description of this failed task is
      2012-11-07 16:23:59.109 ANALYZE TABLE:W_ETL_RUN_S has started.
      
      
      
      ANOMALY INFO::: Error while executing : ANALYZE TABLE:W_ETL_RUN_S
      MESSAGE:::com.siebel.etl.database.IllegalSQLQueryException: DataWarehouse:DBMS_STATS.GATHER_TABLE_STATS(ownname => 'BIAPPS', tabname => 'W_ETL_RUN_S', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => false, degree => DBMS_STATS.DEFAULT_DEGREE)
      ORA-06550: line 1, column 104:
      PLS-00201: identifier 'DBMS_STATS.AUTO_SAMPLE_SIZE' must be declared
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored
      
      Values : 
      Null Map
      EXCEPTION CLASS::: java.lang.Exception
      
      com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:450)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:307)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:214)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:586)
      com.siebel.analytics.etl.taskmanager.XCallable.call(XCallable.java:63)
      java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
      java.util.concurrent.FutureTask.run(FutureTask.java:138)
      java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
      java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
      java.util.concurrent.FutureTask.run(FutureTask.java:138)
      java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
      java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
      java.lang.Thread.run(Thread.java:619)
      
      ::: CAUSE :::
      MESSAGE:::DataWarehouse:DBMS_STATS.GATHER_TABLE_STATS(ownname => 'BIAPPS', tabname => 'W_ETL_RUN_S', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => false, degree => DBMS_STATS.DEFAULT_DEGREE)
      ORA-06550: line 1, column 104:
      PLS-00201: identifier 'DBMS_STATS.AUTO_SAMPLE_SIZE' must be declared
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored
      
      Values : 
      Null Map
      EXCEPTION CLASS::: com.siebel.etl.database.IllegalSQLQueryException
      
      com.siebel.etl.database.DBUtils.executeProcedure(DBUtils.java:757)
      com.siebel.etl.database.DBUtils.executeProcedure(DBUtils.java:657)
      com.siebel.etl.database.DBUtils.operateTable(DBUtils.java:1692)
      com.siebel.etl.database.DBUtils.analyzeTable(DBUtils.java:1716)
      com.siebel.analytics.etl.etltask.AnalyzeTableTask.doExecute(AnalyzeTableTask.java:57)
      com.siebel.analytics.etl.etltask.AnalyzeTableTaskWrapper.doExecute(AnalyzeTableTaskWrapper.java:34)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:411)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:307)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:214)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:586)
      com.siebel.analytics.etl.taskmanager.XCallable.call(XCallable.java:63)
      java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
      java.util.concurrent.FutureTask.run(FutureTask.java:138)
      java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
      java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
      java.util.concurrent.FutureTask.run(FutureTask.java:138)
      java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
      java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
      java.lang.Thread.run(Thread.java:619)
      
      ::: CAUSE :::
      MESSAGE:::ORA-06550: line 1, column 104:
      PLS-00201: identifier 'DBMS_STATS.AUTO_SAMPLE_SIZE' must be declared
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored
      
      EXCEPTION CLASS::: java.sql.SQLException
      
      oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:737)
      oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:647)
      oracle.jdbc.driver.T2CCallableStatement.executeForDescribe(T2CCallableStatement.java:531)
      oracle
      Source Database: ORA_R1213Flatfile
      Target DB: Datawarehouse
      Folder: SILOS

      What could be the reason for this issue and what might be the solution for this.

      Thanks

      REgards,
      Saro
        • 1. Re: Row into Run Table Task Failed
          Lombo
          Hello,

          Is this from a Siebel source ?
          If yes, I believe you need to create the additional tables in Siebel DB as per OBI Apps installation doc.

          Antonio
          BExpert, Brazil
          • 2. Re: Row into Run Table Task Failed
            Saro
            Nope, im using oracle R12.1.3 as a source.

            Regards,
            Saro
            • 3. Re: Row into Run Table Task Failed
              Srini VEERAVALLI
              Try to analyze command manually in the warehouse or target database
              ANALYZE TABLE:W_ETL_RUN_S

              Make sure your target db owners is BIAPPS
              DBMS_STATS.GATHER_TABLE_STATS(ownname => 'BIAPPS', tabname => 'W_ETL_RUN_S', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => false, degree => DBMS_STATS.DEFAULT_DEGREE)

              Edited by: veeravalli on Nov 7, 2012 11:50 AM
              • 4. Re: Row into Run Table Task Failed
                Voltaire
                Hi Saro,

                DBMS_STATS.AUTO_SAMPLE_SIZE is a DB setting set for the Estimate Percentage.
                We can know the value currently set by using below SQL.

                SQL> select dbms_stats.get_prefs('ESTIMATE_PERCENT','DACREP','W_ETL_RUN_S') from dual;

                assuming DACREP is the owner of the DAC Repository. If the value is not set please set it by below SQL. The 2 in sql is variable you can size it optimal.

                SQL> exec dbms_stats.set_database_prefs('ESTIMATE_PERCENT',2);

                Regards,
                Jay