Call DB2 stored procedure from SQL Server 2008 linked server

Question

I have a linked server from SQL Server 2008 to DB2. The linked server uses the IBM Drivers and not the Microsoft ones.

So this works from SQL Server:

exec ('call RERTEBT.GET_DEFINITION (69,'''','''')') AT MyLinkedDB2Server

This also works using openQuery... which is returning different data from another table

select
RPMG_ETY_CD,
     ROW_CU_DATA_IN,
ROW_EF_DT,
ROW_XPR_DT,
RPMG_ETY_NM
from 
OPENQUERY 
         (MyLinkedDB2Server,
                'select 
                      RPMG_ETY_CD,
                      ROW_CU_DATA_IN,
                      ROW_EF_DT,
                      ROW_XPR_DT,
                      RPMG_ETY_NM
                 from RERTEBT.V1RERRMM')

However I cannot get a select to return data with the DB2 Sproc

This fails -

SELECT FLT_DFN_ID, FLT_SRC_DFN_NO, FLT_VRSN_NO, FLT_STAT_CD, FLT_TY_CD, FLT_NAME 
      FROM OPENQUERY (MyLinkedDB2Server, 
                         'call RERTEBT.GET_DEFINITION 69,'''','''')')

Has anyone any idea on how to call a DB2 stored procedure from SQL Server Linked server and return the data or can this be done. I read somewhere the DB2 cant do this but haven't seen any real documentation on it.

Thanks D

Answer 1

More explanation for Josef's answer: You need to right-click the linked server's "properties" then -> "Server option" The "RPC" and "RPC Out" option in the right pane need to be TRUE

-- edited -- I can't comment on the answer yet (don't have 50 rep)

Answer 2

Your should be able to do this:

EXEC ('{CALL RERTEBT.GET_DEFINITION (69,'''','''')}') AT MyLinkedDB2Server;

Or even cleaner with passing variables

EXEC ('{CALL RERTEBT.GET_DEFINITION (?,?,?)}', 69, '', '') AT MyLinkedDB2Server;