Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    4

    Unanswered: SQL Server OPENQUERY to call DB2 Stored Procedure

    Hi, new here, and new to db2. I'm pretty much a lightweight on DB anyway, but we've been using Informix for the last 13 years and are attempting a migration to DB2.

    I've rewritten all my stored procedures and views for DB2 and they seem to all work great, so long as I'm executing them on the host server.

    However, what I really need is to execute these from SQL server via a linked server. The SQL server admin has installed a OLE DB linked server, as he had for the Informix server, and it works great for SELECT queries, but not for stored procedures.

    For example, this works fine:
    Code:
    SELECT * FROM OPENQUERY (DB2_TEST, 'SELECT * FROM Lawson.CuCodes;')
    I can execute the procedure from the command prompt on the DB2 server no problem:
    Code:
    $ db2 "call Lawson.NextCheck('19' , ?)"
    
      Value of output parameters
      --------------------------
      Parameter Name  : CHECKNBR
      Parameter Value : 15835865
    
      Return Status = 0
    However, if I attempt to call it from the openquery it returns and error:
    Code:
    SELECT * FROM OPENQUERY (DB2_TEST, 'CALL Lawson.NextCheck ( ''19'' , ? )') 
    
    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object "CALL Lawson.NextCheck ( '19' , ? )". The OLE DB provider "IBMDADB2.DB2COPY1" for linked server "DB2_TEST" indicates that either the object has no columns or the current user does not have permissions on that object.
    I've tried testing the OLE DB connection using the DB2 owner's permissions, but that didn't help.

    Is this an easy fix?

    64 bit DB2 v9.1.0.7 fix pack 7
    SQLServer 2005 sp3
    IBM OLE DB driver v9.7
    Last edited by waiting; 10-27-10 at 15:55. Reason: Added version info

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What do you get when you try:
    Code:
    EXECUTE DB2_TEST..Lawson.NextCheck '19', @retval
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Posts
    4
    Code:
    DECLARE @retval INTEGER
    EXECUTE DB2_TEST..Lawson.NextCheck '19', @retval
    
    OLE DB provider "IBMDADB2.DB2COPY1" for linked server "DB2_TEST" returned message "[DB2/HP64] SQL0440N  No authorized routine named "Lawson.NextCheck" of type "PROCEDURE" having compatible arguments was found.  SQLSTATE=42884
    ".
    Msg 7212, Level 17, State 1, Line 2
    Could not execute procedure 'NextCheck' on remote server 'DB2_TEST'.
    And what confuses me about that is that I can call it successfully from the DB2 prompt, and my OPENQUERY SELECT queries work great too.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on that error message, the odds are very good that you aren't running Version 3 of the Microsoft DB2 OLE DB driver. Earlier versions of the OLE DB driver don't support stored procedures in DB/2.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2010
    Posts
    4
    We are actually using IBM's DB2 OLE driver, v9.7

  6. #6
    Join Date
    Oct 2010
    Posts
    4
    I'm always amazed that these kinda questions are so hard to get any good information on. I've googled and googled, and I've posted it here. I would imagine that we're not the only ones in the world doing this. In fact, it seems like it'd be pretty routine.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would open a PMR with IBM software support.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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