22
Discoverer support for non-Oracle databases and Oracle Rdb

Discoverer support for non-Oracle databases and Oracle Rdb

This chapter contains information about using Discoverer with non-Oracle databases and Oracle Rdb, and contains the following topics:

• "What are Heterogeneous Services?"

• "About using generic connectivity to connect to non-Oracle databases"

• "About using the Transparent Gateway to connect to non-Oracle databases"

• "About Discoverer support for Oracle Rdb"

What are Heterogeneous Services?

Heterogeneous Services are the common architecture and administration mechanisms provided with the Oracle database to enable you to connect to non-Oracle databases.

You connect to non-Oracle databases using Heterogeneous Services in two ways:

• using generic connectivity (for more information, see "About using generic connectivity to connect to non-Oracle databases")

• using the Transparent Gateway (for more information, see "About using the Transparent Gateway to connect to non-Oracle databases")

For more information about Heterogeneous Services, see the Oracle9i Heterogeneous Connectivity Administrator's Guide.

About using generic connectivity to connect to non-Oracle databases

Generic connectivity is one of the mechanisms supported by the Oracle database Heterogeneous Services feature for accessing non-Oracle databases.

Discoverer users can use generic connectivity to access ODBC or OLE DB (object linking and embedding database) databases.

The non-Oracle database must comply sufficiently with the ODBC standard (for more information about ODBC and OLE DB connectivity requirements, see the Oracle9i Heterogeneous Connectivity Administrator's Guide.

This section contains the following topics:

• "What is the difference between using generic connectivity and ODBC drivers to connect to non-Oracle databases?"

• "What are the advantages of using generic connectivity?"

• "How to set up generic connectivity for Discoverer using Enterprise Manager"

• "How to migrate an EUL from a non-Oracle database (accessed using native ODBC drivers) to an Oracle database (to support generic connectivity)"

What is the difference between using generic connectivity and ODBC drivers to connect to non-Oracle databases?

In previous versions of Discoverer, users could connect to non-Oracle databases using ODBC drivers. The major difference between using ODBC drivers to connect to a non-Oracle database and using the Oracle database generic connectivity feature is the location of the EUL, as follows:

• with ODBC drivers, the EUL is stored in the non-Oracle database

• with generic connectivity, the EUL is stored in the Oracle database

  Figure 22-1 EUL location using ODBC drivers versus using generic connectivity

  
  
  Text description of the illustration hdsmigr.gif

The above diagram illustrates the following:

• when Discoverer connects to a non-Oracle database using ODBC drivers, both the EUL and the data reside on the non-Oracle database

• when Discoverer connects to a non-Oracle database using generic connectivity, the data continues to reside on the non-Oracle database but the EUL must be located on the Oracle database

Discoverer no longer supports the use of native ODBC drivers to connect to non-Oracle databases. You must now use generic connectivity to retrieve data from the non-Oracle database. In other words, the EUL must be stored in an Oracle database. To find out how to move an EUL from a non-Oracle database to an Oracle database, see "How to migrate an EUL from a non-Oracle database (accessed using native ODBC drivers) to an Oracle database (to support generic connectivity)".

What are the advantages of using generic connectivity?

Using Discoverer with the Oracle database's generic connectivity feature rather than native ODBC drivers to connect to a non-Oracle database has the following advantages:

• enables connections to a greater number of ODBC databases

  Generic connectivity provides access to any ODBC database that is compliant with the ODBC standard. Compliance varies with both databases and ODBC drivers.

• enables Discoverer end users to query data from multiple different databases simultaneously

  For example, you can create a single business area with folders based on tables held in Sybase, DB2 and Oracle databases. An end user query can return data joined across multiple databases.

• enables query prediction with ODBC data

  Discoverer's query prediction uses query statistics that are generated when end users run queries and which are saved in the EUL. Because the EUL is in the Oracle database, Discoverer is able to carry out query prediction for the ODBC data on the Oracle database.

• enables batch support for scheduling workbooks

  Discoverer uses the batch scheduler in the Oracle database. Because the EUL is in the Oracle database, Discoverer is able to schedule workbooks with ODBC data.

• enables you to apply Oracle analytical functions (and other Oracle functions) to data provided from an ODBC database

  Because the data is brought into the Oracle database you can apply the full set of Oracle functions to the data, instead of being restricted to the functions supported by the non-Oracle database.

How to set up generic connectivity for Discoverer using Enterprise Manager

Before you can use generic connectivity, you must configure the Oracle database to support generic connectivity.

Note: You should work with your database administrator to set up generic connectivity for Discoverer. However, if you want to use the Enterprise Manager console to set up generic connectivity for Discoverer, you can use the following example (using Enterprise Manager version 2.2).

To set up generic connectivity for Discoverer using Enterprise Manager:

1. To set the Global Names parameter to FALSE, from the Windows Start menu, choose Programs | Oracle Home | Enterprise Manager Console to display the Enterprise Manager Console Login dialog.

   Note: To set the Global Names parameter for Oracle 8.1.7 (or later) Enterprise Edition databases, you change the global_names parameter to false in the init.ora file as follows, then go to step 9:

   1. Open the init.ora file in a text editor.

      Work with your database administrator to locate and edit the init.ora file.

   2. Change the global_names parameter to false.

   3. Close and save the init.ora file.

   The init.ora file is the initialization file that the Oracle database uses when you start up the Oracle database.

2. Select the Launch standalone radio button and click OK to display the Enterprise Manager console.

3. In the left hand pane of the Enterprise Manager Console, expand the tree by clicking the plus (+) symbols, choosing Database | Instance | Configuration.

   The Enterprise Manager console displays the General tab.

4. Click All Initialization Parameters to display the Edit Database Configuration page.

5. Select the SPFile radio button to display the list of SPFile parameters.

   The SPFile parameters are the parameters that are stored in the server side persistent file (spfile).

6. Scroll down to the Global Names parameter, click the Value field and set it to FALSE.

7. Click Apply.

   Enterprise Manager displays a message confirming that the parameters have changed.

8. Click OK.

9. To modify the inithsodbc.ora file, make a copy of the inithsodbc.ora file.

   On Windows, the inithsodbc.ora file is typically located in the <ORACLE_HOME>\hs\admin directory.

   The inithsodbc.ora file is an example of an initialization file that the Oracle database uses for Heterogeneous Services connections.

10. Rename the copy of the inithsodbc.ora file to init<database name>.ora, where <database name> is the name of the non-Oracle database.

    For example, if the database name is DD1, you would rename the copy of the inithsodbc.ora file to initDD1.ora.

11. Open the init<database name>.ora file (i.e. the inithsodbc.ora file that you renamed in the previous step) in a text editor and make the following changes:
    1. Insert a # symbol at the beginning of the following line to comment out the line:

       HS_FDS_TRACE_LEVEL = <trace_level>
       
       
       

       Having made the change, the line will look like this:

       # HS_FDS_TRACE_LEVEL = <trace_level>
       
       
       

    2. Insert the name of the non-Oracle database in the following line, where <data source name> is the name of the ODBC data source:

       HS_FDS_CONNECT_INFO = <data source name>
       
       
       

       For example, if the ODBC data source name is DD1, you would change the line as follows:

       HS_FDS_CONNECT_INFO = DD1
       
       
       

12. Close and save the init<database name>.ora file.

13. Create a new entry for the non-Oracle database in the listener.ora file as follows:
    1. Open the listener.ora file in a text editor.

       On Windows, the listener.ora file is typically located in the <ORACLE_HOME>\network\admin directory.

    2. Create a SID_DESC entry for the non-Oracle database under the SID_LIST_LISTENER heading.

       For example:

       (SID_DESC = 
       (SID_NAME=DD1)
       (ORACLE_HOME=E:\ORACLE\ORA9I)
       (PROGRAM=hsagent)
       )
       
       

       where:

       • DD1 is the non-Oracle database name

       • E:\ORACLE\ORA9I is the <ORACLE_HOME>

       • PROGRAM=hsagent defines the hsagent.exe file as the executable file enabling Heterogeneous Services to access the specified non-Oracle database

    Note: The above SID_DESC= entry example would be different when using an Oracle 8.1.7 (or later) Enterprise Edition database as follows:

    • the path must point to the 8i ORACLE_HOME (e.g. ORACLE_HOME=E:\ORACLE\ORA8I)

    • the PROGRAM= setting must be set to PROGRAM=hsodbc.

    Hint: To help you add new entries to the listener.ora file, Oracle provides a sample source file. You might want to copy text from the sample file, paste it into the corresponding listener.ora file and then modify the entry appropriately. On Windows, the listener.ora.sample file is typically located in the <ORACLE_HOME>\hs\admin\sample directory.

14. Save and close the listener.ora file.

15. Create a new entry for the non-Oracle database in the tnsnames.ora file as follows:
    1. Open the tnsnames.ora file in a text editor.

       On Windows, the tnsnames.ora file is typically located in the <ORACLE_HOME>\network\admin directory.

    2. Create an entry in the tnsnames.ora file for the non-Oracle database.

       For example:

       SALES =
        (DESCRIPTION=
         (ADDRESS_LIST=
          (Address=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
          (CONNECT_DATA=(SID=DD1))
          (HS=)
         )
        )
       

       where:

       • SALES is the name of the tnsnames entry for the non-Oracle database

       • DD1 is the SID of the non-Oracle database

       • (HS=) indicates that this is a Heterogeneous Services connection.

    Hint: To help you add new entries to the tnsnames.ora file, Oracle provides a sample source file. You might want to copy text from the sample file, paste it into the corresponding tnsnames.ora file and then modify the entry appropriately. On Windows, the tnsnames.ora.sample file is typically located in the <ORACLE_HOME>\hs\admin\sample directory.

16. Save and close the tnsnames.ora file.

17. Restart the database and tnslistener.

    Work with your database administrator to restart the database and tnslistener.

18. Display an operating system command prompt.

19. Type the following at the command prompt to test that the listener is working correctly:

    tnsping <data source name>
    
    
    

    where <data source name> is the name of the non-Oracle database you want to test.

    For example, if the database name is DD1, type the following at the command prompt:

    tnsping DD1
    
    

    The tnsping command should display an OK message. If the tnsping command does not succeed, it will display an appropriate error message indicating why the command did not succeed.

20. Start SQL*Plus (if it is not already running) and connect as the EUL owner.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT jchan/tiger@database;
    
    

    Where jchan is the EUL owner and tiger is the EUL owner password.

21. Type the following at the command prompt:

    SQL> create [public] database link <name> connect to <odbcuser> identified 
    by <odbcpassword> using '<tnsnames entry>';
    
    
    

    where:

    [public] is an optional argument that creates a public database link. If the [public] argument is not used, the statement creates a private database link. A public database link enforces a lower level of security than if you create a private database link (for more information, see your database administrator).

    <name> is the name of the database link.

    <odbcuser> is the user on the non-Oracle database.

    <odbcpassword> is the password of the <odbcuser> on the non-Oracle database.

    <tnsnames entry> is the name used at the start of each tnsnames entry in the tnsnames.ora file (e.g. from the earlier example, the <tnsnames entry> would be SALES).

    For example:

    SQL> create database link sales_link connect to odbc_username identified by 
    odbc_userpassword using 'SALES';
    
    
    

    Note the following:

    • To create a private database link, the EUL owner must have the CREATE DATABASE LINK privilege.

      For example, to grant the create private database link privilege in SQL*Plus you would issue the following statement:

      SQL> grant create database link to hdsuser;
      
      

      where hdsuser is the EUL owner

    • To create a public database link, the EUL owner must have the CREATE PUBLIC DATABASE LINK privilege.

      For example, to grant the create public database link privilege in SQL*Plus you would issue the following statement:

      SQL> grant create public database link to hdsuser;
      
      

      where hdsuser is the EUL owner.

    • You can include a domain as part of the link's name (e.g. SALES.mycompany.com).

      Whether you need to include a domain as part of the database link name depends on how you configure SQL*Net (for more information about SQL*Net configuration, see your database administrator).

    • If the non-Oracle database does not support usernames, you can omit the connect to <odbcuser> identified by <odbcpassword> section.

22. In SQL*Plus, test the connection to the non-Oracle database by issuing a SELECT statement against a table on the non-Oracle database.

    For example:

    SQL> select * from PRODUCT@sales_link;
    
    

    where PRODUCT is the name of a table on the non-Oracle database, and sales_link is the name of the database link to the non-Oracle database specified in the previous step.

    Note: Do not use DESC in the SQL statement because DESC is not supported against ODBC and gives unpredictable results.

For more information about generic connectivity, see the Oracle9i Heterogeneous Connectivity Administrator's Guide.

How to migrate an EUL from a non-Oracle database (accessed using native ODBC drivers) to an Oracle database (to support generic connectivity)

To migrate an EUL from a non-Oracle database to an Oracle database:

1. Export the EUL objects from the non-Oracle database to an .eex file.

   For more information, see "Which export/import method to use".

2. Set up generic connectivity in the Oracle database that will contain the EUL.

   For more information, see "How to set up generic connectivity for Discoverer using Enterprise Manager".

3. Create an EUL on the Oracle database.

   For more information about how to create an EUL, see "Creating and maintaining End User Layers".

4. Import the .eex file you created in step 1 into the Oracle database.

   For more information, see "Which export/import method to use".

5. Resolve any mappings (i.e. Discoverer Business areas and underlying objects).
   1. Choose View | Validate | Folders

   2. Select the first folder that displays an error and choose Edit | Properties to display the "Folder Properties dialog".

   3. Click the Database field to display the "Choose user dialog".

   4. Select the database link that you created in the "How to set up generic connectivity for Discoverer using Enterprise Manager" task.

   5. Select the correct user on the non-Oracle database.

   6. Click OK to close the Choose user dialog.

   7. Click OK to apply the new value in the Database field and close the Folder Properties dialog.

   8. Repeat steps a) to g) for each folder that displays an error.

