Skip Headers

Oracle9i Discoverer Administrator Administration Guide
Version 9.0.2

Part Number A90881-02
HomeSolution Area
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

23
Discoverer support for Oracle Rdb, and non-Oracle databases

Discoverer support for Oracle Rdb and non-Oracle databases

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

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?

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

You may 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:

Which Discoverer features are not supported by Oracle Rdb?

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

Table 23-1 Features not supported in Discoverer when using 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:

Table 23-2 Features partially supported in Discoverer when using 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. 

Discoverer support for non-Oracle databases using native ODBC drivers

Discoverer supports access to non-Oracle databases using native ODBC drivers (the ODBC drivers supplied by your database vendor).

Discoverer enables you to create an End User Layer on the non-Oracle database in an existing database user (i.e. Discoverer cannot create a new user on the non-Oracle database). Discoverer uses the security features of the non-Oracle database to control access to the EUL on the non-Oracle datasource and the data it defines.

The available functionality depends on the capabilities of both the non-Oracle database and its ODBC driver. Some Discoverer features are not supported or are only partially supported when you connect to a non-Oracle database using native ODBC drivers.

For information about the non-Oracle datasources tested with Discoverer, see the Release Notes.

This section includes the following topics:

What are the software requirements for Discoverer to support non-Oracle databases using native ODBC drivers?

For Discoverer to support a non-Oracle database, you must install the ODBC driver supplied by the vendor of the non-Oracle database.

For more information about installing and configuring the ODBC driver, see the documentation supplied with the non-Oracle database.

Which Discoverer features are not supported by non-Oracle databases using native ODBC drivers?

The following Discoverer features are not supported by non-Oracle databases using native ODBC drivers:

Table 23-3 Features not supported in Discoverer when using non-Oracle databases and native ODBC drivers
Feature  Reason for non-support 

Database roles. 

Oracle specific. 

Collect query statistics. 

Oracle specific. 

Grant Workbook (the ability to grant access to workbooks to other users). 

Oracle specific. 

Query governor - Query prediction warning. 

Oracle specific. 

Non-ANSI standard database functions in calculations. 

Oracle specific. 

Scheduled workbooks. 

Requires Oracle PL/SQL to submit jobs for batch execution. 

Summary management. 

Requires Oracle PL/SQL to perform management functions.

Note: Summary folders managed by Discoverer are disabled. External summary folders can still be registered by the Discoverer manager and used in query execution (where appropriate). 

Query prediction. 

Requires Oracle specific database objects and functions. 

Date Hierarchies. 

Requires Oracle specific DECODE() syntax (non-ANSI standard). 

Save workbooks to some databases. 

May be restricted by size based on binary large object data type limits. 

EUL Gateway. 

Requires checkpoints for partial transaction rollback. 

Identify and create a new users. 

Oracle specific. 

Some Discoverer Administrator commands:

  • /load

  • /refresh_bus_area

  • /refresh_folder

  • /refresh_summary

  • /db_link

  • /source

 

Oracle specific. 

Some Desktop commands:

  • /opendb

  • /batch

 

Oracle specific. 

Which Discoverer features are only partially supported by non-Oracle databases using native ODBC drivers?

Some Discoverer features are only partially supported when you connect to a non-Oracle database using native ODBC drivers. Typically, the only disabled aspects of a feature are those aspects that the non-Oracle database (or its ODBC driver) cannot support.

The following Discoverer features are only partially supported when using Discoverer with non-Oracle databases and native ODBC drivers:

Table 23-4 Discoverer features partially supported by non-Oracle databases when using native ODBC drivers
Feature  Implementation restriction 

EUL management 

You can create an EUL on the non-Oracle database for the current database user or an existing database user. The facility to identify and create a new user is not available. Similarly, the tutorial data can only be created if the tutorial user has been created in advance 

Count all rows 

The facility to return a count of all the rows to be returned by a query will not work for all possible queries. Oracle Discoverer creates a new query to return this count and makes use of more complex SQL (sometimes called an Inline View) to improve performance. As Inline View syntax is not supported by ODBC, it is necessary to "flatten" the SQL before it is sent to the non-Oracle database. If the query whose rows are being counted contains any aggregate functions, this process is not possible. In this case, a message is displayed explaining that a count is not available for the current query. 

