StarQuest Technical Documents

How to Use a Microsoft SQL Server Linked Server to Access DB2 with StarSQL

Last Update: 14 August 2017
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ004

Abstract

SQL Server linked servers can be used to access DB2 or Apache Derby through a StarSQL ODBC data source. This document demonstrates how to add and use a linked server. These instructions apply to SQL Server 2005 and later.

Solution

Perform the following steps to configure, test, and use a linked server.

StarSQL (64-bit) users should review the StarQuest Technical Document Considerations for using StarSQL (64-bit) with SQL Server 2005 and Later .

Configure a Linked Server

1. Launch the SQL Server Management Studio.
2. Under the Server Objects folder, right-click on Linked Servers and select New Linked Server.
3. Enter in a name for the Linked Server and choose "Other data source". Do not use any spaces or special characters in the Linked Server name.
4. Select "Microsoft OLE DB Provider for ODBC Drivers" as the Provider Name.
5. Enter MSDASQL for the Product Name.
6. In the "Data source" field, enter in the name of the ODBC system data source you wish to use. Note that you must enter in the Data Source name exactly as it is shown in the ODBC Data Source Administrator.

OR: In the "Provider String" field, enter a DSN-less connection string; see Using a DSN-less Connection with StarSQL.

6. Select the Security page and select "Be made using this security context". Enter a valid remote user/login (user ID) and password for the remote host.
7. Leave the remaining fields blank and click OK to save the linked server configuration.

Test the Linked Server

If an error occurs while testing the linked server connection, review the error and make changes to the linked server configuration as necessary.

SQL Server 2005:

In the Management Studio, click on the New Query button and execute a query using the samples below as a guideline.

SQL Server 2008 & Later:

In the Management Studio, expand the linked server node and then expand the Catalogs node. Click on the node for the linked server and expand Tables to view a table list.

Sample Linked Server SQL Syntax

The following examples demonstrate how to use a linked server to execute SELECT, INSERT, UPDATE, and DELETE statements.  Execute these statements in the New Query field. These examples assume the following:

• "STARSQLDSN" is a linked server that accesses DB2 through a StarSQL data source.
  
• TITLES is a valid table located within a library (or with a schema name) called BOOKS, TITLE is a valid column in the TITLES table.
• <catalog> is the first part of the three part name used by StarSQL (catalog.schema.table) and is typically the same as the Database Server Name (RDBNAME). See below for additional considerations.

To perform a SELECT on the table:

SELECT * FROM STARSQLDSN.<catalog>.BOOKS.TITLES

OR

SELECT * FROM OPENQUERY (STARSQLDSN, 'SELECT * FROM BOOKS.TITLES')

OR

EXEC ('SELECT * FROM BOOKS.TITLES') AT STARSQLDSN

Note: Using EXEC requires enabling the RPC & RPC Out checkboxes in the properties of the Linked Server.

To INSERT a row into the table:

INSERT INTO STARSQLDSN.<catalog>.BOOKS.TITLES (TITLE) VALUES ('The Grapes of Wrath')

OR

INSERT INTO OPENQUERY (STARSQLDSN, 'SELECT * FROM BOOKS.TITLES') (TITLE) VALUES ('The Grapes of Wrath')

To UPDATE a row in the table:

UPDATE STARSQLDSN.<catalog>.BOOKS.TITLES SET TITLE = 'Of Mice and Men' WHERE TITLE = 'The Grapes of Wrath'

OR

UPDATE OPENQUERY (STARSQLDSN, 'SELECT * FROM BOOKS.TITLES') SET TITLE = 'Of Mice and Men' WHERE TITLE = 'The Grapes of Wrath'

To DELETE a row in the table:

DELETE STARSQLDSN.<catalog>.BOOKS.TITLES WHERE TITLE = 'The Grapes of Wrath'

OR

DELETE OPENQUERY (STARSQLDSN, 'SELECT * FROM BOOKS.TITLES') WHERE TITLE = 'The Grapes of Wrath'

To call a stored procedure:

