Error trying to Select x rows from DB2 (V4R5M0) via sql server linked server using OPENQUERY

Question

I have a Linked Server from SQL Server 2008 R2, to a DB2 Database (V4R5M0) using OLE DB provider "IBMDA400"

Linked Server Detials

EXEC master.dbo.sp_addlinkedserver 
     @server = N'JTEST', @srvproduct=N'IBM OLE DB Provider for DB2', 
     @provider=N'IBMDA400', @datasrc=N'TestName'

This works fine:

SELECT * FROM OPENQUERY(JTEST, 'Select * from QSYS2.SYSCOLUMNS')

But the following statement produces an error:

SELECT * FROM OPENQUERY(JTEST, 'Select * from QSYS2.SYSCOLUMNS FETCH FIRST 10 ROWS ONLY')

Error

LE DB provider "IBMDA400" for linked server "JTEST" returned message "SQL0199: Keyword FETCH not expected. Valid tokens: FOR WITH ORDER UNION OPTIMIZE. Cause . . . . . : The keyword FETCH was not expected here. A syntax error was detected at keyword FETCH. The partial list of valid tokens is FOR WITH ORDER UNION OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "Select * from QSYS2.SYSCOLUMNS FETCH FIRST 10 ROWS ONLY" for execution against OLE DB provider "IBMDA400" for linked server "JTEST".

I think it's because FETCH FIRST X ROWS ONLY is not supported in this version of DB2? But is there any way of selecting only a limited record set in this version?

Dollydolman

Answer 1

Wouldn't FETCH FIRST need an ORDER BY? Otherwise, which 10 rows would it get?

As far as I can see in the DB2 SQL docs, it is supported on current and older versions, although it doesn't state (and it not clear) if ORDER BY is mandatory with FETCH FIRST

Dice

Adding the Order By, didn't resolve the issue faced. It presented a new error too: OLE DB provider "IBMDA400" for linked server "JTEST" returned message "SQL0199: Keyword FETCH not expected. Valid tokens: <END-OF-STATEMENT>. Cause . . . . . : The keyword FETCH was not expected here. A syntax error was detected at keyword FETCH. The partial list of valid tokens is <END-OF-STATEMENT>. This list assumes that the statement is correct up to the unexpected keyword. – Dollydolman

Which DB2 SQL Documents are you looking at please? – Dollydolman

Answer 2

Fetch first clause work only from V5R1 OS400 version. V4R5M0 is too old

There's a workaround: select * from (
SELECT syscolumns.* , row_number() over() as nre FROM syscolumns
ORDER BY COLUMN_NAME ) as columns
where nre<10

You can try it

---

DEpe

Implosive

I couldn't get this to work either. It also produced an error. - I do how ever have a link to the IBM support pages for this release in case this is useful for anyone else: publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm – Dollydolman