Re: Stored procedure problem

Informix have admitted this is a known bug and is fixed in 9.3 onwards. They suggest running Update Statistics between SPL calls. Which is fine - except.... our application needs to call the procedure many times within one transaction - and what happens when you update stats for a function? - it locks SYSPROCPLAN - and concurrency goes out the window!!!! Marvellous Ian preetinder dhaliwal <preetinder.dhaliwal@dhl.com> wrote in message news:<bigd3s$oqg$1@terabinaries.xmission.com>... > It looks like - for 1 session , server resolves the procedure or > function just once. > > Only way out looks like in script - > > dbaccess<<! > database db1; > execute procedure proc1(acc,site1);> close database; > database db1; > execute procedure proc1(acc,site2);> close database; > ! > > ( or put above in a for loop - but you have to close and open database ) > > If u turn the trace on - you will see that second time the call results > in an existing proc id . > > Rgds > Preetinder > > Ian Briscoe wrote: > > >Running IDS 9.21UC4 on Unixware 7.11 > > > >We have a number of identically structured databases in our group - an > >accounts system. We want to be able to link common accounts together > >for reporting purposes. > > > >Each site will have 2 stored procedures: > >get_account - to which will be passed a company identifier, an > >account number,and the name of the remote server (site_name). This > >will be run locally. > > > >get_account_details - to which will be passed the same company > >identifier and the account number. This will be run remotely. > > > >get_account has the line: > >LET execute_string = > >"mach4@"||trim(site_name)||":get_account_details"; > >CALL execute_string(company_id,customer_acc) returning........ > > > >this in effect builds and executes the remote call (database always > >called mach4). > >Problem is: > >in dbaccess.... > >If I run 'get_account' for a given accont number and site_name I get > >back the results from the remote site as I expected. > >But if I then run it for the same acount at a different site I'm > >getting the SAME results as from the first site. > >If I exit dbaccess and run the procedure for the second site it works > >fine. > >If I run "mach4@site_name:get_account_details" directly from a local > >site to a remote site it works fine time after time. > >The problem only seems to be when one SPL is calling another? > >This is driving me bonkers!!!! > > > >Any tips? > >TIA > > > >Ian > > > > > > sending to informix-list