Query syntax for Linked Table from SQL Server to AS400

Michael Purdham
Michael Purdham used Ask the Experts™
on
I have reposted this question as I seem to have categorised it incorrectly.
Old one is deleted.

I have SQL Server 2008 R2 where I have created a linked server to my AS400 using the following
Linked Server=TRDATA
Provider=IBM DB2 UDB for iSeries IBMDA400 OLE Provider
Data source= Hostname of server
Provider string = User Id=uuuu;Password=pppp;Default Collection=LibraryName
Location = Blank
Catalog = Blank

If I right click on the Linked Server and select Test Connection this is successful.

I want now to just select data from a table XXXX on the AS400 in library YYYY

Given the above what is the query to pull records where field CCCC = 'FRED'

I have searched for a definitive statement that works and have tried many permutations but all fail.

Can anyone give the precise statement?
Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ramromconsultant

Commented:
Since no AS400 experts have responded - you can continue to wait - or humor me by telling me what you have tried. I googled and found some ideas - but want to see what you've tried.

Author

Commented:
From what I've read I need to use something like this but don't know where commas, semi-columns, quotes etc should be
SELECT * FROM
OPENDATASOURCE('IBMDA400','Provider=IBMDA400;Driver={Client Access ODBC Driver (32-bit)};SYSTEM=Hostname of server
;UID=xxxx;PWD=xxxx;DBQ=QGPL;CMT=0;FORCE TRANSLATE=37;SEARCHPATTERN=0 ;PREFETCH=1').QGPL.TRDATA.NEWORDSHIP

This gives me this error
Msg 7303, Level 16, State 1, Line 16
Cannot initialize the data source object of OLE DB provider "IBMDA400" for linked server "(null)".
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Since you already have the linked server created, assuming the security is right, you should be able to do something like this:

SELECT ...
FROM OPENQUERY(TRDATA, 'SELECT * FROM TRDATA.NEWORDSHIP')

Object name may need changed, but the general format should be good.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

ramromconsultant

Commented:
According to http://msdn.microsoft.com/en-us/library/ms179856.aspx OPENDATASOURCE takes 2 arguments, a provider name (character string) and an init_string  (character string like
 is 'kwd1=value2;kwd2=value2').

Your OPENDATASOURCE meets these requirements. So commas, semi-columns, quotes seem OK.

The error refers to linked server "(null)". I would think it should be "TRDATA". How to get there is another question!

I will keep digging.

Author

Commented:
Thanks, that returns data now without error message however much of the data is scrambled. For example these are supposed to be dates.
0xC6F0F0F1F0F0F0      0xF0F0F0F1F0F0F1
0xC6F0F0F1F0F0F1      0xF0F0F0F1F0F0F0
0xC6F0F0F1F0F0F2      0xF0F0F0F1F0F0F2
0xF3F0F0F8F7F1F3      0xF3F0F0F6F5F7F4
0xF3F0F0F8F7F1F4      0xF3F0F0F6F5F6F6

ODBC queries over DB400 need 'Convert binary data (CCSID 65335) to text' to be set in the ODBC Data Source. I suspect that this is similar to the FORCE TRANSLATE=37 that was in my original question.
There are a number of possible things to comment on, and it's hard to tell which affects what. A simple one to start...

ODBC queries over DB400 need 'Convert binary data (CCSID 65335) to text' to be set in the ODBC Data Source.

That may or may not be true. It depends on whether the AS/400 was properly configured at the system level, whether DB2 on the AS/400 has decent fixes applied, whether the tables were created properly, whether you're actually querying against SQL tables or native physical files (or logical files), whether they're System/36 environment files, etc...

If someone set the AS/400 up properly and created proper tables, then there's no reason to convert; and in fact conversion might mess stuff up.

Without knowing what you're querying against, you're likely to get various "Try this, then try this..." suggestions until some combination works.

However, your general thought seems potentially in the right direction based on what you show for "dates". More or less.

Next...

SELECT * FROM
OPENDATASOURCE('IBMDA400','Provider=IBMDA400;Driver={Client Access ODBC Driver (32-bit)};SYSTEM=Hostname of server
;UID=xxxx;PWD=xxxx;DBQ=QGPL;CMT=0;FORCE TRANSLATE=37;SEARCHPATTERN=0 ;PREFETCH=1').QGPL.TRDATA.NEWORDSHIP


That appears to show a four-part name in a form something like:

OPENDATASOURCE()
QGPL
TRDATA
NEWORDSHIP

From other comments, I might think that TRDATA is the schema (or "library") and NEWORDSHIP is the table in that schema that you want to query.

But why is QGPL included there?

QGPL is a 'General Purpose Library' that should exist on all AS/400s. It's not clear why it's referenced here. Is it only because it was shown in other examples from somewhere?

Tom
Solution:
Now that I have inserted TRANSLATE=37 in the Provider string in the linked server setup this works properly.

So to make this work I created a linked server in SQL Server Management Studio with the following settings

Linked Server=LSTRDATA
Provider=IBM DB2 UDB for iSeries IBMDA400 OLE Provider
Data source= Hostname of server
Provider string = User Id=uuuu;Password=pppp;Default Collection=LibraryName;TRANSLATE=37
Location = Blank
Catalog = Blank

SELECT *
FROM OPENQUERY(LSTRDATA, 'SELECT * FROM LibraryName.TableName')

Thanks for all input

Author

Commented:
My comment summarises the solution in simple terms.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial