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, we 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 ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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:
- Paste the modified provider string into the appropriate field
Provider=DB2OLEDB;Initial Catalog=RDBNAME;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=AS400.CYPHER.NET;Network Port=446;Package Collection=Library;Default Schema=Library;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=Library;DBMS Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=False;Connection Pooling=False;Derive Parameters=False;
- Define the product name – this is something to identify the connection.
- The data source/catalog should match on most systems and will be the name of the server. (See Figure M.)
- Define your username/password as in Figure N.
[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:
- Occasionally, loss of network connectivity requires that the SQL Server instance be restarted in order for the DB2 driver to reset.
- I recommend against using the system name as the RDB name, since it will change with role swaps. It's better to use an alias as the RDB name, since the alias can be modified to point to the system name for whatever server you're pointing at. That way, you have no code modifications for your linked server during role swaps.
- Trying to do a DML operation (insert/update/delete) against a DB2 system over a linked server requires you to turn on journaling (roughly equivalent to SQL Server logging) for the destination object. This is because SQL Server automatically creates transactions surrounding DML operations over a linked server, and the functionality cannot be turned off.
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.
ABOUT THE AUTHOR:
[IMAGE]Matthew Schroeder is a senior software engineer working on SQL Server database systems, ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. Matt currently works for the gaming vendor, IGT, providing services to gaming companies. He also works as an independent consultant, specializing in SQL Server, Oracle and .NET for industries such as gaming, automotive, e-commerce, entertainment, banking and non-profit. Matt specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. He is a Microsoft certified MCITP, Database Developer, has a master's degree in computer science and more than 12 years of experience in SQL Server/Oracle. He can be reached at cyberstrike@aggressivecoding.com.
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.
