Configuring iWay Data Profiler With Oracle 11g

How to:

This section describes how to configure iWay Data Profiler on a Windows system with the following configuration:


Top of page

x
Procedure: How to Create Base Tables

To create base tables in Oracle 11g using Oracle Enterprise Manager:

  1. From the Windows Start menu, select All Programs, Oracle - OraClient11g_home, Application Development, then click SQLPlus Worksheet.
  2. In the Oracle Enterprise Manager dialog box, enter the user name, password, and service name. Select Normal from the Connect as drop-down list and click OK to connect, as shown in the following image.

    The workspace for the connected server is displayed, as shown in the following image.

  3. Click File, then select Open and browse to the Oracle_IDP.sql script file, which is located in following directory:
    <dphome>\idpweb\sql\Oracle_IDP.sql

    where:

    <dphome>

    Is the path on the file system where iWay Data Profiler is installed.

  4. Click Execute.

    The script is executed and the tables are generated, as shown in the following image.


Top of page

x
Procedure: How to Configure Java Services
  1. From the Windows Start menu, select All Programs, Information Builders, WebFOCUS 80 Server, then click Web Console.

    The Web Console opens.

    Note: The WebFOCUS Reporting Server must be running in order to open the Web Console.

  2. From the Workspace, navigate to Configuration/Monitor, expand Java Services, double-click DEFAULT, and ensure that it is active.
  3. In the left pane, right-click Java Services, then right-click DEFAULT and select Properties from the context menu.

    The Java Services Configuration pane opens.

  4. Click the Class path tab.
  5. For Oracle 11g, set IBI_CLASSPATH to:
    <dphome>\idpweb\WEB-INF\lib\ojdbc6.jar

    where:

    <dphome>

    Is the path on the file system where iWay Data Profiler is installed.

  6. Click Save and Restart Java Services.

    The Java Services status changes to active.


Top of page

x
Procedure: How to Create the JDBC Connection
  1. From the Windows Start menu, select All Programs, Information Builders, WebFOCUS 80 Server, then click Web Console.

    The Web Console opens.

    Note: The WebFOCUS Reporting Server must be running in order to open the Web Console.

  2. Click Adapters in the menu bar.
  3. In the left pane, expand Adapters, Available, SQL, Oracle, and click 11g (Unicode Optional).
  4. In the left pane, right-click 11g (Unicode Optional) and select Configure from the context menu.

    The Add Oracle 11g to Configuration pane opens, as shown in the following image.

  5. Provide the following values for the connection parameters:

    Parameter

    Description

    Connection Name

    idp

    The logical name used to identify this particular set of connection attributes. There is no default connection name. You must provide a value.

    TNS name

    idpv4

    Service (TNS) name used as a connect descriptor to an Oracle database server across the network. It must point to a valid entry in the tnsnames file.

    Connection to a local database server can be entered as <local>. Note that <local> may not have a match in the tnsnames.ora file.

    Security

    Explicit

    There are three methods by which a user can be authenticated when connecting to an Oracle database server:

    • Explicit. The user ID and password are explicitly specified for each connection and passed to Oracle at connection time, for authentication.
    • Password Passthru. The user ID and password received from the client application are passed to Oracle at connection time, for authentication.
    • Trusted. The adapter connects to Oracle as an operating system logon using the credentials of the operating system user impersonated by the server data access agent.

    User

    The user name for Oracle 11g. This is the primary authorization ID.

    Password

    The password for Oracle 11g. The password is associated with the primary authorization ID.

    Select profile

    Select a profile from the drop-down list to indicate the level of profile in which to store the CONNECTION_ATTRIBUTES command. The global profile, edasprof, is the default.

  6. After you have provided the required values for the connection parameters, click Configure, as shown in the following image.

    A message is displayed indicating that the connection is successfully added to the configuration.

  7. To test the connection, right-click the idp connection node under the Oracle folder and select Test from the context menu.

    The Oracle Adapter Test Completed Successfully pane is displayed, as shown in the following image.