6. Set up security on the Oracle database for database users.

   For more information, see "Controlling access to information".

   Discoverer users can now connect to the non-Oracle database using generic connectivity and continue to use their existing workbooks and worksheets.

About using the Transparent Gateway to connect to non-Oracle databases

The Transparent Gateway is one of the mechanisms supported by the Oracle database Heterogeneous Services feature for accessing non-Oracle databases.

Discoverer users can use an Oracle Transparent Gateway in conjunction with Heterogeneous Services to access a particular, vendor-specific, non-Oracle database. For example, you would use the Oracle Transparent Gateway for Sybase on Solaris to access a Sybase database that was operating on a Sun Solaris platform.

You must have the appropriate Oracle Transparent Gateway software installed.

For more information about the Oracle Transparent Gateway and how to set it up, see the Oracle9i Database Installation Guide.

About Discoverer support for Oracle Rdb

Discoverer can access Oracle Rdb without the need for (and restrictions of) open database connectivity (ODBC).

Topics in this section include the following:

• "What are the software requirements for using Discoverer with Oracle Rdb?"

• "Which Discoverer features are not supported by Oracle Rdb?"

• "Which Discoverer features are partially supported by Oracle Rdb?"

What are the software requirements for using Discoverer with Oracle Rdb?

To use Oracle Discoverer directly with Oracle Rdb you must install:

