Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Florida
    Posts
    15

    Question Unanswered: v8 Stored procedure problem on AIX (SQLCODE -440)

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Bob,
    You can set the CURRENT PATH using the command:

    SET CURRENT PATH = CURRENT PATH,WMMISX

    HTH

    Andy

  3. #3
    Join Date
    Oct 2003
    Location
    Florida
    Posts
    15
    Originally posted by ARWinner
    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 ?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Bob,
    You need to execute the SET CURRENT PATH inside your app.
    Andy

    Originally posted by bobnorma
    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 ?

  5. #5
    Join Date
    Oct 2003
    Location
    Florida
    Posts
    15

    Talking

    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

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Bob,
    How are you currently telling your app which schema to use?
    Andy

    Originally posted by bobnorma
    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

  7. #7
    Join Date
    Oct 2003
    Location
    Florida
    Posts
    15
    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

  8. #8
    Join Date
    Dec 2002
    Posts
    134
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •