8 Scheduling Workbooks

This chapter explains how to enable users to schedule workbooks and contains the following topics:

• "What are workbooks and scheduled workbooks?"

• "Why schedule workbooks?"

• "What happens when an end user schedules a workbook?"

• "Where to store the results of scheduled workbooks?"

• "What are the prerequisites for scheduling workbooks?"

• "How to confirm that the DBMS_JOB package is installed"

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

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

• "How to view and manage scheduled workbooks"

8.1 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 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 Discoverer 10.1.2 and pre-Oracle9i database releases".

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

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

Sequence	Action	Notes
1.	User selects worksheet (or worksheets) to include in scheduled workbook	N/A
2.	User specifies date, time, and frequency at which scheduled workbook is to run	N/A
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: Privileges tab". If the user exceeds their limit, Discoverer Plus displays a message and the workbook is not scheduled.
4.	Discoverer adds scheduled workbook to DBMS_JOB within Oracle database kernel	N/A
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.
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	N/A
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: Privileges tab").

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

Owner	Notes
the schema running the scheduled workbook	The advantage of specifying the result set storage in the end user's schema is that a database limit can be specified on the maximum amount of data an end user can store in the database. If the result set is stored under the end user's schema, you keep control over the maximum amount of space one individual end user can fill with result sets. If the end user creates a scheduled workbook that fills the space, only that end user's schema is affected. A disadvantage is that it increases the maintenance overhead. Note: This option is not recommended for Oracle Applications users.
scheduled workbook results schema	The advantage of specifying the result set storage in a scheduled workbook results schema is that it is generally easier to manage than using multiple database user schemas. Also each end user does not need to set up additional database privileges to run scheduled workbooks. The disadvantage is that space quota is shared and so could be exhausted by a single end user.

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

8.5 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")

• 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")

8.6 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 Oracle databases (version 9.2.0.7 or 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;
   

8.7 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;
   

   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>;
   

   <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 (not the database user scheduling the workbook)

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

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;
   

   Notes:

   • To run the batchusr.sql script, you must know the user name and password of the EUL owner .

   • Oracle Applications users should use the batchuser_app.sql script. To run the batchusr_app.sql script, you must know the Oracle Applications Foundation Name (FNDNAM). If you do not know the FNDNAM, ask your database administrator.

3. (if running the batchusr_app.sql script) Enter the Oracle Applications Foundation Name (FNDNAM). For more information, contact your database administrator.

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

   Passwords are case sensitive in Oracle databases (Enterprise Edition release 11.1 or later).

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

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

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

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

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

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

10. 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 batchusr.sql or batchusr_app.sql script.

11. 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 batchusr.sql or batchusr_app.sql script 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 or batchusr_app.sql script will not be able to directly schedule a workbook using Discoverer Plus.

8.8 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 Oracle databases (version 9.2.0.7 or 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> grant SELECT on v_$parameter to <user>; 
   

   Note: To enable the <user> to select from the synonym v$parameter, you must 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>.

8.9 How to view and manage scheduled workbooks

As the Discoverer manager, you would want to monitor the status of currently scheduled workbooks, and perform the following tasks:

• 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 log in as an applications user with administrative privileges, 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

   
   

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

   
   

   Tips: 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 (that is, 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.