• OracleAS Discoverer Version 9.0.4, Oracle Discoverer Administrator or Oracle Discoverer Desktop

• Oracle Rdb Release 7.0.1.3 or later

• SQL*Net for Oracle Rdb7 Release 1.0.2. or later

You might find that the version of SQL*Net for Oracle Rdb7 requires a special patch with bug fixes specifically for Discoverer (for more information, see your database administrator). Providing you have the necessary support agreement, you can obtain this patch by contacting your Oracle support representative.

SQL*Net for Oracle Rdb7 enables an Oracle Rdb7 server to appear as an Oracle server to the client.

You need to install SQL*Net for Oracle Rdb7 software only once on each server system. You also need to prepare each Oracle Rdb7 database environment by defining the Oracle functions and the emulated Oracle data dictionary to serve with SQL*Net for Oracle Rdb7.

For more information about SQL*Net for Oracle Rdb7, see the following documentation:

• Guide to SQL*Net for Rdb7

  This guide helps you set up and use SQL*Net for Oracle Rdb7 software to configure and develop useful connections between SQL*Net clients and Oracle Oracle Rdb7 databases.

• Oracle SQL/Services Release Notes Release 7.1.2

  This manual contains Oracle SQL/Services Release Notes that are specific to SQL*Net for Oracle Rdb7 Release 7.1.2. The notes describe:

  • changed and enhanced features

  • upgrade and compatibility information

  • new and existing software problems and restrictions

  • other software documentation