The following example calls a stored procedure called "MYSP" (located in the schema "BOOKS") which uses two parameters: @p1 is an input parameter and @p2 is an output parameter. The final Select statement displays the results which are stored in @p2.

declare @p1 varchar(50)
declare @P2 varchar(500)
set @p1 = 'first parameter value';
EXEC ('CALL BOOKS.MYSP (?,?)', @p1,@p2 OUTPUT) AT STARSQLDSN
Select @p2;

NOTES

• To perform DELETE or UPDATE operations, the target table must have a unique index. For more information, please refer to the section titled UPDATE and DELETE Requirements for OLE DB Providers in the SQL Server Books Online.

• By default, StarSQL uses three part naming (<catalog>.<schema>.<table>); the catalog is typically the same as the Database Server Name (RDBNAME). Linked Server nomenclature adds the requirement of further qualification with the linked server name i.e. <linked_server>.<catalog>.<schema>.<table>. StarSQL support of three part names can be defeated with the optional ODBC data source expert setting GetInfoCatalogUsage set to 0. In this case, the linked server name should appear as <linked_server>..<schema>.<table>.

PERFORMANCE HINTS

Depending on your environment and requirements, the following suggestions may enhance your performance:

• Use the OPENQUERY (pass-through query) form of the various SQL commands.
• For the MSDASQL provider: select the properties of the MSDASQL provider (under Linked Servers/Providers) and enable these properties:
  

  Dynamic Parameter; Nested queries; Allow inprocess; Non transacted updates; Index as access path; Supports 'Like' operator.

We recommend that you do not enable "Level zero only"; see below for the error message that will result. Note that these settings will apply to all Linked Servers using the MSDASQL provider.

• For the individual Linked Server "Server Options", we have no specific recommended settings, although remote collation can be a performance enhancement to the extent that the "rules" governing the usage apply; we recommend reviewing the help for this and the other parameters.
  
  
• SQL Server 2008 adds additional "Server" options for the Linked Server. Of the new options, we recommend enabling the "Lazy Schema Validation", especially if Linked Servers are used for updating rows on the host.
  

TROUBLESHOOTING

• If the table being accessed has an index defined on more than 16 columns, you may receive this error message:

  OLE DB provider "MSDASQL" for linked server "<linkedserver>" returned an invalid index definition for table ""<catalog>"."<schema>"."<tablename>"".

As a workaround, either use the OPENQUERY syntax to query this table or remove the offending index. Refer to the "Maximum Capacity Specifications for SQL Server" section of the SQL Server Books Online for more information.

• If the MSDASQL provider has been configured for "Level zero only", you may receive this error message when executing a SQL statement using four-part naming (e.g. SELECT * FROM STARSQLDSN.MYRDB.BOOKS.TITLE)):

  Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "SQLSQLDSN". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
  

Solution: either open the properties of the MSDASQL provider (under Linked Servers/Providers) and uncheck "Level zero only", or use the OPENQUERY or EXEC syntax.

If you are using a clustered SQL Server environment, it may be necessary to restart SQL Server services on all machines for changes to take effect.

• Using SQL Server Studio, when you expand the catalog of a linked server and and right-click on a table, the drop-down menu offers options such as Script Table As/Select To../New Query Window and Script Table As/Insert To../New Query Window. When using the MSDASQL provider, many of those options result in the error message:

[STARSQLDSN].[MYRDB].[BOOKS].[TITLE] contains no columns that can be inserted or the current user does not have permissions on that object.

Solution: Enter the desired SQL statement in the Query Window.

• If there are a large number of tables in the host database, expanding the table list in SQL Server Management Studio may fail with this error:

Failed to retrieve data for this request (Microsoft.SqlServer.Management.Sdk.Sfc)

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for link server "SIETE".
The provider supports the interface, but returns a failure code when it is used (Microsoft SQL Server, Error: 7311)

Solution: Modify the StarSQL ODBC data source or connection string and specify a Catalog Filter to limit the results to a list of tables from a subset of schemas.