Full solution to SQL Serve database connection to DB2 Server

The following articles mainly introduce the implementation process of connecting the SQL Server database to the DB2 connection server. We all know the interconnection between different database platforms, which is generally referred to as the heterogeneous service of databases, currently, all major databases can achieve such Heterogeneous Interconnection, but the specific implementation technologies of different vendors are different, such:

In SQL SERVER, it is called LINKED SERVER, and ODBC is used to interconnect with other databases.

The interconnection of different database platforms is generally called the heterogeneous service of databases. Currently, all major databases can achieve such Heterogeneous Interconnection, but the specific implementation technologies of different vendors are different, such: in SQL SERVER, it is called LINKED SERVER, and ODBC is used to interconnect with other databases.

The test environment in this article is:

Operating System: WINDOWS2000 SERVER (Traditional Chinese System)

Install the database: SQLSERVER2000 (English version) and DB2 7.2 client (English version)

Specific steps:

1. Install the DB2 connection Server Client software and sqlserver2000 software on the PC.

2. Configure the ODBC data source for windows:

Choose "start"> "program set"> "System Management Tools"> "Data Source"> "ODBC" to configure the user DSN or system DSN: add-select IBM DB2 odbc driver-> Custom Data Source Name (for example, DB2TEST)-> Database Name (required !) -"OK.

3. Select the name of the data source you Just configured, and then select Configuration to jump out of DB2 MESSAGES:

The data source is not connected. Connecting to the data source will provide useful information during configuration. wocould you like to connect to the data source?

Select YES (OK or OK ).

Go TO the connect to DB2 DATABASE screen:

User id: USER who entered the DB2 DATABASE

PASSWORD: enter the PASSWORD of the DB2 DATABASE user.

Connection mode: You can select the default share mode.

Select OK!

ODBC configuration and test completed!

4. Configure the connection server in sqlserver2000:

Enterprise Manager-security-DB2 connection server-right-click Create connection server-define the connection name; select another data source; specify the program name as ibm ole db Provider for DB2 SERVERS; the product name is optional. The data source specifies the name of the data source defined in ODBC. The provider string is in the following format: User ID = username; Password = userpasswd (or in the following format: UID = username; PWD = userpasswd). the user name and password here correspond to the user name and password in the DB2 database to be connected-Security Tab: set to use this security context, enter the database username and password of DB2. On the "server options" tab, click "OK" by default.

5. All preparations have been completed! In sqlserver Enterprise Manager-security-DB2 connection server open the newly established connection server-click the table, you can see all the table names owned by the DB2 database user in the right window, however, the table records cannot be viewed here. You need to use specific SQL statements in the SQL Server Query analyzer! When accessing a table, the format is [connection server name]... [DB2 user]. [Table name]. For more details, I will not go into details here.