• Comparison of SQL Dialects for Oracle and Rdb

  The principal purpose of this manual is to help Discoverer managers (who use SQL*Net for Oracle Rdb software) to understand differences in the Oracle and Oracle Rdb7 SQL dialects. This manual identifies where differences in the SQL dialects might occur, and provides additional information to help you achieve the desired functions.

Which Discoverer features are not supported by Oracle Rdb?

The following Discoverer features are not supported when using Discoverer with Oracle Rdb:

Feature	Reason for non-support
Query Prediction.	Uses Oracle's Cost Based Optimizer.
The Discoverer EUL V5 Workbooks - eul5.eex.	Uses Oracle's PL/SQL.
Register PL/SQL functions.	Uses Oracle's PL/SQL.
Summary folders where refresh is managed by Discoverer.	Uses Oracle's PL/SQL.
Database Roles.	Oracle RDBMS specific.
Parser Hints on Folders.	Oracle RDBMS specific.
Server side scheduled reporting.	Uses Oracle's PL/SQL.
Oracle analytic functions.	Oracle RDBMS specific.

Which Discoverer features are partially supported by Oracle Rdb?

The following features are partially supported by Oracle Rdb:

Feature	Reason for non support
Some functions: INSTR INSTRB VSIZE UID DUMP TRANSLATE SOUNDEX STDDEV VARIANCE STDDEV_DISTINCT VARIANCE_DISTINCT	Oracle RDBMS specific
Security - Roles and Users	Not supported directly, needs to be set up by the Rdb administrator.