Unanswered: v8 Stored procedure problem on AIX (SQLCODE -440)
I have DB2 version 8 running on AIX.
I have stored procedures, writted in c++ that I have cataloged with the following:
DB2 CONNECT TO DEVLDB USER WMMISX@
CREATE PROCEDURE MQEQSTATUS (IN CMPNY CHAR(3), IN UNIT CHAR(13), IN OLDSTATUS CHAR(5),OUT RESPONSE CHAR(254),OUT ERRORCODE INTEGER)
DYNAMIC RESULT SETS 1
PARAMETER STYLE SQL
FENCED NOT THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'MQProcedures!MqEqStatus'@
- The create worked and it cataloged the procedures using wmmisx as the schema name.....so far so good!
- I then went to my c++ application and tried to call the procedures and thats when I received the -440 error.
- In my application I then changed the "CALL" statement and qualified the stored procedure with the schema name ( WMMISX.CALL (?) ) and the application worked! no problem.
- The problem is, for our needs here I cannot create code that needs to have the stored procedure name qualified, we need the ability to have DB2 resolve the function as the IBM documentation says it will.
- In the BIND and PREP I tried SYSFUNC and QUALIFIER to see if I could add "WMMISX" to the "CURRENT PATH" and it does not have any effect.
- I then added a line of code to my application see what the "CURRENT PATH' was set to and it was just the standard path "SYSIBM", etc.. with my own user id appended to the end (just as the manuals describe it would)
I think if I can get the "CURRENT PATH" modified to add the "WMMISX" name from above DB2 could then resolve the name....
This is an app that I inherited.... but it was previously running on DB2 version 7 so now that version 8 requires the extrnal stored procedures to be cataloged.
I believe that version 7 just used the id that the application was compiled with (prep and bind), when running the build script here, lets say for the development version, the script asks for the ID and password (in this case wmmisx), the prep and bind run, so db2 just used the id that you entered for the schema name and everything worked fine.
version 8 does not allow host variables in call statements anymore so I changed all the call statements to dynamic calls, then cataloged the stored procedures and so on.....
if the prep and bind or something cannot force an application to append a schema name to the current path then I can find someplace to put the current schema name and then load it when the app loads, it just seems that there might be a better way.....