674: Routine can not be resolved.
A shop migrating servers piecemeal from IDS 7.31 to 11.70 found that remote stored-procedure calls from a 7.31 server to the 11.70 server (e.g. 'execute procedure db@server:procname()' or selecting from it) always failed with error 674, 'Routine cannot be resolved'. Calls in the other direction and 11.70-to-11.70 worked fine, and 11.70.UC5 behaved the same. Suggestions included qualifying with the owner, granting EXECUTE to PUBLIC, routine-overloading/signature differences between versions, and recreating routines as functions. The poster finally reported that IBM Support confirmed it as a defect and supplied a fix, after which remote procedure calls worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello! We are working with a enterprise resource planning system, where the DB-Servers are running under IDS.7.31.UD10X1 (OS: SLES 11 SP1) and we want to migrate step by step all of these Servers. We have migrated now the first of our DB-Servers to 11.70.UC4 (OS: SLES 11 SP1). The other Servers in our Environment are still working under IDS.7.31.UD10X1. The Migration of this Server to 11.70.UC4 works fine. We could work now with different Ifx-Versions and the connections and ER works fine, too. But now the Problem: We use many Stored Procedures under 7.31 and now after the migration we had the Problem, if we want to do a remote request from 7.31 to the 11.70 DB like for example "select db@servername:procname('Parameter') from db@1170server:table" we get the following error: "674: Routine (routinename) can not be resolved." The behaviour is the same when we try a sql like "execute procedure db@servername:procname('Parameter');" instead of the "select"-SQL above: "674: Routine (routinename) can not be resolved." This error appears for every Procedure/Routine, when we do the Call from the 7.31-Server. If we do the request from 11.70 to 7.31, we had no problem. If we do a request from 11.70 to 11.70 there is no Problem, too. We must have a possibility to work the next weeks with different Informix-Versions, because we could not upgrade all of our Servers at the same time. Our environment is too big to do this "short-dated" and our Servers are required 24/7. Excepting the "Remote-Stored-Procedure-Calls" everything works fine between 7.31 and 11.70. Has anyone a tipp or clue or experiences relating to this problem ? Every help would be appreciated. Thanks in advance. Sascha
Hello. What is your 11.70 edition? I read some versions are not able to do remote DML operations, but in your case, if "11.70 -> others" works fine, you might be hitting some bug. I would recommend you (seriously) to use 11.70.xC5, since several bugs were fixed ;) try it out and see if that is your case... Hope it helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator - Cleartech Ltda BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: sascha.kuratis@westfleisch.de > Subject: 674: Routine can not be resolved. [27459] > Date: Mon, 25 Jun 2012 08:40:58 -0400 > > Hello! > > We are working with a enterprise resource planning system, where the > DB-Servers are running under IDS.7.31.UD10X1 (OS: SLES 11 SP1) and we want to > migrate step by step all of these Servers. > > We have migrated now the first of our DB-Servers to 11.70.UC4 (OS: SLES 11 > SP1). > The other Servers in our Environment are still working under IDS.7.31.UD10X1. > The Migration of this Server to 11.70.UC4 works fine. > We could work now with different Ifx-Versions and the connections and ER works > fine, too. > But now the Problem: > We use many Stored Procedures under 7.31 and now after the migration we had > the Problem, if we want to do a remote request from 7.31 to the 11.70 DB like > for example "select db@servername:procname('Parameter') from > db@1170server:table" > we get the following error: > "674: Routine (routinename) can not be resolved." > > The behaviour is the same when we try a sql like > "execute procedure db@servername:procname('Parameter');" instead of the > "select"-SQL above: > "674: Routine (routinename) can not be resolved." > > This error appears for every Procedure/Routine, when we do the Call from the > 7.31-Server. If we do the request from 11.70 to 7.31, we had no problem. If we > do a request from 11.70 to 11.70 there is no Problem, too. > > We must have a possibility to work the next weeks with different > Informix-Versions, because we could not upgrade all of our Servers at the same > time. Our environment is too big to do this "short-dated" and our Servers are > required 24/7. Excepting the "Remote-Stored-Procedure-Calls" everything works > fine between 7.31 and 11.70. > > Has anyone a tipp or clue or experiences relating to this problem ? > > Every help would be appreciated. > > Thanks in advance. > > Sascha > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Hi! Thanks for your response. We use "Informix Ultimate Edition 11.70.UC4" on Suse Linux Enterprise Server 11 SP1. We have tested this with 11.70.UC5, but the Behaviour is the same. We get the same error "674: Routine (routinename) can not be resolved." under 11.70.UC5, too. Sascha
Ok, have you also tried to put the procedure owner inside the statement? Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator - Cleartech Ltda BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: sascha.kuratis@westfleisch.de > Subject: Re: RE: 674: Routine can not be resolved. [27461] > Date: Mon, 25 Jun 2012 09:21:44 -0400 > > Hi! > Thanks for your response. > > We use "Informix Ultimate Edition 11.70.UC4" on Suse Linux Enterprise Server > 11 SP1. > We have tested this with 11.70.UC5, but the Behaviour is the same. > We get the same error "674: Routine (routinename) can not be resolved." under > 11.70.UC5, too. > > Sascha > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
This is probably because 7.31 does not support routine overloading and 11.70 does, so 7.31 is not sending enough information to the 11.70 instance to uniquely identify the function/procedure you want to execute. Versions after 9.10 expect the routine signature that is passed in to include the argument type list and return type list and 7.31 didn't/doesn't need to do that. Open a support case with IBM as they may have a work around for you like adding a specific name to the routine on the 11.70 side or something like that. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jun 25, 2012 at 9:21 AM, SASCHA KURATIS < sascha.kuratis@westfleisch.de> wrote: > Hi! > Thanks for your response. > > We use "Informix Ultimate Edition 11.70.UC4" on Suse Linux Enterprise > Server > 11 SP1. > We have tested this with 11.70.UC5, but the Behaviour is the same. > We get the same error "674: Routine (routinename) can not be resolved." > under > 11.70.UC5, too. > > Sascha > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f83a1294335c404c34c9a79
For debugging purposes, can you "GRANT EXECUTE ON procedure TO PUBLIC;" on 11.70? Just for one procedure of course. Regards On Mon, Jun 25, 2012 at 1:40 PM, SASCHA KURATIS < sascha.kuratis@westfleisch.de> wrote: > Hello! > > We are working with a enterprise resource planning system, where the > DB-Servers are running under IDS.7.31.UD10X1 (OS: SLES 11 SP1) and we want > to > migrate step by step all of these Servers. > > We have migrated now the first of our DB-Servers to 11.70.UC4 (OS: SLES 11 > SP1). > The other Servers in our Environment are still working under > IDS.7.31.UD10X1. > The Migration of this Server to 11.70.UC4 works fine. > We could work now with different Ifx-Versions and the connections and ER > works > fine, too. > But now the Problem: > We use many Stored Procedures under 7.31 and now after the migration we had > the Problem, if we want to do a remote request from 7.31 to the 11.70 DB > like > for example "select db@servername:procname('Parameter') from > db@1170server:table" > we get the following error: > "674: Routine (routinename) can not be resolved." > > The behaviour is the same when we try a sql like > "execute procedure db@servername:procname('Parameter');" instead of the > "select"-SQL above: > "674: Routine (routinename) can not be resolved." > > This error appears for every Procedure/Routine, when we do the Call from > the > 7.31-Server. If we do the request from 11.70 to 7.31, we had no problem. > If we > do a request from 11.70 to 11.70 there is no Problem, too. > > We must have a possibility to work the next weeks with different > Informix-Versions, because we could not upgrade all of our Servers at the > same > time. Our environment is too big to do this "short-dated" and our Servers > are > required 24/7. Excepting the "Remote-Stored-Procedure-Calls" everything > works > fine between 7.31 and 11.70. > > Has anyone a tipp or clue or experiences relating to this problem ? > > Every help would be appreciated. > > Thanks in advance. > > Sascha > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00248c6a6726104dc004c352648a
Hi Sasha, This is just a guess, but I vaguely recall a problem I had upgrading from 7.31 to 9. In version 9 (and after) the definition of a procedure changed. A procedure is a routine that does not return values, and a function is a routine that does return values. You might try dropping the procedures and re-creating them using the CREATE FUNCTION statement in the v11 engine. Have a look in the v11 documentation for the CREATE FUNCTION statement. Its worth a try on a test system. I am not sure how the engine deals with this when the SQL is coming from version 7 which would be looking for a procedure, but in v11 its actually a function. Cheers, Jason
The IBM-Support could help. It was a defect under 11.70.UC4 and we get a fix from IBM. We could now work remote with stored-procedures. Thanks for your responses. Sascha