8 Scheduling workbooks

What are workbooks and scheduled workbooks?

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 Discoverer Plus and 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:

• Users can run workbooks in the background using the Discoverer command line options (Desktop only). Such workbooks are not scheduled workbooks.

• Scheduled workbooks, scheduling information and the results of scheduled workbooks are not exported when you export a business area.

• If the objects of the EUL used by a scheduled workbook changed between the time when the workbook is scheduled and the time when the result set is displayed, the scheduled workbook's status is set to indicate that the EUL has changed.

• For information about compatibility with earlier versions of Discoverer, see "About scheduled workbooks and compatibility between the current version of Discoverer and pre-Oracle9i database releases".

Why schedule workbooks?

From a Discoverer end user's point of view, the ability to schedule workbooks is useful for:

• reports that take a long time to run

• reports that have to run at regular intervals

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.

What happens when an end user schedules a workbook?

The table below shows what happens when an end user schedules a workbook in Discoverer Plus.

What Oracle database features support workbook scheduling?

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 parameter in the Oracle database initialization file (the INIT<SID>.ORA file).

For more information about setting the parameter in the INIT<SID>.ORA file, see "How to control the frequency that the server checks for new scheduled jobs".

Where to store the results of scheduled workbooks?

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

What are the prerequisites for scheduling workbooks?

Before an end user can schedule a workbook, you must:

• configure the database to enable the scheduled workbooks feature by:

  • confirming that the DBMS_JOB package is already installed on the database and installing the package if it is not already installed (for more information, see "How to confirm that the DBMS_JOB package is installed")

  • specifying the database user that will own the tables containing the results of scheduled workbooks (for more information, see "How to specify the owner of the tables containing scheduled workbooks results")

  • granting the SELECT privilege on the view V_$PARAMETER to the database user that owns the results set (for more information, see "How to grant the SELECT privilege on the synonym v$parameter to enable you to run scheduled workbooks")

  • setting the frequency of scheduled workbook processing (for more information, see "How to control the frequency that the server checks for new scheduled jobs")

• grant the Schedule Workbooks privilege to the database user (for more information, see "How to specify the tasks a user or role (responsibility) can perform")

How to confirm that the DBMS_JOB package is installed

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:

1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT dba_user/dba_pw@database;
   
   

   Where dba_user is the database administrator, and dba_pw is the database administrator password and database is the database to connect to.

2. Type the following at the command prompt:

   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 and later:

1. Start SQL*Plus (if it is not already running) and connect as the SYS user, or a user to which the sysdba privilege has been granted.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT sys/sys_pw@database AS SYSDBA;
   
   

   Where sys is the SYS user and sys_pw is the SYS user password.

2. Type the following at the command prompt:

   SQL> start <ORACLE_HOME>/rdbms/admin/dbmsjob.sql;
   
   

3. Type the following at the command prompt:

   SQL> start <ORACLE_HOME>/rdbms/admin/prvtjob.plb;
   
   

How to specify the owner of the tables containing scheduled workbooks results

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:

• the database user running the scheduled workbook

• a scheduled workbook results schema

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.

Specify that the database user scheduling a workbook is to own the database tables containing the results of that workbook

To specify a database user to own the database tables containing the results of that workbook:

1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT dba_user/dba_pw@database;
   
   

   Where dba_user is the database administrator and dba_pw is the database administrator password.

2. Type the following at the command prompt:

   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.

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)

To specify a scheduled workbook results schema to own the results tables of scheduled workbooks:

Note: Oracle Applications users should not use this script, but instead use the existing Oracle Applications APPS user (for more information, see "How to set scheduled workbook limits").

Note: You must know the user name and password of the EUL owner in order to run the script batchusr.sql.

1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT dba_user/dba_pw@database;
   
   

   Where dba_user is the database administrator and dba_pw is the database administrator password.

2. Type the following at the command prompt:

   SQL> start<ORACLE_HOME>\discoverer\util\batchusr.sql;
   
   

3. Enter the user name, password and database connection details for the scheduled workbook results schema being created by the script batchusr.sql.

4. (optional) Change the tablespace settings for the scheduled workbook results schema.

   Note: Ask your database administrator if you are unsure about these settings.

5. Enter the EUL owner user name for the EUL that the scheduled workbook results schema will have access to.

   Note: The script creates a scheduled workbook results schema and grants the following database privileges:

   • CREATE PROCEDURE

   • CREATE TABLE

   • CREATE VIEW

   • SELECT ANY TABLE

   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.

6. Connect to Discoverer Administrator as the EUL owner that you used in the previous step (when running the script batchusr.sql).

7. In Discoverer Administrator select Tools | Privileges and display the "Privileges dialog: Privileges tab".

   You must use Discoverer Administrator to choose the database user that will own the scheduled workbook result tables created in the database.

8. Click the Select button next to the Show scheduling limits for field to display the "Select User/Role dialog" where you select the EUL owner.

9. Use the Select the user to own the tables which store any workbook results: drop down list to select the scheduled workbook results schema that you created in the script batchusr.sql.

10. Click OK.

    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 "How to set scheduled workbook limits".

Notes

• The scheduled workbook results schema created by the script batchusr.sql can only have access to one EUL.

• Be aware that a Discoverer end user could run a scheduled workbook that fills the available result set space. Other database users would not be able to run scheduled workbooks until it is cleared.

• The scheduled workbook results schema created by the batchusr.sql script will not be able to directly schedule a workbook using Discoverer Plus.

How to grant the SELECT privilege on the synonym v$parameter to enable you to run scheduled workbooks

