Free SQL Help for Devs, DBAs, and the Curious

For enterprises that manage multiple databases across different platforms, combining data from these varying sources may present a complex challenge. However, SQL Server provides a function called OPENQUERY that allows users to execute a pass-through query on the specified linked server, which can be a server of any type, including DB2. This allows for cross-platform joins, enabling the retrieval and integration of data from disparate systems.

To utilize OPENQUERY, you first need to establish linked servers. In SQL Server, you do this by leveraging the sp_addlinkedserver system stored procedure. This stored procedure adds a linked server and allows SQL Server to execute commands against OLE DB data sources on remote servers, such as DB2. This procedure takes the linked server name, server type, provider name, and other optional parameters.

EXEC sp_addlinkedserver

@server='DB2LINK',

@srvproduct='DB2',

@provider='MSDASQL',

@datasrc='YourDB2DataSource'

With the linked server established, you can now use OPENQUERY to execute cross-platform joins. The OPENQUERY function takes two parameters: the linked server name and the query to be executed on the linked server. It's important to note that the query is executed on the linked server itself, and only the results are returned to SQL Server, providing a boost in performance.

SELECT * FROM OPENQUERY(DB2LINK, 'SELECT column1, column2 FROM DB2Table')

This method can be extended to perform a join between a table in SQL Server and a table in DB2. For example, if you have a table in SQL Server 2012 named SQLTable and a table in DB2 named DB2Table, and you want to join these two tables on a common column named CommonColumn, you can use the following query:

SELECT * FROM OPENQUERY(DB2LINK, 'SELECT column1, column2 FROM DB2Table') AS DB2,

SQLTable AS SQL

WHERE DB2.CommonColumn = SQL.CommonColumn

Using OPENQUERY for cross-platform joins can also extend to Azure SQL and MySQL. However, it's important to note that SQL Server 2014 or later is required for Azure SQL, while MySQL 5.6 or later is needed for MySQL. The setup process is similar, with the addition of a linked server for the respective database, followed by the use of OPENQUERY to perform the join. Remember, OPENQUERY provides not only a practical solution for cross-platform joins but also a performance advantage, as it allows for the execution of queries on the linked server, returning only the result set to SQL Server. This makes it a valuable tool in the arsenal of any DBA dealing with multiple database platforms.