Oracle9i Discoverer Administrator Administration Guide Version 9.0.2 Part Number A90881-02 |
|
This chapter explains how to enable users to schedule workbooks and contains the following topics:
A workbook is a collection of Discoverer worksheets. Workbooks are essentially documents containing query definitions. Discoverer end users can store their workbooks centrally in the database. In the Discoverer Desktop, users can also store workbooks on their own PC or on a network file server.
A scheduled workbook is a workbook that has been set by the user to run automatically at a particular date, time, and frequency. Scheduled workbooks are placed on a process queue on the database server. End users can schedule workbooks using Discoverer Plus and Desktop. As the Discoverer manager, you can use Discoverer Administrator to monitor and maintain scheduled workbooks.
Note the following:
From a Discoverer end user's point of view, the ability to schedule workbooks is useful for:
For example, a Discoverer end user might want to run a report that they know will take a long time to complete. The user can schedule the report to run overnight and have the results ready to view the next morning.
From a Discoverer manager's point of view, workbook scheduling is useful to prevent long-running queries from adversely affecting system performance. You can force users to schedule workbooks (either all workbooks, or only those workbooks that will exceed a predicted time that you specify), and you can further specify the time periods that scheduled workbooks are permitted to run.
The table below shows what happens when an end user schedules a workbook in Discoverer Plus.
Action | Notes | |
---|---|---|
1. |
User selects worksheet (or worksheets) to include in scheduled workbook |
|
2. |
User specifies date, time, and frequency at which scheduled workbook is to run |
|
3. |
Discoverer Plus confirms that scheduling the workbook does not exceed user's limit on number of scheduled workbooks |
The limit on the number of scheduled workbooks restricts the number of scheduled workbooks that can be maintained at any one time. As the Discoverer manager, you specify this limit on the "Privileges dialog: Scheduled Workbooks tab". If the user exceeds their limit, Discoverer Plus displays a message and the workbook is not scheduled. The job_queue_processes value controls the maximum number of jobs that can be run at any one time on the server. For more information, see "What Oracle database features support workbook scheduling?". You can also limit on the number of scheduled workbooks is not the same as the job_queue_processes value in the Oracle initialization file. |
4. |
Discoverer adds scheduled workbook to DBMS_JOB within Oracle database kernel |
|
5. |
Periodically, the job queue process is activated and the next job in the queue is run |
The scheduled workbook is processed entirely on the server. You use the job_queue_interval value in the Oracle database initialization file to specify the length of time that the job queue process is inactive. For more information, see "How to control the frequency that the server checks for new scheduled jobs". |
6. |
A database table is created and populated with the output or result set of the scheduled workbook |
The result set is stored under the schema specified by the Discoverer manager. For more information, see "Where to store the results of scheduled workbooks?". |
7. |
User can view scheduled workbook |
|
8. |
User can delete result set when no longer required - at which point the table is dropped |
As the Discoverer manager, you can specify how long the results of a user's scheduled workbook can remain in the database before being automatically deleted (see "Privileges dialog: Scheduled Workbooks tab"). |
Discoverer takes advantage of native scheduling functionality in the Oracle database to schedule workbooks. Specifically, Discoverer makes use of the DBMS_JOB package.
You control the processing of the DBMS_JOB package using the following parameters in the Oracle database initialization file (the INIT<SID>.ORA file).
Parameter in INIT<SID>.ORA | Use to: |
---|---|
job_queue_processes |
Use this parameter to specify the number of concurrent processing requests that can be used to process DBMS_JOB. The default value is zero, which means processing requests will not be created. If you want to have ten processing requests to be handled simultaneously, set this parameter to 10. If any other applications use DBMS_JOB, set this parameter to at least 2. Hint: You need more than one job queue process because a job that fails (for any reason) might continue to be re-submitted and prevent any other jobs in the queue from being submitted. |
job_queue_interval |
Use this parameter to specify the time frequency (in seconds) after which job processes are started to process pending jobs. The default is 60 seconds, which means that every 60 seconds the job processes start to serve processing requests. What you set this parameter to depends on how frequently you want the process to start up and serve the requests that have been made. Oracle recommends that you set this parameter to at least 600 seconds (i.e. 10 minutes). Note that this parameter also affects the Discoverer summary management feature (for more information about summary folders, see Chapter 14, "Managing summary folders"). |
For more information about setting the parameters in the INIT<SID>.ORA file, see "How to control the frequency that the server checks for new scheduled jobs".
Note that workbook scheduling is only available when running against an Oracle database because both DBMS_JOB and the INIT<SID>.ORA file are only available with Oracle databases.
Discoverer stores the results of scheduled workbooks in database tables. Before an end user can schedule a workbook, you must decide which database user (schema) is to own those tables. You have two choices as described in the table below:
For more information about how to specify where the results of scheduled workbooks are stored, see "How to specify the owner of the tables containing scheduled workbooks results".
Before an end user can schedule a workbook, you must:
Before an end user can schedule a workbook, the DBMS_JOB package must have already been installed on the database. If the DBMS_JOB package has not yet been installed, you must install it.
To confirm that the DBMS_JOB package is installed:
SQL> select * from all_objects where object_name='DBMS_JOB' and
object_type = 'PACKAGE';
If the above statement returns one or more rows, the DBMS_JOB package is already installed on the database.
If the above statement returns no rows, the DBMS_JOB package has not yet been installed. You must install the DBMS_JOB package before users can schedule workbooks.
To install the DBMS_JOB package if it is not yet installed, for Oracle9i databases:
SQL> CONNECT username/password@database AS SYSDBA;
SQL> start <ORACLE_HOME>/rdbms/admin/dbmsjob.sql;
SQL> start <ORACLE_HOME>/rdbms/admin/prvtjob.plb;
To install the DBMS_JOB package if it is not yet installed, for Oracle databases earlier than Oracle9i:
Version of Oracle: | Type the following: |
---|---|
Oracle 8.0 and above |
|
Oracle8i Personal Edition |
|
SVRMGRL> connect internal
SVRMGRL> start <ORACLE_HOME>/rdbms/admin/dbmsjob.sql;
SVRMGRL> start <ORACLE_HOME>/rdbms/admin/prvtjob.plb;
In some operating systems, the RDBMS directory is suffixed with the version number (e.g. RDBMS73).
The results of scheduled workbooks are stored in database tables. Discoverer's default behavior is to store scheduled workbooks in the EUL owners schema. Before an end user schedules a workbook, you must decide which database user is to own those tables. You have two choices:
For more information about these two choices, see "Where to store the results of scheduled workbooks?"
Regardless of the choice you make, the database user that will own the tables containing scheduled workbook results must have certain database privileges.
To specify a database user to own the database tables containing the results of that workbook:
SQL> grant CREATE PROCEDURE to <user name>;
SQL> grant CREATE TABLE to <user name>;
SQL> grant CREATE VIEW to <user name>;
where <user name> is the name of the database user that is to schedule workbooks.
Note that you must grant these privileges directly to the database user and not to a database role.
To specify a scheduled workbook results schema to own the results tables of scheduled workbooks:
Note: You must know the user name and password of the EUL owner in order to run the script batchusr.sql.
SQL> start<ORACLE_HOME>\discv902\sql\batchusr.sql;
Note: Ask your database administrator if you are unsure about these settings.
Note: The script creates a scheduled workbook results schema and grants the following database privileges:
The scheduled workbook results schema created by the batchusr.sql script is granted the SELECT ANY TABLE database privilege to enable access to the underlying data needed for workbook scheduling. Without this grant, this database user's access to the underlying data might be limited.
If you do not want the scheduled workbook results schema to have the SELECT ANY TABLE database privilege on the underlying data, you must revoke the privilege manually.
You must use Discoverer Administrator to choose the database user that will own the scheduled workbook result tables created in the database.
You have used the script batchusr.sql to create a scheduled workbook results schema and then used the Privileges dialog to select the scheduled workbook results schema to be used for the EUL owner.
For more information about scheduled workbook privileges, see Chapter 7, "How to set scheduled workbook limits".
You use the following two parameters in the INIT<SID>.ORA file to control scheduled workbook processing:
For more information about these parameters, see "What Oracle database features support workbook scheduling?".
To control the frequency of scheduled workbook processing:
The INIT<SID>.ORA file is held in <ORACLE_HOME>\database. The default name of the file is INITORCL.ORA, where ORCL is the <SID> name.
job_queue_processes = <a_value>
job_queue_interval = <a_value_in_seconds>
where:
For example, you might add the following two lines to the INIT<SID>.ORA file:
job_queue_processes = 2
job_queue_interval = 600
Discoverer's summary management feature and workbook scheduling feature both use the Oracle database's native scheduling capability. The values you specify for job_queue_processes and job_queue_interval will affect both features.
The database must be stopped and re-started for these changes to take effect.
As the Discoverer manager, you will want to monitor the status of currently scheduled workbooks, as well as:
Note: If you have an Oracle Applications mode EUL you must login as the EUL owner to view and manage scheduled workbooks.
To view and manage a scheduled workbook:
Hints: You can:
The View Error button is only enabled for scheduled workbooks when the status is set to indicate that an error occurred while running the query.
When you carry out an EUL upgrade to this version of Discoverer, a new version of the batch PL/SQL package is installed into the user's schema alongside the existing package. Preserving the existing PL/SQL package prevents any existing scheduled workbook batch jobs from being destroyed. Function names in the new batch PL/SQL package however, remain the same as the existing batch PL/SQL package.
When you upgrade the EUL to this version of Discoverer and the following conditions are true:
In this case you must install the new version of the batch PL/SQL package (EUL5_BATCH_USER) using the SQL script batchusr.sql. The script is located in the <ORACLE_HOME>\discv902\sql directory. The new version of the batch PL/SQL package can be installed into the scheduled workbook results schema (i.e. the user's schema that owns the tables containing the workbook results). For more information, see "Use a script to specify a scheduled workbook results schema to own the scheduled workbook results tables (i.e. not the database user scheduling the workbook)".
|
Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|