Many SQL Server environments have DB2 servers running that must link to SQL Server. Linking to a DB2 server is challenging because you must first get values from the DB2 engineers who use different terminology and then use those unfamiliar values for the linking process. This article will help bridge the language gap between SQL Server DBAs and DB2 DBAs while taking you step-by-step through the process of creating a SQL Server linked server.
Installation of DB2 drivers
Since we are discussing a Microsoft production, we'll demonstrate the techniques using Microsoft's own DB2 driver. Microsoft's DB2 driver comes as part of the SQL Server 2005 (or 2000) feature packs and thus needs to be downloaded and installed separately on any server expected to talk to a DB2.
Linked server dialog
Once you have the driver installed, you will attempt to create the linked server and get the following screen:
[IMAGE]
Figure A: Using Microsoft's OLE DB driver to create the linked server.
Microsoft provides very little help in determining what the provider string and other settings should be at this screen prompt. Thankfully, if you look on a server that has the DB2 driver installed, there is a program called "Microsoft OLE DB Provider for DB2," which has the link "Data Access Tool." Basically, the "Data Access Tool" helps you set the provider strings necessary for linked server options. Open the "Data Access Tool" and we'll walk through how to create the various options for the linked Server to DB2.
Step 1
Right click over DB2 OLE DB UDLs and select "new data source."
[IMAGE]Figure B: Select new data souce, under Data Access Tool. (Click on image for enlarged view.)
Step 2
Select the platform that you'll be working with. Since DB2 comes on many platforms, touch base with your DB2 administrator to find out which variation is running. For this example, w
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
e will use AS400's DB2 version.
[IMAGE]
Figure C: Select AS400's DB2 platform to create your linked server.
Step 3
Enter the location of the server. It's best not to use the IP address, since that will change as the server's role swaps (SQL Server equivalent of cluster rolls). A DNS entry is best, and in our case we'll use AS400.CYPHER.NET with the default port. Your environment may have a custom port -- see your DB2 administrator for the port.
[IMAGE]
Figure D: Enter AS400.CYPHER.NET as the server location.
Step 4
This (Figure E) is the dialog where you'll enter the bulk of the necessary connection information. The Initial Catalog option is very important; it's what the RDB name is on the DB2. Typically, it's the name of the server when it was originally built, but it could also be an alias defined by your DB2 DBA. All of the other options are typically set to the same value, which is the library you're attempting to access. Libraries on the DB2 side are very roughly equivalent to databases on the SQL Server side.
[IMAGE]
Figure E: The screen where you'll enter most of the linked server connection information.
Step 5
The next dialog (Figure F) allows you to customize the character set conversions. The AS400 uses EBCDIC character sets, so that would be our host CCSID in this scenario. (See your DB2 DBA for the CCSID in use on their system). The PC code page is the code page in current use on the Windows server. Typically, it should be set to ANSI – Latin 1 for the U.S., but you can customize it for other Windows code pages, depending on the language in your given area.
Normally, the "Process binary as character" setting should be left blank so binary values are handled as binary values. But some destinations systems convert it to char, so the option allows you to pass binary as such.
[IMAGE]
Figure F: In most circumstances, do not check the box "process binary as character."
Step 6
The next screen (Figure G) allows you to enter the username/password that you were given by the DB2 DBA.
[IMAGE]
Figure G: Enter username and password provided by DB2 DBA.
Step 7
Advanced options in Figure H should only be set based on information provided by the DB2 DBA. In some environments you can use connection pooling to reduce the load on the system; in other environments, the link will be read-only, so it could be set accordingly.
[IMAGE]
Figure H: These options should be set according to info provided by the DB2 DBA.
Step 8
In the next screenshot (Figure I), you can choose to connect, which then verifies the settings that were chosen.
[IMAGE]
Figure I: Connect SQL Server to DB2 and verify settings.
Step 9
When you come to the final screenshot, pick your output. Since we are going to enter this information into a linked server, we want to just leave "Universal data link" selected.
[IMAGE]
Figure J: Leave "Universal data link" selected.
Verification
Figure K shows you how your "Data Access Tool" should now appear.
[IMAGE]Figure K: How the "data access tool" should appear now. (Click on image for enlarged view.)
Grabbing the provider string
Right-click over DB2/AS400, click "display connection string." The provider string will appear in the bottom window.
[IMAGE]Figure L: The provider string appears in the bottom window. (Click on image for enlarged view.)
Setting up the linked server
We obviously need the provider string for the linked server setup. However, we don't want to include the username or password, so we'll modify our string as follows:
[IMAGE]Figure M: The data source/catalog should match on most systems and will be the name of the server. (Click on image for enlarged view.)
[IMAGE]Figure N: Define username/password for your linked SQL Server. (Click on image for enlarged view.)
Notes:
Now you have created your first linked server from SQL Server to DB2. I have also given you some hints on how to increase your flexibility during role swaps as well as how to perform DML operations against the servers.
[TABLE]
MEMBER FEEDBACK TO THIS TIP
Do you have comments of your own? Let us know.
Great tip. I see people in forums always trying to learn how to connect to DB2, particularly DB2 400. I had to do it in the past and it was always painful. I have bookmarked this tip to give to people who are struggling with it.
—Jack C.
******************************************
This is the best tip on how to do it. I have it book marked to help others. I have been trying for the past 7 months and gotten no where.
It took me less than 30 minutes, going through your steps.
U R the best. Thanks for the help.
—Francis D.
