• Design
  • Tuning
  • IBM DB2
  • MySQL
  • Oracle
  • SQL Server

    • DB2 via Linked Server DB2 Stored Proc. OpenQuery

    You are currently using a web browser that does not support HTML pages with style sheets. Please try the XHTML 1 or XHTML 1 Mobile version of this page instead.

    Executing DB2 Stored Procedures through a SQL Server Linked Server

    Most examples on the web show that a DB2 stored procedure can be called from SQL Server through a linked server using:

    exec sp_serveroption 'db2link', 'rpc', 'true' exec sp_serveroption 'db2link', 'rpc out', 'true' declare @in1, @in2, @outvar as char(3) set @in1 = 'ABC' set @in2 = 'GHI' exec db2link..schema.procname @in1, 'DEF', @in2 exec db2link.location.schema.procname @in1, 'DEF', @in2 exec db2link..schema.procname @in1, 'DEF', @in2, @outvar output

    The rpc and rpc out options can also be set through the linked server proprerties. When location is specified, the stored procedure at that location would be executed through the database at the location specified by db2link.

    However, although the driver returns output parameters from the linked server back to SQL Server, output variables are not set when the stored procedure is called this way. Consequently, this works only if the stored procedure has no output parameters, or if the values returned from the stored procedure (such as "@outvar" above) can be ignored, which is a bad thing if success or failure must be determined from those parameters.

    Executing DB2 Stored Procedures with Output Parameters

    In order to access the output parameters, the stored procedure must be called in this manner:

    exec sp_serveroption 'db2link', 'rpc', 'true' exec sp_serveroption 'db2link', 'rpc out', 'true' declare @in1, @in2, @outvar as char(3) set @in1 = 'ABC' set @in2 = 'GHI' exec ('call schema.procname(?,?,?)', @in1, 'DEF', @in2, @outvar output) at db2link exec ('call location.schema.procname(?,?,?)', @in1, 'DEF', @in2, @outvar output) at db2link select @outvar

    The string passed to the exec command is a native call statement. The question marks are placeholders for the parameters, which are bound to the variables specified in the command.

    Accessing the Result Set

    To access the result set:

    select * from openquery(db2link, 'select * from SYSIBM.SYSDUMMY1') select * from openquery(oralink, 'select * from SYS.DUAL')

    back to top

    Last updated Friday April 20, 2007

    Printer-friendly PDF* format:

    Executing Stored Procedures

    How To Guides for SQL Server

    This Section

    You are currently viewing this page in XHTML 2 Style Sheet* format (* see Clicklets for more infomation). This document is also available in HTML Style Sheet*XHTML 1* XML* HTML 4*HTML 5 Style Sheet*HTML 5 XML*HTML 5 non-XML* XHTML Mobile* WML Mobile* and printer-friendly PDF* formats. This is accomplished with Single Source Publishing, a content management system that uses templates in XSLT style sheets provided by XML Styles .com to transform the source content for various content delivery channels. There is also RDF* metadata that describes the content of this document.