官术网_书友最值得收藏!

首頁(yè) >

IBM DB2 9.7 Advanced Administration Cookbook

Managing federated databases—connecting to Oracle and MSSQL

A federated database allows client applications to see their data as a single database, even though the data itself can be located across different databases, or even databases created with another RDBMS such as Oracle or MSSQL.

Getting ready

IBM InfoSphere Federation Server must be installed. You can either choose to install a new copy of DB2 Enterprise Server Edition Version 9.7 or install Federation Server on top of an existing copy.

Since DB2 acts as a client for the remote databases, the client software for the databases you want to access must be installed on the same system as the federated server. For Oracle databases, you will need Oracle Net Client. Make sure you can do a tnsping and access your remote database with sqlplus.

[ora10g@nodedb21 ~]$ tnsping ERP10R2 TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-JUN-2011 15:34:00 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = nodedb21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ERP10R2))) OK (0 msec) [ora10g@nodedb21 ~]$ sqlplus robert@ERP10R2 SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 19 15:31:29 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning and Data Mining options SQL>

For remote access to MSSQL Server databases, you need to install Unix ODBC driver on the federated server. Please refer to the documentation for the appropriate versions and supported databases. Check for the current configuration, and allow for a time slot in case you need to change the configuration and restart the instance.

How to do it...

  1. Ensure the instance is configured for a federated server:

    Log in as the instance owner, and from the GUI, or at the command line, get the database manager configuration, and look for Federated; it should be set to yes, otherwise you need to change the configuration and restart the instance.

    db2 get dbm cfg

    Look for the following parameters:

    (FEDERATED) = NO
(MAX_COORDAGENTS) = AUTOMATIC(200)
(MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

    Change the parameters and restart the instance:

    db2 update dbm cfg using federated yes max_coordagents 200 max_connections 200;

    If we configure the instance as a federated server, the concentrator has to be off. This is why we set MAX_COORDAGENTS = MAX_CONNECTIONS.

  2. Set environment variables:

    You need to set environment variables in the db2dj.ini file, depending on the data sources you need. If it does not exist already, create db2dj.ini in the following path:

    • On Windows, you would find/create this file in : 
       %DB2PATH%\cfg\db2dj.ini
    • In Linux: 
       /home/db2instf/sqllib/cfg/db2dj.ini Add the following values depending on the data source you want to configure:
    • Oracle: 
      ORACLE_HOME=/opt/oracle/product/10.2.0
    • SQL Server: 
       DJX_ODBC_LIBRARY_PATH=/

      Now, set DB2's registry so it knows where db2gj.ini is:

       db2set DB2_DJ_INI=/home/db2instf/sqllib/cfg/db2dj.ini
  3. Verify that library files have been linked correctly:

    Verify that library files have been linked correctly, and then proceed to step 5. Look for a message file, depending on the data source, in our example in /opt/IBM/DB2/V9.7/lib64:

    • For Oracle, look for dxjlinkOracle.out and library file libdb2net8.so
    • For Microsoft SQL Server, look for djxlinkMssql.out and library file libdb2mssql3.so

      If these files show errors or do not exist, relink manually, as seen in step 4.

  4. Relink the library files:

    If you installed the client software after you installed Federation Server, or if you upgraded your client, you must manually relink the library files to the data source client software.

    Stop the instance:

    db2stop force
    • For Oracle execute script:
    export ORACLE_HOME=/opt/oracle/product/10.2.0 cd /opt/IBM/DB2/v9.7.4P/bin ./djxlinkOracle
    • For MSSQL execute script:
    export DJX_ODBC_LIBRARY_PATH=/opt/IBM/WSII/odbc/lib ./djxlinkMssql
    • In a multipartitioned instance, repeat this step for each instance.

      Now, log in as root, and update each instance:

    cd /opt/IBM/DB2/v9.7.4P/instance ./db2iupdt db2instf
  5. Create a database:

    Before you configure the federated server to access data sources, you must create a database. It can be a single or multipartitioned database. The current partition will become the catalog partition.

  6. Create a wrapper:

    Choose or create a database to be used as the federated database. Then, you need to create a wrapper for each of the data sources you want. These wrappers encompass libraries used by the federated server to connect, as a client, to the target data sources. On the Linux command line, as the instance owner, issue the command:

    • For Oracle:
    db2 create wrapper net8 library 'libdb2net8.so'
    • For MSSQL:
    db2 create wrapper mssql3 library 'libdb2mssql3.so'
  7. Locate the node name in the Oracle tnsnames.ora file: 
     [ora10g@nodedb21 ~]$ cd $ORACLE_HOME/network/admin [ora10g@nodedb21 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ERP10R2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ERP10R2)
)
)
  8. Create a server definition:

    Use the Oracle Service Name or Oracle SID as NODE name. You will need this so the connection can work.

    CREATE SERVER ERPFED TYPE oracle VERSION 10.2.0 WRAPPER net8 OPTIONS (NODE 'ERP10R2')
  9. Create user mappings:

    The user ID at the Oracle data source must be created by using the Oracle create user command:

    CREATE USER MAPPING FOR robert SERVER ERPFED OPTIONS (REMOTE_AUTHID 'robert', REMOTE_PASSWORD 'mypasswd') ;
    • Test the connection (make sure user 'robert' has access to this table on the Oracle database):
    SET PASSTHRU ERPFED SELECT * FROM V$INSTANCE SET PASSTHRU RESET
  10. Update data source stats:

    You need to have up-to-date statistics on the remote Oracle database.

  11. Create nicknames:

    Statistics on the remote Oracle database are added to the system catalog in the federated database.

CREATE NICKNAME N_GL FOR ERPFED."ERP"."GENERAL_LEDGER" ;

How it works...

A federated system is a DB2 instance that acts as a server and contains a database that is identified as the federated database. A client application accesses the server as if it were a DB2 database. DB2 accesses these data sources on behalf of these client applications.

See also

There is a lot of material for this matter that cannot be covered in detail, so please refer to the IBM Documentation, Configuration Guide for Federated Data Sources (SC19-1034-02) if you happen to have any difficulty with this setup.

主站蜘蛛池模板： 平果县| 岢岚县| 富阳市| 都江堰市| 九龙县| 盐亭县| 奉贤区| 城步| 阆中市| 安阳县| 寿宁县| 托克托县| 扎兰屯市| 平谷区| 丁青县| 诸城市| 卢龙县| 寿光市| 墨脱县| 浮山县| 偃师市| 离岛区| 得荣县| 仙居县| 惠来县| 五寨县| 德兴市| 小金县| 大洼县| 湘潭市| 措勤县| 社会| 辉南县| 平顶山市| 屯门区| 武川县| 报价| 博兴县| 汕头市| 织金县| 彭州市|