Use IBM Data Studio to create and manage DB2 federated database objects

Use IBM Data Studio to create and manage DB2 federated database objects

In the latest version 4.1, IBM Data Studio (DS) provides more comprehensive support for federated databases than earlier DS3.x versions. This article describes how to create and manage federated database objects in DS.

Overview

In today's large enterprises and institutions, massive and complex data, as well as a large number of organizational units, business development and competition and cooperation among enterprises, this makes it almost impossible for these enterprises and organizations to use different database management systems to store and manage their important data. The Federal functions of DB2 and other mainstream databases provided by IBM provide powerful support for the integration of such information between enterprises and institutions. At the same time, since DB2V10, IBM DataStudio, an integrated tool for database management, development, SQL optimization and integration released together with DB2, is in its 4.1 version, it also provides comprehensive support for the creation and management of federated database objects. DS4.1 supports the creation and management of federated database objects for the following eight remote data sources: SQL Server, Oracle, Teradata, Informix, JDBC, ODBC, DB2, SYBASE. In earlier DS3.x versions, only remote DB2 and Oracle data sources are supported. In addition, DS4.1 adds the function of automatic discovery of tables on the remote data source when creating Nickname. This article describes how to use DS4.1 to create and manage federated database objects using instances.

Introduction to the DB2 federated database environment

Before using DataStudio to create a federated database object for different data sources, assume that you have configured the DB2 federated server. The configuration of the DB2 federated server is at the operating system level, while DataStudio is a tool for operating the database. Therefore, you need to configure the support for various data sources on your DB2 federated server in advance, generally, the DB2 federal server supports the following configurations for various data sources:

1. Set the instance parameter federated on the DB2 federated server to YES.

2. install and configure the clients for the supported data sources on the DB2 federal server. DB2 and informix do not require additional installation. The DB2 federal server provides support for these two data sources.

3. wrapper for various data sources supported by the DB2 federal server is installed. Wrapper provides the function for the DB2 federal server to interact with other data sources. In the Installation Wizard, you can configure the association between Wrapper and the corresponding data source library file. These configurations will be written to the db2dj. ini file of the DB2 federal server.

4. if you are the Wrapper of the DB2 federated server installed first, and then the clients of various data sources installed later, you can also manually edit db2dj. ini file to add the association between each Wrapper and the corresponding data source. Starting from DB2V10.1, a graphical Wrapper Configuration tool rwcfg is provided. You can also use it to configure Wrapper. The configuration results will be saved in the db2dj. ini file.

When the configuration of the federated server is complete, you can connect to the DB2 federated server and operate the tables and stored procedures on other data sources. Next we will introduce how to use DS4.1 to manage federated database objects.

Create a DB2 federated database object

This section uses DB2 and Oracle Federation as an example to illustrate how to use DS to create Wrapper, Remote Server, User Mapping, and Nickname.

1. Create a database connection for the DB2 federated database.

Start DS, click the "Down Arrow" icon in the Administration Explorer (AE) view, and select New connection to a database, such as 1:
Note: If the AE view is not opened in DS, click Windows> ShowView> Other In the DS menu bar. In the displayed wizard, expand the Data Management folder and select Administration Explorer, click OK to open this view.

Figure 1: Select create database connection

The Create Database Connection Wizard will be opened. In the wizard, select the Database Manager type. In this example, It is DB2 for Linux, Unix, and Windows, enter the database connection information in the Wizard, for example, 2:

Figure 2: Create a database connection wizard

Click Test Connection in Figure 2 to confirm the Connection is successful. Then, click Finish to create a database Connection in the Administration Explorer view.

2. Create a change plan

Create a Change Plan in DS, and then add the operations for creating Wrapper, Remote Server, and User Mapping to the Change Plan. In the AE view of DS, expand the federated database node under the newly created database connection, right-click Change Plans, and select Create Change Plan, as shown in Figure 3:

Figure 3. Select create a change plan

Next, a dialog box is displayed, prompting you to enter the name of the Change Plan. The name is testplan1, for example, 4.

Figure 4. specify the name of the Change Plan

Click OK. The newly created change plan is displayed in the Object List Editor (OLE) of DS, as shown in Figure 5.

Figure 5. Status of the newly created change plan

Figure 5. Status of the newly created change plan

As shown in figure 5, the change plan is in pending state and is waiting for execution. However, it contains 0 User changes, and some operations need to be added before execution. Next, add the operation to create a federated database object.

3. Add the create Wrapper, RemoteServer, and UserMapping operations to the Change Plan.

From the AE view of DS, expand the Federated Database Objects folder, right-click the folder Wrappers, and select Create Wrapper. You will see that all Wrapper of the current Federated Database is listed in the DS OLE, this includes the Wrapper you are creating, and the Properties View of this Wrapper will be opened. In the property view, enter the Wrapper name TESTORAWRA1 In the name text box and select ORACLE from the Data Source drop-down list. The Data Source drop-down list contains eight Data Source options, select libdb2net8 from the Library name drop-down list. a (Aix). The Library name drop-down list contains three platform file options. In this example, the DB2 federal database is installed on the Aix machine. For details, refer to 6.

Figure 6. Create Wrapper for the ORACLE Data Source

Click to view the chart

Disable [x]

Figure 6. Create Wrapper for the ORACLE Data Source

Next, Create a Remote Server based on the preceding Wrapper. In the AE view of DS, right-click the folder Remote Servers and choose Create RemoteServer. In the displayed dialog box, select the created Wrapper. for example, 7.

