1 Reply Latest reply on Dec 10, 2004 3:38 AM by 416113

    BC4J and Timeouts

    416113
      As a work around from the use of Message Driven Beans, we have designed an application that polls an Oracle database table looking for "jobs" to perform. When a record is placed in the table by a user action performed in our Struts/BC4J web application, the Jobs application looks at the record, checks the Job Type, and spawns off the Java code that relates to the requested Job Type.

      Now, in one of our job types, we perform the following:

      Step 1) Using an instance of the root application module, get access to an application module to update the database with the status of the job - "Initialised":

      ApplicationModule myAm = Configuration.createRootApplicationModule("tbs.model.services.TbsService", "TbsServiceConfiguration");
      myAm.clearVOCaches(null, true);

      myAm.getProcessJobsAM().updateJobStatus(job,"I");
      myAm.getTransaction().postChanges();

      Step 2) Update the status of the job to "G"enerating:

      myAm.getProcessJobsAM().updateJobStatus(job,"G");
      myAm.getTransaction().postChanges();

      Step 3) Run a batch file from the Java Command Line:

      Process proc = Runtime.getRuntime().exec(myBatchFile);
      try
      {
      proc.waitFor();
      }
      catch (InterruptedException e)
      {
      // ignore
      }

      Step 4) Update the status of the job to "Completed":

      myAm.getProcessJobsAM().updateJobStatus(job,"Completed");
      myAm.getTransaction().postChanges();


      The problem here is the Step 3 takes around 4 hours to complete (from 18:13:49 to 22:23:15)! My issue is that I seem to be seeing some sort of timeout that causes Step 4 to fail:

      2004-12-06 18:13:49 reportScheduler : Updating Job(928) Status to I
      2004-12-06 18:13:49 reportScheduler : Updating Job(928) Status to G
      2004-12-06 22:23:15 reportScheduler :
      oracle.jbo.SQLStmtException: JBO-27124: SQL error during empty statement creation
      at oracle.jbo.server.DBTransactionImpl.createStatement(DBTransactionImpl.java:2908)
      at oracle.jbo.server.DBTransactionImpl2.createStatement(DBTransactionImpl2.java:396)
      at oracle.jbo.server.DBTransactionImpl.dumpQueryResult(DBTransactionImpl.java:3232)
      at tbs.model.dataaccess.JobsEditRowImpl.getNextSequenceNumber(JobsEditRowImpl.java:133)
      at tbs.model.services.batch.ProcessJobsImpl.insertIntoJobsTable(ProcessJobsImpl.java:708)
      at tbs.batch.JobRunEnvironment.createJob(JobRunEnvironment.java:339)
      at tbs.batch.JobRunEnvironment.createReportingJob(JobRunEnvironment.java:314)
      at tbs.batch.GenerateEOMInvoicesJob.execute(GenerateEOMInvoicesJob.java:87)
      at tbs.batch.JobInitiator.runJob(JobInitiator.java:167)
      at tbs.batch.JobInitiator.run(JobInitiator.java:124)
      at tbs.batch.JobInitiationThread.run(JobInitiationThread.java:163)
      at java.lang.Thread.run(Unknown Source)
      ## Detail 0 ##
      java.sql.SQLException: Io exception: Connection reset
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:187)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:229)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:342)
      at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:574)
      at oracle.jdbc.driver.OracleConnection.privateCreateStatement(OracleConnection.java:795)
      at oracle.jdbc.driver.OracleConnection.createStatement(OracleConnection.java:676)
      at oracle.jbo.server.DBTransactionImpl.createStatement(DBTransactionImpl.java:2889)
      at oracle.jbo.server.DBTransactionImpl2.createStatement(DBTransactionImpl2.java:396)
      at oracle.jbo.server.DBTransactionImpl.dumpQueryResult(DBTransactionImpl.java:3232)
      at tbs.model.dataaccess.JobsEditRowImpl.getNextSequenceNumber(JobsEditRowImpl.java:133)
      at tbs.model.services.batch.ProcessJobsImpl.insertIntoJobsTable(ProcessJobsImpl.java:708)
      at tbs.batch.JobRunEnvironment.createJob(JobRunEnvironment.java:339)
      at tbs.batch.JobRunEnvironment.createReportingJob(JobRunEnvironment.java:314)
      at tbs.batch.GenerateEOMInvoicesJob.execute(GenerateEOMInvoicesJob.java:87)
      at tbs.batch.JobInitiator.runJob(JobInitiator.java:167)
      at tbs.batch.JobInitiator.run(JobInitiator.java:124)
      at tbs.batch.JobInitiationThread.run(JobInitiationThread.java:163)
      at java.lang.Thread.run(Unknown Source)

      I figure that this relates to some sort of timeout - I have had confirmed by the IS Department that the server in which this code runs has associated routers between it and the Oracle database that are configured to clear up any stale/opened connections that are inactive for 3 hours.

      Now, I have had a look at a few settings in BC4J. I added to bc4j.xcfg:

      <jbo.poolmaxinactiveage>86400000</jbo.poolmaxinactiveage>
      <jbo.ampool.maxinactiveage>86400000</jbo.ampool.maxinactiveage>

      and this didn't affect the result - probably confirming that it is a router or some external technology that is clearing up the connection - not BC4J itself.

      I have tried looking at attributes of the application module transaction:

      if (!myAm.getTransaction().isConnected())
      {
      myAm.getTransaction().reconnect();
      }

      The IF statement returns true and so the reconnect() method is called, and I get the following error:

      2004-12-08 16:54:19 reportScheduler : Updating Job(968) Status to I
      2004-12-08 16:54:19 reportScheduler : Updating Job(968) Status to G
      2004-12-08 21:00:17 reportScheduler :
      oracle.jbo.JboException: JBO-25224: Could not disconnect and retain application module state because database state exists for current connection.
      at oracle.jbo.server.DBTransactionImpl.disconnect(DBTransactionImpl.java:4209)
      at oracle.jbo.server.DBTransactionImpl2.disconnect(DBTransactionImpl2.java:306)
      at oracle.jbo.server.DBTransactionImpl2.reconnect(DBTransactionImpl2.java:325)
      at tbs.batch.JobRunEnvironment.createJob(JobRunEnvironment.java:341)
      at tbs.batch.JobRunEnvironment.createReportingJob(JobRunEnvironment.java:314)
      at tbs.batch.GenerateEOMInvoicesJob.execute(GenerateEOMInvoicesJob.java:87)
      at tbs.batch.JobInitiator.runJob(JobInitiator.java:167)
      at tbs.batch.JobInitiator.run(JobInitiator.java:124)
      at tbs.batch.JobInitiationThread.run(JobInitiationThread.java:163)
      at java.lang.Thread.run(Unknown Source)

      So, I am sure that this is something that BC4J/OC4J can overcome, I just don't know what the code ought to be:

      if (!myAm().getTransaction().isConnected())
      {
      < some code to reconnect to database >
      }

      Am I on the right track? Is there some other way to ensure the database transaction in Step 4 completes after such a long period of time?

      I know I could set up something that polls the database every 10 minutes to keep the connection current, but I figure that I shouldn't have to be doing that, given the BC4J API.

      Your assistance with this issue is greatly appreciated.
        • 1. Re: BC4J and Timeouts
          416113
          Hmmmm... its looking like this works:

          if (!am.getTransaction().isConnected())
          {
          am.getTransaction().disconnect(false); // clean up connections
          am.getTransaction().reconnect(); // reconnect to previously established connection
          }

          Cool! Thought I am not too sure of the consequences for all transactions performed by the app. module.