Hi All,

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
LANGUAGE C
PARAMETER STYLE SQL
NO DBINFO
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....

any help would be greatly appreciated!!!!!


thanks...Bob

Bob,
You can set the CURRENT PATH using the command:

SET CURRENT PATH = CURRENT PATH,WMMISX

HTH

Andy

hi Andy,

thanks for replying!!!

I tried that just that, from the command prompt I ran the set current path, it changed the path, I verified it changed by using values(current path) from a db2 prompt that it did change,

but..... as always it seems:-)

when I ran my app, I stopped at a break point to check the path and it was back to the standard path!!!

Somehow it seems the application must be setting the path back to standard ?

Bob,
You need to execute the SET CURRENT PATH inside your app.
Andy

that did it!!!!!

but since we use 3 different schemas, one for development, test and production, do you have any ideas on how I can pass down to the code which one to set ?

thanks!!!
Bob

Bob,
How are you currently telling your app which schema to use?
Andy

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.....


thanks again....
bob

There is an ugly solution - define your stored procedure in all possible schemas.
You can have multiple stored procedures referencing the same extrenal code (test.sp, prod.sp and so on)

Basically it will work as some kind of aliasing.

regards,
dmitri