Resolving The Problem

Microsoft's SQL Server product is often used to link to DB2 UDB for iSeries as a linked server. This connection is an OLEDB consumer so it can be made using the OLEDB providers shipped with iSeries Access (IBMDA400 or IBMDASQL) or it can use the iSeries Access ODBC driver through Microsoft's OLEDB provider for ODBC Data Sources (MSDASQL). When the linked server is created, the connection properties can be defined on the provider string. The special properties for the iSeries Access OLEDB providers may be specified here, separating each property from the next using semicolons. An example might look similar to the following:



In this case, the linked server XC6_IBMDASQL is being created. The IBMDASQL provider is selected as the OLEDB provider. The system name is used as the Data Source. In this example, the Provider string is specifying the properties, "Force Translate=0;Sort Sequence=3;Sort Table=MIKSWENS/EN_US_SW". Refer to the following Rochester Support Center knowledgebase document for a complete listing of all of the custom properties available for the iSeries Access OLEDB providers:

iSeries Access for Windows Custom Connection Properties for OLE DB

The ones specified here force conversion of character data stored in CCSID 65535 and will use a custom sort sequence table, MIKSWENS/EN_US_SW that was created to provide a custom sort order.

Queries are performed against the linked servers using one of two syntaxes. The first one uses a 4-part naming convention that consists of the linked server name, the database or catalog name, the collection (library), and table or view (file). An example using the server defined above looks similar to the following:

SELECT TEXT1 FROM XC6_IBMDASQL.RCHASXC6.MIKSWENS.UNITEST ORDER BY TEXT1

This should look familiar to anyone with a passing knowledge of SQL. In this case though it will produce incorrect output, the results will not be sorted as they should be. The reason for this is that SQL Server does not pass this query directly on to the OLEDB provider. Ultimately, it executes the select statement and performs the sort using default sort order that was defined during the install of SQL Server. Although this syntax is more familiar, it does not provide correct output.

The second syntax looks more like two queries, a select of the data returned by a passthrough query. The query equivalent to the one above looks like the following:

SELECT * FROM OPENQUERY ( XC6_IBMDASQL, 'SELECT TEXT1 FROM MIKSWENS.UNITEST ORDER BY TEXT1' )

This second query actually performs better than the first because SQL Server makes fewer communications flows to the server. When the first syntax is used, SQL Server runs the statement several times so that in can retrieve metadata before it finally runs a version of the statement from which it will retrieve results.