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 support for non-Oracle databases using native ODBC drivers"

• "Discoverer support for non-Oracle databases using generic connectivity"

• "Discoverer support for non-Oracle databases using the Transparent Gateway"

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

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

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

• Oracle9iAS Discoverer V9.0.2, Oracle9i Discoverer Administrator or Desktop

• Oracle Rdb Release 7.0.1.3 or later

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

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:

• 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:

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

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

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

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

• "Why does Discoverer require a list of known database users?"

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

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:

• be created in the EUL owner database user

• be called EUL5_ODBC_SCHEMAS

• return a single column called (or aliased to) OS_SCHEMA_NAME

• be based on underlying objects that are accessible to all users

• return no duplicates

• only return users (i.e. not groups or aliases)

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

SQL> select username from all_users

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

SQL> select name from sysusers where suid > 1 and uid <> gid

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:

• create the view manually in the EUL owner database user alongside the standard EUL objects

• grant the same permissions on the view as on other read-only objects in the EUL (e.g. as for the table EUL5_VERSIONS)

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:

• using the Connect dialog

  Type the following in the Connect field on the Connect dialog:

  ODBC:<dbname>

  For example:

  ODBC:sqldb1

• using the command line interface

  To start Discoverer Administrator using the command line interface, type the following at the command prompt:

  dis5adm.exe /connect <username/password>@ODBC:<dbname>

  For example:

  dis5adm.exe /connect jchan/jchan@ODBC:sqldb1

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

• "How to set up generic connectivity"

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

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:

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

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

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:

• 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 on non-Oracle databases

  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 with ODBC data.

• 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

  The data is brought into the Oracle database and the function is applied. This enables access to very powerful analytical functions against ODBC data.

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