How to create a SQL Server linked server to DB2
Matthew Schroeder, Contributor
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in August 2008
a DB2.
Linked server dialog
Once you have the driver installed, you will attempt to create the linked server and get the
following screen:
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."
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.
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.
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.
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.
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.
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.
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.
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.
Figure J: Leave "Universal data link" selected.
Verification
Figure K shows you how your "Data Access Tool" should now appear.
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.
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.
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.)
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
 |
| Comment on this tip, or get related SQL Server tips: |
|
|
|
|
|
|
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
Matthew Schroeder is a senior software engineer who works on SQL Server database
systems ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. He specializes in
OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. Matthew 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 [email protected].
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation