DB2 Federation (DB2 uses federal functions to link to DB2)

1. Create Federation using a visual window

1. Open the control center, open the database, right-click the "Create package" option in its "database objects" file, and open the "Create package" window. Select DB2 as the data source, add a package name (the name must be unique ).

Switch to the settings tab, select DB2_FENCED, and use its default value "N". The "DB2_FENCED" attribute is used to specify whether the package runs in a protected or trusted manner, the default value "N" indicates that the package runs in trusted mode.

Click "show SQL" to view the SQL statement used to create the package:

Create wrapper DRDA2 LIBRARY 'db2drda. dll 'OPTIONS (ADD DB2_FENCED 'n ');

Note: "enable database instance for the specified operation" may appear when creating the package. Cause Code: "1 ". You can solve this problem by setting FEDERATED to yes. For details, refer to the first point of attention in section 3.

2. Select the created package DRDA2, right-click "server definition", and click "create server definition".

Click discover to view the Database List, select the database to be federated, and select the type and version,

Click properties to set the server definition and enter the user ID and password:

Click "show SQL" to view the SQL statements for creating a server:

Create server appdb type DB2/udb version '9. 7' WRAPPER DRDA2 AUTHID "db2admin" PASSWORD "*****" OPTIONS (add dbname 'appdb ');

3. create user ing: Click User ing, right-click "CREATE", and select the local user ID

Switch to the settings tab and set the user ID and password of the remote database:

View SQL statements:

Create user mapping for administrator server filedb options (ADD REMOTE_AUTHID 'db2admin', ADD REMOTE_PASSWORD '*****');

4. Create a nickname, click the nickname, and right-click "CREATE" to create a nickname dialog box:

Click "add" to the "add nickname" dialog box, enter the nickname (nickname cannot be duplicate), and add the remote mode and remote Table Name:

You can also find remote tables in the mode or on the surface.

SQL statement for nickname creation:

Create nickname DB2ADMIN. usertbl for appdb. DB2ADMIN. USERTBL;

2. Create Federation using SQL

The preceding SQL statements are created in the window:

1. Create an installation package:

Create server appdb type DB2/udb version '9. 7' WRAPPER DRDA2 AUTHID "db2admin" PASSWORD "*****" OPTIONS (add dbname 'appdb ');

2. Create a server Definition

Create server appdb type DB2/udb version '9. 7' WRAPPER DRDA2 AUTHID "db2admin" PASSWORD "*****" OPTIONS (add dbname 'appdb ');

3. create user ing

Create user mapping for administrator server filedb options (ADD REMOTE_AUTHID 'db2admin', ADD REMOTE_PASSWORD '*****');

4. Create a nickname

Create nickname DB2ADMIN. usertbl for appdb. DB2ADMIN. USERTBL;

Iii. Notes:

1. The system prompts to enable the database instance for the specified operation. Reason: Set FEDERATED to yes for the configuration item. Solution: update dbm cfg using FEDERATED yes. Stop the server and then start the server.

2. DB2 Federation permissions: You can create a database federation only with the permissions of the DB2 administrator.

3. User ing: Local Users and remote permissions.