Skip Headers

Oracle® Business Intelligence Discoverer Administration Guide 10g Release 2 (10.1.2.1) B13916-04 |
| Library | Product | Contents | Index

Previous | Next |

# 26 Using SQL files and trace files

This chapter provides reference information about files that are used by Discoverer and includes the following topics:

- "List of SQL files supplied with Discoverer"

- "Working with Oracle Support to resolve Discoverer issues"

- "How to create trace files to diagnose Discoverer problems"

- "How to set the DCLOG_CONFIG_FILE Windows environment variable and create an EUL trace file"

## List of SQL files supplied with Discoverer

A number of SQL files are installed with Discoverer Administrator and enable you to carry out specific database configuration tasks. The table below lists the SQL files and describes their functions.

The following files are located in the <ORACLE_HOME>\discoverer\util directory unless otherwise stated:

File name | Description
--- | ---
batchusr.sql | Sets up a scheduled workbook results schema (for more information, see "How to specify the owner of the tables containing scheduled workbooks results").
dbmsjob.sql | The interface for the Oracle DBMS JOB queue (for more information, see "How to confirm that the DBMS_JOB package is installed"). The dbmsjob.sql file is not installed with the database in the <ORACLE_HOME>\rdbms\admin directory.
eul4del.sql | Clears all 4.1 EUL objects (for more information, see "Upgrade step 5: Remove the Release 4.1 EULs").
eul5.sql | Creates the functions required by the eul5.eex business area and its workbooks (for more information, see "How to install the standard EUL status workbooks").
eul5_apps.sql | Creates the functions required by the eul5.eex business area and its workbooks when using an Oracle Applications EUL (for more information, see "How to install Oracle Applications EUL status workbooks"). Note: You use this script only with Oracle Applications EULs.
eul5_id.sql | Changes the reference number of an EUL. Use this script when you have used the database export and import utilities to copy an EUL and you want to: modify objects in both the original EUL and the new EUL having modified objects in both EULs, you then want to copy objects between the two EULs using the Discoverer Export Wizard and Import Wizard (or the Discoverer /export and /import commands) Run the eul5_id.sql script to give a new reference number to the new EUL and thereby prevent any uniqueness conflicts in the above scenario (for more information, see "How to import an EUL using the standard database import utility").
eulasm.sql | Grants the privileges required for summary folder management (and ASM) in Discoverer Administrator (for more information, see "How to use SQL*Plus to grant the privileges required to create summary folders").
eulgwreg.sql | Contains a template EUL gateway registration script that you use when setting up EUL gateway metadata. For more information, see egwspec.doc and eulgatew.doc located in the <ORACLE_HOME>\discoverer\kits directory.
eulgwtbl.sql | Contains the template CREATE TABLE SQL statements that you use when setting up EUL gateway metadata. For more information, see egwspec.doc and eulgatew.doc located in the <ORACLE_HOME>\discoverer\kits directory.
eulgwvue.sql | Contains the template CREATE VIEW SQL statements that you use when setting up EUL gateway metadata. Note: If you execute this script without modifcation, it will not create a valid gateway. You must modify the SQL statement to correctly map the view columns to the metadata, in order to transfer information through the gateway. For more information, see egwspec.doc and eulgatew.doc located in the <ORACLE_HOME>\discoverer\kits directory.
eulsown.sql | Modifies summary folder ownership information that you might need if you export the EUL using the database export utility and import it into a different database user (for more information, see "How to export an EUL using the standard database export utility"). This script prompts you for the owner name of the summary folders in the source EUL, and for the owner name of the new summary folder in the target EUL (for more information, see "How to import an EUL using the standard database import utility").
eulstdel.sql | Deletes query prediction statistics older than a specified age (for more information, see "How to delete old query prediction statistics").
eulsuqpp.sql | Grants the necessary database privileges to enable query performance prediction (QPP) (for more information, see "How to use query prediction with secure views").
gwdrop.sql | Enables you to delete an EUL gateway. For more information, see egwspec.doc and eulgatew.doc located in the <ORACLE_HOME>\discoverer\kits directory.
gwgrant.sql | Enables you to grant access to EUL gateways. For more information, see egwspec.doc and eulgatew.doc located in the <ORACLE_HOME>\discoverer\kits directory.
lineage.sql | You must run this script before using the lineage.eex business area and its workbook (for more information, see "Using Discoverer with Oracle Warehouse Builder").

## Working with Oracle Support to resolve Discoverer issues

If you work with Oracle Support to resolve a Discoverer issue, you will typically be asked for the following information:

- the version of Discoverer

- the Discoverer tool that was being used when the problem occurred (i.e. Discoverer Administrator, Discoverer Plus, Discoverer Viewer, Discoverer Desktop)