Figure 7. Select Wrapper when creating a Remote Server

Click "OK" in. All the Remote servers in the current federated database are listed in the DS OLE, including the Remote Server you are creating, the attribute view of the Remote Server is opened. Select the General page in the property view, specify the Remote Server name as TESTORASVR1 In the name text box, select ORACLE from the TYPE drop-down list, and select the ORACLE Version from the Version drop-down list, in this example, the ORACLE version is 11. In the Wrapper drop-down list, select the created Wrapper TESTORAWRA1 and enter the username and password for connecting to ORACLE, as shown in figure 8.

Figure 8. Create a Remote Server attribute view for the ORACLE Data Source

In the attribute view shown in figure 8, select the Options page, select the NODE attribute, and specify a value for the NODE. In this example, the NODE name configured in ORACLE is ora11gcsdl1, other options are optional, such as 9.

Figure 9. Options for creating the Remoete Server for the ORACLE Data Source

Then, create User Mapping for the created Remote Server. In the AE view of DS, right-click the User Mappings folder and select Create User Mapping. In the displayed dialog box, select the newly created Remot Server, as shown in figure 10.

Figure 10. Select Remote Server for User Mapping of the ORACLE Data Source

Click "OK" in the left-side Navigation Pane. All User mappings in the current federated database are listed in the DS OLE, including the User Mapping you are about to create. In this User Mapping attribute view, select the General page, enter the DB2 federal database user name in the Local User ID text box, and select the created Remote Server from the Local Server name drop-down list, then, in the Remote User ID and Remote password text boxes, enter the Remote ORACLE User name and password, for example, 11.

Figure 11. Create a User Mapping

Click to view the chart

Disable [x]

Figure 11. Create a User Mapping

At this point, we can see that on the toolbar of the Change Plan above OLE, the number on the right of the Change Plan testplan1 has changed from the initial 0 to 3, which indicates that currently testplan1 contains three operations, create Wrapper, RemoteServer, and UserMapping respectively. Click Review and Deploy Changes on the right of the Change Plan toolbar. The Review and Deploy dialog box is displayed, and all operation DDL operations are generated, as shown in Figure 12.

Figure 12. Preview and deploy the current change plan

Figure 12. Preview and deploy the current change plan

Click the run button in. The DDL running result is displayed in the SQL Results view of DS, as shown in Figure 13.

Figure 13. Results of running the change plan

Figure 13. Results of running the change plan

From the running results, we can see that all SQL statements are successfully executed, and the specified Wraper, RemoteServer, and UserMapping are successfully created.

4. Create a table on a remote ORACLE data source

In this example, we create a table on the remote ORACLE Server, and then create Nickname for the table in DS.

1) First, log on to the DB2 federated database machine and connect to the test database.

Listing 1. Connecting to the DB2 federated database

 db2 connect to testdb  Database Connection Information  Database server        = DB2/AIX64 9.7.7  SQL authorization ID   = IIFVT66  Local database alias   = TESTDB

2) open a session to connect to the ORACLE server.

Listing 2. Open a session to connect to the ORACLE Data Source

 db2 set passthru TESTORASVR1  DB20000I  The SQL command completed successfully.

3) create a table on the remote ORACLE Server.

Listing 3. Creating a remote table

 db2 "create table J15USER1.TESTTAB01(C1 integer, C2 varchar(12))" DB20000I  The SQL command completed successfully.

5. Create nick name for the table on the remote ORACLE database

In the AE view of DS, right-click the folder Nicknames and select Create Nickname. In the displayed dialog box, select a Schema, as shown in figure 14.

Figure 14. Select a Schema for Nickname

Select a schema and click "OK". All the nicknames in the current federated database are listed in the DS OLE, including the Nickname you are about to create. In the attribute view of this Nickname, select the General page, specify a Name for Nickname in the Name text box, and select the newly created TESTORASVR1 from the Server drop-down list, then, in the Remote Schema text box, enter the schema on the Remote ORACLE, and in the Remote table text box, enter the table TESTTAB01 we just created. Please refer to 15.

Figure 15. Create a nick name for the table on the remote ORACLE Server

Note that if no change plan is created before database object operations, DS creates a change plan by default for user operations. For example, a Change plan named Default Change Plan2013-04-11 13-44-19 was created by Default, and this Change plan contains an action, that is, the operation we created for Nickname, click the Review and Deploy Changplan button on the right of the Change Plan menu bar. The displayed preview and deployment dialog box contains the DDL statement for creating a Nickname, for example, 16.

Figure 16. Preview and deploy the DDL statement for creating Nickname.

Click the "run" button in the middle. From the SQL Result view, we can see that the Nickname is successfully created. The newly created Nickname is also displayed in OLE, for example, 17.

Figure 17. The nick name is successfully created for the table on the remote ORACLE database.

The operations for creating a federated database object for other data sources are similar to the preceding steps. They are all performed in the AE view, OLE view, and attribute view of DS, and then the corresponding change plan is deployed. At this point, the reader may find that when creating a Nickname, You need to manually enter the table on the remote data source. If you want to create multiple nicknames, you need to create them one by one, which is really inconvenient. To facilitate user operations, the auto-discovery feature of nick name is added to DS4.1. This feature allows users to create nick names for multiple tables on Multiple Remote Data sources at the same time. The following is a detailed introduction.

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page