You grant the SELECT privilege on the synonym v$parameter by granting the SELECT privilege on the view v_$parameter to the scheduled workbook results set schema name.

The scheduled workbook results schema requires the select privilege to be granted on v_$parameter to run scheduled workbooks. However, v_$parameter is a view owned by the SYS user and does not have public access, therefore the SYS user must explicitly grant the SELECT privilege on v_$parameter to the scheduled workbook results schema name.

Notes

• The <user> below is the name of the scheduled workbook results schema created by the batchusr.sql script (for more information, see "How to specify the owner of the tables containing scheduled workbooks results").

To use SQL*Plus to manually grant the select privilege on v_$parameter to run scheduled workbooks, for Oracle9i (and later) databases:

1. Start SQL*Plus (if it is not already running) and connect as the SYS user, or a user to which the sysdba privilege has been granted.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT sys/sys_pw@database AS SYSDBA;
   
   

   Where sys is the SYS user and sys_pw is the SYS user password.

2. Type the following at the command prompt:

   SQL> grant SELECT on v_$parameter to <user>; 
   
   

   Note: To enable the <user> to select from the synonym v$parameter, you have to grant the SELECT privilege on the view v_$parameter.

   Note: To grant SELECT on v_$parameter you must log in as the SYS user. If you are unable to login as the SYS user or are unsure about the SYS user name and password, see your database administrator.

To use SQL*Plus to verify the select privilege on v_$parameter has been granted:

1. Start SQL*Plus (if it is not already running) and connect as the <user>.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT <user>/<user_pw>@database;
   
   

   Where user is the is the name of the scheduled workbook results schema and user_pw is the user password.

2. Type the following at the command prompt:

   SQL> select count (*) from v$parameter; 
   
   

   SQL*Plus displays the number of rows when the SELECT privilege has been granted to the <user>.

   SQL*Plus displays an 'ORA - 01031 Insufficient privileges' error message when the SELECT privilege has not been granted to the <user>.

How to control the frequency that the server checks for new scheduled jobs

You use the following parameter in the INIT<SID>.ORA file to control scheduled workbook processing:

• job_queue_processes

For more information about this parameter, see "What Oracle database features support workbook scheduling?".

To control the frequency of scheduled workbook processing:

1. Locate the INIT<SID>.ORA file.

   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.

2. Add the following line to the INIT<SID>.ORA file:

   job_queue_processes = <a_value>
   
   

   where:

   • <a_value> is the number of concurrent processing requests that can be used to process DBMS_JOB (if other applications use DBMS_JOB, we recommend you set this parameter to at least 2)

   For example, you might add the following line to the INIT<SID>.ORA file:

   job_queue_processes = 2
   
   

Notes

• Discoverer's summary management feature and workbook scheduling feature both use the Oracle database's native scheduling capability. The value you specify for job_queue_processes will affect both features. The database must be stopped and re-started for this change to take effect.

How to view and manage scheduled workbooks

As the Discoverer manager, you will want to monitor the status of currently scheduled workbooks, as well as:

• view error messages for scheduled workbooks that have not run successfully

• delete scheduled workbook results from the database

• remove scheduled workbooks from the process queue

• edit scheduled workbook settings

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:

1. Choose Tools | Manage Scheduled Workbooks to display the "Manage Scheduled Workbooks dialog".

2. Click Select to display the "Select User/Role dialog" where you can search for and select the user or role whose scheduled workbooks you want to display in the Show workbooks for field.

   Figure 8-1 Select User/Role dialog

   
   Description of "Figure 8-1 Select User/Role dialog"
   
   

3. Enter the search criteria in the Search For field and click Go.

   Discoverer Administrator displays the search results in the Results list.

4. Select a user or role from the Results list and click OK.

   Discoverer displays the following screenshot showing the results of scheduled workbooks owned by the user you selected in the Results list field.

   Figure 8-2 Manage Scheduled Workbooks dialog

   
   Description of "Figure 8-2 Manage Scheduled Workbooks dialog"
   
   

   Hints: You can:

   • show all scheduled workbooks by selecting the Select All Users check box

   • sort the list using different columns by clicking the appropriate column heading

   • view a description (where one exists) of the currently selected scheduled workbook in the Description field.

5. (optional) To view an error message for a scheduled workbook that has not run successfully, select the scheduled workbook and click View Error.

   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.

6. (optional) To prevent a scheduled workbook from running by removing it from the process queue:

   1. Select the scheduled workbook.

   2. Click Unschedule.

   3. Click Yes to confirm that you want to remove the scheduled workbook.

7. (optional) To delete the results of a scheduled workbook from the database (i.e. after the scheduled workbook has run), select the scheduled workbook and click Delete.

8. (optional) To edit a scheduled workbook scheduling information, select the workbook and click Edit... to display the Schedule Workbook dialog and:

   • use the "Schedule Workbook dialog: General tab" to view the sheets included in the scheduled workbook, set the schedule time and repeat interval.

   • use the "Schedule Workbook dialog: General tab" to view the name of the scheduled workbook, view and edit the description, choose whether to save just the latest results and set the interval after which results will automatically be deleted.

9. (optional) To see the effect of the changes you have made in the "Manage Scheduled Workbooks dialog", click Refresh.

10. Click Close to close the Manage Scheduled Workbooks dialog.

Notes

• The Delete button only appears when you select a scheduled workbook that has run.

• If a scheduled workbook uses EUL objects that have changed between the time when the workbook is scheduled and the time when the result set is displayed, the scheduled workbook's status is set to EUL has changed.