Select from IBM DB2 using Openquery() and member

Question

I want to fetch some data from an IBM DB2 server using SQL Server 2008.

For this I have tried to use:

SELECT * From Openquery(LINKED_SRV,'Select * from dta.Filename')

which gives me data, albeit rather old data.

I have since gathered that the updated data is in another Member on the server, and that the default member is the outdated one I tried to import data from.

I am told the current Member is PROD2017. I have read some IBM DB2 documentation where a Library.Filename(Member) convention is used, but using:

 SELECT * From Openquery(LINKED_SRV,'Select * from dta.Filename(PROD2017)')

but this gives me an error that ( is not supported.

Of several attempts which has failed I have also tried to use:

 SELECT * From Openquery(LINKED_SRV,'Select * from dta.Filename"(PROD2017)"')

which actually gives a result set, but it is the same result set as the original one omitting the membership, so I'd reckon there is some issue with the Query either way.

How can I find data using openquery() from an IBM DB2 server using Sql server, when the DB2 server uses membership?

Answer 1

I received a Notable Question award for this question today and I saw that it doesn't answer how I got this issue solved.

Mind you, this is a 3 year old question so my specifics may be a little bit off but I remember it as the following:

I queried a member for all production of 2017, called PROD2017 I understood this as a book analogy, where PROD2017 was the name of the book and that my query returned a chapter. For me, the query returned the wrong chapter (I believe January's sales or something where I wanted August's). I told the company responsible for the data in the IBM DB2 connection and they had to redo their "sorting of the book", so that the query would return the correct month's data.

I believe they had to do this each month, but once again I'm iffy on the details seeing as how long ago this was.

Hopefully this can help someone, though :)

Answer 2

Cenderze,

From my SQL server I can query my DB2 machine through a linked server without OPENQUERY. For example:

select t1.ordno from LinkedServer.Database.Library.Table t1

Note the use of aliasing and the 4-part identifier. Replace these with your specifics and let me know what happens.

M