Use SQL Server to connect to the Server to access DB2 Server and sqldb2

Use SQL Server to connect to the Server to access DB2 Server and sqldb2

First, install Microsoft ole db Provider for DB2

: Http://download.microsoft.com/download/ B/B /2/BB22098A-C071-415F-9269-2EB26CEFB562/DB2OLEDB_CN.exe

After installation, one more menu item is displayed:

Next, run the "data access tool" for detailed configuration.

Assume that the DB2 configuration information is as follows:

Accessible Database address: jdbc: db2: // 10.18.50.149: 50000/BPMDB
Username: db2admin
Password: db2admin

Description of the character set in step 5:

You can use the following command to learn about the host CCSID.

Db2 connect to BPMDB user db2admin using db2admin
Db2 get db cfg for BPMDB

Query results:

C: \ IBM \ BPM \ v8.5 \ db2 \ BIN> db2 connect to BPMDB user db2admin using db2admin

Database connection information

Database Server = DB2/NT64 10.1.1
SQL authorization id = DB2ADMIN
Local Database alias = BPMDB


C: \ IBM \ BPM \ v8.5 \ db2 \ BIN> db2 get db cfg for BPMDB

Database Configuration of database BPMDB

Database Configuration release level = 0x0f00
Database Release level = 0x0f00

Database region = US
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database sorting order = IDENTITY
Standby sorting order (ALT_COLLATE) =
Digit compatibility = OFF

The PC code page, which can be learned through the following operations.

 

:

The string in step 2 is required.

Provider = DB2OLEDB; User ID = db2admin; Password = db2admin; Initial Catalog = BPMDB; Network Transport Library = TCP; Host CCSID = 1208; PC Code Page = 936; Network Address = 10.18.50.149; network Port = 50000; Package Collection = BPMDB; Process Binary as Character = False; Units of Work = RUW; DBMS Platform = DB2/MVS; Defer Prepare = False; rowset Cache Size = 0; Persist Security Info = True; Connection Pooling = False; Derive Parameters = False;

 

It's time to configure the linked server.

 

Added successfully. The linked server HEATHERNET is displayed.

 

Test link server to query data

The query is normal and Chinese fields are displayed.

 

Tip: if the character set in step 1 is improperly configured, the Chinese field name cannot be displayed normally and the system will always prompt that the field does not exist.

 

Message 7339, level 16, state 1, 1st rows
Ole db provider 'db2oledb' for linked server 'heathernet' returned invalid data for column' [HEATHERNET]. [BPMDB]. [DB2ADMIN]. [V_USER_MAPPING]. POSITION_NAME '.

The following are the correct query results.

So far, the link server of DB2 has been set up.