Implementation of SQL Server to DB2 connection server

The interconnection of different database platform is commonly called the heterogeneous service of the database, now each big database can realize such heterogeneous interconnection, but the specific implementation technology of each vendor is not the same, such as: In SQL Server called linked server, through ODBC and other database interconnection.

The test environment for this article is:

Operating system: WINDOWS2000 SERVER (Traditional system)

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

Specific steps to achieve:

1. require PC to install DB2 client software and sqlserver2000 software.

2. Configure Windows ODBC Data source:

Start Menu-"assembly-" System management Tool-"data source (ODBC)-" Enter configuration User DSN or System DSN can: Add-"Select IBM DB2 ODBC driver-" Custom data source name (optional: db2test)-"Database name (required!) )-"OK complete."

3. Select the name of the data source that you just configured, then select Configure and jump out of DB2 MESSAGES:

The data source is not connected.　Connecting to the data source would provide useful information during configuration. Would to connect to the data source?

Select Yes (OK or confirm)

Enter connect to DB2 database screen:

User ID: Users entering DB2 database

PASSWORD: Enter the user's password for DB2 database

CONNECTION mode: You can choose the default share mode

Select OK (confirm)!

Configure and test ODBC complete!

4. Configure the connection server in sqlserver2000:

Enterprise Manager-"Security-" Connection server-"right" New connection server-"Define the connection name, select a different data source, and specify the program name: IBM OLE DB Provider for DB2 SERVERS; Product name is not filled; Data source Specifies the name of the data source that is defined in ODBC just now; the provider string is filled in the following format: User id=username; PASSWORD=USERPASSWD (or in the following format: Uid=username; PWD=USERPASSWD), where the username and password correspond to the user name and password in the DB2 database to which you want to connect-security tab: Set in this security context, and enter the DB2 username and password-the Server Option tab page can be determined by default-".

5. All preparations are complete! In SQL Server Enterprise Manager-"security-" connect the server to open the newly built connection server-"Click on the table, you can see in the right window of the DB2 database users have all the table names, but here is not to view the records of the table, This needs to be implemented in the Sqserver Query Analyzer with specific SQL! When accessing a table, use the format: [Connect server name]. [DB2 user]. [Table name]. More detailed and specific use here no longer repeat.