x
Procedure: How to Create Synonyms for iWay Data Profiler
  1. From the Windows Start menu, select All Programs, Information Builders, WebFOCUS 80 Server, then click Web Console.

    The Web Console opens.

    Note: The WebFOCUS Reporting Server must be running in order to open the Web Console.

  2. Right-click the idp connection node and select Create Synonym from the context menu.
  3. Clear the Views check box and select the Filter by owner/schema and object name check box instead.
  4. From the Owner/schema drop-down list, select you Schema name, as shown in the following image.

  5. Click Next.
  6. Select idp from the Select Application pane by clicking the ellipsis (...) button as shown in the following image.

  7. Click OK.
  8. Select the Overwrite existing synonyms check box and the check box in front of the Default synonym name field.

    Note: Select the synonym names individually from TBL_BUSINESS_ANALYSIS to TBL_User. Do not click the Select All button, which selects all of the check boxes in the table. It is highly unadvisable to perform this operation.

  9. Click Create Synonym.

    A message indicating that the synonyms are created successfully is displayed with a list of all of the created synonyms, as shown in the following image.

  10. Click the Applications tab from the menu bar.
  11. In the left pane, under Application Directories, expand the idp folder.
  12. Right-click a table and select Sample Data from the context menu to view the sample data values for that table, as shown in the following image.

    Values for the selected table is displayed, are shown in the following image.


Top of page

x
Procedure: How to Configure Access to Oracle RAC Databases Using a System ID (SID) Value

iWay Data Profiler (iDP) supports integration with Oracle Real Application Clusters (RAC). Oracle RAC is a database clustering option that allows more than one instance to access an Oracle database.

A typical Oracle installation consists of a single Oracle instance that accesses a database on the same computer system. In contrast, RAC allows multiple instances on different computer systems (nodes in a cluster) to access the same database files simultaneously.

This section describes how to configure access to Oracle RAC databases using a System ID (SID).

Note: The configuration steps in this section should be used only if you need to change the Oracle SID value after iDP has already been installed. By default, the Oracle SID value to enable access for Oracle RAC can be specified during the initial iDP installation.

  1. Log on to the iWay Data Profiler - Server Manager console as an administrator using the following URL:
    http://hostname:port/idpweb/manager

    where:

    hostname

    Is the name of the application server system that is hosting the iDP application.

    port

    Is the port number where the application server is listening.

    idpweb

    Is the specific name of the XML file that was created for iDP, as described in Integrating iWay Data Profiler With the Web and Application Server.

    Note: The idpweb XML file can have a different name when more than one instance of iDP is installed to coexist with an earlier version of iDP.

    You can also access iWay Data Profiler - Server Manager console, through the Server Manager link/icon on the iWay Data Profiler logon page.

    After you have successfully logged into the iWay Data Profiler - Server Manager console, the following page is displayed.

  2. Select Data Profiler Settings in the left pane and then expand Database in the right pane.

    The database properties for iDP are listed, as shown in the following image.

  3. For the SID/Schema property, enter the Oracle SID that is assigned to the Oracle RAC (for example, ora1021).
  4. Expand Drill-through and Plan in the right pane.

    The drill-through and plan properties for iDP are listed, as shown in the following image.

  5. For the SID/Schema property, enter the Oracle SID that is assigned to the Oracle RAC (for example, ora1021).
  6. Click Save.
  7. Restart the Apache Tomcat web and application server.

    The url= and dqurl= values of the Oracle connection string in the dpcontext.properties file are updated with the Oracle SID value that you specified (for example, ora1021), as shown in the following image.

    The dpcontext.properties file is located in the following folder by default:

    C:\ibi\apps\idpweb\WEB-INF\classes

    You have now provided your iDP application access to a Oracle RAC using a SID value.


Top of page

x
Procedure: How to Configure Access to Oracle RAC Databases Using a Service Name Value

iWay Data Profiler (iDP) supports integration with Oracle Real Application Clusters (RAC). Oracle RAC is a database clustering option that allows more than one instance to access an Oracle database.

A typical Oracle installation consists of a single Oracle instance that accesses a database on the same computer system. In contrast, RAC allows multiple instances on different computer systems (nodes in a cluster) to access the same database files simultaneously.

To configure access to Oracle RAC databases using a Service Name (instead of an Oracle SID):

  1. Navigate to the following folder:
    C:\ibi\apps\idpweb\WEB-INF\classes
  2. Edit the dpcontext.properties file to manually alter the Oracle connection string.
  3. Instead of @host_name:port_number:SID in the Oracle connection string, specify @host_name:port_number/service_name.

    For example:

    jdbc:oracle:thin:@orac1:1521/ora102

    In this example, ora102 is the Service Name of the Oracle RAC database.

  4. Update the url= and dqurl= values of the Oracle connection string in the dpcontext.properties file, as shown in the following image.

  5. Restart the Apache Tomcat web and application server.

    You have now provided your iDP application access to a Oracle RAC using a Service Name value.


iWay Software