23 Discoverer support for non-Oracle databases and Oracle Rdb

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 23-1 EUL location using ODBC drivers versus using generic connectivity

Description of "Figure 23-1 EUL location using ODBC drivers versus using generic connectivity"

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 Oracle Application Server Control

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 Central Console to set up generic connectivity for Discoverer, you can use the following example (using Oracle Application Server Control version 2.2).

To set up generic connectivity for Discoverer using the Central Console:

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

   Note: To set the Global Names parameter for Oracle 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 Central Console.

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

   The Central 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.

   Central Console 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\ORA10)
       (PROGRAM=hsodbc)
       )
       
       

       where:

       • DD1 is the non-Oracle database name

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

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

    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".

   Note: This must be carried out using a version of Discoverer that supports a direct ODBC connection (i.e. 9.0.4 or earlier).

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 Oracle Application Server Control".

3. Create an EUL on the Oracle database.

   For more information about how to create an EUL, see Chapter 4, "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 or table/view dialog".

   4. Select the database link that you created in the "How to set up generic connectivity for Discoverer using Oracle Application Server Control" 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 Chapter 7, "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 ORA-3113 Errors when Using Heterogeneous Data Services with Discoverer Administrator

If you are accessing data from a non-Oracle database using Discoverer Administrator (for example to create a business area) and ORA-3113 errors are displayed, do one of the following:

• Use a more recent version of the Oracle database (version 9.0.1.4 or later).

• Contact Oracle Support to determine the availability of patches for earlier versions of the Oracle database.

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

To use Discoverer directly with Oracle Rdb you must install:

• Discoverer V9.0.4 or later, Discoverer Administrator or 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:

Which Discoverer features are partially supported by Oracle Rdb?

The following features are partially supported by Oracle Rdb: