SQL Server OPENQUERY to call DB2 Stored Procedure

waiting · #1 (**permalink**) 10-27-10, 14:36

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

Pat Phelan · #2 (**permalink**) 10-27-10, 15:14

What do you get when you try:

Code:

EXECUTE DB2_TEST..Lawson.NextCheck '19', @retval

-PatP

waiting · #3 (**permalink**) 10-27-10, 17:22

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.

Pat Phelan · #4 (**permalink**) 10-28-10, 11:52

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

waiting · #5 (**permalink**) 10-28-10, 13:04

We are actually using IBM's DB2 OLE driver, v9.7

waiting · #6 (**permalink**) 10-29-10, 13:10

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.

Marcus_A · #7 (**permalink**) 10-29-10, 13:28

I would open a PMR with IBM software support.

Internet Services

Web Development

Digital Marketing

Consumer Tech