Creating items using the formula editor 

Users can still create derived or complex items using the formula editor by combining items and constants with database functions. However, the available functions are only those functions or their equivalents available via ODBC on the non-Oracle database. For example:

  • DECODE() is an Oracle specific function and is not available on ODBC

  • SYSDATE is replaced by the ODBC function NOW()

 

A number of non-Oracle databases escalate locks during intensive processes such as bulk load (for more information about bulk load, see Chapter 3, "Creating and maintaining End User Layers"). If you run multiple Discoverer Administrator sessions on the same database, processes might lock each other out. Therefore we recommend that you run only one Discoverer Administrator session at a time against a single EUL.

In the case of some non-Oracle databases, multiple databases can share common system objects. The individual databases are said to reside in the same datasource.

You can run multiple copies of Discoverer Administrator against different datasources (or different databases in the same datasource) with no locking contention providing system objects are unique to the individual databases.

Which Discoverer features are implemented differently by non-Oracle databases using native ODBC drivers?

The following Discoverer features are implemented differently by non-Oracle databases using native ODBC drivers:

Table 23-5 Discoverer features implemented differently by non-Oracle databases using native ODBC drivers
Feature  Implementation difference 

Folder validation error messages 

If you use Discoverer Administrator to validate folders on a non-Oracle database, any error messages will be governed by that non-Oracle database.

For example, if an underlying object no longer exists:

  • connection to an Oracle database displays the following message:

    ORA-00942 Table or view does not exist

  • connection to a SQL Server database displays the following message:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name EMP

The quality of the message depends on both the non-Oracle database and the ODBC driver 

Why does Discoverer require a list of known database users?

Discoverer Administrator requires a list of known database users in order to perform many of its operations. However, some non-Oracle databases return only a list of users that own objects that the currently connected user can see. You can continue to use Discoverer Administrator in this restricted environment but some administrative functions will not be available. To make sure you have access to all the administrative functions, you can create a view on the non-Oracle database that returns more accurate user information (e.g. retrieves all database user names).

The view you create must:

If you create the view on an Oracle database, the SQL is:

If you create the view on a SQL Server database, the SQL is:

In this case, the first filter removes temporary user id's, while the second removes user groups.

Note that a suitable view is automatically created with an EUL for some non-Oracle databases. If the view was not automatically created, you must:

If you have more than one EUL in the database, you might have as many copies of this object as you have EULs.

How to use Discoverer with non-Oracle databases using native ODBC drivers

You can use Discoverer with non-Oracle databases using native ODBC drivers by connecting in one of two ways:

In both cases, <dbname> is the name of the non-Oracle database defined in the ODBC Data Source Administrator (in the Windows Control Panel program group).

Discoverer support for non-Oracle databases using generic connectivity

Discoverer fully leverages the Oracle8i database generic connectivity feature. For more information about this feature, see theOracle9i Heterogeneous Connectivity Administrator's Guide.

This section includes the following topics:

What is generic connectivity?

Generic connectivity is the ability of the Oracle database to provide users with access to ODBC or OLE DB (object linking and embedding database) databases. You can use the Oracle database's generic connectivity feature to connect to non-Oracle databases with Oracle Discoverer. The databases 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).

For Discoverer, the major difference between using the Oracle database generic connectivity feature and using ODBC drivers to connect to a non-Oracle database is as follows:

How to set up generic connectivity

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

What are the advantages of using generic connectivity?

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

Discoverer support for non-Oracle databases using the Transparent Gateway

In addition to generic connectivity and native ODBC drivers, you can also use the Transparent Gateway with Oracle databases to provide Discoverer users with access to ODBC and OLE DB databases. The databases must comply with the ODBC standard (for more information about the Transparent Gateway and how to set it up, see the Discoverer Release Notes).


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
HomeSolution Area
Go To Table Of Contents
Contents
Go To Index
Index