PRB: Error 7321 Occurs When You Run a Query That Uses Four-Part Names Against DB2 on an AS/400 Computer (278984)

SYMPTOMS

When all of the following conditions are met:

You have defined a linked server with DB2 on AS/400 as a back-end server.
You run a SQL Server query using four-part names against the linked server.
You enable the Nested queries property of the OLE DB provider that is used by the linked server.

The SQL Server statement may return the following error message:

Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while
preparing a query for execution against OLE DB provider 'DB2OLEDB'.
[OLE/DB provider returned message: A SQL error has occurred. Please
consult the documentation for your specific DB2 version for a
description of the associated Native Error and SQL State.
SQLSTATE: 42707, SQLCODE: -208]

MORE INFORMATION

Background

SQL Server 7.0 and SQL Server 2000 are compliant to the Entry level of the SQL-92 standard. This standard specifies an ORDER BY clause only for a DECLARE CURSOR statement, not for a plain SELECT statement. However, the SQL-92 standard does specify that you need to use the column name and not the column alias in the GROUP BY, WHERE and HAVING clauses for plain SELECT statements. So, SQL Server uses a syntax that is in line with the explicit rules of the SQL-92 standard and is supported by most other OLE DB providers and SQL dialects. Unfortunately, there are versions of DB2 on AS/400 that do not understand this syntax. If you think you are experiencing this problem, you can confirm this by using these steps:

Run set showplan_text on before you run the problem query to get the query plan.
Look at all the nodes of the query plan that are marked "Remote Query". If there is one remote query that looks like this:
```
"SELECT ColName ColAlias FROM <three-part table name> ...ORDER BY ColName" 
						
```
You are experiencing this problem.
Run set showplan_text off to disable the output of query plans.

Steps to Reproduce Problem

Given there are copies of the tables authors, titles and titleauthor of the pubs database on the DB2 system and the preceding requirements are met, you can reproduce the problem with this SQL statement (assuming that the linked server="DB2", catalog="Salvator" and schema="pubs"):

select a.AU_ID, a.AU_LNAME, a.AU_FNAME, a.PHONE, a.CITY, t.TITLE
from DB2.SALVATOR.PUBS.AUTHORS a
left join DB2.SALVATOR.PUBS.TITLEAUTHOR ta
on (a.AU_ID=ta.AU_ID and ta.AU_ORD>1)
left join DB2.SALVATOR.PUBS.TITLES t
on ta.TITLE_ID=t.TITLE_ID
order by a.AU_LNAME, a.AU_FNAME

The query produces this plan:

  |--Nested Loops(Left Outer Join)
       |--Remote Query(SELECT a.AU_ID Col1008,a.AU_LNAME Col1009,a.AU_FNAME Col1010,a.PHONE Col1011,a.CITY Col1012 FROM SALVATOR.PUBS.AUTHORS a ORDER BY a.AU_LNAME ASC,a.AU_FNAME ASC)
       |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([DB2].[SALVATOR].[PUBS].[TITLEAUTHOR].[TITLE_ID])=([DB2].[SALVATOR].[PUBS].[TITLES].[TITLE_ID]), RESIDUAL:([DB2].[SALVATOR].[PUBS].[TITLEAUTHOR].[TITLE_ID]=[DB2].[SALVATOR].[PUBS].[TITLES].[TITLE_ID]))
            |--Filter(WHERE:([DB2].[SALVATOR].[PUBS].[TITLEAUTHOR].[AU_ID]=[DB2].[SALVATOR].[PUBS].[AUTHORS].[AU_ID] AND [DB2].[SALVATOR].[PUBS].[TITLEAUTHOR].[AU_ORD]>1))
            |    |--Table Spool
            |         |--Remote Query(SELECT ta.TITLE_ID Col1024,ta.AU_ID Col1023,ta.AU_ORD Col1025 FROM SALVATOR.PUBS.TITLEAUTHOR ta ORDER BY ta.TITLE_ID ASC)
            |--Table Spool
                 |--Remote Query(SELECT t.TITLE Col1022,t.TITLE_ID Col1021 FROM SALVATOR.PUBS.TITLES t ORDER BY t.TITLE_ID ASC)

NOTE: The last "Remote Query" node sends this SQL statement to the linked server:

 SELECT ... t.TITLE_ID Col1021 FROM ... ORDER BY t.TITLE_ID ASC

The preceding line is the offending part of the query.

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.