- exact details of the actions the user performed in Discoverer prior to the problem occurring

- what the Discoverer user saw on the screen when the problem occurred

In addition, Oracle Support might ask you to provide a trace file (for more information, see "How to create trace files to diagnose Discoverer problems").

## How to create trace files to diagnose Discoverer problems

Sometimes you might encounter a problem with Discoverer and be unable to identify the cause of the problem (e.g. a generic error message might not provide sufficient information). To provide more information about Discoverer problems, you can generate two types of trace file:

- an EUL trace file, which records low-level interactions between Discoverer and the EUL

- a server trace file, which records the SQL statements sent to the database server

You can either use trace files yourself to diagnose the cause of the problem, or use trace files when working with Oracle Support.

To create an EUL trace file and/or a server trace file containing information relevant to the problem you are trying to resolve:

1. Start with Discoverer functioning as normal.

2. Specify the trace file you want to create, as follows:

   - to create an EUL trace file, you create a configuration file containing appropriate values, and set the DCLOG_CONFIG_FILE Windows environment variable to the name and path of the configuration file

     For more information about setting the DCLOG_CONFIG_FILE Windows environment variable, see "How to set the DCLOG_CONFIG_FILE Windows environment variable and create an EUL trace file".

   - to create a server trace file, set the SqlTrace Discoverer registry setting to 1

     For more information about Discoverer registry settings, see Chapter 21, "Discoverer registry settings".

3. Perform the actions in Discoverer that cause the problem.

4. Remove the DCLOG_CONFIG_FILE Windows environment variable and/or restore the SqlTrace Discoverer registry settings to their default values to prevent unnecessary information being written to the trace files.

## How to set the DCLOG_CONFIG_FILE Windows environment variable and create an EUL trace file

You set the DCLOG_CONFIG_FILE Windows environment variable to gather EUL trace information in an EUL trace file. You can subsequently enable or disable the writing of information to the trace file.

To set the DCLOG_CONFIG_FILE Windows environment variable and create an EUL trace file:

1. From the Windows Start menu, select Control Panel | System Properties | Advanced tab | Environment Variables.

2. Create a new user variable:

   - Variable name=`DCLOG_CONFIG_FILE`

   - Variable value=`<path>/<file_name>`

     For example, `c:\dc_config/dc_config.txt`

3. Create a text file in the directory specified for the DCLOG_CONFIG_FILE Windows environment variable (e.g. c:\dc_config\dc_config.txt) .

4. Enter the configuration settings in the text file (e.g. dc_config.txt) for Discoverer to use to create a trace file containing the required level of diagnostic information.

   For example, you could enter the following settings into dc_config.txt:

   DCLOG_ENABLED=1

   DCLOG_LEVEL=4

   DCLOG_LOGDIR=d:\oracle_home\bin

   DCLOG_ODLDEMO=1

   DCLOG_DCELEVEL='OL=6,DC=10,DCOV=10'

   You specify one or more values for the DCLOG_DCELEVEL setting, and give each value an integer (e.g. OB=1). If you specify more than one value integer pair, separate each pair with a comma (e.g. OB=1, OL=3). The values used for the DCLOG_DCELEVEL setting will create specific trace information at defined levels of granularity. The higher the integer used, the more detailed the information.

   The different types of trace information that can be written to the trace file using the DCLOG_DCELEVEL setting are as follows:

   - DC - General important events

     - DC=15 logs a range of Discoverer actions

   - DCBL - Bulk load details

   - DCIE - Import/export details

     - DCIE=2 logs actions and elements processed by an import/export operation

   - DCOV - Metadata validation

   - OB - Process of posting data to the database when a transaction is committed

   - OL - Database interactions (SQL statements, bind variables, etc)

     - OL=0 logs all SQL statements which fail execution on the database

     - OL=2 logs all SQL statements executed, as well as numbers of rows fetched and basic cursor lifecycle (in addition to the information logged by OL=0)

     - OL=3 logs all cursor bind variables (in addition to the information logged by OL=2)

   - OM - Database connect/disconnect

5. Connect to Discoverer Administrator or Discoverer Desktop.

   Discoverer writes the trace information to an XML trace file (e.g. log200501204153419588.xml), according to the configuration settings in the text file that is specified in the DCLOG_CONFIG_FILE Windows environment variable (e.g. in dc_config.txt).

6. When you have gathered enough trace information, you can disable the writing of trace information to prevent unnecessary information being written to the trace file.

   To disable the writing of trace information, you edit the text file specified in the DCLOG_CONFIG_FILE Windows environment variable (e.g. dc_config.txt), and change the value of the DCLOG_ENABLED setting from 1 to 0 (alternatively, you could remove the DCLOG_ENABLED setting).

   For more information, see "Working with Oracle Support to resolve Discoverer issues".