4 Replies Latest reply on Mar 5, 2012 5:37 PM by Mikereiche-Oracle

    Any implication of using fn-bea:async


      I read what I could find about fn-bea:asynch, but I am left with some questions about its specifics. We have a function that performs a relatively slow database stored procedure. The logical service is called as a web service, and it invokes the stored procedure using fn-bea:asynch, presumably so that the web service can return while the stored procedure is still running.

      declare function tns:LDRuleCalc($customerId as xs:string, $startDate as xs:dateTime, $endDate as xs:dateTime) {
           let $x:= fn-bea:async(ld_1:LD_RULE_CALC($customerId, $startDate, $endDate))
           return -1

      My question is whether this works the way we are expecting. That is, will it make the call and leave that running, allowing the function to return? If so, what happens to the database transaction context? Will that be handled correctly by ODSI so that the connection remains until the database call completes, and then be returned to the connection pool?

      The reason I ask is that we are seeing some weirdness in our connection pool since we put this code into production. Intermittently, a function will be unable to obtain a db connection, or it gets one but only after a 20 or 30 second wait.

      Thank you for any info!

        • 1. Re: Any implication of using fn-bea:async
          Hi Jeff, good to hear from you again.

          When exposed as a ws, the whole xquery is executed before the call returns. There is no benefit of using async in hope of leaving something running beyond the life of the query.

          For xqse exposed as a ws, the net result is the same, but for a slightly different reason. Although the result of an xqse could be possibly computed without waiting for the sp to complete, all the threads need to complete (or be killed) before the query can return.

          I suppose that if there was a timeout, followed by an attempt to kill the thread that does not succeed in killing the thread. That could leave the thread running the stored procedure after the call returned (but there would be an exception for the timeout unless it was caught). Unless specified otherwise, I think the timeout for a call is 30 seconds.

          Without exposing as a ws and using the java api with hasNext()/next(), the initial call only begins execution of a query, results are not computed (i.e. underlying calls are not made) until the results are "pulled" back to the client. And there is nothing that forces the client to read all the results or disconnect - so the client could simply abandon the call and leaving the executing query in limbo - complete with any resources (db connections) it is holding. At some point the session ejb for the query will be "idled" (serialized) and all the db connections should be eventually released.

          I'm not sure what could be going on with your connection pool. Unless exceptions are being caught with fail-over, it's usually clear when things go bad.
          • 2. Re: Any implication of using fn-bea:async
            If this is ALDSP 3.2, you should install the Patch ID 13835030 (bug 8188541)
            If this is an earlier version, let me know.

            Does the query use any timeout or fail-over functions?
            • 3. Re: Any implication of using fn-bea:async
              Thanks for the quick reply, Mike.

              I think we will remove that async call, since it doesn't seem to be doing what the programmer intended anyway. We have been experimenting with a few unrelated changes we think might have resolved the issue (particularly the caching of ken generation SEQUENCEs in the database).

              Regarding that patch, I am not familiar with it...how can I see release notes for it?


              • 4. Re: Any implication of using fn-bea:async
                Jeff -

                The patch is only for ALDSP 3.2, it is fixed in ODSI. You should be able to search metalink.oracle.com to find the patch.

                - mike