Linking a DB2 AS400 server

Question

Linking a DB2 AS400 server

Alejandro DAndrea

SSC Veteran

Points: 275
More actions
May 21, 2004 at 9:19 am

#359813

Hey, I've been trying for a while to link an AS400 DB2 database to a SQL server. Actually, I need to be able to run queries against that DB2, but declaring a linked server sounds like a good option.
The problem is I just don't know what provider to use or how to get it. I've tried several ways, and all I could get was a suggestion about intalling Host Integration Server. Does anyone know of another way? Where can I get the DLLs, connection strings, etc to do this??
By the way, I have the "IBM AS400 Client Access Express" on my PC, so I already have the "Client Access ODBC Driver (32-bit)" ODBC driver, if that helps...
Anyone can lead me please!
Thanks a lot,
Alejandro

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Nilssond Hall of Fame Points: 3285 More actions · Answer 1

The Client Access Express driver should work, as long as the AS/400 is using the same version ( could be up to V5R3M0 ).

Set up a valid DSN using the Client Access Driver you have. Then, in EM, set up the linked server and in the linked server set up applet, use the Microsoft OLEDB Provider for ODBC, Product Name is DB2 and the Provider String will be

DSN=<your new DSN name>

Make certain that you have already set up the alias for the AS/400 DB2 server

in your SQL Server Client Network Utility.

Alejandro DAndrea SSC Veteran Points: 275 More actions · Answer 2

YOU-ARE-THE-MAN!!!

I have really been around with this... thanks a lot!!!

Just a brief follow up:

Can you tell me how to write the sql? (server, schema, catalog, etc). I don't really what the schema and catalog are in DB2. I tried:

select * from [linked_server_name]..[library].[table_name]

select * from [linked_server_name].[library]..[table_name]

but none works....

Thanks again!!!

Alejandro

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 3

Hi Alejandro

This might be useful. From BOL,

OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2, distributed with Microsoft® Host Integration Server 2000, allows Microsoft SQL Server™ 2000 distributed queries to query data in DB2 databases.

To create a linked server to access a DB2 database

Install the Windows NT Client for Host Integration Server 2000 or the Windows 9x Client for Host Integration Server 2000 on a computer running an instance of SQL Server. Select the options to install the OLE DB Provider for DB2 and the network components needed to communicate with an IBM computer running in an SNA network.

Determine the connection string the OLE DB Provider for DB2 needs to access the DB2 data source you want to query. The best way to determine a connection string is to build a Data Link file using the Host Integration Server New OLE DB Data Source application. For more information, see the Microsoft Host Integration Server 2000 documentation.

Execute sp_addlinkedserver to create a linked server, specifying DB2OLEDB as the provider_name, the name of the DB2 catalog containing the data you want to access as catalog, and the connection string from Step 2 as provider_string.

This example shows how to use sp_addlinkedserver to create a linked server definition accessing a DB2 database:

EXEC sp_addlinkedserver @server = 'DB2SRV',

@srvproduct = 'Microsoft OLE DB Provider for DB2',

@catalog = 'SEATTLE',

@provider = 'DB2OLEDB',

@provstr =

'NetLib=SNA;NetAddr=;NetPort=;RemoteLU=SEATTLE;LocalLU=LOCAL;

ModeName=QPCSUPP;InitCat=SEATTLE;

Default Schema=WNW3XX;PkgCol=WNW3XX;TPName=;Commit=YES;

IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;

Data Source=Seattle_WNW3XX'

Execute sp_addlinkedsrvlogin to create login mappings from SQL Server 2000 logins to DB2 logins.

This example maps the SQL Server 2000 login SQLJoe to DB2 login DB2Joe:

       EXEC sp_addlinkedsrvlogin 'DB2SRV', false, 'SQLJoe', 'DB2Joe', 'JoePwd'

After completing these steps, you can use the linked server name DB2SRV as the server name in four part names and as linked_server in the OPENQUERY function. For example:

    SELECT *    FROM DB2SRV.SEATTLE.WNW3XX.DEPARTMENT
 
    Or
 
    SELECT *    FROM OPENQUERY(DB2SRV, 'SELECT * FROM SEATTLE.WNW3XX.EMP_ACT')
    
    When the distributed queries against DB2 data sources involve NULL comparisons, use IS 
    NULL or IS NOT NULL rather than comparison operators, such as =, <, or >. In addition, 
    INSERT statements should supply values for all columns in a table even if certain columns 
    in the table can be NULL or have default values.

Lucky

Nilssond Hall of Fame Points: 3285 More actions · Answer 4

Select <fieldname1>, <fieldname-n> from [Servername].<DatabaseName>.<LibararyName>.<TableName> where <conditions>

Best of luck.

Alejandro DAndrea SSC Veteran Points: 275 More actions · Answer 5

Ok, I read that syntax somewhere. The problem is that I don't know what to use as <DatabaseName> . I mean, when I enter AS400 and run queries from STRSQL, I logon to a SERVERNAME, and perform a query against a TABLENAME located in a LIBRARY (select * from library/tablename). But I never seem to specify a DATABASE. How can I found out what DATABASE to use? Thanks again, sorry I'm being such a newbie at this!

Best regards

Alejandro DAndrea SSC Veteran Points: 275 More actions · Answer 6

Never mind!! Got it!! I just had to repeat the server's name as Database (without domain qualification). Something like:

SELECT DISTINCT name FROM [SRV1.mydomain.com].[SRV1].[LIBTEST].[SOMETABLE]

Thanks a lot for your help!!!

Nilssond Hall of Fame Points: 3285 More actions · Answer 7

Get a copy of Client Express Toolkit (FREE) to see some samples of executing Select statements against a DB2 database.

Alejandro DAndrea SSC Veteran Points: 275 More actions · Answer 8

Where can I get that one? I searched google for "Client Express Toolkit" and got noting...

And finally, do you have any idea of how I can install the "Client Access ODBC Driver (32-bit)" without having to install the whole "IBM Personal Communications" or the "IBM AS400 Client Access Express" software?

To begin with, they lost the installer CDs for those 2 softwares here at work . Moreover, I'd like to avoid installing the whole access software in a server where I only need the ODBC driver. I searched all over the net, and also inside IBM.com but nothing....

Thanks again!!

Nilssond Hall of Fame Points: 3285 More actions · Answer 9

http://www-1.ibm.com/servers/eserver/iseries/access/toolkit/

Daniel Sousa Newbie Points: 5 More actions · Answer 10

Hi,

I´m trying to link my DB2 server but I have this error:

"Error 7339: OLE DB Provider 'MDASQL' reported an error. Data Source not found and no default driver specified]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize:: Initialize return 0x80004005: ]."

Can anybody help me?

Thanks

Daniel Sousa

chuk cha Newbie Points: 3 More actions · Answer 11

I am having the same issue.

The problem is to find 'provider_name' in your case you specified MDASQL - which is a driver for MS SQL Server.

Does any body know what the provider name is for iSeries 400?

Mike Yankilevich Grasshopper Points: 13 More actions · Answer 12

Mike Yankilevich

Grasshopper

Points: 13

July 3, 2007 at 7:22 am

#716865

This is a such great thread!

Mike Yankilevich Grasshopper Points: 13 More actions · Answer 13

Hello, lucky...

Is this post still current for SQL Server 2005?

I am just getting into SQL Server. We installed SQL Server 2005 on Windows 2000 Server. Which host integration server should I use?

I am very new to this, so I am just feeling my way through this.

Mike Yankilevich Grasshopper Points: 13 More actions · Answer 14

I ca not figure out if Host Integration Server is free or not. Any suggestions?