Executing DB2/UDB Stored Procedure from SQL Linked Server

Last Post 02 Nov 2006 06:43 AM by galmar. 12 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
timobr76
New Member
New Member

--
27 Apr 2006 12:53 PM
I have a series of stored procedures and tables on a UDB database that I need to access. Reading tables and views via Select, OpenQuery and OpenRowset are not a problem and function properly. However, all attempts to execute DB2/UDB stored procedures from SQL Server fail. I can however execute these DB2/UDB stored procedures from code (VB) using ADO/OLE DB.

SQL Server 2000, current service pack
IBM UDB Client v8.2, Fixpack 9a

Linked Server Definition:

EXEC sp_addlinkedserver
@server='TEST2',
@srvproduct='IBM OLE DB Provider for DB2',
@catalog='DB2',
@provider='IBMDADB2',
@provstr='Hostname=w2kmnrbap405;Database=TNEDEV;Protocol=TCPIP;Port=60004'
EXEC sp_addlinkedsrvlogin 'TEST2', 'false', NULL, 'db2user', 'db2pwrd'
RPC enabled via Enterprise Manager

SQL to execute stored procedure (no parameters, not that it matters, get same result):

exec TEST2..TNE.NXT_DOCID_REQ

Error Message Returned:

[OLE/DB provider returned message: [DB2/NT] SQL0108N The name "NXT_DOCID_REQ" has the wrong number of qualifiers. SQLSTATE=42601]
[OLE/DB provider returned message: [DB2/NT] SQL0104N An unexpected token "1" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "< values >". SQLSTATE=42601]

Regardless of how I configure the linked server or format the SQL to execute the stored procedure I pretty much get the same result. It appears that the OLE DB translation of the stored procedure call is syntactically incorrect when DB2 processes it, but this is only a guess.

The only real difference is when I use the OpenRowset SQL as follows:

SELECT * FROM OPENROWSET('IBMDADB2','Driver={IBM OLE DB Provider for DB2};Hostname=w2kmnrbap405;Database=TNEDEV;Protocol=TCPIP;Port=60004';'db2user';'db2pwrd',
'TNE.NXT_DOCID_REQ')

I get the following message:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'TNE.NXT_DOCID_REQ'. The OLE DB provider 'IBMDADB2' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='IBMDADB2', Query=TNE.NXT_DOCID_REQ'].

The only thing on the web I've found is that you may be able to wrap the stored procedure in a function that returns a table. While I have yet to try this, it should't be necessary.

If anyone has had success in executing DB2/UDB stored procedures from within a SQL Server Linked Server, I would greatly appreciate any insight you may have.

Many thanks in advance
SQLUSA
New Member
New Member

--
07 May 2006 02:09 PM
Have you tried setting the "rpc out" option?

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/articles2005/top10/
The Best SQL Server 2005 Training in the World
timobr76
New Member
New Member

--
18 May 2006 10:19 AM
I have tried the "rpc out" option(s) with no luck.

From what I'm hearing from various sources, stored procedure calls from an SQL Server linked server (or OpenRowSet) to UDB are not supported, regardless if using the IBM UDB driver or the Microsoft OLE DB driver. Sounds very strange, and I am shocked that I've not been able to find any clear documentation on this. I did see a reference on MSDN indicating one type of stored procedure call was not supported but another one was.

I also found a reference from an IBM employee on the UDB/DB2 developer group suggesting that the stored procedure be wrapped inside a runction that returns a table. We tried this and received the same result.

I've also sent a request to IBM (through our IBM DB2 DBA team) for information, but as yet, nothing.

If you can provide anything else, let me know. I certainly do appreciate your help
galmar
New Member
New Member

--
02 Nov 2006 06:43 AM
I have a smiliar problem.
Did you have any response, or come up with a solution yet ?

Peter Galmar, Copenhagen
galmar
New Member
New Member

--
11 Dec 2006 05:37 AM
I have just received the information from Microsoft Support, that DB2 stored procedures are not supported via linked server.
It is implicitly documented in the last sentence of this excerpt from BOL for SQL Server 2005, in the chapter for SP_ADDLINKEDSERVER:
Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.
as400
New Member
New Member

--
09 Mar 2007 09:06 PM
I know its been a while but thanks for the update. I may have to put a stop in trying to make the DB2 "OLE" call to a SP work.

Ive been trying with no luck using the ole IBMDASQL to work by calling a SP on the iSeries. However, I was able to get the MSDASQL ole that wraps an ODBC provider from client access to work, just make sure you set on the MSDTC 'Enable XA transaction" on. Every example i threw at it worked, from EXE 4-part, EXEC AT and OPENQRY. The IBMDASQL OLE fails in all case on stored procedures calls, SELECT statements work like a charm though. The interesting part is that the IBMDASQL call to the SP on the iseries executed! But I would always get a return message like "MSG 0 Line 0...."

Ive journaled all required files, rpc on, MSDTC set up, host server started on iSeries... Ive come to the point theres nothing else to do.

Anyway, its interesting that no one has stood up and said you cant call a sp from tsql using the new ole on DB2 box, such as the iSeries. In addition, im not surprised that there is not one example that can be found.

It may be just as well. May be better to start using SQLCLR there you can use the even better provider than ODBC or OLE, the new .NET providers for DB2 and iSeries. Its a trick to even get this to work within SQLCRL.

Here you must assembly the providers *.DLL from SQL Server 2005, making sure you set to "UNSAFE" and the DB "trustworthy". After that you will be able to import, using, the provider within VS 2005.

...you would think after all these years...
quozzle
New Member
New Member

--
06 Aug 2007 06:14 AM
Russell,

Could you provide an example and maybe the file name of the .dll you use? You are the first person in all of the research that I've done that has been able to get this to work! Thanks!
quozzle
New Member
New Member

--
06 Aug 2007 09:19 AM
From a DTS package I am executing a program call as:

call JMP005S ('51', 'PLAN')

We need to get it to work from a SQL Server stored proc and eliminate the DTS execution. There is no return data.

However, this SQL procedure on the DB2 actually, in turn, calls either a CL or RPG program (I'm trying to find out which). So the answer may be just bypass the SQL and go direct to the RPG.

We've got both SQL & RPG development going on so we can program anything we need to. I stick to the SQL Server so I just need a good direction to send the AS400 guys in. I'll work with the dba to hunt up the .dll.

Everything else via linked servers works great. Love the speed and it sure beats waiting for replication to a database that other applications share. Appreciate the quick feedback!

quozzle
New Member
New Member

--
08 Aug 2007 12:30 AM
Three things I need to do. 1) Get the correct .dll (same one as used in DTS) in a linked server entry. 2) SET NOCOUNT ON. 3) Call the RPG, not the SP. I have to work with some other people so it will be a few days. However, this is the most hope I've had that I can eventually get this thing to work!

Thanks so much for your insight. I'll post back when I get more info/results.
smikusek
New Member
New Member

--
22 Aug 2007 08:01 AM
I am not sure if you have been able to resolve your problem, but we were able to exec a procedure on a AS400 from a linked server on SQL 2005.

Here is an example of what we did:

Declare @B varchar(8)
Declare @A varchar(8)

select @B = replace(convert(varchar, '01/01/2007', 102), '.', '')
select @A = replace(convert(varchar, '12/31/2007', 102), '.','')

--(?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?,?) the ? represent the number of parameters expected.
--'','', @A , @B, '', '', '', '', '','', '', '', '', '', '', '', 'N') these are the values being passed from SQL to procedure.

EXEC ('CALL LIBRARY.AS400STOREDPROC(?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?,?) ',
'','', @A , @B, '', '', '', '', '','', '', '', '', '', '', '', 'N') AT LINKEDSERVERNAME

Hope this helps.

quozzle
New Member
New Member

--
23 Aug 2007 09:55 AM
Well, here's the current scoop. We were also able to get a call to an RPG procedure to work in SQL 2005 using a different syntax. Unfortunately, the application I need it for is still on 2000 and no plans to upgrade in the near future.

Per IBM, here's the reason it doesn't work (copied below - you need an IBM ID): SQL Server: Unable to Call Parameterized Stored Procedures

quote:

Document Number: 26814647
____________________________________________________________
Product: IBM ISERIES CLIENT ACCESS EXP (5722XE100)
Release: V5R1M0; V5R2M0; V5R3M0; V5R4M0
___________________________________________________________
Document Title:SQL Server: Unable to Call Parameterized Stored Procedures
Document Description:
A connection to an iSeries database is configured as a linked server in SQL Server via ODBC (using the MSDASQL OLEDB provider for ODBC Data Sources) or using the IBM's IBMDA400 OLEDB provider. An attempt to call a parameterized stored procedure through these linked server connections will fail.

For example, consider the following call executed in Microsoft query manager:

DECLARE @P1 AS CHAR(10)
DECLARE @P2 AS DECIMAL(8,2)
SET @P1='TEST'
SET @P2=0
EXECUTE ODBCSLH.RCHASSLH.MYLIB.SPCLINOUT @P1 OUTPUT, @P2 OUTPUT
PRINT @P1
PRINT @P2

When run over MSDASQL and ODBC, SQL Server converts the call to the following ODBC syntax: {?=call "RCHASSLH"."MYLIB"."SPCLINOUT";1(?,?)}. This will, and should, result in a syntax error. The semicolon followed by the number 1 does not belong in this statement and it results in an SQL0104
being generated. This appears to be a defect or limitation in the SQL Server product.

If the link uses the IBMDA400 OLEDB provider SQL Server sets the command text to the same statement which was passed to the ODBC driver: {?=call "RCHASSLH"."MYLIB"."SPCLINOUT";1(?,?)}. This also causes a syntax error in the OLEDB provider but this time at the open curly brace. The curly brace itself is valid but the IBMDA400 OLEDB provider does not support return codes from stored procedures and of course the invalid ';1' is in this statement as well so there are two other syntax errors in this statement.
If the statement passed to the OLEDB provider had been "{ CALL
"RCHASSLH"."MYLIB"."SPCLINOUT"(?,?)}", then it would have executed the call once the parameters were bound. This again appears to be a defect or limitation in the SQL Server product.

Note: Document 24061227, SQL Server Linked Server Error: Object has no columns, provides an explanation of a related failure that occurs with a call of a stored procedure that has no output (OUT) or input-output (INOUT) parameters but which returns a result set. To link to document 24061227
immediately, click here (Document link: Database 'Rochester Support Line KnowledgeBase', View 'All Documents', Document 'SQL Server Linked Server Error: Object has no columns.').


In a nutshell, SQL doesn't translate the statement correctly.

To make matters worse for me, although the insert and delete do work via linked servers (tested on 3 different .dlls) the performance for anything bigger than a small dataset is abysmal. Microsoft's reason for this is something like it wasn't designed that way so don't use it: Slow DELETE or UPDATE against non-SQL linked server

Due to time contraints to get the project out, I will have to stick with the existing DTS/VBScript packages and (yuk) creat
lbrock
New Member
New Member

--
28 Oct 2009 12:10 PM
smikusek,

I cannot tell you how long I have been looking for this solution.

I found this post a couple of weeks ago and since that time have been able to successfully implement calling a stored procedure on the AS400 via our SQL Server 2005 linked server to insert data on the iSeries database. In addition, I've been able to do this with a trigger.

What a relief to find this forum. We were not able to get an answer from the IBM reps, online searches in Microsoft, or any other avenue until finding your comment to timobr76.

lbrock
stacey.doran
New Member
New Member

--
11 Jan 2010 11:35 AM
I don't quite get the syntax for solution suggested below:

I need to use this solution to call a stored proc on db2 that takes 2 parameter which are varchars and I attempted to modify the solution below to fit but iI fails.

"Invalid Conversion" Could not execute statement on remote server 'LINKEDSERVERNAME'.

I must be missing something in the suggested solution syntax.

Here is what i modified the suggested solution below to:
Declare @A varchar(5)
Declare @B varchar(7)
select @A = 'XXXXX'
select @B 'YYYYYYY'

EXEC ('CALL LIBRARY.AS400STOREDPROC(?,?)',
@A, @B) AT LINKEDSERVERNAME


Here is the original solution post.
quote:

Originally posted by: smikusek
I am not sure if you have been able to resolve your problem, but we were able to exec a procedure on a AS400 from a linked server on SQL 2005.

Here is an example of what we did:

Declare @B varchar(8)
Declare @A varchar(8)

select @B = replace(convert(varchar, '01/01/2007', 102), '.', '')
select @A = replace(convert(varchar, '12/31/2007', 102), '.','')

--(?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?,?) the ? represent the number of parameters expected.
--'','', @A , @B, '', '', '', '', '','', '', '', '', '', '', '', 'N') these are the values being passed from SQL to procedure.

EXEC ('CALL LIBRARY.AS400STOREDPROC(?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?,?) ',
'','', @A , @B, '', '', '', '', '','', '', '', '', '', '', '', 'N') AT LINKEDSERVERNAME

Hope this helps.




You are not authorized to post a reply.

Acceptable Use Policy