Configuring InfoSphere Federation Server to access Oracle data source
InfoSphere Federation Server Configuring InfoSphere Federation Server to access Oracle data source
This presentation describes how to configure InfoSphere™ Federation Server Oracle wrapper on Windows®.
Objectives (1 of 2)
Objectives (1 of 2) Overview of configuring access to Oracle data sources on Windows Setting Oracle Client environment variable Registering Oracle wrapper Registering server definitions for Oracle data source
This module contains screen captures of what you can expect to see when following these instructions. The objective of this module is to provide an overview of configuring access to Oracle data sources on the Windows platform. It includes how to set the Oracle Client environment variable, how to register the Oracle wrapper and how to register server definitions for Oracle data source.
Objectives (2 of 2)
Objectives (2 of 2) Creating user mappings for Oracle data source Registering nicknames for Oracle tables and views Configure Oracle wrapper using DB2® Command Line Processor Reference
This module will also explain how to create user mappings for the Oracle data source, how to register nicknames for Oracle tables and views and how to configure the Oracle wrapper using the DB2 Command Line Processor. There is also a slide at the end of this module that provides reference material and links.
Overview of configuring access to Oracle data sources on Windows
Overview of configuring access to Oracle data sources on Windows Configuration of IFS Oracle wrapper Configuring Oracle client (prerequisite) Configuring IFS Oracle wrapper Note: IFS requires a working connection from Oracle client to Oracle server before configuring the InfoSphere Federation Server Oracle wrapper.
The InfoSphere Federation Server product is also referred to as IFS. IFS Oracle wrapper configuration involves two parts: One part is to configure the Oracle client where the federated database resides to connect to Oracle server. IFS requires a working connection from Oracle client to Oracle server. This task is handled by an Oracle DBA and Oracle support if needed. The other part involves configuring the IFS Oracle wrapper which includes creating the wrapper, server, user mapping and nickname. Use Control Center and command line for this part.
Setting Oracle Client environment variable
Setting Oracle Client environment variable Set ORACLE_HOME variable in db2dj.ini file located under
\sqllib\cfg ORACLE_HOME=\oracleClientDirectory\ Sample db2dj.ini content ORACLE_HOME=C:\oracle\product\10.2.0\client_1
If you install Oracle client before you install IFS Oracle wrapper, the required environment variable is set when you install IFS Oracle wrapper in db2dj.ini file. If you install Oracle client after you install IFS Oracle wrapper, you need to manually set the ORACLE_HOME variable to point to the Oracle client directory in the db2dj.ini file, located under \sqllib\cfg directory. If db2dj.ini does not exist, you can create one using any text editor.
Registering Oracle wrapper (1 of 2)
Registering Oracle wrapper (1 of 2)
Open Control Center and expand your federated database view. Locate ‘Federated Database Objects’ and right click. This will give you the option to create the wrapper.
Registering Oracle wrapper (2 of 2)
Registering Oracle wrapper (2 of 2)
A new window opens and allows you to select the wrapper to create and specify the wrapper name. The wrapper name will default to ‘net8’ when you select ‘Oracle using OCI 8’. You can leave it as is or change the wrapper name to whatever you choose. The example on this slide displays the wrapper name using the default. Next, click ‘OK’ to create the Oracle wrapper.
Registering server definitions for an Oracle data source (1 of 4)
Registering server definitions for an Oracle data source (1 of 4)
Right click Server Definitions and select ‘Create …’
Registering server definitions for an Oracle data source (2 of 4)
Registering server definitions for an Oracle data source (2 of 4) Specify remote database version
A new window opens and allows you to create your server definitions. You can choose to manually input all information or use the ‘Discover…’ feature which will detect an available Oracle client connection that you can select from. You need to select the correct version for the remote Oracle database.
Registering server definitions for an Oracle data source (3 of 4)
Registering server definitions for an Oracle data source (3 of 4) Change the server properties as needed
Highlight the Oracle name and select 'Properties' to change server definitions and settings.
Registering server definitions for an Oracle data source (4 of 4)
Registering server definitions for an Oracle data source (4 of 4) 'ShowSQL' allows you to see or save ‘CREATE SERVER’ command
You can save the SQL for reference by selecting the ‘Save…’ button.
Creating user mappings for an Oracle data source (1 of 3)
Creating user mappings for an Oracle data source (1 of 3) Create user mappings to map IFS user to remote Oracle database user
Create user mappings by right clicking ‘User Mappings’ and selecting ‘Create…’ The Create User Mappings window will open and you will choose one or more federated users to map to the remote Oracle user.
Creating user mappings for an Oracle data source (2 of 3)
Creating user mappings for an Oracle data source (2 of 3)
Select the user to be mapped to the Oracle database user.
Creating user mappings for an Oracle data source (3 of 3)
Creating user mappings for an Oracle data source (3 of 3) Enter Oracle user ID and password
In the ‘Settings’ tab, specify the remote Oracle user ID and password, then select ‘OK’ to complete.
Registering nicknames for Oracle tables or views (1 of 4)
Registering nicknames for Oracle tables or views (1 of 4)
You can create nicknames on the federated database for any table or view that you have on a remote Oracle database. To do this, right click ‘Nicknames’ under the Server definitions you defined and then select ‘Create…’.
Registering nicknames for Oracle tables or views (2 of 4)
Registering nicknames for Oracle tables or views (2 of 4)
Use the ‘Discover’ feature to test the connection by clicking on ‘Count’. This will make the connection and return a count for the number of objects in the remote Oracle database. This is equivalent to the connection using ‘PASSTHRU’ mode in the DB2 command line processor.
Registering nicknames for Oracle tables or views (3 of 4)
Registering nicknames for Oracle tables or views (3 of 4)
You can filter tables for a specific schema or Oracle table name. For example, if you specify the Oracle schema as ‘METALICA’, you will receive all objects with that schema that you can potentially create nicknames for.
Registering nicknames for Oracle tables or views (4 of 4)
Registering nicknames for Oracle tables or views (4 of 4)
Once you select ‘OK’ and create the nicknames successfully, you can view them under your nickname folder in the Control Center. You can access Oracle tables through the federated database nicknames.
Configure Oracle wrapper using DB2 command line processor
Configure Oracle wrapper using DB2 command line processor Use these commands by replacing the value in the bracket(<>) with your own --------------------------------------------------------------------------------------- CREATE WRAPPER NET8 LIBRARY 'db2net8.dll'; CREATE SERVER TYPE ORACLE VERSION ‘xxx' WRAPPER NET8 OPTIONS (ADD NODE '< xxxxx >'); CREATE USER MAPPING FOR SERVER OPTIONS ( ADD REMOTE_AUTHID '', ADD REMOTE_PASSWORD '') ; --test connection in Passthru before creating nickname(*)— CREATE NICKNAME . FOR ..; --------------------------------------------------------------------------------------- Note: The NODE name in the CREATE SERVER statement is the value you named your Oracle Net service name
This slide and the next slide displays commands used to configure the Oracle wrapper using the DB2 Command Line Processor.
Configure Oracle wrapper using DB2 command line processor
Configure Oracle wrapper using DB2 command line processor Command used in the DB2 command line processor SET PASSTHRU SELECT COUNT(*) FROM SET PASSTHRU RESET
This slide also displays commands used to configure the Oracle wrapper using the DB2 Command Line Processor. Before creating a nickname, test your connection to the Oracle server in ‘PASSTHRU’ mode to ensure that your connection is working. You can do this by running the commands displayed on this slide in the DB2 Command Line Processor. To configure the Oracle wrapper in the DB2 Command Line Processor, you can either run the commands one by one or you can save and run the commands in a script. You have now completed the IFS Oracle wrapper configuration.
References
References To open a PMR http://www-01.ibm.com/support/docview.wss?uid=swg21321031 DCF information center http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.container.doc/doc/t0024244.html Versions of Oracle supported by InfoSphere Federation Server 9.7: http://www-01.ibm.com/support/docview.wss?uid=swg27015299 9.5: http://www-01.ibm.com/support/docview.wss?rs=3551&uid=swg27011206 9.1: http://www-01.ibm.com/support/docview.wss?rs=3551&uid=swg27008401
If you run into any issues that you cannot resolve during the IFS Oracle wrapper configuration, consult with IFS product support by opening a PMR and attach the information listed at the web address displayed on this slide. This slide also displays links documenting all versions of Oracle that is supported by the InfoSphere Federation Server for V9.7, V9.5 and V9.1.
Feedback
Feedback Your feedback is valuable You can help improve the quality of IBM Education Assistant content to better meet your needs by providing feedback. Did you find this module useful? Did it help you solve a problem or answer a question? Do you have suggestions for improvements? Click to send email feedback: mailto:iea@us.ibm.com?subject=Feedback_about_InfoSphereConfig.ppt This module is also available in PDF format at: ../InfoSphereConfig.pdf
You can help improve the quality of IBM Education Assistant content by providing feedback.
Trademarks